用ssh做的网站,自助网站建设工具,网站点击量在哪里看,大气网站源码下载目录 工具类
头部实体类#xff08;要和工具类在同一个module或项目下#xff09;
日期转换器 工具类
/*** 导出Excel工具类*/
public class EasyExcelUtilT {/*** 单sheet#xff08;Map写入#xff09;* param response 响应对象* param headList 头部集合* p…目录 工具类
头部实体类要和工具类在同一个module或项目下
日期转换器 工具类
/*** 导出Excel工具类*/
public class EasyExcelUtilT {/*** 单sheetMap写入* param response 响应对象* param headList 头部集合* param dataList 数据集合*/public static void write(HttpServletResponse response, ListExcelHead headList, ListMapString, Object dataList) throws IOException {ExcelWriterBuilder writerBuilder EasyExcel.write();writerBuilder.file(response.getOutputStream());writerBuilder.excelType(ExcelTypeEnum.XLSX);//日期转换器TimestampStringConverter converter new TimestampStringConverter();writerBuilder.registerConverter(converter).registerWriteHandler(new ColumnWidthStyleStrategy()).head(convertHead(headList)).sheet(sheet1).doWrite(convertData(headList, dataList));}/*** 多sheetMap写入* param response 响应对象* param headMap 头部Map数据* param dataMap 数据Map数据* param sheetMap sheet Map数据*/public static void multipleWrite(HttpServletResponse response, MapString,ListExcelHead headMap, MapString,ListMapString, Object dataMap, MapString,String sheetMap) throws IOException {//日期转换器TimestampStringConverter converter new TimestampStringConverter();ExcelWriter excelWriter EasyExcel.write().registerConverter(converter).registerWriteHandler(new ColumnWidthStyleStrategy()).file(response.getOutputStream()).excelType(ExcelTypeEnum.XLSX).autoCloseStream(true).build();int i 0;for (Map.EntryString,ListExcelHead entry : headMap.entrySet()) {WriteSheet writeSheet EasyExcel.writerSheet(i, sheetMap.get(entry.getKey())).head(convertHead(entry.getValue())).build();excelWriter.write(convertData(entry.getValue(), dataMap.get(entry.getKey())), writeSheet);}excelWriter.finish();}/*** 实体写入* param response 响应对象* param sheetName sheet名称* param c 实体类* param list 实体数据*/public static T void writeSheet(HttpServletResponse response, String sheetName, ClassT c, ListT list) throws IOException {EasyExcel.write(response.getOutputStream(), c).sheet(sheetName).doWrite(list);}/*** 读取并存储到实体* param fileName 路径地址* param sheetName sheet名称* param c 实体类*/public static T ListT read(String fileName, String sheetName, Class c) {ListT list new ArrayList();EasyExcel.read(fileName, c, new ReadListenerT() {Overridepublic void invoke(T o, AnalysisContext analysisContext) {list.add(o);}Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}).sheet(sheetName).doRead();return list;}/*** 读取并存储到实体* param fileName 路径地址* param sheetNo 指定sheet*/public static MapString,Object readToMap(String fileName, Integer sheetNo) {MapString,Object result new HashMap();ListMapString,Object dataList new ArrayList();//头部mapMapString,String headMap new HashMap();//头部拼音mapMapString,String pinyinMap new HashMap();EasyExcel.read(fileName,new AnalysisEventListenerMapInteger, Object() {Overridepublic void invoke(MapInteger, Object data, AnalysisContext context) {MapString,Object map new HashMap();for (Integer key : data.keySet()) {if(key!null data.get(key)!null) {map.put(field_ key.toString(), data.get(key));}}dataList.add(map);}Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}Overridepublic void invokeHead(MapInteger, ReadCellData? head, AnalysisContext context) {for (Integer key : head.keySet()) {if(key!null head.get(key)!null StringUtils.isNotBlank(head.get(key).getStringValue())) {headMap.put(field_ key.toString(), head.get(key).getStringValue());pinyinMap.put(field_ key.toString(), Pinyin4jUtils.getPinYinHeadChar(head.get(key).getStringValue()));}}}}).sheet(sheetNo).headRowNumber(1).doRead();result.put(headMap,headMap);result.put(pinyinMap,pinyinMap);result.put(dataList,dataList);result.put(count,dataList.size());return result;}/*** 读取表头并存储到实体* param fileName 路径地址* param sheetNo 指定sheet*/public static MapString,Object readToMapHead(String fileName, Integer sheetNo) {MapString,Object result new HashMap();//头部mapMapString,String headMap new HashMap();//头部拼音mapMapString,String pinyinMap new HashMap();EasyExcel.read(fileName,new AnalysisEventListenerMapInteger, Object() {Overridepublic void invoke(MapInteger, Object data, AnalysisContext context) {}Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}Overridepublic void invokeHead(MapInteger, ReadCellData? head, AnalysisContext context) {for (Integer key : head.keySet()) {if(key!null head.get(key)!null StringUtils.isNotBlank(head.get(key).getStringValue())) {headMap.put(field_ key.toString(), head.get(key).getStringValue());pinyinMap.put(field_ key.toString(), Pinyin4jUtils.getPinYinHeadChar(head.get(key).getStringValue()));}}}}).sheet(sheetNo).headRowNumber(1).doRead();result.put(headMap,headMap);result.put(pinyinMap,pinyinMap);return result;}/*** 头部转换* param headList 头部集合*/private static ListListString convertHead(ListExcelHead headList) {ListListString list new ArrayList();for (ExcelHead head : headList) {list.add(Lists.newArrayList(head.getTitle()));}//沒有搞清楚head的参数为ListListString,用ListString就OK了return list;}/*** 数据转换* param headList 头部集合* param dataList 数据集合*/private static ListListObject convertData(ListExcelHead headList, ListMapString, Object dataList) {ListListObject result new ArrayList();//对dataList转为easyExcel的数据格式for (MapString, Object data : dataList) {ListObject row new ArrayList();for (ExcelHead h : headList) {Object o data.get(h.getFieldName());//需要对null的处理比如age的null要转为-1row.add(handler(o, h.getNullValue()));}result.add(row);}return result;}/*** 空值处理* param o 数值* param nullValue 空值置换*/private static Object handler(Object o, Object nullValue) {return o ! null ? o : nullValue;}
}头部实体类要和工具类在同一个module或项目下
/*** Excel头部实体*/
public class ExcelHeadT {private String fieldName;private String title;private T nullValue;public ExcelHead(String fieldName, String title) {this.fieldName fieldName;this.title title;}public ExcelHead(String fieldName, String title, T nullValue) {this.fieldName fieldName;this.title title;this.nullValue nullValue;}public String getFieldName() {return fieldName;}public void setFieldName(String fieldName) {this.fieldName fieldName;}public String getTitle() {return title;}public void setTitle(String title) {this.title title;}public T getNullValue() {return nullValue;}public void setNullValue(T nullValue) {this.nullValue nullValue;}
}注意真正导出表格的是ExcelWriterSheetBuilder类中的方法前面只是封装这个是真正导出用到的这个类是EasyExcel自带的。
日期转换器
/*** 日期转换器*/
public class TimestampStringConverter implements ConverterTimestamp {Overridepublic Class? supportJavaTypeKey() {return Timestamp.class;}Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}Overridepublic WriteCellData? convertToExcelData(Timestamp value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {WriteCellData cellData new WriteCellData();String cellValue;if (contentProperty null || contentProperty.getDateTimeFormatProperty() null) {cellValue DateUtils.format(value.toLocalDateTime(), null, globalConfiguration.getLocale());} else {cellValue DateUtils.format(value.toLocalDateTime(), contentProperty.getDateTimeFormatProperty().getFormat(),globalConfiguration.getLocale());}cellData.setType(CellDataTypeEnum.STRING);cellData.setStringValue(cellValue);cellData.setData(cellValue);return cellData;}
}
