POI之Excel导入
1，maven配置
1 <dependency>
2 <groupId>org.apache.poi</groupId>
3 <artifactId>poi-ooxml</artifactId>
4 <version>${poi.version}</version>
5 </dependency>
2，Controller层
1 /**
2 * Excel导入数据
3 * @return
4 */
5 @ResponseBody
6 @RequestMapping(value="importExcel", method={RequestMethod.GET, RequestMethod.POST})
7 public String importExcel(HttpServletRequest request, String name) {
8 JSONObject result = new JSONObject();
9 result.put("code", 0);
10 result.put("msg", "导入成功！");
11 //初始化解析器
12 CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getServletContext());
13 //解析form中是否有enctype="multipart/form-data"
14 try {
15 if (multipartResolver.isMultipart(request)) {
16 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
17 Iterator iterator = multipartRequest.getFileNames();
18 while (iterator.hasNext()) {
19 MultipartFile multipartFile = multipartRequest.getFile(iterator.next().toString());
20 List<String[]> data = GhPOIUtils.readExcel(multipartFile);
21 Method method = IExportExcelService.class.getDeclaredMethod(name + "Import", new Class[]{List.class});
22 method.invoke(exportExcelService, data);
23 }
24 }
25 } catch(Exception e) {
26 result.put("code", -1);
27 result.put("msg", e.getMessage());
28 }
29 return result.toString();
30 }
3，Util
1 package com.smart.produce.core.utils.comm;
2
3 import java.io.FileNotFoundException;
4 import java.io.IOException;
5 import java.io.InputStream;
6 import java.util.ArrayList;
7 import java.util.List;
8
9 import org.apache.log4j.Logger;
10 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
11 import org.apache.poi.ss.usermodel.Cell;
12 import org.apache.poi.ss.usermodel.Row;
13 import org.apache.poi.ss.usermodel.Sheet;
14 import org.apache.poi.ss.usermodel.Workbook;
15 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
16 import org.springframework.web.multipart.MultipartFile;
17 /**
18 * excel读写工具类 */
19 public class GhPOIUtils {
20 private static Logger logger = Logger.getLogger(GhPOIUtils.class);
21 private final static String xls = "xls";
22 private final static String xlsx = "xlsx";
23
24 public static void checkFile(MultipartFile file) throws IOException{
25 //判断文件是否存在
26 if(null == file){
27 logger.error("文件不存在！");
28 throw new FileNotFoundException("文件不存在！");
29 }
30 //获得文件名
31 String fileName = file.getOriginalFilename();
32 //判断文件是否是excel文件
33 if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
34 logger.error(fileName + "不是excel文件");
35 throw new IOException(fileName + "不是excel文件");
36 }
37 }
38
39 public static Workbook getWorkBook(MultipartFile file) {
40 //获得文件名
41 String fileName = file.getOriginalFilename();
42 //创建Workbook工作薄对象，表示整个excel
43 Workbook workbook = null;
44 try {
45 //获取excel文件的io流
46 InputStream is = file.getInputStream();
47 //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
48 if(fileName.endsWith(xls)){
49 //2003
50 workbook = new HSSFWorkbook(is);
51 }else if(fileName.endsWith(xlsx)){
52 //2007
53 workbook = new XSSFWorkbook(is);
54 }
55 } catch (IOException e) {
56 logger.info(e.getMessage());
57 }
58 return workbook;
59 }
60
61 public static String getCellValue(Cell cell){
62 String cellValue = "";
63 if(cell == null){
64 return cellValue;
65 }
66 //把数字当成String来读，避免出现1读成1.0的情况
67 if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
68 cell.setCellType(Cell.CELL_TYPE_STRING);
69 }
70 //判断数据的类型
71 switch (cell.getCellType()){
72 case Cell.CELL_TYPE_NUMERIC: //数字
73 cellValue = String.valueOf(cell.getNumericCellValue());
74 break;
75 case Cell.CELL_TYPE_STRING: //字符串
76 cellValue = String.valueOf(cell.getStringCellValue());
77 break;
78 case Cell.CELL_TYPE_BOOLEAN: //Boolean
79 cellValue = String.valueOf(cell.getBooleanCellValue());
80 break;
81 case Cell.CELL_TYPE_FORMULA: //公式
82 cellValue = String.valueOf(cell.getCellFormula());
83 break;
84 case Cell.CELL_TYPE_BLANK: //空值
85 cellValue = "";
86 break;
87 case Cell.CELL_TYPE_ERROR: //故障
88 cellValue = "非法字符";
89 break;
90 default:
91 cellValue = "未知类型";
92 break;
93 }
94 return cellValue;
95 }
96
97 /**
98 * 读入excel文件，解析后返回
99 * @param file
100 */
101 public static List<String[]> readExcel(MultipartFile file) throws IOException{
102 //检查文件
103 checkFile(file);
104 //获得Workbook工作薄对象
105 Workbook workbook = getWorkBook(file);
106 //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
107 List<String[]> list = new ArrayList<String[]>();
108 if(workbook != null){
109 for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
110 //获得当前sheet工作表
111 Sheet sheet = workbook.getSheetAt(sheetNum);
112 if(sheet == null){
113 continue;
114 }
115 //获得当前sheet的开始行
116 int firstRowNum = sheet.getFirstRowNum();
117 //获得当前sheet的结束行
118 int lastRowNum = sheet.getLastRowNum();
119 //循环所有行
120 for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){
121 //获得当前行
122 Row row = sheet.getRow(rowNum);
123 if(row == null){
124 continue;
125 }
126 //获得当前行的开始列
127 int firstCellNum = row.getFirstCellNum();
128 //获得当前行的列数
129 int lastCellNum = row.getPhysicalNumberOfCells();
130 String[] cells = new String[row.getPhysicalNumberOfCells()];
131 //循环当前行
132 for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
133 Cell cell = row.getCell(cellNum);
134 cells[cellNum] = getCellValue(cell);
135 }
136 list.add(cells);
137 }
138 }
139 workbook.close();
140 }
141 return list;
142 }
143 }
