根据excel模板导出excel

最新推荐文章于 2024-11-08 09:31:20 发布

原创最新推荐文章于 2024-11-08 09:31:20 发布 · 3.2k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

java

本文介绍如何通过编程方式，根据预设的Excel模板导出数据，实现自动化数据处理和报告生成。

1. 代码

package com.base.pf.common.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.base.pf.base.util.StringUtils;

/**
 * 通用导出EXCEL类
 * 
 * @author ZHEN.L
 * @date 2014.09.04
 * 
 */
public class ExcelUtils {

	/**
	 * EXCEL导出标记
	 */
	public static final String EXCEL_EXP_TAG = "Excel.exp";

	/**
	 * Excel导出标记值
	 */
	public static final String EXCEL_EXP_TAG_VALUE = "excel";

	/**
	 * 导出EXCLE名称
	 */
	public static final String EXCEL_FILE_NAME = "Excel.fileName";
	/**
	 * 导出excle模板名称
	 */
	public static final String EXCEL_TEMPLATE_NAME = "Excel.templateName";
	/**
	 * 导出字段：有顺序，以逗号隔开(注：字段名称要和dto属性名称或者map中的key值相同)
	 */
	public static final String EXCEL_FIELD = "Excel.field";
	/**
	 * 在EXCEL中，开始写记录的行号；从0开始计数
	 */
	public static final String EXCEL_START_ROW_NAME = "Excel.startRow";

	// 导出excel模板的相对路径
	protected static final String EXCEL_TEMPLATE_PATH = "\\page\\project\\template_excel\\";

	/**
	 * 导出EXCEL
	 * 
	 * @param response
	 * @param request
	 * @param list
	 *            需要导出的集合:集合中可以为map，也可以为Dto
	 * @return
	 */
	public static String exp(HttpServletResponse response,
			HttpServletRequest request, List<Object> list) {
		String templatePath = request.getServletContext().getRealPath("/")
				+ EXCEL_TEMPLATE_PATH;
		String fileName = request.getParameter(EXCEL_FILE_NAME);
		String templateName = request.getParameter(EXCEL_TEMPLATE_NAME);
		String field = request.getParameter(EXCEL_FIELD);
		String startRowStr = request.getParameter(EXCEL_START_ROW_NAME);
		int startRow = 2; // 开始写入excel的开始行号
		if(StringUtils.isNumeric(startRowStr)){
			startRow = Integer.parseInt(startRowStr);
		}
		if ("".equals(StringUtils.withSpaceReplaceNull(templateName)))
			throw new NullPointerException("模板路径不能为空!");
		if ("".equals(StringUtils.withSpaceReplaceNull(field)))
			throw new NullPointerException("导出字段不能为空!");
		String path = templatePath + templateName;
		String[] fields = field.split(",");
		fileName = StringUtils.withSpaceReplaceNull(fileName);
		String date = new SimpleDateFormat("yyyyMMdd").format(new Date());
		fileName += "_" + date;
		OutputStream os = null;
		InputStream is = null;
		try {
			is = new FileInputStream(path);
			Workbook work = new HSSFWorkbook(is);
			// 得到excel的第0张表
			Sheet sheet = work.getSheetAt(0);
			// 得到第1行的第一个单元格的样式
			CellStyle cellStyle = createBorderedStyle(work);
			Row row = null;
			Cell cell = null;
			Object rDto = null;
			Map map = null;
			// 得到行，并填充数据和表格样式
			for (int i = 0; i < list.size(); i++) {
				rDto = list.get(i);
				if (rDto instanceof Map)
					map = (Map) rDto;
				else
					map = BeanToMapUtils.convertBean(rDto);
				row = sheet.createRow(startRow++);
				row.setHeight((short)350);
				for (int j = 0; j < fields.length + 1; j++) {
					cell = row.createCell(j);
					if (j == 0)
						cell.setCellValue(i + 1);
					else
						cell.setCellValue(StringUtils.withSpaceReplaceNull(map
								.get(fields[j - 1])));
					cell.setCellStyle(cellStyle);// 填充样式
				}
			}
			// 输出工作簿: 这里使用的是 response 的输出流，如果将该输出流换为普通的文件输出流则可以将生成的文档写入磁盘等
			os = response.getOutputStream();
			response.setContentType("application/ms-excel,charset=gbk");
			response.setHeader("Content-disposition", "attachment;filename="
					+ URLEncoder.encode(fileName + ".xls", "utf-8"));
			work.write(os); // 将工作簿进行输出
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			close(is, os);
		}
		return null;
	}

