Java导出Excel工具类

1. Excel工具类:

package com.tymk.backend.wxchart.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;

/**
 * Excel工具类(两个重载方法,一般用第二个)
 */
public class ExcelUtil {
    /**
     * Excel表格导出
     * @param response HttpServletResponse对象
     * @param excelData Excel表格的数据，封装为List<List<String>>
     * @param sheetName sheet的名字
     * @param fileName 导出Excel的文件名
     * @param columnWidth Excel表格的宽度
     * @throws IOException 抛IO异常
     */
    public static void exportExcel(HttpServletResponse response,
                                   List<List<String>> excelData,
                                   String sheetName,
                                   String fileName,
                                   int columnWidth) throws IOException {
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        //生成一个表格，设置表格名称
        HSSFSheet sheet = workbook.createSheet(sheetName);

        //设置表格列宽度
        sheet.setDefaultColumnWidth(columnWidth);

        //写入List<List<String>>中的数据
        int rowIndex = 0;
        for(List<String> data : excelData){
            //创建一个row行，然后自增1
            HSSFRow row = sheet.createRow(rowIndex++);

            //遍历添加本行数据
            for (int i = 0; i < data.size(); i++) {
                //创建一个单元格
                HSSFCell cell = row.createCell(i);

                //创建一个内容对象
                HSSFRichTextString text = new HSSFRichTextString(data.get(i));

                //将内容对象的文字内容写入到单元格中
                cell.setCellValue(text);
            }
        }
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html");
        response.setHeader("Content-Disposition", "attachment; filename="+ fileName);
        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        OutputStream os = response.getOutputStream();
        workbook.write(os);
        //刷新缓冲
        os.flush();
        os.close();
        //关闭workbook
        workbook.close();

    }

    public static void exportExcel(HttpServletRequest request, HttpServletResponse response,
                                   List<List<String>> excelData, String fileName,
                                   HSSFWorkbook workbook, HSSFSheet sheet) {
        //写入List<List<String>>中的数据
        int rowIndex = 0;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)10);
        font.setFontName("微软雅黑");
        cellStyle.setFont(font);
        for(List<String> data : excelData){
            //创建一个row行，然后自增1
            HSSFRow row = sheet.createRow(rowIndex++);

            //遍历添加本行数据
            for (int i = 0; i < data.size(); i++) {
                //创建一个单元格
                HSSFCell cell = row.createCell(i);

                //创建一个内容对象
                HSSFRichTextString text = new HSSFRichTextString(data.get(i));

                //将内容对象的文字内容写入到单元格中
                cell.setCellValue(text);
                //设置居中
                cell.setCellStyle(cellStyle);
            }
        }
        try {
            String userAgent = request.getHeader("User-Agent");
            // 针对IE或者以IE为内核的浏览器：
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {
                // 非IE浏览器的处理：
                fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            }
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("UTF-8");
            response.setContentType("text/html");
            response.setHeader("Content-Disposition", "attachment; filename="+ fileName);
            //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
            OutputStream os = response.getOutputStream();
            workbook.write(os);
            //刷新缓冲
            os.flush();
            os.close();
            //关闭workbook
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

2. 一般在具体的导出Excel中设置每列的宽度:

    private HSSFSheet setSheetFields(HSSFWorkbook workbook){
        HSSFSheet sheet = workbook.createSheet("关键字信息表");
        sheet.setColumnWidth(0,10*256);
        sheet.setColumnWidth(1,13*256);
        sheet.setColumnWidth(2,13*256);
        sheet.setColumnWidth(3,11*256);
        sheet.setColumnWidth(4,23*256);
        sheet.setColumnWidth(5,10*256);
        sheet.setColumnWidth(6,14*256);
        sheet.setColumnWidth(7,40*256);
        sheet.setColumnWidth(8,23*256);
        sheet.setColumnWidth(9,23*256);
        return sheet;
    }

3. 一般在Controller接口中定义如下调用上面的方法:

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = setSheetFields(workbook);
        ExcelUtil.exportExcel(request,response, dataList,"keyword.xls",workbook,sheet);