在java的excel表格中生成报告

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

我想生成Excel报表，但是我无法生成Excel报表，不知道是什么问题?

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 .

每次点击生成报告按钮，我都需要生成自动报告。我正在使用sqlyog，我的表名是final，我的数据库名是etc.我的数据库表项不是静态的，所以我需要一个自动报告。

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

我正在使用Eclipse IDE，是否需要使用更多的外部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");
 }
}

3 个解决方案

#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.

我在数据库中创建了与您相同的表，并尝试运行您的代码。我可以创建Excel文件而不需要修改您的代码。不同之处在于我使用了不同的驱动程序(“oracle.jdb .driver. oracledriver”)。首先检查数据库连接。如果它成功了，那么其余的代码应该可以正常工作。请张贴更具体的例外，如果有。这将有助于解决问题。还有一件事你已经从第1行和单元格1中使用了索引，但是POI使用了从0开始的行和列的索引。

Read Excel file and generate report as follows

阅读Excel文件并生成如下报告

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

您可以从excel中读取所有的行和列并在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.

要在Excel文件中添加头文件，请使用以下代码。

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.

您可以提供使用CellRangeAddress合并的范围。将startRow、endRow、startCol、endCol作为值创建单元范围地址。

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

创建合并区域后，您应该在区域中最左边的单元格中设置值，即在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);

#2

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

您可以使用apache poi库类(如HSSFSheet、HSSFRow)通过java代码创建excel表。

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.

使用上述程序，您可以创建一个excel表。

#3

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