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:log_compressor.write_disk_space.java

public static void write_disk_space(HashMap<String, ArrayList<String>> map, List<String> server_list)
        throws FileNotFoundException, IOException {
    File myFile = new File("D:\\log\\log_output.xlsx");
    FileInputStream fis = new FileInputStream(myFile);
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    HashMap<String, String> res = new HashMap<String, String>();
    ArrayList<String> key_set = new ArrayList<String>();
    ArrayList<String> free_space = new ArrayList<String>();
    BusinessOrInfra boi = new BusinessOrInfra();
    int rownum = mySheet.getLastRowNum() + 1;
    Row row1 = mySheet.createRow(rownum++);

    Cell cell2 = row1.createCell(0);/* w  w w .j  a va 2 s  . c  om*/
    Date date = new Date();
    cell2.setCellValue(date.toString());
    int i = 0;
    boolean isInfra = boi.isInfrastructure();
    for (String key : server_list) {
        free_space.clear();
        ArrayList<String> disk_free = map.get(key);

        for (String df : disk_free) {

            if (!df.equals("need manual check")) {
                int free_position1 = df.lastIndexOf("GB");
                int free_position2 = df.lastIndexOf("free");
                String disk = df.substring(0, 1);
                String key_disk = key + ":" + disk;

                String free_space_percent = df.substring(free_position1 + 3, free_position2 - 1);
                free_space.add(free_space_percent);
                res.put(key_disk, free_space_percent);
                key_set.add(key_disk);
            } else {

                free_space.add("need manual check");
            }
        }

        Row row = null;
        if (isInfra) {
            if (i != 22) {
                row = mySheet.createRow(rownum++);
                i++;
            } else {
                rownum = rownum + 2;
                row = mySheet.createRow(rownum++);
                i++;
            }
        } else {
            row = mySheet.createRow(rownum++);
            i++;
        }

        int cellnum = 0;
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue(key);
        for (String val : free_space) {
            cellnum = cellnum + 1;
            Cell cell1 = row.createCell(cellnum);
            cell1.setCellValue(val);
        }
    }

    FileOutputStream os = new FileOutputStream(myFile);
    myWorkBook.write(os);
}

From source file:log_compressor.write_xlsx.java

public static void write_xlsx(HashMap<String, List<String>> map, ArrayList<String> server_list)
        throws FileNotFoundException, IOException {
    File myFile = new File("D:\\log\\log_output.xlsx");

    FileInputStream fis = new FileInputStream(myFile);
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    BusinessOrInfra boi = new BusinessOrInfra();
    boolean isInfra = boi.isInfrastructure();
    int rownum = mySheet.getLastRowNum() + 1;
    Row row1 = mySheet.createRow(rownum++);
    Cell cell2 = row1.createCell(0);/*from w  w w.  j ava  2 s . c o  m*/
    Date date = new Date();
    cell2.setCellValue(date.toString());
    int i = 0;
    for (String key : server_list) {
        Row row = null;
        if (isInfra) {
            if (i != 22) {
                row = mySheet.createRow(rownum++);
                i++;
            } else {
                rownum = rownum + 2;
                row = mySheet.createRow(rownum++);
                i++;
            }

        } else {
            row = mySheet.createRow(rownum++);
            i++;
        }

        int cellnum = 0;

        List<String> event = map.get(key);

        Cell cell = row.createCell(cellnum);
        cell.setCellValue(key);
        Cell cell1 = row.createCell(cellnum + 2);
        cell1.setCellValue(event.toString().substring(1, event.toString().length() - 1));
    }
    FileOutputStream os = new FileOutputStream(myFile);
    myWorkBook.write(os);
}

From source file:lospolloshermanos.SalesTablePan.java

