SpringBoot之导出Excel

1、添加springBoot支持

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.13</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.13</version>
</dependency>

2、ExcelUtils编写

package com.wiseda.inno.common.utils.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;

/**
 * @Description: 导出导入excel
 * @author: sjm
 * @date: 2021年04月21日 9:29
 */
public class ExcelUtils {

    private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 文件导出为excel
     * @param titles     excel文件第一行，存列的名字
     * @param objList       对象列表，excel文件中的数据
     */
    public Workbook exportExcel(String[] titles, List<Map<String,Object>> objList, String sheetName, String[] colunms, int[] lens) {
        ExcelUtils fdu = new ExcelUtils();
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet(sheetName);
        String[] title = titles;

        //向文件内填入标头
        fdu.setTitle(sheet,title,lens);

        //向工作表写入数据
        fdu.writeData(objList,sheet,colunms);

        return wb;
    }

    private void setTitle(Sheet sheet, String[] title, int[] lens) {
        Row r = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i,lens[i]*2*256);
            Cell c = r.createCell(i);
            c.setCellValue(title[i]);
        }
    }

    private void writeData(List<Map<String,Object>> list, Sheet sheet, String[] colunms) {

        for (int i = 0; i < list.size(); i++) {
            Row r = sheet.createRow(i+1);
            for (int j = 0; j < colunms.length; j++) {
                Cell c = r.createCell(j);
                Object value = list.get(i).get(colunms[j]);
                c.setCellValue(value == null ? "" : value.toString());
            }
        }
    }
    
}

3、Controller层代码编写

@RequestMapping("/exportExcel")
    public void exportExcel(ActivityQuery params, HttpServletResponse response)  throws IOException {

        OutputStream out = null;
        long t1 = System.currentTimeMillis();
        try {
            List<Map<String, Object>> resultList = activityService.getActivityList(params);
            //excel文件的表头
            String[] titles = {"专题活动名称","征集类别","活动类别","活动范围","开始日期","结束日期","发起部门","发起人","发布时间","已征集","活动状态"};
            //表头对应的列名
            String[] colunms = {"name","activityTypeName","activeName","orgNames","startDate","endDate","orgName","userName","releaseDate","collected","status"};
            String fileName = "专题活动";

//            resultList = changeValue(resultList);
            int[] lens = new int[] {20,10,10,20,6,6,6,6,6,6,6};
            Workbook wb = new ExcelUtils().exportExcel(titles, resultList, fileName, colunms, lens);
            response.reset();
            response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + fileName + ".xls", "UTF-8"));
            response.setContentType("application/vnd.ms-excel");
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        long t2 = System.currentTimeMillis();
        System.out.println(String.format("write over! cost:%sms", (t2 - t1)));
    }