EsayPoi模板导出

Love The Way You Lie 2022-05-19 04:06 188阅读 0赞

1.首先导入jar包

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.0.3</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>3.0.3</version> </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.0.3</version>
            </dependency>
            
    
            <dependency>
                <groupId>commons-fileupload</groupId>
                <artifactId>commons-fileupload</artifactId>
                <version>1.3.1</version>
            </dependency>

2.导入工具类

package com.zzf.finals.utiles;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.ExcelImportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import cn.afterturn.easypoi.excel.entity.ImportParams;
    import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.File;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.util.List;
    import java.util.Map;
    import java.util.NoSuchElementException;
    
    public class ExcelUtiles {
        public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
                                       String fileName, boolean isCreateHeader, HttpServletResponse response){
            ExportParams exportParams = new ExportParams(title, sheetName);
            exportParams.setCreateHeadRows(isCreateHeader);
            defaultExport(list, pojoClass, fileName, response, exportParams);
        }
    
        public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,
                                       HttpServletResponse response){
            defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
        }
    
        public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
            defaultExport(list, fileName, response);
        }
    
        private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
                                          HttpServletResponse response, ExportParams exportParams) {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
            if (workbook != null); downLoadExcel(fileName, response, workbook);
        }
    
        private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
            try {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                //throw new NormalException(e.getMessage());
            }
        }
    
        private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
            Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
            if (workbook != null);
            downLoadExcel(fileName, response, workbook);
        }
    
        public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
            if (StringUtils.isBlank(filePath)){
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
                list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
            }catch (NoSuchElementException e){
                //throw new NormalException("模板不能为空");
            } catch (Exception e) {
                e.printStackTrace();
                //throw new NormalException(e.getMessage());
            } return list;
        }
    
            public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
            if (file == null){ return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            }catch (NoSuchElementException e){
               // throw new NormalException("excel文件不能为空");
            } catch (Exception e) {
                //throw new NormalException(e.getMessage());
                System.out.println(e.getMessage());
            }
            return list;
        }
    
    }

3.demo代码

@RequestMapping(value = "/test", method = RequestMethod.GET)
    	public void test(HttpServletResponse response) throws IOException {
    		TemplateExportParams params = new TemplateExportParams("excel/test.xlsx");
    		List<Map<String, String>> List = new ArrayList<Map<String,String>>();
    
    		for(int i=1;i<=9;i++)
    		{
    			Map<String, String> map = new HashMap<String, String>();
    			for(int n=1;n<=9;n++)
    			{
    				map.put("n"+n, (i*n)+"");
    			}
    			List.add(map);
    		}
    		
    		Map<String, Object> map = new HashMap<String, Object>();
    		map.put("lists", List);
    		Workbook workbook = ExcelExportUtil.exportExcel(params, map);
    
    		response.setCharacterEncoding("UTF-8");
    		response.setHeader("content-Type", "application/vnd.ms-excel");
    		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("9*9乘法表", "UTF-8"));
    		workbook.write(response.getOutputStream());
    	}

4.excel的导出模板

![70][]

这个模板的路径是在resources目录下的excel文件夹中。

![70 1][]

Ok,接下来进行测试一下

![70 2][]

![70 3][]

![70 4][]

Ok这样就不需要POI的样式了~

文档地址：http://easypoi.mydoc.io/

参考：https://www.jianshu.com/p/5d67fb720ece

[70]: /images/20220519/143876b0aef246e7bc74cb21d2acf7e4.png
[70 1]: /images/20220519/1a53d2d6a34d459a89ce27890af2d264.png
[70 2]: /images/20220519/d0484d1d3d104f90a3de530d2dba19c4.png
[70 3]: /images/20220519/e1b0ada8458141f7aab6cce1f5e6ce4a.png
[70 4]: /images/20220519/10201cb88b0e4173b8667fa91b043a0c.png