POI导入导出Excel文件（一）

最近工作中用到的POI导入导出Excel文件的地方比较到，所以就乘机总结一下，在这里先说以导入Excel的情况简单的说一下，现在就直接上代码，在代码上面做注释了。下面只是自己工作中的一段代码，其实主要就是在讲将Excel的文档读入之后将文件中的每一行进行解析得到的根据自己的实体等信息进行保存即可。

//将文件进行读入
/*** 构造函数* @param path 导入文件，读取第一个工作表* @param headerNum 标题行号，数据行号=标题行号+1* @throws InvalidFormatException * @throws IOException */public ImportExcel(String fileName, int headerNum) throws InvalidFormatException, IOException {this(new File(fileName), headerNum);}/*** 构造函数* @param path 导入文件对象，读取第一个工作表* @param headerNum 标题行号，数据行号=标题行号+1* @throws InvalidFormatException * @throws IOException */public ImportExcel(File file, int headerNum) throws InvalidFormatException, IOException {this(file, headerNum, 0);}/*** 构造函数* @param path 导入文件* @param headerNum 标题行号，数据行号=标题行号+1* @param sheetIndex 工作表编号* @throws InvalidFormatException * @throws IOException */public ImportExcel(String fileName, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {this(new File(fileName), headerNum, sheetIndex);}/*** 构造函数* @param path 导入文件对象* @param headerNum 标题行号，数据行号=标题行号+1* @param sheetIndex 工作表编号* @throws InvalidFormatException * @throws IOException */public ImportExcel(File file, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);}/*** 构造函数* @param file 导入文件对象* @param headerNum 标题行号，数据行号=标题行号+1* @param sheetIndex 工作表编号* @throws InvalidFormatException * @throws IOException */public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);}public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {if (StringUtils.isBlank(fileName)){throw new RuntimeException("导入文档为空!");}else if(fileName.toLowerCase().endsWith("xls")){ this.wb = new HSSFWorkbook(is); }else if(fileName.toLowerCase().endsWith("xlsx")){ this.wb = new XSSFWorkbook(is);}else{ throw new RuntimeException("文档格式不正确!");} if (this.wb.getNumberOfSheets()throw new RuntimeException("文档中没有工作表!");}this.sheet = this.wb.getSheetAt(sheetIndex);this.headerNum = headerNum;log.debug("Initialize success.");}


private List importExcel(ImportExcel ei,Class cls) throws Exception{//拿到文件的标题信息Row OneRow= ei.getRow(0);Map menuMap = new HashMap();for(int i=1;iString cellValue = (String)ei.getCellValue(oneRow, i);menuMap.put(i, cellValue);}//log.debug("Import column count:"+annotationList.size());// 解析文件获得数据信息List dataList = Lists.newArrayList();for (int i = ei.getDataRowNum(); i Row row = ei.getRow(i);StringBuilder sb = new StringBuilder();//先拿到每行第一列的信息Object cellValue = ei.getCellValue(row, 0);Date parseDate = DateUtils.parseDate(cellValue);//循环获得每一列的数据信息for (int column = 1;columnE e = (E)cls.newInstance();Object val = ei.getCellValue(row, column);Field field = cls.getDeclaredField("yunj");Method getYunj = cls.getDeclaredMethod("get"+StringUtils.capitalize(field.getName()),null);if (val != null){ExcelField annotation = getYunj.getAnnotation(ExcelField.class);// 处理字典方面的数据信息if (StringUtils.isNotBlank(annotation.dictType())){val = DictUtils.getDictValue(val.toString(), annotation.dictType(), "");}Reflections.invokeMethod(e, "set"+StringUtils.capitalize(field.getName()), new Class[] {String.class}, new Object[] {val});Field luxmc = cls.getDeclaredField("luxmc");Reflections.invokeMethod(e, "set"+StringUtils.capitalize(luxmc.getName()), new Class[] {String.class}, new Object[] {menuMap.get(column)});Field shij = cls.getDeclaredField("shij");Reflections.invokeMethod(e, "set"+StringUtils.capitalize(shij.getName()), new Class[] {Date.class}, new Object[] {parseDate});}sb.append(val+", ");dataList.add(e);}}return dataList;}