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

user3027085 发表于 Dev

用户名

帮助停留在从结果集导出到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();
                }

SparkOn

啊，在发布之前尝试对其进行格式化的检查非常困难，对于查询，请尝试类似这样的操作。

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