使用Apache POI数据导出及EasyExcel进行十万、百万的数据导出

Apache POI

Apache POI 是基于 Office Open XML 标准（ OOXML ）和 Microsoft 的 OLE 2 复合⽂档
格式（ OLE2 ）处理各种⽂件格式的开源项⽬。 简⽽⾔之，您可以使⽤ Java 读写
MS Excel ⽂件，可以使⽤ Java 读写 MS Word 和 MS PowerPoint ⽂件。

1.HSSF － 提供读写 Microsoft Excel XLS 格式 (Microsoft Excel 97 (-2003)) 档案的功
能。
2. XSSF － 提供读写 Microsoft Excel OOXML XLSX 格式 (Microsoft Excel XML (2007+))
档案的功能。
3.SXSSF － 提供低内存占⽤量读写 Microsoft Excel OOXML XLSX 格式档案的功能。
4.HWPF - 提供读写 Microsoft Word DOC97 格式 (Microsoft Word 97 (-2003)) 档案的
功能。
5.XWPF － 提供读写 Microsoft Word DOC2003 格式 (WordprocessingML (2007+)) 档
案的功能。
6.HSLF/XSLF － 提供读写 Microsoft PowerPoint 格式档案的功能。
7.HDGF/XDGF － 提供读 Microsoft Visio 格式档案的功能。
8.HPBF － 提供读 Microsoft Publisher 格式档案的功能。
9.HSMF － 提供读 Microsoft Outlook 格式档案的功能。

缺点：
java解析，生成Excel比较有名的框架有 POI ，JXL，但他们有一个严重的问题，就是 非常消耗内存， 也就是说数据量比较大的情况下有的时候会出现OOM（全称“Out Of Memory”，翻译成中文就是“内存用完了” ）的问题。

使用

导入依赖包：

 <!--EXCEL导入导出 -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17-beta1</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17-beta1</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.17-beta1</version>
</dependency>

注意：3.8版本才引入了SXSSF的使用
工具类：ExportExcel.java(放在文章结尾)

使用：

