首页 > 代码库 > 一个基于POI的通用excel导入导出工具类的简单实现及使用方法
一个基于POI的通用excel导入导出工具类的简单实现及使用方法
前言：
最近PM来了一个需求，简单来说就是在录入数据时一条一条插入到系统显得非常麻烦，让我实现一个直接通过excel导入的方法一次性录入所有数据。网上关于excel导入导出的例子很多，但大多相互借鉴。经过思考，认为一百个客户在录入excel的时候，就会有一百个格式版本，所以在实现这个功能之前，所以要统一excel的格式。于是提供了一个通用excel模版的下载功能。当所有客户用模版录入好数据再上传到系统，后端对excel进行解析，然后再持久化到数据库。
概述：
此工具类的几大特点
1、基本导入导出
2、提供excel模版自动生成及下载功能
3、创建模版过程简单通用，只需要在实体类上进行注解
4、springMVC框架
5、模版可以生成下拉框选择列
废话不多说，上代码......
一、引入poi相关依赖（及spring上传文件相关配置，不再解释）
1 <!-- apache poi start --> 2 <poi.version>3.14</poi.version> 3 <dependency> 4 <groupId>org.apache.poi</groupId> 5 <artifactId>poi</artifactId> 6 <version>${poi.version}</version> 7 </dependency> 8 9 <dependency>10 <groupId>org.apache.poi</groupId>11 <artifactId>poi-scratchpad</artifactId>12 <version>${poi.version}</version>13 </dependency>14 15 <dependency>16 <groupId>org.apache.poi</groupId>17 <artifactId>poi-ooxml</artifactId>18 <version>${poi.version}</version>19 </dependency>20 <!-- apache poi end -->
二、excel导入导出工具类的实现
1 /** 2 * @Description 3 * @author zhaomin E-mail:min.zhao@mljr.com 4 * @date 创建时间：2017年2月14日 下午2:13:30 5 * @version 1.0 6 */ 7 public class ImportExcelUtil { 8 final static String notnullerror = "请填入第{0}行的{1},{2}不能为空"; 9 final static String errormsg = "第{0}行的{1}数据导入错误"; 10 11 /** 12 * 导入Excel 13 * 14 * @param clazz 15 * @param xls 16 * @return 17 * @throws Exception 18 */ 19 @SuppressWarnings("rawtypes") 20 public static List importExcel(Class<?> clazz, InputStream xls) throws Exception { 21 try { 22 // 取得Excel 23 HSSFWorkbook wb = new HSSFWorkbook(xls); 24 HSSFSheet sheet = wb.getSheetAt(0); 25 Field[] fields = clazz.getDeclaredFields(); 26 List<Field> fieldList = new ArrayList<Field>(fields.length); 27 for (Field field : fields) { 28 if (field.isAnnotationPresent(ModelProp.class)) { 29 ModelProp modelProp = field.getAnnotation(ModelProp.class); 30 if (modelProp.colIndex() != -1) { 31 fieldList.add(field); 32 } 33 } 34 } 35 EmployeeDTO employee = new EmployeeDTO(); 36 // 行循环 37 List<ImportModel> modelList = new ArrayList<ImportModel>(sheet.getPhysicalNumberOfRows() * 2); 38 for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) { 39 // 数据模型 40 ImportModel model = (ImportModel) clazz.newInstance(); 41 int nullCount = 0; 42 Exception nullError = null; 43 for (Field field : fieldList) { 44 ModelProp modelProp = field.getAnnotation(ModelProp.class); 45 HSSFCell cell = sheet.getRow(i).getCell(modelProp.colIndex()); 46 try { 47 if (cell == null || cell.toString().length() == 0) { 48 nullCount++; 49 if (!modelProp.nullable()) { 50 nullError = new Exception(StringUtil.format(notnullerror, 51 new String[] { "" + (1 + i), modelProp.name(), modelProp.name() })); 52 53 } 54 } else if (field.getType().equals(Date.class)) { 55 if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 56 BeanUtils.setProperty(model, field.getName(), new Date(parseDate(parseString(cell)))); 57 } else { 58 BeanUtils.setProperty(model, field.getName(), 59 new Date(cell.getDateCellValue().getTime())); 60 61 } 62 } else if (field.getType().equals(Timestamp.class)) { 63 if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 64 BeanUtils.setProperty(model, field.getName(), 65 new Timestamp(parseDate(parseString(cell)))); 66 } else { 67 BeanUtils.setProperty(model, field.getName(), 68 new Timestamp(cell.getDateCellValue().getTime())); 69 } 70 71 } else if (field.getType().equals(java.sql.Date.class)) { 72 if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 73 BeanUtils.setProperty(model, field.getName(), 74 new java.sql.Date(parseDate(parseString(cell)))); 75 } else { 76 BeanUtils.setProperty(model, field.getName(), 77 new java.sql.Date(cell.getDateCellValue().getTime())); 78 } 79 } else if (field.getType().equals(java.lang.Integer.class)) { 80 if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { 81 BeanUtils.setProperty(model, field.getName(), (int) cell.getNumericCellValue()); 82 } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 83 BeanUtils.setProperty(model, field.getName(), Integer.parseInt(parseString(cell))); 84 } 85 } else if (field.getType().equals(java.math.BigDecimal.class)) { 86 if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { 87 BeanUtils.setProperty(model, field.getName(), 88 new BigDecimal(cell.getNumericCellValue())); 89 } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 90 BeanUtils.setProperty(model, field.getName(), new BigDecimal(parseString(cell))); 91 } 92 } else { 93 if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { 94 BeanUtils.setProperty(model, field.getName(), 95 new BigDecimal(cell.getNumericCellValue())); 96 } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 97 BeanUtils.setProperty(model, field.getName(), parseString(cell)); 98 } 99 }100 } catch (Exception e) {101 e.printStackTrace();102 throw new Exception(StringUtil.format(errormsg, new String[] { "" + (1 + i), modelProp.name() })103 + "," + e.getMessage());104 }105 }106 if (nullCount == fieldList.size()) {107 break;108 }109 if (nullError != null) {110 throw nullError;111 }112 modelList.add(model);113 }114 return modelList;115 116 } finally {117 xls.close();118 }119 }120 121 private final static int colsizeN = 630;122 private final static int colsizeM = 1000;123 124 /**125 * 下载Excel模版126 * 127 * @param clazz128 * @param map129 * @param rowSize130 * @return131 */132 public static InputStream excelModelbyClass(Class<?> clazz, Map<Integer, String[]> map, Integer rowSize) {133 try {134 if (!clazz.isAnnotationPresent(ModelTitle.class)) {135 throw new Exception("请在此类型中加上ModelTitle注解");136 }137 if (rowSize == null) {138 rowSize = 1000;139 }140 HSSFWorkbook wb = new HSSFWorkbook();141 HSSFSheet sheet = wb.createSheet();142 /**143 * 设置标题样式144 */145 HSSFCellStyle titleStyle = wb.createCellStyle();146 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);147 HSSFFont font = wb.createFont();148 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);149 font.setFontHeight((short) 400);150 titleStyle.setFont(font);151 HSSFCell titleCell = sheet.createRow(0).createCell(0); // 创建第一行，并在该行创建单元格，设置内容，做为标题行152 /**153 * 获取标题154 */155 ModelTitle modelTitle = clazz.getAnnotation(ModelTitle.class);156 titleCell.setCellValue(new HSSFRichTextString(modelTitle.name()));157 titleCell.setCellStyle(titleStyle);158 159 Field[] fields = clazz.getDeclaredFields();160 HSSFRow headRow = sheet.createRow(1);161 int colSzie = 0;162 /**163 * 设置表头样式164 */165 HSSFCellStyle headStyle = wb.createCellStyle();166 headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);167 HSSFFont headFont = wb.createFont();168 headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);169 headFont.setFontHeight((short) 240);170 headStyle.setFont(headFont);171 List<Integer> cells = new ArrayList<Integer>();172 173 for (Field field : fields) {174 if (field.isAnnotationPresent(ModelProp.class)) {175 ModelProp modelProp = field.getAnnotation(ModelProp.class);176 if (modelProp.colIndex() == -1)177 continue;178 cells.add(modelProp.colIndex());179 HSSFCell cell = headRow.createCell(modelProp.colIndex());180 cell.setCellValue(new HSSFRichTextString(modelProp.name()));181 cell.setCellStyle(headStyle);182 colSzie++;183 sheet.autoSizeColumn((short) modelProp.colIndex());184 sheet.setColumnWidth(modelProp.colIndex(), modelProp.name().length() * colsizeN + colsizeM);185 186 // 设置列为下拉框格式187 if (map != null && map.get(new Integer(modelProp.colIndex())) != null) {188 DVConstraint constraint = DVConstraint189 .createExplicitListConstraint(map.get(modelProp.colIndex()));190 CellRangeAddressList regions = new CellRangeAddressList(2, rowSize, modelProp.colIndex(),191 modelProp.colIndex());192 HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);193 sheet.addValidationData(dataValidation);194 }195 }196 }197 HSSFCellStyle cellStyle = wb.createCellStyle();198 HSSFDataFormat format = wb.createDataFormat();199 cellStyle.setDataFormat(format.getFormat("@"));200 for (int i = 2; i < rowSize; i++) {201 HSSFRow row = sheet.createRow(i);202 for (Integer integer : cells) {203 HSSFCell cell = row.createCell(integer);204 cell.setCellStyle(cellStyle);205 }206 }207 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSzie - 1));208 if (map != null) {209 for (Integer colIndex : map.keySet()) {210 DVConstraint constraint = DVConstraint.createExplicitListConstraint(map.get(colIndex));211 CellRangeAddressList regions = new CellRangeAddressList(2, 1000, colIndex, colIndex);212 HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);213 sheet.addValidationData(dataValidation);214 }215 }216 217 ByteArrayOutputStream os = new ByteArrayOutputStream();218 try {219 wb.write(os);220 } catch (IOException e) {221 e.printStackTrace();222 }223 224 byte[] b = os.toByteArray();225 226 ByteArrayInputStream in = new ByteArrayInputStream(b);227 return in;228 } catch (Exception e) {229 e.printStackTrace();230 return null;231 }232 }233 234 private static String parseString(HSSFCell cell) {235 return String.valueOf(cell).trim();236 }237 238 private static long parseDate(String dateString) throws ParseException {239 if (dateString.indexOf("/") == 4) {240 return new SimpleDateFormat("yyyy/MM/dd").parse(dateString).getTime();241 } else if (dateString.indexOf("-") == 4) {242 return new SimpleDateFormat("yyyy-MM-dd").parse(dateString).getTime();243 } else if (dateString.indexOf("年") == 4) {244 return new SimpleDateFormat("yyyy年MM月dd").parse(dateString).getTime();245 } else if (dateString.length() == 8) {246 return new SimpleDateFormat("yyyyMMdd").parse(dateString).getTime();247 } else {248 return new Date().getTime();249 }250 }251 252 }
三、自定义spring注解
1 @Retention(RetentionPolicy.RUNTIME)2 @Target(ElementType.FIELD)3 public @interface ModelProp{4 public String name();5 public int colIndex() default -1;6 public boolean nullable() default true;7 public String interfaceXmlName() default "";8 }
1 @Retention(RetentionPolicy.RUNTIME)2 @Target(ElementType.TYPE)3 public @interface ModelTitle{4 public String name();5 }
四、定义实体类父类
1 public class ImportModel {2 3 }
五、定义实体类
1 @ModelTitle(name="人员列表") 2 public class EmployeeDTO extends ImportModel implements Serializable { 3 4 private static final long serialVersionUID = -3434719712955859295L; 5 6 private Long id; 7 @ModelProp(name = "电话", colIndex = 1, nullable = false) 8 private String telephone; 9 10 @ModelProp(name = "名称", colIndex = 0, nullable = false)11 private String name;12 13 @ModelProp(name = "性别", colIndex = 2, nullable = false)14 private Integer sex;15 }
六、定义controller
1 @RestController 2 @RequestMapping("/api/excelOpera") 3 public class ImportEmployeeController extends BaseController { 4 5 private static Logger logger = LoggerFactory.getLogger(ImportEmployeeController.class); 6 /** 7 * 导入excel表 8 * @version 1.0 9 * @since 1.010 */11 @RequestMapping(path = "/importEmployee", method = RequestMethod.POST)12 public RespMsg uploadExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) {13 try{14 15 List<EmployeeDTO> employeeDTOList = ImportExcelUtil.importExcel(EmployeeDTO.class, file.getInputStream());16 //可做持久化操作，现只打印观察17 for(EmployeeDTO employeeDTO : employeeDTOList){18 logger.info("name=" + employeeDTO.getName() + ",telephone=" + employeeDTO.getTelephone()+",sex=" + employeeDTO.getSex());19 }20 }catch(Exception e){21 logger.error(e.getMessage());22 }23 return null;24 }25 /**26 * 导出excel模版27 * @version 1.028 * @since 1.029 */30 @RequestMapping(path = "/downloadEmployeeModel", method = RequestMethod.GET)31 public RespMsg downloadEmployeeModel(HttpServletResponse response) {32 try{33 response.setContentType("application/xls");34 response.addHeader("Content-Disposition", "attachment;filename="+new String(("eeelist").getBytes("UTF-8"),"iso-8859-1")+".xls");35 Map<Integer,String[]> paramMap = new HashMap<Integer,String[]>();36 //excel第三行为下拉选择框37 paramMap.put(2, new String[]{"man","women"});38 BufferedInputStream input = new BufferedInputStream(ImportExcelUtil.excelModelbyClass(EmployeeDTO.class, paramMap, null));39 byte buffBytes[] = new byte[1024];40 OutputStream os = response.getOutputStream();41 int read = 0;42 while ((read = input.read(buffBytes)) != -1) {43 os.write(buffBytes, 0, read);44 }45 os.flush();46 os.close();47 input.close();48 return success("下载成功！");49 }catch(Exception e){50 logger.error("downloadEmployeeModel() catch Exception ",e);51 return fail("下载失败！");52 }53 }54 55 }56
至此全部工具类的实现已经完成，可以请求访问检验一下结果。
下载下来的excel模版
填写数据上传
后台控制台打印输出结果
检验结果能够达到预期效果且能通用，是不是很简单呢，欢迎大神们提出意见，小女子感谢了。
一个基于POI的通用excel导入导出工具类的简单实现及使用方法
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。