关于Java中用Poi包写复杂Excel表格代码
前一阵子客户要求复杂表格，所以参考了网上的一些资料，自己写了一个多功能封装Excel类的方法，可以对于表格的列与行进行合并 ，如图示：
?
测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据(*)? 测试数据测试数据测试数据测试数据(*)测试数据测试数据，测试数据测试数据，测试数据测试数据测试数据测试数据是测试数据测试数据/测试数据测试数据无测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据这是备注业务要素描述分项业务描述测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据套餐名称及编号测试数据测试数据,测试数据测试数据:测试数据测试数据测试数据测试数据测试数据测试数据,测试数据测试数据:测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据3000.01.01测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据?
代码如下：
?
package cn.indoing.poi.chap3;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.hssf.util.Region;/** * <p>Title:关于Java中用Poi包写复杂Excel表格代码 </p> * * <p>Description: </p> * * <p>Copyright: Copyright (c) 2009</p> * * <p>Company: </p> * * @author virture * @version 1.0 */public class PerFeeToExcel{private static int currRows = 0;// 当前所在行/** * 创建一行数据 * * @param st * @param normalStyle * @param ruleStr * @param lettersStr */public void WriteColsCell(HSSFSheet st, HSSFCellStyle normalStyle, HSSFCellStyle colorStyle,int[] colorCol,String ruleStr, String[] letters){String[] temp = ruleStr.split(";");int[] rules = new int[temp.length];for (int i = 0; i < temp.length; i++){rules[i] = Integer.parseInt(temp[i]);}HSSFRow row = st.createRow(currRows);int preCols = 0;int colorInt=0;//当前行颜色for (int i = 0; i < rules.length; i++){st.addMergedRegion(new Region(currRows, (short) preCols, currRows, (short) (preCols + rules[i] - 1)));HSSFCell cell = row.createCell((short) preCols);if (colorCol!=null && colorCol.length>colorInt){if (colorCol[colorInt]==i){cell.setCellValue(new HSSFRichTextString(letters[i]));cell.setCellStyle(colorStyle);colorInt++;}else{cell.setCellValue(new HSSFRichTextString(letters[i]));cell.setCellStyle(normalStyle);}}else{cell.setCellValue(new HSSFRichTextString(letters[i]));cell.setCellStyle(normalStyle);}for (int j = preCols + 1; j < rules[i]; j++){row.createCell((short) j).setCellStyle(normalStyle);}preCols = preCols + rules[i];}currRows++;}/** * 增加几列数据,只能对第一列数据有效 * * @param st * @param normalStyle * @param ruleStr * @param lettersStr */public void WriteRowsCell(HSSFSheet st, HSSFCellStyle normalStyle,HSSFCellStyle colorStyle,int[] colorCol,String ruleStr, String[] letters){String[] temp = ruleStr.split(";");int[] rules = new int[temp.length];for (int i = 0; i < temp.length; i++){rules[i] = Integer.parseInt(temp[i]);}int curCols = 0;//当前所在列int curLetters = 0;//当前字符值int tempRows=0;int colorInt=0;//当前行颜色for (int i = 0; i < rules.length; i++){if (rules[i] != -1){if (rules[i]>1) tempRows=rules[i];st.addMergedRegion(new Region(currRows, (short) curCols, currRows + rules[i] - 1, (short) curCols));HSSFRow row = st.createRow(currRows);HSSFCell cell = row.createCell((short) curCols);if (colorCol!=null && colorCol.length>colorInt){if (colorCol[colorInt]==curLetters){cell.setCellValue(new HSSFRichTextString(letters[curLetters++]));cell.setCellStyle(colorStyle);colorInt++;}else{cell.setCellValue(new HSSFRichTextString(letters[curLetters++]));cell.setCellStyle(normalStyle);}}else{cell.setCellValue(new HSSFRichTextString(letters[curLetters++]));cell.setCellStyle(normalStyle);}curCols++;} else{currRows++;if ((tempRows--)>1) curCols = 1;}}}/** * @param args */public static void main(String[] args) throws IOException{HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet st = wb.createSheet("测试数据测试数据");// 这里首先创建一个单元格样式对象，设置了四周的边框以及字体可以换行// 其中的字体换行是用来竖向显示其中的一个单元格的// 更好的一点儿做法是再做一个单独的单元格样式对象// 要不然在处理自动列宽的时候可能会有点儿小问题HSSFCellStyle normalStyle = wb.createCellStyle();normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);normalStyle.setWrapText(true);HSSFCellStyle colorStyle = wb.createCellStyle();//背景有颜色colorStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);colorStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);colorStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);colorStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);colorStyle.setFillPattern(HSSFCellStyle.BRICKS);colorStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);colorStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);colorStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);colorStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);PerFeeToExcel pe = new PerFeeToExcel();pe.WriteColsCell(st, normalStyle, colorStyle,null,"3", new String[]{"测试数据测试数据"});pe.WriteColsCell(st, colorStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据(*)"," 测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据(*)","测试数据测试数据，测试数据测试数据，测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","是"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据/测试数据测试数据","无"});pe.WriteRowsCell(st, normalStyle, colorStyle,null, "2;1;1;-1;1;1;-1", new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据"});pe.WriteRowsCell(st, normalStyle, colorStyle,null, "2;1;1;-1;1;1;-1", new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"2;1", new String[]{"测试数据测试数据","这是备注"});pe.WriteColsCell(st, colorStyle, colorStyle,null,"1;1;1", new String[]{"业务要素","描述分项","业务描述"});pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "8;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1", new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","套餐名称及编号","测试数据测试数据,测试数据测试数据:测试数据测试数据","测试数据测试数据","测试数据测试数据,测试数据测试数据:测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","3000.01.01"});pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "8;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1",new String[]{"测试数据测试数据","测试数据测试数据测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据"});pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "8;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1",new String[]{"测试数据测试数据","测试数据测试数据。测试数据测试数据","测试数据测试数据","测试数据测试数据（测试数据测试数据）","","测试数据测试数据","月测试数据测试数据","测试数据测试数据","否","测试数据测试数据","否","测试数据测试数据","无","其他","无"});pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "8;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1;1;1;-1",new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据","营业前后台能否受理该业务","测试数据测试数据","测试数据测试数据","测试数据测试数据","测试数据测试数据 （测试数据测试数据）","测试数据测试数据，测试数据测试数据","业务服务区编号","","测试数据测试数据","","其他","测试数据测试数据"});pe.WriteRowsCell(st, normalStyle,colorStyle,new int[]{0}, "2;1;1;-1;1;1;-1",new String[]{"测试数据测试数据","测试数据测试数据，测试数据测试数据","","测试数据测试数据",""});pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据","测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据","测试数据测试数据测试数据测试数据测试数据测试数据测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"测试数据测试数据",""});pe.WriteColsCell(st, colorStyle, colorStyle,null,"3", new String[]{"测试数据测试数据"});pe.WriteColsCell(st, colorStyle, colorStyle,null,"1;1;1", new String[]{"测试数据测试数据","测试数据测试数据","测试数据测试数据"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"1;1;1", new String[]{"测试数据测试数据1","测试数据测试数据1","测试数据测试数据1"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"1;1;1", new String[]{"测试数据测试数据2","测试数据测试数据2","测试数据测试数据2"});pe.WriteColsCell(st, normalStyle, colorStyle,null,"1;1;1", new String[]{"测试数据测试数据3","测试数据测试数据3","测试数据测试数据3"});pe.WriteColsCell(st, normalStyle, colorStyle,new int[]{0},"1;2", new String[]{"备注(注意,不能删除该行！)","测试数据测试数据测试数据测试数据测试数据测试数据"});OutputStream outputStream = new FileOutputStream("c:/a.xls");wb.write(outputStream);outputStream.close();pe.readExcel("c:/a.xls");}public void readExcel(String sFileName){try{Workbook book = Workbook.getWorkbook(new File(sFileName));// 获得第一个工作表对象Sheet sheet = book.getSheet(0);// 得到第一列第一行的单元格int columnum = sheet.getColumns(); // 得到列数int rownum = sheet.getRows(); // 得到行数for (int i = 0; i < rownum; i++) // 循环进行读写{for (int j = 0; j < columnum; j++){Cell cell1 = sheet.getCell(j, i);String result = cell1.getContents();System.out.print("content[" + i + "][" + j + "]=" + result);System.out.print(" ");}System.out.println();}book.close();} catch (BiffException e){// TODO Auto-generated catch blocke.printStackTrace();} catch (IndexOutOfBoundsException e){// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e){// TODO Auto-generated catch blocke.printStackTrace();}}}?
1 楼 wh8766 2009-05-03 代码收藏了~