Java 导出Excel

<pre name="code" class="java">package net.e_lian.bpm.core.report.crossreport.runtime.action;

import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import net.e_lian.bpm.core.report.crossreport.runtime.analyzer.AnalyseDimension;
import net.e_lian.bpm.core.report.crossreport.runtime.analyzer.AnalyseTable;
import net.e_lian.bpm.core.report.crossreport.runtime.dataset.ConsoleData;
import net.e_lian.bpm.core.report.crossreport.runtime.dataset.ConsoleDataSet;
import net.e_lian.bpm.core.report.crossreport.runtime.dataset.ConsoleDataType;
import net.e_lian.bpm.core.report.crossreport.runtime.dataset.ConsoleMetaData;
import net.e_lian.bpm.core.report.crossreport.runtime.dataset.ConsoleRow;
import net.e_lian.bpm.util.StringUtil;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ExcelWriter {
	private static int MAX_EXCEL_ROW_COUNT = 65535;

	public long write(String url, AnalyseTable analyseTable) throws Exception {
		try {
			if ((url != null) && (url.length() > 0) && (analyseTable != null)) {
				SXSSFWorkbook workbook = new SXSSFWorkbook(100);
				SXSSFSheet sheet = workbook.createSheet("result");

				short rowIndex = 0;

				rowIndex = getColumnSetExcel(sheet, analyseTable, rowIndex);

				getRowSetExcel(sheet, analyseTable, rowIndex);

				FileOutputStream fos = new FileOutputStream(url);
				workbook.write(fos);
				fos.close();
			}

			return -1L;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1L;
	}

	private short getColumnSetExcel(SXSSFSheet sheet, AnalyseTable analyseTable, short rowIndex) {
		for (Iterator iterator = analyseTable.getColumnSet().iterator(); iterator.hasNext();) {
			SXSSFRow row = sheet.createRow(rowIndex);
			AnalyseDimension column = (AnalyseDimension) iterator.next();
			List keySet = column.getDatasKeyIterator();
			short columnIndex = 0;
			for (Iterator iterator2 = analyseTable.getRowSet().iterator(); iterator2.hasNext();) {
				AnalyseDimension deimension = (AnalyseDimension) iterator2.next();
				ConsoleMetaData metaData = deimension.getMetaData();
				if (metaData != null) {
					String columnName = metaData.getColumnName().toUpperCase();
					SXSSFCell cell = row.createCell(columnIndex);
					cell.setCellValue(columnName.indexOf("ITEM_") >= 0 ? columnName.substring(columnName.indexOf("ITEM_") + 5) : columnName);

					columnIndex = (short) (columnIndex + 1);
				}
			}
			for (Iterator iterator2 = keySet.iterator(); iterator2.hasNext();) {
				String key = (String) iterator2.next();
				if (analyseTable.getCalculationMethod() != null) {
					key = StringUtil.isBlank(key) ? "汇总" : key;
					SXSSFCell cell = row.createCell(columnIndex);
					cell.setCellValue(getKeyLabel(key));
					columnIndex = (short) (columnIndex + 1);
				}
			}
			if ((analyseTable.isDisplayCol()) && (rowIndex == 0)) {
				SXSSFCell cell0 = row.createCell(columnIndex);
				cell0.setCellValue("总计:");
				columnIndex = (short) (columnIndex + 1);
			}
			rowIndex = (short) (rowIndex + 1);
		}
		return rowIndex;
	}

	private String getKeyLabel(String key) {
		String[] keys = key.split("!!");
		return keys[(keys.length - 1)];
	}

	private void getRowSetExcel(SXSSFSheet sheet, AnalyseTable analyseTable, short rowIndex) {
		DecimalFormat df = new DecimalFormat("######0.00");
		DecimalFormat df1 = new DecimalFormat();

		Object[] rowDimesions = analyseTable.getRowSet().toArray();
		AnalyseDimension lastRowDimension = (AnalyseDimension) rowDimesions[(rowDimesions.length - 1)];

		Object[] columnDimesions = analyseTable.getColumnSet().toArray();
		AnalyseDimension lastColumnDimension = null;
		if ((columnDimesions == null) || (columnDimesions.length == 0)) {
			ConsoleMetaData metaData = new ConsoleMetaData(ConsoleDataType.toDataType(-1), "小计", 0);
			ConsoleDataSet dataSet = new ConsoleDataSet(new HashMap());
			lastColumnDimension = new AnalyseDimension(metaData, dataSet);
		} else {
			lastColumnDimension = (AnalyseDimension) columnDimesions[(columnDimesions.length - 1)];
		}

		Map resultDatas = lastRowDimension.getCrossDatas(lastColumnDimension);
		Collection outputValue = new ArrayList();

		Map map = new LinkedHashMap();
		Collection mergeCellList = new ArrayList();

		for (Iterator iterator = lastRowDimension.getDatasKeyIterator().iterator(); (iterator.hasNext()) && (rowIndex <= MAX_EXCEL_ROW_COUNT);) {
			short columnIndex = 0;
			SXSSFRow row = sheet.createRow(rowIndex);
			String lastRowKey = (String) iterator.next();
			String[] keys = lastRowKey.split("!!");

			for (int i = 0; i < keys.length; i++) {
				String currentKey = "";
				for (int j = 0; j <= i; j++) {
					currentKey = StringUtil.isBlank(keys[j]) ? " " : currentKey.length() > 0 ? currentKey + "!!" + (StringUtil.isBlank(keys[j]) ? " " : keys[j]) : keys[j];
				}

				if (!outputValue.contains(currentKey)) {
					int rowSpan = getRowSpan(currentKey, analyseTable) - 1;
					if (rowSpan > 0) {
						int mergeStartRowIndex = rowIndex;
						int mergeEndRowIndex = rowIndex + rowSpan;
						
						sheet.addMergedRegion(new CellRangeAddress(mergeStartRowIndex, columnIndex, mergeEndRowIndex, columnIndex));
						//sheet.addMergedRegion(new Region(mergeStartRowIndex, columnIndex, mergeEndRowIndex, columnIndex));
						for (int k = rowIndex; k <= mergeEndRowIndex; k++) {
							String mergeCellStr = String.valueOf(k) + String.valueOf(columnIndex);
							mergeCellList.add(mergeCellStr.trim());
						}
					} else {
						String cellStr = String.valueOf(rowIndex) + String.valueOf(columnIndex);
						if (!mergeCellList.contains(cellStr))
							;
						// if (mergeCellList.contains(cellStr)) {
						// columnIndex = (short) (columnIndex + 1);
						// }
					}
					SXSSFCell cell = row.createCell(columnIndex);
					cell.setCellValue(getKeyLabel(currentKey));
					outputValue.add(currentKey);
					columnIndex = (short) (columnIndex + 1);
				} else {
					columnIndex = (short) (columnIndex + 1);
				}

			}

			double colSummarize = 0.0D;
			double colValue = 0.0D;
			double countColumn = 0.0D;
			for (Iterator iterator2 = lastColumnDimension.getDatasKeyIterator().iterator(); iterator2.hasNext();) {
				String lastColumnKey = (String) iterator2.next();
				String resultKey = lastRowKey + "@@" + lastColumnKey;
				Collection collection = (Collection) resultDatas.get(resultKey);
				Collection datas = collection;

				if (analyseTable.getCalculationMethod() != null) {
					getCalculationCell(datas, analyseTable.getCalculationField(), analyseTable.getCalculationMethod(), row, columnIndex);
					columnIndex = (short) (columnIndex + 1);
				}

				if (analyseTable.isDisplayCol()) {
					colValue = getSummarizeTotal(datas, analyseTable.getCalculationField(), analyseTable.getCalculationMethod());

					colSummarize += colValue;
				}

				if (analyseTable.isDisplayRow()) {
					double rowValue = getSummarizeTotal(datas, analyseTable.getCalculationField(), analyseTable.getCalculationMethod());

					if (map.containsKey(lastColumnKey)) {
						double temp = ((Double) map.get(lastColumnKey)).doubleValue();
						map.put(lastColumnKey, new Double(temp + rowValue));
					} else {
						map.put(lastColumnKey, new Double(rowValue));
					}
				}
				countColumn += 1.0D;
			}
			if (analyseTable.isDisplayCol()) {
				countColumn = countColumn == 0.0D ? 1.0D : countColumn;

				if (CalculationMethod.valueOf(analyseTable.getColCalMethod()).equals(CalculationMethod.AVERAGE)) {
					SXSSFCell cell = row.createCell(columnIndex);
					cell.setCellValue(df.format(colSummarize / countColumn));
				} else {
					SXSSFCell cell = row.createCell(columnIndex);
					cell.setCellValue(colSummarize);
				}

				if (map.containsKey("Total")) {
					double temp = ((Double) map.get("Total")).doubleValue();
					map.put("Total", new Double(temp + colSummarize));
				} else {
					map.put("Total", new Double(colSummarize));
				}
			}

			rowIndex = (short) (rowIndex + 1);
		}

		rowIndex = rowIndex == 0 ? 1 : rowIndex;
		int columnIndex;
		SXSSFRow row;
		Iterator iterator;
		if (analyseTable.isDisplayRow()) {
			columnIndex = 0;
			row = sheet.createRow(rowIndex);
			SXSSFCell cell0 = row.createCell(columnIndex);
			cell0.setCellValue("总计:");
			columnIndex += analyseTable.getRowSet().size();
			for (iterator = map.entrySet().iterator(); (iterator != null) && (iterator.hasNext());) {
				Map.Entry entry = (Map.Entry) iterator.next();
				Double value = (Double) entry.getValue();
				if (CalculationMethod.valueOf(analyseTable.getRowCalMethod()) == CalculationMethod.AVERAGE) {
					SXSSFCell cell = row.createCell(columnIndex);
					cell.setCellValue(df.format(value.doubleValue() / rowIndex));
					columnIndex++;
				} else {
					SXSSFCell cell = row.createCell(columnIndex);
					cell.setCellValue(df1.format(value));
					columnIndex++;
				}
			}
		}
	}

	private int getRowSpan(String analyseRowKey, AnalyseTable analyseTable) {
		Object[] rowSet = analyseTable.getRowSet().toArray();

		AnalyseDimension lastAnalyseColumn = (AnalyseDimension) rowSet[(rowSet.length - 1)];

		Collection keySet = lastAnalyseColumn.getDatas().keySet();

		int count = 0;

		for (Iterator iterator = keySet.iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			if (key.startsWith(analyseRowKey + "!!")) {
				count++;
			}
		}
		return count;
	}

	private void getCalculationCell(Collection<ConsoleRow> datas, ConsoleMetaData calculationField, CalculationMethod method, SXSSFRow HFrow, int columnIndex) {
		Calculator calculator = new Calculator();
		Iterator iterator;
		if (method.equals(CalculationMethod.DISTINCT)) {
			if ((datas != null) && (datas.size() > 0))
				for (iterator = datas.iterator(); iterator.hasNext();) {
					ConsoleRow row = (ConsoleRow) iterator.next();
					ConsoleData data = row.getData(calculationField);
					SXSSFCell cell = HFrow.createCell(columnIndex);
					cell.setCellValue(data != null ? data.getStringValue() : "");
				}
		} else if (method.equals(CalculationMethod.MAX)) {
			if ((datas != null) && (datas.size() > 0)) {
				SXSSFCell cell = HFrow.createCell(columnIndex);
				cell.setCellValue(calculator.max(datas, calculationField).getStringValue());
			} else {
				SXSSFCell cell = HFrow.createCell(columnIndex);
				cell.setCellValue("");
			}
		} else if (method.equals(CalculationMethod.MIN)) {
			if ((datas != null) && (datas.size() > 0)) {
				SXSSFCell cell = HFrow.createCell(columnIndex);
				cell.setCellValue(calculator.min(datas, calculationField).getStringValue());
			} else {
				SXSSFCell cell = HFrow.createCell(columnIndex);
				cell.setCellValue("");
			}
		} else {
			DecimalFormat f = new DecimalFormat();
			SXSSFCell cell = HFrow.createCell(columnIndex);
			cell.setCellValue(f.format(calculator.compute(datas, method, calculationField)));
		}
	}

	private double getSummarizeTotal(Collection<ConsoleRow> datas, ConsoleMetaData calculationField, CalculationMethod method) {
		Calculator calculator = new Calculator();
		return calculator.compute(datas, method, calculationField);
	}
}

// package net.e_lian.bpm.core.report.crossreport.runtime.action;
//
// import java.io.FileOutputStream;
// import java.util.Iterator;
//
// import org.apache.poi.hssf.usermodel.HSSFCell;
// import org.apache.poi.hssf.usermodel.HSSFRow;
// import org.apache.poi.hssf.usermodel.HSSFSheet;
// import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//
// import net.e_lian.bpm.core.report.crossreport.runtime.dataset.ConsoleData;
// import net.e_lian.bpm.core.report.crossreport.runtime.dataset.ConsoleDataSet;
// import net.e_lian.bpm.core.report.crossreport.runtime.dataset.ConsoleMetaData;
// import net.e_lian.bpm.core.report.crossreport.runtime.dataset.ConsoleRow;
//
// /**
// * The excel console writer which implement the ConsoleWriter interface, it
// can
// * export the console object(like data set) to excel file.
// *
// */
// public class ExcelWriter {
//
// private static int MAX_EXCEL_ROW_COUNT = 65535;
//
// /**
// *
// * @param url
// * @param dataSet
// * @SuppressWarnings 使用了过时的POI API
// * @return
// *
// * @throws Exception
// */
// @SuppressWarnings("deprecation")
// public long write(String url, ConsoleDataSet dataSet) throws Exception {
// try {
// if (url != null && url.length() > 0 && dataSet != null) {
// // 1.Create the excel work sheet
// HSSFWorkbook workbook = new HSSFWorkbook();
// HSSFSheet sheet = workbook.createSheet("result");
//
// // 2.Export the title
// Iterator<ConsoleMetaData> metaDataSet = dataSet.getMetaDataSetIterator();
// short columnIndex = 0;
//
// while (metaDataSet.hasNext()) {
// ConsoleMetaData metaData = (ConsoleMetaData) metaDataSet.next();
//
// HSSFRow row = sheet.createRow(0);
// HSSFCell cell = row.createCell(columnIndex);
// // cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
// cell.setCellValue(metaData.getColumnName());
//
// columnIndex++;
// }
//
// // 3.Export the data
// if (dataSet.getRows() != null && dataSet.getRows().size() > 0) {
// short rowIndex = 1;
//
// for (Iterator<ConsoleRow> iterator = dataSet.getRows().iterator();
// iterator.hasNext();) {
// ConsoleRow dataRow = (ConsoleRow) iterator.next();
// columnIndex = 0;
//
// if (rowIndex > MAX_EXCEL_ROW_COUNT)
// break;
//
// HSSFRow row = sheet.createRow(rowIndex);
//
// for (Iterator<ConsoleData> iterator2 = dataRow.getDatas().iterator();
// iterator2.hasNext();) {
// ConsoleData data = (ConsoleData) iterator2.next();
// HSSFCell cell = row.createCell(columnIndex);
// // cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
// cell.setCellValue(data.getStringValue());
//
// columnIndex++;
// }
// rowIndex++;
// }
// }
//
// // 4.Save the file
// FileOutputStream fos = new FileOutputStream(url);
// workbook.write(fos);
// fos.close();
// }
//
// return -1;
// } catch (Exception e) {
// e.printStackTrace();
// return -1;
// }
// }
// }

package net.e_lian.bpm.util.excel;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;

public class ExcelSheetRow {
	public ExcelSheetRow() {
		// TODO Auto-generated constructor stub
	}

	public static HSSFSheet sheet = null;

	/**
	 * 设置当前Sheet名字
	 */
	private static String sheetName = null;

	private static SXSSFRow row = null;

	/**
	 * 创建当前标题行
	 * 
	 * @param sheet
	 * @return
	 */
	public static SXSSFRow createCurrSheetTitle(ExcelWorkBook work) {
		SXSSFSheet sheet = work.getSheet();
		row = sheet.createRow(0);
		return row;
	}

	/**
	 * 创建当前excel记录内容
	 * 
	 * @param sheet
	 * @param i
	 * @return
	 */
	public static SXSSFRow createCurrSheetRecord(ExcelWorkBook work, int i) {
		SXSSFSheet sheet = work.getSheet();
		row = sheet.createRow(i + 1);
		return row;
	}

	public static String getSheetName() {
		return sheetName;
	}

	public static void setSheetName(String sheetName) {
		ExcelSheetRow.sheetName = sheetName;
	}
}

package net.e_lian.bpm.util.excel;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ExcelWorkBook {
	public SXSSFWorkbook workbook = null;

	// 设置当前workbookName
	private String workbookName = null;

	private SXSSFSheet sheet = null;

	private FileOutputStream fileOut;

	public ExcelWorkBook() {
		workbook = new SXSSFWorkbook(100);
	}

	public ExcelWorkBook(String workbookName) {
		this();
		setWorkbookName(workbookName);
	}

	public String getWorkbookName() {
		return workbookName;
	}

	public void setWorkbookName(String workbookName) {
		this.workbookName = workbookName;
	}

	/**
	 * @return
	 * @SuppressWarnings("deprecation") 使用了过时的POI API
	 */
	@SuppressWarnings("deprecation")
	public SXSSFSheet getSheet() {
		if (sheet == null) {
			sheet = workbook.createSheet();
			workbook.setSheetName(0, workbookName);
		}
		return sheet;
	}

	public SXSSFWorkbook getWorkbook() {
		return workbook;
	}

	/**
	 * 
	 * @param toDir
	 * @param excelName
	 */
	public void write(String destDir, String excelName) {
		File file = new File(destDir);
		if (!file.exists()) {
			boolean isok = file.mkdirs();
			if (isok)
				writerFileStream(destDir + "/" + excelName);
		}
	}

	/**
	 * 输入当前WorkBook为下载临时文件记录
	 * 
	 * @param excelName
	 */
	public void writerFileStream(String excelName) {
		try {
			fileOut = new FileOutputStream(excelName);
			workbook.write(fileOut);
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				fileOut.flush();
				fileOut.close();
				if (workbook != null) {
					// dispose of temporary files backing this workbook on disk
					workbook.dispose();
					workbook = null;
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
}

package net.e_lian.bpm.util.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ExcelCellStyleUtils {
	// 标题样式
	public CellStyle titleStyle;

	// 时间样式
	public CellStyle dataStyle;

	// 单元格样式
	public CellStyle nameStyle;

	// 超链接样式
	public CellStyle linkStyle;

	public HSSFFont font;

	public ExcelCellStyleUtils(ExcelWorkBook work) {
		titleStyle = linkStyle(work.getWorkbook());
		dataStyle = dataStyle(work.getWorkbook());
		nameStyle = nameStyle(work.getWorkbook());
		linkStyle = linkStyle(work.getWorkbook());
	}

	/**
	 * 超链接样式
	 * 
	 * @return HSSFCellStyle
	 */
	private CellStyle linkStyle(SXSSFWorkbook work) {
		CellStyle linkStyle = work.createCellStyle();
		linkStyle.setBorderBottom((short) 1);
		linkStyle.setBorderLeft((short) 1);
		linkStyle.setBorderRight((short) 1);
		linkStyle.setBorderTop((short) 1);
		linkStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		linkStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		Font font = work.createFont();
		font.setFontName(HSSFFont.FONT_ARIAL);
		font.setUnderline((byte) 1);
		font.setColor(HSSFColor.BLUE.index);
		linkStyle.setFont(font);
		return linkStyle;
	}

	/**
	 * s 单元格样式
	 * 
	 * @return HSSFCellStyle
	 */
	private CellStyle nameStyle(SXSSFWorkbook work) {
		CellStyle nameStyle = work.createCellStyle();
		nameStyle.setBorderBottom((short) 1);
		nameStyle.setBorderLeft((short) 1);
		nameStyle.setBorderRight((short) 1);
		nameStyle.setBorderTop((short) 1);
		nameStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
		nameStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		return nameStyle;
	}

	/**
	 * 时间样式
	 * 
	 * @return HSSFCellStyle
	 */
	private CellStyle dataStyle(SXSSFWorkbook work) {
		CellStyle dataStyle = work.createCellStyle();
		dataStyle.setBorderBottom((short) 1);
		dataStyle.setBorderLeft((short) 1);
		dataStyle.setBorderRight((short) 1);
		dataStyle.setBorderTop((short) 1);
		dataStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
		dataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return dataStyle;
	}

	/**
	 * 标题样式
	 * 
	 * @return HSSFCellStyle
	 */
	/*
	 * private static HSSFCellStyle titleStyle(HSSFWorkbook work) { HSSFCellStyle titleStyle = work.createCellStyle(); font = work.createFont(); font.setItalic(true); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.BLUE.index); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); titleStyle.setBorderLeft((short) 1); titleStyle.setBorderRight((short) 1); titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); titleStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); return titleStyle; }
	 */
}

package net.e_lian.bpm.core.report.standardreport.action;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

import net.e_lian.bpm.core.report.standardreport.ejb.StandarReportProcessBean;
import net.e_lian.bpm.util.StringUtil;
import net.e_lian.bpm.util.property.DefaultProperty;
import net.e_lian.bpm.util.sequence.Sequence;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.util.CellRangeAddress;
import org.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.Action;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;

public class StandardReportAction extends ActionSupport implements Action {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public String _formid;

	public String application;

	public String startdate;

	public String enddate;

	public String dbmethod;

	public String filePath;

	Map<String, String> columns = new LinkedHashMap<String, String>();

	public String getDbmethod() {
		return dbmethod;
	}

	public void setDbmethod(String dbmethod) {
		this.dbmethod = dbmethod;
	}

	public String get_formid() {
		return _formid;
	}

	public void set_formid(String _formid) {
		this._formid = _formid;
	}

	public String doSummaryReport() throws Exception {
		setColMap();
		return SUCCESS;
	}

	/**
	 * 
	 * @return
	 * @throws Exception
	 * @SuppressWarnings 使用了过时的POI API
	 * 
	 */
	@SuppressWarnings("deprecation")
	public String doExportReport() throws Exception {
		Map<?, ?> m = getContext().getParameters();
		String selColNames[] = (String[]) m.get("col");
		String listColName[] = new String[selColNames.length + 1];
		int count = 0;
		String path = DefaultProperty.getProperty("EXPORT_PATH");
		String ctxPath = ServletActionContext.getServletContext().getRealPath(path);
		String fileName = Sequence.getSequence() + ".xls";
		selColNames[0] = StringUtil.isBlank(selColNames[0]) ? "AUDITOR" : selColNames[0];

		setFilePath(path + fileName);
		String xlsFile = ctxPath + "/" + fileName;

		if (!(new File(ctxPath).isDirectory())) {
			File f = new File(ctxPath);
			if (!f.exists()) {
				if (!f.mkdirs())
					throw new IOException("create directory '" + ctxPath + "' failed!");
			}
		}

		for (int i = 0; i < selColNames.length; i++)
			listColName[i] = selColNames[i];

		listColName[selColNames.length] = "USEDTIME";

		StandarReportProcessBean spb = new StandarReportProcessBean(application);
		int index = Integer.valueOf(dbmethod);
		Collection<Map<String, String>> collection = spb.getSummaryReport(_formid, startdate, enddate, selColNames, ReportUtil.dbmethod[index]);

		FileOutputStream fos = new FileOutputStream(xlsFile);
		HSSFWorkbook wb = new HSSFWorkbook();

		// for list
		HSSFSheet s = wb.createSheet();
		wb.setSheetName(0, "list Excel ");// , HSSFWorkbook.ENCODING_UTF_16);

		// ��ӡ����ͷ
		HSSFRow rowheader = s.createRow(count);
		for (short j = 0; j < listColName.length; j++) {
			HSSFCell cell = rowheader.createCell(j);
			// cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
			cell.setCellValue(listColName[j]);
		}

		count++;

		for (Iterator<Map<String, String>> iterator = collection.iterator(); iterator.hasNext();) {
			Map<String, String> rowValues = (Map<String, String>) iterator.next();
			HSSFRow row = s.createRow(count);
			HSSFRow preRow = s.getRow(count - 1);

			for (short j = 0; j < listColName.length; j++) {
				HSSFCell cell = row.createCell(j);
				// cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
				cell.setCellValue(rowValues.get(listColName[j]).toString());

				if (count != 1 && preRow.getCell(j).getStringCellValue().equals(cell.getStringCellValue()) && !listColName[j].equals("USEDTIME")) {
					s.addMergedRegion(new CellRangeAddress(count - 1, j, count, j));
					//s.addMergedRegion(new Region(count - 1, j, count, j));
				}
			}
			count++;
		}

		// for bar
		wb.write(fos);
		fos.close();

		return SUCCESS;
	}

	public String[] getcolumsId() throws Exception {
		Map<?, ?> m = getContext().getParameters();
		Object obj = m.get("colids");
		String[] rolesid = null;

		if (obj != null && obj instanceof String[] && ((String[]) obj).length > 0) {
			rolesid = (String[]) obj;
		}
		return rolesid;
	}

	public Map<String, String> getColumns() {
		return columns;
	}

	public void setColumns(Map<String, String> columns) {
		setColMap();
	}

	public void setColMap() {
		columns.put("", "");

		// ��̬��columns
		Map<?, ?> m = getContext().getParameters();
		Object obj = m.get("colids");
		String[] ids = null;

		if (obj != null && obj instanceof String[] && ((String[]) obj).length > 0) {
			ids = (String[]) obj;
			for (int i = 0; i < ids.length; i++)
				columns.put("ITEM_" + ids[i].toUpperCase(), ids[i]);
		}

		columns.put("ARRIVEDTIME", "ARRIVEDTIME");
		columns.put("SENDOUTTIME", "SENDOUTTIME");
		columns.put("AUDITOR", "AUDITOR");
		columns.put("NODENAME", "NODENAME");

	}

	public static ActionContext getContext() {
		ActionContext context = ActionContext.getContext();
		return context;
	}

	public String getApplication() {
		return application;
	}

	public void setApplication(String application) {
		this.application = application;
	}

	public String getStartdate() {
		return startdate;
	}

	public void setStartdate(String startdate) {
		this.startdate = startdate;
	}

	public String getEnddate() {
		return enddate;
	}

	public void setEnddate(String enddate) {
		this.enddate = enddate;
	}

	public String getFilePath() {
		return filePath;
	}

	public void setFilePath(String filePath) {
		this.filePath = filePath;
	}

}