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

最新推荐文章于 2022-12-17 01:15:00 发布

落叶上的秋

最新推荐文章于 2022-12-17 01:15:00 发布

阅读量791

点赞数

分类专栏： poi

本文链接：https://blog.csdn.net/huanglgln/article/details/41013789

版权

poi 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

网上的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();
	}
	
}