Spring使用POI实现Excel导入导出

 Apache POI 是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。

       简单理解就是通过POI，java可以与office建立联系。

       本次项目实践基于SSM框架，简单封装了Excel批量导入导出功能，实现过程如下

       1. maven导入java包：

[html]  view plain  copy

1.   <dependency>  
2. <groupId>org.apache.poi</groupId>    
3. <artifactId>poi-ooxml</artifactId>    
4. <version>3.5-FINAL</version>    
5.  </dependency>   

       2. 建立Excel实体--ExcelBean

[java]  view plain  copy

1. /** 
2.  *  
3. * @Description: 导入导出excel 
4. * @author haipeng 
5. * @date 2017年4月11日 
6.  */  
7. public class ExcelBean implements java.io.Serializable {  
8.      private String headTextName;//列头（标题）名  
9.      private String propertyName;//对应字段名  
10.      private Integer cols;//合并单元格数  
11.      private XSSFCellStyle cellStyle;  
12.        
13.      public ExcelBean(){  
14.            
15.      }  
16.      public ExcelBean(String headTextName, String propertyName){  
17.          this.headTextName = headTextName;  
18.          this.propertyName = propertyName;  
19.      }  
20.        
21.      public ExcelBean(String headTextName, String propertyName, Integer cols) {  
22.          super();  
23.          this.headTextName = headTextName;  
24.          this.propertyName = propertyName;  
25.          this.cols = cols;  
26.      }   
27.        
28.      public String getHeadTextName() {  
29.         return headTextName;  
30.     }  
31.   
32.     public void setHeadTextName(String headTextName) {  
33.         this.headTextName = headTextName;  
34.     }  
35.   
36.     public String getPropertyName() {  
37.         return propertyName;  
38.     }  
39.   
40.     public void setPropertyName(String propertyName) {  
41.         this.propertyName = propertyName;  
42.     }  
43.   
44.     public Integer getCols() {  
45.         return cols;  
46.     }  
47.   
48.     public void setCols(Integer cols) {  
49.         this.cols = cols;  
50.     }  
51.   
52.     public XSSFCellStyle getCellStyle() {  
53.         return cellStyle;  
54.     }  
55.   
56.     public void setCellStyle(XSSFCellStyle cellStyle) {  
57.         this.cellStyle = cellStyle;  
58.     }  
59. }  

      3.  封装Excel工具类--ExcelUtils

[java]  view plain  copy

