Store database data into the excel sheet using java Apache POI

Question

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

5. 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();
       }
   }
   

   }

Answer 1

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++;
        }
    }

Answer 2

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");

} }