Java tutorial
/* * ReportXlsFormGrid.java 2010-11-11 * * Copyright 2010 Guangzhou Donghong Software Technology Inc. * Licensed under the www.jxstar.org */ package org.jxstar.report.xls; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.jxstar.dao.DaoParam; import org.jxstar.report.ReportException; import org.jxstar.report.util.ReportXlsUtil; import org.jxstar.util.StringUtil; import org.jxstar.util.factory.FactoryUtil; /** * * * @author TonyTan * @version 1.0, 2010-11-11 */ public class ReportXlsFormGrid extends ReportXls { /** * */ public Object output() throws ReportException { _log.showDebug("excel form-grid report output ..."); //?ID????? String funId = _mpReptInfo.get("fun_id"); //? HSSFWorkbook tmpwb = ReportXlsUtil.readWorkBook(_xlsFile); HSSFSheet tmpsheet = tmpwb.getSheetAt(0); HSSFSheet sheet = _hssfWB.getSheetAt(0); int tempRow = -1; if (_lsSubArea.size() > 0) { String not_page = _lsSubArea.get(_lsSubArea.size() - 1).get("not_page"); if (not_page.equals("1")) { tempRow = getAreaTempRow(); _log.showDebug(".............sub area is not page, template clone row:" + tempRow); } } Map<String, String> mpValue = null; for (int i = 0; i < _lsMainRecord.size(); i++) { mpValue = _lsMainRecord.get(i); //? if (i == 0) { sheet = ReportXlsUtil.fillForm(funId, sheet, mpValue, _lsMainCol, _mpUser, i + 1, _lsMainRecord.size()); sheet = ReportXlsUtil.fillHead(sheet, _lsHeadInfo, _mpUser); //? sheet = fillSubArea(sheet, mpValue); } else { tmpsheet = ReportXlsUtil.fillForm(funId, tmpsheet, mpValue, _lsMainCol, _mpUser, i + 1, _lsMainRecord.size()); tmpsheet = ReportXlsUtil.fillHead(tmpsheet, _lsHeadInfo, _mpUser); //?????tmpsheet??? tmpsheet = fillSubArea(tmpsheet, mpValue); sheet = ReportXlsUtil.appendSheet(sheet, tmpsheet, tempRow); tmpwb = ReportXlsUtil.readWorkBook(_xlsFile); tmpsheet = tmpwb.getSheetAt(0); } //SHEET??? if (i == 0) sheet.setForceFormulaRecalculation(true); //?? if (!ReportXlsUtil.isAllowOut(sheet)) break; } return _hssfWB; } /** * ??? * * @param sheet -- * @param mpData -- * @return * @throws ReportException */ private HSSFSheet fillSubArea(HSSFSheet sheet, Map<String, String> mpData) throws ReportException { String strPKCol = _mpMainArea.get("pk_col"); //??? if (strPKCol == null || strPKCol.length() == 0) throw new ReportException("????"); //??? strPKCol = StringUtil.getNoTableCol(strPKCol); String pkval = mpData.get(strPKCol.toLowerCase()); Map<String, List<Map<String, String>>> mpSubRecord = getSubRecord(pkval, strPKCol, _lsSubArea); int maxPage = getMaxPageBySubRecord(mpSubRecord, _lsSubArea); Map<String, String> mpField = null; List<Map<String, String>> lsRecord = null, lsCollist = null; String strAreaID = null, strPageNum = null, isStatArea = null; //?ID????? String funId = _mpReptInfo.get("fun_id"); //? HSSFWorkbook tmpwb = ReportXlsUtil.readWorkBook(_xlsFile); HSSFSheet tmpsheet = tmpwb.getSheetAt(0); //?????? int curNum = 0; for (int i = 0; i < maxPage; i++) { // for (int j = 0, n = _lsSubArea.size(); j < n; j++) { mpField = _lsSubArea.get(j); strAreaID = mpField.get("area_id"); strPageNum = mpField.get("page_size"); isStatArea = mpField.get("is_stat"); if (isStatArea == null) isStatArea = ""; //? int pageSize = Integer.parseInt(strPageNum); curNum = i * pageSize; lsCollist = _mpSubCol.get(strAreaID); lsRecord = mpSubRecord.get(strAreaID); if (isStatArea.equals("1")) { curNum = 0; } //===================SubGrid??========================= String not_page = mpField.get("not_page"); if (not_page.equals("1")) { if (insertSheetRow(sheet, strAreaID, lsRecord.size(), pageSize)) { //?1?? maxPage = 1; pageSize = lsRecord.size(); } } //========================================================== //i=0sheet???tmpsheet if (i > 0) { tmpsheet = ReportXlsUtil.fillGrid(tmpsheet, lsRecord, lsCollist, _mpUser, pageSize, curNum, i + 1, maxPage); if ((j + 1) == n) { if (!ReportXlsUtil.isAllowOut(sheet)) break; sheet = ReportXlsUtil.appendSheet(sheet, tmpsheet); } } else { sheet = ReportXlsUtil.fillGrid(sheet, lsRecord, lsCollist, _mpUser, pageSize, curNum, i + 1, maxPage); } } //?maxPage if ((i + 1) < maxPage) { //?form tmpwb = ReportXlsUtil.readWorkBook(_xlsFile); tmpsheet = tmpwb.getSheetAt(0); tmpsheet = ReportXlsUtil.fillForm(funId, tmpsheet, mpData, _lsMainCol, _mpUser, i + 1, maxPage); } } return sheet; } /** * * * @param mpSubRecord -- ? * @param lsSubArea -- ? * @return * @throws ReportException */ public static int getMaxPageBySubRecord(Map<String, List<Map<String, String>>> mpSubRecord, List<Map<String, String>> lsSubArea) throws ReportException { int ret = 0; if (lsSubArea == null || lsSubArea.isEmpty()) return ret; if (mpSubRecord == null || mpSubRecord.isEmpty()) return ret; for (int i = 0, n = lsSubArea.size(); i < n; i++) { Map<String, String> mpValue = lsSubArea.get(i); String rowNum = mpValue.get("page_size"); String areaName = mpValue.get("area_name"); if (rowNum == null || rowNum.length() == 0) throw new ReportException("[" + areaName + "]??"); List<Map<String, String>> lsData = mpSubRecord.get(mpValue.get("area_id")); int itmp = lsData.size() / Integer.parseInt(rowNum); int ipage = (lsData.size() % Integer.parseInt(rowNum) == 0) ? itmp : itmp + 1; if (ret < ipage) ret = ipage; } return ret; } /** * ? * @param keyID -- * @param pkcol -- ?? * @param lsSubArea -- ? * @return * @throws ReportException */ public static Map<String, List<Map<String, String>>> getSubRecord(String keyID, String pkcol, List<Map<String, String>> lsSubArea) throws ReportException { Map<String, List<Map<String, String>>> ret = FactoryUtil.newMap(); if (lsSubArea == null || lsSubArea.isEmpty()) return ret; if (keyID == null || pkcol == null) return ret; for (int i = 0, n = lsSubArea.size(); i < n; i++) { Map<String, String> mpField = lsSubArea.get(i); String sql = mpField.get("data_sql"); String areaName = mpField.get("area_name"); String subFkcol = mpField.get("sub_fkcol"); //?? if (subFkcol == null || subFkcol.length() == 0) { subFkcol = pkcol; } if (subFkcol == null || subFkcol.length() == 0) { throw new ReportException("?????"); } String strWhere = mpField.get("data_where"); String strOrder = mpField.get("data_order"); String strGroup = mpField.get("data_group"); String dsName = mpField.get("ds_name"); sql += " where (" + subFkcol + " = '" + keyID + "')"; if (strWhere.length() > 0) { sql += " and (" + strWhere + ")"; } if (strGroup.length() > 0) { sql += " group by " + strGroup; } if (strOrder.length() > 0) { sql += " order by " + strOrder; } _log.showDebug(areaName + "[" + keyID + "] " + "sub sql = " + sql); DaoParam param = _dao.createParam(sql); param.setDsName(dsName); List<Map<String, String>> lsTmpRs = _dao.query(param); _log.showDebug(areaName + "[" + keyID + "] " + "data size = " + lsTmpRs.size()); ret.put(mpField.get("area_id"), lsTmpRs); } return ret; } //? private boolean insertSheetRow(HSSFSheet sheet, String areaId, int dataSize, int pageSize) throws ReportException { //?? if (dataSize > 0) { //?? int rows = dataSize - pageSize; if (rows > 0) { //??? int firstRow = ReportXlsUtil.getFirstRows(areaId); if (firstRow < 0) return false; //??? int startRow = firstRow + pageSize; ReportXlsUtil.insertSheetRow(sheet, startRow, rows); return true; } } return false; } //??????? private int getAreaTempRow() { if (_lsSubArea == null || _lsSubArea.isEmpty()) return -1; Map<String, String> mpArea = _lsSubArea.get(_lsSubArea.size() - 1); String areaId = mpArea.get("area_id"); String pageSize = mpArea.get("page_size"); int firstRow = ReportXlsUtil.getFirstRows(areaId); if (firstRow < 0) return -1; //???? return firstRow + Integer.parseInt(pageSize) - 1; } }