Search in sources :

Example 11 with XSSFDrawing

use of org.apache.poi.xssf.usermodel.XSSFDrawing in project poi by apache.

the class XSSFExcelExtractor method getText.

/**
     * Retrieves the text contents of the file
     */
public String getText() {
    DataFormatter formatter;
    if (locale == null) {
        formatter = new DataFormatter();
    } else {
        formatter = new DataFormatter(locale);
    }
    StringBuffer text = new StringBuffer();
    for (Sheet sh : workbook) {
        XSSFSheet sheet = (XSSFSheet) sh;
        if (includeSheetNames) {
            text.append(sheet.getSheetName()).append("\n");
        }
        // Header(s), if present
        if (includeHeadersFooters) {
            text.append(extractHeaderFooter(sheet.getFirstHeader()));
            text.append(extractHeaderFooter(sheet.getOddHeader()));
            text.append(extractHeaderFooter(sheet.getEvenHeader()));
        }
        // Rows and cells
        for (Object rawR : sheet) {
            Row row = (Row) rawR;
            for (Iterator<Cell> ri = row.cellIterator(); ri.hasNext(); ) {
                Cell cell = ri.next();
                // Is it a formula one?
                if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    if (formulasNotResults) {
                        String contents = cell.getCellFormula();
                        checkMaxTextSize(text, contents);
                        text.append(contents);
                    } else {
                        if (cell.getCachedFormulaResultTypeEnum() == CellType.STRING) {
                            handleStringCell(text, cell);
                        } else {
                            handleNonStringCell(text, cell, formatter);
                        }
                    }
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    handleStringCell(text, cell);
                } else {
                    handleNonStringCell(text, cell, formatter);
                }
                // Output the comment, if requested and exists
                Comment comment = cell.getCellComment();
                if (includeCellComments && comment != null) {
                    // Replace any newlines with spaces, otherwise it
                    //  breaks the output
                    String commentText = comment.getString().getString().replace('\n', ' ');
                    checkMaxTextSize(text, commentText);
                    text.append(" Comment by ").append(comment.getAuthor()).append(": ").append(commentText);
                }
                if (ri.hasNext()) {
                    text.append("\t");
                }
            }
            text.append("\n");
        }
        // add textboxes
        if (includeTextBoxes) {
            XSSFDrawing drawing = sheet.getDrawingPatriarch();
            if (drawing != null) {
                for (XSSFShape shape : drawing.getShapes()) {
                    if (shape instanceof XSSFSimpleShape) {
                        String boxText = ((XSSFSimpleShape) shape).getText();
                        if (boxText.length() > 0) {
                            text.append(boxText);
                            text.append('\n');
                        }
                    }
                }
            }
        }
        // Finally footer(s), if present
        if (includeHeadersFooters) {
            text.append(extractHeaderFooter(sheet.getFirstFooter()));
            text.append(extractHeaderFooter(sheet.getOddFooter()));
            text.append(extractHeaderFooter(sheet.getEvenFooter()));
        }
    }
    return text.toString();
}
Also used : Comment(org.apache.poi.ss.usermodel.Comment) XSSFSimpleShape(org.apache.poi.xssf.usermodel.XSSFSimpleShape) XSSFShape(org.apache.poi.xssf.usermodel.XSSFShape) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) Row(org.apache.poi.ss.usermodel.Row) Sheet(org.apache.poi.ss.usermodel.Sheet) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) Cell(org.apache.poi.ss.usermodel.Cell) XSSFCell(org.apache.poi.xssf.usermodel.XSSFCell) XSSFDrawing(org.apache.poi.xssf.usermodel.XSSFDrawing) DataFormatter(org.apache.poi.ss.usermodel.DataFormatter)

Example 12 with XSSFDrawing

use of org.apache.poi.xssf.usermodel.XSSFDrawing in project ocvn by devgateway.

the class TotalCancelledTendersExcelControllerTest method cancelledFundingExcelChart.

