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