Java：表单生成excel文档 poi 通用

在用java 写数据库应用的时候, 通常会生成各种报表，而这些报表可能会被导出为各种格式的文件，比如Excel文档，pdf 文档等等. 今天先做了一个生成Excel 文档的例子，主要解决以下问题：

1. 生成 Excel 文档.
2. 自动对生成的Excel 文档第一行标题栏设置成filter 过滤形式, 方便用户使用.

导入依赖： 

<!--依赖的jar包-->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version>
</dependency>

 Excel转换通用类：

package com.kingbal.king.dmp;import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;/*** <b>Function: </b> todo** @program: ExcelUtil* @Package: com.kingbal.king.dmp* @author: dingcho* @date: 2023/12/30* @version: 1.0* @Copyright: 2023 www.kingbal.com Inc. All rights reserved.*/
public class ExcelUtil<T> {public HSSFCellStyle getCellStyle(HSSFWorkbook workbook, boolean isHeader) {HSSFCellStyle style = workbook.createCellStyle();style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);style.setBorderRight(HSSFCellStyle.BORDER_THIN);style.setBorderTop(HSSFCellStyle.BORDER_THIN);style.setLocked(true);if (isHeader) {style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);HSSFFont font = workbook.createFont();font.setColor(HSSFColor.BLACK.index);font.setFontHeightInPoints((short) 12);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);style.setFont(font);}return style;}public void generateHeader(HSSFWorkbook workbook, HSSFSheet sheet, String[] headerColumns) {HSSFCellStyle style = getCellStyle(workbook, true);Row row = sheet.createRow(0);row.setHeightInPoints(30);for (int i = 0; i < headerColumns.length; i++) {Cell cell = row.createCell(i);String[] column = headerColumns[i].split("_#_");sheet.setColumnWidth(i, Integer.valueOf(column[1]));cell.setCellValue(column[0]);cell.setCellStyle(style);}}@SuppressWarnings({"rawtypes", "unchecked"})public HSSFSheet creatAuditSheet(HSSFWorkbook workbook, String sheetName,List<T> dataset, String[] headerColumns, String[] fieldColumns)throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {HSSFSheet sheet = workbook.createSheet(sheetName);sheet.protectSheet("");generateHeader(workbook, sheet, headerColumns);HSSFCellStyle style = getCellStyle(workbook, false);SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");int rowNum = 0;for (T t : dataset) {rowNum++;Row row = sheet.createRow(rowNum);row.setHeightInPoints(25);for (int i = 0; i < fieldColumns.length; i++) {String fieldName = fieldColumns[i];String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);try {Class clazz = t.getClass();Method getMethod;getMethod = clazz.getMethod(getMethodName, new Class[]{});Object value = getMethod.invoke(t, new Object[]{});String cellValue = "";// 对于某些特定类型进行转换if (value instanceof Date) {Date date = (Date) value;cellValue = sd.format(date);} else {cellValue = null != value ? value.toString() : "";}Cell cell = row.createCell(i);cell.setCellStyle(style);cell.setCellValue(cellValue);} catch (Exception e) {}}}return sheet;}}

根据需求调用：

public static void main(String[] args) {Map<String, Object> param = Maps.newHashMap();param.put( "callType", "CALL_IN" );// 获取您对应查询的字段List<Cdr> cdrList = cdrService.queryListByParam(param);String [] cdrInColumns = new String[]{"呼入时间_#_3000" ,"挂断时间_#_3000" ,"座席工号_#_3000" ,"座席姓名_#_3000" ,"呼入时长（秒）_#_3000" ,"IVR时长（秒）_#_3000" ,"排队时长（秒）_#_3000" ,"通话时长（秒）_#_3000" ,"归属地_#_3000" ,"满意度调查_#_3000"};String [] cdrInFields = new String []{"callTime","hangupTime" ,"src" ,"workNo" ,"trueName" ,"totalLength" ,"ivrLength" ,"queueLength","datlLength" ,"city" ,"ivrKey"};HSSFWorkbook workbook = new HSSFWorkbook();ExcelUtil<Cdr> cdrSheet = new ExcelUtil<Cdr>();cdrSheet.creatAuditSheet(workbook, "呼出信息" , cdrList, cdrInColumns, cdrInFields);ByteArrayOutputStream os = new ByteArrayOutputStream();workbook.write(os);InputStream inputStream = new ByteArrayInputStream(os.toByteArray());// 获取文件长度 inputStream.available()}
}