excel 模板导出工具类

电玩女神 2024-04-03 12:12 113阅读 0赞

### ExportExcelUtil 工具类 ###

package com.***.utils.excel;
    
    import net.sf.jxls.exception.ParsePropertyException;
    import net.sf.jxls.transformer.XLSTransformer;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.IOException;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Set;
    
    /**
     * excel模板导出
     */
    public class ExportExcelUtil {
        
    
        /**
         * 模板导出
         *
         * @param objectMap
         * @param tempPath
         * @param dataPath
         * @return
         */
        public static boolean ExportData(Map<String, Object> objectMap, String tempPath, String dataPath) {
        
            try {
        
                XLSTransformer former = new XLSTransformer();
                former.transformXLS(tempPath, objectMap, dataPath);
            } catch (ParsePropertyException e) {
        
                e.printStackTrace();
            } catch (InvalidFormatException e) {
        
                e.printStackTrace();
            } catch (IOException e) {
        
                e.printStackTrace();
            }
            return true;
        }
    
        /**
         * @param args
         */
    //    public static void main(String[] args) throws Exception {
        
    //        List<SysTask> list = new ArrayList<SysTask>();
    //
    //        SysTask user1 = new SysTask();
    //        user1.setTaskName("李四1");
    //        user1.setId(1L);
    //        list.add(user1);
    //
    //        String srcFilePath = "C:\\Users\\Administrator\\Desktop\\1234.xlsx";
    //        String destFilePath = "C:\\Users\\Administrator\\Desktop\\result.xlsx";
    //
    //        Map<String, Object> beanParams = new HashMap<String, Object>();
    //        beanParams.put("list", list);
    //        beanParams.put("contract","合同名称");
    //        XLSTransformer former = new XLSTransformer();
    //        former.transformXLS(srcFilePath, beanParams, destFilePath);
    //        System.out.println("the end !!!");
    //    }
    
        /**
         * 根据参数二维List，创建一个sheet的Excel，并返回XSSFWorkbook
         * @param rowList
         * @return
         */
        public static XSSFWorkbook createExcelResult(List<List> rowList) {
        
            Map<String,List<List>> map=new HashMap<>();
            map.put("sheet",rowList);
            return createExcelResult(map);
        }
    
        /**
         * 根据参数map的二维List，创建多个sheet的Excel，并返回XSSFWorkbook
         * @param sheetMap
         * @return
         */
        public static XSSFWorkbook createExcelResult(Map<String,List<List>> sheetMap) {
        
            //创建工作薄对象
            XSSFWorkbook workbook=new XSSFWorkbook();//这里也可以设置sheet的Name
            if(sheetMap.size()>0){
        
                Set<Map.Entry<String,List<List>>> entrySet=sheetMap.entrySet();
                for (Map.Entry<String,List<List>> entry:entrySet) {
        
                    //创建工作表对象
                    XSSFSheet sheet = workbook.createSheet(entry.getKey());
                    List<List> rowList=entry.getValue();
                    for(int r=0;r<rowList.size();r++){
        
                        //创建工作表的行
                        XSSFRow row = sheet.createRow(r);//设置第i行，从零开始
                        List cellList=rowList.get(r);
                        for (int c=0;c<cellList.size();c++) {
        
                            XSSFCell cell=row.createCell(c);
                            if(null!=cellList.get(c)){
        
                                cell.setCellValue(cellList.get(c).toString());
                            }
                        }
                    }
                }
            }
            return workbook;
        }
    }

### 使用案例 ###

//查询任务列表
            List<Map<String, Object>> maps = eleDetailMapper.selectList(eleDetail);
            //导出的map集合
            Map<String, Object> beanParams = new HashMap<>();
            beanParams.put("list",maps);
            //默认地址+时间+文件名称
            String exportUrl = "C:/Users/XXXX/fsdownload" + FileUtils.getTempPath() + "本月列表" + UUID.randomUUID().toString() +".xlsx";
            FileUtils.createFile(exportUrl);//创建文件
            boolean result = ExportExcelUtil.ExportData(beanParams, "C:/Users/XXX/ELEDETAIL-TEMPLATE.xlsx", exportUrl);
            if (result) {
        
                File uploadFile = new File(exportUrl);
                return AjaxResult.success("本地下载成功");
            }

### 模板就是$\{list.字段名\} ###