摘要：
Excel生成 Excel生成导入依赖˂!
导入依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.15</version> </dependency>
<!--easypoi--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency>
控制层代码：
@PostMapping("/tcontrolEntmeasuresExcel") private voidTControlEntmeasuresExcel(HttpServletResponse response, HttpServletRequest request){String[] fieldData = new String[] {"序号", "企业名称", "行业", "设施名称", "年份"};
//查询数据库得到想要导出数据的list集合 List<TControlEntmeasures> findT_control_entmeasuresByallexcel =tcontrolEntmeasuresService.findT_control_entmeasuresByallexcel(); controlReserveplanService.comCompanyExcelXLSX(response, request,fieldData); }
//可以根据request进行条件查询
业务层controlReserveplanService.comCompanyExcelXLSX(response, request,fieldData代码
public void comCompanyExcelXLSX(HttpServletResponse response,HttpServletRequest, List<TControlEntmeasures>list, String startTime, String endTime, String[] fieldData) { int cowspan = 0; if(fieldData != null) { cowspan =fieldData.length; } try{ String fileName = "污染源管控措施"; response.setContentType("application/vnd.ms-excel"); //传递中文参数编码 String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8"); response.setHeader("content-disposition", ";filename="+codedFileName+".xlsx"); /*** 创建工作表 */XSSFWorkbook wb = newXSSFWorkbook(); Sheet sheet = wb.createSheet("0"); //设置列宽 sheet.setColumnWidth(0, 256*10); sheet.setColumnWidth(1, 256*20); sheet.setColumnWidth(2, 256*40); sheet.setColumnWidth(3, 256*20); sheet.setColumnWidth(4, 256*25); sheet.setColumnWidth(5, 256*25); sheet.setColumnWidth(6, 256*25); sheet.setColumnWidth(7, 256*25); sheet.setColumnWidth(8, 256*25); sheet.setColumnWidth(9, 256*15); sheet.setColumnWidth(10, 256*15); sheet.setColumnWidth(11, 256*20); sheet.setColumnWidth(12, 256*15); sheet.setColumnWidth(13, 256*15); sheet.setColumnWidth(14, 256*15); sheet.setColumnWidth(15, 256*15); /*** 标题样式 样式 */CellStyle titleCellStyle =wb.createCellStyle(); titleCellStyle.setBorderLeft(BorderStyle.NONE); titleCellStyle.setBorderRight(BorderStyle.NONE); titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中 titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //上下居中 XSSFFont titleFont =wb.createFont(); titleFont.setFontHeight(18); titleFont.setBold(true); titleCellStyle.setFont(titleFont); /*** 主标题 在这里插入主标题 */Row titleRow; Cell titleCell; sheet.addMergedRegion(new CellRangeAddress((short) 0, (short) 0, (short) 0, (short) cowspan));//合并单元格；四个参数分别为起始行、终止行、起始列、终止列 titleRow = sheet.createRow(0);//从0开始，第一行 for (int j = 0; j < 3; j++) { titleCell =titleRow.createCell(j); titleCell.setCellType(CellType.STRING); titleCell.setCellStyle(titleCellStyle); titleCell.setCellValue("污染源管控措施"); } /*** 时间范围 */CellStyle timeCellStyle =wb.createCellStyle(); timeCellStyle.setBorderLeft(BorderStyle.NONE); timeCellStyle.setBorderRight(BorderStyle.NONE); timeCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平居中 timeCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //上下居中 XSSFFont timeFont =wb.createFont(); timeFont.setFontHeight(12); timeCellStyle.setFont(timeFont); /*** 列 标题 在这里插入标题 */CellStyle textCellStyle =wb.createCellStyle(); textCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中 textCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //上下居中 XSSFFont textFont =wb.createFont(); textFont.setFontHeight(12); textFont.setBold(true); textCellStyle.setFont(textFont); //列标题赋值处理 Row rowLabel; Cell cellLabel; rowLabel = sheet.createRow(1);//第二行，插入标题 for (int j = 0; j < fieldData.length; j++) { cellLabel =rowLabel.createCell(j); cellLabel.setCellType(CellType.STRING); cellLabel.setCellStyle(textCellStyle); cellLabel.setCellValue(fieldData[j]); } /*** 单元格 样式 */CellStyle cellStyle =wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //上下居中 /*** --列 数据 在这里插入数据 */ int i=2; int count = 2;//第三行；插入数据 Row rowCheck; for(TControlEntmeasures temp : list) { rowCheck =sheet.createRow(count); rowCheck.setRowStyle(cellStyle); rowCheck.createCell(0).setCellValue(i-1); rowCheck.createCell(1).setCellValue(temp.getEntName());//企业名称 rowCheck.createCell(2).setCellValue(temp.getRedalert());//红色预警等级 rowCheck.createCell(3).setCellValue(temp.getRedwarningscheme());//红色措施 ...
...
...
rowCheck.createCell(15).setCellValue(temp.getYear());//年份 for(int n=0; n<15; n++) { rowCheck.getCell(n).setCellStyle(cellStyle);//给单元格加上样式 } count++;//循环一次行数+1 i++;//循环一次序号+1 }
/**
* 合并单元格
startRow = endRow + 1;
endRow = endRow + device.size();
if(endRow > startRow) {
//合并单元格 -指定 4 个参数，起始行，结束行，起始列，结束列。然后这个区域将被合并
CellRangeAddress region = new CellRangeAddress(startRow, endRow, 0, 0);
sheet.addMergedRegion(region);
CellRangeAddress region1 = new CellRangeAddress(startRow, endRow, 1, 1);
sheet.addMergedRegion(region1);
CellRangeAddress region2 = new CellRangeAddress(startRow, endRow, 2, 2);
sheet.addMergedRegion(region2);
CellRangeAddress region3 = new CellRangeAddress(startRow, endRow, 3, 3);
sheet.addMergedRegion(region3);
}
*/
/*** 页脚 */wb.setSheetName(0, "污染源管控措施"); /*** 进行导出 */OutputStream out =response.getOutputStream(); wb.write(out); out.flush(); out.close(); } catch(Exception e) { //TODO: handle exception e.printStackTrace(); } }
控制层代码：
/*** 根据文件路径导入excel数据 * @paramresponse */@PostMapping("/tcontrolEntmeasuresExcelImport") private voidTControlEntmeasuresExcelImport(HttpServletResponse response){//传入对应需要导入文档的路径和创建的实体类 List<TControlEntmeasuresExcel> tcontrolEntmeasuresExcellmport = ExcelUtiles.importExcel("F:\程序快捷\---文档\--模板.xlsx", 0, 1, TControlEntmeasuresExcel.class); for(int i=0;i<tcontrolEntmeasuresExcellmport.size();i++) { TControlEntmeasuresExcel tControlEntmeasuresExcel =tcontrolEntmeasuresExcellmport.get(i); String entId = tControlEntmeasuresExcel.getEntId();//获取企业名称进行查询企业id插入 String deviceId = tControlEntmeasuresExcel.getDeviceId();//获取设施名称查询设施id插入 String redStart = tControlEntmeasuresExcel.getRedStart();//获取是否启动红色预警 ...
...
...
TControlEntmeasures controlEntmeasures =new TControlEntmeasures();
controlEntmeasures.setYear(Integer.valueOf(tControlEntmeasuresExcel.getYear()));//年份
controlEntmeasures.setEntId(tControlEntmeasuresExcel.getEntId());//企业id
controlEntmeasures.setDeviceId(tControlEntmeasuresExcel.getDeviceId());//设施id
...
...
...
int insertT_control_entmeasuress =tcontrolEntmeasuresService.insertT_control_entmeasuress(controlEntmeasures);//将数据插入 System.out.println(tControlEntmeasuresExcel.toString()); } }
ExcelUtiles.importExcel代码：
1 /** 2 * 功能描述：根据文件路径来导入Excel 3 * @paramfilePath 文件路径 4 * @paramtitleRows 表标题的行数 5 * @paramheaderRows 表头行数 6 * @parampojoClass Excel实体类 7 * @return 8 */ 9 public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T>pojoClass) { 10 //判断文件是否存在 11 if(StringUtils.isBlank(filePath)) { 12 return null; 13 } 14 ImportParams params = newImportParams(); 15 params.setTitleRows(titleRows);//设置表标题行数 16 params.setHeadRows(headerRows);//设置表头行数 17 List<T> list = null; 18 try{ 19 list = ExcelImportUtil.importExcel(newFile(filePath), pojoClass, params); 20 } catch(NoSuchElementException e) { 21 //throw new RuntimeException("模板不能为空"); 22 e.printStackTrace(); 23 } catch(Exception e) { 24 e.printStackTrace(); 25 26 } 27 returnlist; 28 }
实体类代码：
1 public classTControlEntmeasuresExcel { 2 @Excel(name = "序号",orderNum = "0") 3 privateString number; 4 @Excel(name = "年份",orderNum = "1") 5 privateString year; 6 @Excel(name = "企业名称",orderNum = "2") 7 privateString entId; 8 @Excel(name = "设施名称",orderNum = "3") 9 privateString deviceId; 10 11 getter、setter和tostring方法
控制层代码：
1 @RequestMapping("/importAirPollutionElectricityData") 2 public voidimportAirPollutionElectricityData(MultipartFile file) { 3 4 String s = excelService.importSheets(file);
}
service层：
1 /** 2 * 多sheet导入 3 * @paramfile 4 * @return 5 * @throwsIOException 6 */ 7 @Override 8 publicString importSheets(MultipartFile file){ 9 try{ 10 //根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页 11 Workbook workBook =ExcelUtiles.getWorkBook(file); 12 StringBuilder sb=newStringBuilder(); 13 ImportParams params = newImportParams(); 14 //循环工作表Sheet 15 for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) { 16 //第几个sheet页 17 params.setStartSheetIndex(numSheet); 18 //验证数据 19 params.setNeedVerfiy(true); 20 if(numSheet==0){ 21 //表头在第几行 22 params.setTitleRows(1); 23 //距离表头中间有几行不要的数据 24 params.setStartRows(0); 25 ExcelImportResult<ControlEntElectricity> result =ExcelImportUtil.importExcelMore(file.getInputStream(), 26 ControlEntElectricity.class, params); 27 //校验是否合格 28 if(result.isVerfiyFail()){ 29 //不合格的数据 30 List<ControlEntElectricity> errorList =result.getList(); 31 //拼凑错误信息,自定义 32 for(int i=0;i<errorList.size();i++){ 33 ExcelUtiles.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "。。信息不合法"); 34 } 35 } 36 //合格的数据 37 List<ControlEntElectricity> successList =result.getList(); 38 //业务逻辑 39 System.out.println(successList.toString()); 40 }else if(numSheet==1){ 41 //表头在第几行 42 params.setTitleRows(2); 43 //距离表头中间有几行不要的数据 44 params.setStartRows(0); 45 ExcelImportResult<ControlEntElectricityDay> result =ExcelImportUtil.importExcelMore(file.getInputStream(), 46 ControlEntElectricityDay.class, params); 47 //校验是否合格 48 if(result.isVerfiyFail()){ 49 //不合格的数据 50 List<ControlEntElectricityDay> errorList =result.getList(); 51 //拼凑错误信息,自定义 52 for(int i=0;i<errorList.size();i++){ 53 ExcelUtiles.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "。。信息不合法"); 54 } 55 } 56 //校验合格的数据 57 List<ControlEntElectricityDay> successList =result.getList(); 58 //业务逻辑 59 System.out.println(successList.toString()); 60 } 61 62 } 63 } catch(Exception e) { 64 //log.error("导入失败:{}",e); 65 return "导入失败！请检查导入文档的格式是否正确"; 66 } 67 return "导入成功！"; 68 }
ExcelUtiles.getWorkBook()和 ExcelUtiles.getWrongInfo()方法代码：
1 /** 2 * 得到Workbook对象 3 * @paramfile 4 * @return 5 * @throwsIOException 6 */ 7 public static Workbook getWorkBook(MultipartFile file) throwsIOException{ 8 //这样写 excel 能兼容03和07 9 InputStream is =file.getInputStream(); 10 Workbook hssfWorkbook = null; 11 try{ 12 hssfWorkbook = newHSSFWorkbook(is); 13 } catch(Exception ex) { 14 is =file.getInputStream(); 15 hssfWorkbook = newXSSFWorkbook(is); 16 } 17 returnhssfWorkbook; 18 } 19 20 21 /** 22 * 得到错误信息 23 * @paramsb 24 * @paramlist 25 * @parami 26 * @paramobj 27 * @paramname 用哪个属性名去表明不和规定的数据 28 * @parammsg 29 * @throwsException 30 */ 31 public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throwsException{ 32 Class clazz=obj.getClass(); 33 Object str=null; 34 //得到属性名数组 35 Field[] fields =clazz.getDeclaredFields(); 36 for(Field f : fields){ 37 if(f.getName().equals(name)){ 38 //用来得到属性的get和set方法 39 PropertyDescriptor pd = newPropertyDescriptor(f.getName(), clazz); 40 //得到get方法 41 Method getMethod=pd.getReadMethod(); 42 str =getMethod.invoke(obj); 43 } 44 } 45 if(i==0) { 46 sb.append(msg + str + ";"); 47 } 48 else if(i==(list.size()-1)) { 49 sb.append(str + "</br>"); 50 } 51 else{ 52 sb.append(str + ";"); 53 } 54 }
附上excel导入导出可能会用到的工具类代码ExcelUtiles.java：
1 packagecom.mapuni.datacenter.utils; 2 3 importcn.afterturn.easypoi.excel.ExcelExportUtil; 4 importcn.afterturn.easypoi.excel.ExcelImportUtil; 5 importcn.afterturn.easypoi.excel.entity.ExportParams; 6 importcn.afterturn.easypoi.excel.entity.ImportParams; 7 importcn.afterturn.easypoi.excel.entity.enmus.ExcelType; 8 importorg.apache.commons.lang3.StringUtils; 9 importorg.apache.poi.hssf.usermodel.HSSFWorkbook; 10 importorg.apache.poi.ss.usermodel.Workbook; 11 importorg.apache.poi.xssf.usermodel.XSSFWorkbook; 12 importorg.springframework.web.multipart.MultipartFile; 13 14 importcom.mapuni.datacenter.entity.ControlEntElectricity; 15 16 importjavax.servlet.http.HttpServletResponse; 17 importjava.io.File; 18 importjava.io.IOException; 19 importjava.io.InputStream; 20 importjava.net.URLEncoder; 21 importjava.util.List; 22 importjava.util.Map; 23 importjava.util.NoSuchElementException; 24 importjava.beans.PropertyDescriptor; 25 importjava.lang.reflect.Field; 26 importjava.lang.reflect.Method; 27 28 /** 29 * @description: easypoiUtils 30 * 导入导出工具类 31 */ 32 public classExcelUtiles { 33 34 /** 35 * 测试 36 * @paramargs 37 */ 38 public static voidmain(String[] args) { 39 List<ControlEntElectricity> list = ExcelUtiles.importExcel("D:\5..27.xlsx", 0, 1, ControlEntElectricity.class); 40 System.out.println(list.toString()); 41 String s = "D:\5..27.xlsx"; 42 System.out.println(s.substring(s.lastIndexOf("."))); 43 } 44 45 /** 46 * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头 47 * @paramlist 导出的实体类 48 * @paramtitle 表头名称 49 * @paramsheetName sheet表名 50 * @parampojoClass 映射的实体类 51 * @paramisCreateHeader 是否创建表头 52 * @paramfileName 53 * @paramresponse 54 * @return 55 */ 56 public static void exportExcel(List<?> list, String title, String sheetName, Class<?>pojoClass, String fileName, 57 booleanisCreateHeader, HttpServletResponse response) { 58 ExportParams exportParams = newExportParams(title, sheetName); 59 exportParams.setCreateHeadRows(isCreateHeader); 60 defaultExport(list, pojoClass, fileName, response, exportParams); 61 } 62 63 /** 64 * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头 65 * @paramlist 导出的实体类 66 * @paramtitle 表头名称 67 * @paramsheetName sheet表名 68 * @parampojoClass 映射的实体类 69 * @paramfileName 70 * @paramresponse 71 * @return 72 */ 73 public static void exportExcel(List<?> list, String title, String sheetName, Class<?>pojoClass, String fileName, 74 HttpServletResponse response) { 75 defaultExport(list, pojoClass, fileName, response, newExportParams(title, sheetName)); 76 } 77 78 /** 79 * 功能描述：Map 集合导出 80 * @paramlist 实体集合 81 * @paramfileName 导出的文件名称 82 * @paramresponse 83 * @return 84 */ 85 public static void exportExcel(List<Map<String, Object>>list, String fileName, HttpServletResponse response) { 86 defaultExport(list, fileName, response); 87 } 88 89 /** 90 * 功能描述：默认导出方法 91 * @paramlist 导出的实体集合 92 * @paramfileName 导出的文件名 93 * @parampojoClass pojo实体 94 * @paramexportParams ExportParams封装实体 95 * @paramresponse 96 * @return 97 */ 98 private static void defaultExport(List<?> list, Class<?>pojoClass, String fileName, HttpServletResponse response, 99 ExportParams exportParams) { 100 Workbook workbook =ExcelExportUtil.exportExcel(exportParams, pojoClass, list); 101 if (workbook != null) { 102 downLoadExcel(fileName, response, workbook); 103 } 104 } 105 106 /** 107 * 功能描述：Excel导出 108 * @paramfileName 文件名称 109 * @paramresponse 110 * @paramworkbook Excel对象 111 * @return 112 */ 113 private static voiddownLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) { 114 try{ 115 response.setCharacterEncoding("UTF-8"); 116 response.setHeader("content-Type", "application/vnd.ms-excel"); 117 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); 118 workbook.write(response.getOutputStream()); 119 } catch(IOException e) { 120 //throw new NormalException(e.getMessage()); 121 } 122 } 123 124 /** 125 * 功能描述：默认导出方法 126 * @paramlist 导出的实体集合 127 * @paramfileName 导出的文件名 128 * @paramresponse 129 * @return 130 */ 131 private static void defaultExport(List<Map<String, Object>>list, String fileName, HttpServletResponse response) { 132 Workbook workbook =ExcelExportUtil.exportExcel(list, ExcelType.HSSF); 133 if (workbook != null) 134 ; 135 downLoadExcel(fileName, response, workbook); 136 } 137 138 /** 139 * 功能描述：根据文件路径来导入Excel 140 * @paramfilePath 文件路径 141 * @paramtitleRows 表标题的行数 142 * @paramheaderRows 表头行数 143 * @parampojoClass Excel实体类 144 * @return 145 */ 146 public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T>pojoClass) { 147 //判断文件是否存在 148 if(StringUtils.isBlank(filePath)) { 149 return null; 150 } 151 ImportParams params = newImportParams(); 152 params.setTitleRows(titleRows); 153 params.setHeadRows(headerRows); 154 List<T> list = null; 155 try{ 156 list = ExcelImportUtil.importExcel(newFile(filePath), pojoClass, params); 157 } catch(NoSuchElementException e) { 158 //throw new RuntimeException("模板不能为空"); 159 e.printStackTrace(); 160 } catch(Exception e) { 161 e.printStackTrace(); 162 163 } 164 returnlist; 165 } 166 167 /** 168 * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类 169 * @paramfile 上传的文件 170 * @paramtitleRows 表标题的行数 171 * @paramheaderRows 表头行数 172 * @parampojoClass Excel实体类 173 * @return 174 */ 175 public static <T> List<T>importExcel(MultipartFile file, Integer titleRows, Integer headerRows, 176 Class<T>pojoClass) { 177 if (file == null) { 178 return null; 179 } 180 ImportParams params = newImportParams(); 181 params.setTitleRows(titleRows); 182 params.setHeadRows(headerRows); 183 List<T> list = null; 184 try{ 185 list =ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params); 186 } catch(NoSuchElementException e) { 187 //throw new NormalException("excel文件不能为空"); 188 } catch(Exception e) { 189 //throw new NormalException(e.getMessage()); 190 System.out.println(e.getMessage()); 191 } 192 returnlist; 193 } 194 /** 195 * 得到Workbook对象 196 * @paramfile 197 * @return 198 * @throwsIOException 199 */ 200 public static Workbook getWorkBook(MultipartFile file) throwsIOException{ 201 //这样写 excel 能兼容03和07 202 InputStream is =file.getInputStream(); 203 Workbook hssfWorkbook = null; 204 try{ 205 hssfWorkbook = newHSSFWorkbook(is); 206 } catch(Exception ex) { 207 is =file.getInputStream(); 208 hssfWorkbook = newXSSFWorkbook(is); 209 } 210 returnhssfWorkbook; 211 } 212 213 /** 214 * 得到错误信息 215 * @paramsb 216 * @paramlist 217 * @parami 218 * @paramobj 219 * @paramname 用哪个属性名去表明不和规定的数据 220 * @parammsg 221 * @throwsException 222 */ 223 public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throwsException{ 224 Class clazz=obj.getClass(); 225 Object str=null; 226 //得到属性名数组 227 Field[] fields =clazz.getDeclaredFields(); 228 for(Field f : fields){ 229 if(f.getName().equals(name)){ 230 //用来得到属性的get和set方法 231 PropertyDescriptor pd = newPropertyDescriptor(f.getName(), clazz); 232 //得到get方法 233 Method getMethod=pd.getReadMethod(); 234 str =getMethod.invoke(obj); 235 } 236 } 237 if(i==0) { 238 sb.append(msg + str + ";"); 239 } 240 else if(i==(list.size()-1)) { 241 sb.append(str + "</br>"); 242 } 243 else{ 244 sb.append(str + ";"); 245 } 246 } 247 248 249 }