POI操作Excelテーブルシリーズ4---Excelテーブルを作成してデータを埋め込み、ダウンロードする
6726 ワード
Webページからデータベースのデータを読み出し、Excel(2007+)テーブルに埋め込み、テーブルをローカルにダウンロードします.
考え方:バックグラウンドでデータベースを読み取る--->Excelテーブルに記入する--->Excelテーブルを一時フォルダに保存する--->一時フォルダからダウンロードする
jspコード
コントロール処理コード
Excel作成ツールクラス
考え方:バックグラウンドでデータベースを読み取る--->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;
}