然后根据你是需要合并行还是合并列，分别处理。如果是合并行的话，那么列的信息就不用变了，我们只需要处理getCellRect和rowAtPoint()。 如果是合并列的话，我们那个row的信息就不变了，只需要处理column和getCellRect的信息就好了。下面是一个合并列的例子：
public Rectangle getCellRect(int row, int column, boolean includeSpacing){
// 该方法是Jtable构建时所必须的
if (map==null) return super.getCellRect(row,column, includeSpacing)
// 指定单元格的可视单元格列值
int sk=map.visibleCell(row,column)
Rectangle r1=super.getCellRect(row,sk,includeSpacing)
// 如果指定单元格列宽不为1，累计出跨列单元格的宽度
if (map.span(row,sk)!=1)
for (int i=1i<map.span(row,sk)i++){
r1.width+=getColumnModel().getColumn(sk+i).getWidth()
}
return r1
}
public int columnAtPoint(Point p) {
int x=super.columnAtPoint(p)
// 当指定位置不在Table内时，返回－1
if (x<0) return x
int y=super.rowAtPoint(p)
//获取指定位置可视单元格的列值
return map.visibleCell(y,x)
}
利用java *** 作Excel源东东－jxl.jar读取前单元格值 String cellValue = cells[k].getContents() sb.append(cellValue+" ") } } sb.append("") } sb.append("
") } } //关闭资源释放内存 wb.close() return sb.toString() } 二.写入Excel文件格式比文本内容加粗加某些颜色等参考jxlapi同推荐篇错文章：/developerworks/cn/java/l-javaExcel/?ca=j-t10java 代码public static void writeExcel(String fileName){ WritableWorkbook wwb = null try { //首先要使用Workbook类工厂创建写入工作薄(Workbook)象 wwb = Workbook.createWorkbook(new File(fileName)) } catch (IOException e) { e.printStackTrace() } if(wwb!=null){ //创建写入工作表 //WorkbookcreateSheet两参数第工作表名称第二工作表工作薄位置 WritableSheet ws = wwb.createSheet("sheet1", 0) //面始添加单元格 for(int i=0i0){ //每工作表进行循环 for(int i=0iif(breakSheet) break //前工作表行数 int rowNum = sheet[i].getRows() boolean breakRow = false for(int j=0jif(breakRow) break //前行所单元格 Cell[] cells = sheet[i].getRow(j) if(cells!=null&&cells.length>0){ boolean breakCell = false //每单元格进行循环 for(int k=0kif(breakCell) break //读取前单元格值 String cellValue = cells[k].getContents() if(cellValue==null) continue if(cellValue.contains(keyWord)){ res = true breakCell = true breakRow = true breakSheet = true } } } } } } //关闭资源释放内存 wb.close() return res } 四.往Excel插入图片图标插入图片实现容易参看代码：java 代码public static void insertImg(WritableSheet dataSheet, int col, int row, int width, int height, File imgFile){ WritableImage img = new WritableImage(col, row, width, height, imgFile) dataSheet.addImage(img) } 代码注释已经清楚概用再解释我用程序验证：java 代码try { //创建工作薄 WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls")) //待插入工作表 WritableSheet imgSheet = workbook.createSheet("Images",0) //要插入图片文件 File imgFile = new File("D:/1.png") //图片插入第二行第单元格宽各占六单元格 insertImg(imgSheet,0,1,6,6,imgFile) workbook.write() workbook.close() catch (IOException e) { e.printStackTrace() catch (WriteException e) { e.printStackTrace() jxl支持png格式图片jpg格式gif格式都支持五.插入页眉页脚般页眉页脚都三部,左,,右三部,利用代码实现插入页眉页脚java 代码public static void setHeader(WritableSheet dataSheet,String left,String center,String right){ HeaderFooter hf = new HeaderFooter() hf.getLeft().append(left) hf.getCentre().append(center) hf.getRight().append(right) //加入页眉 dataSheet.getSettings().setHeader(hf) //加入页脚 //dataSheet.getSettings().setFooter(hf) } 我用代码测试该:java 代码try { //创建工作薄 WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls")) //待插入工作表 WritableSheet dataSheet = workbook.createSheet("加入页眉",0) ExcelUtils.setHeader(dataSheet, "chb", "2007-03-06", "第1页,共3页") workbook.write() workbook.close() } catch (IOException e) { e.printStackTrace() } catch (WriteException e) { e.printStackTrace() } } 合并单元格：//首先创建sheetWritableSheet sheetWrite=writeWorkbook.createSheet("sheet名称",0)// 单元格（column, row）单元格（column1, row1）进行合并sheetWrite.mergeCells(column, row, column1, row1)java *** 作exceljxl加边框jxl合并单元格,单元格设置单元WritableCellFormat headerFormat = new WritableCellFormat(HEADER_FONT_STYLE)//水平居齐headerFormat.setAlignment(Alignment.CENTRE)//竖直向居齐headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE)//建立标签参数依：列索引、行索引、内容、格式Label seqLabel = new Label(0, 0, "序号", headerFormat)//增加单元格sheet.addCell(seqLabel)//合并单元格参数依：列索引、行索引、列索引+需要合并列数、行索引+需要合并行数sheet.mergeCells(0, 0, 0, 1)//设置单元格宽度字符单位sheet.setColumnView(0, "序号".length() + 10)Label basicInfoLabel = new Label(1, 0, "基本信息",headerFormat)sheet.addCell(basicInfoLabel)sheet.mergeCells(1, 0, 3, 0)//合并垮3行sheet.mergeCells(1, 1, 1, 3)//合并垮3列label = new Label(0, 1, "苹)sheet.mergeCells(0,1, 0, 3- 1)//合并垮3列WritableCellFormat cellFormat = new WritableCellFormat()cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE)// 垂直居label.setCellFormat(cellFormat)sheet.addCell(label)注：再使cellFormat需要重新NEW WritableCellFormatjava怎么 *** 作excel合并单元格
/***
* @param context
* @param dictionary
* @param rows 数据行
* @param fileName 文件名
* @param fields 列名
* @param fieldsName 字段名
*/
private void outExcelFile(HttpContext context,
IContextDictionary dictionary, DataRowCollections rows,
String fileName, List<String> fields, List<String> fieldsName) {
int cellSize = fields.size()
if(cellSize == 0){
LogManager.debug("没有指定列头信息,无法导出Excel文件!")
return
}
//============创建样式 start
HSSFWorkbook workbook = new HSSFWorkbook()
HSSFSheet sheet = workbook.createSheet()
//列头字体样式
HSSFFont headerFont = workbook.createFont()
headerFont.setFontName("宋体")
headerFont.setFontHeightInPoints((short) 12)
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
//列头样式
HSSFCellStyle headerStyle = workbook.createCellStyle()
headerStyle.setFont(headerFont)
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)// 左右居中
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)// 上下居中
headerStyle.setLocked(true)
headerStyle.setWrapText(true)
//标题样式
HSSFFont titleFont = workbook.createFont()
titleFont.setFontName("宋体")
titleFont.setFontHeightInPoints((short) 15)
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
HSSFCellStyle titleStyle = workbook.createCellStyle()
titleStyle.setFont(titleFont)
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)
titleStyle.setLocked(true)
titleStyle.setWrapText(true)
//普通单元格字体样式
HSSFFont cellFont = workbook.createFont()
cellFont.setFontName("宋体")
cellFont.setFontHeightInPoints((short)12)
//普通单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle()
cellStyle.setFont(cellFont)
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT)// 左右居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)// 上下居中
cellStyle.setLocked(true)
cellStyle.setWrapText(true)
//============创建样式 end
//设置序号列、列宽和标题行 start
HSSFRow titleRow = sheet.createRow(0)
titleRow.setHeightInPoints(50)
HSSFCell titleCell = titleRow.createCell(0)
titleCell.setCellValue(new HSSFRichTextString(fileName))
titleCell.setCellStyle(titleStyle)
//sheet.addMergedRegion(new Region(0,(short)0,0,(short)cellSize))//合并单元格--方法过时
//CellRangeAddress 起始行 结束行 起始列 结束列
sheet.addMergedRegion(new CellRangeAddress(0,0,(short)0,(short)cellSize))//合并单元格
//显示总的数据个数 start
HSSFRow countRow = sheet.createRow(1)
countRow.setHeightInPoints(40)
HSSFCell countCell = countRow.createCell(0)
countCell.setCellValue(new HSSFRichTextString("共计专项检查（"+rows.size()+"）项"))
countCell.setCellStyle(headerStyle)
sheet.addMergedRegion(new CellRangeAddress(1,1,(short)0,(short)cellSize))//合并单元格
//显示总的数据个数 end
HSSFRow headerRow = sheet.createRow(2)
headerRow.setHeightInPoints(35)
HSSFCell headerCell = null
//序号
int startIndex = 0
headerCell = headerRow.createCell(0)
sheet.setColumnWidth(0, 2000)
headerCell.setCellValue(new HSSFRichTextString("序号"))
headerCell.setCellStyle(headerStyle)
startIndex ++
//列头
for(int i = 0 i < cellSize i ++){
sheet.setColumnWidth(startIndex + i, 7000)
headerCell = headerRow.createCell(startIndex + i)
headerCell.setCellValue(new HSSFRichTextString(fields.get(i)))
headerCell.setCellStyle(headerStyle)
}
//设置序号列、列宽和标题行 end
//文件正文 start
int rowNum = 1
int rowIndex = 0
HSSFRow row = null
List<Integer[]> cellRangeLst = new ArrayList<Integer[]>(0)
Integer [] arr = null
int l = 0
String orgName = ""
for(int j = 2 j<rows.size()+2 j++){//循环行
DataRow dataRow = rows.get(rowIndex) //对应数据库字段
HSSFCell cell = null
row = sheet.createRow(j + 1)
row.setHeightInPoints(55)
//序号
cell = row.createCell(0)
cell.setCellValue(rowNum++)
cell.setCellStyle(cellStyle)
if(StringHelper.isNullOrEmpty(orgName)){
arr = new Integer[2]
arr[0] = j + 1
l =j + 1
orgName = dataRow.getString("ORGNAME")
}else{
if(!orgName.equals(dataRow.getString("ORGNAME"))){
arr[1] = j
cellRangeLst.add(arr)
sheet.addMergedRegion(new CellRangeAddress(l,j,1,1))
arr = new Integer[2]
l = j+1
orgName = dataRow.getString("ORGNAME")
}
if(rowIndex == rows.size() - 1){
arr[1] = j+1
cellRangeLst.add(arr)
sheet.addMergedRegion(new CellRangeAddress(l,j+1,1,1))
}
}
for(int k = 0 k < cellSize k++){
cell = row.createCell(k + startIndex)
String column = fieldsName.get(k) //对应数据库字段
String value = ""
if("APSJ".equals(column)){
value = getAPSJValue(dataRow.getString(column))
}else{
value = dataRow.getString(column)
}
cell.setCellValue(new HSSFRichTextString(value))
cell.setCellStyle(cellStyle)
}
rowIndex ++
}
//文件正文 end
//for(Integer[] te : cellRangeLst){
// sheet.addMergedRegion(new CellRangeAddress(te[0],te[1],1,1))//合并处室单元格
//}
//下载
HttpServletResponse response = context.getResponse()
response.setContentType("application/x-downloadcharset=UTF-8")
String title = "export"
try {
title = java.net.URLEncoder.encode(fileName, "UTF-8")
} catch (UnsupportedEncodingException e) {
e.printStackTrace()
}
response.addHeader("Content-Disposition", "attachmentfilename=" + title + ".xls")
try {
OutputStream out = response.getOutputStream()
workbook.write(out)
out.flush()
out.close()
} catch (IOException e) {
e.printStackTrace()
}
}
//参考一下吧
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）