使用Apache Poi将Java数据库中的结果集导出到Excel

sshcrbum 于 2023-01-16 发布在 Java

关注(0)|答案(4)|浏览(143)

帮助是停留在这个项目的导出结果集到Excel。以前的解决方案在这里还没有回答我的问题，但他们有帮助。这里是我的代码到目前为止，它只显示数据库中的一行.我的代码

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

public class Plexada2 {

        public static void main(String[] args) {
        try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         Connection conn = DriverManager.getConnection("jdbc:odbc:Storeway","root", "");
         Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
         ResultSet rs = st.executeQuery("Select * from Storeway.order");

         HSSFWorkbook workbook = new HSSFWorkbook();
         HSSFSheet sheet = workbook.createSheet("lawix10");

         Row row = sheet.createRow(0);
         int x=0;
         while (rs.next()){
         String crate_id=  rs.getString(2);
         String content=rs.getString(3);
         String Order_type=  rs.getString(4);
         java.sql.Date date= rs.getDate(5);
         String datex= String.valueOf(date);

         row.createCell(0).setCellValue(crate_id);
         row.createCell(1).setCellValue(content);
         row.createCell(2).setCellValue(Order_type);
         row.createCell(3).setCellValue(datex); 

                }
         x+=1;  


        String yemi = "C:\\Users\\lawix10\\Desktop\\testlno9.xls";
        FileOutputStream fileOut;
        try {
             fileOut = new FileOutputStream(yemi);
             workbook.write(fileOut);
             fileOut.close();
                }

Java

来源：https://stackoverflow.com/questions/24933868/exporting-resultset-from-java-database-to-excel-using-apache-poi

4条答案

按热度按时间

krugob8w1#

啊，这是非常困难的检查您的代码尝试格式化它之前，您张贴和关于您的查询尝试这样的东西。

try {
    Class.forName("driverName");
    Connection con = DriverManager.getConnection("url", "user", "pass");
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("Select * from tablename");
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("lawix10");
    HSSFRow rowhead = sheet.createRow((short) 0);
    rowhead.createCell((short) 0).setCellValue("CellHeadName1");
    rowhead.createCell((short) 1).setCellValue("CellHeadName2");
    rowhead.createCell((short) 2).setCellValue("CellHeadName3");
    int i = 1;
    while (rs.next()){
        HSSFRow row = sheet.createRow((short) i);
        row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("column1")));
        row.createCell((short) 1).setCellValue(rs.getString("column2"));
        row.createCell((short) 2).setCellValue(rs.getString("column3"));
        i++;
    }
    String yemi = "g:/test.xls";
    FileOutputStream fileOut = new FileOutputStream(yemi);
    workbook.write(fileOut);
    fileOut.close();
    } catch (ClassNotFoundException e1) {
       e1.printStackTrace();
    } catch (SQLException e1) {
        e1.printStackTrace();
    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    } catch (IOException e1) {
        e1.printStackTrace();
    }