@Test
public void cancelledFundingExcelChart() throws Exception {
    LangYearFilterPagingRequest filter = getLangYearFilterMockRequest();
    totalCancelledTendersExcelController.cancelledFundingExcelChart(filter, mockHttpServletResponse);
    final byte[] responseOutput = mockHttpServletResponse.getContentAsByteArray();
    final Workbook workbook = new XSSFWorkbook(new ByteArrayInputStream(responseOutput));
    Assert.assertNotNull(workbook);
    final Sheet sheet = workbook.getSheet(ChartType.area.toString());
    Assert.assertNotNull("check chart type, sheet name should be the same as the type", sheet);
    final XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
    final List<XSSFChart> charts = drawing.getCharts();
    Assert.assertEquals("number of charts", 1, charts.size());
    final XSSFChart chart = charts.get(0);
    Assert.assertEquals("chart title", translationService.getValue(filter.getLanguage(), "charts:cancelledAmounts:title"), chart.getTitle().getString());
    final List<? extends XSSFChartAxis> axis = chart.getAxis();
    Assert.assertEquals("number of axis", 2, axis.size());
    final CTChart ctChart = chart.getCTChart();
    Assert.assertEquals("Check if we have 1 area chart", 1, ctChart.getPlotArea().getAreaChartArray().length);
}
Also used : XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) LangYearFilterPagingRequest(org.devgateway.ocds.web.rest.controller.request.LangYearFilterPagingRequest) CTChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTChart) ByteArrayInputStream(java.io.ByteArrayInputStream) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Sheet(org.apache.poi.ss.usermodel.Sheet) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Workbook(org.apache.poi.ss.usermodel.Workbook) XSSFDrawing(org.apache.poi.xssf.usermodel.XSSFDrawing) Test(org.junit.Test)

Example 13 with XSSFDrawing

use of org.apache.poi.xssf.usermodel.XSSFDrawing in project ocvn by devgateway.

the class AverageNumberOfTenderersExcelControllerTest method averageNumberBidsExcelChart.

@Test
public void averageNumberBidsExcelChart() throws Exception {
    LangYearFilterPagingRequest filter = getLangYearFilterMockRequest();
    averageNumberOfTenderersExcelController.averageNumberBidsExcelChart(filter, mockHttpServletResponse);
    final byte[] responseOutput = mockHttpServletResponse.getContentAsByteArray();
    final Workbook workbook = new XSSFWorkbook(new ByteArrayInputStream(responseOutput));
    Assert.assertNotNull(workbook);
    final Sheet sheet = workbook.getSheet(ChartType.barcol.toString());
    Assert.assertNotNull("check chart type, sheet name should be the same as the type", sheet);
    final XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
    final List<XSSFChart> charts = drawing.getCharts();
    Assert.assertEquals("number of charts", 1, charts.size());
    final XSSFChart chart = charts.get(0);
    Assert.assertEquals("chart title", translationService.getValue(filter.getLanguage(), "charts:avgNrBids:title"), chart.getTitle().getString());
    final List<? extends XSSFChartAxis> axis = chart.getAxis();
    Assert.assertEquals("number of axis", 2, axis.size());
    final CTChart ctChart = chart.getCTChart();
    Assert.assertEquals("Check if we have 1 bar chart", 1, ctChart.getPlotArea().getBarChartArray().length);
}
Also used : XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) LangYearFilterPagingRequest(org.devgateway.ocds.web.rest.controller.request.LangYearFilterPagingRequest) CTChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTChart) ByteArrayInputStream(java.io.ByteArrayInputStream) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Sheet(org.apache.poi.ss.usermodel.Sheet) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Workbook(org.apache.poi.ss.usermodel.Workbook) XSSFDrawing(org.apache.poi.xssf.usermodel.XSSFDrawing) Test(org.junit.Test)

Example 14 with XSSFDrawing

use of org.apache.poi.xssf.usermodel.XSSFDrawing in project ocvn by devgateway.

the class AverageTenderAndAwardsExcelControllerTest method bidTimelineExcelChart.

@Test
public void bidTimelineExcelChart() throws Exception {
    LangYearFilterPagingRequest filter = getLangYearFilterMockRequest();
    averageTenderAndAwardsExcelController.bidTimelineExcelChart(filter, mockHttpServletResponse);
    final byte[] responseOutput = mockHttpServletResponse.getContentAsByteArray();
    final Workbook workbook = new XSSFWorkbook(new ByteArrayInputStream(responseOutput));
    Assert.assertNotNull(workbook);
    final Sheet sheet = workbook.getSheet(ChartType.stackedbar.toString());
    Assert.assertNotNull("check chart type, sheet name should be the same as the type", sheet);
    final XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
    final List<XSSFChart> charts = drawing.getCharts();
    Assert.assertEquals("number of charts", 1, charts.size());
    final XSSFChart chart = charts.get(0);
    Assert.assertEquals("chart title", translationService.getValue(filter.getLanguage(), "charts:bidPeriod:title"), chart.getTitle().getString());
    final List<? extends XSSFChartAxis> axis = chart.getAxis();
    Assert.assertEquals("number of axis", 2, axis.size());
    final CTChart ctChart = chart.getCTChart();
    Assert.assertEquals("Check if we have 1 bar chart", 1, ctChart.getPlotArea().getBarChartArray().length);
}
Also used : XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) LangYearFilterPagingRequest(org.devgateway.ocds.web.rest.controller.request.LangYearFilterPagingRequest) CTChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTChart) ByteArrayInputStream(java.io.ByteArrayInputStream) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Sheet(org.apache.poi.ss.usermodel.Sheet) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Workbook(org.apache.poi.ss.usermodel.Workbook) XSSFDrawing(org.apache.poi.xssf.usermodel.XSSFDrawing) Test(org.junit.Test)

