Example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:Logic.Xls.java

public void genXLS(ResultSet rs, String Rpt_name, String path) {
    try {//w  ww . ja  va2  s.  com
        //RS METE DATA
        ResultSetMetaData rsmd = rs.getMetaData();
        int col_count = rsmd.getColumnCount();
        ArrayList<String> col_name = new ArrayList<>();
        for (int i = 1; i <= col_count; i++) {
            col_name.add(rsmd.getColumnLabel(i));
        }

        //XLS Variable
        XSSFSheet spreadsheet;
        XSSFWorkbook workbook;
        XSSFRow row;
        XSSFCell cell;
        XSSFFont xfont = null;
        XSSFCellStyle xstyle = null;

        //2.Create WorkBook and Sheet
        workbook = new XSSFWorkbook();
        spreadsheet = workbook.createSheet(Rpt_name);

        //set header style
        xfont = workbook.createFont();
        xfont.setFontHeight(11);
        xfont.setFontName("Calibri");
        xfont.setBold(true);

        //Set font into style
        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
        borderStyle.setFont(xfont);
        xstyle = workbook.createCellStyle();
        xstyle.setFont(xfont);

        //header
        row = spreadsheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue(Rpt_name);
        cell.setCellStyle(borderStyle);
        spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count - 1));

        //3.Get First Row and Set Headers
        row = spreadsheet.createRow(1);

        for (int i = 0; i < col_count; i++) {
            cell = row.createCell(i);
            cell.setCellValue(col_name.get(i));
            cell.setCellStyle(xstyle);
        }

        //Itrate or Database data and write
        int i = 2;
        while (rs.next()) {
            row = spreadsheet.createRow(i);
            for (int j = 1; j <= col_count; j++) {
                cell = row.createCell(j - 1);
                cell.setCellValue(rs.getString(j));
            }
            i++;
        }

        //Export to Excel
        // FileOutputStream out = new FileOutputStream(new File("D://" + Rpt_name + ".xlsx"));
        FileOutputStream out = new FileOutputStream(new File(path));
        workbook.write(out);

        Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "DONE|!");
        Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "");
    } catch (Exception ex) {
        Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "Exception : " + ex);
    }
}

From source file:Logica.L_POIExcel.java

public void Exportar(JTable Tbl_OT_Eventos_MostrandoExportar) throws Exception {
    JFileChooser selector = new JFileChooser();
    selector.setFileFilter(new FileNameExtensionFilter("Excel 2010 or Superior", "xlsx"));
    //selector.setFileFilter(new FileNameExtensionFilter("Excel with Macrs", "xlsm"));
    int numfila = Tbl_OT_Eventos_MostrandoExportar.getRowCount();
    int numcolumn = Tbl_OT_Eventos_MostrandoExportar.getColumnCount();

    String directorio = "";
    String extension = "";

    boolean seleccion = false;

    int resultado = selector.showOpenDialog(null);

    switch (resultado) {
    case JFileChooser.APPROVE_OPTION:
        directorio = selector.getSelectedFile().getPath();

        int i = directorio.lastIndexOf('.');
        if (i >= 0) {
            extension = directorio.substring(i + 1);
        }//  ww w  .java2s.  co  m
        seleccion = true;

        JOptionPane.showMessageDialog(null, "Seleccionaste" + directorio);
        break;
    case JFileChooser.CANCEL_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "No seleccionaste un archivo");
        break;
    case JFileChooser.ERROR_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "Ocurreo un Error");
        break;
    default:
        break;
    }

    if (extension.equals("xlsx")) {
        FileInputStream entrada = new FileInputStream(new File(directorio));
        XSSFWorkbook xlsx = new XSSFWorkbook(entrada);
        XSSFSheet hoja = xlsx.getSheetAt(0);
        Row fila = null;
        Cell celda = null;
        try {
            //EN CASO CELDAS TENGAN ALGUN VALOR
            //PODEMOS MANEJAR DICHO VALOR O SOBREESCRIBIR SOBRE EL COMO EN EL SIGUIENTE CASO
            //fila = hoja.getRow(0);
            //celda = fila.getCell(0);
            //celda.setCellValue(1);

            //fila = hoja.getRow(7);
            //celda = fila.getCell(6);
            //celda.setCellValue(9);
            for (int i = -1; i < numfila; i++) {
                //esta linea posiciona donde se empezara a escribir en este caso en la fila 4
                //recuerda que tanto fila como columna inician su conteo con 0
                fila = hoja.getRow(i + 14);
                for (int j = 0; j < numcolumn; j++) {
                    celda = fila.getCell(j);
                    if (i == -1) {
                        celda.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getColumnName(j)));
                    } else {
                        celda.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getValueAt(i, j)));
                    }
                }
            }
        } catch (NullPointerException NPE) {
            //EN CSAO LAS CELDAS ESTE VACIAS
            //ESCRIBIMOS UN VALOR SOBRE ELLAS
            for (int i = -1; i < numfila; i++) {
                Row filaV = hoja.createRow(i + 14);
                for (int j = 0; j < numcolumn; j++) {
                    Cell celdaV = filaV.createCell(j);
                    if (i == -1) {
                        celdaV.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getColumnName(j)));
                    } else {
                        celdaV.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getValueAt(i, j)));
                    }
                }
            }
        }
        //EVALUA LAS FORMULAS DEL WORKBK
        XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx);

        entrada.close();

        //ABRIENDO ARCHIVO PARA ESCRITURA
        FileOutputStream salida = new FileOutputStream(new File(directorio));
        //ESCRIBIENDO DATOS, EN ESTE CASO DE LA TABLA
        xlsx.write(salida);

        salida.close();
    }
}

