poi excel报表生成的流程和例子

网上的poi例子大多是对excel的读和写的单独操作，而实际项目中常常是向一个已经做好的报表中导入数据，所以在这里做了一个简单的例子。

具体流程如图：

poi最新版本下载：http://poi.apache.org/download.html

1.共通类：主要包含excel版本的判断，excel行的复制，excel函数的处理，打印格式的设置。

import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;public abstract class ExcelWriter{/** * 文件类型excle2003,excle2007... *///protected final String FILE_TYPE = "2007";/** * 文件模板 *///protected final String FILE_TEMPLATE = "";/** * 文件数据 */protected List<Object> fileData;/** * 模板一页的列数 *///protected final int TEMPLATE_COLSPAN = 7;/** * 模板一页的行数 *///protected final int TEMPLATE_ROW = 53;/** * 模板一页的数据行数 *///protected final int DATA_ROW = 49;/** * 数据总数 */protected int dateCount = 0;/** * 总页数 */protected int pageCount = 0;public List<Object> getFileData() {return fileData;}public void setFileData(List<Object> fileData) {this.fileData = fileData;}/** * @Description: 取得模板文件名称 *  * @return 文件名称 */public String getFileName(){return null;}/** * @Description: 取得工作录 *  * @param is 文件流 * @return 工作录 * @throws IOException */protected abstract Workbook getWorkbook(InputStream is) throws IOException;/** * @Description: 取得合并单元格 *  * @param sheet sheet * @return 合并单元格 */protected List<CellRangeAddress> getMergedRegion(Sheet sheet){List<CellRangeAddress> ranges = new ArrayList<CellRangeAddress>();for (int i = 0; i < sheet.getNumMergedRegions(); i++) {ranges.add(sheet.getMergedRegion(i));}return ranges;}/**     * @Description: 拷贝行并填充数据     *      * @param sheet sheet页     * @param oldRanges 合并单元格     * @param pStartRow 起始读取行     * @param pEndRow 结束读取行     * @param startRow 起始复制行     */protected void copyRows(Sheet sheet, List<CellRangeAddress> oldRanges,    int pStartRow, int pEndRow, int startRow) {    Row sourceRow = null;    Row targetRow = null;Cell sourceCell = null;Cell targetCell = null;int cType;int i;int j;int targetRowFrom;int targetRowTo;if ((pStartRow == -1) || (pEndRow == -1)) {return;}// 拷贝合并的单元格。for (int k = 0; k < oldRanges.size(); k++) {CellRangeAddress oldRange=  oldRanges.get(k);CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(),oldRange.getFirstColumn(), oldRange.getLastColumn());if (oldRange.getFirstRow() >= pStartRow && oldRange.getLastRow() <= pEndRow) {targetRowFrom = oldRange.getFirstRow() - pStartRow + startRow;targetRowTo = oldRange.getLastRow() - pStartRow + startRow;newRange.setFirstRow(targetRowFrom);newRange.setLastRow(targetRowTo);//sheet.addMergedRegion(oldRange);sheet.addMergedRegion(newRange);}}// 设置列宽for (i = pStartRow; i <= pEndRow; i++) {sourceRow = sheet.getRow(i);if (sourceRow != null) {for (j = sourceRow.getLastCellNum(); j > sourceRow.getFirstCellNum(); j--) {sheet.setColumnWidth(j, sheet.getColumnWidth(j));sheet.setColumnHidden(j, false);}break;}}// 拷贝行并填充数据for (; i <= pEndRow; i++) {sourceRow = sheet.getRow(i);if (sourceRow == null) {continue;}targetRow = sheet.createRow(i - pStartRow + startRow);targetRow.setHeight(sourceRow.getHeight());for (j = sourceRow.getFirstCellNum(); j < sourceRow.getPhysicalNumberOfCells(); j++) {sourceCell = sourceRow.getCell(j);if (sourceCell == null) {continue;}targetCell = targetRow.createCell(j);// targetCell.setEncoding(sourceCell.getEncoding());targetCell.setCellStyle(sourceCell.getCellStyle());cType = sourceCell.getCellType();targetCell.setCellType(cType);switch (cType) {case HSSFCell.CELL_TYPE_BOOLEAN:targetCell.setCellValue(sourceCell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_ERROR:targetCell.setCellErrorValue(sourceCell.getErrorCellValue());break;case HSSFCell.CELL_TYPE_FORMULA:targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));break;case HSSFCell.CELL_TYPE_NUMERIC:targetCell.setCellValue(sourceCell.getNumericCellValue());break;case HSSFCell.CELL_TYPE_STRING:targetCell.setCellValue(sourceCell.getRichStringCellValue());break;}}}    }    /** * @Description: 公式型数据处理 *  * @param pPOIFormula cellValue * @return */protected String parseFormula(String pPOIFormula) {        final String cstReplaceString = "ATTR(semiVolatile)";         StringBuffer result = null;        int index;        result = new StringBuffer();        index = pPOIFormula.indexOf(cstReplaceString);        if (index >= 0) {        result.append(pPOIFormula.substring(0, index));        result.append(pPOIFormula.substring(index + cstReplaceString.length()));        } else {        result.append(pPOIFormula);        }        return result.toString();}    /** * @Description: 设置打印区域 *  * @param wk 工作录 * @param sheetNum sheet页编号 */protected abstract void setPrintSetup(Workbook wk, int sheetNum);/*{Sheet sheet = wk.getSheetAt(sheetNum);PrintSetup ps = sheet.getPrintSetup();    ps.setPageStart((short) 0);    ps.setFitWidth((short) TEMPLATE_COLSPAN);    ps.setFitHeight((short) TEMPLATE_ROW);    //wk.setPrintArea(sheetNum, "$A$1:$G$" + (TEMPLATE_ROW * pageCount) );    wk.setPrintArea(sheetNum, 0, TEMPLATE_COLSPAN - 1, 0, TEMPLATE_ROW * pageCount - 1);}*/    /** * @Description: 设置该页的数据 *      * @param sheet sheet页 * @param page 页数 */protected abstract void setPageData(Sheet sheet, int page);/** * @Description: 生成输入文件流 *  * @return 文件流 * @throws Exception */public abstract InputStream getInputStream() throws Exception;       /** * @Description: 生成输入文件流 *  * @param fileData 数据 * @return 文件流 * @throws Exception */@SuppressWarnings({ "unchecked", "rawtypes" })public InputStream getInputStream(List fileData) throws Exception{this.setFileData(fileData);return getInputStream();}/** * @Description: 生成输出流 *  * @param os 输出流 * @return 文件流 * @throws Exception */public abstract OutputStream getOutputStream(OutputStream os) throws Exception;       /** * @Description: 生成输出流 *  * @param fileData 数据 * @param os 输出流 * @return 文件流 * @throws Exception */@SuppressWarnings({ "unchecked", "rawtypes" })public OutputStream getOutputStream(List fileData, OutputStream os) throws Exception{this.setFileData(fileData);return getOutputStream(os);}}

2.实现例子类：

import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.PrintSetup;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import wip.uhps.web.contractManage.model.Contract;public class testWriter extends ExcelWriter{/** * 文件类型excle2003,excle2007... */protected final String FILE_TYPE = "2007";/** * 文件模板 */protected final String FILE_TEMPLATE = "D:/uhpsFiles/U-home项目资金来源汇总.xlsx";/** * 文件模板 */protected final String FILE_TEMPLATE_DOWN = "D:/uhpsFiles/U-home项目资金支出汇总.xlsx";/** * 模板一页的列数 */protected final int TEMPLATE_COLSPAN = 7;/** * 模板一页的行数 */protected final int TEMPLATE_ROW = 53;/** * 模板一页的数据行数 */protected final int DATA_ROW = 49;/** * 模板一页的数据行数 */protected final int DATA_ROW_START = 4;/** * @Description: 取得工作录 *  * @param is 文件流 * @return 工作录 * @throws IOException */protected Workbook getWorkbook(InputStream is) throws IOException{Workbook wb = null;        if("2003".equals(FILE_TYPE)){//2003           wb = new HSSFWorkbook(is);           }else if("2007".equals(FILE_TYPE)){//2007           wb = new XSSFWorkbook(is);           }          return wb;}/** * @Description: 设置该页的数据 *      * @param sheet sheet页 * @param page 页数 */protected void setPageData(Sheet sheet, int page){int rowStart = (page - 1) * TEMPLATE_ROW + DATA_ROW_START - 1;int rowEnd = rowStart + DATA_ROW;int dateStart = (page - 1) * DATA_ROW;for(int i = rowStart, j = dateStart; i < rowEnd && j < dateCount; i++, j++){Row dataRow = sheet.getRow(i);Contract contract = (Contract)fileData.get(j);dataRow.getCell(0).setCellValue(contract.getProjectCode());dataRow.getCell(1).setCellValue(contract.getProjectName());dataRow.getCell(3).setCellValue(contract.getContractCode());dataRow.getCell(4).setCellValue(contract.getContractName());dataRow.getCell(6).setCellValue(contract.getContractMoney());}//页数设置Row pageRow = sheet.getRow((page - 1) * TEMPLATE_ROW + 52);pageRow.getCell(5).setCellValue(page + "/" + pageCount);//求和Row sumRow = sheet.getRow((page - 1) * TEMPLATE_ROW + 1);sumRow.getCell(3).setCellFormula("SUM(G" + (rowStart + 1) +":G"+ rowEnd +")");}/** * @Description: 设置打印区域 *  * @param wk 工作录 * @param sheetNum sheet页编号 */protected void setPrintSetup(Workbook wk, int sheetNum){Sheet sheet = wk.getSheetAt(sheetNum);PrintSetup ps = sheet.getPrintSetup();    ps.setPageStart((short) 0);    ps.setFitWidth((short) TEMPLATE_COLSPAN);    ps.setFitHeight((short) TEMPLATE_ROW);    //wk.setPrintArea(sheetNum, "$A$1:$G$" + (TEMPLATE_ROW * pageCount) );    wk.setPrintArea(sheetNum, 0, TEMPLATE_COLSPAN - 1, 0, TEMPLATE_ROW * pageCount - 1);}/** * @Description: 生成输入文件流 *  * @return 文件流 * @throws Exception */public InputStream getInputStream() throws Exception{return null;}/** * @Description: 生成输出流 *  * @param os 输出流 * @return 文件流 * @throws Exception */public OutputStream getOutputStream(OutputStream os) throws Exception {//取得工作录File file = new File(this.FILE_TEMPLATE);FileInputStream is = new FileInputStream(file);        Workbook wb  =  getWorkbook(is);        Sheet sheet = wb.getSheetAt(0);        //数据总数        dateCount = fileData.size();        //总页数        pageCount = (dateCount-1)/DATA_ROW + 1;        //复制页数        if(pageCount > 1){        List<CellRangeAddress> oldRanges =  getMergedRegion(sheet);        for (int i = 1; i < pageCount; i++) {        copyRows(sheet, oldRanges, 0, TEMPLATE_ROW - 1, TEMPLATE_ROW * i );}        }        //数据设置        for (int i = 1; i <= pageCount; i++) {    setPageData(sheet, i);}        //设置打印区域        setPrintSetup(wb, 0);                is.close();        wb.write(os);        return os;}

3：下载例子：

<pre name="code" class="java">import java.io.OutputStream;import java.net.URLEncoder;import java.util.ArrayList;import java.util.List;import wip.uhps.web.base.action.BusinessAction;import wip.uhps.web.contractManage.model.Contract;public class testAction extends BusinessAction {/** * *********************文件下载例子*************************************** * 用法： *   jsp提交:$("#testForm").attr("action","testDownload.do"); *        $("#testForm").submit(); *   struts配置：<action name="testDownload" class="testAction" method="testDownload"></action> */public void testDownload() throws Exception{String fileName = "asda号已存sd.xlsx";this.getResponse().reset();this.getResponse().setContentType("application/vnd.ms-excel");this.getResponse().setHeader("Content-Disposition", "attachment;   filename=" + URLEncoder.encode(fileName, "UTF-8"));List contracts = new ArrayList<Contract>();for (int i = 0; i < 49; i++) {Contract con = new Contract();con.setProjectCode("P0"+i);con.setProjectName("aa"+i);con.setContractCode("C0"+i);con.setContractName("bb"+i);con.setContractMoney(Double.valueOf(i));contracts.add(con);}for (int i = 0; i < 180; i++) {Contract con = new Contract();con.setProjectCode("P0"+i);con.setProjectName("aa"+i);con.setContractCode("C0"+i);con.setContractName("bb"+i);con.setContractMoney(Double.valueOf(i));contracts.add(con);}BudgetWriter bw = new BudgetWriter();OutputStream out = bw.getOutputStream(contracts, this.getResponse().getOutputStream());        out.flush();        out.close();}}