POI 3.17 读取Excel（模板）

最近在给通用做一个项目，很多功能块需要导入Excel模板，我正好研究了几天，两种输出方式供你选择

Maven依赖

<dependency>      <groupId>org.apache.commons</groupId>      <artifactId>commons-collections4</artifactId>      <version>4.1</version>  </dependency><dependency>    <groupId>commons-io</groupId>    <artifactId>commons-io</artifactId>    <version>2.4</version></dependency> <dependency>    <groupId>org.apache.xmlbeans</groupId>    <artifactId>xmlbeans</artifactId>    <version>2.6.0</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.17</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.17</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml-schemas</artifactId>    <version>3.17</version></dependency>

如果你想简化你的代码，可以加上

<dependency>    <groupId>org.projectlombok</groupId>    <artifactId>lombok</artifactId>    <version>1.16.10</version>    <scope>provided</scope></dependency>

模板示例

新建模板Bean

package com.phil.excel;import lombok.Data;@Datapublic class RefPartExcel {private String partNo;private String partName;private String refPartNo;private String refPartName;;private String length;private String width;}

Lombok 注解在线帮助文档：http://projectlombok.org/features/index
介绍几个常用的 lombok 注解：
@Data   ：注解在类上；提供类所有属性的 get 和 set 方法，此外还提供了equals、canEqual、hashCode、toString 方法
@Setter：注解在属性上；为属性提供 sett方法
@Getter：注解在属性上；为属性提供 get 方法
@Log4j ：注解在类上；为类提供一个 属性名为log 的 log4j 日志对象
@NoArgsConstructor：注解在类上；为类提供一个无参的构造方法
@AllArgsConstructor：注解在类上；为类提供一个全参的构造方法
加一个注解，让属性对应列名

package com.phil.annotation;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Target({ElementType.FIELD})@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface ExcelColumn{public String value() default "";}

这样Bean改写成

package com.phil.excel;import com.phil.annotation.ExcelColumn;import lombok.Data;@Datapublic class RefPartExcel {@ExcelColumn("原零件号")private String partNo;@ExcelColumn("原零件名称")private String partName;@ExcelColumn("参考零件号")private String refPartNo;@ExcelColumn("参考零件名称")private String refPartName;;@ExcelColumn("长")private String length;@ExcelColumn("宽")private String width;}

上传我是采用的MultipartFile，可以读取.xlsx或者.xls格式的Excel(POI3.15以上版本可以兼容，不用再分别读取了)

