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