Store database data into the excel sheet using java Apache POI

Question

I am executing query and I am getting results as 149.
I want to flush my results to xls file.
I just called writeExcelSheetForCountByCategories() in the test
But I am getting column header only in the xls file

Can anybody knows What I am missing here?

@Test
    public void getCountByCategories()
    {
        Map<String, String> results = DbManager.getCountByCategories(); 
        ExcelUtils.writeExcelSheetForCountByCategories(results);
    }

// code for writing in excel sheet
public static void writeExcelSheetForCountByCategories(Map<String, String> results) {

    // Blank workbook
    @SuppressWarnings("resource")
    XSSFWorkbook workbook = new XSSFWorkbook();

    // Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Count_sheet");

    // This data needs to be written (Object[])
    Map<String, Object[]> data = new TreeMap<String, Object[]>();
    data.put("1", new Object[] { "ID", "PREV COUNT", "CURRENT COUNT","STAGING"});
    data.put("2", new Object[] { results});

    // Iterate over data and write to sheet
    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        // this creates a new row in the sheet
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            // this line creates a cell in the next column of that row
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
        }
    }
    try {
        // this Writes the workbook
        FileOutputStream out = new FileOutputStream(new File("Counts2.xlsx"));
        workbook.write(out);
        out.close();
        LOG.debug("Counts2.xlsx written successfully on disk.");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

}

you might think about stopping writing boilerplate code by switching to MemPOI github.com/firegloves/MemPOI — firegloves
– firegloves, Commented Jul 2, 2019 at 10:36

surendrapanday · Accepted Answer · 2018-05-30 12:53:52Z

Below is working example for table which have following column..

EMP ID | EMP NAME | DEG | SALARY | DEPT

import java.io.File;
import java.io.FileOutputStream;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDatabase {
   public static void main(String[] args) throws Exception {
     Class.forName("com.mysql.jdbc.Driver");
     Connection connect = DriverManager.getConnection( 
     "jdbc:mysql://localhost:3306/test" , 
     "root" , 
     "root"
  );

  Statement statement = connect.createStatement();
  ResultSet resultSet = statement.executeQuery("select * from emp_tbl");
  XSSFWorkbook workbook = new XSSFWorkbook(); 
  XSSFSheet spreadsheet = workbook.createSheet("employe db");

  XSSFRow row = spreadsheet.createRow(1);
  XSSFCell cell;
  cell = row.createCell(1);
  cell.setCellValue("EMP ID");
  cell = row.createCell(2);
  cell.setCellValue("EMP NAME");
  cell = row.createCell(3);
  cell.setCellValue("DEG");
  cell = row.createCell(4);
  cell.setCellValue("SALARY");
  cell = row.createCell(5);
  cell.setCellValue("DEPT");
  int i = 2;

  while(resultSet.next()) {
     row = spreadsheet.createRow(i);
     cell = row.createCell(1);
     cell.setCellValue(resultSet.getInt("eid"));
     cell = row.createCell(2);
     cell.setCellValue(resultSet.getString("ename"));
     cell = row.createCell(3);
     cell.setCellValue(resultSet.getString("deg"));
     cell = row.createCell(4);
     cell.setCellValue(resultSet.getString("salary"));
     cell = row.createCell(5);
     cell.setCellValue(resultSet.getString("dept"));
     i++;
  }

  FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx"));
  workbook.write(out);
  out.close();
  System.out.println("exceldatabase.xlsx written successfully");

} }

selvi · Accepted Answer · 2018-07-02 11:13:47Z

The below method we can use create a sheet in excel file, We don't need to specify column number and I am passing Db records,header values and sheetName as argument. Based on the result set size and it will create the rows and column dynamically.

Note:

// call this method in .java test file using @AfterClass annotation
writes in single excel file with multiple sheets. This is my scenario, We can use annotation based on our needs
@AfterClass()
    private static void WriteInExcelFile() throws Exception {
        excelWriterUtils.writeExcelFile("Results.xls");
    }

public void writeResultsnExcelSheet(List<Map<String, String>> records, String sheetName,
            List<String> headerValues) throws Exception {
        XSSFSheet spreadsheet = workbook.createSheet(sheetName);

        // Header
        XSSFRow header = spreadsheet.createRow(0);
        setValue(header, headerValues);

        for (int i = 0, size = records.size(); i < size; i++) {
            XSSFRow row = spreadsheet.createRow(i + 1);
            Map<String, String> record = records.get(i);
            setValue(row, record.values());
        }

private void setValue(XSSFRow row, Collection<String> values) {
        Iterator<String> valueIter = values.iterator();
        int index = 0;
        while (valueIter.hasNext()) {
            XSSFCell cell = row.createCell(index);
            cell.setCellValue(valueIter.next());
            index++;
        }
    }

Collectives™ on Stack Overflow

Store database data into the excel sheet using java Apache POI

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related