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

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

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

Introduction

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

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

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//from   w  w  w  .  ja  v  a  2 s . c  o  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;//ww  w .j  a  va  2 s  .c om
    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);//from   ww  w.  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

private void saveSheet(Workbook wb, ReportModel model, ReportBook reportBook, String reportTitle)
        throws SaveReportException {

    CreationHelper createHelper = wb.getCreationHelper();

    Sheet sheet = wb.createSheet(reportTitle);
    sheet.setDisplayGridlines(reportBook.isShowGrid());
    sheet.setPrintGridlines(false);/*from w  w w .  j a v  a  2  s.  c  o  m*/
    sheet.setFitToPage(model.isStretchPage());
    sheet.setDisplayRowColHeadings(model.isShowHeader() || model.isShowRowHeader());
    ReportPage rp = model.getReportPage();
    sheet.setMargin(Sheet.TopMargin, rp.getTopMargin(Units.INCH));
    sheet.setMargin(Sheet.BottomMargin, rp.getBottomMargin(Units.INCH));
    sheet.setMargin(Sheet.LeftMargin, rp.getLeftMargin(Units.INCH));
    sheet.setMargin(Sheet.RightMargin, rp.getRightMargin(Units.INCH));
    sheet.getPrintSetup().setLandscape(rp.getOrientation() == ReportPage.LANDSCAPE);
    short paperSize = convertPaperSize(rp.getPaperSize());
    if (paperSize > 0) {
        sheet.getPrintSetup().setPaperSize(paperSize);
    }

    TableColumnModel cm = model.getColumnModel();

    for (int c = 0; c < model.getColumnCount(); c++) {
        if (model.isColumnBreak(c)) {
            sheet.setColumnBreak(c);
        }

        //char width in points
        float char_width = 5.5f;
        sheet.setColumnWidth(c,
                (int) ((((ReportColumn) cm.getColumn(c)).getNativeWidth() - 2) / char_width * 256));
    }

    fillStyles(wb, reportBook);

    createRows(model, sheet);

    drawing = sheet.createDrawingPatriarch();
    for (int row = 0; row < model.getRowCount(); row++) {
        saveRow(wb, sheet, reportBook, model, row, createHelper);
    }
    drawing = null;
}

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 w ww  . ja va  2 s . co m*/
        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:kaflib.types.Matrix.java

License:Open Source License

