Spring+SpringMVC+MyBatis +apche poi 实现excel 导出工具类封装

Bertha 。 2023-10-17 00:52 17阅读 0赞

第一：apache poi excel工具类封装【POIUtil】

package com.wlsq.kso.util;
    
    import org.apache.poi.hssf.usermodel.*;  
    
    import java.io.FileOutputStream;  
    import java.io.IOException;  
    import java.util.Calendar;  
    import java.util.List;  
    import java.util.Map;  
      
    /** 
     * poi 导出excel 工具类 
     */  
    public class POIUtil {  
      
        /** 
         * 1.创建 workbook 
         * @return 
         */  
        public HSSFWorkbook getHSSFWorkbook(){  
            return new HSSFWorkbook();  
        }  
      
        /** 
         * 2.创建 sheet 
         * @param hssfWorkbook 
         * @param sheetName sheet 名称 
         * @return 
         */  
        public HSSFSheet getHSSFSheet(HSSFWorkbook hssfWorkbook, String sheetName){  
            return hssfWorkbook.createSheet(sheetName);  
        }  
      
        /** 
         * 3.写入表头信息 
         * @param hssfWorkbook 
         * @param hssfSheet 
         * @param headInfoList List<Map<String, Object>> 
         *              key: title         列标题 
         *                   columnWidth   列宽 
         *                   dataKey       列对应的 dataList item key 
         */  
        public void writeHeader(HSSFWorkbook hssfWorkbook,HSSFSheet hssfSheet ,List<Map<String, Object>> headInfoList){  
            HSSFCellStyle cs = hssfWorkbook.createCellStyle();  
            HSSFFont font = hssfWorkbook.createFont();  
            font.setFontHeightInPoints((short)12);  
            font.setBoldweight(font.BOLDWEIGHT_BOLD);  
            cs.setFont(font);  
            cs.setAlignment(cs.ALIGN_CENTER);  
      
            HSSFRow r = hssfSheet.createRow(0);  
            r.setHeight((short) 380);  
            HSSFCell c = null;  
            Map<String, Object> headInfo = null;  
            //处理excel表头  
            for(int i=0, len = headInfoList.size(); i < len; i++){  
                headInfo = headInfoList.get(i);  
                c = r.createCell(i);  
                c.setCellValue(headInfo.get("title").toString());  
                c.setCellStyle(cs);  
                if(headInfo.containsKey("columnWidth")){  
                    hssfSheet.setColumnWidth(i, (short)(((Integer)headInfo.get("columnWidth") * 8) / ((double) 1 / 20)));  
                }  
            }  
        }  
      
        /** 
         * 4.写入内容部分 
         * @param hssfWorkbook 
         * @param hssfSheet 
         * @param startIndex 从1开始，多次调用需要加上前一次的dataList.size() 
         * @param headInfoList List<Map<String, Object>> 
         *              key: title         列标题 
         *                   columnWidth   列宽 
         *                   dataKey       列对应的 dataList item key 
         * @param dataList 
         */  
        public void writeContent(HSSFWorkbook hssfWorkbook,HSSFSheet hssfSheet ,int startIndex,  
                                         List<Map<String, Object>> headInfoList, List<Map<String, Object>> dataList){  
            Map<String, Object> headInfo = null;  
            HSSFRow r = null;  
            HSSFCell c = null;  
            //处理数据  
            Map<String, Object> dataItem = null;  
            Object v = null;  
            for (int i=0, rownum = startIndex, len = (startIndex + dataList.size()); rownum < len; i++,rownum++){  
                r = hssfSheet.createRow(rownum);  
                r.setHeightInPoints(16);  
                dataItem = dataList.get(i);  
                for(int j=0, jlen = headInfoList.size(); j < jlen; j++){  
                    headInfo = headInfoList.get(j);  
                    c = r.createCell(j);  
                    v = dataItem.get(headInfo.get("dataKey").toString());  
      
                    if (v instanceof String) {  
                        c.setCellValue((String)v);  
                    }else if (v instanceof Boolean) {  
                        c.setCellValue((Boolean)v);  
                    }else if (v instanceof Calendar) {  
                        c.setCellValue((Calendar)v);  
                    }else if (v instanceof Double) {  
                        c.setCellValue((Double)v);  
                    }else if (v instanceof Integer  
                            || v instanceof Long  
                            || v instanceof Short  
                            || v instanceof Float) {  
                        c.setCellValue(Double.parseDouble(v.toString()));  
                    }else if (v instanceof HSSFRichTextString) {  
                        c.setCellValue((HSSFRichTextString)v);  
                    }else {  
                        c.setCellValue(v.toString());  
                    }  
                }  
            }  
        }  
      
        public void write2FilePath(HSSFWorkbook hssfWorkbook, String filePath) throws IOException{  
            FileOutputStream fileOut = null;  
            try{  
                fileOut = new FileOutputStream(filePath);  
                hssfWorkbook.write(fileOut);  
            }finally{  
                if(fileOut != null){  
                    fileOut.close();  
                }  
            }  
        }  
      
      
        /** 
         * 导出excel 
         * code example: 
             List<Map<String, Object>> headInfoList = new ArrayList<Map<String,Object>>(); 
             Map<String, Object> itemMap = new HashMap<String, Object>(); 
             itemMap.put("title", "序号1"); 
             itemMap.put("columnWidth", 25); 
             itemMap.put("dataKey", "XH1"); 
             headInfoList.add(itemMap); 
     
             itemMap = new HashMap<String, Object>(); 
             itemMap.put("title", "序号2"); 
             itemMap.put("columnWidth", 50); 
             itemMap.put("dataKey", "XH2"); 
             headInfoList.add(itemMap); 
     
             itemMap = new HashMap<String, Object>(); 
             itemMap.put("title", "序号3"); 
             itemMap.put("columnWidth", 25); 
             itemMap.put("dataKey", "XH3"); 
             headInfoList.add(itemMap); 
     
             List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>(); 
             Map<String, Object> dataItem = null; 
             for(int i=0; i < 100; i++){ 
             dataItem = new HashMap<String, Object>(); 
             dataItem.put("XH1", "data" + i); 
             dataItem.put("XH2", 88888888f); 
             dataItem.put("XH3", "脉兜V5.."); 
             dataList.add(dataItem); 
             } 
             POIUtil.exportExcel2FilePath("test sheet 1","F:\\temp\\customer2.xls", headInfoList, dataList); 
     
         * @param sheetName   sheet名称 
         * @param filePath   文件存储路径， 如：f:/a.xls 
         * @param headInfoList List<Map<String, Object>> 
         *                           key: title         列标题 
         *                                columnWidth   列宽 
         *                                dataKey       列对应的 dataList item key 
         * @param dataList  List<Map<String, Object>> 导出的数据 
         * @throws java.io.IOException 
         * 
         */  
        public static void exportExcel2FilePath(String sheetName, String filePath,  
                                       List<Map<String, Object>> headInfoList,  
                                       List<Map<String, Object>> dataList) throws IOException {  
            POIUtil poiUtil = new POIUtil();  
            //1.创建 Workbook  
            HSSFWorkbook hssfWorkbook = poiUtil.getHSSFWorkbook();  
            //2.创建 Sheet  
            HSSFSheet hssfSheet = poiUtil.getHSSFSheet(hssfWorkbook, sheetName);  
            //3.写入 head  
            poiUtil.writeHeader(hssfWorkbook, hssfSheet, headInfoList);  
            //4.写入内容  
            poiUtil.writeContent(hssfWorkbook, hssfSheet, 1, headInfoList, dataList);  
            //5.保存文件到filePath中  
            poiUtil.write2FilePath(hssfWorkbook, filePath);  
        }  
      
    }

