前提是导入POI相关jar包
1.html
<button class="btn-search" οnclick="exploreExcel()">导出报表</button>
2.js
function exploreExcel () {
var startTime = $("#input-start-time").val();
var endTime = $("#input-end-time").val();
var duleStatus = $("#select-status option:selected").val();
var sourceType = $("#select-rog option:selected").val();
var _a = $("<a target='_blank' ></a>").get(0);
$(_a).attr("href", 'exploreEvents.do?' + "startTime=" + startTime +"&endTime=" + endTime + "&duleStatus=" + duleStatus + "&sourceType=" + sourceType);
_a.click();
}
3.java后台实现
protected boolean ExploreEventToExcel ( List<CEventInfoT> datas ,HttpServletResponse response) {
String projectName = "历史事件表";
response.setContentType("application/msexcel;charset=UTF-8");
String fileName;
OutputStream os = null;
try {
fileName = "attachment; filename=" + URLEncoder.encode(projectName, "UTF-8") + System.currentTimeMillis() + ".xls";
response.setHeader("Content-disposition", fileName);
os = response.getOutputStream();
} catch (Exception e1) {
e1.printStackTrace();
}
HSSFWorkbook workbook ;
int i = 0;
try {
// 新建工作薄和表单,并且初始化单元格大小
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("历史事件表");
HSSFRow row = null;
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 17));
sheet.setDefaultRowHeightInPoints(15);
sheet.setDefaultColumnWidth(10);
// sheet.createFreezePane(0, 1);// 冻结标题窗口
// Sheet 页自适应页面大小
PrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short) 1);
ps.setFitWidth((short) 1);
// 写入标题行(title),并设置标题行单元格的格式
// 字体格式设置
HSSFFont workFont = workbook.createFont();
workFont.setFontName("等线");
workFont.setFontHeightInPoints((short) 11);
// 单元格格式设置
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.BLACK.index);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // 背景色
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中或者靠左靠右
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//
//设置数据格式
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
cellStyle.setFont(workFont);
row = sheet.createRow(i);
HSSFCell cellTitle = row.createCell(0);
cellTitle.setCellStyle(cellStyle);
cellTitle.setCellValue(projectName);
HSSFCell cellTitle17 = row.createCell(17);
cellTitle17.setCellStyle(cellStyle);
row = sheet.createRow(++i);
int rIndex = 0;
String[] title = {"序号","事件时间", "事件地点", "事件类型", "求助来源", "事件状态", " 救助人员"};
HSSFCellStyle cellStyleTitle = workbook.createCellStyle();
for (int j = 0; j < title.length ; j++) {
HSSFCell cell = row.createCell(rIndex++);
cellStyleTitle = cellStyle;
cell.setCellStyle(cellStyleTitle);
cell.setCellValue(title[j]);
}
// 写入内行
int size = datas == null ? 0 : datas.size();
if(size > 0) {
for(int k = 0; k < size && k < 65535; k++){
CEventInfoT e = datas.get(k);
row = sheet.createRow(++i);
HSSFCell cell0 = row.createCell(0);
cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
cell0.setCellStyle(cellStyle);
cell0.setCellValue(k + 1);
HSSFCell cell1 = row.createCell(1);
cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1.setCellStyle(cellStyle);
cell1.setCellValue(e.getOccurDate());
HSSFCell cell2 = row.createCell(2);
cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
cell2.setCellStyle(cellStyle);
cell2.setCellValue(e.getAlarmAddress() == null ? "" : e.getAlarmAddress());
HSSFCell cell3 = row.createCell(3);
cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(e.getEventTypeValue() == null ? "" : e.getEventTypeValue());
HSSFCell cell4 = row.createCell(4);
cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
cell4.setCellStyle(cellStyle);
cell4.setCellValue(e.getSourceType() == null ? "" : e.getSourceType());
HSSFCell cell5 = row.createCell(5);
cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
cell5.setCellStyle(cellStyle);
cell5.setCellValue(e.getDuleStatus() == null ? "" : e.getDuleStatus());
HSSFCell cell6 = row.createCell(6);
cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
cell6.setCellStyle(cellStyle);
cell6.setCellValue(e.getResponserName() == null ? "" : e.getResponserName());
}
}
workbook.getSheetAt(0).setForceFormulaRecalculation(true);
workbook.write(os);
return true;
} catch(Exception e) {
e.printStackTrace();
return false;
}
}