public void PrintSales() {

    if (items != null && categories != null) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        //Create a blank sheet
        XSSFSheet itemssheet = workbook.createSheet("Item-wise");
        XSSFSheet categorysheet = workbook.createSheet("Cateogry-wise");
        XSSFRow row;/*from   ww  w . j av  a 2 s.  com*/
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        Cell cell;
        row = itemssheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue("Meal Name");
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("Quantity");
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue("Sub Total");
        font.setBold(false);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 2; i <= no_of_items + 1; i++) {
            try {
                row = itemssheet.createRow(i);
                String QtyTot = items.getString("QtyTot");
                String SubTot = items.getString("SubTot");
                cell = row.createCell(0);
                cell.setCellValue(items.getString("MName"));
                cell = row.createCell(1);
                if (QtyTot != null)
                    cell.setCellValue(QtyTot);
                else
                    cell.setCellValue("0");
                cell = row.createCell(2);
                if (SubTot != null)
                    cell.setCellValue(SubTot);
                else
                    cell.setCellValue("0.00");
                items.next();
            } catch (SQLException ex) {
                Logger.getLogger(SalesTablePan.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        row = itemssheet.createRow(no_of_items + 3);
        font.setBold(true);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("Grand Total");
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(GrandTot + "");

        row = categorysheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue("Category Name");
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("No of items sold");
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue("Sub Total");
        font.setBold(false);
        for (int i = 2; i <= no_of_cats + 1; i++) {
            try {
                row = categorysheet.createRow(i);

                cell = row.createCell(0);
                cell.setCellValue(categories.getString("CName"));
                cell = row.createCell(1);
                String QtyTot = categories.getString("QtyTot");
                String SubTot = categories.getString("SubTot");
                if (QtyTot != null)
                    cell.setCellValue(QtyTot);
                else
                    cell.setCellValue("0");
                cell = row.createCell(2);
                if (SubTot != null)
                    cell.setCellValue(SubTot);
                else
                    cell.setCellValue("0.00");
                categories.next();
            } catch (SQLException ex) {
                Logger.getLogger(SalesTablePan.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        row = categorysheet.createRow(no_of_cats + 3);
        font.setBold(true);
        cell = row.createCell(0);
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue("Grand Total");
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(GrandTot + "");
        font.setBold(false);

        itemssheet.autoSizeColumn(0);
        itemssheet.autoSizeColumn(1);
        itemssheet.autoSizeColumn(2);
        categorysheet.autoSizeColumn(0);
        categorysheet.autoSizeColumn(1);
        categorysheet.autoSizeColumn(2);
        try {
            items.first();
            categories.first();
            FileOutputStream out = new FileOutputStream(
                    new File("C:/Program Files/RMS/Sales_from_" + Date1 + "_to_" + Date2 + ".xlsx"));
            workbook.write(out);
            out.close();
        } catch (Exception e) {
        }
    }
}

From source file:Main.Database.java

/**
 * @param dbTable - the database table which contains job results from a
 * particular job board.//from w  ww  . j  a  v a 2 s.c o m
 * @param excelSheetIndex - the sheet index on the Excel File which will
 * house the results from the database.
 *
 * This method appends results from the specific database table onto the
 * index of the excel sheet.
 */
/*
 public void appendDBTableToExcel(String dbTable, int excelSheetIndex) {
 try {
 connect = connectDatabase();
 String sqlQuery = "Select *from " + dbTable;
 // create the java statement
 selectStmt = connect.prepareStatement(sqlQuery);
 resultSet = selectStmt.executeQuery();
 //call upon the excel file.
 excelFile = new File("C:\\Users\\jason\\Desktop\\Job Scraper\\JobScraper\\src\\main\\java\\Main\\links.xlsx");
 excelInputStream = new FileInputStream(excelFile);
 Workbook workbook = create(excelInputStream);
        
 CreationHelper createHelper = workbook.getCreationHelper();
 Sheet sheet = workbook.getSheetAt(excelSheetIndex);
 excelOutputStream = new FileOutputStream(excelFile);
 //set link style to blue
 CellStyle hlinkstyle = workbook.createCellStyle();
 Font hlinkfont = workbook.createFont();
 hlinkfont.setUnderline(XSSFFont.U_SINGLE);
 hlinkfont.setColor(HSSFColor.BLUE.index);
 hlinkstyle.setFont(hlinkfont);
        
 // iterate through the java database,grabbing the details of the job.
 System.out.println("I can see contents of database");
 while (resultSet.next()) {
 this.title = resultSet.getString("title");
 this.link = resultSet.getString("link");
 this.date = resultSet.getString("date");
 System.out.println(this.title + " / " + this.link + " / " + this.date );
 //append database Information onto Excel
 Row row = sheet.createRow(rowNumber);
 Cell titleCell = row.createCell(0);
 titleCell.setCellValue(title);
 Cell linkCell = row.createCell(1);
 linkCell.setCellValue(link);
 Cell dateCell = row.createCell(2);
 dateCell.setCellValue(date);
        
 //Make the link a clickable blue url.
 XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
 hyperLink.setAddress(link);
 linkCell.setHyperlink(hyperLink);
 linkCell.setCellStyle(hlinkstyle);
 rowNumber++;
 }
 //autosizes the columns for clarity
 for (int i = 0; i < 3; i+=2) {
 sheet.autoSizeColumn(i);
 }
 selectStmt.close();
 excelOutputStream = new FileOutputStream(excelFile);
 workbook.write(excelOutputStream);
 excelOutputStream.close();
            
            
 System.out.println("Printed out " + dbTable);
 } catch (SQLException ex) {
 getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
 } catch (FileNotFoundException ex) {
 getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
 } catch (IOException ex) {
 getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
 } catch (InvalidFormatException ex) {
 Logger.getLogger(IndeedScraper.class.getName()).log(Level.SEVERE, null, ex);
 }
 }
 */
public void appendDBTableToExcel(String dbTable) {
    rowNumber = 0;
    try {
        connect = connectDatabase();
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement.executeQuery("Select * from " + dbTable);

        File file = new File("exceldatabase.xlsx");
        if (file.exists()) {
            closeDBSession();
            callExistingExcel(dbTable);
        } else {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet spreadsheet = workbook.createSheet(dbTable);
            while (resultSet.next()) {
                XSSFRow row = spreadsheet.createRow(rowNumber);
                XSSFCell titleCell = row.createCell(0);
                titleCell.setCellValue(resultSet.getString("title"));
                XSSFCell linkCell = row.createCell(1);
                linkCell.setCellValue(resultSet.getString("link"));
                XSSFCell dateCell = row.createCell(2);
                dateCell.setCellValue(resultSet.getString("date"));
                System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link")
                        + resultSet.getString("date"));
                rowNumber++;
            }
            //autosizes the columns for clarity
            for (int i = 0; i < 3; i += 2) {
                spreadsheet.autoSizeColumn(i);
            }
            FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("exceldatabase.xlsx written successfully");
        }
    } catch (SQLException ex) {
        Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:Main.Database.java

private void callExistingExcel(String dbTable) throws FileNotFoundException, IOException, SQLException {
    rowNumber = 0;// w w  w. jav a 2s  . co  m
    connect = connectDatabase();
    Statement statement = connect.createStatement();
    ResultSet resultSet = statement.executeQuery("Select * from " + dbTable);

    File file = new File("exceldatabase.xlsx");
    FileInputStream fIP = new FileInputStream(file);
    //Get the workbook instance for XLSX file 
    XSSFWorkbook workbook = new XSSFWorkbook(fIP);
    try {
        if (file.isFile() && file.exists()) {
            System.out.println("openworkbook.xlsx file open successfully.");
            XSSFSheet spreadsheet = workbook.createSheet(dbTable);
            while (resultSet.next()) {
                XSSFRow row = spreadsheet.createRow(rowNumber);
                XSSFCell titleCell = row.createCell(0);
                titleCell.setCellValue(resultSet.getString("title"));
                XSSFCell linkCell = row.createCell(1);
                linkCell.setCellValue(resultSet.getString("link"));
                XSSFCell dateCell = row.createCell(2);
                dateCell.setCellValue(resultSet.getString("date"));
                System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link")
                        + resultSet.getString("date"));
                rowNumber++;
            }
            //autosizes the columns for clarity
            for (int i = 0; i < 3; i += 2) {
                spreadsheet.autoSizeColumn(i);
            }
            FileOutputStream out = new FileOutputStream("exceldatabase.xlsx");
            workbook.write(out);
            out.close();
            System.out.println("Successfully written");
        } else {
            System.out.println("Error to open openworkbook.xlsx file.");
        }
    } catch (IllegalArgumentException ex) {
    }
}

From source file:minor.Bill.java

private void writetoexcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();/* w  ww  .  j a v  a 2  s. co m*/

    //load data to treemap

    TreeMap<String, Object[]> data = new TreeMap<>();

    //add column headers

    data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2),
            model.getColumnName(3), model.getColumnName(4) });

    //add rows and cells
    for (int i = 0; i < model.getRowCount(); i++) {
        data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2),
                getcellvalue(i, 3), getcellvalue(i, 4) });

    }

    //write to excel
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);

        //get data as per key

        Object[] values = data.get(key);
        int cellID = 0;
        for (Object O : values) {
            XSSFCell cell = row.createCell(cellID++);
            cell.setCellValue(O.toString());
        }
    }

    //write to filesystem
    try

    {
        FileOutputStream fos = new FileOutputStream(new File("E:/excel/bill.xlsx"));
        wb.write(fos);
        fos.close();
    } catch (Exception ex) {
        ex.printStackTrace();
        JOptionPane.showMessageDialog(null, ex);
    }

}

