List of usage examples for org.apache.poi.ss.usermodel Workbook getSheet
Sheet getSheet(String name);
From source file:org.suren.autotest.web.framework.data.ExcelDataSource.java
License:Apache License
/** * ?excel??/*from w w w .j av a2 s. co m*/ * @param inputStream * @throws IOException */ private void parse(InputStream inputStream) throws IOException { String name = targetPage.getClass().getName(); Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheet(name); if (sheet == null) { int index = name.lastIndexOf("."); if (index > 0) { name = name.substring(index + 1); sheet = workbook.getSheet(name); } } sheetParse(sheet); }
From source file:org.talend.dataprep.schema.xls.serialization.XlsRunnable.java
License:Open Source License
/** * @see Runnable#run()/*w w w . java 2s . c om*/ */ @Override public void run() { try { Workbook workbook = WorkbookFactory.create(rawContent); JsonGenerator generator = jsonFactory.createGenerator(jsonOutput); // if no sheet name just get the first one (take it easy mate :-) ) Sheet sheet = isEmpty(metadata.getSheetName()) ? workbook.getSheetAt(0) : workbook.getSheet(metadata.getSheetName()); if (sheet == null) { // auto generated sheet name so take care!! "sheet-" + i if (StringUtils.startsWith(metadata.getSheetName(), "sheet-")) { String sheetNumberStr = StringUtils.removeStart(metadata.getSheetName(), "sheet-"); sheet = workbook.getSheetAt(Integer.valueOf(sheetNumberStr)); } // still null so use the first one if (sheet == null) { sheet = workbook.getSheetAt(0); } } generator.writeStartArray(); List<ColumnMetadata> columns = metadata.getRowMetadata().getColumns(); serializeColumns(workbook, generator, sheet, columns); generator.writeEndArray(); generator.flush(); } catch (Exception e) { // Consumer may very well interrupt consumption of stream (in case of limit(n) use for sampling). // This is not an issue as consumer is allowed to partially consumes results, it's up to the // consumer to ensure data it consumed is consistent. LOG.debug("Unable to continue serialization for {}. Skipping remaining content.", metadata.getId(), e); } finally { try { jsonOutput.close(); } catch (IOException e) { LOG.error("Unable to close output", e); } } }
From source file:org.talend.dataprep.schema.xls.serialization.XlsxStreamRunnable.java
License:Open Source License
/** * @see Runnable#run()//from w ww . ja v a 2 s. c om */ @Override public void run() { try { JsonGenerator generator = jsonFactory.createGenerator(jsonOutput); Workbook workbook = StreamingReader.builder() // .bufferSize(4096) // .rowCacheSize(1) // .open(rawContent); try { Sheet sheet = StringUtils.isEmpty(metadata.getSheetName()) ? // workbook.getSheetAt(0) : workbook.getSheet(metadata.getSheetName()); generator.writeStartArray(); for (Row row : sheet) { if (limit > 0 && row.getRowNum() > limit) { break; } if (!XlsSerializer.isHeaderLine(row.getRowNum(), metadata.getRowMetadata().getColumns())) { generator.writeStartObject(); // data quality Analyzer doesn't like to not have all columns even if we don't have any values // so create so field with empty value otherwise we get exceptions int i = 0; for (ColumnMetadata columnMetadata : metadata.getRowMetadata().getColumns()) { Cell cell = row.getCell(i); String cellValue = cell == null ? null : cell.getStringCellValue(); // StringUtils.EMPTY generator.writeFieldName(columnMetadata.getId()); if (cellValue != null) { generator.writeString(cellValue); } else { generator.writeNull(); } i++; } generator.writeEndObject(); } } generator.writeEndArray(); generator.flush(); } finally { workbook.close(); } } catch (Exception e) { // Consumer may very well interrupt consumption of stream (in case of limit(n) use for sampling). // This is not an issue as consumer is allowed to partially consumes results, it's up to the // consumer to ensure data it consumed is consistent. LOG.debug("Unable to continue serialization for {}. Skipping remaining content.", metadata.getId(), e); } finally { try { jsonOutput.close(); } catch (IOException e) { LOG.error("Unable to close output", e); } } }
From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java
License:MIT License
/** * Copy the each command area to seperated sheet. As it will be used for * iteration./*w w w. jav a 2 s . c o m*/ * * @param sheet * sheet. */ private void copyTemplateForTieCommands(final Sheet sheet) { // if skip configuration. then return. if (parent.isSkipConfiguration()) { return; } Workbook wb = sheet.getWorkbook(); String copyName = TieConstants.COPY_SHEET_PREFIX + sheet.getSheetName(); if (wb.getSheet(copyName) == null) { Sheet newSheet = wb.cloneSheet(wb.getSheetIndex(sheet)); int sheetIndex = wb.getSheetIndex(newSheet); wb.setSheetName(sheetIndex, copyName); wb.setSheetHidden(sheetIndex, Workbook.SHEET_STATE_VERY_HIDDEN); } }
From source file:org.tiefaces.components.websheet.configuration.SheetConfiguration.java
License:MIT License
/** * recover the cell reference to the sheet. * /*from w ww. j a v a 2 s . co m*/ * @param wb * workbook. */ public void recover(final Workbook wb) { Sheet sheet = wb.getSheet(this.sheetName); this.getSerialCachedCells().recover(sheet); this.getSerialFinalCommentMap().recover(sheet); this.getFormCommand().recover(sheet); if (this.getShiftMap() != null) { for (Map.Entry<String, ConfigRangeAttrs> entry : this.getShiftMap().entrySet()) { entry.getValue().recover(sheet); } } if (this.getCommandIndexMap() != null) { for (Map.Entry<String, Command> entry : this.getCommandIndexMap().entrySet()) { entry.getValue().recover(sheet); } } }
From source file:org.tiefaces.components.websheet.utility.CommandUtility.java
License:MIT License
/** * Insert each template.// w w w. j a v a 2s . c o m * * @param sourceConfigRange * the source config range * @param configBuildRef * the config build ref * @param index * the index * @param insertPosition * the insert position * @param unitRowsMapping * the unit rows mapping */ public static void insertEachTemplate(final ConfigRange sourceConfigRange, final ConfigBuildRef configBuildRef, final int index, final int insertPosition, final RowsMapping unitRowsMapping) { int srcStartRow = sourceConfigRange.getFirstRowAddr().getRow(); int srcEndRow = sourceConfigRange.getLastRowPlusAddr().getRow() - 1; Sheet sheet = configBuildRef.getSheet(); Workbook wb = sheet.getWorkbook(); // excel sheet name has limit 31 chars String copyName = TieConstants.COPY_SHEET_PREFIX + sheet.getSheetName(); if (copyName.length() > TieConstants.EXCEL_SHEET_NAME_LIMIT) { copyName = copyName.substring(0, TieConstants.EXCEL_SHEET_NAME_LIMIT); } Sheet srcSheet = wb.getSheet(copyName); if (index > 0) { CellUtility.copyRows(srcSheet, sheet, srcStartRow, srcEndRow, insertPosition, false, true); } for (int rowIndex = srcStartRow; rowIndex <= srcEndRow; rowIndex++) { if (configBuildRef.getWatchList().contains(rowIndex) && (ConfigurationUtility.isStaticRow(sourceConfigRange, rowIndex))) { unitRowsMapping.addRow(rowIndex, sheet.getRow(insertPosition + rowIndex - srcStartRow)); } } }
From source file:org.unhcr.eg.odk.utilities.xlsform.controller.SheetProcessor.java
public static Survey processSurveySheet(Workbook wb, Survey survey) { Sheet sheet = wb.getSheet(XLSFormModel.SheetName.SURVEY.value()); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int i = 0;/*ww w . ja v a 2s .co m*/ while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); if (i == 0) { loadColumns(cellIterator, survey); } else { Question q = createQuestion(cellIterator, survey); String position = getQuestionPosition(q, survey); survey.getQuestions().put(new QuestionPosition(i, position, q.getName()), q); } i++; } return survey; }
From source file:org.unhcr.eg.odk.utilities.xlsform.controller.SheetProcessor.java
public static Survey processSettingsSheet(Workbook wb, Survey survey) { Sheet sheet = wb.getSheet(XLSFormModel.SheetName.SETTINGS.value()); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int i = 0;/*from www . ja v a 2s . c om*/ while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); if (i == 0) { loadSettingsColumns(cellIterator, survey); } else if (i == 1) { survey = createSetting(cellIterator, survey); } i++; } return survey; }
From source file:org.unhcr.eg.odk.utilities.xlsform.controller.SheetProcessor.java
public static Survey processChoicesSheet(Workbook wb, Survey survey) { Sheet sheet = wb.getSheet(XLSFormModel.SheetName.CHOICES.value()); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int i = 0;/*from ww w . j a v a 2 s . c om*/ while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); if (i == 0) { loadChoicesColumns(cellIterator, survey); } else { Item q = createChoice(cellIterator, survey); ListItem listItem = survey.getChoices().get(q.getListName()); if (listItem == null) { listItem = new ListItem(q.getListName()); } listItem.getListOfItems().put(q.getName(), q); survey.getChoices().put(listItem.getName(), listItem); } i++; } return survey; }
From source file:org.waterforpeople.mapping.dataexport.GraphicalSurveySummaryExporter.java
License:Open Source License
/** * Writes the report as an XLS document/*from w w w. j a v a 2s . c o m*/ */ private void writeSummaryReport(Map<QuestionGroupDto, List<QuestionDto>> questionMap, SummaryModel summaryModel, String sector, Workbook wb) throws Exception { String title = sector == null ? SUMMARY_LABEL.get(locale) : sector; Sheet sheet = null; int sheetCount = 2; String curTitle = WorkbookUtil.createSafeSheetName(title); while (sheet == null) { sheet = wb.getSheet(curTitle); if (sheet == null) { sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(curTitle)); } else { sheet = null; curTitle = title + " " + sheetCount; sheetCount++; } } CreationHelper creationHelper = wb.getCreationHelper(); Drawing patriarch = sheet.createDrawingPatriarch(); int curRow = 0; Row row = getRow(curRow++, sheet); if (sector == null) { createCell(row, 0, REPORT_HEADER.get(locale), headerStyle); } else { createCell(row, 0, sector + " " + REPORT_HEADER.get(locale), headerStyle); } for (QuestionGroupDto group : orderedGroupList) { if (questionMap.get(group) != null) { for (QuestionDto question : questionMap.get(group)) { if (!(QuestionType.OPTION == question.getType() || QuestionType.NUMBER == question.getType())) { continue; } else { if (summaryModel.getResponseCountsForQuestion(question.getKeyId(), sector).size() == 0) { // if there is no data, skip the question continue; } } // for both options and numeric, we want a pie chart and // data table for numeric, we also want descriptive // statistics int tableTopRow = curRow++; int tableBottomRow = curRow; row = getRow(tableTopRow, sheet); // span the question heading over the data table sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 2)); createCell(row, 0, getLocalizedText(question.getText(), question.getTranslationMap()), headerStyle); DescriptiveStats stats = summaryModel.getDescriptiveStatsForQuestion(question.getKeyId(), sector); if (stats != null && stats.getSampleCount() > 0) { sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 4, 5)); createCell(row, 4, getLocalizedText(question.getText(), question.getTranslationMap()), headerStyle); } row = getRow(curRow++, sheet); createCell(row, 1, FREQ_LABEL.get(locale), headerStyle); createCell(row, 2, PCT_LABEL.get(locale), headerStyle); // now create the data table for the option count Map<String, Long> counts = summaryModel.getResponseCountsForQuestion(question.getKeyId(), sector); int sampleTotal = 0; List<String> labels = new ArrayList<String>(); List<String> values = new ArrayList<String>(); int firstOptRow = curRow; for (Entry<String, Long> count : counts.entrySet()) { row = getRow(curRow++, sheet); String labelText = count.getKey(); if (labelText == null) { labelText = ""; } StringBuilder builder = new StringBuilder(); if (QuestionType.OPTION == question.getType() && !DEFAULT_LOCALE.equals(locale)) { String[] tokens = labelText.split("\\|"); // see if we have a translation for this option for (int i = 0; i < tokens.length; i++) { if (i > 0) { builder.append("|"); } if (question.getOptionContainerDto() != null && question.getOptionContainerDto().getOptionsList() != null) { boolean found = false; for (QuestionOptionDto opt : question.getOptionContainerDto() .getOptionsList()) { if (opt.getText() != null && opt.getText().trim().equalsIgnoreCase(tokens[i])) { builder.append(getLocalizedText(tokens[i], opt.getTranslationMap())); found = true; break; } } if (!found) { builder.append(tokens[i]); } } } } else { builder.append(labelText); } createCell(row, 0, builder.toString(), null); createCell(row, 1, count.getValue().toString(), null); labels.add(builder.toString()); values.add(count.getValue().toString()); sampleTotal += count.getValue(); } row = getRow(curRow++, sheet); createCell(row, 0, TOTAL_LABEL.get(locale), null); createCell(row, 1, sampleTotal + "", null); for (int i = 0; i < values.size(); i++) { row = getRow(firstOptRow + i, sheet); if (sampleTotal > 0) { createCell(row, 2, PCT_FMT.format((Double.parseDouble(values.get(i)) / sampleTotal)), null); } else { createCell(row, 2, PCT_FMT.format(0), null); } } tableBottomRow = curRow; if (stats != null && stats.getSampleCount() > 0) { int tempRow = tableTopRow + 1; row = getRow(tempRow++, sheet); createCell(row, 4, "N", null); createCell(row, 5, sampleTotal + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MEAN_LABEL.get(locale), null); createCell(row, 5, stats.getMean() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, STD_E_LABEL.get(locale), null); createCell(row, 5, stats.getStandardError() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MEDIAN_LABEL.get(locale), null); createCell(row, 5, stats.getMedian() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MODE_LABEL.get(locale), null); createCell(row, 5, stats.getMode() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, STD_D_LABEL.get(locale), null); createCell(row, 5, stats.getStandardDeviation() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, VAR_LABEL.get(locale), null); createCell(row, 5, stats.getVariance() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, RANGE_LABEL.get(locale), null); createCell(row, 5, stats.getRange() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MIN_LABEL.get(locale), null); createCell(row, 5, stats.getMin() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MAX_LABEL.get(locale), null); createCell(row, 5, stats.getMax() + "", null); if (tableBottomRow < tempRow) { tableBottomRow = tempRow; } } curRow = tableBottomRow; if (labels.size() > 0) { boolean hasVals = false; if (values != null) { for (String val : values) { try { if (val != null && new Double(val.trim()) > 0D) { hasVals = true; break; } } catch (Exception e) { // no-op } } } // only insert the image if we have at least 1 non-zero // value if (hasVals && generateCharts) { // now insert the graph int indx = wb.addPicture(JFreechartChartUtil.getPieChart(labels, values, getLocalizedText(question.getText(), question.getTranslationMap()), CHART_WIDTH, CHART_HEIGHT), Workbook.PICTURE_TYPE_PNG); ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setDx1(0); anchor.setDy1(0); anchor.setDx2(0); anchor.setDy2(255); anchor.setCol1(6); anchor.setRow1(tableTopRow); anchor.setCol2(6 + CHART_CELL_WIDTH); anchor.setRow2(tableTopRow + CHART_CELL_HEIGHT); anchor.setAnchorType(2); patriarch.createPicture(anchor, indx); if (tableTopRow + CHART_CELL_HEIGHT > tableBottomRow) { curRow = tableTopRow + CHART_CELL_HEIGHT; } } } // add a blank row between questions getRow(curRow++, sheet); // flush the sheet so far to disk; we will not go back up ((SXSSFSheet) sheet).flushRows(0); // retain 0 last rows and // flush all others } } } }