Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook write

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. write

Introduction

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

Prototype

@SuppressWarnings("resource")
public final void write(OutputStream stream) throws IOException

Source Link

Document

Write out this document to an Outputstream.

Usage

From source file:com.mycompany.owl.fxml.FXMLFilterController.java

@FXML
public void saveFileTransformed() throws FileNotFoundException, IOException {
    FileInputStream fileInputStream = new FileInputStream(file);
    XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbookToModify.getSheetAt(0);
    XSSFRow row;/*  w w  w . jav  a 2s  . c o m*/
    String atcMask = getATCMask();
    ArrayList<String> firstRowCells = new ArrayList<>();
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            System.out.print(row.getCell(j).getRawValue() + "\t");
            if (i == 0)
                firstRowCells.add(row.getCell(j).getRawValue());
        }
        System.out.println("");
    }

    XSSFWorkbook transformedWB = new XSSFWorkbook();
    transformedWB.createSheet();
    XSSFSheet transformedS = transformedWB.getSheetAt(0);
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        transformedS.createRow(i);
    }
    /*
    elkezdnk vgigmenni az alap sheeten
    ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre
    */
    ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells);
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int sum = 0;
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (i != 0 && j != 0) {
                for (int index : matchingIndexes) {
                    if (index == j) {
                        sum += Integer.valueOf(row.getCell(j).getRawValue());
                    }
                }
            }
        }
        System.out.println(sum);
        if (i > 0) {
            row = sheet.getRow(i);
            row.createCell(row.getLastCellNum()).setCellValue(sum);
        }
    }
    for (int index : matchingIndexes) {
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            row.getCell(index).setCellValue(3.14159);
        }
        /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
        if(i == index){
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                row = sheet.getRow(j);
                transformedS.getRow(j).createCell(transformedColumnCount).setCellValue(
                        row.getCell(i).getRawValue()
                );
            }
            transformedColumnCount++;
        }
        }*/
    }
    int columnsInTransformed = 0;
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            String cellValue = row.getCell(j).getRawValue();
            if (!cellValue.equals("3.14159")) {
                transformedS.getRow(i).createCell(columnsInTransformed);
                transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue);
                columnsInTransformed++;
            }
        }
        columnsInTransformed = 0;
    }

    row = transformedS.getRow(0);
    row.createCell(row.getLastCellNum()).setCellValue(atcMask);

    File file = fileChooser.showSaveDialog(new Stage());
    if (file != null) {
        try {
            FileOutputStream fop = new FileOutputStream(file);
            transformedWB.write(fop);
            fop.close();
        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
    }

}

From source file:com.mycompany.owl.fxml.FXMLFilterController.java