1. public class ExcelUtils {  
2.     private final static String excel2003L =".xls";    //2003- 版本的excel    
3.     private final static String excel2007U =".xlsx";   //2007+ 版本的excel    
4.     /*************************************文件上传****************************/  
5.     public static  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{    
6.         List<List<Object>> list = null;    
7.             
8.         //创建Excel工作薄    
9.         Workbook work = getWorkbook(in,fileName);    
10.         if(null == work){    
11.             throw new Exception("创建Excel工作薄为空！");    
12.         }    
13.         Sheet sheet = null;    
14.         Row row = null;    
15.         Cell cell = null;    
16.             
17.         list = new ArrayList<List<Object>>();    
18.         //遍历Excel中所有的sheet    
19.         for (int i = 0; i < work.getNumberOfSheets(); i++) {    
20.             sheet = work.getSheetAt(i);    
21.             if(sheet==null){continue;}    
22.                 
23.             //遍历当前sheet中的所有行    
24.             for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j++) {    
25.                 row = sheet.getRow(j);    
26.                 if(row==null||row.getFirstCellNum()==j){continue;}    
27.                     
28.                 //遍历所有的列    
29.                 List<Object> li = new ArrayList<Object>();    
30.                 for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {    
31.                     cell = row.getCell(y);    
32.                     li.add(getCellValue(cell));    
33.                 }    
34.                 list.add(li);    
35.             }    
36.         }    
37. //        work.close();    
38.         return list;    
39.     }    
40.         
41.     /**  
42.      * 描述：根据文件后缀，自适应上传文件的版本   
43.      * @param inStr,fileName  
44.      * @return  
45.      * @throws Exception  
46.      */    
47.     public static  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{    
48.         Workbook wb = null;    
49.         String fileType = fileName.substring(fileName.lastIndexOf("."));    
50.         if(excel2003L.equals(fileType)){    
51.             wb = new HSSFWorkbook(inStr);  //2003-    
52.         }else if(excel2007U.equals(fileType)){    
53.             wb = new XSSFWorkbook(inStr);  //2007+    
54.         }else{    
55.             throw new Exception("解析的文件格式有误！");    
56.         }    
57.         return wb;    
58.     }    
59.     
60.     /**  
61.      * 描述：对表格中数值进行格式化  
62.      * @param cell  
63.      * @return  
64.      */    
65.     public static  Object getCellValue(Cell cell){    
66.         Object value = null;    
67.         DecimalFormat df = new DecimalFormat("0");  //格式化number String字符    
68.         SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化    
69.         DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字    
70.             
71.         switch (cell.getCellType()) {    
72.         case Cell.CELL_TYPE_STRING:    
73.             value = cell.getRichStringCellValue().getString();    
74.             break;    
75.         case Cell.CELL_TYPE_NUMERIC:    
76.             if("General".equals(cell.getCellStyle().getDataFormatString())){    
77.                 value = df.format(cell.getNumericCellValue());    
78.             }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){    
79.                 value = sdf.format(cell.getDateCellValue());    
80.             }else{    
81.                 value = df2.format(cell.getNumericCellValue());    
82.             }    
83.             break;    
84.         case Cell.CELL_TYPE_BOOLEAN:    
85.             value = cell.getBooleanCellValue();    
86.             break;    
87.         case Cell.CELL_TYPE_BLANK:    
88.             value = "";    
89.             break;    
90.         default:    
91.             break;    
92.         }    
93.         return value;    
94.     }    
95.     /****************************************上传结束*************************************** 
96.     /** 
97.          * 多列头创建EXCEL 
98.          *  
99.          * @param sheetName 工作簿名称 
100.          * @param clazz  数据源model类型 
101.          * @param objs   excel标题列以及对应model字段名 
102.          * @param map  标题列行数以及cell字体样式 
103.          * @return 
104.          * @throws IllegalArgumentException 
105.          * @throws IllegalAccessException 
106.          * @throws InvocationTargetException 
107.          * @throws ClassNotFoundException 
108.          * @throws IntrospectionException 
109.          * @throws ParseException 
110.          */  
111.     public static XSSFWorkbook createExcelFile(Class clazz, List objs,Map<Integer, List<ExcelBean>> map,String sheetName) throws IllegalArgumentException,IllegalAccessException,  
112.     InvocationTargetException, ClassNotFoundException, IntrospectionException, ParseException{  
113.             // 创建新的Excel 工作簿  
114.             XSSFWorkbook workbook = new XSSFWorkbook();  
115.             // 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称  
116.             XSSFSheet sheet = workbook.createSheet(sheetName);  
117.             // 以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析;  
118.             createFont(workbook);//字体样式  
119.             createTableHeader(sheet, map);//创建标题（头）  
120.             createTableRows(sheet, map, objs, clazz);//创建内容  
121.             return workbook;  
122.         }  
123.     private static XSSFCellStyle fontStyle;  
124.     private static XSSFCellStyle fontStyle2;  
125.     public static void createFont(XSSFWorkbook workbook) {  
126.         // 表头  
127.         fontStyle = workbook.createCellStyle();  
128.         XSSFFont font1 = workbook.createFont();  
129.         font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  
130.         font1.setFontName("黑体");  
131.         font1.setFontHeightInPoints((short) 14);// 设置字体大小  
132.         fontStyle.setFont(font1);  
133.         fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框  
134.         fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框  
135.         fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框  
136.         fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框  
137.         fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中  
138.   
139.         // 内容  
140.         fontStyle2=workbook.createCellStyle();  
141.         XSSFFont font2 = workbook.createFont();  
142.         font2.setFontName("宋体");  
143.         font2.setFontHeightInPoints((short) 10);// 设置字体大小  
144.         fontStyle2.setFont(font2);       
145.         fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框  
146.         fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框  
147.         fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框  
148.         fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框  
149.         fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中  
150.     }  
151.       
152.     /** 
153.      * 根据ExcelMapping 生成列头（多行列头） 
154.      *  
155.      * @param sheet 
156.      *            工作簿 
157.      * @param map 
158.      *            每行每个单元格对应的列头信息 
159.      */  
160.     public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map) {  
161.         int startIndex=0;//cell起始位置  
162.         int endIndex=0;//cell终止位置  
163.   
164.         for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {  
165.             XSSFRow row = sheet.createRow(entry.getKey());  
166.             List<ExcelBean> excels = entry.getValue();  
167.             for (int x = 0; x < excels.size(); x++) {  
168.                 //合并单元格  
169.                 if(excels.get(x).getCols()>1){  
170.                     if(x==0){                                       
171.                         endIndex+=excels.get(x).getCols()-1;  
172.                         CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);  
173.                         sheet.addMergedRegion(range);  
174.                         startIndex+=excels.get(x).getCols();  
175.                     }else{  
176.                         endIndex+=excels.get(x).getCols();  
177.                         CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);  
178.                         sheet.addMergedRegion(range);  
179.                         startIndex+=excels.get(x).getCols();  
180.                     }  
181.                     XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());  
182.                     cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容  
183.                     if (excels.get(x).getCellStyle() != null) {  
184.                         cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式  
185.                     }  
186.                     cell.setCellStyle(fontStyle);  
187.                 }else{  
188.   
189.                     XSSFCell cell = row.createCell(x);  
190.                     cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容  
191.                     if (excels.get(x).getCellStyle() != null) {  
192.                         cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式  
193.                     }  
194.                     cell.setCellStyle(fontStyle);  
195.                 }  
196.   
197.             }  
198.         }  
199.     }  
200.       
201.     /** 
202.      *  
203.      * @param sheet 
204.      * @param map 
205.      * @param objs 
206.      * @param clazz 
207.      */  
208.     @SuppressWarnings("rawtypes")  
209.     public static void createTableRows(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map, List objs, Class clazz)  
210.             throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,  
211.             ClassNotFoundException, ParseException {  
212.   
213.         int rowindex = map.size();  
214.         int maxKey = 0;  
215.         List<ExcelBean> ems = new ArrayList<>();  
216.         for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {  
217.             if (entry.getKey() > maxKey) {  
218.                 maxKey = entry.getKey();  
219.             }  
220.         }  
221.         ems = map.get(maxKey);  
222.   
223.         List<Integer> widths = new ArrayList<Integer>(ems.size());  
224.         for (Object obj : objs) {  
225.             XSSFRow row = sheet.createRow(rowindex);  
226.             for (int i = 0; i < ems.size(); i++) {  
227.                 ExcelBean em = (ExcelBean) ems.get(i);  
228.             // 获得get方法   
229.                 PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);  
230.                 Method getMethod = pd.getReadMethod();  
231.                 Object rtn = getMethod.invoke(obj);  
232.                 String value = "";  
233.                 // 如果是日期类型 进行 转换  
234.                 if (rtn != null) {  
235.                     if (rtn instanceof Date) {  
236.                         value = DateUtils.date2String((Date) rtn,"yyyy-MM-dd");  
237.                     } else if(rtn instanceof BigDecimal){  
238.                         NumberFormat nf = new DecimalFormat("#,##0.00");  
239.                         value=nf.format((BigDecimal)rtn).toString();  
240.                     } else if((rtn instanceof Integer) && (Integer.valueOf(rtn.toString())<0 )){  
241.                         value="--";  
242.                     }else {  
243.                         value = rtn.toString();  
244.                     }  
245.                 }  
246.                 XSSFCell cell = row.createCell(i);  
247.                 cell.setCellValue(value);  
248.                 cell.setCellType(XSSFCell.CELL_TYPE_STRING);  
249.                 cell.setCellStyle(fontStyle2);  
250.                 // 获得最大列宽  
251.                 int width = value.getBytes().length * 300;  
252.                 // 还未设置，设置当前  
253.                 if (widths.size() <= i) {  
254.                     widths.add(width);  
255.                     continue;  
256.                 }  
257.                 // 比原来大，更新数据  
258.                 if (width > widths.get(i)) {  
259.                     widths.set(i, width);  
260.                 }  
261.             }  
262.             rowindex++;  
263.         }  
264.         // 设置列宽  
265.         for (int index = 0; index < widths.size(); index++) {  
266.             Integer width = widths.get(index);  
267.             width = width < 2500 ? 2500 : width + 300;  
268.             width = width > 10000 ? 10000 + 300 : width + 300;  
269.             sheet.setColumnWidth(index, width);  
270.         }  
271.     }  
272. }  

      4.  在HTML页面导入需要的js