	// 单元格样式
	protected static CellStyle createBorderedStyle(Workbook wb) {
		CellStyle style = wb.createCellStyle();
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		return style;
	}

	// 单元格样式:获取的第二行的第一个单元格的样式
	protected static CellStyle createGetCellStyle(Workbook work) {
		Sheet sheet = work.getSheetAt(0);
		Row rowCellStyle = sheet.getRow(1);
		CellStyle cellStyle = rowCellStyle.getCell(0).getCellStyle();
		return cellStyle;
	}

	// 关闭IO
	protected static void close(InputStream is, OutputStream os) {
		if (is != null) {
			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		if (os != null) {
			try {
				os.flush();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}

}

2. 在查询列表方法中加入代码

	if (ExcelUtils.EXCEL_EXP_TAG_VALUE.equalsIgnoreCase(request
					.getParameter(ExcelUtils.EXCEL_EXP_TAG))) {
				ExcelUtils.exp(response, request, rows);
				return null;
			}

3. 导出页面中加入

<!-- 导出EXCEL -->
<div><form id="ExcelForm" name="ExcelForm" method="post" action="queryList.do"> <!-- 查询路径 -->
	<input type="text" name="excelProjectName" id="excelProjectName"/>
	<input type="text" name="excelYear" id="excelYear"/>
	<input type="text" name="execelProjectType" id="execelProjectType"/>
	<input type="text" name="excelOrgId" id="excelOrgId"/>
	<input type="hidden" name="<%=com.base.pf.common.util.ExcelUtils.EXCEL_EXP_TAG %>" value="<%=com.base.pf.common.util.ExcelUtils.EXCEL_EXP_TAG_VALUE %>"/><!-- 用于判断是否为导出功能，无需修改 -->
	<input type="hidden" name="<%=com.base.pf.common.util.ExcelUtils.EXCEL_FIELD %>" value="projectName,contractName,period,replyDateCn,valuationAmount,invoiceCode,invoiceAmount,invoiceDateCn,deductPaymentAmount,deductBondAmount,deductOtherBondAmount,deductRewardAmount,deductFineAmount,deductOtherAmount,receivableAmount,unpaidAmount,toDateCn,toAmount"/> <!-- 要导出的字段 -->
	<input type="hidden" name="<%=com.base.pf.common.util.ExcelUtils.EXCEL_TEMPLATE_NAME %>" value="valuationReport.xls"/> <!-- 模板名称：模板放在page/project/excel_template下 -->
	<input type="hidden" name="<%=com.base.pf.common.util.ExcelUtils.EXCEL_FILE_NAME %>" value="项目计价收款情况表"/> <!-- 导出的EXCLE名称（注：系统会自动为名称后加上当前日期） -->
	<input type="hidden" name="<%=com.base.pf.common.util.ExcelUtils.EXCEL_START_ROW_NAME %>" value="4"/> <!-- 导出的EXCLE中开始记录的行号：从0开始计算-->
</form></div>

<script type="text/javascript"/>
ValuationReportManage.excelForm = $('#ExcelForm');
ValuationReportManage.expExcel = function(){
	var projectName = $("#projectName").val();
	if(projectName){
		$("#excelProjectName").val(projectName);
	}	
	var year = $("#year").val();
	if(year){
		$("#excelYear").val(year);
	}	
	var projectType = $("#projectType").val();
	if(projectType){
		$("#execelProjectType").val(projectType);
	}	
	var orgSelectText = $("#orgSelectText").val();
	if(orgSelectText){
		$("#excelOrgId").val(orgSelectText);
	}	
	$('#ExcelForm').submit();
};
</script>