From source file:Logica.L_POIExcel.java

public void Exportar_To_Mcr() throws Exception {
    JFileChooser selector = new JFileChooser();
    selector.setFileFilter(new FileNameExtensionFilter("Excel 2010 or Superior", "xlsx"));
    //selector.setFileFilter(new FileNameExtensionFilter("Excel with Macrs", "xlsm"));        
    String sSql = "";
    String UPS_Monitoreados = "";

    sSql = "SELECT Count(EstadoMonitoreo) FROM upsinformacion_prb WHERE EstadoMonitoreo Like '%MON%'";

    try {//  ww  w .  j a va2  s. c o m
        Statement st = cn.createStatement();
        ResultSet rs = st.executeQuery(sSql);
        rs.next();
        UPS_Monitoreados = rs.getString("Count(EstadoMonitoreo)");
    } catch (Exception e) {
    }

    String directorio = "";
    String extension = "";

    boolean seleccion = false;

    int resultado = selector.showOpenDialog(null);

    switch (resultado) {
    case JFileChooser.APPROVE_OPTION:
        directorio = selector.getSelectedFile().getPath();

        int i = directorio.lastIndexOf('.');
        if (i >= 0) {
            extension = directorio.substring(i + 1);
        }
        seleccion = true;

        JOptionPane.showMessageDialog(null, "Seleccionaste" + directorio);
        break;
    case JFileChooser.CANCEL_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "No seleccionaste un archivo");
        break;
    case JFileChooser.ERROR_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "Ocurreo un Error");
        break;
    default:
        break;
    }
    if (extension.equals("xlsx")) {
        //Leer el archivo de Excel XLSX
        FileInputStream entrada = new FileInputStream(new File(directorio));
        //Acceso al libro de trabajo
        XSSFWorkbook xlsx = new XSSFWorkbook(entrada);
        //Acceso a la hoja de trabajo
        XSSFSheet hoja = xlsx.getSheetAt(0);
        //Declaracion de fila y celda
        Row fila = null;
        Cell celda = null;

        try {
            //Asignando a valores a celdas con valores                                       
            fila = hoja.getRow(2);

            celda = fila.getCell(8);
            celda.setCellValue(Integer.parseInt(UPS_Monitoreados));
        } catch (NullPointerException NPE) {
            //En caso de que las celdas esten vacias hay que crearlas
            fila = hoja.createRow(0);

            celda = fila.createCell(0);
            celda.setCellValue(2);
            celda = fila.createCell(1);
            celda.setCellValue(6);

            fila = hoja.createRow(1);

            celda = fila.createCell(0);
            celda.setCellValue(9);
            celda = fila.createCell(1);
            celda.setCellValue(3);
        }

        //Evaluando formulas de todo el libro de excel
        XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx);

        //Cerrando la entrada archivo
        entrada.close();

        //Abriendo archivo para escritura
        FileOutputStream salida = new FileOutputStream(new File(directorio));
        //write changes
        xlsx.write(salida);
        //close the stream
        salida.close();
    }
}

