利用POI进行OFFICE的导入导出之EXECL导出

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