Examples with XSSFWorkbook org.apache.poi.xssf.usermodel.XSSFWorkbook used on opensource projects

Example 96 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project jgnash by ccavanaugh.

the class AccountExport method exportAccount.

public static void exportAccount(final Account account, final String[] columnNames, final LocalDate startDate, final LocalDate endDate, final File file) {
    Objects.requireNonNull(account);
    Objects.requireNonNull(startDate);
    Objects.requireNonNull(endDate);
    Objects.requireNonNull(file);
    Objects.requireNonNull(columnNames);
    final String extension = FileUtils.getFileExtension(file.getAbsolutePath());
    try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) {
        final CreationHelper createHelper = wb.getCreationHelper();
        // create a new sheet
        final Sheet s = wb.createSheet(account.getName());
        // create 2 fonts objects
        final Font defaultFont = wb.createFont();
        final Font headerFont = wb.createFont();
        defaultFont.setFontHeightInPoints((short) 10);
        defaultFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setFontHeightInPoints((short) 11);
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // create header cell styles
        final CellStyle headerStyle = wb.createCellStyle();
        // Set the other cell style and formatting
        headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headerStyle.setBorderTop(CellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headerStyle.setBorderRight(CellStyle.BORDER_THIN);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        DataFormat df_header = wb.createDataFormat();
        headerStyle.setDataFormat(df_header.getFormat("text"));
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        final CellStyle dateStyle = wb.createCellStyle();
        dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yy"));
        dateStyle.setFont(defaultFont);
        final CellStyle timestampStyle = wb.createCellStyle();
        timestampStyle.setDataFormat(createHelper.createDataFormat().getFormat("YYYY-MM-DD HH:MM:SS"));
        timestampStyle.setFont(defaultFont);
        final CellStyle textStyle = wb.createCellStyle();
        textStyle.setFont(defaultFont);
        final CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(defaultFont);
        amountStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
        final String pattern = format.toLocalizedPattern().replace("¤", account.getCurrencyNode().getPrefix());
        final DataFormat df = wb.createDataFormat();
        amountStyle.setDataFormat(df.getFormat(pattern));
        // Create headers
        int row = 0;
        Row r = s.createRow(row);
        for (int i = 0; i < columnNames.length; i++) {
            Cell c = r.createCell(i);
            c.setCellValue(createHelper.createRichTextString(columnNames[i]));
            c.setCellStyle(headerStyle);
        }
        // Dump the transactions
        for (final Transaction transaction : account.getTransactions(startDate, endDate)) {
            r = s.createRow(++row);
            int col = 0;
            // date
            Cell c = r.createCell(col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(DateUtils.asDate(transaction.getLocalDate()));
            c.setCellStyle(dateStyle);
            // timestamp
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(DateUtils.asDate(transaction.getTimestamp()));
            c.setCellStyle(timestampStyle);
            // number
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getNumber());
            c.setCellStyle(textStyle);
            // payee
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getPayee());
            c.setCellStyle(textStyle);
            // memo
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getMemo());
            c.setCellStyle(textStyle);
            // account
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(getAccountColumnValue(transaction, account));
            c.setCellStyle(textStyle);
            // clr
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getReconciled(account).toString());
            c.setCellStyle(textStyle);
            final BigDecimal amount = transaction.getAmount(account);
            // increase
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            if (amount.signum() >= 0) {
                c.setCellValue(amount.doubleValue());
            }
            c.setCellStyle(amountStyle);
            // decrease
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            if (amount.signum() < 0) {
                c.setCellValue(amount.abs().doubleValue());
            }
            c.setCellStyle(amountStyle);
            // balance
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(account.getBalanceAt(transaction).doubleValue());
            c.setCellStyle(amountStyle);
        }
        // autosize the column widths
        final short columnCount = s.getRow(1).getLastCellNum();
        // autosize all of the columns + 10 pixels
        for (int i = 0; i <= columnCount; i++) {
            s.autoSizeColumn(i);
            s.setColumnWidth(i, s.getColumnWidth(i) + 10);
        }
        Logger.getLogger(AccountExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles());
        // Save
        final String filename;
        if (wb instanceof XSSFWorkbook) {
            filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xlsx";
        } else {
            filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xls";
        }
        try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) {
            wb.write(out);
        } catch (final Exception e) {
            Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
        }
    } catch (final IOException e) {
        Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

Example 97 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project chilo-producer by cccties.

the class SettingReader method read.

public Series read() throws Epub3MakerException {
    try {
        workBook = new XSSFWorkbook(new FileInputStream(filePath.toString()));
        meta = readMetaSheet();
        bookList = readBookList();
        Map<Integer, Book> books = readVolSheets();
        return new Series(meta, books);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            workBook.close();
            workBook = null;
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    return null;
}

Example 98 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project dhis2-core by dhis2.

the class ExcelNodeSerializer method startSerialize.

@Override
protected void startSerialize(RootNode rootNode, OutputStream outputStream) throws Exception {
    workbook = new XSSFWorkbook();
    sheet = workbook.createSheet("Sheet1");
    XSSFFont boldFont = workbook.createFont();
    boldFont.setBold(true);
    XSSFCellStyle boldCellStyle = workbook.createCellStyle();
    boldCellStyle.setFont(boldFont);
    // build schema
    for (Node child : rootNode.getChildren()) {
        if (child.isCollection()) {
            if (!child.getChildren().isEmpty()) {
                Node node = child.getChildren().get(0);
                XSSFRow row = sheet.createRow(0);
                int cellIdx = 0;
                for (Node property : node.getChildren()) {
                    if (property.isSimple()) {
                        XSSFCell cell = row.createCell(cellIdx++);
                        cell.setCellValue(property.getName());
                        cell.setCellStyle(boldCellStyle);
                    }
                }
            }
        }
    }
}

Example 99 with XSSFWorkbook

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

the class AbstractExcelSheetTest method writeCell.

@Test
public void writeCell() throws Exception {
    final Workbook workbook = new XSSFWorkbook();
    final ExcelSheet excelSheet = new MockExcelSheet(workbook);
    final Sheet sheet = workbook.createSheet("sheet");
    final Row row = sheet.createRow(0);
    excelSheet.writeCell(null, row, 0);
    excelSheet.writeCell(Boolean.TRUE, row, 1);
    excelSheet.writeCell("text", row, 2);
    excelSheet.writeCell(1, row, 3);
    Assert.assertEquals(Cell.CELL_TYPE_BLANK, row.getCell(0).getCellType());
    Assert.assertEquals("Yes", row.getCell(1).getStringCellValue());
    Assert.assertEquals(Cell.CELL_TYPE_STRING, row.getCell(2).getCellType());
    Assert.assertEquals(Cell.CELL_TYPE_NUMERIC, row.getCell(3).getCellType());
}

Example 100 with XSSFWorkbook

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

the class TotalCancelledTendersExcelControllerTest method cancelledTendersByYearByRationaleExcelChart.

@Test
public void cancelledTendersByYearByRationaleExcelChart() throws Exception {
    LangYearFilterPagingRequest filter = getLangYearFilterMockRequest();
    totalCancelledTendersExcelController.cancelledTendersByYearByRationaleExcelChart(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:cancelledFunding: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);
}