From source file:Logica.L_POIExcel.java

public void Servic_Realizados() throws Exception {
    JFileChooser selector = new JFileChooser();
    selector.setFileFilter(new FileNameExtensionFilter("Excel 2010 or Superior", "xlsx"));
    //selector.setFileFilter(new FileNameExtensionFilter("Excel with Macrs", "xlsm"));        
    String sSql = "";
    String sSql1 = "";
    String sSql2 = "";
    String sSql3 = "";
    String sSql4 = "";
    String sSql5 = "";
    String sSql6 = "";
    String sSql7 = "";
    String sSql8 = "";

    String UPS_Monitoreados = "";
    String BYF = "";

    sSql = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and  Proveedor_Item like '%SISE%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')";
    sSql1 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and  Proveedor_Item like '%BYF%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')";
    sSql2 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and  Proveedor_Item like '%GE%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')";
    sSql3 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and  Proveedor_Item like '%EM%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')";
    sSql4 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and  Proveedor_Item like '%GATM%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')";
    sSql5 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and  Proveedor_Item like '%SOD%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')";
    sSql6 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and  Proveedor_Item like '%TEC%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')";
    try {//from   w w  w.  j  a va  2  s . c  om
        Statement st = cn.createStatement();
        ResultSet rs = st.executeQuery(sSql);
        rs.next();
        UPS_Monitoreados = rs.getString("Count(Nro_OT)");
        ResultSet rs1 = st.executeQuery(sSql1);
        rs1.next();
        BYF = rs1.getString("Count(Nro_OT)");
    } catch (Exception e) {
    }

    String directorio = "";
    String extension = "";

    boolean seleccion = false;

    int resultado = selector.showOpenDialog(null);

    switch (resultado) {
    case JFileChooser.APPROVE_OPTION:
        directorio = selector.getSelectedFile().getPath();

        int i = directorio.lastIndexOf('.');
        if (i >= 0) {
            extension = directorio.substring(i + 1);
        }
        seleccion = true;

        JOptionPane.showMessageDialog(null, "Seleccionaste" + directorio);
        break;
    case JFileChooser.CANCEL_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "No seleccionaste un archivo");
        break;
    case JFileChooser.ERROR_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "Ocurreo un Error");
        break;
    default:
        break;
    }
    if (extension.equals("xlsx")) {
        //Leer el archivo de Excel XLSX
        FileInputStream entrada = new FileInputStream(new File(directorio));
        //Acceso al libro de trabajo
        XSSFWorkbook xlsx = new XSSFWorkbook(entrada);
        //Acceso a la hoja de trabajo
        XSSFSheet hoja = xlsx.getSheetAt(0);
        //Declaracion de fila y celda
        Row fila = null;
        Cell celda = null;

        try {
            //Asignando a valores a celdas con valores                                       
            fila = hoja.getRow(1);

            celda = fila.getCell(8);
            celda.setCellValue(Integer.parseInt(UPS_Monitoreados));

            fila = hoja.getRow(2);

            celda = fila.getCell(8);
            celda.setCellValue(Integer.parseInt(BYF));
        } catch (NullPointerException NPE) {
            //En caso de que las celdas esten vacias hay que crearlas
            fila = hoja.createRow(0);

            celda = fila.createCell(0);
            celda.setCellValue(2);
            celda = fila.createCell(1);
            celda.setCellValue(6);

            fila = hoja.createRow(1);

            celda = fila.createCell(0);
            celda.setCellValue(9);
            celda = fila.createCell(1);
            celda.setCellValue(3);
        }

        //Evaluando formulas de todo el libro de excel
        XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx);

        //Cerrando la entrada archivo
        entrada.close();

        //Abriendo archivo para escritura
        FileOutputStream salida = new FileOutputStream(new File(directorio));
        //write changes
        xlsx.write(salida);
        //close the stream
        salida.close();
    }
}

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);//from  w  ww  . ja v a2s.c  o  m
    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);// w  w  w  .ja  v  a2  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;//w w w  .j a v a 2s  . c o m
        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.// w  ww.  j a va  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;/*from w  w w  .  ja  v a2s. com*/
    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();

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

    }//  w  w  w . java2  s .c  om

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

}