From source file:minor.dbook.java

private void writetoexcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();//from   w ww  .  j  a v a2  s  .c o  m

    //load data to treemap

    TreeMap<String, Object[]> data = new TreeMap<>();

    //add column headers

    data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2),
            model.getColumnName(3), model.getColumnName(4) });

    //add rows and cells
    for (int i = 0; i < model.getRowCount(); i++) {
        data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2),
                getcellvalue(i, 3), getcellvalue(i, 4) });

    }

    //write to excel
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);

        //get data as per key

        Object[] values = data.get(key);
        int cellID = 0;
        for (Object O : values) {
            XSSFCell cell = row.createCell(cellID++);
            cell.setCellValue(O.toString());
        }
    }

    //write to filesystem
    try

    {
        FileOutputStream fos = new FileOutputStream(new File("E:/excel/daybook.xlsx"));
        wb.write(fos);
        fos.close();
    } catch (Exception ex) {
        ex.printStackTrace();
        JOptionPane.showMessageDialog(null, ex);
    }

}

From source file:minor.UpdateMenu.java

private void writetoexcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();/*from   w ww .  j  ava 2  s.  com*/

    //load data to treemap

    TreeMap<String, Object[]> data = new TreeMap<>();

    //add column headers

    data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2) });

    //add rows and cells
    for (int i = 0; i < model.getRowCount(); i++) {
        data.put(Integer.toString(i),
                new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2) });

    }

    //write to excel
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);

        //get data as per key

        Object[] values = data.get(key);
        int cellID = 0;
        for (Object O : values) {
            XSSFCell cell = row.createCell(cellID++);
            cell.setCellValue(O.toString());
        }
    }

    //write to filesystem
    try

    {
        FileOutputStream fos = new FileOutputStream(new File("E:/excel/menurecords.xlsx"));
        wb.write(fos);
        fos.close();
    } catch (Exception ex) {
        ex.printStackTrace();
        JOptionPane.showMessageDialog(null, ex);
    }

}

