株洲网站建设哪家好,网站建设配色方案,网站特点,电脑搭建网站需要空间文章目录 Apache POI使用 EasyExcel工具类easyExcel工具类poi Apache POI
Apache POI 是基于 Office Open XML 标准#xff08; OOXML #xff09;和 Microsoft 的 OLE 2 复合⽂档 格式#xff08; OLE2 #xff09;处理各种⽂件格式的开源项⽬。 简⽽⾔之#xff0c;您可… 文章目录 Apache POI使用 EasyExcel工具类easyExcel工具类poi 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导入导出 --
dependencygroupIdorg.apache.poi/groupIdartifactIdpoi/artifactIdversion3.17-beta1/version
/dependency
dependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion3.17-beta1/version
/dependency
dependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml-schemas/artifactIdversion3.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;ListUploadRecord uploadRecordDtos null;ArrayListString 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) {PageUploadRecord uploadListPage this.getUploadListPage(k 1, 10000, structureName, monitorName, deviceName, state, startTime, endTime);uploadRecordDtos uploadListPage.getRecords();ListUploadRecordDto 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导入导出 --dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion2.0.5/version/dependencyInteger count uploadRecordService.getUploadListCount(structureName, monitorName, deviceName, state, startTime, endTime);int num 0;ListUploadRecordDto 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 localDateTimeLocalDateTime.now();//获取应该随机数避免文件名称一致int randomCode random.nextInt(899999) 100000;//文件名String fileNameString.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(sheetNamei);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,ListString excelClass,String summary){WriteSheet writeSheet new WriteSheet();//设置写到第几个sheet//设置表头ListListString 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(sheetNamei);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 ListListT splitList(ListT list, int len) {if (list null || list.size() 0 || len 1) {return null;}ListListT result new ArrayListListT();int size list.size();int count (size len - 1) / len;for (int i 0; i count; i) {ListT 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 ConverterList {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(,);ListString 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 ConverterLocalDateTime {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, MapString, MapString, 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);// 创建第 i1 行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, MapString, MapString, 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);// 创建第 i1 行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, MapString, MapString, 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, MapString, MapString, 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(ListListCell 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);ListCell 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(MapString, ListListCell vMap, String[] showName) {HSSFWorkbook workbook new HSSFWorkbook();for (Map.EntryString, ListListCell 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);ListCell 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,ArrayListString headerName, String[] resourceField, Class? resultObj,MapString, MapString, 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);}// 创建第 i1 行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, ArrayListString 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(ArrayListArrayListString resultList, String[] showName,ArrayListString 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) {ArrayListString 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, MapString, MapString, 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);// 创建第 i1 行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) {// 折合时间mincell.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;}}
