Java操作excel的综合应用(jxl)
根据excel模板生成excel报表文件--用于报表打印
jxl修改excel模板文件，实现动态数据分页打印
1.支持公式运算
2.支持对合并的单元格复制
package mcfeng.util.excel;import java.io.File;import java.io.IOException;import jxl.CellType;import jxl.Workbook;import jxl.format.CellFormat;import jxl.read.biff.BiffException;import jxl.write.Label;import jxl.write.WritableCell;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;import mcfeng.util.ExcelDataSource;import mcfeng.util.MoneyUtil;import mcfeng.util.StringUtil;public class ExcelEditByModel {//list中取数据private final static String LIST_FLAG = "##";//map中取数据private final static String MAP_FLAG = "#&";//数字类型处理，支持公式private final static String NUM_FLAG = "#_&";//大写金额处理private final static String DX_FLAG = "##D&";public static void editExcel(int totalPage,String sourcefile, String targetfile) {File file1 = new File(sourcefile);File file2 = new File(targetfile);editExcel(totalPage,file1, file2);}public static void editExcel(int totalPage,File sourcefile, File targetfile) {String mycellValue = null;Workbook wb = null;try {// 构造Workbook（工作薄）对象wb = Workbook.getWorkbook(sourcefile);} catch (BiffException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}WritableWorkbook wwb = null;try {// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象wwb = Workbook.createWorkbook(targetfile, wb);} catch (IOException e) {e.printStackTrace();}if (wwb != null) {// 读取第一张工作表// Workbook的getSheet方法的参数，表示工作表在工作薄中的位置WritableSheet ws = wwb.getSheet(0);int scale = ws.getSettings().getScaleFactor();// 获取页面缩放比例int rowNum = ws.getRows();int colNum = ws.getColumns();//计算出每页行数int pageNum = rowNum/totalPage;for (int j = 0; j < rowNum; j++) {// 得到当前行的所有单元格//计算出取数据的位置int dataNum = j/pageNum;for (int k = 0; k < colNum; k++) {// 对每个单元格进行循环WritableCell mywc = ws.getWritableCell(k, j);System.out.println("mywc.getType(): " + mywc.getType());if (mywc.getType() == CellType.LABEL) {Label l = (Label) mywc;String cellValue = l.getContents();//处理后的值String opValue = null;System.out.println("cellValue: " + cellValue);// 处理excel单元格中#开头的字符串if (cellValue != null && cellValue.startsWith("#")) {if (cellValue.startsWith(LIST_FLAG)) {if(cellValue.startsWith(DX_FLAG)){opValue = cellValue.replaceAll(DX_FLAG, "");}else{opValue = cellValue.replaceAll(LIST_FLAG, "");}if (StringUtil.isNumeric(opValue)) {mycellValue = ExcelDataSource.getData(opValue,dataNum);if(cellValue.startsWith(DX_FLAG)){mycellValue = MoneyUtil.amountToChinese(mycellValue);}}} else if (cellValue.startsWith(MAP_FLAG)) {opValue = cellValue.replaceAll(MAP_FLAG, "");mycellValue = ExcelDataSource.getData(opValue,dataNum);}else if (cellValue.startsWith(NUM_FLAG)) {//支持公式运算opValue = cellValue.replaceAll(NUM_FLAG, "");mycellValue = ExcelDataSource.getData(opValue,dataNum);System.out.println("mycellValue: " + mycellValue);//获取字体，重新设置CellFormat wcff = mywc.getCellFormat();jxl.write.Number num = new jxl.write.Number(k,j,Double.valueOf(mycellValue),wcff);try {ws.addCell(num);} catch (RowsExceededException e) {e.printStackTrace();} catch (WriteException e) {e.printStackTrace();}continue;}l.setString(mycellValue);}}}}//设置页面缩放比例ws.getSettings().setScaleFactor(scale);try {// 写入 Excel 对象wwb.write();// 关闭可写入的 Excel 对象wwb.close();// 关闭只读的 Excel 对象wb.close();} catch (IOException e) {e.printStackTrace();} catch (WriteException e) {e.printStackTrace();}}}}package mcfeng.util.excel;import java.io.File;import java.io.IOException;import jxl.Range;import jxl.Workbook;import jxl.read.biff.BiffException;import jxl.write.WritableCell;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;public class ExcelEditByModelPage {public static void editExceltoModel(int totalPage,String sourcefile, String targetfile) {File file1 = new File(sourcefile);File file2 = new File(targetfile);editExceltoModel(totalPage, file1,file2);}public static void editExcelbyModelPage(int totalPage,String sourcefile,String tempfile, String targetfile) {File file1 = new File(sourcefile);File file2 = new File(tempfile);File file3 = new File(targetfile);editExcelbyModelPage(totalPage, file1,file2,file3);}public static void editExcelbyModelPage(int totalPage, File sourcefile,File tempfile,File targetfile){if(totalPage == 1){ExcelEditByModel.editExcel(totalPage, sourcefile, targetfile);return;}//需要分页时，生成中间模板文件ExcelEditByModel.editExcel(totalPage,editExceltoModel(totalPage,sourcefile,tempfile), targetfile);}// 生成分页模板public static File editExceltoModel(int totalPage, File sourcefile,File targetfile) {Workbook wb = null;try {// 构造Workbook（工作薄）对象wb = Workbook.getWorkbook(sourcefile);} catch (BiffException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}WritableWorkbook wwb = null;try {// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象wwb = Workbook.createWorkbook(targetfile, wb);} catch (IOException e) {e.printStackTrace();}if (wwb != null) {// 读取第一张工作表// Workbook的getSheet方法的参数，表示工作表在工作薄中的位置WritableSheet ws = wwb.getSheet(0);int scale = ws.getSettings().getScaleFactor();// 获取页面缩放比例int rowNum = ws.getRows();int colNum = ws.getColumns();System.out.println("rowNum: " + rowNum);System.out.println("colNum: " + colNum);//找出合并的单元格Range[] ranges = ws.getMergedCells();for(int rnum = 0;rnum < ranges.length;rnum++){System.out.println("左上行数" + ranges[rnum].getTopLeft().getRow());System.out.println("左上列数" + ranges[rnum].getTopLeft().getColumn());System.out.println("右下行数" + ranges[rnum].getBottomRight().getRow());System.out.println("右下列数" + ranges[rnum].getBottomRight().getColumn());}int i = 1;while (i < totalPage) {for (int row = 0; row < rowNum; row++) {// 得到当前行的所有单元格for (int col = 0; col < colNum; col++) {// 对每个单元格进行循环// 复制单元格WritableCell cell = ws.getWritableCell(col, row).copyTo(col, row + (rowNum*i));try {ws.addCell(cell);} catch (RowsExceededException e) {e.printStackTrace();} catch (WriteException e) {e.printStackTrace();}}}//按照模板合并单元格for(int rnum = 0;rnum < ranges.length;rnum++){int lcol = ranges[rnum].getTopLeft().getColumn();int lrow = ranges[rnum].getTopLeft().getRow() + (rowNum*i);int rcol = ranges[rnum].getBottomRight().getColumn();int rrow = ranges[rnum].getBottomRight().getRow() + (rowNum*i);try {ws.mergeCells(lcol, lrow, rcol, rrow);} catch (RowsExceededException e) {e.printStackTrace();} catch (WriteException e) {e.printStackTrace();}}i++;}//设置页面缩放比例ws.getSettings().setScaleFactor(scale);}try {// 写入 Excel 对象wwb.write();// 关闭可写入的 Excel 对象wwb.close();// 关闭只读的 Excel 对象wb.close();} catch (IOException e) {e.printStackTrace();} catch (WriteException e) {e.printStackTrace();}return targetfile;}}