Example usage for org.apache.poi.ss.usermodel Workbook write

List of usage examples for org.apache.poi.ss.usermodel Workbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. write

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook write.

Prototype

void write(OutputStream stream) throws IOException;

Source Link

Document

Write out this workbook to an Outputstream.

Usage

From source file:itpreneurs.itp.report.archive.LoanCalculator.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else// w  w w .  j  av  a2  s. co m
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/loan-calculator.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:javafxapplication12.FXMLDocumentController.java

public void parse(File file, ArrayList<Check> list) throws FileNotFoundException, IOException {
    Workbook book = new HSSFWorkbook();
    Sheet sheet = book.createSheet("new Sheet");
    int i = 0;/*from   w  w w.  j  a va2s. co m*/
    while (i != list.size()) {
        Row row = sheet.createRow(i);
        row.createCell(0).setCellValue(list.get(i).getId());
        row.createCell(1).setCellValue(list.get(i).getCondition());
        row.createCell(2).setCellValue(list.get(i).getResult());
        row.createCell(3).setCellValue(list.get(i).getDate().toString());
        row.createCell(4).setCellValue(list.get(i).getResponsible());
        i++;
    }
    sheet.autoSizeColumn(1);
    FileOutputStream out = new FileOutputStream(file);
    book.write(out);
    out.close();
}

From source file:javafxapplication7.Main_controller.java

