excel导出

1、查出要导出的数据

2、将需要的数据每条封装成一个数组

3、先设置导出栏的头是什么

 public void exportSchool(@Principal String userId, @Valid EsCommonQueryDto queryDto, HttpServletResponse response){
        UserDTO userDTO = userClient.getUserById(userId);
        List<EsSchoolScoreDto> esSchoolScoreDtoList = esSchoolScoreService.listSchoolScoreByPhaseId(queryDto, userDTO.getUnitId());
        List<List<String>> dataList = new ArrayList<>();
        List<String> inList;
        int order = 0;
        for(EsSchoolScoreDto e : esSchoolScoreDtoList){
            order++;
            inList = new ArrayList<>();
            inList.add(order+"");
            inList.add(e.getStudentName());
            inList.add(e.getIdentityCard());
            inList.add(e.getExamNumber());
            inList.add(e.getClassName());
            inList.add(e.getSubjectName());
            if(e.getScore()!= null){
                inList.add(e.getScore().toString());
            }else{
                inList.add("");
            }
            if(StringUtils.isNotBlank(e.getScoreStatus()) && "0".equals(e.getScoreStatus())){
                inList.add("正常");
            }else if("1".equals(e.getScoreStatus())){
                inList.add("缺考");
            }else if("2".equals(e.getScoreStatus())){
                inList.add("作弊");
            }else {
                inList.add("");
            }
            dataList.add(inList);
        }

        // 设置Excel格式
        List<String> titleList = new ArrayList<>();
        titleList.add("序号");
        titleList.add("姓名");
        titleList.add("身份证号");
        titleList.add("考号");
        titleList.add("班级");
        titleList.add("科目");
        titleList.add("成绩");
        titleList.add("状态");

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        //标题行固定
        sheet.createFreezePane(0, 1);
        //单元格样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        HSSFFont titleFont = workbook.createFont();
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //标题行内容
        HSSFRow titleRow = sheet.createRow(0);
        for(int i=0;i<titleList.size();i++){
            sheet.setColumnWidth(i, 10 * 256);
            HSSFCell titleCell = titleRow.createCell(i);
            titleCell.setCellValue(new HSSFRichTextString(titleList.get(i)));
            titleCell.setCellStyle(titleStyle);
        }

        HSSFRow row;
        HSSFCell cell;
        int rowNum = 1;
        for (List<String> data : dataList) {
            row = sheet.createRow(rowNum++);
            row.setHeightInPoints((1.5f) * sheet.getDefaultRowHeightInPoints());
            for(int i=0;i<titleList.size();i++){
                cell = row.createCell(i);
                cell.setCellValue(new HSSFRichTextString(data.get(i)));
                cell.setCellStyle(style);
            }
        }
        String fileName = "成绩信息";
        ExportUtils.outputData(workbook, fileName, response);
    } 

4、设置样式

5、ExportUtils 输出流

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

@Slf4j
public class ExportUtils {

    /**
     * 组织文件下载
     *
     * @param filePath
     *            文件绝对路径
     * @param fileName
     *            下载的文件名称
     * @throws IOException
     */
    public static void outputFile(String filePath, String fileName, HttpServletResponse resp)
            throws IOException {
        outputFile(new File(filePath), fileName, resp);
    }

    public static void outputFile(File file, String fileName, HttpServletResponse resp)
            throws IOException {
        resp.setContentType("application/octet-stream");
        resp.setHeader("Content-Disposition",
                "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));

        FileInputStream fromStream = null;
        BufferedOutputStream buff = null;
        try {
            buff = new BufferedOutputStream(resp.getOutputStream());
            fromStream = new FileInputStream(file);
            byte[] buffer = new byte[4096];
            int bytes_read;
            while ((bytes_read = fromStream.read(buffer)) != -1) {
                buff.write(buffer, 0, bytes_read);
            }
            buff.flush();
        }
        finally {
            close(buff);
            close(fromStream);
        }
    }

    public static void outputData(HSSFWorkbook workbook, String fileName, HttpServletResponse resp) {
        try {
            fileName = URLEncoder.encode(fileName + ".xls", "utf-8");
        }
        catch (UnsupportedEncodingException e1) {
            e1.printStackTrace();
        }
        resp.setContentType("application/octet-stream");
        resp.setHeader("Content-Disposition",
                "attachment; filename=" + fileName);
        OutputStream out = null;
        try {
            out = new BufferedOutputStream(resp.getOutputStream());
            // 输出文件
            workbook.write(out);
            out.flush();
        }
        catch (IOException e) {
            log.error(e.toString());
        }
        finally {
            close(out);
        }
    }

    public static void close(OutputStream out) {
        if (out == null) {
            return;
        }
        try {
            out.close();
        }
        catch (IOException e) {
        }
    }

    private static void close(InputStream out) {
        if (out == null) {
            return;
        }

        try {
            out.close();
        }
        catch (IOException e) {
        }
    }
}