在页面上查询数据后导出Excel表

我参考已有的一个功能做的一个导出一个Excel。

设置好我们需要的Excel表

private void addExcelHeader(WritableSheet ws) throws Exception {
try {
Label label = null;
SheetSettings sheetSettings = ws.getSettings();
sheetSettings.setVerticalFreeze(2);
ws.setRowView(0, 400);// 设置行高
ws.setRowView(1, 400);
ws.setRowView(2, 400);
ws.setRowView(3, 400);
ws.setRowView(4, 400);
ws.setRowView(5, 400);

ws.setColumnView(0, 14);// 设置列宽
ws.setColumnView(1, 28);
ws.setColumnView(2, 15);
ws.setColumnView(3, 20);
ws.setColumnView(4, 24);
ws.setColumnView(5, 24);

WritableFont wf_header = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD);


// 头部
WritableCellFormat wcf_header = new WritableCellFormat(wf_header);
wcf_header.setAlignment(Alignment.CENTRE);
wcf_header.setVerticalAlignment(VerticalAlignment.CENTRE);
// wcf_header.setBorder(Border.ALL, BorderLineStyle.THIN);
label = new Label(0, 0, "标题", wcf_header);
ws.addCell(label);

ws.mergeCells(0, 0, 5, 0);//合并单元格


WritableFont wf_title = new WritableFont(WritableFont.ARIAL, 12);
WritableCellFormat wcf_title = new WritableCellFormat(wf_title);
wcf_title.setAlignment(Alignment.RIGHT);
WritableCellFormat wcf_table = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10));
wcf_table.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf_table.setWrap(true);
wcf_table.setAlignment(Alignment.CENTRE);
wcf_table.setVerticalAlignment(VerticalAlignment.CENTRE);
String[] tableHeaders = new String[] { "姓名", "年龄", "性别", "爱好", "特长", "备注" };
for (int i = 0; i < tableHeaders.length; i++) {
label = new Label(i, 1, tableHeaders[i], wcf_table);
ws.addCell(label);
}
}
catch (Exception e) {
throw new Exception(e.getMessage());
}


}

往Excel表中传数据

private void putDataOnOutputStream(OutputStream os, List<AxkfflBO> vlist) throws Exception {
WritableWorkbook workbook = null;
try {
Label label = null;
workbook = Workbook.createWorkbook(os);
WritableSheet ws = workbook.createSheet("Sheet1", 0);
addExcelHeader(ws);
//设置传入数据的边框
WritableCellFormat wcf_table = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10));
wcf_table.setBorder(Border.ALL, BorderLineStyle.THIN);
//表头固定字段使用属性
WritableCellFormat wcf_table1 = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10));
wcf_table1.setWrap(true);
wcf_table1.setAlignment(Alignment.LEFT);
wcf_table1.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_table1.setBorder(Border.ALL, BorderLineStyle.THIN);

for (int i = 0; i < vlist.size(); i++) {
AxkfflBO bo = vlist.get(i);
ws.setRowView(2+i, 400);//高度设置为400

label = new Label(0, 2 + i, bo.getAac003(),wcf_table);
ws.addCell(label);
label = new Label(1, 2 + i, bo.getAac002(),wcf_table);
ws.addCell(label);
label = new Label(2, 2 + i, bo.getAah002(),wcf_table);
ws.addCell(label);
label = new Label(3, 2 + i, bo.getAae036(),wcf_table);
ws.addCell(label);
label = new Label(4, 2 + i, bo.getAah003(),wcf_table);
ws.addCell(label);
label = new Label(5, 2 + i, bo.getAaa028(),wcf_table);
ws.addCell(label);

}
workbook.write();
workbook.close();
os.close();
}
catch (Exception e) {
throw new Exception(e.getMessage());
}finally{
if(os != null){
os.close();
}
}
}

将Excel写到输出流输出

public InputStream getaxkmxExcel(ActionLogDTO dto) throws Exception {

List<AxkfflBO> oList=actionLogDAO.queryAxkffmx(dto);

ByteArrayOutputStream out = new ByteArrayOutputStream();
putDataOnOutputStream(out, nList);
return new ByteArrayInputStream(out.toByteArray());
}