[html]  view plain  copy

1. <script type="text/javascript" src="${ctxPath}/js/jquery-form.js"></script>  

      5.  在HTML添加测试控件

[java]  view plain  copy

1. <form id="uploadForm" enctype="multipart/form-data" method="post">   
2.     <input id="upfile" type="file" name="upfile">  
3.     <input type="button" value="导入" id="upLoadPayerCreditInfoExcel" name="btn">  
4. </form>  
5. <a href="${ctxPath}/creditInfo/downLoadReceverCreditInfoExcel.html">导出</a>  

      6. 导出通过a标签的get请求即可以实现，导入则通过ajax的post请求实现：

[java]  view plain  copy

1. $('#upLoadPayerCreditInfoExcel').click(function(){  
2.     var cacheVersion=$("#cacheVersion").val();  
3.     if(checkData()){    
4.         $('#uploadForm').ajaxSubmit({      
5.             url:$("#root").val()+'/creditInfo/uploadReceiverCreditInfoExcel.html',  
6.             data:{'cacheVersion':cacheVersion},  
7.             dataType: 'text'  
8.         });     
9.     }    
10. });    
11.   
12. //JS校验form表单信息    
13. function checkData(){    
14.    var fileDir = $("#upfile").val();    
15.    var suffix = fileDir.substr(fileDir.lastIndexOf("."));    
16.    if("" == fileDir){    
17.        alert("选择需要导入的Excel文件！");    
18.        return false;    
19.    }    
20.    if(".xls" != suffix && ".xlsx" != suffix ){    
21.        alert("选择Excel格式的文件导入！");    
22.        return false;    
23.    }    
24.    return true;    
25. }  

       7. controller端实现

       导出：

