POI匯出之我的實踐篇

做了不少Excel匯出 終于弄能夠靈活運用POI實作Excel匯出了，下面是我的實踐案例，可以根據下面代碼進行整合：

String[] titles = new String[]{"序號","檔號","文號","責任者","題名","日期","密級","頁數","備注"};
        String[] titlesvar = new String[]{"order","dh","wh","zrz","tm","rq","mj","ys","bz"};
        List<Map<String,String>> rows = new ArrayList<>();
        StringBuffer querySql = new StringBuffer("SELECT ELEARCHIVEFILECODE,BT,ARCHIVEYEAR,DEOTORPROBLEM,BMQX_TEXT,MJ_TEXT,ISPUBLIC_TEXT,NGDW,FWH,CWRQ,GWYS,NGR,NGRQ,ZTC,PUBLICTYPE_TEXT,QZH,HAVEPAPERS,HAVEPAPERS_TEXT,DYFS FROM OA_ARCHIVES WHERE ROWSTATE = 1 ");
        if (StringUtil.isNotBlank(bmqx)){
            querySql.append(" AND bmqx IN (" + SqlUtil.dealCondition(bmqx) + ")");
        }
        if (StringUtil.isNotBlank(deotorproblem)){
            querySql.append(" AND deotorproblem like concat('%','"+deotorproblem+"','%') ");
        }
        if (StringUtil.isNotBlank(archiveyear)){
            querySql.append("  AND archiveyear like concat('"+archiveyear+"','%') ");
        }

        DataTable dataTable = DBManager.getDataTable(new Sql(querySql.toString()));
        List<ArchivesSheet> sheetEntities = DataTableKit.parseList(dataTable, ArchivesSheet.class);
        sheetEntities.stream().forEach(e->{
            if (StringUtil.isNotBlank(e.getCwrq())){
                String cwrq = e.getCwrq();
                e.setCwrq(cwrq.substring(0,4)+cwrq.substring(5,7)+cwrq.substring(8,10));
            }
        });
        Map<String, String> rowItem = null;
        if (sheetEntities != null && sheetEntities.size() > 0) {
            for (int i = 0; i < sheetEntities.size(); i++) {
                ArchivesSheet sheet = sheetEntities.get(i);
                rowItem = new HashMap<>();
                rowItem.put("order", String.valueOf(i + 1));
                rowItem.put("dh", sheet.getElearchivefilecode());
                rowItem.put("wh", sheet.getFwh());
                rowItem.put("zrz", sheet.getNgdw());
                rowItem.put("tm", sheet.getBt());
                rowItem.put("rq", sheet.getCwrq());
                rowItem.put("bz", sheet.getHavepapers() == 1 ? "" : "電子");
                rowItem.put("mj", sheet.getMjText());
                rowItem.put("ys", sheet.getGwys());
                rows.add(rowItem);
            }
        }
        //創建作業簿物件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //創建作業表物件
        HSSFSheet sheet = workbook.createSheet();
        //串列頭樣式
        HSSFFont font = ExcelUtils.getFont(workbook, "宋體", (short)20, false);
        HSSFCellStyle titleStyle = ExcelUtils.getTitleStyle(workbook, font, HorizontalAlignment.CENTER);
        //串列體樣式
        font = ExcelUtils.getFont(workbook, "宋體", (short)14, false);
        HSSFCellStyle bodyStyle = ExcelUtils.getStyle(workbook, font,HorizontalAlignment.LEFT);
        //作業表物件設定寬度setColumnWidth這個方法寬度的單位是字符數的256分之一
       sheet.setColumnWidth(0,  20*256+184);
        //設定表格最上面標題
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell_10 = row1.createCell(0);
        row1.setHeightInPoints(40);
        cell_10.setCellStyle(titleStyle);
        cell_10.setCellValue("歸檔檔案目錄");
        CellRangeAddress region = new CellRangeAddress(0, 0, 0,titles.length-1 );
        sheet.addMergedRegion(region);
        for (int i = 0; i < titles.length ; i++) {
            //設定列寬
            sheet.setColumnWidth(i, 20*256+184);
            //設定表頭
            HSSFRow row = sheet.getRow(1);
            if (row == null) {
                 row = sheet.createRow(1);
            }
            //設定行高度
            row.setHeightInPoints(20);
            //創建單元格
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(bodyStyle);
            //設定內容
            for (int j = 0; j < rows.size(); j++) {
                //資料內容從第三行開始
                row = sheet.getRow(j + 2);
                if (row == null) {
                    row = sheet.createRow(j + 2);
                }
                row.setHeightInPoints(20);
                //在當前行創建第i個單元格
                cell = row.createCell(i);
                //rows.get(i)為第幾行；rows.get(j).get(titles.get(i))為第幾行的啥欄位 這里可以考慮用物件反射
                cell.setCellValue(rows.get(j).get(titlesvar[i]));
                cell.setCellStyle(bodyStyle);
            }
        }
        ExcelUtils.doDownload(workbook, "歸檔檔案目錄下載", response);

工具類ExcelUtils.doDownLoad的原始碼為下面的doDownLoad方法：

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package cn.dreamit.p1000.util;

import java.io.IOException;
import java.net.URLEncoder;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class ExcelUtils {
    public ExcelUtils() {
    }

    public static HSSFFont getFont(HSSFWorkbook workbook, String name, short size, boolean bold) {
        HSSFFont font = workbook.createFont();
        font.setFontName(name);
        font.setFontHeightInPoints(size);
        font.setBold(bold);
        return font;
    }

    public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook, HSSFFont font, HorizontalAlignment alignment) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(alignment);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setLocked(true);
        style.setWrapText(true);
        return style;
    }

    public static HSSFCellStyle getStyle(HSSFWorkbook workbook, HSSFFont font, HorizontalAlignment alignment) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(alignment);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setLocked(true);
        style.setWrapText(true);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    public static void doDownload(HSSFWorkbook workbook, String fileName, HttpServletResponse response) {
        response.setContentType("application/x-download;charset=UTF-8");

        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (Exception var15) {
            var15.printStackTrace();
        }

        response.addHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + fileName + ".xls");
        ServletOutputStream out = null;

        try {
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        } catch (IOException var14) {
            var14.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException var13) {
                    var13.printStackTrace();
                }
            }

        }

    }
}

//下載的Excel內容如下