excel 模板导出工具类

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.字段名}