Java tutorial
/* * ExportStatBO.java 2010-12-14 * * Copyright 2010 Guangzhou Donghong Software Technology Inc. * Licensed under the www.jxstar.org */ package org.jxstar.report.studio; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFName; 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.jxstar.control.action.RequestContext; import org.jxstar.report.util.ReportXlsUtil; import org.jxstar.service.BusinessObject; import org.jxstar.util.StringValidator; import org.jxstar.util.config.SystemVar; import org.jxstar.util.resource.JsMessage; /** * ?xls * * @author TonyTan * @version 1.0, 2010-12-14 */ public class ExportStatBO extends BusinessObject { private static final long serialVersionUID = 1L; //?xls private ExportXlsBO expXls = new ExportXlsBO(); public String exportXls(RequestContext request) { //??? String expText = request.getRequestValue("exp_text"); String chartType = request.getRequestValue("chart_type"); String charTitle = request.getRequestValue("selchar"); String numTitle = request.getRequestValue("selnum"); if (expText.length() == 0) { setMessage("??"); return _returnFaild; } String[] expTexts = expText.split("\n"); int explen = expTexts.length; _log.showDebug(".............exp text rownum=" + explen); if (explen < 2) { setMessage("??"); return _returnFaild; } HSSFWorkbook wb = readWorkbook(chartType); if (wb == null) { setMessage("/report/tpl/???"); return _returnFaild; } //?? String[] titles = expTexts[0].trim().split(","); //?? String[][] contents = new String[explen - 1][]; for (int i = 1, n = explen; i < n; i++) { contents[i - 1] = expTexts[i].trim().split(","); } //??? updateChartName(titles, charTitle, numTitle, wb); //?? request.setRequestValue("ContentType", "application/vnd.ms-excel"); //"?.xls" request.setRequestValue("Attachment", JsMessage.getValue("exportstatbo.stat")); //xls HSSFWorkbook wbRet = writeBook(contents, titles, wb); request.setReturnObject(wbRet); _log.showDebug("---------file output end!"); return _returnSuccess; } /** * ?workbook * @param lsData -- ? * @param lsCol -- * @return */ private HSSFWorkbook writeBook(String[][] contents, String[] titles, HSSFWorkbook wb) { //sheet HSSFSheet sheet = wb.getSheetAt(0); //sheet?? String title = "?"; wb.setSheetName(0, title); //?cell? HSSFCellStyle cellStyle = expXls.createCellStyle(wb); // sheet = createTitleArea(title, titles, sheet); //? for (int i = 0, n = contents.length; i < n; i++) { String[] rowconts = contents[i]; HSSFCell sfCell = null; HSSFRow hfRow = sheet.createRow(i + 2); for (int j = 0, m = rowconts.length + 1; j < m; j++) { sfCell = hfRow.createCell(j); if (j == 0) continue; String value = rowconts[j - 1]; //???? boolean isDouble = StringValidator.validValue(value, StringValidator.DOUBLE_TYPE); // boolean isNum = false; if (j > 1 && isDouble && value.length() > 0) { String v = Double.toString(Double.parseDouble(value)); if (v.indexOf('E') < 0) isNum = true; } if (isNum) { sfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); sfCell.setCellValue(Double.parseDouble(value)); } else { sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(value); } sfCell.setCellStyle(cellStyle); } } return wb; } /** * * @param fileTitle -- * @param titles -- * @param sheet -- * @return */ private HSSFSheet createTitleArea(String fileTitle, String[] titles, HSSFSheet sheet) { HSSFCell sfCell = null; int rsCnt = titles.length + 1; HSSFWorkbook wb = sheet.getWorkbook(); //? HSSFCellStyle titleStyle = expXls.createTitleStyle(wb); //? HSSFCellStyle headerStyle = expXls.createHeadStyle(wb); //1 HSSFRow hfRow = sheet.createRow(0); hfRow.setHeightInPoints(25); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); sheet.setColumnWidth(i, 4000);//? } sfCell = hfRow.getCell(0); sheet.setColumnWidth(0, 448); // //?? CellRangeAddress range = new CellRangeAddress(0, 0, 1, rsCnt - 1); sheet.addMergedRegion(range); //?? sfCell = hfRow.getCell(1); sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(fileTitle); sfCell.setCellStyle(titleStyle); // hfRow = sheet.createRow(1); for (int i = 0, n = rsCnt; i < n; i++) { sfCell = hfRow.createCell(i); if (i == 0) continue; String colname = titles[i - 1]; sfCell.setCellType(HSSFCell.CELL_TYPE_STRING); sfCell.setCellValue(colname); sfCell.setCellStyle(headerStyle); } return sheet; } /** * ??? * @param chartType * @return */ private HSSFWorkbook readWorkbook(String chartType) { String fileName = SystemVar.REALPATH + "/report/tpl/"; if (chartType.equals("columnchart")) { fileName += "TplColumn.xls"; } else if (chartType.equals("linechart")) { fileName += "TplLine.xls"; } else if (chartType.equals("piechart")) { fileName += "TplPie.xls"; } else { return null; } return ReportXlsUtil.readWorkBook(fileName); } /** * excle??????? * s1y=OFFSET(Sheet1!$C$3,0,0,COUNTA(Sheet1!C:C)-1,1) -- * s2y=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!B:B)-1,1) -- * @param titles -- * @param chars -- * @param nums -- * @param wb -- * @return */ private boolean updateChartName(String[] titles, String chars, String nums, HSSFWorkbook wb) { //?1???1 char x_no = 'B', y_no = 'C'; for (int i = 0, n = titles.length; i < n; i++) { if (chars.equals(titles[i])) { x_no = (char) (65 + i + 1); } else if (nums.equals(titles[i])) { y_no = (char) (65 + i + 1); } } //????? HSSFName s1y = wb.getName("s1y"); HSSFName s2y = wb.getName("s2y"); if (s1y == null || s2y == null) { _log.showWarn("chart tpl HSSFName s1y is null or s2y is null!!"); return false; } _log.showDebug(".............x_no=" + x_no + ";y_no=" + y_no); s1y.setRefersToFormula("OFFSET(Sheet1!$" + y_no + "$3,0,0,COUNTA(Sheet1!" + y_no + ":" + y_no + ")-1,1)"); s2y.setRefersToFormula("OFFSET(Sheet1!$" + x_no + "$3,0,0,COUNTA(Sheet1!" + x_no + ":" + x_no + ")-1,1)"); return true; } }