赞(0）回复(0）举报 2023-01-16

5kgi1eie2#

我建议使用另一种解决方案，利用MemPOI lib。

public class Plexada2 {

    public static void main(String[] args) {
        try {
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
           Connection conn = DriverManager.getConnection("jdbc:odbc:Storeway","root", "");
           PreparedStatement prepStmt = conn.prepareStatement("Select * from Storeway.order");
           
           File fileDest = new File("C:\\Users\\lawix10\\Desktop\\testlno9.xls");

           final MempoiReport mempoiReport = 
                MempoiBuilder.aMemPOI()
                    .withFile(fileDest)
                    .addMempoiSheet(new MempoiSheet(prepStmt))
                    .build()
                    .prepareMempoiReport()
                    .get();

           final String file = mempoiReport.getFile();

         } catch (Exception e) {
            // TODO manage exception
         }
    }
}

赞(0）回复(0）举报 2023-01-16

q35jwt9p3#

我们可以用for循环来输入动态值到getString（）吗？我试过了，但是我无法获取准确的结果。
我的代码：public static void excel（字符串appDB）{列表头值=new数组列表（）;

XSSFWorkbook workbook = new XSSFWorkbook();
    try {
    setConnection(appDB);

    String queryName="SELECT * FROM ALL_TABLES where table_name='table_name'";

    Reporter.addStepLog("----------------------------------- " + queryName.toUpperCase()
            + "\n - Validation Start" + " -----------------------------------");
    ps = con.prepareStatement(queryName, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    rs = ps.executeQuery();

    Statement statement = con.createStatement();
    XSSFSheet spreadsheet = workbook.createSheet("employedb");
      ResultSet resultSet = statement.executeQuery("select * from all_tab_columns where table_name='table_name'"); 
      XSSFRow row = spreadsheet.createRow(0);
      XSSFCell cell;
      int cc=resultSet.getMetaData().getColumnCount();
      for(int i=1;i<=cc;i++)
      {
          String headerVal=resultSet.getMetaData().getColumnName(i);
          headerValues.add(headerVal);
          cell = row.createCell(i-1);
          cell.setCellValue(resultSet.getMetaData().getColumnName(i));
      }
      System.out.println(headerValues);
      int i = 1;
      while (resultSet.next())
      {  
          for(int j=1;j<=cc;j++)
          {  
          System.out.println(resultSet.getString(j));
          XSSFRow row1 = spreadsheet.createRow((short) i);
          row1.createCell((short) i).setCellValue(resultSet.getString(resultSet.getMetaData().getColumnName(j)));
          i++;

      }  
      }

      FileOutputStream out = new FileOutputStream(new File("S:\\Downloads\\excel.xlsx"));
      workbook.write(out);
      out.close();  
      System.out.println("exceldatabase.xlsx written successfully");

}catch(Exception e){}
}

赞(0）回复(0）举报 2023-01-16

pengsaosao4#

上述问题得到解决。

public static void excel(String appDB)
{
    List<String> headerValues=new ArrayList<String>();

    XSSFWorkbook workbook = new XSSFWorkbook();
    try {
    setConnection(appDB);

    String queryName="SELECT * FROM ALL_TABLES where table_name='table_name";

    Reporter.addStepLog("----------------------------------- " + queryName.toUpperCase()
            + "\n - Validation Start" + " -----------------------------------");
    ps = con.prepareStatement(queryName, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    rs = ps.executeQuery();

    Statement statement = con.createStatement();
    XSSFSheet spreadsheet = workbook.createSheet("employedb");
      ResultSet resultSet = statement.executeQuery("select * from all_tab_columns where table_name='table_name'");

      XSSFRow row = spreadsheet.createRow(0);
      XSSFCell cell;
      int cc=resultSet.getMetaData().getColumnCount();
      for(int i=1;i<=cc;i++)
      {
          String headerVal=resultSet.getMetaData().getColumnName(i);
          headerValues.add(headerVal);
          cell = row.createCell(i-1);
          cell.setCellValue(resultSet.getMetaData().getColumnName(i));
      }
      System.out.println(headerValues);

      int i = 1;
      while (resultSet.next())
      {  

          XSSFRow row1 = spreadsheet.createRow((short) i);
          for(int p=0;p<headerValues.size();p++)
          {
          row1.createCell((short) p).setCellValue(resultSet.getString(headerValues.get(p)));
          }
          i++;
      } 
      FileOutputStream out = new FileOutputStream(new File("S:\\Downloads\\excel.xlsx"));
      workbook.write(out);
      out.close();  
      System.out.println("exceldatabase.xlsx written successfully");

}catch(Exception e){}
}

赞(0）回复(0）举报 2023-01-16

我来回答

使用Apache Poi将Java数据库中的结果集导出到Excel

4条答案

相关问题

热门标签

最新问答