@FXML
public void saveFileFiltered() throws FileNotFoundException, IOException {
    FileInputStream fileInputStream = new FileInputStream(file);
    XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbookToModify.getSheetAt(0);
    XSSFRow row;//from  w ww .j  a v a 2s. c o  m
    String atcMask = getATCMask();
    ArrayList<String> firstRowCells = new ArrayList<>();
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            System.out.print(row.getCell(j).getRawValue() + "\t");
            if (i == 0)
                firstRowCells.add(row.getCell(j).getRawValue());
        }
        System.out.println("");
    }
    row = sheet.getRow(0);
    row.createCell(row.getLastCellNum()).setCellValue("ATC mask:");
    row.createCell(row.getLastCellNum()).setCellValue(atcMask);

    ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells);
    System.out.println("SUMS");
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int sum = 0;
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (i != 0 && j != 0) {
                for (int index : matchingIndexes) {
                    if (index == j) {
                        sum += Integer.valueOf(row.getCell(j).getRawValue());
                    }
                }
            }
        }
        System.out.println(sum);
        if (i > 0) {
            row = sheet.getRow(i);
            row.createCell(row.getLastCellNum() + 1).setCellValue(sum);
        }
    }

    File file = fileChooser.showSaveDialog(new Stage());
    if (file != null) {
        try {
            FileOutputStream fop = new FileOutputStream(file);
            workbookToModify.write(fop);
            fop.close();
        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
    }
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiPeriodTrendsExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();/* w  w w.j av  a2 s.com*/

    this.putTables(wb, sh, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "kpis-period-trends.xlsx");
    file = null;
    return oid;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {
    String visionOid = (String) context.get("visionOid");
    VisionVO vision = null;/*  w  ww  . ja va  2  s.c o  m*/
    BscStructTreeObj treeObj = (BscStructTreeObj) this.getResult(context);
    for (VisionVO visionObj : treeObj.getVisions()) {
        if (visionObj.getOid().equals(visionOid)) {
            vision = visionObj;
        }
    }
    BscReportPropertyUtils.loadData();
    BscReportSupportUtils.loadExpression(); // 2015-04-18 add
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    int row = 24;
    if (context.get("pieCanvasToData") == null || context.get("barCanvasToData") == null) {
        row = 0;
    }
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();

    row += this.createHead(wb, sh, row, vision);
    row = this.createMainBody(wb, sh, row, vision);

    row = row + 1; // 
    row = this.createDateRange(wb, sh, row, vision, context);
    if (context.get("pieCanvasToData") != null && context.get("barCanvasToData") != null) {
        this.putCharts(wb, sh, context);
    }
    this.putSignature(wb, sh, row + 1, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "kpi-report.xlsx");
    file = null;
    return oid;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpisDashboardExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();//w w  w. j  a  v  a 2 s.  c  om

    int row = this.putTables(wb, sh, context);
    row = this.putCharts(wb, sh, context, row);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "kpis-dashboard.xlsx");
    file = null;
    return oid;
}

From source file:com.netsteadfast.greenstep.bsc.command.ObjectivesDashboardExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();/*from  ww  w  . j  a v  a2  s  .  c o  m*/

    this.putCharts(wb, sh, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "objectives-dashboard.xlsx");
    file = null;
    return oid;
}

From source file:com.netsteadfast.greenstep.bsc.command.OrganizationReportExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {
    String visionOid = (String) context.get("visionOid");
    VisionVO vision = null;/*from   w  w w  .j av  a 2  s .co  m*/
    BscStructTreeObj treeObj = (BscStructTreeObj) this.getResult(context);
    for (VisionVO visionObj : treeObj.getVisions()) {
        if (visionObj.getOid().equals(visionOid)) {
            vision = visionObj;
        }
    }
    BscReportPropertyUtils.loadData();
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    int row = 0;
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();

    row += this.createHead(wb, sh, row, vision, context);
    row = this.createMainBody(wb, sh, row, vision);

    this.putSignature(wb, sh, row + 1, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "department-report.xlsx");
    file = null;
    return oid;
}

From source file:com.netsteadfast.greenstep.bsc.command.PdcaReportExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {

    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    int row = 0;/* w w w. j a v a2s.  co  m*/
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh1 = wb.createSheet("PDCA Report");

    row += this.createPdca(wb, sh1, row, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "pdca-report.xlsx");
    file = null;
    return oid;
}

From source file:com.netsteadfast.greenstep.bsc.command.PersonalReportExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {
    String visionOid = (String) context.get("visionOid");
    VisionVO vision = null;/*  ww  w.  ja va  2s.  co m*/
    BscStructTreeObj treeObj = (BscStructTreeObj) this.getResult(context);
    for (VisionVO visionObj : treeObj.getVisions()) {
        if (visionObj.getOid().equals(visionOid)) {
            vision = visionObj;
        }
    }
    BscReportPropertyUtils.loadData();
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    int row = 0;
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();

    row += this.createHead(wb, sh, row, vision, context);
    row = this.createMainBody(wb, sh, row, vision, context);
    this.createFoot(wb, sh, row, vision, context);

    this.putSignature(wb, sh, row + 2, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "personal-report.xlsx");
    file = null;
    return oid;
}

From source file:com.netsteadfast.greenstep.bsc.command.PerspectivesDashboardExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();/*ww w  . j a  v a 2s  . co m*/

    this.putCharts(wb, sh, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "perspectives-dashboard.xlsx");
    file = null;
    return oid;
}