package com.phil.util;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.HashMap;import java.util.LinkedList;import java.util.List;import java.util.Map;import java.util.regex.Pattern;import javax.servlet.http.HttpServletRequest;import org.apache.commons.io.IOUtils;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;import org.springframework.util.StringUtils;import org.springframework.web.multipart.MultipartFile;import com.phil.annotation.ExcelColumn;public class ImportExcel {private static final DecimalFormat df = new DecimalFormat("0");// 格式化 number为整private static final DecimalFormat df_per = new DecimalFormat("##.00%");//格式化分比格式，后面不足2位的用0补齐//private static final DecimalFormat df_per_ = new DecimalFormat("0.00%");//格式化分比格式，后面不足2位的用0补齐,比如0.00,%0.01%private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串private static final DecimalFormat sc_number  = new DecimalFormat("0.00E000"); //格式化科学计数器private static final Pattern points_ptrn = Pattern.compile("0.0+_*[^/s]+");/** * 对外提供读取excel 的方法 *  */public static List<List<Object>> readExcel(HttpServletRequest request, MultipartFile file) throws IOException {String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();if ("xls".equals(extension) || "xlsx".equals(extension)) {return readExcel(file.getInputStream());} else {throw new IOException("不支持的文件类型");}}/** * 对外提供读取excel 的方法 *  */public static <T> List<T> readExcel(HttpServletRequest request, MultipartFile file, Class<T> cls) throws IOException {String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();if ("xls".equals(extension) || "xlsx".equals(extension)) {return readExcel(file.getInputStream(),cls);} else {throw new IOException("不支持的文件类型");}}/** * 读取 office excel *  * @param stream * @return * @throws IOException */private static List<List<Object>> readExcel(InputStream inputStream) throws IOException {List<List<Object>> list = new LinkedList<List<Object>>();Workbook workbook = null;try {workbook = WorkbookFactory.create(inputStream);int sheetsNumber = workbook.getNumberOfSheets();for (int n = 0; n < sheetsNumber; n++) {Sheet sheet = workbook.getSheetAt(n);Object value = null;Row row = null;Cell cell = null;for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // 从第二行开始读取row = sheet.getRow(i);if (StringUtils.isEmpty(row)) {continue;}List<Object> linked = new LinkedList<Object>();for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {cell = row.getCell(j);if (StringUtils.isEmpty(cell)) {continue;}value = getCellValue(cell);linked.add(value);}list.add(linked);}}} catch (Exception e) {e.printStackTrace();} finally {IOUtils.closeQuietly(workbook);IOUtils.closeQuietly(inputStream);}System.out.println(" linked.size " + list.size());return list;}/** * 获取excel数据 将之转换成bean *  * @param path * @param cls * @param <T> * @return */private static <T> List<T> readExcel(InputStream inputStream, Class<T> cls) {List<T> dataList = new LinkedList<>();//Workbook workbook = null;try {workbook = WorkbookFactory.create(inputStream);Map<String, List<Field>> classMap = new HashMap<>();Field[] fields = cls.getDeclaredFields();for (Field field : fields) {ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);if (annotation != null) {String value = annotation.value();if (!classMap.containsKey(value)) {classMap.put(value, new ArrayList<Field>());}field.setAccessible(true);classMap.get(value).add(field);}}Map<Integer, List<Field>> reflectionMap = new HashMap<>();int sheetsNumber = workbook.getNumberOfSheets();for (int n = 0; n < sheetsNumber; n++) {Sheet sheet = workbook.getSheetAt(n);for (int j = sheet.getRow(0).getFirstCellNum(); j < sheet.getRow(0).getLastCellNum(); j++) { //首行提取注解Object cellValue = getCellValue(sheet.getRow(0).getCell(j)); if (classMap.containsKey(cellValue)) {reflectionMap.put(j, classMap.get(cellValue));}}Row row = null;Cell cell = null;for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {row = sheet.getRow(i);T t = cls.newInstance();for (int j =row.getFirstCellNum(); j < row.getLastCellNum(); j++) {cell = row.getCell(j);System.out.println(cell.getCellTypeEnum());System.out.println(cell.getCellStyle().getDataFormatString());if (reflectionMap.containsKey(j)) {Object cellValue = getCellValue(cell);List<Field> fieldList = reflectionMap.get(j);for (Field field : fieldList) {try {field.set(t, cellValue);} catch (Exception e) {//logger.error()}}}}dataList.add(t);}}} catch (Exception e) {dataList = null;} finally {IOUtils.closeQuietly(workbook);IOUtils.closeQuietly(inputStream);}return dataList;}/** * 获取excel 单元格数据 *  * @param cell * @return */private static Object getCellValue(Cell cell) {Object value = null;switch (cell.getCellTypeEnum()) {case _NONE:break;case STRING:value = cell.getStringCellValue();break;case NUMERIC:if(DateUtil.isCellDateFormatted(cell)){ //日期value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())); } else if("@".equals(cell.getCellStyle().getDataFormatString())|| "General".equals(cell.getCellStyle().getDataFormatString()) || "0_ ".equals(cell.getCellStyle().getDataFormatString())){//文本  or 常规 or 整型数值value = df.format(cell.getNumericCellValue());} else if(points_ptrn.matcher(cell.getCellStyle().getDataFormatString()).matches()){ //正则匹配小数类型value = cell.getNumericCellValue();  //直接显示} else if("0.00E+00".equals(cell.getCellStyle().getDataFormatString())){//科学计数value = cell.getNumericCellValue();//待完善 value = sc_number.format(value);} else if("0.00%".equals(cell.getCellStyle().getDataFormatString())){//百分比value = cell.getNumericCellValue(); //待完善value = df_per.format(value);} else if("# ?/?".equals(cell.getCellStyle().getDataFormatString())){//分数value = cell.getNumericCellValue(); ////待完善} else { //货币value = cell.getNumericCellValue();value = DecimalFormat.getCurrencyInstance().format(value);}break;case BOOLEAN:value = cell.getBooleanCellValue();break;case BLANK://value = ",";break;default:value = cell.toString();}return value;}}

控制层调用

@RequestMapping(value = "/fileUpload",method = RequestMethod.POST,    consumes = "application/json",produces = "application/json")public Map<String, Object> fileUpload(HttpServletRequest request,@RequestParam("file") MultipartFile file) {Map<String, Object> map = new HashMap<String, Object>();// 判断文件是否为空if (!StringUtils.isEmpty(file)) {try {List<RefPartExcel> excelBeans = ImportExcel.readExcel(request,RefPartExcel.class);System.out.println(excelBeans.size());for(RefPartExcel ep : excelBeans){System.out.println(ep.toString());}//........逻辑} catch (Exception e) {e.printStackTrace();}}return map;}

读取成功之后遍历的结果

RefPartExcel [partNo=3739472432, partName=上海, refPartNo=50000001, refPartName=前轮驱动轴螺母1, length=8, width=12]RefPartExcel [partNo=3739472433, partName=湖北, refPartNo=50000002, refPartName=前轮驱动轴螺母2, length=9, width=13]RefPartExcel [partNo=3739472434, partName=陕西, refPartNo=50000003, refPartName=前轮驱动轴螺母3, length=10, width=14]RefPartExcel [partNo=3739472435, partName=河南, refPartNo=50000004, refPartName=前轮驱动轴螺母4, length=11, width=15]RefPartExcel [partNo=3739472436, partName=湖南, refPartNo=50000005, refPartName=前轮驱动轴螺母5, length=12, width=16]

PS：当然了，如果模板的Excel之中文本或者常规有小数，百分比之类的，还是先规范下模板吧。。。