[java]  view plain  copy

1. @RequestMapping(value = "/downLoadPayerCreditInfoExcel", method = RequestMethod.GET)  
2.     @ResponseBody  
3.     public void downLoadPayerCreditInfoExcel(HttpServletRequest request,HttpServletResponse response,HttpSession session){  
4.         response.reset();  
5.         SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmssms");  
6.         String dateStr = sdf.format(new Date());  
7.         Long companyId=UserUtils.getCompanyIdBySession(session);  
8.         Map<String,Object> map=new HashMap<String,Object>();  
9.         // 指定下载的文件名  
10.         response.setHeader("Content-Disposition", "attachment;filename=" +dateStr+".xlsx");  
11.         response.setContentType("application/vnd.ms-excel;charset=UTF-8");  
12.         response.setHeader("Pragma", "no-cache");  
13.         response.setHeader("Cache-Control", "no-cache");  
14.         response.setDateHeader("Expires", 0);  
15.   
16.         XSSFWorkbook workbook=null;  
17.         try {  
18.             //导出Excel对象  
19.             workbook = creditInfoService.exportPayerCreditInfoExcel(companyId);  
20.         } catch (IllegalArgumentException | IllegalAccessException  
21.                 | InvocationTargetException | ClassNotFoundException  
22.                 | IntrospectionException | ParseException e1) {  
23.             e1.printStackTrace();  
24.         }  
25.         OutputStream output;  
26.         try {  
27.             output = response.getOutputStream();  
28.           
29.             BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);  
30.             bufferedOutPut.flush();  
31.             workbook.write(bufferedOutPut);  
32.             bufferedOutPut.close();  
33.               
34.         } catch (IOException e) {  
35.             e.printStackTrace();  
36.         }  
37.     }  

       导入：

