generating report in excel sheet in java

Question

I want to generate Excel Report but I am not able to generate excel report , I don't know what is the problem ?

I need to generate automated report everytime I click on generate report button. I am using sqlyog,my table name is final and my database name is etc. my database table entries are not static so I need an automated report .

I am using Eclipse IDE Is it that I need to use any more external api.

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.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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

    Statement statement = connect.createStatement();
    ResultSet resultSet = statement.executeQuery("select * from final");
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet spreadsheet = workbook.createSheet("engine report");
    HSSFRow row = spreadsheet.createRow(1);
    HSSFCell cell;
    cell = row.createCell(1);
    cell.setCellValue("engine_code");
    cell = row.createCell(2);
    cell.setCellValue("var1");
    cell = row.createCell(3);
    cell.setCellValue("var2");
    cell = row.createCell(4);
    cell.setCellValue("var3");
    cell = row.createCell(5);
    cell.setCellValue("var4");
    cell = row.createCell(6);
    cell.setCellValue("var5");
    cell = row.createCell(7);
    cell.setCellValue("User_Name");
    cell = row.createCell(8);
    cell.setCellValue("time_stamp");
    int i = 2;
    while (resultSet.next()) {
        row = spreadsheet.createRow(i);
        cell = row.createCell(1);
        cell.setCellValue(resultSet.getInt("ec"));
        cell = row.createCell(2);
        cell.setCellValue(resultSet.getString("v1"));
        cell = row.createCell(3);
        cell.setCellValue(resultSet.getString("v2"));
        cell = row.createCell(4);
        cell.setCellValue(resultSet.getString("v3"));
        cell = row.createCell(5);
        cell.setCellValue(resultSet.getString("v4"));
        cell = row.createCell(6);
        cell.setCellValue(resultSet.getString("v5"));
        cell = row.createCell(7);
        cell.setCellValue(resultSet.getString("user"));
        cell = row.createCell(8);
        cell.setCellValue(resultSet.getString("time"));
        i++;
    }
    FileOutputStream out = new FileOutputStream(new File("exceldatabase.xls"));
    workbook.write(out);
    out.close();
    System.out.println("exceldatabase.xls written successfully");
 }
}

Answer 1

I created a same table in database as yours and tried running your code. i could create Excel file without changing your code. Just the difference is i used different driver ("oracle.jdbc.driver.OracleDriver"). So first check your database connection. If it is successful then rest of the code should work fine.
Please post the more specific exception if any. That will help solve the problem. One more thing you have used indexing from row 1 and cell 1 but POI uses indexing of rows and columns from 0.

Read Excel file and generate report as follows

You can read all rows and columns from excel and display it in your UI.

    FileInputStream file = new FileInputStream("exceldatabase.xls");
    Workbook wb = new HSSFWorkbook(file);
    Sheet sheet = wb.getSheet("engine report");
    int lastRowNum = sheet.getLastRowNum();
    for(int rowIndex = 0 ; rowIndex < lastRowNum ; rowIndex++){
        Row currRow = sheet.getRow(rowIndex);
        if(currRow != null) {
            List<String> currRowValues = new ArrayList<String>();
            for(int cellNo = currRow.getFirstCellNum(); cellNo < currRow.getLastCellNum();cellNo++) {
                Cell currCell = currRow.getCell(cellNo);

                if(currCell != null) {
                    int cellType = currCell.getCellType();
                    switch(cellType) {
                        case Cell.CELL_TYPE_BLANK :
                            currRowValues.add("");
                        break;
                        case Cell.CELL_TYPE_BOOLEAN :
                            currRowValues.add(String.valueOf(currCell.getBooleanCellValue()));
                        break;
                        case Cell.CELL_TYPE_NUMERIC :
                            currRowValues.add(String.valueOf(currCell.getNumericCellValue()));
                        break;
                        case Cell.CELL_TYPE_STRING :
                            currRowValues.add(currCell.getStringCellValue());
                        break;
                        case Cell.CELL_TYPE_ERROR :
                            currRowValues.add("");
                        break;

                    }
                } else {
                    currRowValues.add("");
                }

            }

            // Add your code here 
            // Add current list to your UI or the way you want to display report
                System.out.println( currRowValues);
        }
    } 

For adding Header in Excel file use following code.

You should create a Merged region in sheet.

You can provide range be be merged using CellRangeAddress. Which takes startRow, endRow , startCol ,endCol as values to create cell range address.

After creating merged region you should set the value in left most cell in region i.e. cell at startRow,startCol.

I have used alignment to align content in center.

Save your file and you will get the expected result. :)

    HSSFRow createRow = spreadsheet.createRow(0);
    CellRangeAddress range = new CellRangeAddress(0, 0, 1, 8);
    spreadsheet.addMergedRegion(range);
    HSSFCell createCell = createRow.createCell(1);
    createCell.setCellValue("My header");//ADD Your Custom Header value Here 
    CellUtil.setAlignment(createCell, workbook, CellStyle.ALIGN_CENTER);

Answer 2

It seems you are overwriting the same row and cell objects again and again. for each new cell you need to create a new object :

               //instead of 
               HSSFCell cell;
               cell = row.createCell(1);
               cell.setCellValue("engine_code");
               cell = row.createCell(2);
               cell.setCellValue(resultSet.getString("v1"));

               //do
               HSSFCell cell1 = row.createCell(1);
               cell1.setCellValue("engine_code");
               HSSFCell cell2 = row.createCell(2);
               cell2.setCellValue(resultSet.getString("v1"));

The same applies to rows objects:

               HSSFRow row1 = spreadsheet.createRow(1);
               HSSFRow row2 = spreadsheet.createRow(2);

Answer 3

You can create excel sheet via java code using apache poi library classes like HSSFSheet, HSSFRow.

import java.io.ByteArrayOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

public class CreateExcel(){
    public static void main(String args[]){
       ByteArrayOutputStream baos = new ByteArrayOutputStream(); 
       HSSFWorkbook workbook = new HSSFWorkbook();
       HSSFSheet sheet = workbook.createSheet("sheet 1");
       HSSFRow row = sheet.createRow(rowNumber); // 0,1,2..
       HSSFCell cell = row.createCell(columnNumber); // 0,1,2...
       cell.setCellValue("Hello Apache POI !");
       HSSFFont font = workbook.createFont();
       HSSFCellStyle style = workbook.createCellStyle();
       style.setFont(font);
       cell.setCellStyle(style);
       workbook.write(baos);
       baos.flush();
  }
}

Using above program you can create an excel sheet.