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 void TControlEntmeasuresExcel(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 = new XSSFWorkbook(); 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导入数据库
控制层代码：
/** * 根据文件路径导入excel数据 * @param response */ @PostMapping("/tcontrolEntmeasuresExcelImport") private void TControlEntmeasuresExcelImport(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 * @param filePath 文件路径 4 * @param titleRows 表标题的行数 5 * @param headerRows 表头行数 6 * @param pojoClass 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 = new ImportParams(); 15 params.setTitleRows(titleRows);//设置表标题行数 16 params.setHeadRows(headerRows);//设置表头行数 17 List<T> list = null; 18 try { 19 list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params); 20 } catch (NoSuchElementException e) { 21 // throw new RuntimeException("模板不能为空"); 22 e.printStackTrace(); 23 } catch (Exception e) { 24 e.printStackTrace(); 25 26 } 27 return list; 28 }
实体类代码：
1 public class TControlEntmeasuresExcel { 2 @Excel(name = "序号",orderNum = "0") 3 private String number; 4 @Excel(name = "年份",orderNum = "1") 5 private String year; 6 @Excel(name = "企业名称",orderNum = "2") 7 private String entId; 8 @Excel(name = "设施名称",orderNum = "3") 9 private String deviceId; 10 11 getter、setter和tostring方法
多sheet导入
控制层代码：
1 @RequestMapping("/importAirPollutionElectricityData") 2 public void importAirPollutionElectricityData(MultipartFile file) { 3 4 String s = excelService.importSheets(file);
}
service层：
1 /** 2 * 多sheet导入 3 * @param file 4 * @return 5 * @throws IOException 6 */ 7 @Override 8 public String importSheets(MultipartFile file){ 9 try { 10 // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页 11 Workbook workBook = ExcelUtiles.getWorkBook(file); 12 StringBuilder sb=new StringBuilder(); 13 ImportParams params = new ImportParams(); 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 * @param file 4 * @return 5 * @throws IOException 6 */ 7 public static Workbook getWorkBook(MultipartFile file) throws IOException{ 8 //这样写 excel 能兼容03和07 9 InputStream is = file.getInputStream(); 10 Workbook hssfWorkbook = null; 11 try { 12 hssfWorkbook = new HSSFWorkbook(is); 13 } catch (Exception ex) { 14 is =file.getInputStream(); 15 hssfWorkbook = new XSSFWorkbook(is); 16 } 17 return hssfWorkbook; 18 } 19 20 21 /** 22 * 得到错误信息 23 * @param sb 24 * @param list 25 * @param i 26 * @param obj 27 * @param name 用哪个属性名去表明不和规定的数据 28 * @param msg 29 * @throws Exception 30 */ 31 public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throws Exception{ 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 = new PropertyDescriptor(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 package com.mapuni.datacenter.utils; 2 3 import cn.afterturn.easypoi.excel.ExcelExportUtil; 4 import cn.afterturn.easypoi.excel.ExcelImportUtil; 5 import cn.afterturn.easypoi.excel.entity.ExportParams; 6 import cn.afterturn.easypoi.excel.entity.ImportParams; 7 import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; 8 import org.apache.commons.lang3.StringUtils; 9 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 10 import org.apache.poi.ss.usermodel.Workbook; 11 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 12 import org.springframework.web.multipart.MultipartFile; 13 14 import com.mapuni.datacenter.entity.ControlEntElectricity; 15 16 import javax.servlet.http.HttpServletResponse; 17 import java.io.File; 18 import java.io.IOException; 19 import java.io.InputStream; 20 import java.net.URLEncoder; 21 import java.util.List; 22 import java.util.Map; 23 import java.util.NoSuchElementException; 24 import java.beans.PropertyDescriptor; 25 import java.lang.reflect.Field; 26 import java.lang.reflect.Method; 27 28 /** 29 * @description: easypoiUtils 30 * 导入导出工具类 31 */ 32 public class ExcelUtiles { 33 34 /** 35 * 测试 36 * @param args 37 */ 38 public static void main(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 * @param list 导出的实体类 48 * @param title 表头名称 49 * @param sheetName sheet表名 50 * @param pojoClass 映射的实体类 51 * @param isCreateHeader 是否创建表头 52 * @param fileName 53 * @param response 54 * @return 55 */ 56 public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, 57 boolean isCreateHeader, HttpServletResponse response) { 58 ExportParams exportParams = new ExportParams(title, sheetName); 59 exportParams.setCreateHeadRows(isCreateHeader); 60 defaultExport(list, pojoClass, fileName, response, exportParams); 61 } 62 63 /** 64 * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头 65 * @param list 导出的实体类 66 * @param title 表头名称 67 * @param sheetName sheet表名 68 * @param pojoClass 映射的实体类 69 * @param fileName 70 * @param response 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, new ExportParams(title, sheetName)); 76 } 77 78 /** 79 * 功能描述：Map 集合导出 80 * @param list 实体集合 81 * @param fileName 导出的文件名称 82 * @param response 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 * @param list 导出的实体集合 92 * @param fileName 导出的文件名 93 * @param pojoClass pojo实体 94 * @param exportParams ExportParams封装实体 95 * @param response 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 * @param fileName 文件名称 109 * @param response 110 * @param workbook Excel对象 111 * @return 112 */ 113 private static void downLoadExcel(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 * @param list 导出的实体集合 127 * @param fileName 导出的文件名 128 * @param response 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 * @param filePath 文件路径 141 * @param titleRows 表标题的行数 142 * @param headerRows 表头行数 143 * @param pojoClass 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 = new ImportParams(); 152 params.setTitleRows(titleRows); 153 params.setHeadRows(headerRows); 154 List<T> list = null; 155 try { 156 list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params); 157 } catch (NoSuchElementException e) { 158 // throw new RuntimeException("模板不能为空"); 159 e.printStackTrace(); 160 } catch (Exception e) { 161 e.printStackTrace(); 162 163 } 164 return list; 165 } 166 167 /** 168 * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类 169 * @param file 上传的文件 170 * @param titleRows 表标题的行数 171 * @param headerRows 表头行数 172 * @param pojoClass 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 = new ImportParams(); 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 return list; 193 } 194 /** 195 * 得到Workbook对象 196 * @param file 197 * @return 198 * @throws IOException 199 */ 200 public static Workbook getWorkBook(MultipartFile file) throws IOException{ 201 //这样写 excel 能兼容03和07 202 InputStream is = file.getInputStream(); 203 Workbook hssfWorkbook = null; 204 try { 205 hssfWorkbook = new HSSFWorkbook(is); 206 } catch (Exception ex) { 207 is =file.getInputStream(); 208 hssfWorkbook = new XSSFWorkbook(is); 209 } 210 return hssfWorkbook; 211 } 212 213 /** 214 * 得到错误信息 215 * @param sb 216 * @param list 217 * @param i 218 * @param obj 219 * @param name 用哪个属性名去表明不和规定的数据 220 * @param msg 221 * @throws Exception 222 */ 223 public static void getWrongInfo(StringBuilder sb,List list,int i,Object obj,String name,String msg) throws Exception{ 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 = new PropertyDescriptor(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 }
