导出excel工具类

今天药忘吃喽~ 2021-12-14 02:35 521阅读 0赞

1、excel工具类代码如下

package com.spring.excel.utils;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.util.StringUtils;
    
    import javax.servlet.ServletOutputStream;
    import java.io.IOException;
    import java.lang.reflect.Field;
    import java.util.Collection;
    import java.util.List;
    import java.util.Map;
    /**
     * 导出excel工具类
     * @author 福小林
     */
    public class ExcelUtils {
        /**
         * 日志框架对象
         */
        private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
        private ExcelUtils() {
    
        }
        /**
         *
         * @param titleMap excel标题map key-->标题对应的英文属性，value-->对应的中文标题名称
         * @param out  输出流对象
         * @param list 需要打印在excel上面的数据集合
         * @param <T> 泛型标识
         */
        public static <T> void exportExcel(Map<String,String> titleMap,ServletOutputStream out, List<T> list) {
            try{
                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFSheet hssfSheet = getRows(titleMap, workbook);
                setExcelData(titleMap, list, hssfSheet);
                closeServletOutputStream(out, workbook);
            }catch(Exception e){
                logger.error("导出excel信息失败！"+e.getMessage(),e);
            }
        }
    
        /**
         * 关闭excel导出的各种流
         * @param out  输出流对象
         * @param workbook   HSSFWorkbook对象
         * @throws IOException 可能抛出的IO异常
         */
        private static void closeServletOutputStream(ServletOutputStream out, HSSFWorkbook workbook) throws IOException {
            workbook.write(out);
            out.flush();
            out.close();
        }
        /**
         * 把数据放入excel表格中
         * @param titleNames  excel标题map key-->标题对应的英文属性，value-->对应的中文标题名称
         * @param list 需要打印在excel上面的数据集合
         * @param hssfSheet HSSFSheet对象
         * @param <T> 泛型标识
         * @throws Exception 异常
         */
        private static <T>  void setExcelData(Map<String,String> titleNames,List<T> list, HSSFSheet hssfSheet) throws  Exception {
            HSSFRow row;
            //循环数据，打印到excel
            for (int i = 0; i < list.size(); i++) {
                row = hssfSheet.createRow(i+1);
                //获取list里面的对象
                T t = list.get(i);
                //如果list集合的类型是Map<String,Object>
                if (t instanceof Map){
                    Map map=(Map) t;
                    int k=0;
                    for(Map.Entry<String,String> entry :titleNames.entrySet()){
                        //标题英文字段
                        String key = entry.getKey();
                        Object obj = map.get(key);
                        //excel每个单元格插入的数据
                        String excelStr=getObjStr(obj);
                        row.createCell(k).setCellValue(excelStr);
                        k++;
                    }
                    //如果list集合的类型是实体类对象
                }else {
                    int k=0;
                    for(Map.Entry<String,String> entry :titleNames.entrySet()){
                        String key = entry.getKey();
                        //获取对应实体类Class对象
                        Class < ?>cls = t.getClass();
                        //把t的数据赋值给obj
                        Object obj = cls.cast(t);
                        //获取标题对应的哪一个字段对象
                        Field field = cls.getDeclaredField(key);
                        //可以访问私有字段数据
                        field.setAccessible(true);
                        //获取对应的字段值
                        Object fieldValue = field.get(obj);
                        //excel每个单元格插入的数据
                        String excelStr = getObjStr(fieldValue);
                        row.createCell(k).setCellValue(excelStr);
                        k++;
                    }
                }
            }
        }
    
        /**
         * 获取对象的字符串
         * @param obj Object对象
         * @return  String
         */
        private static String getObjStr(Object obj) {
            if (StringUtils.isEmpty(obj)){
                return "";
            }else {
                return obj.toString();
            }
        }
    
    
        /**
         *
         * @param titleMap excel标题map key-->标题对应的英文属性，value-->对应的中文标题名称
         * @param workbook HSSFWorkbook对象
         * @return HSSFSheet已经把标题和样式设置和打印完成的对象
         */
        private static  HSSFSheet getRows(Map<String,String> titleMap, HSSFWorkbook workbook) {
            Collection<String> valueCollection = titleMap.values();
            final int size = valueCollection.size();
            String[] titles = new String[size];
            titleMap.values().toArray(titles);
            HSSFSheet hssfSheet = workbook.createSheet("sheet1");
            HSSFRow row = hssfSheet.createRow(0);
            HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
            hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
            HSSFCell hssfCell ;
            for (int i = 0; i < titles.length; i++) {
                hssfCell = row.createCell(i);
                hssfCell.setCellValue(titles[i]);
                hssfCell.setCellStyle(hssfCellStyle);
            }
            return hssfSheet;
        }
    
    }

2、调用导出excel示例

package com.spring.excel.controller;
    
    import com.spring.excel.entity.User;
    import com.spring.excel.utils.ExcelUtils;
    import org.apache.commons.collections4.map.LinkedMap;
    import org.springframework.util.StringUtils;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.ResponseBody;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 导出excel数据
     * @author 福小林
     */
    @RestController
    public class ExcelController {
    
    
        /**
         *
         * @param response  浏览器相应
         * @param request  获取前端传入的值 测试暂时未用
         * @return String
         */
        @RequestMapping("/exportExcel")
        @ResponseBody
        public  String exportExcel(HttpServletResponse response, HttpServletRequest request){
            try{
                response.setContentType("application/binary;charset=UTF-8");
                ServletOutputStream out=response.getOutputStream();
                //设置文件头：最后一个参数是设置下载文件名(这里我们叫：张三.pdf)
                String excelName="导出的数据表格";
                response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(excelName+".xls", "UTF-8"));
                //用作传入的参数不正确时，返回给浏览器的提示信息
                if (StringUtils.isEmpty(excelName)){
                    response.getWriter().print("传入参数不全!");
                    return "传入参数不全";
                }
               //拼装测试数据
                LinkedMap<String,String> titleMap=new LinkedMap<>();
                titleMap.put("name","姓名");
                titleMap.put("age","年龄");
                titleMap.put("address","居住地址");
                titleMap.put("email","邮箱");
                titleMap.put("telephone","电话号码");
                List<User> list = new ArrayList<>();
                list.add(new User("张三","28","成都市","zhangsan@126.com","13312345678"));
                list.add(new User("李四","36","中江县","lisi@163.com","18998765432"));
                list.add(new User("王五","75","北京市","wangwu@126.com","15676543456"));
                list.add(new User("赵六","47","上海市","zhaoliu@126.com","1387656789"));
                ExcelUtils.exportExcel(titleMap, out,list);
                return "success";
            } catch(Exception e){
                return "导出信息失败";
            }
        }
    }

3、需要导入的java包

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.16</version>
       </dependency>
       
      <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>javax.servlet-api</artifactId>
                <version>3.1.0</version>
                <scope>provided</scope>
            </dependency>

4、下载表格  
在浏览器上输入http://localhost:8080/exportExcel下载并打开  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70]

代码下载地址：https://github.com/hello-google/excel

参考文档 https://blog.csdn.net/yudiandemingzi/article/details/80204794

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70]: /images/20211213/c396363bfcf14b479b1131fc72558615.png