/**
 * Writes the specified matrices to file as worksheets.  Each object is
 * represented by its toString() value.//w  w w . j av  a  2  s . c o m
 * @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();

}

From source file:ket_noi_DB.ket_noi_kh.java

public void luuFile(Workbook workbook, String path, String sql, String tenCSDL) {
    try {/*from   w  w w. j av a 2  s  . c  o m*/
        data = statement.executeQuery(sql);
    } catch (SQLException ex) {
        Logger.getLogger(Khach_Hang.class.getName()).log(Level.SEVERE, null, ex);
    }
    Sheet sheet1 = workbook.createSheet(tenCSDL);
    try {
        metadata = data.getMetaData();
        int numrow = 0;
        while (data.next()) {
            Row row = sheet1.createRow(numrow);
            sheet1.setColumnWidth(numrow, 5000);
            for (int i = 1; i <= metadata.getColumnCount(); i++) {
                row.createCell(i - 1).setCellValue(data.getString(i));
            }
            numrow++;
        }
    } catch (SQLException ex) {
        Logger.getLogger(Khach_Hang.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        FileOutputStream fout = new FileOutputStream(path);
        workbook.write(fout);
        fout.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:ket_noi_excel.ket_noi_excel_cd.java

public void saveFile(JTable tb, String path) {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("kho cd");
    int numrow = 0;
    DefaultTableModel model = new DefaultTableModel();
    model = (DefaultTableModel) tb.getModel();
    Vector vt = model.getDataVector();// ly i tng vector ca model, cha ton b d liu
    for (int i = 0; i < tb.getRowCount(); i++) {
        Vector vtt = (Vector) vt.get(i);//ly dng i
        Row row = sheet.createRow(numrow);
        sheet.setColumnWidth(numrow, 5000);
        for (int j = 0; j < tb.getColumnCount() - 1; j++) {// tr i ct id  cui cng  ? phng khi import li ko c li out of range
            row.createCell(j).setCellValue(vtt.get(j).toString());
        }/*  w  w w  . ja v a  2s . c om*/
        numrow++;
    }
    try {
        FileOutputStream fout = new FileOutputStream(path);
        JOptionPane.showMessageDialog(null, "D liu  c a ra file excel");
        workbook.write(fout);
        fout.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:ket_noi_excel.ket_noi_excel_dvd.java

public void saveFile(JTable tb, String path) {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("kho dvd");
    int numrow = 0;
    DefaultTableModel model = new DefaultTableModel();
    model = (DefaultTableModel) tb.getModel();
    Vector vt = model.getDataVector();// ly i tng vector ca model, cha ton b d liu
    for (int i = 0; i < tb.getRowCount(); i++) {
        Vector vtt = (Vector) vt.get(i);//ly dng i
        Row row = sheet.createRow(numrow);
        sheet.setColumnWidth(numrow, 5000);
        for (int j = 0; j < tb.getColumnCount() - 1; j++) {// tr i ct id  cui cng  ? phng khi import li ko c li out of range
            row.createCell(j).setCellValue(vtt.get(j).toString());
        }/*from  w  ww.j  ava  2  s.c o  m*/
        numrow++;
    }
    try {
        FileOutputStream fout = new FileOutputStream(path);
        JOptionPane.showMessageDialog(null, "D liu  c a ra file excel");
        workbook.write(fout);
        fout.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:lldval.LLDVal.java

/**
 * @param args the command line arguments
 *///  w w  w .  j  a v  a  2 s.c o m
public static void main(String[] args) throws IOException {

    // reading data from a csv file  
    System.out.println("Reading data from csv :");
    ReadCsv readCsv = new ReadCsv();
    readCsv.readCsv("./tunables.csv");
    writeXLSXFile("./PCAT_AnalysisFile.xlsx", "Info");
    Workbook wb = new XSSFWorkbook(new FileInputStream("./PCAT_AnalysisFile.xlsx"));
    XSSFSheet clusterSheet = (XSSFSheet) wb.createSheet("caaConfigurations");
    XSSFSheet hwSheet = (XSSFSheet) wb.createSheet("HWConfigurations");
    XSSFSheet vfcSheet = (XSSFSheet) wb.createSheet("VFC Mappings");
    // reading data from a csv file and convert to java object  
    System.out.println("Reading data from csv and convert to java object:");
    //  csvToTunables csvToJavaObject = new csvToTunables();  
    //  csvToJavaObject.convertCsvToJava();  
    pcatFolders pcatFolder = new pcatFolders("./systems");

    //    System.out.println(Arrays.toString(pcatFolder.pcatFolderList));
    String[] lparName = pcatFolder.pcatFolderList;
    System.out.println(Arrays.toString(lparName));
    //      caa CAA = new caa();
    //      readCaa readCaas = new readCaa("dx980");
    for (int j = 0; j < lparName.length - 1; j++) {
        pcatLparFolders lparFolders = new pcatLparFolders("./systems", lparName[j]);
        System.out.print("LparName : " + String.valueOf(lparName[j]) + "  : ");
        System.out.println(Arrays.toString(lparFolders.fileList));
        for (int t = 0; t < lparFolders.fileList.length; t++) {
            System.out.println(lparFolders.fileList[t]);
            switch (lparFolders.fileList[t]) {
            //        case "caa":
            //            readCaa caa = new readCaa(lparName[j]);
            //            caa.processingLineByLine();
            //            System.out.println("Cluster Configuration Populating");
            ////            System.out.println(caa.toString());
            ////            System.out.println(String.valueOf(CAA.CLUSTER_NAME+" : "+CAA.Cluster_shorthand_id_for_node+" : "+CAA.Mean_Deviation_in_network_rtt_to_node+" : "+CAA.Node_name+" : "+CAA.Number_of_clusters_node_is_a_member_in+" : "+CAA.Smoothed_rtt_to_node+" : "+CAA.State_of_node+" : "+CAA.UUID_for_node));
            //            int sheetLength = clusterSheet.getPhysicalNumberOfRows();
            //            if(sheetLength == 0){
            //                Row row = clusterSheet.createRow((short)sheetLength);
            //                Cell cell = row.createCell(0);
            //                cell.setCellValue("CLUSTER_NAME");
            //                Cell cell1 = row.createCell(1);
            //                cell1.setCellValue("Node_name");
            //                Cell cell2 = row.createCell(2);
            //                cell2.setCellValue("Number_of_clusters_node_is_a_member_in");
            //                Cell cell3 = row.createCell(3);
            //                cell3.setCellValue("State_of_node");
            //                Cell cell4 = row.createCell(4);
            //                cell4.setCellValue("UUID_for_node");
            //                Cell cell5 = row.createCell(5);
            //                cell5.setCellValue("Cluster_shorthand_id_for_node");
            //                Cell cell6 = row.createCell(6);
            //                cell6.setCellValue("Smoothed_rtt_to_node");
            //                Cell cell7 = row.createCell(7);
            //                cell7.setCellValue("Mean_Deviation_in_network_rtt_to_node");}else
            //              if (CAA.CLUSTER_NAME != null && !CAA.CLUSTER_NAME.isEmpty()){
            //                Row row = clusterSheet.createRow((short)sheetLength+1);
            //                Cell cell = row.createCell(0);
            //                cell.setCellValue(CAA.CLUSTER_NAME);
            //                Cell cell1 = row.createCell(1);
            //                cell1.setCellValue(CAA.Node_name);
            //                Cell cell2 = row.createCell(2);
            //                cell2.setCellValue(CAA.Number_of_clusters_node_is_a_member_in);
            //                Cell cell3 = row.createCell(3);
            //                cell3.setCellValue(CAA.State_of_node);
            //                Cell cell4 = row.createCell(4);
            //                cell4.setCellValue(CAA.UUID_for_node);
            //                Cell cell5 = row.createCell(5);
            //                cell5.setCellValue(CAA.Cluster_shorthand_id_for_node);
            //                Cell cell6 = row.createCell(6);
            //                cell6.setCellValue(CAA.Smoothed_rtt_to_node);
            //                Cell cell7 = row.createCell(7);
            //                cell7.setCellValue(CAA.Mean_Deviation_in_network_rtt_to_node);
            //              }
            //                    try {
            //        FileOutputStream out = new FileOutputStream("./PCAT_AnalysisFile.xlsx");
            //        wb.write(out);
            //        out.close();
            //    } catch (Exception e) {
            //        e.printStackTrace();
            //    }
            //            break;
            case "hds":
                System.out.println("HDS Storage Configuration Populating");
                readHDL parser = new readHDL(lparName[j]);
                parser.processLineByLine(lparName[j]);
                //    System.out.println(String.valueOf(lpar.getLparName()+" : "+lpar.getLparID()+" : "+lpar.getHostname()+" : "+lpar.getEntCapacity()));
                log("Done.");
                break;
            //        case "hmc":
            //            System.out.println("HMC Configuration Populating");
            //            break;
            case "hw":
                System.out.println("Hardware  Configuration Populating");
                readHW hw = new readHW(lparName[j]);
                hw.processLineByLine();
                System.out.println(lpar.lparID + " : " + lpar.lparName + " : " + lpar.hostname + " : "
                        + lpar.entCapacity + " : " + lpar.maxCPU + " : " + lpar.desiredCPU + " : " + lpar.minCPU
                        + " : " + lpar.MaxVirtCPU + " : " + lpar.DesVirtCPU + " : " + lpar.minVirtCPU);
                int hwsheetLength = hwSheet.getPhysicalNumberOfRows();
                if (hwsheetLength == 0) {
                    Row row = hwSheet.createRow((short) hwsheetLength);
                    Cell cell = row.createCell(0);
                    cell.setCellValue("LparID");
                    Cell cell1 = row.createCell(1);
                    cell1.setCellValue("lparName");
                    Cell cell2 = row.createCell(2);
                    cell2.setCellValue("hostname");
                    Cell cell3 = row.createCell(3);
                    cell3.setCellValue("maxMem");
                    Cell cell4 = row.createCell(4);
                    cell4.setCellValue("desiredMem");
                    Cell cell5 = row.createCell(5);
                    cell5.setCellValue("maxMem");
                    Cell cell6 = row.createCell(6);
                    cell6.setCellValue("MemMode");
                    Cell cell7 = row.createCell(7);
                    cell7.setCellValue("maxCPU");
                    Cell cell8 = row.createCell(8);
                    cell8.setCellValue("desiredCPU");
                    Cell cell9 = row.createCell(9);
                    cell9.setCellValue("minCPU");
                    Cell cell10 = row.createCell(10);
                    cell10.setCellValue("MaxVirtCPU");
                    Cell cell11 = row.createCell(11);
                    cell11.setCellValue("DesVirtCPU");
                    Cell cell12 = row.createCell(12);
                    cell12.setCellValue("minVirtCPU");
                    Cell cell13 = row.createCell(13);
                    cell13.setCellValue("entCapacity");
                    Cell cell14 = row.createCell(14);
                    cell14.setCellValue("weight");
                    Cell cell15 = row.createCell(15);
                    cell15.setCellValue("SMTType");
                    Cell cell16 = row.createCell(16);
                    cell16.setCellValue("cpuMode");
                } else if (lpar.lparID != null && !lpar.lparID.isEmpty()) {
                    Row row = hwSheet.createRow((short) hwsheetLength + 1);
                    Cell cell = row.createCell(0);
                    cell.setCellValue(lpar.lparID);
                    Cell cell1 = row.createCell(1);
                    cell1.setCellValue(lpar.lparName);
                    Cell cell2 = row.createCell(2);
                    cell2.setCellValue(lpar.hostname);
                    Cell cell3 = row.createCell(3);
                    cell3.setCellValue(lpar.maxMem);
                    Cell cell4 = row.createCell(4);
                    cell4.setCellValue(lpar.desiredMem);
                    Cell cell5 = row.createCell(5);
                    cell5.setCellValue(lpar.maxMem);
                    Cell cell6 = row.createCell(6);
                    cell6.setCellValue(lpar.MemMode);
                    Cell cell7 = row.createCell(7);
                    cell7.setCellValue(lpar.maxCPU);
                    Cell cell8 = row.createCell(8);
                    cell8.setCellValue(lpar.desiredCPU);
                    Cell cell9 = row.createCell(9);
                    cell9.setCellValue(lpar.minCPU);
                    Cell cell10 = row.createCell(10);
                    cell10.setCellValue(lpar.MaxVirtCPU);
                    Cell cell11 = row.createCell(11);
                    cell11.setCellValue(lpar.DesVirtCPU);
                    Cell cell12 = row.createCell(12);
                    cell12.setCellValue(lpar.minVirtCPU);
                    Cell cell13 = row.createCell(13);
                    cell13.setCellValue(lpar.entCapacity);
                    Cell cell14 = row.createCell(14);
                    cell14.setCellValue(lpar.weight);
                    Cell cell15 = row.createCell(15);
                    cell15.setCellValue(lpar.SMTType);
                    Cell cell16 = row.createCell(16);
                    cell16.setCellValue(lpar.cpuMode);
                }
                try {
                    FileOutputStream out = new FileOutputStream("./PCAT_AnalysisFile.xlsx");
                    wb.write(out);
                    out.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                break;
            //        case "network":
            //            System.out.println("Network Configuration Populating");
            //            break;
            //        case "os":
            //            System.out.println("Operating System Configuration Populating");
            //            break;
            //        case "perf":
            //            System.out.println("Performance Configuration Populating");
            //            break;
            //        case "secure":
            //            System.out.println("Security Configuration Populating");
            //            break;
            //        case "storage":
            //            System.out.println("Storage Configuration Populating");
            //            break;
            //        case "tsm":
            //            System.out.println("TSM Configuration Populating");
            //            break;
            case "vio":
                vfcs.clear();
                System.out.println("VIO Configuration Populating");
                readVFCMap vfcmapd = new readVFCMap(lparName[j]);
                vfcmapd.processPatern();
                vfcMap vfcmap = new vfcMap();
                System.out.println(vfcs.size());
                for (int vfcRows = 0; vfcRows <= vfcs.size() - 1; vfcRows++) {
                    System.out.println(vfcs.get(vfcRows).vfcName + " | " + vfcs.get(vfcRows).physLoc + " | "
                            + vfcs.get(vfcRows).clntId + " | " + vfcs.get(vfcRows).clntName + " | "
                            + vfcs.get(vfcRows).status + " | " + vfcs.get(vfcRows).fc_name + " | "
                            + vfcs.get(vfcRows).fc_loc_code + " | " + vfcs.get(vfcRows).flags + " | "
                            + vfcs.get(vfcRows).VFC_client_name + " | " + vfcs.get(vfcRows).VFC_client_DRC);
                    int vfcsheetLength = vfcSheet.getPhysicalNumberOfRows();
                    if (vfcsheetLength == 0) {
                        Row row = vfcSheet.createRow((short) vfcsheetLength);
                        int count = 0;
                        Cell cell = row.createCell(0);
                        cell.setCellValue("Lpar Name");
                        for (Field field : vfcmap.getClass().getDeclaredFields()) {
                            count++;
                            //                System.out.println(field.getName());
                            Cell nextcell = row.createCell(count);
                            nextcell.setCellValue(field.getName());

                        }
                        //                Row row1 = vfcSheet.createRow(1);
                        //                System.out.println("value row"+row1);
                        //                Cell valuecell = row1.createCell(0);
                        //                valuecell.setCellValue(lparName[j]);
                        //                Cell vfcNamecell = row1.createCell(vfcRows);
                        //                vfcNamecell.setCellValue(vfcs.get(vfcRows).vfcName);
                        //                Cell physLoccell = row1.createCell(vfcRows);
                        //                physLoccell.setCellValue(vfcs.get(vfcRows).physLoc);
                        //                Cell clntIdcell = row1.createCell(vfcRows);
                        //                clntIdcell.setCellValue(vfcs.get(vfcRows).clntId);
                        //                Cell clntNamecell = row1.createCell(vfcRows);
                        //                clntNamecell.setCellValue(vfcs.get(vfcRows).clntName);
                        //                Cell statuscell = row1.createCell(vfcRows);
                        //                statuscell.setCellValue(vfcs.get(vfcRows).status);
                        //                Cell fc_namecell = row1.createCell(vfcRows);
                        //                fc_namecell.setCellValue(vfcs.get(vfcRows).fc_name);
                        //                Cell fc_loc_codecell = row1.createCell(vfcRows);
                        //                fc_loc_codecell.setCellValue(vfcs.get(vfcRows).fc_loc_code);
                        //                Cell flagscell = row1.createCell(vfcRows);
                        //                flagscell.setCellValue(vfcs.get(vfcRows).flags);
                        //                Cell VFC_client_namecell = row1.createCell(vfcRows);
                        //                VFC_client_namecell.setCellValue(vfcs.get(vfcRows).VFC_client_name);
                        //                Cell VFC_client_DRCcell = row1.createCell(vfcRows);
                        //                VFC_client_DRCcell.setCellValue(vfcs.get(vfcRows).VFC_client_DRC);
                        //vfcs.get(vfcRows).vfcName+" | "+vfcs.get(vfcRows).physLoc+" | "+vfcs.get(vfcRows).clntId+" | "+vfcs.get(vfcRows).clntName+" | "+vfcs.get(vfcRows).status+" | "+vfcs.get(vfcRows).fc_name+" | "+vfcs.get(vfcRows).fc_loc_code+" | "+vfcs.get(vfcRows).flags+" | "+vfcs.get(vfcRows).VFC_client_name+" | "+vfcs.get(vfcRows).VFC_client_DRC
                    } else if (vfcs.get(vfcRows).vfcName != null && !vfcs.get(vfcRows).vfcName.isEmpty()) {
                        Row valuerow = vfcSheet.createRow((short) vfcsheetLength);
                        System.out.println("value row" + valuerow);
                        Cell valuecell = valuerow.createCell(0);
                        valuecell.setCellValue(lparName[j]);
                        Cell vfcNamecell = valuerow.createCell(vfcRows);
                        vfcNamecell.setCellValue(vfcs.get(vfcRows).vfcName);
                        Cell physLoccell = valuerow.createCell(vfcRows);
                        physLoccell.setCellValue(vfcs.get(vfcRows).physLoc);
                        Cell clntIdcell = valuerow.createCell(vfcRows);
                        clntIdcell.setCellValue(vfcs.get(vfcRows).clntId);
                        Cell clntNamecell = valuerow.createCell(vfcRows);
                        clntNamecell.setCellValue(vfcs.get(vfcRows).clntName);
                        Cell statuscell = valuerow.createCell(vfcRows);
                        statuscell.setCellValue(vfcs.get(vfcRows).status);
                        Cell fc_namecell = valuerow.createCell(vfcRows);
                        fc_namecell.setCellValue(vfcs.get(vfcRows).fc_name);
                        Cell fc_loc_codecell = valuerow.createCell(vfcRows);
                        fc_loc_codecell.setCellValue(vfcs.get(vfcRows).fc_loc_code);
                        Cell flagscell = valuerow.createCell(vfcRows);
                        flagscell.setCellValue(vfcs.get(vfcRows).flags);
                        Cell VFC_client_namecell = valuerow.createCell(vfcRows);
                        VFC_client_namecell.setCellValue(vfcs.get(vfcRows).VFC_client_name);
                        Cell VFC_client_DRCcell = valuerow.createCell(vfcRows);
                        VFC_client_DRCcell.setCellValue(vfcs.get(vfcRows).VFC_client_DRC);

                        //                    valueCount++;
                        //                Cell value1cell = valuerow.createCell(valueCount);
                        //                value1cell.setCellValue(map.clntName);// i am stuck here to iterate around the values of each instance of the object.
                    }
                }
                FileOutputStream out = new FileOutputStream("./PCAT_AnalysisFile.xlsx");
                wb.write(out);
                out.close();
                break;
            default:
                //            System.out.println("Nothing to do");
                break;

            }
        }
        ;
    }
    ;

    //  getPropValue testRun = new getPropValue("./Systems/dx1010/hw/lparstat-vfcRows.txt");

}