List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetIndex
int getSheetIndex(Sheet sheet);
From source file:org.bbreak.excella.reports.tag.RowRepeatParamParser.java
License:Open Source License
@Override public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ?/*from ww w . j ava 2s.c om*/ checkParam(paramDef, tagCell); String tag = tagCell.getStringCellValue(); ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data; // ? Object[] paramValues = null; try { // ? boolean rowShift = false; if (paramDef.containsKey(PARAM_ROW_SHIFT)) { rowShift = Boolean.valueOf(paramDef.get(PARAM_ROW_SHIFT)); } // ?? boolean hideDuplicate = false; if (paramDef.containsKey(PARAM_DUPLICATE)) { hideDuplicate = Boolean.valueOf(paramDef.get(PARAM_DUPLICATE)); } // ? Integer breakNum = null; if (paramDef.containsKey(PARAM_BREAK_NUM)) { breakNum = Integer.valueOf(paramDef.get(PARAM_BREAK_NUM)); } // boolean changeBreak = false; if (paramDef.containsKey(PARAM_CHANGE_BREAK)) { changeBreak = Boolean.valueOf(paramDef.get(PARAM_CHANGE_BREAK)); } // ? Integer repeatNum = null; if (paramDef.containsKey(PARAM_REPEAT_NUM)) { repeatNum = Integer.valueOf(paramDef.get(PARAM_REPEAT_NUM)); } // ?? Integer minRepeatNum = null; if (paramDef.containsKey(PARAM_MIN_REPEAT_NUM)) { minRepeatNum = Integer.valueOf(paramDef.get(PARAM_MIN_REPEAT_NUM)); } // ? boolean sheetLink = false; if (paramDef.containsKey(PARAM_SHEET_LINK)) { sheetLink = Boolean.valueOf(paramDef.get(PARAM_SHEET_LINK)); } // String propertyName = null; if (paramDef.containsKey(PARAM_PROPERTY)) { propertyName = paramDef.get(PARAM_PROPERTY); } // ??? String replaceParam = paramDef.get(PARAM_VALUE); // if (ReportsUtil.VALUE_SHEET_NAMES.equals(replaceParam)) { // ?? paramValues = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook()).toArray(); } else if (ReportsUtil.VALUE_SHEET_VALUES.equals(replaceParam)) { // paramValues = ReportsUtil.getSheetValues(reportsParserInfo.getReportBook(), propertyName, reportsParserInfo.getReportParsers()).toArray(); } else { // ??? ParamInfo paramInfo = reportsParserInfo.getParamInfo(); if (paramInfo != null) { paramValues = getParamData(paramInfo, replaceParam); } } if (paramValues == null || paramValues.length == 0) { // ? paramValues = new Object[] { null }; } // ? int shiftNum = paramValues.length; // ? int paramLength = paramValues.length; // ??????? if (minRepeatNum != null && shiftNum < minRepeatNum) { Object[] tmpValues = new Object[minRepeatNum]; System.arraycopy(paramValues, 0, tmpValues, 0, paramValues.length); paramValues = tmpValues; shiftNum = paramValues.length; paramLength = paramValues.length; } // ??? int defaultFromCellRowIndex = tagCell.getRowIndex(); // ??? int defaultFromCellColIndex = tagCell.getColumnIndex(); // ??? int unitRowSize = 1; // ??? List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress targetAddress = sheet.getMergedRegion(i); maegedAddresses.add(targetAddress); } // ??? if (maegedAddresses.size() > 0) { // ????????????? for (CellRangeAddress curMergedAdress : maegedAddresses) { if (defaultFromCellColIndex == curMergedAdress.getFirstColumn() && defaultFromCellRowIndex == curMergedAdress.getFirstRow()) { // ???????????? // ?????? unitRowSize = curMergedAdress.getLastRow() - curMergedAdress.getFirstRow() + 1; // ?????? shiftNum = shiftNum * unitRowSize; } } } // ??? // ????? tagCell = new CellClone(tagCell); List<Cell> cellList = new ArrayList<Cell>(); int defaultToOverCellRowIndex = tagCell.getRowIndex() + unitRowSize; for (int i = defaultFromCellRowIndex; i < defaultToOverCellRowIndex; i++) { Row targetCellRow = sheet.getRow(i); cellList.add(new CellClone(targetCellRow.getCell(tagCell.getColumnIndex()))); } // ? if (repeatNum != null && repeatNum < shiftNum) { // ?????? // ???????????????? // ????? shiftNum = repeatNum * unitRowSize; // ?????? paramLength = repeatNum; } // ? if (shiftNum > 1) { // ?(????????) int shiftRowSize = tagCell.getRowIndex() + shiftNum - unitRowSize - 1; if (!rowShift) { // ????? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex(), shiftRowSize, tagCell.getColumnIndex(), tagCell.getColumnIndex()); PoiUtil.insertRangeDown(sheet, rangeAddress); } else { // ????? // #35 POI??????????????????? // ??????0???????? CellRangeAddress rangeAddress = new CellRangeAddress(tagCell.getRowIndex() + unitRowSize, tagCell.getRowIndex() + shiftNum - 1, 0, PoiUtil.getLastColNum(sheet)); PoiUtil.insertRangeDown(sheet, rangeAddress); // int shiftStartRow = tagCell.getRowIndex() + 1; // int shiftEndRow = sheet.getLastRowNum(); // if ( shiftEndRow < shiftStartRow) { // // ???????????????? // // ???????????? // shiftEndRow = shiftStartRow + 1; // } // sheet.shiftRows( shiftStartRow, shiftEndRow, shiftNum - unitRowSize); } } // ??? Workbook workbook = sheet.getWorkbook(); String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet)); // ?? List<String> sheetNames = ReportsUtil.getSheetNames(reportsParserInfo.getReportBook()); // ? List<Object> resultValues = new ArrayList<Object>(); // ??(beforeValue) Object beforeValue = null; // ? int valueIndex = -1; // ????? for (int rowIndex = 0; rowIndex < shiftNum; rowIndex++) { // ?? Row row = sheet.getRow(tagCell.getRowIndex() + rowIndex); if (row == null) { row = sheet.createRow(tagCell.getRowIndex() + rowIndex); } // ?? Cell cell = row.getCell(tagCell.getColumnIndex()); if (cell == null) { cell = row.createCell(tagCell.getColumnIndex()); } // ????(null) Object value = null; // ?????? // ??0???(?????????)??????? int cellIndex = rowIndex % unitRowSize; // ????????? boolean skipRow = false; if (cellIndex != 0) { skipRow = true; } else { valueIndex++; } // (?) PoiUtil.copyCell(cellList.get(cellIndex), cell); // ? Object currentValue = paramValues[valueIndex]; // ??=true??????????? boolean duplicateValue = false; if (beforeValue != null && currentValue != null && beforeValue.equals(currentValue)) { // ??? duplicateValue = true; } if (!skipRow && !(hideDuplicate && duplicateValue)) { // ??=true // ?????????????? value = currentValue; } if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] " + tag + " " + value); } PoiUtil.setCellValue(cell, value); resultValues.add(value); // ? if (sheetLink) { if (!skipRow && valueIndex < sheetNames.size()) { PoiUtil.setHyperlink(cell, HyperlinkType.DOCUMENT, "'" + sheetNames.get(valueIndex) + "'!A1"); if (log.isDebugEnabled()) { log.debug("[??=" + sheetName + ",=(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")] Hyperlink " + "'" + sheetNames.get(valueIndex) + "'!A1"); } } } // if (!skipRow) { if (breakNum != null && valueIndex != 0 && valueIndex % breakNum == 0) { // ??? sheet.setRowBreak(row.getRowNum() - 1); } if (changeBreak && valueIndex != 0 && !duplicateValue) { // ??? sheet.setRowBreak(row.getRowNum() - 1); } } // ?? // ?????????? // ???????????? if (!skipRow && unitRowSize > 1) { // ??? boolean mergedRegionFlag = false; if (rowShift && valueIndex != 0) { // ????????????? mergedRegionFlag = true; } else if (!rowShift && paramLength > valueIndex + 1) { // ???????????? mergedRegionFlag = true; } // ?? if (mergedRegionFlag) { CellRangeAddress rangeAddress = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex() + unitRowSize - 1, cell.getColumnIndex(), cell.getColumnIndex()); sheet.addMergedRegion(rangeAddress); // ???????????? beforeValue = currentValue; } } // ??????? if (unitRowSize == 1) { beforeValue = currentValue; } } // ???? ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); parsedReportInfo.setParsedObject(resultValues); // ?? parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex() + unitRowSize - 1); parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex()); parsedReportInfo.setRowIndex(tagCell.getRowIndex() + shiftNum - 1); parsedReportInfo.setColumnIndex(tagCell.getColumnIndex()); if (log.isDebugEnabled()) { log.debug(parsedReportInfo); } return parsedReportInfo; } catch (Exception e) { throw new ParseException(tagCell, e); } }
From source file:org.bbreak.excella.reports.tag.RowRepeatParamParserTest.java
License:Open Source License
/** * {@link org.bbreak.excella.reports.tag.RowRepeatParamParser#parse(org.apache.poi.ss.usermodel.Sheet, org.apache.poi.ss.usermodel.Cell, java.lang.Object)} ???? */// w ww . j av a 2 s . c o m @Test public void testParseSheetCellObject() { Workbook workbook = getWorkbook(); Sheet sheet1 = workbook.getSheetAt(0); // ----------------------- // []?? // ----------------------- ReportBook reportBook = new ReportBook("", "test", new ConvertConfiguration[] {}); // reportBook.setCopyTemplate( true); ReportSheet reportSheet1 = new ReportSheet("sheet1", "Sheet1"); reportBook.addReportSheet(reportSheet1); ReportSheet reportSheet2 = new ReportSheet("sheet1", "Sheet2"); reportBook.addReportSheet(reportSheet2); ReportSheet reportSheet3 = new ReportSheet("sheet1", "Sheet3"); reportBook.addReportSheet(reportSheet3); ReportSheet[] reportSheets = new ReportSheet[] { reportSheet1, reportSheet2, reportSheet3 }; for (ReportSheet reportSheet : reportSheets) { ParamInfo info = reportSheet.getParamInfo(); info.addParam("$R[]", "A", new Object[] { "AA1", "AA1", "AA2", "AA2", "AA3" }); info.addParam("$R[]", "B", new Object[] { "BB1", "BB1", "BB2" }); info.addParam("$R[]", "C", new Object[] { "CC1", "CC2", "CC3", "CC4", "CC5" }); info.addParam("$", "D", "DDD"); } RowRepeatParamParser parser = new RowRepeatParamParser(); ReportsParserInfo reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setReportParsers( new ArrayList<ReportsTagParser<?>>(ReportCreateHelper.createDefaultParsers().values())); reportsParserInfo.setReportBook(reportBook); reportsParserInfo.setParamInfo(reportSheets[0].getParamInfo()); // ??. List<ParsedReportInfo> results = null; try { results = parseSheet(parser, sheet1, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } CellObject[] expectBeCells = new CellObject[] { new CellObject(0, 0), new CellObject(2, 1) }; CellObject[] expectAfCells = new CellObject[] { new CellObject(4, 0), new CellObject(4, 1) }; checkResult(expectBeCells, expectAfCells, results); // ?? if (version.equals("2007")) { int index = workbook.getSheetIndex(PoiUtil.TMP_SHEET_NAME); if (index > 0) { workbook.removeSheetAt(index); } } checkSheet("Sheet1", sheet1, true); // ----------------------- // [] // // // ?? // ? // // ----------------------- workbook = getWorkbook(); Sheet sheet2 = workbook.getSheetAt(1); // ?? results = null; try { results = parseSheet(parser, sheet2, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } expectBeCells = new CellObject[] { new CellObject(0, 0), new CellObject(2, 1), new CellObject(4, 2) }; expectAfCells = new CellObject[] { new CellObject(4, 0), new CellObject(4, 1), new CellObject(5, 2) }; checkResult(expectBeCells, expectAfCells, results); // ?? if (version.equals("2007")) { int index = workbook.getSheetIndex(PoiUtil.TMP_SHEET_NAME); if (index > 0) { workbook.removeSheetAt(index); } } checkSheet("Sheet2", sheet2, true); // ----------------------- // [] // // ----------------------- workbook = getWorkbook(); Sheet sheet3 = workbook.getSheetAt(2); // ?? results = null; try { results = parseSheet(parser, sheet3, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } expectBeCells = new CellObject[] { new CellObject(0, 0), new CellObject(0, 1), new CellObject(0, 2), new CellObject(17, 1), new CellObject(18, 0) }; expectAfCells = new CellObject[] { new CellObject(2, 0), new CellObject(1, 1), new CellObject(4, 2), new CellObject(19, 1), new CellObject(20, 0) }; checkResult(expectBeCells, expectAfCells, results); // ?? if (version.equals("2007")) { int index = workbook.getSheetIndex(PoiUtil.TMP_SHEET_NAME); if (index > 0) { workbook.removeSheetAt(index); } } checkSheet("Sheet3", sheet3, true); // ----------------------- // []? // ???????? // ----------------------- workbook = getWorkbook(); Sheet sheet4 = workbook.getSheetAt(3); // ?? results = null; try { results = parseSheet(parser, sheet4, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } expectBeCells = new CellObject[] { new CellObject(0, 0), new CellObject(1, 0), new CellObject(2, 0) }; expectAfCells = new CellObject[] { new CellObject(0, 0), new CellObject(1, 0), new CellObject(2, 0) }; checkResult(expectBeCells, expectAfCells, results); // ?? if (version.equals("2007")) { int index = workbook.getSheetIndex(PoiUtil.TMP_SHEET_NAME); if (index > 0) { workbook.removeSheetAt(index); } } checkSheet("Sheet4", sheet4, true); Sheet sheet5 = workbook.getSheetAt(4); // ?? try { parseSheet(parser, sheet5, reportsParserInfo); fail("?????????????????"); } catch (ParseException e) { } // ----------------------- // []? // ????? // ----------------------- workbook = getWorkbook(); Sheet sheet6 = workbook.getSheetAt(5); // ?? try { parseSheet(parser, sheet6, reportsParserInfo); fail(); } catch (ParseException e) { assertTrue(e instanceof ParseException); } // ------------------------------------------------------------ // [] // ?????????? // PoiUtil.getMergedAddress?? // ????????? // ------------------------------------------------------------ workbook = getWorkbook(); Sheet sheet7 = workbook.getSheetAt(6); // ?? try { results = parseSheet(parser, sheet7, reportsParserInfo); fail("???"); } catch (ParseException e) { // org.bbreak.excella.core.util.PoiUtil#getMergedAddress( Sheet sheet, CellRangeAddress rangeAddress) // ?throw???????? assertTrue(e.getCause() instanceof IllegalArgumentException); assertTrue(e.getMessage().contains("There are crossing merged regions in the range.")); } // ------------------------------------------------------------ // [] // ????????? // ------------------------------------------------------------ workbook = getWorkbook(); Sheet sheet8 = workbook.getSheetAt(7); // ?? results = null; try { results = parseSheet(parser, sheet8, reportsParserInfo); } catch (ParseException e) { e.printStackTrace(); fail(e.toString()); } expectBeCells = new CellObject[] { new CellObject(2, 0), new CellObject(2, 1), new CellObject(2, 2), new CellObject(2, 3), new CellObject(2, 4), new CellObject(14, 0) }; expectAfCells = new CellObject[] { new CellObject(10, 0), new CellObject(10, 1), new CellObject(10, 2), new CellObject(4, 3), new CellObject(6, 4), new CellObject(22, 0) }; checkResult(expectBeCells, expectAfCells, results); // ?? if (version.equals("2007")) { int index = workbook.getSheetIndex(PoiUtil.TMP_SHEET_NAME); if (index > 0) { workbook.removeSheetAt(index); } } checkSheet("Sheet8", sheet8, true); // ------------------------------------------------------------ // [] // ????????? // ------------------------------------------------------------ workbook = getWorkbook(); Sheet sheet9 = workbook.getSheetAt(8); // ?? results = null; try { results = parseSheet(parser, sheet9, reportsParserInfo); } catch (ParseException e) { e.printStackTrace(); fail(e.toString()); } expectBeCells = new CellObject[] { new CellObject(3, 0), new CellObject(3, 1), new CellObject(3, 2), new CellObject(3, 3), new CellObject(3, 4) }; expectAfCells = new CellObject[] { new CellObject(15, 0), new CellObject(15, 1), new CellObject(15, 2), new CellObject(6, 3), new CellObject(9, 4) }; checkResult(expectBeCells, expectAfCells, results); // ?? if (version.equals("2007")) { int index = workbook.getSheetIndex(PoiUtil.TMP_SHEET_NAME); if (index > 0) { workbook.removeSheetAt(index); } } checkSheet("Sheet9", sheet9, true); // ------------------------------------------------------------ // [] // ?????????? // PoiUtil.getMergedAddress?? // ????????? // ------------------------------------------------------------ workbook = getWorkbook(); Sheet sheet10 = workbook.getSheetAt(9); // ?? results = null; try { results = parseSheet(parser, sheet10, reportsParserInfo); fail("???"); } catch (ParseException e) { // org.bbreak.excella.core.util.PoiUtil#getMergedAddress( Sheet sheet, CellRangeAddress rangeAddress) // ?throw???????? assertTrue(e.getCause() instanceof IllegalArgumentException); assertTrue(e.getMessage().contains("There are crossing merged regions in the range.")); } // ------------------------------------------------------------ // [] // ????? // ------------------------------------------------------------ workbook = getWorkbook(); Sheet sheet17 = workbook.getSheetAt(16); results = null; try { results = parseSheet(parser, sheet17, reportsParserInfo); } catch (ParseException e) { e.printStackTrace(); fail(e.toString()); } checkSheet("Sheet17", sheet17, true); // ------------------------------------------------------------ // [] // // ------------------------------------------------------------ workbook = getWorkbook(); Sheet sheet18 = workbook.getSheetAt(17); // ?? results = null; try { results = parseSheet(parser, sheet18, reportsParserInfo); } catch (ParseException e) { e.printStackTrace(); fail(e.toString()); } // ?=?=-1,=-1(?????),=-1(???) expectBeCells = new CellObject[] { new CellObject(1, 1), new CellObject(2, 2), new CellObject(2, 4), new CellObject(4, 3) }; expectAfCells = new CellObject[] { new CellObject(5, 1), new CellObject(5, 2), new CellObject(5, 4), new CellObject(10, 3) }; checkResult(expectBeCells, expectAfCells, results); // ?? if (version.equals("2007")) { int index = workbook.getSheetIndex(PoiUtil.TMP_SHEET_NAME); if (index > 0) { workbook.removeSheetAt(index); } } checkSheet("Sheet18", sheet18, true); }
From source file:org.bbreak.excella.reports.tag.SingleParamParser.java
License:Open Source License
@Override public ParsedReportInfo parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { List<String> tmpTargets = new ArrayList<String>(); StringTokenizer tagTokenizer = new StringTokenizer(tagCell.getStringCellValue(), getTag()); while (tagTokenizer.hasMoreTokens()) { tmpTargets.add(tagTokenizer.nextToken()); }/* w w w . java 2 s. c o m*/ List<String> finalTargets = new ArrayList<String>(); for (String tempTarget : tmpTargets) { if (tempTarget.startsWith(TagParser.TAG_PARAM_PREFIX) && !tempTarget.endsWith(TagParser.TAG_PARAM_SUFFIX)) { finalTargets.add(tempTarget.substring(0, tempTarget.indexOf(TagParser.TAG_PARAM_SUFFIX) + 1)); finalTargets.add(tempTarget.substring(tempTarget.indexOf(TagParser.TAG_PARAM_SUFFIX) + 1)); continue; } finalTargets.add(tempTarget); } ParsedReportInfo parsedReportInfo = new ParsedReportInfo(); List<Object> paramValues = new ArrayList<Object>(); for (int i = 0; i < finalTargets.size(); i++) { String target = finalTargets.get(i); if (!target.startsWith(TagParser.TAG_PARAM_PREFIX)) { // ??? paramValues.add(target); continue; } else { // ????? target = getTag() + target; } Map<String, String> paramDef = TagUtil.getParams(target); // ? checkParam(paramDef, tagCell); ReportsParserInfo reportsParserInfo = (ReportsParserInfo) data; ParamInfo paramInfo = reportsParserInfo.getParamInfo(); if (paramInfo != null) { // ??? String replaceParam = paramDef.get(PARAM_VALUE); // ??? paramValues.add(getParamData(paramInfo, replaceParam)); if (log.isDebugEnabled()) { Workbook workbook = sheet.getWorkbook(); String sheetName = workbook.getSheetName(workbook.getSheetIndex(sheet)); log.debug("[??=" + sheetName + ",=(" + tagCell.getRowIndex() + "," + tagCell.getColumnIndex() + ")] " + tagCell.getStringCellValue() + " " + paramValues.get(i)); } } } // ? if (paramValues.size() > 1) { StringBuilder builder = new StringBuilder(); for (Object object : paramValues) { if (object == null) { continue; } if (object instanceof Date) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); builder.append(sdf.format(object)); continue; } builder.append(object); } PoiUtil.setCellValue(tagCell, builder.toString()); parsedReportInfo.setParsedObject(builder.toString()); } else if (paramValues.size() == 1) { PoiUtil.setCellValue(tagCell, paramValues.get(0)); parsedReportInfo.setParsedObject(paramValues.get(0)); } parsedReportInfo.setDefaultRowIndex(tagCell.getRowIndex()); parsedReportInfo.setDefaultColumnIndex(tagCell.getColumnIndex()); parsedReportInfo.setRowIndex(tagCell.getRowIndex()); parsedReportInfo.setColumnIndex(tagCell.getColumnIndex()); return parsedReportInfo; }
From source file:org.bbreak.excella.reports.tag.SumParamParserTest.java
License:Open Source License
/** * {@link org.bbreak.excella.reports.tag.SumParamParser#parse(org.apache.poi.ss.usermodel.Sheet, org.apache.poi.ss.usermodel.Cell, java.lang.Object)} ???? *//*from ww w. j a v a 2s . c o m*/ @Test public void testParseSheetCellObject() { // ----------------------- // []? // ----------------------- Workbook workbook = getWorkbook(); Sheet sheet1 = workbook.getSheetAt(0); ReportBook reportBook = new ReportBook("", "test", new ConvertConfiguration[] {}); // reportBook.setCopyTemplate( true); ReportSheet reportSheet = new ReportSheet("Sheet1", "Sheet1"); reportBook.addReportSheet(reportSheet); // ParamInfo info = reportSheet.getParamInfo(); ParamInfo info$1 = new ParamInfo(); info$1.addParam(SingleParamParser.DEFAULT_TAG, "p1", (byte) 1); info$1.addParam(SingleParamParser.DEFAULT_TAG, "p2", (short) 30); ParamInfo info$2 = new ParamInfo(); info$2.addParam(SingleParamParser.DEFAULT_TAG, "p1", 100); info$2.addParam(SingleParamParser.DEFAULT_TAG, "p2", 300L); ParamInfo info$3 = new ParamInfo(); info$3.addParam(SingleParamParser.DEFAULT_TAG, "p1", 3.5f); info$3.addParam(SingleParamParser.DEFAULT_TAG, "p2", 0.889); ParamInfo info$4 = new ParamInfo(); info$4.addParam(SingleParamParser.DEFAULT_TAG, "p1", new BigInteger("50000")); info$4.addParam(SingleParamParser.DEFAULT_TAG, "p2", new BigDecimal("6.66")); info.addParam(BlockRowRepeatParamParser.DEFAULT_TAG, "br1", new ParamInfo[] { info$1, info$2, info$3, info$4 }); SumParamParser parser = new SumParamParser(); ReportsParserInfo reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setReportParsers( new ArrayList<ReportsTagParser<?>>(ReportCreateHelper.createDefaultParsers().values())); reportsParserInfo.setReportBook(reportBook); reportsParserInfo.setParamInfo(reportSheet.getParamInfo()); // ?? List<ParsedReportInfo> results = null; try { results = parseSheet(parser, sheet1, reportsParserInfo); } catch (ParseException e) { fail(e.toString()); } // ???? checkResult(new CellObject[] { new CellObject(4, 1), new CellObject(7, 1) }, results); // ?? if (version.equals("2007")) { int index = workbook.getSheetIndex(PoiUtil.TMP_SHEET_NAME); if (index > 0) { workbook.removeSheetAt(index); } } checkSheet("Sheet1", sheet1, true); }
From source file:org.bbreak.excella.reports.util.ReportsUtil.java
License:Open Source License
/** * fromIdx??toIdx?????/*from w w w . ja v a 2 s .co m*/ * @param workbook fromIdx?toIdx??workbook * @param fromIdx ? * @param sheet */ public static void copyPrintSetup(Workbook workbook, int fromIdx, Sheet toSheet) { Sheet fromSheet = workbook.getSheetAt(fromIdx); // ? PrintSetup fromPrintSetup = fromSheet.getPrintSetup(); PrintSetup printSetup = toSheet.getPrintSetup(); printSetup.setCopies(fromPrintSetup.getCopies()); printSetup.setDraft(fromPrintSetup.getDraft()); printSetup.setFitHeight(fromPrintSetup.getFitHeight()); printSetup.setFitWidth(fromPrintSetup.getFitWidth()); printSetup.setFooterMargin(fromPrintSetup.getFooterMargin()); printSetup.setHeaderMargin(fromPrintSetup.getHeaderMargin()); printSetup.setHResolution(fromPrintSetup.getHResolution()); printSetup.setLandscape(fromPrintSetup.getLandscape()); printSetup.setLeftToRight(fromPrintSetup.getLeftToRight()); printSetup.setNoColor(fromPrintSetup.getNoColor()); printSetup.setNoOrientation(fromPrintSetup.getNoOrientation()); printSetup.setPageStart(fromPrintSetup.getPageStart()); printSetup.setPaperSize(fromPrintSetup.getPaperSize()); printSetup.setScale(fromPrintSetup.getScale()); printSetup.setUsePage(fromPrintSetup.getUsePage()); printSetup.setValidSettings(fromPrintSetup.getValidSettings()); printSetup.setVResolution(fromPrintSetup.getVResolution()); // ? String printArea = workbook.getPrintArea(fromIdx); if (printArea != null) { if (printArea.contains("!")) { printArea = printArea.substring(printArea.indexOf("!") + 1); } int toIdx = workbook.getSheetIndex(toSheet); workbook.setPrintArea(toIdx, printArea); } // ? toSheet.setRepeatingColumns(fromSheet.getRepeatingColumns()); toSheet.setRepeatingRows(fromSheet.getRepeatingRows()); }
From source file:org.datanucleus.store.excel.ExcelSchemaHandler.java
License:Open Source License
@Override public void deleteSchemaForClasses(Set<String> classNames, Properties props, Object connection) { Workbook wb = (Workbook) connection; ManagedConnection mconn = null;/*from w w w . ja v a 2 s . com*/ try { if (wb == null) { mconn = storeMgr.getConnection(-1); wb = (Workbook) mconn.getConnection(); } Iterator<String> classIter = classNames.iterator(); ClassLoaderResolver clr = storeMgr.getNucleusContext().getClassLoaderResolver(null); while (classIter.hasNext()) { String className = classIter.next(); AbstractClassMetaData cmd = storeMgr.getMetaDataManager().getMetaDataForClass(className, clr); if (cmd != null) { StoreData storeData = storeMgr.getStoreDataForClass(cmd.getFullClassName()); Table table = null; if (storeData != null) { table = storeData.getTable(); } else { table = new CompleteClassTable(storeMgr, cmd, null); } String sheetName = table.getName(); Sheet sheet = wb.getSheet(sheetName); if (sheet != null) { wb.removeSheetAt(wb.getSheetIndex(sheetName)); if (NucleusLogger.DATASTORE_PERSIST.isDebugEnabled()) { NucleusLogger.DATASTORE_PERSIST.debug( Localiser.msg("Excel.SchemaDelete.Class", cmd.getFullClassName(), sheetName)); } } } } } finally { if (mconn != null) { mconn.release(); } } }
From source file:org.eclipse.rcptt.ecl.data.apache.poi.impl.internal.commands.ReadExcelFileService.java
License:Open Source License
public IStatus service(Command command, IProcess context) throws InterruptedException, CoreException { ReadExcelFile ref = (ReadExcelFile) command; EList<String> sheetNames = ref.getSheets(); String uri = ref.getUri();// w w w . j a v a 2 s. c o m EclFile file = FileResolver.resolve(uri); Workbook book = ExcelFileService.readBook(file); if (sheetNames != null && !sheetNames.isEmpty()) { // try to read sheets for (String sheetName : sheetNames) { if (book.getSheet(sheetName) == null) { return EclDataApachePOIImplPlugin.createErr("Sheet %s does not persist in file %s", sheetName, file.toURI()); } Table table = readTable(book, book.getSheetIndex(sheetName)); context.getOutput().write(table); } } else { // read all the sheets int sheetnum = 0; while (sheetnum < book.getNumberOfSheets()) { Table table = readTable(book, sheetnum); context.getOutput().write(table); sheetnum++; } } return Status.OK_STATUS; }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
/** * parse excel file data to java object//from w ww .j av a2 s . c om * * @param workbookInputStream * @param sheetProcessors */ @SuppressWarnings({ "unchecked", "rawtypes" }) public static void read(InputStream workbookInputStream, ExcelReadSheetProcessor<?>... sheetProcessors) { Assert.isTrue(workbookInputStream != null, "workbookInputStream can't be null"); Assert.isTrue(sheetProcessors != null && sheetProcessors.length != 0, "sheetProcessor can't be null"); try { Workbook workbook = WorkbookFactory.create(workbookInputStream); for (ExcelReadSheetProcessor<?> sheetProcessor : sheetProcessors) { ExcelReadContext context = new ExcelReadContext(); try { Class clazz = sheetProcessor.getTargetClass(); Integer sheetIndex = sheetProcessor.getSheetIndex(); String sheetName = sheetProcessor.getSheetName(); context.setCurSheetIndex(sheetIndex); context.setCurSheetName(sheetName); Sheet sheet = null; if (sheetName != null) { try { sheet = workbook.getSheet(sheetName); } catch (IllegalArgumentException e) { // ignore } if (sheet != null && sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException( "sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else if (sheetIndex != null) { try { sheet = workbook.getSheetAt(sheetIndex); } catch (IllegalArgumentException e) { // ignore } } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } if (sheet == null) { ExcelReadException e = new ExcelReadException( "Sheet Not Found Exception. for sheet name:" + sheetName); e.setCode(ExcelReadException.CODE_OF_SHEET_NOT_EXSIT); throw e; } if (sheetIndex == null) { sheetIndex = workbook.getSheetIndex(sheet); } if (sheetName == null) { sheetName = sheet.getSheetName(); } // do check Map<Integer, Map<String, ExcelReadFieldMappingAttribute>> fieldMapping = new HashMap<Integer, Map<String, ExcelReadFieldMappingAttribute>>(); Map<String, Map<String, ExcelReadFieldMappingAttribute>> src = null; if (sheetProcessor.getFieldMapping() != null) { src = sheetProcessor.getFieldMapping().export(); } convertFieldMapping(sheet, sheetProcessor, src, fieldMapping); if (sheetProcessor.getTargetClass() != null && sheetProcessor.getFieldMapping() != null && !Map.class.isAssignableFrom(sheetProcessor.getTargetClass())) { readConfigParamVerify(sheetProcessor, fieldMapping); } // proc sheet context.setCurSheet(sheet); context.setCurSheetIndex(sheetIndex); context.setCurSheetName(sheet.getSheetName()); context.setCurRow(null); context.setCurRowData(null); context.setCurRowIndex(null); context.setCurColIndex(null); context.setCurColIndex(null); // beforeProcess sheetProcessor.beforeProcess(context); if (sheetProcessor.getPageSize() != null) { context.setDataList(new ArrayList(sheetProcessor.getPageSize())); } else { context.setDataList(new ArrayList()); } Integer pageSize = sheetProcessor.getPageSize(); int startRow = sheetProcessor.getStartRowIndex(); Integer rowEndIndex = sheetProcessor.getEndRowIndex(); int actLastRow = sheet.getLastRowNum(); if (rowEndIndex != null) { if (rowEndIndex > actLastRow) { rowEndIndex = actLastRow; } } else { rowEndIndex = actLastRow; } ExcelProcessControllerImpl controller = new ExcelProcessControllerImpl(); if (pageSize != null) { int total = rowEndIndex - startRow + 1; int pageCount = (total + pageSize - 1) / pageSize; for (int i = 0; i < pageCount; i++) { int start = startRow + pageSize * i; int size = pageSize; if (i == pageCount - 1) { size = rowEndIndex - start + 1; } read(controller, context, sheet, start, size, fieldMapping, clazz, sheetProcessor.getRowProcessor(), sheetProcessor.isTrimSpace()); sheetProcessor.process(context, context.getDataList()); context.getDataList().clear(); if (controller.isDoBreak()) { controller.reset(); break; } } } else { read(controller, context, sheet, startRow, rowEndIndex - startRow + 1, fieldMapping, clazz, sheetProcessor.getRowProcessor(), sheetProcessor.isTrimSpace()); sheetProcessor.process(context, context.getDataList()); context.getDataList().clear(); } } catch (RuntimeException e) { sheetProcessor.onException(context, e); } finally { sheetProcessor.afterProcess(context); } } } catch (Exception e) { if (e instanceof RuntimeException) { throw (RuntimeException) e; } else { throw new RuntimeException(e); } } }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings("unchecked") private static void write(boolean useTemplate, Workbook workbook, OutputStream outputStream, ExcelWriteSheetProcessor<?>... sheetProcessors) { for (@SuppressWarnings("rawtypes") ExcelWriteSheetProcessor sheetProcessor : sheetProcessors) { @SuppressWarnings("rawtypes") ExcelWriteContext context = new ExcelWriteContext(); try {/*from ww w.j a v a 2s . c o m*/ if (sheetProcessor == null) { continue; } String sheetName = sheetProcessor.getSheetName(); Integer sheetIndex = sheetProcessor.getSheetIndex(); Sheet sheet = null; if (sheetProcessor.getTemplateStartRowIndex() == null && sheetProcessor.getTemplateEndRowIndex() == null) { sheetProcessor.setTemplateRows(sheetProcessor.getStartRowIndex(), sheetProcessor.getStartRowIndex()); } // sheetName priority, if (useTemplate) { if (sheetName != null) { try { sheet = workbook.getSheet(sheetName); } catch (IllegalArgumentException e) { // ignore } if (sheet != null && sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException( "sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else if (sheetIndex != null) { try { sheet = workbook.getSheetAt(sheetIndex); } catch (IllegalArgumentException e) { // ignore } } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } if (sheet == null) { ExcelWriteException e = new ExcelWriteException( "Sheet Not Found Exception. for sheet name:" + sheetName); e.setCode(ExcelWriteException.CODE_OF_SHEET_NOT_EXSIT); throw e; } } else { if (sheetName != null) { sheet = workbook.getSheet(sheetName); if (sheet != null) { if (sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException("sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else { sheet = workbook.createSheet(sheetName); if (sheetIndex != null) { workbook.setSheetOrder(sheetName, sheetIndex); } } } else if (sheetIndex != null) { sheet = workbook.createSheet(); workbook.setSheetOrder(sheet.getSheetName(), sheetIndex); } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } } if (sheetIndex == null) { sheetIndex = workbook.getSheetIndex(sheet); } if (sheetName == null) { sheetName = sheet.getSheetName(); } // proc sheet context.setCurSheet(sheet); context.setCurSheetIndex(sheetIndex); context.setCurSheetName(sheet.getSheetName()); context.setCurRow(null); context.setCurRowIndex(null); context.setCurCell(null); context.setCurColIndex(null); // beforeProcess sheetProcessor.beforeProcess(context); // write head writeHead(useTemplate, sheet, sheetProcessor); // sheet ExcelProcessControllerImpl controller = new ExcelProcessControllerImpl(); int writeRowIndex = sheetProcessor.getStartRowIndex(); boolean isBreak = false; Map<Integer, InnerRow> cacheForTemplateRow = new HashMap<Integer, InnerRow>(); List<?> dataList = sheetProcessor.getDataList(); // if (dataList != null && !dataList.isEmpty()) { for (Object rowData : dataList) { // proc row Row row = sheet.getRow(writeRowIndex); if (row == null) { row = sheet.createRow(writeRowIndex); } InnerRow templateRow = getTemplateRow(cacheForTemplateRow, sheet, sheetProcessor, writeRowIndex); if (templateRow != null) { row.setHeight(templateRow.getHeight()); row.setHeightInPoints(templateRow.getHeightInPoints()); row.setRowStyle(templateRow.getRowStyle()); row.setZeroHeight(templateRow.isZeroHeight()); } context.setCurRow(row); context.setCurRowIndex(writeRowIndex); context.setCurColIndex(null); context.setCurCell(null); // try { controller.reset(); if (sheetProcessor.getRowProcessor() != null) { sheetProcessor.getRowProcessor().process(controller, context, rowData, row); } if (!controller.isDoSkip()) { writeRow(context, templateRow, row, rowData, sheetProcessor); writeRowIndex++; } if (controller.isDoBreak()) { isBreak = true; break; } } catch (RuntimeException e) { if (e instanceof ExcelWriteException) { ExcelWriteException ewe = (ExcelWriteException) e; // ef.setColIndex(null); user may want to set this value, ewe.setRowIndex(writeRowIndex); throw ewe; } else { ExcelWriteException ewe = new ExcelWriteException(e); ewe.setColIndex(null); ewe.setCode(ExcelWriteException.CODE_OF_PROCESS_EXCEPTION); ewe.setRowIndex(writeRowIndex); throw ewe; } } } if (isBreak) { break; } } if (sheetProcessor.getTemplateStartRowIndex() != null && sheetProcessor.getTemplateEndRowIndex() != null) { writeDataValidations(sheet, sheetProcessor); writeStyleAfterFinish(useTemplate, sheet, sheetProcessor); } } catch (RuntimeException e) { sheetProcessor.onException(context, e); } finally { sheetProcessor.afterProcess(context); } } try { workbook.write(outputStream); } catch (IOException e) { throw new RuntimeException(e); } }
From source file:org.jreserve.gui.poi.ExcelUtil.java
License:Open Source License
public static CellReference getValidCellReference(Workbook wb, String ref) { CellReference cr = getCellReference(wb, ref); String shName = cr.getSheetName(); if (shName == null || wb.getSheetIndex(shName) < 0 || cr.getRow() < 0 || cr.getCol() < 0) return null; return cr;/* ww w . j a va 2s .com*/ }