[java]  view plain  copy

1. @ResponseBody    
2.     @RequestMapping(value="uploadPayerCreditInfoExcel",method={RequestMethod.GET,RequestMethod.POST})    
3.     Public void uploadPayerCreditInfoExcel(HttpServletRequest request,HttpServletResponse response,HttpSession session) throws Exception {    
4.         MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;      
5.         MultipartFile file = multipartRequest.getFile("upfile");    
6.         if(file.isEmpty()){    
7.             throw new Exception("文件不存在！");    
8.         }    
9.         Long companyId=UserUtils.getCompanyIdBySession(session);  
10.         Long userId=UserUtils.getUserIdBySession(session);  
11.         InputStream in = file.getInputStream();  
12.         creditInfoService.uploadPayerCreditInfoExcel(in,file,companyId,userId);  
13.         in.close();  
14.         PrintWriter out = null;    
15.         response.setCharacterEncoding("utf-8");  //防止ajax接受到的中文信息乱码    
16.         out = response.getWriter();    
17.         out.print("文件导入成功！");    
18.         out.flush();    
19.         out.close();    
20.     }  

       8、service层

 

          导入，从excel中获得数据放入List<List<object>>中，然后遍历放入实体，执行插入操作：

[java]  view plain  copy

1. public void uploadPayerCreditInfoExcel(InputStream in, MultipartFile file,Long companyId,Long userId) throws Exception {  
2.     List<List<Object>> listob = ExcelUtils.getBankListByExcel(in,file.getOriginalFilename());    
3.     List<CreditInfoBean> creditInfoList=new ArrayList<CreditInfoBean>();  
4.     for (int i = 0; i < listob.size(); i++) {    
5.             List<Object> ob = listob.get(i);    
6.             CreditInfoBean creditInfoBean = new CreditInfoBean();  
7.             creditInfoBean.setCompanyName(String.valueOf(ob.get(0)));  
8.             creditInfoBean.setBillType(String.valueOf(ob.get(1)));  
9.             creditInfoBean.setBillNumber(String.valueOf(ob.get(2)));  
10.             BigDecimal bd=new BigDecimal(String.valueOf(ob.get(3)));     
11.             creditInfoBean.setBuyerBillAmount(bd.setScale(2, BigDecimal.ROUND_HALF_UP));  
12.             creditInfoBean.setReceiveTime(String.valueOf(ob.get(4)));  
13.             creditInfoBean.setBuyerRemark(String.valueOf(ob.get(5)));  
14.             creditInfoList.add(creditInfoBean);  
15.         }    
16. }  

         导出：

[java]  view plain  copy

1. public XSSFWorkbook exportPayerCreditInfoExcel(Long companyId) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, ClassNotFoundException, IntrospectionException, com.sun.tools.example.debug.expr.ParseException {  
2.         List<CreditInfoBean> creditInfoList=creditInfoDao.listAllPayerCreditInfoPage(companyId);  
3.         List<ExcelBean> ems=new ArrayList<>();  
4.         Map<Integer,List<ExcelBean>>map=new LinkedHashMap<>();  
5.         XSSFWorkbook book=null;  
6.         ems.add(new ExcelBean("供应商名称","companyName",0));  
7.         ems.add(new ExcelBean("票据类型","billType",0));  
8.         ems.add(new ExcelBean("票据号","billNumber",0));  
9. //      ems.add(new ExcelBean("买方是否参与","isBuyerIquidation",0));  
10.         ems.add(new ExcelBean("票据金额","buyerBillAmount",0));  
11.         ems.add(new ExcelBean("应付日期","buyerPayTime",0));  
12.         ems.add(new ExcelBean("剩余天数","overplusDays",0));  
13.         ems.add(new ExcelBean("状态","buyerBillStatus",0));  
14.         map.put(0, ems);  
15.         List<CreditInfoBean> afterChangeList=changeBuyerStatus(creditInfoList);  
16.         book=ExcelUtils.createExcelFile(CreditInfoBean.class, afterChangeList, map, "应付账款信息");  
17.         return book;  
18. }