package com.util.report;
import java.util.Date;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import com.ibm.icu.text.SimpleDateFormat;
public abstract class ReportExcelUltity {
private HttpServletResponse response;
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private HSSFRow row;
private String fileName;
private String[] header;
private String title;
private int lineNum; //从0开始
private HSSFCellStyle contentCellStyle;
private int [][] storeWidth = new int[100][1];
void setConfiguration(String fileName) {
//response.setContentType("ms-excel");
response.setContentType("application/ms-excel" );
response.setHeader("Content-Disposition", "attachment;filename="
+ fileName + ".xls");
sheet = workbook.createSheet(fileName);
}
void setTitle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
Font font = workbook.createFont();
font.setFontHeightInPoints((short)16);
font.setColor(HSSFColor.BLUE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setBoldweight((short)10);
style.setFont(font);
sheet.addMergedRegion(new Region(0,(short)0,1,(short)(header.length-1))); //标题
sheet.addMergedRegion(new Region(2,(short)0,2,(short)(header.length-1)));//时间
//第几行,第几个单元格,第几行,第几个单元格(从0开始)
sheet.createFreezePane( 2, 4); //列数,行数(从1开始)
HSSFRow row = sheet.createRow(0); //从0开始
HSSFCell cell = row.createCell(0); //从0开始
cell.setCellValue(this.title);
cell.setCellStyle(style);
row = sheet.createRow(2); //从0开始
cell = row.createCell(0); //从0开始
cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
style= workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cell.setCellStyle(style);
lineNum = 2;
}
void setHeader(String[] headers) throws Exception {
row = sheet.createRow(++lineNum);
HSSFCellStyle style = workbook.createCellStyle();
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
Font font = workbook.createFont();
font.setColor(HSSFColor.YELLOW.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
style.setFont(font);
for (int i = 0; i < headers.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
setMaxWidth(i, headers[i]);
}
}
public abstract void setContent() throws Exception;
public ReportExcelUltity(HttpServletResponse response) {
this.response = response;
try {
workbook = new HSSFWorkbook();
contentCellStyle = this.getWorkbook().createCellStyle();
contentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void export(String fileName, String title, String[] header) {
try {
this.fileName = fileName;
this.header = header;
this.title = title;
setConfiguration(fileName);
setTitle();
setHeader(header);
setContent();
handleLast();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
releaseResource();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
private void handleLast() throws Exception {
// TODO Auto-generated method stub
for (int j = 0; j < header.length; j++) {
storeWidth[j][0] = storeWidth[j][0] == 0?1:storeWidth[j][0];
if (storeWidth[j][0] < 4){
getSheet().setColumnWidth(j,(storeWidth[j][0])*800);
}if(storeWidth[j][0] > 255){
getSheet().setColumnWidth(j,(storeWidth[j][0]));
}
else
getSheet().setColumnWidth(j,(storeWidth[j][0])*400);
}
workbook.write(response.getOutputStream());
}
public void releaseResource() throws Exception {
response.getOutputStream().flush();
response.getOutputStream().close();
}
public HSSFWorkbook getWorkbook() {
return workbook;
}
public int getLineNum() {
return lineNum;
}
public void setLineNum(int lineNum) {
this.lineNum = lineNum;
}
public void setMaxWidth(int index,String str){ //设置每列的最大宽度
if (!StringUtils.isBlank(str) && storeWidth[index][0] < str.length()) {
storeWidth[index][0] = str.length();
}
}
public HSSFSheet getSheet() {
return sheet;
}
/**
* 增加单元格,填充内容
* @param row
* @param desc
* @param index
* @return
*/
public Cell addCell(Row row,String desc,int index) {
// TODO Auto-generated method stub
Cell cell = row.createCell(index);
cell.setCellValue(desc);
cell.setCellStyle(contentCellStyle);
setMaxWidth(index, desc);
return cell;
}
protected Row getNextRow(){
return workbook.getSheet(fileName).createRow(++lineNum);
}
}
@SuppressWarnings("unchecked") public String getDataExcelAndPdf(AuditRecord record, String condiftion) { Class auditClass = record.getClass(); Method methods[] = auditClass.getDeclaredMethods(); String resultString = ""; for (Method methodMeta : methods) { if (methodMeta.getName().startsWith("get")) { if (methodMeta.getName().substring(3).toUpperCase().equals( condiftion.toUpperCase())) { try { Object o = methodMeta.invoke(record); if (o != null) { resultString += (o.toString()); } } catch (Exception e) { e.printStackTrace(); } } } } return resultString; } chinese代表第一行的中文名称,english代表数据列对应的英文名称
String chinese = ServletActionContext.getRequest().getParameter("chinese");
String engish = ServletActionContext.getRequest().getParameter("english");
// 此处根据exportRows 去数据库中查询数据,设置到exportRows中
exportRows = getAllReportRecord(tableName);
chineseArray = chinese.split(",");
englishArray = engish.split(",");
HttpServletResponse response = ServletActionContext.getResponse();
new ReportExcelUltity(response) {
@Override
public void setContent() throws Exception {
// TODO Auto-generated method stub
CellStyle cellStyle = this.getWorkbook().createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (int i = 0; i < exportRows.size(); i++) {
Row row = getNextRow();
int n = 0;
for (String string : englishArray) {
if (string.equals("srcIp"))
string = "srcToString";
else if (string.equals("destIp"))
string = "destToString";
addCell(row, getDataExcelAndPdf(exportRows.get(i),string), n++);
}
}
}
}.export("AuditBSReport", "AuditBSReport", chineseArray);
下面是导出的类POI