第二、项目引用

/*开发者数据导出功能*/
    	@RequestMapping(value = "/develop_export.action")
    	@ResponseBody
    	public void Export(HttpServletRequest request) throws IOException {	
    		Developer developer = new Developer();
            Integer pageNum = 1;
    		if (request.getParameter("pageNum") != null) {
    			pageNum = Integer.parseInt(request.getParameter("pageNum"));					
    		}
    		developer.setPageNum((pageNum - 1) * 10);
    		developer.setPageSize(10);
    		List<Developer> develpers = developerService
    				.selectByDevelopers(developer);
    		
    		 List<Map<String, Object>> headInfoList = new ArrayList<Map<String,Object>>(); 
             Map<String, Object> itemMap = new HashMap<String, Object>(); 
             itemMap.put("title", "开发者编号"); 
             itemMap.put("columnWidth", 25); 
             itemMap.put("dataKey", "XH1"); 
             headInfoList.add(itemMap); 
             
             itemMap = new HashMap<String, Object>(); 
             itemMap.put("title", "账户"); 
             itemMap.put("columnWidth", 50); 
             itemMap.put("dataKey", "XH2"); 
             headInfoList.add(itemMap); 
             
             itemMap = new HashMap<String, Object>(); 
             itemMap.put("title", "唯一编号"); 
             itemMap.put("columnWidth", 50); 
             itemMap.put("dataKey", "XH3"); 
             headInfoList.add(itemMap); 
     
             itemMap = new HashMap<String, Object>(); 
             itemMap.put("title", "真实姓名"); 
             itemMap.put("columnWidth", 50); 
             itemMap.put("dataKey", "XH4"); 
             headInfoList.add(itemMap); 
     
             List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>(); 
             Map<String, Object> dataItem = null; 
             for(int i=0; i < develpers.size(); i++){ 
             dataItem = new HashMap<String, Object>(); 
             Developer de=develpers.get(i);
             dataItem.put("XH1", ""+de.getAcctId()); 
             dataItem.put("XH2", ""+de.getUsername()); 
             dataItem.put("XH3", ""+de.getOpenId()); 
             dataItem.put("XH4", ""+de.getAcctRealNm()); 
             dataList.add(dataItem); 
             } 
             POIUtil.exportExcel2FilePath("统一认证平台开发者数据信息","D:\\temp\\customer2.xls", headInfoList, dataList); 
    		
    
    	}

第三、结果展示：  
![Center][]

[Center]: https://img-blog.csdn.net/20160613190411121?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center