java - 使用 java Apache POI 将数据库数据存储到 Excel 工作表中

Question

1. 我正在执行查询，得到的结果为 149。
2. 我想将结果刷新到 xls 文件。
3. 我刚刚在测试中调用了 writeExcelSheetForCountByCategories()
4. 但我只在 xls 文件中获取列标题

5. 有人知道我在这里缺少什么吗？

   @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

下面是具有以下列的表格的工作示例..

EMP ID | EMP 姓名 |度 |薪资|部门

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

}}