private void exportExcel() throws FileNotFoundException, IOException {
    setBounds(0, 0, 500, 500);/*w  ww.  j a  va2  s. c o m*/
    JFileChooser dialog = new JFileChooser();
    dialog.setFileSelectionMode(DIRECTORIES_ONLY);
    dialog.showOpenDialog(this);
    File file = dialog.getSelectedFile();
    setVisible(true);

    System.out.println(file);

    if (file != null) {
        setVisible(false);
        Workbook wb = new HSSFWorkbook();
        //Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");

        // Create a row and put some cells in it. Rows are 0 based.
        for (int i = 0; i < usersData.size(); i++) {
            Row row = sheet.createRow((short) i);
            // Or do it on one line. 
            row.createCell(0).setCellValue(createHelper.createRichTextString(String.valueOf("P-41")));
            row.createCell(1).setCellValue(true);
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(file + "/Export.xls");
        wb.write(fileOut);
        fileOut.close();
    }
}

From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java

License:Apache License

public void save(OutputStream out, ReportBook reportBook) throws SaveReportException {
    Workbook wb = createWorkbook();

    Set<String> titles = new HashSet<>();
    for (ReportModel model : reportBook) {
        String reportTitle = model.getReportTitle();
        if (reportTitle.length() > 26)
            reportTitle = reportTitle.substring(0, 26);
        String title = reportTitle;
        int n = 1;
        while (titles.contains(reportTitle.toUpperCase())) {
            reportTitle = title + "(" + n++ + ")";
        }//from ww w . j av  a  2 s.  c o  m
        titles.add(reportTitle.toUpperCase());
        saveSheet(wb, model, reportBook, reportTitle);
    }

    try {
        wb.write(out);
    } catch (IOException e) {
        throw new SaveReportException(e);
    }

}

From source file:jgnash.engine.budget.BudgetResultsExport.java

License:Open Source License

public static void exportBudgetResultsModel(final File file, final BudgetResultsModel model) {

    Resource rb = Resource.get();

    Workbook wb;

    String extension = FileUtils.getFileExtension(file.getAbsolutePath());

    if (extension.equals("xlsx")) {
        wb = new XSSFWorkbook();
    } else {//from   ww  w  .j a v  a2s  .  c om
        wb = new HSSFWorkbook();
    }

    CreationHelper createHelper = wb.getCreationHelper();

    // create a new sheet
    Sheet s = wb.createSheet(model.getBudget().getName());

    // create header cell styles
    CellStyle headerStyle = wb.createCellStyle();

    // create 2 fonts objects
    Font amountFont = wb.createFont();
    Font headerFont = wb.createFont();

    amountFont.setFontHeightInPoints((short) 10);
    amountFont.setColor(IndexedColors.BLACK.getIndex());

    headerFont.setFontHeightInPoints((short) 11);
    headerFont.setColor(IndexedColors.BLACK.getIndex());
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

    DataFormat df = wb.createDataFormat();

    // 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);

    headerStyle.setDataFormat(df.getFormat("text"));
    headerStyle.setFont(headerFont);
    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);

    int row = 0;
    Row r = s.createRow(row);

    // create period headers
    for (int i = 0; i < model.getDescriptorList().size(); i++) {
        Cell c = r.createCell(i * 3 + 1);
        c.setCellValue(
                createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription()));
        c.setCellStyle(headerStyle);
        s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3));
    }

    {
        int col = model.getDescriptorList().size() * 3 + 1;
        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary")));
        c.setCellStyle(headerStyle);
        s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2));
    }

    // create results header columns
    row++;
    r = s.createRow(row);

    {
        Cell c = r.createCell(0);
        c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account")));
        c.setCellStyle(headerStyle);

        for (int i = 0; i <= model.getDescriptorList().size(); i++) {
            c = r.createCell(i * 3 + 1);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted")));
            c.setCellStyle(headerStyle);

            c = r.createCell(i * 3 + 2);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Change")));
            c.setCellStyle(headerStyle);

            c = r.createCell(i * 3 + 3);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining")));
            c.setCellStyle(headerStyle);
        }
    }

    // must sort the accounts, otherwise child structure is not correct
    List<Account> accounts = new ArrayList<>(model.getAccounts());
    Collections.sort(accounts);

    // create account rows
    for (Account account : accounts) {

        CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(amountFont);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
        String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix());
        amountStyle.setDataFormat(df.getFormat(pattern));

        row++;

        int col = 0;

        r = s.createRow(row);

        CellStyle cs = wb.createCellStyle();
        cs.cloneStyleFrom(headerStyle);
        cs.setAlignment(CellStyle.ALIGN_LEFT);
        cs.setIndention((short) (model.getDepth(account) * 2));

        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(account.getName()));
        c.setCellStyle(cs);

        List<CellReference> budgetedRefList = new ArrayList<>();
        List<CellReference> changeRefList = new ArrayList<>();
        List<CellReference> remainingRefList = new ArrayList<>();

        for (int i = 0; i < model.getDescriptorList().size(); i++) {

            BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getBudgeted().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference budgetedRef = new CellReference(row, col);
            budgetedRefList.add(budgetedRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getChange().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference changeRef = new CellReference(row, col);
            changeRefList.add(changeRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellStyle(amountStyle);
            c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());

            CellReference remainingRef = new CellReference(row, col);
            remainingRefList.add(remainingRef);
        }

        // add summary columns                               
        addSummaryCell(r, ++col, budgetedRefList, amountStyle);
        addSummaryCell(r, ++col, changeRefList, amountStyle);
        addSummaryCell(r, ++col, remainingRefList, amountStyle);
    }

    // add group summary rows
    for (AccountGroup group : model.getAccountGroupList()) {

        CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(amountFont);
        amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        amountStyle.setBorderBottom(CellStyle.BORDER_THIN);
        amountStyle.setBorderTop(CellStyle.BORDER_THIN);
        amountStyle.setBorderLeft(CellStyle.BORDER_THIN);
        amountStyle.setBorderRight(CellStyle.BORDER_THIN);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
        String pattern = format.toLocalizedPattern().replace("", model.getBaseCurrency().getPrefix());
        amountStyle.setDataFormat(df.getFormat(pattern));

        row++;

        int col = 0;

        r = s.createRow(row);

        CellStyle cs = wb.createCellStyle();
        cs.cloneStyleFrom(headerStyle);
        cs.setAlignment(CellStyle.ALIGN_LEFT);

        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(group.toString()));
        c.setCellStyle(cs);

        List<CellReference> budgetedRefList = new ArrayList<>();
        List<CellReference> changeRefList = new ArrayList<>();
        List<CellReference> remainingRefList = new ArrayList<>();

        for (int i = 0; i < model.getDescriptorList().size(); i++) {

            BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getBudgeted().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference budgetedRef = new CellReference(row, col);
            budgetedRefList.add(budgetedRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getChange().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference changeRef = new CellReference(row, col);
            changeRefList.add(changeRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellStyle(amountStyle);
            c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());

            CellReference remainingRef = new CellReference(row, col);
            remainingRefList.add(remainingRef);
        }

        // add summary columns                               
        addSummaryCell(r, ++col, budgetedRefList, amountStyle);
        addSummaryCell(r, ++col, changeRefList, amountStyle);
        addSummaryCell(r, ++col, remainingRefList, amountStyle);
    }

    // force evaluation
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    evaluator.evaluateAll();

    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);
    }

    // Save
    String filename = file.getAbsolutePath();

    if (wb instanceof XSSFWorkbook) {
        filename = FileUtils.stripFileExtension(filename) + ".xlsx";
    } else {
        filename = FileUtils.stripFileExtension(filename) + ".xls";
    }

    try (FileOutputStream out = new FileOutputStream(filename)) {
        wb.write(out);
    } catch (Exception e) {
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

From source file:joinery.impl.Serialization.java

License:Open Source License

public static <V> void writeXls(final DataFrame<V> df, final OutputStream output) throws IOException {
    final Workbook wb = new HSSFWorkbook();
    final Sheet sheet = wb.createSheet();

    // add header
    Row row = sheet.createRow(0);//from  ww  w.  j av  a  2s .  co m
    final Iterator<Object> it = df.columns().iterator();
    for (int c = 0; c < df.size(); c++) {
        final Cell cell = row.createCell(c);
        writeCell(cell, it.hasNext() ? it.next() : c);
    }

    // add data values
    for (int r = 0; r < df.length(); r++) {
        row = sheet.createRow(r + 1);
        for (int c = 0; c < df.size(); c++) {
            final Cell cell = row.createCell(c);
            writeCell(cell, df.get(r, c));
        }
    }

    //  write to stream
    wb.write(output);
    output.close();
}

From source file:jp.ac.tohoku.ecei.sb.metabolome.lims.gui.progenesis.TemplateCreatorController.java

License:Open Source License

@FXML
void onImport(ActionEvent event) {
    if (textProgenesisCSV.getText().length() == 0) {
        new Alert(Alert.AlertType.ERROR, "Progenesis CSV is not selected").show();
        return;/* www.  j  a  va 2 s  .c  o m*/
    }

    if (textSampleInfoXlsx.getText().length() == 0) {
        new Alert(Alert.AlertType.ERROR, "Sample info xlsx is not selected").show();
        return;
    }

    File inputCSVPath = new File(textProgenesisCSV.getText());
    File outputXlsxPath = new File(textSampleInfoXlsx.getText());

    try (InputStream is = new FileInputStream(inputCSVPath)) {
        Workbook workbook = ProgenesisLoader.createTemplateXlsxFile(is);
        try (OutputStream os = new FileOutputStream(outputXlsxPath)) {
            workbook.write(os);
        }

        new Alert(Alert.AlertType.INFORMATION, "Template xlsx file was created successfully").show();
    } catch (IOException | InvalidSampleInfoFormatException e1) {
        e1.printStackTrace();
        AlertHelper.showExceptionAlert("Cannot create template excel", null, e1);
    }
}

From source file:jp.ac.tohoku.ecei.sb.metabolome.lims.progenesis.ProgenesisLoaderTest.java

License:Open Source License

@Test
public void createTemplateXlsxFile() throws Exception {
    Workbook workbook = ProgenesisLoader
            .createTemplateXlsxFile(getClass().getResourceAsStream("random-progenesis.csv"));

    File buildDir = new File(System.getProperty("user.dir"), "build");
    File testOutput = new File(buildDir, "test-data");
    testOutput.mkdirs();/*  w  w  w . java  2 s  .  co  m*/
    File templateXlsx = new File(testOutput, "generated-template.xlsx");

    try (OutputStream os = new FileOutputStream(templateXlsx)) {
        workbook.write(os);
    }
}

From source file:jp.ac.tohoku.ecei.sb.metabolomeqc.cli.commands.progenesis.CreateTemplate.java

License:Open Source License

@Override
public CommandResult execute() throws Exception {
    try (OutputStream os = new FileOutputStream(output)) {
        try (InputStream is = new FileInputStream(file)) {
            Workbook workbook = ProgenesisLoader.createTemplateXlsxFile(is);
            workbook.write(os);
        } catch (InvalidSampleInfoFormatException e) {
            System.err.printf("Invalid Progenesis File");
            e.printStackTrace();/*from ww w.java  2  s.  com*/
        }
    }
    return new CommandResult(null, CommandResult.ResultState.SUCCESS);
}

From source file:kaflib.types.Matrix.java

License:Open Source License

/**
 * Writes the specified matrices to file as worksheets.  Each object is
 * represented by its toString() value.//from  www.j  a v  a 2  s .  c om
 * @param file
 * @param worksheets
 * @throws Exception
 */
@SafeVarargs
public static <T> void toXLSX(final File file, final Pair<String, Matrix<T>>... worksheets) throws Exception {

    Workbook workbook = new XSSFWorkbook();

    for (Pair<String, Matrix<T>> worksheet : worksheets) {
        Sheet sheet = workbook.createSheet(worksheet.getFirst());
        Matrix<T> matrix = worksheet.getSecond();

        int i = 0;
        if (matrix.getColumnLabels() != null && matrix.getColumnLabels().size() > 0) {
            Row row = sheet.createRow(i);
            int j = 0;
            for (String string : matrix.getColumnLabels()) {
                row.createCell(j).setCellValue(string);
                j++;
            }
            i++;
        }

        for (int mrow = 0; mrow < matrix.getRowCount(); mrow++) {
            Row row = sheet.createRow(i);
            int j = 0;
            for (T t : matrix.getRow(mrow)) {
                if (t != null) {
                    row.createCell(j).setCellValue(t.toString());
                } else {
                    row.createCell(j).setCellValue("");
                }
                j++;
            }
            i++;
        }

    }

    FileOutputStream stream = new FileOutputStream(file);
    workbook.write(stream);
    stream.close();

    workbook.close();

}