Java常用代码实现——导出excel

/**
 * 导出excel相关的参数
 */
private static final String FILE_NAME = "package-book";
private static final String[] PACKAGE_EXPORT_TITLE =
        new String[]{"bookId", "书名", "作者", "性别分类", "书籍一级分类", "下载链接"};
        
/**
 * 导出excel
 */
@RequestMapping(value = {"/export"}, method = RequestMethod.GET)
public void exportPackageInfo(@ModelAttribute PackageQueryDto queryDto, HttpServletResponse response) {
    BaseResultDto resultDto = null;
    List<PackageBookDto> result = packageBookService.getExportData(queryDto);
    try {
        if (result != null && result.size() > 0) {
            List<String[]> packageBooks = new ArrayList<>();
            for (PackageBookDto packageBook : result) {
                packageBooks.add(new String[]{packageBook.getBookId(), packageBook.getBookName(), packageBook.getAuthor(),
                        packageBook.getSexCategory(), packageBook.getBookPrimaryCategory(),
                        packageBook.getDownloadUrl()});
            }
            ExportDataUtil util =
                    new ExportDataUtil(FILE_NAME, response, PACKAGE_EXPORT_TITLE, packageBooks);
            util.export();
        } else {
            resultDto = new BaseResultDto(ResultStatusEnum.EXPORT_PACKAGE_INFO_ERROR);
            response.setContentType("application/json; charset=UTF-8");
            response.getWriter().write(GsonUtil.getInstance().toJson(resultDto));
        }
    } catch (Exception e) {
        ScribeLogger.error(logEvent, "export package book exception, %s", e);
    }
}

import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * 导出excel 工具类
 */
public class ExportDataUtil {

    private static final Logger logger = LoggerFactory.getLogger(ExportDataUtil.class);

    private final String[] title;
    private final List<String[]> contents;
    private final HttpServletResponse response;
    private final String fileName;

    public ExportDataUtil(String fileName, HttpServletResponse response, String[] title, List<String[]> contents) {
        this.response = response;
        this.title = title;
        this.contents = contents;
        this.fileName = fileName;
    }

    public void export() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = createSheet(wb, fileName);
        HSSFCellStyle style = wb.createCellStyle();

        //data tuple
        List<String[]> tuples = new ArrayList<>();
        //add title
        tuples.add(title);

        for (String[] temp : contents) {
            tuples.add(temp);
        }
        // create excel
        int i = 0, j = 0;
        for (String[] temp : tuples) {
            HSSFRow row = sheet.createRow(i++);
            for (String str : temp) {
                HSSFCell cell = row.createCell(j++);
                cell.setCellStyle(style);
                cell.setCellValue(str);
            }
            j = 0;
        }
        //send to client
        response.reset();
        response.setContentType("text/plain;charset=UTF-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        try {
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            logger.error("导出excel失败：：", e);
        } finally {
            try {
                response.getOutputStream().close();
            } catch (IOException e) {
                logger.error("关闭excel流error：：", e);
            }
        }
    }

    public static HSSFSheet createSheet(HSSFWorkbook wb, String sheetName) {
        HSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultColumnWidth(12);
        sheet.setGridsPrinted(false);
        return sheet;
    }
}