导出excel工具类

❤️

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下载并打开

代码下载地址：https://github.com/ourlang/excel