Java tutorial
/** * PureInfo Command * @(#)CompileExportHelper.java 1.0 2007-7-26 * * Copyright(c) 2004-2005, PureInfo Information Technology Corp. Ltd. * All rights reserved, see the license file. * * www.pureinfo.com.cn */ package com.pureinfo.srm.project.model.compile.helper; import java.io.IOException; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.Iterator; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFFooter; import org.apache.poi.hssf.usermodel.HSSFHeader; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import com.pureinfo.dolphin.export.impl.ExcelExporterImpl; import com.pureinfo.dolphin.export.model.IExportGoods; import com.pureinfo.force.exception.PureException; import com.pureinfo.srm.project.domain.ICompileExport; public class CompileExcelExportHelper extends ExcelExporterImpl implements ICompileExport { private static HSSFCellStyle style; public void export(OutputStream _os, IExportGoods _goods, int _year) throws PureException { try { // 1. to create sheet HSSFWorkbook workbook = new HSSFWorkbook(); style = workbook.createCellStyle(); style.setWrapText(true); HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(0, _goods.getName(), (short) 1); // change the print way to landscape sheet.getPrintSetup().setLandscape(true); // headLine is the true header of the page. // Workbook book = new CompileWorkBook(); // int index = book.addSSTString(HSSFHeader.font("", "Border") + // HSSFHeader.fontSize((short) 16) // + "\u6d59\u6c5f\u5927\u5b66" + _year // + // "\u5e74\u9ad8\u65b0\u529e\u65b0\u4e0a\u9879\u76ee\u8ba1\u5212\u9879\u76ee\u5355",true); HSSFHeader headLine = sheet.getHeader(); headLine.setCenter(HSSFHeader.font("", "Border") + HSSFHeader.fontSize((short) 16) + "" + _year + ""); // footer HSSFFooter footer = sheet.getFooter(); footer.setRight("." + HSSFFooter.page() + "."); footer.setLeft(this.dateFormat(new Date())); // 2. to export headers if (_goods.hasHeader()) { HSSFCellStyle headerStyle = getHeaderStyle(workbook); exportHeaders(sheet, _goods.getHeaders(), headerStyle); } // 3. to export data Object[] values; HSSFCellStyle dateStyle = getDateStyle(workbook); HSSFCellStyle doubleStyle = getDoubleStyle(workbook); int nRowNum = 1; Iterator iter = _goods.iterator(); while (iter.hasNext()) { values = _goods.unpackGoods(iter.next()); exportRow(sheet, values, nRowNum++, dateStyle, doubleStyle); } // 4. to output to stream workbook.write(_os); } catch (IOException ex) { throw new PureException(PureException.UNKNOWN, "export " + _goods.getName() + " to excel", ex); } } public static void exportRow(HSSFSheet _sheet, Object[] _cols, int _nRowNum, HSSFCellStyle _dateStyle, HSSFCellStyle _doubleStyle) throws PureException { HSSFCell cell; Object value; HSSFRow row = _sheet.createRow(_nRowNum); for (short nCol = 0; nCol < _cols.length; nCol++) { _sheet.setColumnWidth(nCol, (short) 3000); cell = row.createCell(nCol); cell.setEncoding((short) 1); value = _cols[nCol]; if (value == null) { cell.setCellValue(""); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setCellStyle(style); } else if (value instanceof String) { cell.setCellValue((String) value); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setCellStyle(style); } else if (value instanceof Number) { cell.setCellValue(((Number) value).doubleValue()); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setCellStyle(_doubleStyle); } else if (value instanceof Boolean) { cell.setCellValue(((Boolean) value).booleanValue()); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setCellStyle(style); } else if (value instanceof Date) { cell.setCellValue((Date) value); cell.setCellStyle(_dateStyle); } else if (value instanceof Calendar) { cell.setCellValue((Calendar) value); cell.setCellStyle(_dateStyle); } else { cell.setCellValue(value.toString()); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); cell.setCellStyle(style); } } } private String dateFormat(Date date) { SimpleDateFormat format = new SimpleDateFormat("MM/dd/yy"); return format.format(date); } /** * Returns the date style in excel. * * @param _workbook * excell work book * @return the date style in excel. */ protected HSSFCellStyle getDateStyle(HSSFWorkbook _workbook) { HSSFCellStyle dateStyle = _workbook.createCellStyle(); HSSFDataFormat fmt = _workbook.createDataFormat(); dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-mm")); HSSFFont font = _workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); dateStyle.setWrapText(true); dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); dateStyle.setFont(font); return dateStyle; } protected HSSFCellStyle getDoubleStyle(HSSFWorkbook _workbook) { HSSFCellStyle doubleStyle = _workbook.createCellStyle(); HSSFFont font = _workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); doubleStyle.setWrapText(true); HSSFDataFormat fmt = _workbook.createDataFormat(); doubleStyle.setDataFormat(fmt.getFormat("0.00")); doubleStyle.setFont(font); return doubleStyle; } /** * Returns the head style * * @param _workbook * excell work book * @return the head style */ protected HSSFCellStyle getHeaderStyle(HSSFWorkbook _workbook) { HSSFCellStyle headerStyle = _workbook.createCellStyle(); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headFont = _workbook.createFont(); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(headFont); headerStyle.setWrapText(true); return headerStyle; } public static void exportHeaders(HSSFSheet _sheet, String _headers[], HSSFCellStyle _headStyle) throws PureException { HSSFRow row = _sheet.createRow(0); for (short j = 0; j < _headers.length; j++) { _sheet.setColumnWidth(j, (short) 3000); HSSFCell cell = row.createCell(j); cell.setEncoding((short) 1); cell.setCellValue(_headers[j]); cell.setCellStyle(_headStyle); } } }