public ResponseResult reportUploadListPage(String structureName, String monitorName, String deviceName, Stringstate, String startTime, String endTime) {//查询数量int count = this.getUploadListCount(structureName, monitorName, deviceName, state, startTime, endTime);String[] showName = null;String[] resourceFild = null;List<UploadRecord> uploadRecordDtos = null;ArrayList<String> strings = new ArrayList<>();try {if (count > 200000) {return ResponseResult.failure("数据量过大！请控制在20万以内");}if (count > 0) {showName = new String[]{"监测项目名称", "监测时间", "水工建筑物", "测点名称(设备名称)","测点编号", "测值1说明", "测值1", "测值2说明", "测值2", "测值3说明", "测值3","状态", "上传时间"};resourceFild = new String[]{"getMonitorName", "getMonitorTime", "getStructureName", "getDeviceName","getDeviceName2", "getMonitorExplain1", "getMonitorValue1", "getMonitorExplain2", "getMonitorValue2", "getMonitorExplain3", "getMonitorValue3","getStateValue", "getUploadTime"};int num = 0;if (count % 10000 == 0) {num = count % 10000;} else {num = count / 10000 + 1;}for (int k = 0; k < num; k++) {Page<UploadRecord> uploadListPage = this.getUploadListPage(k + 1, 10000, structureName, monitorName, deviceName, state, startTime, endTime);uploadRecordDtos = uploadListPage.getRecords();List<UploadRecordDto> collect = uploadRecordDtos.stream().map(uploadRecord -> {UploadRecordDto uploadRecordDto = new UploadRecordDto();BeanUtil.copyProperties(uploadRecord, uploadRecordDto);if (uploadRecord.getState() == 0) {uploadRecordDto.setStateValue("未上传");} else {uploadRecordDto.setStateValue("已上传");}return uploadRecordDto;}).collect(Collectors.toList());Workbook workbook = ExportExcel.getWorkbookXlsx(collect, showName, resourceFild, UploadRecordDto.class,null);ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();workbook.write(byteArrayOutputStream);byte[] bytes = byteArrayOutputStream.toByteArray();MinioUploadDto minioUploadDto = minioService.upload("水利部导出数据" + (k + 1) + ".xlsx", bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");byteArrayOutputStream.flush();byteArrayOutputStream.close();strings.add(minioUploadDto.getUrl());}}} catch (Exception e) {e.printStackTrace();throw new BusinessException("数据导出异常");}return ResponseResult.success(strings);}

由于基本的XSSF单次只能导出65535，数据量有限而且极其容易报OOM的异常。测试的时候甚至2万条数据就OOM,当然和jvm的内存是有关的。后来使用SXSSF可以解决这个问题。

EasyExcel

EasyExcel 是⼀个基于 Java 的简单、省内存的读写 Excel 的开源项⽬。在尽可能节约内
存的情况下⽀持读写百 M 的 Excel 。
github 地址： https://github.com/alibaba/easyexcel
官⽅⽹站： https://easyexcel.opensource.alibaba.com/

使用：

<!--EXCEL导入导出 --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.0.5</version></dependency>

 Integer count = uploadRecordService.getUploadListCount(structureName, monitorName, deviceName, state, startTime, endTime);int num = 0;List<UploadRecordDto> uploadRecordDtos = new ArrayList<>(); //集合赋值的步骤已省略if (count > 300000) {return ResponseResult.failure("数据量过大，请控制在30万内！");}try {return ResponseResult.success(EasyExcelUtils.exportExcel(response, uploadRecordDtos, UploadRecordDto.class, "sheetName"));} catch (Exception e) {e.printStackTrace();return ResponseResult.failure("导出失败");}

UploadRecordDto.java中需要增加注解

@Data
public class UploadRecordDto implements Serializable {@ExcelIgnoreprivate static final long serialVersionUID = 2545145641431449276L;/*** 监测项目编码*/@ExcelIgnore     //导出时忽视该字段private String monitorCode;/*** 监测项目名称*/@ExcelProperty(value = "监测项目名称", index = 0)  //value 导出的标题    index为顺序索引private String monitorName;/*** 水工建筑物名称*/@ExcelProperty(value = "水工建筑物", index = 2)private String structureName;/*** 测点设备名*/@ExcelProperty(value = "测点名称(设备名称)", index = 3)private String deviceName;/*** 测点设备别名*/@ExcelProperty(value = "测点编号", index = 1)private String deviceName2;
}

EasyExcel的速度要比poi快很多，并且不会出现oom的异常。

工具类easyExcel

EasyExcelUtils.java

package com.youming.shuiku.upload.business.utils;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;/***** easyExcel工具类* 可用于文件的上传以及下载*/
@Slf4j
public class EasyExcelUtils {// 时间格式化private static final String DEFAULT_DATE_FORMAT = "yyyyMMddHHmm";static DateTimeFormatter formatter3 = DateTimeFormatter.ofPattern(DEFAULT_DATE_FORMAT);static  DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("yyyyMM");// 每个sheet的容量，超过100000就会把数据分给其他sheetprivate static final int PAGE_SIZE = 100000;//随机数public static Random random = new Random();/**** 文件的导出（使用之前需要传入生成excel的数据，以及当前数据的类别以及当前数据的实体类----可以使用注解生成表头的标题@ExcelProperty）* @param response* @param data 生成excel的数据* @param excelClass 当前数据的实体类，方便使用注解生成表头* @param <T>* @return* @throws Exception*/public static <T> String exportExcel(HttpServletResponse response, List<?> data,Class<?> excelClass,String sheetName) throws Exception{// 获取当前的时间LocalDateTime localDateTime=LocalDateTime.now();//获取应该随机数，避免文件名称一致int randomCode = random.nextInt(899999) + 100000;//文件名String fileName=String.format("%s%d.xlsx",formatter3.format(localDateTime),randomCode);//文件路径String newName = String.format("data-export/timescard/%s/%s", formatter2.format(localDateTime), fileName);// 把查询到的数据按设置的sheet的容量进行切割List<? extends List<?>> lists = splitList(data, PAGE_SIZE);// 设置响应头setHead(response, fileName);ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), excelClass).registerWriteHandler(formatExcel()).registerConverter(new ListConverter()).registerConverter(new LocalDateStringConverter()).registerWriteHandler(formatExcel()).build();ExcelWriterSheetBuilder excelWriterSheetBuilder;WriteSheet writeSheet;for (int i = 1; i <= lists.size(); ++i) {excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);excelWriterSheetBuilder.sheetNo(i).sheetName(sheetName+i);writeSheet = excelWriterSheetBuilder.build();excelWriter.write(lists.get(i - 1), writeSheet);}// 必须要finish才会写入，不finish只会创建empty的文件excelWriter.finish();return "导出成功";}/*** 写入数据* @param excelWriter excelWriter*/public static void writeData(ExcelWriter excelWriter,List<? extends List<?>> lists,List<String> excelClass,String summary){WriteSheet writeSheet = new WriteSheet();//设置写到第几个sheet//设置表头List<List<String>> headList = new ArrayList<>();String name = summary;for (String excelCla : excelClass) {headList.add(Arrays.asList(name,excelCla));}writeSheet.setHead(headList);for (int i = 1; i <= lists.size(); ++i) {writeSheet.setSheetNo(i);writeSheet.setSheetName("sheetName"+i);excelWriter.write(lists.get(i - 1), writeSheet);}}/*** 设置响应头* @param response 回应的请求数据* @param fileName 文件名字*/public static void setHead(HttpServletResponse response, String fileName) {response.setContentType("application/json");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码try {fileName = URLEncoder.encode(fileName, "UTF-8");} catch (UnsupportedEncodingException e) {e.printStackTrace();log.error("编码异常");}response.setHeader("Content-disposition", "attachment;filename=" + fileName);}/*** 设置Excel的格式** @return 格式化后的Excel*/public static HorizontalCellStyleStrategy formatExcel() {WriteCellStyle headWriteCellStyle = new WriteCellStyle();headWriteCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short) 10);headWriteCellStyle.setWriteFont(headWriteFont);// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();WriteFont contentWriteFont = new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints((short) 10);contentWriteCellStyle.setWriteFont(contentWriteFont);// 设置自动换行contentWriteCellStyle.setWrapped(false);// 设置垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}/*** 切割查询的数据* @param list 需要切割的数据* @param len 按照什么长度切割* @param <T>* @return*/public static <T> List<List<T>> splitList(List<T> list, int len) {if (list == null || list.size() == 0 || len < 1) {return null;}List<List<T>> result = new ArrayList<List<T>>();int size = list.size();int count = (size + len - 1) / len;for (int i = 0; i < count; i++) {List<T> subList = list.subList(i * len, (Math.min((i + 1) * len, size)));result.add(subList);}return result;}
}

ListConverter.java

package com.youming.shuiku.upload.business.utils;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;import java.util.ArrayList;
import java.util.List;public class ListConverter implements Converter<List> {@Overridepublic Class supportJavaTypeKey() {return List.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic List convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {String stringValue = cellData.getStringValue();String[] split = stringValue.split(",");List<String> enterpriseList = new ArrayList<>();for(int i = 0; i < split.length; i++){enterpriseList.add(split[i]);}return enterpriseList;}@Overridepublic CellData convertToExcelData(List list, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {StringBuilder stringBuilder = new StringBuilder();list.forEach(o -> {String s = o.toString();stringBuilder.append(s+",");});return new CellData(stringBuilder.toString());}
}

LocalDateStringConverter.java

package com.youming.shuiku.upload.business.utils;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;public class LocalDateStringConverter implements Converter<LocalDateTime> {@Overridepublic Class supportJavaTypeKey() {return LocalDateTime.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm"));}@Overridepublic CellData convertToExcelData(LocalDateTime localDateTime, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");String format = formatter.format(localDateTime);return new CellData(format);}
}

工具类poi

ExportExcel.java

//import org.apache.poi.hssf.usermodel.HSSFCellStyle;@SuppressWarnings("all")
public class ExportExcel implements Serializable {public static String getFileName() {return com.youming.shuiku.system.utils.excel.DateUtil.toString(new Date(), "yyyyMMdd-HHmmss");}@SuppressWarnings("deprecation")public static HSSFWorkbook getWorkbookXls(List<?> resultList, String[] showName, String[] resourceField,Class<?> resultObj, Map<String, Map<String, String>> formatMap) throws SecurityException,NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("sheet1");sheet.setDefaultColumnWidth((short) 20);HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中centerStyle.setAlignment(HorizontalAlignment.CENTER);centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框centerStyle.setBorderTop(BorderStyle.THIN);// 上边框centerStyle.setBorderRight(BorderStyle.THIN);// 右边框Font font = workbook.createFont();font.setBold(true); // 粗体centerStyle.setFont(font);HSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中contentStyle.setAlignment(HorizontalAlignment.CENTER);contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框contentStyle.setBorderTop(BorderStyle.THIN);// 上边框contentStyle.setBorderRight(BorderStyle.THIN);// 右边框HSSFRow row;HSSFCell cell;createTitleXls(showName, sheet, centerStyle);// 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的for (int i = 0; i < resultList.size(); i++) {Object result = resultList.get(i);row = sheet.createRow(i + 1);// 创建第 i+1 行for (int j = 0; j < resourceField.length; j++) {cell = row.createCell(j);// 创建第 j 列Method method;method = resultObj.getMethod(resourceField[j]);// 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象Object obj = method.invoke(result);if (obj != null) {if (formatMap != null && formatMap.containsKey(resourceField)) {cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));cell.setCellStyle(contentStyle);} else {String type = method.getGenericReturnType().toString();if ("class java.util.Date".equals(type)) {cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));cell.setCellStyle(contentStyle);} else if ("class java.time.LocalDateTime".equals(type)) {cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtils.formatLocalDateTime((LocalDateTime) obj,com.youming.shuiku.system.utils.excel.DateUtils.DATETIME_FORMAT));cell.setCellStyle(contentStyle);} else {cell.setCellValue(obj.toString());cell.setCellStyle(contentStyle);}}} else {cell.setCellStyle(contentStyle);}}}return workbook;}@SuppressWarnings("deprecation")public static XSSFWorkbook getWorkbookXlsx(List<?> resultList, String[] showName, String[] resourceField,Class<?> resultObj, Map<String, Map<String, String>> formatMap) throws SecurityException,NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("sheet1");sheet.setDefaultColumnWidth((short) 20);// 设置宽度XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中centerStyle.setAlignment(HorizontalAlignment.CENTER);centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框centerStyle.setBorderTop(BorderStyle.THIN);// 上边框centerStyle.setBorderRight(BorderStyle.THIN);// 右边框Font font = workbook.createFont();font.setBold(true); // 粗体centerStyle.setFont(font);XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中contentStyle.setAlignment(HorizontalAlignment.CENTER);contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框contentStyle.setBorderTop(BorderStyle.THIN);// 上边框contentStyle.setBorderRight(BorderStyle.THIN);// 右边框XSSFRow row;XSSFCell cell;createTitleXlsx(showName, sheet, centerStyle);// 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的for (int i = 0; i < resultList.size(); i++) {Object result = resultList.get(i);row = sheet.createRow(i + 1);// 创建第 i+1 行for (int j = 0; j < resourceField.length; j++) {cell = row.createCell(j);// 创建第 j 列Method method;method = resultObj.getMethod(resourceField[j]);// 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象Object obj = method.invoke(result);if (obj != null) {if (formatMap != null && formatMap.containsKey(resourceField)) {cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));cell.setCellStyle(contentStyle);} else {String type = method.getGenericReturnType().toString();if ("class java.util.Date".equals(type)) {cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));cell.setCellStyle(contentStyle);} else if ("class java.time.LocalDateTime".equals(type)) {cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtils.formatLocalDateTime((LocalDateTime) obj,com.youming.shuiku.system.utils.excel.DateUtils.DATETIME_FORMAT));cell.setCellStyle(contentStyle);} else {cell.setCellValue(obj.toString());cell.setCellStyle(contentStyle);}}} else {cell.setCellStyle(contentStyle);}}}return workbook;}@SuppressWarnings("deprecation")public static XSSFWorkbook getWorkbookXlsx(String[] showName, Map<String, Map<String, String>> formatMap)throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException,InvocationTargetException {XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("sheet1");sheet.setDefaultColumnWidth((short) 20);XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中centerStyle.setAlignment(HorizontalAlignment.CENTER);centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框centerStyle.setBorderTop(BorderStyle.THIN);// 上边框centerStyle.setBorderRight(BorderStyle.THIN);// 右边框Font font = workbook.createFont();font.setBold(true); // 粗体centerStyle.setFont(font);XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中contentStyle.setAlignment(HorizontalAlignment.CENTER);contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框contentStyle.setBorderTop(BorderStyle.THIN);// 上边框contentStyle.setBorderRight(BorderStyle.THIN);// 右边框XSSFRow row;XSSFCell cell;createTitleXlsx(showName, sheet, centerStyle);return workbook;}@SuppressWarnings("deprecation")public static XSSFWorkbook getWorkbookXlsxContract(String[] showName, Map<String, Map<String, String>> formatMap)throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException,InvocationTargetException {XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("sheet1");CellStyle textStyle = workbook.createCellStyle();DataFormat format = workbook.createDataFormat();textStyle.setDataFormat(format.getFormat("@"));for (int i = 0; i < showName.length; i++) {sheet.setDefaultColumnStyle(0, textStyle);}sheet.setDefaultColumnWidth((short) 20);XSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中centerStyle.setAlignment(HorizontalAlignment.CENTER);centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框centerStyle.setBorderTop(BorderStyle.THIN);// 上边框centerStyle.setBorderRight(BorderStyle.THIN);// 右边框Font font = workbook.createFont();font.setBold(true); // 粗体centerStyle.setFont(font);XSSFCellStyle contentStyle = workbook.createCellStyle();// 设置为水平居中contentStyle.setAlignment(HorizontalAlignment.CENTER);contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);contentStyle.setBorderBottom(BorderStyle.THIN); // 下边框contentStyle.setBorderLeft(BorderStyle.THIN);// 左边框contentStyle.setBorderTop(BorderStyle.THIN);// 上边框contentStyle.setBorderRight(BorderStyle.THIN);// 右边框XSSFRow row;XSSFCell cell;createTitleXlsx(showName, sheet, centerStyle);return workbook;}/*** 设置某些列的值只能输入预制的数据,显示下拉框.** @param sheet    要设置的sheet.* @param textlist 下拉框显示的内容* @param firstRow 开始行* @param endRow   结束行* @param firstCol 开始列* @param endCol   结束列* @return 设置好的sheet.*/public static XSSFSheet setXSSFValidation(XSSFSheet sheet, String[] textlist, int firstRow, int endRow,int firstCol, int endCol) {// 加载下拉列表内容DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);// 数据有效性对象HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);sheet.addValidationData(data_validation_list);return sheet;}private static void createTitleXls(String[] showName, HSSFSheet sheet, HSSFCellStyle cellStyle) {HSSFRow row = sheet.createRow(0); // 创建第 1 行，也就是输出表头 创建第HSSFCell cell;for (int i = 0; i < showName.length; i++) {cell = row.createCell(i);// 创建第 i 列 创建第cell.setCellValue(new HSSFRichTextString(showName[i]));cell.setCellStyle(cellStyle);}}private static void createTitleXlsx(String[] showName, XSSFSheet sheet, XSSFCellStyle cellStyle) {XSSFRow row = sheet.createRow(0); // 创建第 1 行，也就是输出表头 创建第XSSFCell cell;for (int i = 0; i < showName.length; i++) {cell = row.createCell(i);// 创建第 i 列 创建第cell.setCellValue(new XSSFRichTextString(showName[i]));cell.setCellStyle(cellStyle);}}private static void createTitleSXXlsx(String[] showName, SXSSFSheet sheet, CellStyle cellStyle) {SXSSFRow row = sheet.createRow(0);// 创建第 1 行，也就是输出表头 创建第SXSSFCell cell;for (int i = 0; i < showName.length; i++) {cell = row.createCell(i);// 创建第 i 列 创建第cell.setCellValue(new XSSFRichTextString(showName[i]));cell.setCellStyle(cellStyle);}}private static void createTitle2(String[] showName, HSSFSheet sheet, HSSFCellStyle centerStyle,HSSFCellStyle style) {HSSFRow row = sheet.createRow(3); // 创建第 1 行，也就是输出表头 创建第HSSFCell cell;for (int i = 0; i < showName.length; i++) {cell = row.createCell(i);// 创建第 i 列 创建第cell.setCellValue(new HSSFRichTextString(showName[i]));cell.setCellStyle(centerStyle); // 样式，居中cell.setCellStyle(style); // 填充亮橘色}}/*** @param @param  resultList* @param @param  showName* @param @return 设定文件* @return HSSFWorkbook 返回类型* @throws* @Title: createWorkbook* @Description: 创建Workbook*/@SuppressWarnings("deprecation")public static HSSFWorkbook createWorkbook(List<List<Cell>> resultList, String[] showName) {HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("sheet1");HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);centerStyle.setAlignment(HorizontalAlignment.CENTER);centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);createTitleXls(showName, sheet, centerStyle);HSSFRow row = null;HSSFCell cell = null;if (resultList.size() > 0) {int[][] arraSort = new int[resultList.get(0).size()][resultList.size()];for (int i = 0; i < resultList.size(); i++) {row = sheet.createRow(i + 1);// sheet.setColumnWidth(i + 1, 15);List<Cell> cellList = resultList.get(i);for (int j = 0; j < cellList.size(); j++) {cell = row.createCell(j);// 创建第 j 列cell.setCellValue(cellList.get(j).getValue());int b = cell.getStringCellValue().getBytes().length;arraSort[j][i] = b;if (cellList.get(j).getStyle() != null) {cell.setCellStyle(cellList.get(j).getStyle());}}}// 列的最大列宽值（不包括标题）int widthInfo[] = TwoMaxInfo(arraSort);// 与标题在比较列宽for (int i = 0; i < showName.length; i++) {// sheet.autoSizeColumn(i);// 算出列（包括标题的最大列宽）int maxWidthInfo = showName[i].getBytes().length > widthInfo[i] ? showName[i].getBytes().length: widthInfo[i];sheet.setColumnWidth(i, maxWidthInfo > 255 ? 255 * 256 : maxWidthInfo * 256);}}return workbook;}public static int[] TwoMaxInfo(int[][] arraSort) {int[] arraySortInfo = null;arraySortInfo = new int[arraSort.length];int count = 0;for (int[] is : arraSort) {int[] arraInfo = is;Arrays.sort(arraInfo);arraySortInfo[count] = arraInfo[arraInfo.length - 1];count++;}return arraySortInfo;}/*** @param @param  resultList* @param @param  showName* @param @return 设定文件* @return HSSFWorkbook 返回类型* @throws* @Title: createWorkbookAll* @Description: 创建Workbook* @author: 张燕* @date 2015-06-23 11:13:23 +0800*/@SuppressWarnings("deprecation")public static HSSFWorkbook createWorkbookAll(Map<String, List<List<Cell>>> vMap, String[] showName) {HSSFWorkbook workbook = new HSSFWorkbook();for (Map.Entry<String, List<List<Cell>>> entry : vMap.entrySet()) {HSSFSheet sheet = workbook.createSheet(entry.getKey());sheet.setDefaultColumnWidth((short) 15);HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);centerStyle.setAlignment(HorizontalAlignment.CENTER);centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);createTitleXls(showName, sheet, centerStyle);HSSFRow row;HSSFCell cell;for (int i = 0; i < entry.getValue().size(); i++) {row = sheet.createRow(i + 1);List<Cell> cellList = entry.getValue().get(i);for (int j = 0; j < cellList.size(); j++) {cell = row.createCell(j);// 创建第 j 列cell.setCellValue(cellList.get(j).getValue());if (cellList.get(j).getStyle() != null) {cell.setCellStyle(cellList.get(j).getStyle());}}}for (int i = 0; i < showName.length; i++) {sheet.autoSizeColumn(i);}}return workbook;}public static InputStream workbook2InputStreamXls(HSSFWorkbook workbook, String fileName) throws Exception {ByteArrayOutputStream baos = new ByteArrayOutputStream();workbook.write(baos);baos.flush();byte[] aa = baos.toByteArray();InputStream excelStream = new ByteArrayInputStream(aa, 0, aa.length);baos.close();return excelStream;}public static InputStream workbook2InputStreamXlsx(XSSFWorkbook workbook, String fileName) throws Exception {ByteArrayOutputStream baos = new ByteArrayOutputStream();workbook.write(baos);baos.flush();byte[] aa = baos.toByteArray();InputStream excelStream = new ByteArrayInputStream(aa, 0, aa.length);baos.close();return excelStream;}/*** @param @param  resultList 导出的数据集合* @param @param  showName 导出的字段名称* @param @param  headerName Excel表头参数* @param @param  resourceField 实例类对象get方法名，通过反射获取值* @param @param  resultObj 实例类* @param @param  formatMap* @param @return 返回workbook* @param @throws SecurityException* @param @throws NoSuchMethodException* @param @throws IllegalArgumentException* @param @throws IllegalAccessException* @param @throws InvocationTargetException 设定文件* @return HSSFWorkbook 返回类型* @throws* @Title: createWorkbookVariety* @Description: 导出Excel报表*/public static HSSFWorkbook createWorkbookVariety(List<?> resultList, String[] showName,ArrayList<String> headerName, String[] resourceField, Class<?> resultObj,Map<String, Map<String, String>> formatMap) throws SecurityException, NoSuchMethodException,IllegalArgumentException, IllegalAccessException, InvocationTargetException {HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("sheet1");sheet.setDefaultColumnWidth((short) 15);HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);centerStyle.setAlignment(HorizontalAlignment.CENTER);centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);/*** 设置表头的样式*/HSSFCellStyle titylStyle = workbook.createCellStyle();createTitleVariety(showName, headerName, sheet, titylStyle);HSSFRow row;HSSFCell cell;for (int i = 0; i < resultList.size(); i++) {Object result = resultList.get(i);if (headerName != null && headerName.size() > 0) {row = sheet.createRow(i + 1 + headerName.size());} else {row = sheet.createRow(i + 1);}// 创建第 i+1 行for (int j = 0; j <= resourceField.length; j++) {cell = row.createCell(j);// 创建第 j 列cell.setCellStyle(centerStyle);if (j == 0) {// 为Excel表的第一列添加编号，表头为：序号；eg:1,2,3,4……cell.setCellValue(i + 1);} else {Method method;method = resultObj.getMethod(resourceField[j - 1]);// 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象Object obj = method.invoke(result);if (obj != null) {if (formatMap != null && formatMap.containsKey(resourceField)) {cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));} else {String type = method.getGenericReturnType().toString();if ("class java.util.Date".equals(type)) {cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));} else {cell.setCellValue(obj.toString());}}}}}}return workbook;}/*** @param @param showName* @param @param headerName* @param @param sheet 设定文件* @return void 返回类型* @throws* @Title: createTitleVariety* @Description: 多行表头*/private static void createTitleVariety(String[] showName, ArrayList<String> headerName, HSSFSheet sheet,HSSFCellStyle titylStyle) {HSSFRow row;HSSFCell cell;// titylStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// titylStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);titylStyle.setAlignment(HorizontalAlignment.CENTER);titylStyle.setVerticalAlignment(VerticalAlignment.CENTER);if (headerName != null && headerName.size() > 0) {for (int i = 0; i < headerName.size(); i++) {row = sheet.createRow((short) i);if (i == 0) {cell = row.createCell(i);sheet.addMergedRegion(new CellRangeAddress(i, i, (short) 0, (short) showName.length));cell.setCellStyle(titylStyle);if (headerName.get(i) != null) {cell.setCellValue(new HSSFRichTextString(headerName.get(i).toString()));} else {cell.setCellValue(new HSSFRichTextString(""));}} else {cell = row.createCell(i - 1);sheet.addMergedRegion(new CellRangeAddress(i, i, (short) 0, (short) showName.length));if (headerName.get(i) != null) {cell.setCellValue(new HSSFRichTextString(headerName.get(i).toString()));} else {cell.setCellValue(new HSSFRichTextString(""));}}}}// 设置Excel字段if (headerName != null && headerName.size() > 0) {row = sheet.createRow((short) headerName.size());} else {row = sheet.createRow(0);}for (int n = 0; n <= showName.length; n++) {if (n == 0) {cell = row.createCell(n);cell.setCellStyle(titylStyle);cell.setCellValue(new HSSFRichTextString("序号"));} else {cell = row.createCell(n);cell.setCellStyle(titylStyle);cell.setCellValue(new HSSFRichTextString(showName[n - 1]));}}}public static HSSFWorkbook createWorkbookVarietyParam(ArrayList<ArrayList<String>> resultList, String[] showName,ArrayList<String> headerName) {HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("sheet1");sheet.setDefaultColumnWidth((short) 15);HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);centerStyle.setAlignment(HorizontalAlignment.CENTER);centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);/*** 设置表头的样式*/HSSFCellStyle titylStyle = workbook.createCellStyle();createTitleVariety(showName, headerName, sheet, titylStyle);HSSFRow row;HSSFCell cell;if (resultList != null && resultList.size() > 0) {for (int i = 0; i < resultList.size(); i++) {ArrayList<String> rowResultList = resultList.get(i);if (headerName != null && headerName.size() > 0) {row = sheet.createRow((short) (i + 1 + headerName.size()));} else {row = sheet.createRow((short) (i + 1));}if (rowResultList != null && rowResultList.size() > 0) {for (int n = 0; n <= rowResultList.size(); n++) {cell = row.createCell(n);// 创建第 j 列cell.setCellStyle(centerStyle);if (n == 0) {// 为Excel表的第一列添加编号，表头为：序号；eg:1,2,3,4……cell.setCellValue(i + 1);} else if (rowResultList.get(n - 1) != null) {cell.setCellValue(rowResultList.get(n - 1).toString());} else {cell.setCellValue("");}}}}}return workbook;}/*** @param @param  resultList* @param @param  headList* @param @param  sumList* @param @param  showName* @param @param  resourceField* @param @param  resultObj* @param @param  formatMap* @param @return* @param @throws SecurityException* @param @throws NoSuchMethodException* @param @throws IllegalArgumentException* @param @throws IllegalAccessException* @param @throws InvocationTargetException 设定文件* @return HSSFWorkbook 返回类型* @throws* @Title: getWorkbook2*/@SuppressWarnings("deprecation")public static HSSFWorkbook getWorkbook2(List<?> resultList, List<?> headList, List<?> sumList, String[] showName,String[] resourceField, Class<?> resultObj, Map<String, Map<String, String>> formatMap)throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException,InvocationTargetException {HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("sheet1");sheet.setDefaultColumnWidth((short) 20);HSSFCellStyle centerStyle = workbook.createCellStyle();// 设置为水平居中// centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// centerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);centerStyle.setAlignment(HorizontalAlignment.CENTER);centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);centerStyle.setBorderBottom(BorderStyle.THIN); // 下边框centerStyle.setBorderLeft(BorderStyle.THIN);// 左边框centerStyle.setBorderTop(BorderStyle.THIN);// 上边框centerStyle.setBorderRight(BorderStyle.THIN);// 右边框HSSFDataFormat format = workbook.createDataFormat();// 这样才能真正的控制单元格格式，@就是指文本型centerStyle.setDataFormat(format.getFormat("@"));HSSFCellStyle style = workbook.createCellStyle();// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setBorderBottom(BorderStyle.THIN); // 下边框style.setBorderLeft(BorderStyle.THIN);// 左边框style.setBorderTop(BorderStyle.THIN);// 上边框style.setBorderRight(BorderStyle.THIN);// 右边框style.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_ORANGE.getIndex());// 填亮橘色HSSFCellStyle greenStyle = workbook.createCellStyle();// greenStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// greenStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);greenStyle.setAlignment(HorizontalAlignment.CENTER);greenStyle.setVerticalAlignment(VerticalAlignment.CENTER);greenStyle.setBorderBottom(BorderStyle.THIN); // 下边框greenStyle.setBorderLeft(BorderStyle.THIN);// 左边框greenStyle.setBorderTop(BorderStyle.THIN);// 上边框greenStyle.setBorderRight(BorderStyle.THIN);// 右边框greenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格// greenStyle.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);//填亮绿色greenStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());// 填深绿色Font greenfont = workbook.createFont();greenfont.setBold(true); // 粗体greenStyle.setFont(greenfont);HSSFCellStyle overGreenStyle = workbook.createCellStyle();// overGreenStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// overGreenStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);overGreenStyle.setAlignment(HorizontalAlignment.CENTER);overGreenStyle.setVerticalAlignment(VerticalAlignment.CENTER);overGreenStyle.setBorderBottom(BorderStyle.THIN); // 下边框overGreenStyle.setBorderLeft(BorderStyle.THIN);// 左边框overGreenStyle.setBorderTop(BorderStyle.THIN);// 上边框overGreenStyle.setBorderRight(BorderStyle.THIN);// 右边框overGreenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格overGreenStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());// 填深绿色HSSFCellStyle fontStyle = workbook.createCellStyle();// 字体样式fontStyle.setAlignment(HorizontalAlignment.CENTER);fontStyle.setVerticalAlignment(VerticalAlignment.CENTER);fontStyle.setBorderBottom(BorderStyle.THIN); // 下边框fontStyle.setBorderLeft(BorderStyle.THIN);// 左边框fontStyle.setBorderTop(BorderStyle.THIN);// 上边框fontStyle.setBorderRight(BorderStyle.THIN);// 右边框fontStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 填充单元格fontStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SEA_GREEN.getIndex());// 填深绿色Font font = workbook.createFont();font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()); // 白字fontStyle.setFont(font);HSSFRow row;HSSFCell cell;// createTitle2(showName, sheet, centerStyle, style);for (int j = 0; j < 3; j++) {row = sheet.createRow(j);for (int i = 0; i < showName.length; i++) {cell = row.createCell(i);if (j == 0) {if (i == 0) {cell.setCellValue(new HSSFRichTextString("查询时间"));cell.setCellStyle(greenStyle);} else if (i == 1) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(j, j, (short) i, (short) (i + 3));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString(headList.get(0).toString()));cell.setCellStyle(fontStyle);}} else if (j == 1) {if (i == 0) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(j, (j + 1), (short) 0, (short) 0);sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("车辆信息"));cell.setCellStyle(greenStyle);} else if (i == 1) {cell.setCellValue(new HSSFRichTextString("车牌号"));} else if (i == 2) {cell.setCellValue(new HSSFRichTextString("所属公司"));} else if (i == 3) {cell.setCellValue(new HSSFRichTextString("车辆类型"));} else if (i == 4) {cell.setCellValue(new HSSFRichTextString("入网时间"));} else if (i == 5) {cell.setCellValue(new HSSFRichTextString("车身颜色"));} else if (i == 6) {cell.setCellValue(new HSSFRichTextString("型号"));} else if (i == 7) {cell.setCellValue(new HSSFRichTextString("司机"));} else if (i == 8) {cell.setCellValue(new HSSFRichTextString("手机号"));}if (i > 0 && i < 9) {cell.setCellStyle(overGreenStyle);}} else if (j == 2) {if (i == 1) {cell.setCellValue(new HSSFRichTextString(headList.get(1).toString()));} else if (i == 2) {cell.setCellValue(new HSSFRichTextString(headList.get(2).toString()));} else if (i == 3) {cell.setCellValue(new HSSFRichTextString(headList.get(3).toString()));} else if (i == 4) {cell.setCellValue(new HSSFRichTextString(headList.get(4).toString()));} else if (i == 5) {cell.setCellValue(new HSSFRichTextString(headList.get(5).toString()));} else if (i == 6) {cell.setCellValue(new HSSFRichTextString(headList.get(6).toString()));} else if (i == 7) {cell.setCellValue(new HSSFRichTextString(headList.get(7).toString()));} else if (i == 8) {cell.setCellValue(new HSSFRichTextString(headList.get(8).toString()));}if (i > 0 && i < 9) {cell.setCellStyle(fontStyle);}}}}for (int k = 0; k < 3; k++) {row = sheet.createRow(k + 3);for (int i = 0; i < showName.length; i++) {cell = row.createCell(i);if (k == 0) {if (i == 0) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i + 1));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("时间区间"));} else if (i == 2) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i + 1));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("时长"));} else if (i == 4) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("位置信息"));} else if (i == 5) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("车辆状态"));} else if (i == 6) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("行驶里程（km）"));} else if (i == 7) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("超速（次数）"));} else if (i == 8) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 3, (short) (i), (short) (i + 3));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("疲劳驾驶"));} else if (i == 12) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("急加速（次数）"));} else if (i == 13) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("急减速（次数）"));} else if (i == 14) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("急转弯（次数）"));} else if (i == 15) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("平均速度（km/h）"));} else if (i == 16) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 5, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("最高速度（km/h）"));}} else if (k == 1) {if (i == 8) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("次数"));} else if (i == 9) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 3, (short) (i), (short) (i + 1));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("时长"));} else if (i == 11) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(k + 3, k + 4, (short) (i), (short) (i));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("里程（km）"));}} else if (k == 2) {if (i == 0) {cell.setCellValue(new HSSFRichTextString("开始时间"));} else if (i == 1) {cell.setCellValue(new HSSFRichTextString("结束时间"));} else if (i == 2) {cell.setCellValue(new HSSFRichTextString("小时"));} else if (i == 3) {cell.setCellValue(new HSSFRichTextString("分钟"));} else if (i == 4) {cell.setCellValue(new HSSFRichTextString("详细地址"));} else if (i == 5) {cell.setCellValue(new HSSFRichTextString("状态"));} else if (i == 9) {cell.setCellValue(new HSSFRichTextString("小时"));} else if (i == 10) {cell.setCellValue(new HSSFRichTextString("分钟"));}}cell.setCellStyle(style); // 填充亮橘色}}// 下面是输出各行的数据 下面是输出各行的数据 下面是输出各行的for (int i = 0; i < resultList.size(); i++) {Object result = resultList.get(i);row = sheet.createRow(i + 6);// 创建第 i+1 行for (int j = 0; j < resourceField.length; j++) {cell = row.createCell(j);// 创建第 j 列Method method;method = resultObj.getMethod(resourceField[j]);// 这里用到了反射机制，通 这里用到了反射机制， 这里用到了反射机制 过方法名来取得对应方法返回的结果对象Object obj = method.invoke(result);if (obj != null) {if (formatMap != null && formatMap.containsKey(resourceField)) {cell.setCellValue(formatMap.get(resourceField).get(obj.toString()));cell.setCellStyle(centerStyle); // 样式，居中} else {String type = method.getGenericReturnType().toString();if ("class java.util.Date".equals(type)) {cell.setCellValue(com.youming.shuiku.system.utils.excel.DateUtil.toString((Date) obj,com.youming.shuiku.system.utils.excel.DateUtil.DEFAULT_DATETIME_FORMAT_SEC));} else {cell.setCellValue(obj.toString());}cell.setCellStyle(centerStyle); // 样式，居中}} else {cell.setCellStyle(centerStyle); // 样式，居中}}}// 参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列// CellRangeAddress region1 = new CellRangeAddress(showName.length,// showName.length, (short) 0, (short) 11);row = sheet.createRow(resultList.size() + 6);for (int i = 0; i < showName.length; i++) {cell = row.createCell(i);if (i == 0) {// 参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号CellRangeAddress region1 = new CellRangeAddress(resultList.size() + 6, resultList.size() + 6,(short) (i), (short) (i + 1));sheet.addMergedRegion(region1);cell.setCellValue(new HSSFRichTextString("总计"));cell.setCellStyle(greenStyle);} else if (i == 2) {cell.setCellValue(new HSSFRichTextString(sumList.get(0).toString()));cell.setCellStyle(fontStyle);} else if (i == 3) {cell.setCellValue(new HSSFRichTextString(sumList.get(1).toString()));cell.setCellStyle(fontStyle);} else if (i == 4) {// 折合时间（min）cell.setCellValue(new HSSFRichTextString("折合时间（min）"));cell.setCellStyle(greenStyle);} else if (i == 5) {cell.setCellValue(new HSSFRichTextString(sumList.get(2).toString()));cell.setCellStyle(fontStyle);} else if (i == 6) {cell.setCellValue(new HSSFRichTextString(sumList.get(3).toString()));cell.setCellStyle(fontStyle);} else if (i == 7) {cell.setCellValue(new HSSFRichTextString(sumList.get(4).toString()));cell.setCellStyle(fontStyle);} else if (i == 8) {cell.setCellValue(new HSSFRichTextString(sumList.get(5).toString()));cell.setCellStyle(fontStyle);} else if (i == 9) {cell.setCellValue(new HSSFRichTextString(sumList.get(6).toString()));cell.setCellStyle(fontStyle);} else if (i == 10) {cell.setCellValue(new HSSFRichTextString(sumList.get(7).toString()));cell.setCellStyle(fontStyle);} else if (i == 11) {cell.setCellValue(new HSSFRichTextString(sumList.get(8).toString()));cell.setCellStyle(fontStyle);} else if (i == 12) {cell.setCellValue(new HSSFRichTextString(sumList.get(9).toString()));cell.setCellStyle(fontStyle);} else if (i == 13) {cell.setCellValue(new HSSFRichTextString(sumList.get(10).toString()));cell.setCellStyle(fontStyle);} else if (i == 14) {cell.setCellValue(new HSSFRichTextString(sumList.get(11).toString()));cell.setCellStyle(fontStyle);} else if (i == 15) {cell.setCellValue(new HSSFRichTextString(sumList.get(12).toString()));cell.setCellStyle(fontStyle);} else if (i == 16) {cell.setCellValue(new HSSFRichTextString(sumList.get(13).toString()));cell.setCellStyle(fontStyle);}}return workbook;}}