List of usage examples for org.apache.poi.ss.usermodel Sheet createDrawingPatriarch
Drawing<?> createDrawingPatriarch();
From source file:org.isisaddons.module.excel.dom.CellMarshaller.java
License:Apache License
private static void setCellComment(final Cell cell, final String commentText) { Sheet sheet = cell.getSheet(); Row row = cell.getRow();/* w w w. ja va2 s .c o m*/ Workbook workbook = sheet.getWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 3); Drawing drawing = sheet.createDrawingPatriarch(); Comment comment1 = drawing.createCellComment(anchor); RichTextString commentRtf = creationHelper.createRichTextString(commentText); comment1.setString(commentRtf); Comment comment = comment1; cell.setCellComment(comment); }
From source file:org.waterforpeople.mapping.dataexport.GraphicalSurveySummaryExporter.java
License:Open Source License
/** * Writes the report as an XLS document/* w ww . jav 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 } } } }
From source file:packtest.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet(); Cell cell1 = sheet.createRow(3).createCell(5); cell1.setCellValue("F4"); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1);//from ww w.j av a2s . co m comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //apply custom font to the text in the comment Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 14); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); // comment2.setAddress(new CellAddress("C3")); String fname = "comments.xlsx"; FileOutputStream out = new FileOutputStream(fname); wb.write(out); out.close(); wb.close(); }
From source file:packtest.LineChart.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("linechart"); final int NUM_OF_ROWS = 3; final int NUM_OF_COLUMNS = 10; // Create a row and put some cells in it. Rows are 0 based. Row row;/* ww w . j av a 2 s . co m*/ Cell cell; for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) { row = sheet.createRow((short) rowIndex); for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) { cell = row.createCell((short) colIndex); cell.setCellValue(colIndex * (rowIndex + 1)); } } Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15); Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); LineChartData data = chart.getChartDataFactory().createLineChartData(); // Use a category axis for the bottom axis. ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); data.addSeries(xs, ys1); data.addSeries(xs, ys2); chart.plot(data, bottomAxis, leftAxis); // Write the output to a file FileOutputStream fileOut = new FileOutputStream(Utils.getPath("ooxml-line-chart.xlsx")); wb.write(fileOut); fileOut.close(); }
From source file:packtest.ScatterChart.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet 1"); final int NUM_OF_ROWS = 3; final int NUM_OF_COLUMNS = 10; // Create a row and put some cells in it. Rows are 0 based. Row row;//from w w w .j a va 2s . c om Cell cell; for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) { row = sheet.createRow((short) rowIndex); for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) { cell = row.createCell((short) colIndex); cell.setCellValue(colIndex * (rowIndex + 1)); } } Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15); Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); ScatterChartData data = chart.getChartDataFactory().createScatterChartData(); ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); data.addSerie(xs, ys1); data.addSerie(xs, ys2); chart.plot(data, bottomAxis, leftAxis); // Write the output to a file FileOutputStream fileOut = new FileOutputStream(Utils.getPath("ooxml-scatter-chart.xlsx")); wb.write(fileOut); fileOut.close(); }
From source file:packtest.WorkingWithPictures.java
License:Apache License
public static void main(String[] args) throws IOException { //create a new workbook Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); try {// w w w . j a va2 s . co m CreationHelper helper = wb.getCreationHelper(); //add a picture in this workbook. InputStream is = new FileInputStream(args[0]); byte[] bytes = IOUtils.toByteArray(is); is.close(); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); //create sheet Sheet sheet = wb.createSheet(); //create drawing Drawing drawing = sheet.createDrawingPatriarch(); //add a picture shape ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(1); anchor.setRow1(1); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture pict.resize(2); //save workbook String file = "picture.xls"; if (wb instanceof XSSFWorkbook) file += "x"; // NOSONAR OutputStream fileOut = new FileOutputStream(file); try { wb.write(fileOut); } finally { fileOut.close(); } } finally { wb.close(); } }
From source file:panel.AnalysisPanel.java
void excel(Workbook wb, String name, double xData[], double yData[]) { // Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(name); //? ? final int NUM_OF_ROWS = 2; final int NUM_OF_COLUMNS = yData.length; // Create a row and put some cells in it. Rows are 0 based. Row row;/*from w w w .ja v a 2 s .co m*/ Cell cell; for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) { row = sheet.createRow((short) rowIndex); for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) { cell = row.createCell((short) colIndex); if (rowIndex == 0) { cell.setCellValue(xData[colIndex]); } if (rowIndex == 1) { cell.setCellValue(yData[colIndex]); } } } Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 25, 35); org.apache.poi.ss.usermodel.Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); LineChartData data = chart.getChartDataFactory().createLineChartData(); // Use a category axis for the bottom axis. ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); data.addSeries(xs, ys1); chart.plot(data, bottomAxis, leftAxis); }
From source file:poi.xssf.usermodel.examples.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet(); Cell cell1 = sheet.createRow(3).createCell(5); cell1.setCellValue("F4"); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1);/*from w ww . j a v a 2 s.com*/ comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //apply custom font to the text in the comment Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 14); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); comment2.setColumn(2); comment2.setRow(2); String fname = "comments.xlsx"; FileOutputStream out = new FileOutputStream(fname); wb.write(out); out.close(); }
From source file:poi.xssf.usermodel.examples.LineChart.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("linechart"); final int NUM_OF_ROWS = 3; final int NUM_OF_COLUMNS = 10; // Create a row and put some cells in it. Rows are 0 based. Row row;//from w w w. j a va 2 s . c o m Cell cell; for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) { row = sheet.createRow((short) rowIndex); for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) { cell = row.createCell((short) colIndex); cell.setCellValue(colIndex * (rowIndex + 1)); } } Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15); Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); LineChartData data = chart.getChartDataFactory().createLineChartData(); // Use a category axis for the bottom axis. ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); data.addSerie(xs, ys1); data.addSerie(xs, ys2); chart.plot(data, bottomAxis, leftAxis); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("ooxml-line-chart.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:poi.xssf.usermodel.examples.ScatterChart.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet 1"); final int NUM_OF_ROWS = 3; final int NUM_OF_COLUMNS = 10; // Create a row and put some cells in it. Rows are 0 based. Row row;/*w ww . ja va2s .c o m*/ Cell cell; for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) { row = sheet.createRow((short) rowIndex); for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) { cell = row.createCell((short) colIndex); cell.setCellValue(colIndex * (rowIndex + 1)); } } Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15); Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); ScatterChartData data = chart.getChartDataFactory().createScatterChartData(); ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); data.addSerie(xs, ys1); data.addSerie(xs, ys2); chart.plot(data, bottomAxis, leftAxis); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("ooxml-scatter-chart.xlsx"); wb.write(fileOut); fileOut.close(); }