POI操作Excelテーブルシリーズ4---Excelテーブルを作成してデータを埋め込み、ダウンロードする


Webページからデータベースのデータを読み出し、Excel(2007+)テーブルに埋め込み、テーブルをローカルにダウンロードします.
考え方:バックグラウンドでデータベースを読み取る--->Excelテーブルに記入する--->Excelテーブルを一時フォルダに保存する--->一時フォルダからダウンロードする
jspコード
<a class="btn btn-primary" type="button" id="download-excel">Download Excel Menu</a>

JavaScript   

$('#download-excel').click(function(){
    $.ajax({
        url: 'createTempExcel',
        type: 'post',
        dataType: 'json',
        success: function(data){
            if(data.status == 'fail'){
               alert('error');
            }else{
                window.location.href = "downloadExcel?fileName="+data.fileName;
            }
        }

    });

});

コントロール処理コード
@RequestMapping(value = "/createTempExcel", method = RequestMethod.POST, produces = "application/json; charset=utf-8")
@ResponseBody
public Map<String, Object> createTempExcel(HttpServletRequest request) {
    Map<String, Object> result = new HashMap<String, Object>();
    // ---            Excel         ---
    String tempPath = request.getServletContext().getInitParameter("tempPath");
    File dir = new File(tempAbsolutePath);
    if (!dir.exists()) {
        dir.mkdirs();
    }
    String fileName = new Date().getTime() ++ ".xlsx";

    List<Document> docs = new ArrayList<Document>();
    //--- docs          ---
    result = ExcelXSSFUtils.createExcel(docs, tempAbsolutePath, fileName);
    if (result.get("status").equals("fail")) {
        return result;
    }

    result.put("fileName", fileName);
    return result;
}

@RequestMapping(value = "/downloadExcel")
public void downloadExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
    String fileName = request.getParameter("fileName");
    String tempPath = request.getServletContext().getInitParameter("tempPath");
    // ---          ---
    response.setHeader("content-disposition", "attachment;filename=xxx.xlsx");
    response.setContentType("application/octet-stream; charset=utf-8");
    FileInputStream in = new FileInputStream(tempAbsolutePath + fileName);
    OutputStream out = response.getOutputStream();
    byte buffer[] = new byte[1024];
    int len = 0;
    while ((len = in.read(buffer)) > 0) {
        out.write(buffer, 0, len);
    }
    in.close();
    out.close();
}

Excel作成ツールクラス
/**
 *
 *
 * @param data
 *                 
 * @param tempPath
 *                   
 * @param fileName
 *               
 * @return
 */
public static Map<String, Object> createExcel(List<Document> data, String tempPath, String fileName) {
    Map<String, Object> result = new HashMap<String, Object>();
    String[] textList = { "true", "false" };

    XSSFWorkbook  wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = null;
    XSSFCell cell = null;

    // --- rowHeader ---
    row = sheet.createRow(0);
    cell = row.createCell(0);
    //--     header ---

    // ---             ---
    DataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet) sheet);
    XSSFDataValidationConstraint constraintBoolean = new XSSFDataValidationConstraint(textList);
    CellRangeAddressList regionsBoolean = new CellRangeAddressList(1, 500, 6, 11);
    DataValidation validationBoolean = helper.createValidation(constraintBoolean, regionsBoolean);
    validationBoolean.createErrorBox("     ", "       ");
    validationBoolean.setShowErrorBox(true);
    sheet.addValidationData(validationBoolean);

    // ---               ---
    DataValidationConstraint constraintNum = new XSSFDataValidationConstraint(
            DataValidationConstraint.ValidationType.INTEGER,
            DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0");
    CellRangeAddressList regionNumber = new CellRangeAddressList(1, 500, 4, 5);
    DataValidation validationNum = helper.createValidation(constraintNum, regionNumber);
    validationNum.createErrorBox("       ", "   ,        0    ");
    validationNum.setShowErrorBox(true);
    sheet.addValidationData(validationNum);

    // ---               ---
    DataValidationConstraint constraintDecimal = new XSSFDataValidationConstraint(
            DataValidationConstraint.ValidationType.DECIMAL,
            DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0");
    CellRangeAddressList regionDecimal = new CellRangeAddressList(1, 500, 3, 3);
    DataValidation validationDecimal = helper.createValidation(constraintDecimal, regionDecimal);
    validationDecimal.createErrorBox("       ", "   ,        0    ");
    validationDecimal.setShowErrorBox(true);
    sheet.addValidationData(validationDecimal);

    for (int i = 0; i < menuItems.size(); i++) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        XSSFColor xssfColor = new XSSFColor();
        Document item = menuItems.get(i);

        // --- get cell value ---
        String code = data.getString("code");
        String color = "FF"
                + (item.getString("color") != null ? item.getString("color").substring(1,
                        item.getString("color").length()) : "FFFFFF");
       //---         data      ---
        xssfColor.setARGBHex(color);
        cellStyle.setFillForegroundColor(xssfColor);
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //    
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//    
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);//    
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);//    

        // --- insert cell value ---
        row = sheet.createRow(i + 1);
        cell = row.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(code);
    }

    FileOutputStream out;
    try {
        out = new FileOutputStream(tempPath + fileName);
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        result.put("status", "fail");
        result.put("message", "Database Error.");
        return result;
    } catch (IOException e) {
        result.put("status", "fail");
        result.put("message", "Database Error.");
        return result;
    }

    result.put("status", "success");
    return result;

}