Example 15 with XSSFDrawing

use of org.apache.poi.xssf.usermodel.XSSFDrawing in project ocvn by devgateway.

the class CostEffectivenessExcelControllerTest method costEffectivenessExcelChart.

@Test
public void costEffectivenessExcelChart() throws Exception {
    LangGroupingFilterPagingRequest filter = getLangGroupingFilterMockRequest();
    costEffectivenessExcelController.costEffectivenessExcelChart(filter, mockHttpServletResponse);
    final byte[] responseOutput = mockHttpServletResponse.getContentAsByteArray();
    final Workbook workbook = new XSSFWorkbook(new ByteArrayInputStream(responseOutput));
    Assert.assertNotNull(workbook);
    final Sheet sheet = workbook.getSheet(ChartType.stackedcol.toString());
    Assert.assertNotNull("check chart type, sheet name should be the same as the type", sheet);
    final XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
    final List<XSSFChart> charts = drawing.getCharts();
    Assert.assertEquals("number of charts", 1, charts.size());
    final XSSFChart chart = charts.get(0);
    Assert.assertEquals("chart title", translationService.getValue(filter.getLanguage(), "charts:costEffectiveness:title"), chart.getTitle().getString());
    final List<? extends XSSFChartAxis> axis = chart.getAxis();
    Assert.assertEquals("number of axis", 2, axis.size());
    final CTChart ctChart = chart.getCTChart();
    Assert.assertEquals("Check if we have 1 bar chart", 1, ctChart.getPlotArea().getBarChartArray().length);
}
Also used : XSSFChart(org.apache.poi.xssf.usermodel.XSSFChart) CTChart(org.openxmlformats.schemas.drawingml.x2006.chart.CTChart) ByteArrayInputStream(java.io.ByteArrayInputStream) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Sheet(org.apache.poi.ss.usermodel.Sheet) LangGroupingFilterPagingRequest(org.devgateway.ocds.web.rest.controller.request.LangGroupingFilterPagingRequest) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Workbook(org.apache.poi.ss.usermodel.Workbook) XSSFDrawing(org.apache.poi.xssf.usermodel.XSSFDrawing) Test(org.junit.Test)

Aggregations

XSSFDrawing (org.apache.poi.xssf.usermodel.XSSFDrawing)19 Sheet (org.apache.poi.ss.usermodel.Sheet)17 XSSFChart (org.apache.poi.xssf.usermodel.XSSFChart)16 Workbook (org.apache.poi.ss.usermodel.Workbook)15 Test (org.junit.Test)15 CTChart (org.openxmlformats.schemas.drawingml.x2006.chart.CTChart)15 ByteArrayInputStream (java.io.ByteArrayInputStream)14 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)14 LangYearFilterPagingRequest (org.devgateway.ocds.web.rest.controller.request.LangYearFilterPagingRequest)13 Comment (org.apache.poi.ss.usermodel.Comment)2 XSSFClientAnchor (org.apache.poi.xssf.usermodel.XSSFClientAnchor)2 XSSFSheet (org.apache.poi.xssf.usermodel.XSSFSheet)2 Cell (org.apache.poi.ss.usermodel.Cell)1 DataFormatter (org.apache.poi.ss.usermodel.DataFormatter)1 Row (org.apache.poi.ss.usermodel.Row)1 XSSFCell (org.apache.poi.xssf.usermodel.XSSFCell)1 XSSFPicture (org.apache.poi.xssf.usermodel.XSSFPicture)1 XSSFRichTextString (org.apache.poi.xssf.usermodel.XSSFRichTextString)1 XSSFShape (org.apache.poi.xssf.usermodel.XSSFShape)1 XSSFSimpleShape (org.apache.poi.xssf.usermodel.XSSFSimpleShape)1