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;}}