根据excel模板生成excel报表文件--用于报表打印
jxl修改excel模板文件,实现动态数据分页打印
1.支持公式运算
2.支持对合并的单元格复制
生成分页模板
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;
- }
- }