From source file:minor.UpdateRecords.java

private void writetoexcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();//from  ww  w .  jav a 2s .  c o  m

    //load data to treemap

    TreeMap<String, Object[]> data = new TreeMap<>();

    //add column headers

    data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2),
            model.getColumnName(3), model.getColumnName(4), model.getColumnName(5), model.getColumnName(6),
            model.getColumnName(7), model.getColumnName(8), model.getColumnName(9) });

    //add rows and cells
    for (int i = 0; i < model.getRowCount(); i++) {
        data.put(Integer.toString(i),
                new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2), getcellvalue(i, 3),
                        getcellvalue(i, 4), getcellvalue(i, 5), getcellvalue(i, 6), getcellvalue(i, 7),
                        getcellvalue(i, 8), getcellvalue(i, 9) });

    }

    //write to excel
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);

        //get data as per key

        Object[] values = data.get(key);
        int cellID = 0;
        for (Object O : values) {
            XSSFCell cell = row.createCell(cellID++);
            cell.setCellValue(O.toString());
        }
    }

    //write to filesystem
    try

    {
        FileOutputStream fos = new FileOutputStream(new File("E:/excel/employeerecords.xlsx"));
        wb.write(fos);
        fos.close();
    } catch (Exception ex) {
        ex.printStackTrace();
        JOptionPane.showMessageDialog(null, ex);
    }

}

From source file:mpqq.MPQQ.java

/**
 * @param args the command line arguments
 *//*from ww  w. j a va  2  s.c  om*/
public static void main(String[] args) {

    try {
        //Read Reference File
        FileInputStream referenceFile = new FileInputStream(
                new File("C:\\Personal\\09168336\\Documents\\iRef.xlsx"));
        XSSFWorkbook reference = new XSSFWorkbook(referenceFile);
        reference.close();

        FileInputStream mpqqFile = new FileInputStream(
                new File("C:\\Personal\\09168336\\Documents\\mpqq.xlsm"));
        XSSFWorkbook mpqq = new XSSFWorkbook(mpqqFile);
        mpqqFile.close();
        int referenceStartRow = 156;

        //Create a MPQQ for each supplier in the reference document
        String currentSupplier = reference.getSheetAt(USE_FOR_TAB1).getRow(referenceStartRow)
                .getCell(T2PEPSICO_SUPPLIER_SITE_NAME).getStringCellValue();

        System.out.println(currentSupplier);

        mpqq = procTab1(reference, mpqq, referenceStartRow);
        FileOutputStream outFile = new FileOutputStream(
                new File("C:\\Personal\\09168336\\Documents\\mpqq.xlsm"));
        mpqq.write(outFile);
        outFile.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }

    System.out.println("Testing");
}