List of usage examples for org.apache.poi.ss.usermodel Workbook getSheet
Sheet getSheet(String name);
From source file:org.bbreak.excella.reports.tag.SumParamParserTest.java
License:Open Source License
private void checkSheet(String expectedSheetName, Sheet actualSheet, boolean outputExcel) { // ???/* w w w . j a v a 2 s. c om*/ Workbook expectedWorkbook = getExpectedWorkbook(); Sheet expectedSheet = expectedWorkbook.getSheet(expectedSheetName); try { // ? ReportsTestUtil.checkSheet(expectedSheet, actualSheet, false); } catch (ReportsCheckException e) { fail(e.getCheckMessagesToString()); } finally { if (outputExcel) { String tmpDirPath = ReportsTestUtil.getTestOutputDir(); try { String filepath = null; Date now = new Date(); if (version.equals("2007")) { filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xlsx"; } else { filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xls"; } PoiUtil.writeBook(actualSheet.getWorkbook(), filepath); } catch (IOException e) { e.printStackTrace(); } } } }
From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaExecuter.java
License:Open Source License
/** * ??<BR>/*from w w w . java 2s . c o m*/ * ?????????<BR> * ????SheetData??<BR> * * @param sheet * @param sheetParser * @param sheetData ?? */ @SuppressWarnings("unchecked") public void postParse(Sheet sheet, SheetParser sheetParser, SheetData sheetData) throws ParseException { // ?? List<Object> results = new ArrayList<Object>(); // ?SheetToJavaParser??? List<TagParser<?>> tagParsers = sheetParser.getTagParsers(); // ?????SheetToJavaSettingParser? // ?? List<String> removeTags = new ArrayList<String>(); // ???? List<String> targetTags = new ArrayList<String>(); for (TagParser<?> tagParser : tagParsers) { // SheetToJavaParser? if (tagParser instanceof SheetToJavaParser) { targetTags.add(tagParser.getTag()); } // SheetToJavaSettingParser? if (tagParser instanceof SheetToJavaSettingParser) { removeTags.add(tagParser.getTag()); } } // ? Workbook workbook = sheet.getWorkbook(); // ??? for (String tag : targetTags) { List<SheetToJavaParseInfo> sheetInfoList = (List<SheetToJavaParseInfo>) sheetData.get(tag); if (sheetInfoList == null) { continue; } // ()??? for (SheetToJavaParseInfo sheetInfo : sheetInfoList) { List<SheetToJavaSettingInfo> allColumnInfoList = (List<SheetToJavaSettingInfo>) sheetData .get(sheetInfo.getSettingTagName()); // ??? List<SheetToJavaSettingInfo> targetColumnInfoList = new ArrayList<SheetToJavaSettingInfo>(); for (SheetToJavaSettingInfo columnInfo : allColumnInfoList) { if (columnInfo.getSheetName().equals(sheetInfo.getSheetName())) { targetColumnInfoList.add(columnInfo); } } // ??? Sheet targetSheet = workbook.getSheet(sheetInfo.getSheetName()); if (targetSheet == null) { throw new ParseException(sheetInfo.getSheetNameCell(), "[" + sheetInfo.getSheetName() + "]????"); } results.addAll(parseTargetSheet(targetSheet, sheetInfo, targetColumnInfoList)); } // sheetData??? sheetData.put(tag, results); } // ??????? for (String removeTag : removeTags) { sheetData.remove(removeTag); } }
From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaParser.java
License:Open Source License
/** * ?//from ww w . j a v a2s . co m * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToJavaParseInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); // ???? String settingTagName = getTag() + DEFAULT_SETTING_SUFFIX; try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } // ???? if (paramDef.containsKey(PARAM_SETTING_TAG_NAME)) { settingTagName = paramDef.get(PARAM_SETTING_TAG_NAME); } // ? if (paramDef.containsKey(PARAM_RESULT_KEY)) { // ??????? throw new ParseException(tagCell, PARAM_RESULT_KEY + "????????"); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToJavaParseInfo> sheetInfoList = new ArrayList<SheetToJavaParseInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // ???No int logicalRowColIdx = tagColIdx++; // No int dataRowColIdx = tagColIdx; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell logicalRowNumCell = row.getCell(logicalRowColIdx); Cell valueRowNumCell = row.getCell(dataRowColIdx); // ? if ((sheetNameCell == null) && (logicalRowNumCell == null) && (valueRowNumCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (logicalRowNumCell == null) { requiredErrorCell = row.createCell(logicalRowColIdx); } else if (valueRowNumCell == null) { requiredErrorCell = row.createCell(dataRowColIdx); } // ?????? if (requiredErrorCell != null) { throw new ParseException(requiredErrorCell, "?null??"); } } // ???No? int logicalRowNum; try { logicalRowNum = (Integer) PoiUtil.getCellValue(logicalRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(logicalRowNumCell, e); } // No? int valueRowNum; try { valueRowNum = (Integer) PoiUtil.getCellValue(valueRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(valueRowNumCell, e); } // ??? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // SheetToJavaParseInfo sheetInfo = new SheetToJavaParseInfo(); sheetInfo.setSettingTagName(settingTagName); sheetInfo.setSettingTagNameCell(tagCell); sheetInfo.setSheetName(sheetName); sheetInfo.setSheetNameCell(sheetNameCell); sheetInfo.setLogicalNameRowNum(logicalRowNum); sheetInfo.setLogicalNameRowNumCell(logicalRowNumCell); sheetInfo.setValueRowNum(valueRowNum); sheetInfo.setValueRowNumCell(valueRowNumCell); sheetInfoList.add(sheetInfo); } } return sheetInfoList; }
From source file:org.bbreak.excella.trans.tag.sheet2java.SheetToJavaSettingParser.java
License:Open Source License
/** * ?// ww w . j a v a 2s.c om * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToJavaSettingInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); // int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToJavaSettingInfo> sheetSettingInfoList = new ArrayList<SheetToJavaSettingInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // int valueColIdx = tagColIdx++; // int classColIdx = tagColIdx++; // int propertyNameColIdx = tagColIdx++; // ? int uniqueColIdx = tagColIdx++; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell valueCell = row.getCell(valueColIdx); Cell classCell = row.getCell(classColIdx); Cell propertyNameCell = row.getCell(propertyNameColIdx); Cell uniqueCell = row.getCell(uniqueColIdx); // ? if ((sheetNameCell == null) && (valueCell == null) && (classCell == null) && (propertyNameCell == null) && (uniqueCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (classCell == null) { // ?null?? requiredErrorCell = row.createCell(classColIdx); } // ?? if (requiredErrorCell != null) { throw new ParseException(requiredErrorCell, "?null??"); } } // ?? SheetToJavaSettingInfo settingInfo = new SheetToJavaSettingInfo(); // ???? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // ?? settingInfo.setSheetName(sheetName); settingInfo.setSheetNameCell(sheetNameCell); // ? try { settingInfo.setClazz(Class.forName(classCell.getStringCellValue())); settingInfo.setClazzCell(classCell); } catch (ClassNotFoundException e) { throw new ParseException(classCell, e); } // Object value = PoiUtil.getCellValue(valueCell); settingInfo.setValueCell(valueCell); // ????? boolean isValueTag = false; // ???????? boolean isValueLogicalNameTag = false; if (value instanceof String) { // ?? String valueStr = (String) value; if ((valueStr).startsWith(SheetToJavaExecuter.TAG_PREFIX)) { // ?? isValueTag = true; if ((valueStr).startsWith(SheetToJavaExecuter.TAG_LOGICAL_NAME_PREFIX)) { // ????? isValueLogicalNameTag = true; } } } if (!isValueTag || isValueLogicalNameTag) { // ????????? // ?? Cell requiredErrorCell = null; if (propertyNameCell == null) { requiredErrorCell = row.createCell(propertyNameColIdx); } if (requiredErrorCell != null) { throw new ParseException(requiredErrorCell, "?null??"); } // ? settingInfo.setPropertyName(propertyNameCell.getStringCellValue()); settingInfo.setPropertyNameCell(propertyNameCell); // ? Class<?> propertyClass = null; try { Object obj = settingInfo.getClazz().newInstance(); propertyClass = PropertyUtils.getPropertyType(obj, settingInfo.getPropertyName()); } catch (Exception e) { throw new ParseException(propertyNameCell, e); } if (propertyClass == null) { throw new ParseException(propertyNameCell, "?:" + settingInfo.getPropertyName()); } // ???? if (uniqueCell != null) { if (uniqueCell.getStringCellValue() != null && uniqueCell.getStringCellValue().equals(UNIQUE_PROPERTY_MARK)) { settingInfo.setUnique(true); settingInfo.setUniqueCell(uniqueCell); } } } else { // ?? // ????? try { TagUtil.getParams((String) value); } catch (Exception e) { throw new ParseException(valueCell, e); } } // ?????? boolean checkTypeFlag = false; if (value instanceof String) { if (!isValueTag) { // ?? checkTypeFlag = true; } } else { // ?? if (value != null) { // null?? checkTypeFlag = true; } } // ? if (checkTypeFlag) { // ???? Object obj; try { obj = settingInfo.getClazz().newInstance(); Class<?> propertyClass = PropertyUtils.getPropertyType(obj, settingInfo.getPropertyName()); value = PoiUtil.getCellValue(valueCell, propertyClass); } catch (Exception e) { throw new ParseException(valueCell, e); } } settingInfo.setValue(value); // ???? sheetSettingInfoList.add(settingInfo); } } return sheetSettingInfoList; }
From source file:org.bbreak.excella.trans.tag.sheet2sql.SheetToSqlExecuter.java
License:Open Source License
/** * ??<BR>//from w w w. j a va2 s .c om * ?????Sql????<BR> * ????SheetData??<BR> * * @param sheet * @param sheetParser * @param sheetData ?? */ @SuppressWarnings("unchecked") public void postParse(Sheet sheet, SheetParser sheetParser, SheetData sheetData) throws ParseException { // ?? List<Object> results = new ArrayList<Object>(); // ?SheetToSqlParser??? List<TagParser<?>> tagParsers = sheetParser.getTagParsers(); // ?????SheetToJavaSettingParser? // ?? List<String> removeTags = new ArrayList<String>(); // ???? List<String> targetTags = new ArrayList<String>(); for (TagParser<?> tagParser : tagParsers) { // SheetToSqlParser? if (tagParser instanceof SheetToSqlParser) { targetTags.add(tagParser.getTag()); } // SheetToSqlSettingParser? if (tagParser instanceof SheetToSqlSettingParser) { removeTags.add(tagParser.getTag()); } } // ?? Workbook workbook = sheet.getWorkbook(); // ??? for (String tag : targetTags) { List<SheetToSqlParseInfo> sheetInfoList = (List<SheetToSqlParseInfo>) sheetData.get(tag); if (sheetInfoList == null) { continue; } // ()??? for (SheetToSqlParseInfo sheetInfo : sheetInfoList) { List<SheetToSqlSettingInfo> allColumnInfoList = (List<SheetToSqlSettingInfo>) sheetData .get(sheetInfo.getSettingTagName()); // ??? List<SheetToSqlSettingInfo> targetColumnInfoList = new ArrayList<SheetToSqlSettingInfo>(); for (SheetToSqlSettingInfo columnInfo : allColumnInfoList) { if (columnInfo.getSheetName().equals(sheetInfo.getSheetName())) { targetColumnInfoList.add(columnInfo); } } // ??? Sheet targetSheet = workbook.getSheet(sheetInfo.getSheetName()); if (targetSheet == null) { throw new ParseException("[" + sheetInfo.getSheetName() + "]????"); } results.addAll(parseTargetSheet(targetSheet, sheetInfo, targetColumnInfoList)); } // sheetData??? sheetData.put(tag, results); } // ??????? for (String removeTag : removeTags) { sheetData.remove(removeTag); } }
From source file:org.bbreak.excella.trans.tag.sheet2sql.SheetToSqlParser.java
License:Open Source License
/** * ?/*from w w w. j a v a 2 s . com*/ * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToSqlParseInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); // ???? String settingTagName = getTag() + DEFAULT_SETTING_SUFFIX; try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } // ???? if (paramDef.containsKey(PARAM_SETTING_TAG_NAME)) { settingTagName = paramDef.get(PARAM_SETTING_TAG_NAME); } // ? if (paramDef.containsKey(PARAM_RESULT_KEY)) { // ??????? throw new ParseException(tagCell, PARAM_RESULT_KEY + "????????"); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToSqlParseInfo> sheetInfoList = new ArrayList<SheetToSqlParseInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // ???No int logicalRowColIdx = tagColIdx++; // No int dataRowColIdx = tagColIdx; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell logicalRowNumCell = row.getCell(logicalRowColIdx); Cell valueRowNumCell = row.getCell(dataRowColIdx); // ? if ((sheetNameCell == null) && (logicalRowNumCell == null) && (valueRowNumCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (logicalRowNumCell == null) { requiredErrorCell = row.createCell(logicalRowColIdx); } else if (valueRowNumCell == null) { requiredErrorCell = row.createCell(dataRowColIdx); } // ?????? if (requiredErrorCell != null) { throw new ParseException(requiredErrorCell, "?null??"); } } // ???No? int logicalRowNum; try { logicalRowNum = (Integer) PoiUtil.getCellValue(logicalRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(logicalRowNumCell, e); } // No? int valueRowNum; try { valueRowNum = (Integer) PoiUtil.getCellValue(valueRowNumCell, Integer.class); } catch (Exception e) { throw new ParseException(valueRowNumCell, e); } // ??? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // SheetToSqlParseInfo sheetInfo = new SheetToSqlParseInfo(); sheetInfo.setSettingTagName(settingTagName); sheetInfo.setSheetName(sheetName); sheetInfo.setLogicalNameRowNum(logicalRowNum); sheetInfo.setValueRowNum(valueRowNum); sheetInfoList.add(sheetInfo); } } return sheetInfoList; }
From source file:org.bbreak.excella.trans.tag.sheet2sql.SheetToSqlSettingParser.java
License:Open Source License
/** * ?//www . j a v a 2 s.co m * * @param sheet * @param tagCell ??? * @param data TransProcessor?processBook, processSheet?<BR> * ????TagParser.parse??????<BR> * @return ? * @throws ParseException */ @Override public List<SheetToSqlSettingInfo> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException { // ? int tagRowIdx = tagCell.getRowIndex(); int tagColIdx = tagCell.getColumnIndex(); // int valueRowFromIdx; int valueRowToIdx = sheet.getLastRowNum(); try { Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue()); // ? valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM, DEFAULT_DATA_ROW_FROM_ADJUST); if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ? valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx); if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO); } // ??? if (valueRowFromIdx > valueRowToIdx) { throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO); } } catch (Exception e) { if (e instanceof ParseException) { throw (ParseException) e; } else { throw new ParseException(tagCell, e); } } List<SheetToSqlSettingInfo> sheetSettingInfoList = new ArrayList<SheetToSqlSettingInfo>(); // ?? int sheetNameColIdx = tagColIdx++; // int valueColIdx = tagColIdx++; // int tableColIdx = tagColIdx++; // int columnNameColIdx = tagColIdx++; // ??? int uniqueColIdx = tagColIdx++; // int dataTypeColIdx = tagColIdx++; // ????? Workbook workbook = sheet.getWorkbook(); // ??? for (int rowNum = valueRowFromIdx; rowNum <= valueRowToIdx; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { // ?? Cell sheetNameCell = row.getCell(sheetNameColIdx); Cell valueCell = row.getCell(valueColIdx); Cell tableNameCell = row.getCell(tableColIdx); Cell columnNameCell = row.getCell(columnNameColIdx); Cell uniqueCell = row.getCell(uniqueColIdx); Cell dataTypeCell = row.getCell(dataTypeColIdx); // ? if ((sheetNameCell == null) && (valueCell == null) && (tableNameCell == null) && (columnNameCell == null) && (uniqueCell == null) && (dataTypeCell == null)) { // ????null?? continue; } else if ((sheetNameCell == null) || (sheetNameCell.getStringCellValue() == null) || ("".equals(sheetNameCell.getStringCellValue()))) { // ????????? continue; } else { // ???? Cell requiredErrorCell = null; if (tableNameCell == null) { // ?null?? requiredErrorCell = row.createCell(tableColIdx); } else if (columnNameCell == null) { // ?null?? requiredErrorCell = row.createCell(columnNameColIdx); } if (requiredErrorCell != null) { // ?? throw new ParseException(requiredErrorCell, "?null??"); } } // ?? SheetToSqlSettingInfo settingInfo = new SheetToSqlSettingInfo(); // ???? String sheetName = sheetNameCell.getStringCellValue(); if (workbook.getSheet(sheetName) == null) { throw new ParseException(sheetNameCell, "[" + sheetName + "]????"); } // ?? settingInfo.setSheetName(sheetName); settingInfo.setSheetNameCell(sheetNameCell); // ?? settingInfo.setTableName(tableNameCell.getStringCellValue()); settingInfo.setTableNameCell(tableNameCell); // settingInfo.setColumnName(columnNameCell.getStringCellValue()); settingInfo.setColumnNameCell(columnNameCell); // if (valueCell != null) { Object value = PoiUtil.getCellValue(valueCell); settingInfo.setValue(value); settingInfo.setValueCell(valueCell); } // ??? if (uniqueCell != null) { if (uniqueCell.getStringCellValue() != null && uniqueCell.getStringCellValue().equals(UNIQUE_PROPERTY_MARK)) { settingInfo.setUnique(true); settingInfo.setUniqueCell(uniqueCell); } } // if (dataTypeCell != null) { settingInfo.setDataType(dataTypeCell.getStringCellValue()); settingInfo.setDataTypeCell(dataTypeCell); } // ???? sheetSettingInfoList.add(settingInfo); } } return sheetSettingInfoList; }
From source file:org.centralperf.helper.view.ExcelOOXMLView.java
License:Open Source License
/** * @see AbstractPOIExcelView#buildExcelDocument(Map, Workbook, HttpServletRequest, HttpServletResponse) *//* w w w . j a va 2 s . c o m*/ @Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { log.debug("Generating Excel report from run samples"); // Set the headers response.setHeader("Content-Type", "application/octet-stream"); response.setHeader("Content-Disposition", "attachment; filename=central_perf_result.xlsx"); // get data model which is passed by the Spring container Run run = (Run) model.get("run"); // Set run summary informations setCellValueByName(PROJECT_NAME_CELL_NAME, run.getProject().getName(), workbook); setCellValueByName(RUN_LABEL_CELL_NAME, run.getLabel(), workbook); setCellValueByName(RUN_DESCRIPTION_CELL_NAME, run.getComment(), workbook); setCellValueByName(START_DATE_CELL_NAME, run.getStartDate().toString(), workbook); setCellValueByName(START_DATE_CELL_NAME, run.getStartDate().toString(), workbook); setCellValueByName(GENERATED_ON_CELL_NAME, "" + unixTimestamp2ExcelTimestampconvert(new Date().getTime()), workbook); // Populate data sheet XSSFSheet dataSheet = (XSSFSheet) workbook.getSheet(DATA_SHEET_NAME); // Set date style for first column CellStyle dateStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd")); dataSheet.setDefaultColumnStyle(0, dateStyle); // Add samples for (int i = 0; i < run.getSamples().size(); i++) { Sample sample = run.getSamples().get(i); XSSFRow dataRow = dataSheet.createRow(i + 1); if (sample.getTimestamp() != null) { dataRow.createCell(0) .setCellValue(unixTimestamp2ExcelTimestampconvert(sample.getTimestamp().getTime())); dataRow.createCell(1).setCellValue(sample.getElapsed()); dataRow.createCell(2).setCellValue(sample.getSampleName()); dataRow.createCell(3).setCellValue(sample.getStatus()); dataRow.createCell(4).setCellValue(sample.getReturnCode()); dataRow.createCell(5).setCellValue(sample.getSizeInOctet()); dataRow.createCell(6).setCellValue(sample.getGrpThreads()); dataRow.createCell(7).setCellValue(sample.getAllThreads()); dataRow.createCell(8).setCellValue(sample.getLatency()); } } // Return generated sheet OutputStream outStream = null; try { outStream = response.getOutputStream(); workbook.write(outStream); outStream.flush(); } finally { outStream.close(); } }
From source file:org.centralperf.helper.view.ExcelOOXMLView.java
License:Open Source License
/** * Retrieve a cell in workbook by its name * @param cellName The name of the cell * @param workbook The workbook//from www . j a v a 2s.c o m * @return the cell found, null if multiple cells or not found */ private Cell getCellByName(String cellName, Workbook workbook) { int namedCellIdx = workbook.getNameIndex(cellName); Name aNamedCell = workbook.getNameAt(namedCellIdx); // retrieve the cell at the named range and test its contents AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula()); if (aref.isSingleCell()) { CellReference cref = aref.getFirstCell(); Sheet s = workbook.getSheet(cref.getSheetName()); Row r = s.getRow(cref.getRow()); Cell c = r.getCell(cref.getCol()); return c; } return null; }
From source file:org.corpus_tools.peppermodules.spreadsheet.Spreadsheet2SaltMapper.java
License:Apache License
/** * get the primary text tiers and their annotations of the given document * /*from w w w .j ava 2 s .c o m*/ * @param workbook * @param timeline */ private void getPrimTextTiers(Workbook workbook, STimeline timeline) { // get all primary text tiers String primaryTextTier = getProps().getPrimaryText(); // seperate string of primary text tiers into list by commas List<String> primaryTextTierList = Arrays.asList(primaryTextTier.split("\\s*,\\s*")); TreeSet<String> annosWithoutPrim = new TreeSet<>(); if (workbook != null) { // get corpus sheet Sheet corpusSheet; // default ("Tabelle1"/ first sheet) if (getProps().getCorpusSheet().equals("Tabelle1")) { corpusSheet = workbook.getSheetAt(0); } else { // get corpus sheet by name corpusSheet = workbook.getSheet(getProps().getCorpusSheet()); } // end of the excel file int lastRow = corpusSheet.getLastRowNum(); mapLinenumber2STimeline(lastRow, timeline); if (corpusSheet != null) { // row with all names of the annotation tiers (first row) Row headerRow = corpusSheet.getRow(0); // List for each primary text and its annotations HashMap<Integer, Integer> annoPrimRelations = new HashMap<>(); List<Integer> primTextPos = new ArrayList<Integer>(); if (headerRow != null) { // iterate through all tiers and save tiers (column number) // that hold the primary data int currColumn = 0; List<String> emptyColumnList = new ArrayList<>(); while (currColumn < headerRow.getPhysicalNumberOfCells()) { if (headerRow.getCell(currColumn) == null || headerRow.getCell(currColumn).toString().isEmpty()) { String emptyColumn = CellReference.convertNumToColString(currColumn); emptyColumnList.add(emptyColumn); currColumn++; continue; } else { if (!emptyColumnList.isEmpty()) { for (String emptyColumn : emptyColumnList) { SpreadsheetImporter.logger.warn("Column \"" + emptyColumn + "\" in document \"" + getResourceURI().lastSegment() + "\" has no name."); } emptyColumnList = new ArrayList<>(); } boolean primWasFound = false; String tierName = headerRow.getCell(currColumn).toString(); if (primaryTextTierList.contains(tierName)) { // current tier contains primary text // save all indexes of tier containing primary // text primTextPos.add(currColumn); primWasFound = true; } else { // current tier contains (other) annotations if (tierName.matches(".+\\[.+\\]") || getProps().getAnnoPrimRel() != null || getProps().getShortAnnoPrimRel() != null) { if (tierName.matches(".+\\[.+\\]")) { // the belonging primary text was set by // the annotator String primTier = tierName.split("\\[")[1].replace("]", ""); setAnnotationPrimCouple(primTier, annoPrimRelations, currColumn, headerRow); primWasFound = true; } String primOfAnnoFromConfig = getPrimOfAnnoPrimRel(tierName.split("\\[")[0]); if (primOfAnnoFromConfig != null) { // current tier is an annotation and the // belonging primary text was set by // property setAnnotationPrimCouple(primOfAnnoFromConfig, annoPrimRelations, currColumn, headerRow); primWasFound = true; } } else if (primaryTextTierList.size() == 1 && getProps().getAnnoPrimRel() == null && getProps().getShortAnnoPrimRel() == null) { // There is only one primary text so we can // safely assume this is the one // the annotation is connected to. setAnnotationPrimCouple(primaryTextTierList.get(0), annoPrimRelations, currColumn, headerRow); primWasFound = true; } } if (!primWasFound) { annosWithoutPrim.add(tierName); } currColumn++; } } } final Map<String, SLayer> layerTierCouples = getLayerTierCouples(); Table<Integer, Integer, CellRangeAddress> mergedCells = null; if (corpusSheet.getNumMergedRegions() > 0) { mergedCells = calculateMergedCellIndex(corpusSheet.getMergedRegions()); } int progressTotalNumberOfColumns = 0; if (!primTextPos.isEmpty()) { progressTotalNumberOfColumns = setPrimText(corpusSheet, primTextPos, annoPrimRelations, headerRow, mergedCells, layerTierCouples); } else { SpreadsheetImporter.logger .warn("No primary text for the document \"" + getResourceURI().lastSegment() + "\" found. Please check the spelling of your properties."); } setAnnotations(annoPrimRelations, corpusSheet, mergedCells, layerTierCouples, progressTotalNumberOfColumns); } if (getProps().getMetaAnnotation()) { setDocMetaData(workbook); } // report if any column was not included if (!annosWithoutPrim.isEmpty()) { SpreadsheetImporter.logger.warn( "No primary text column found for columns\n- {}\nin document {}. This means these columns are not included in the conversion!", Joiner.on("\n- ").join(annosWithoutPrim), getResourceURI().toFileString()); } } }