poi(1)读写excel小例子

最新推荐文章于 2025-04-02 20:17:38 发布

原创最新推荐文章于 2025-04-02 20:17:38 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #exception #import #string #templates #null

apache 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种将数据库查询结果导出到Excel文件的方法。通过Java编程实现，利用JDBC连接数据库并执行SQL查询，再使用Apache POI库将查询结果写入Excel文件。此过程包括获取数据库连接、执行查询、读取结果集元数据、填充Excel表格等步骤。

从databse写入到excel中

/*
* Created on 2006-8-28
*
* XXX To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package pois;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
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;

/**
* @author jacky
*
* XXX To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class DbtoExcel {

    public static void main(String[] args) throws Exception
    {
      String filename = "c://dept.xls";
      String column_name="";
      PreparedStatement ps=null; //预编译
      Connection conn=null;      //连接
      Statement smt=null;         //sql语句
      ResultSet rs = null,rs1=null;        //结果集合
      ResultSetMetaData md = null;
     long rowCount =0l;               //总行数
       try{

       Class.forName("net.sourceforge.jtds.jdbc.Driver");
       }catch(Exception e){
        e.printStackTrace();
       }
       String url = "jdbc:jtds:sqlserver://10.1.0.6:1433;DatabaseName=szPayroll";
       String userName = "sa";
       String password = "sql";
       conn = DriverManager.getConnection (url, userName, password);
       smt = conn.createStatement();

       //取得总记录数
       String sql="select * from department";
       String sqlqeury="select count(*) as total from department";
       try{
            rs = smt.executeQuery(sqlqeury);
            if(rs.next()){
                rowCount =rs.getLong("total");
                System.out.println(rowCount) ; //记录总数
             }
         }catch(Exception e){
          System.out.print("aaa");
                e.printStackTrace();
                System.out.println(e.getMessage());
                conn.close();   //异常后关闭连接
                throw new Exception("获得记录总数失败");
          }

        //处理sql语句
        try{
             rs1 = smt.executeQuery(sql);
             md = rs1.getMetaData();
              System.out.println(md.getColumnCount());
         }catch(Exception e){
           System.out.print("bbb");
               e.printStackTrace();
               System.out.println(e.getMessage());
               conn.close();   //异常后关闭连接
               throw new Exception("传入的SQL无法处理");
        }


       //生成excel代码
       HSSFWorkbook wb = new HSSFWorkbook();
       HSSFSheet sheet = wb.createSheet("dbCreate");
       HSSFRow row = null;
       HSSFCell cell=null;
       for(long i =0;i<=rowCount;i++){

           row = sheet.createRow((short)i);                     //创建一个行
           for(int j =0;j<md.getColumnCount();j++){

              cell = row.createCell((short)j);                  //创建单元格
              cell.setEncoding(HSSFCell.ENCODING_UTF_16);
              if(i==0){
                 cell.setCellValue(md.getColumnName(j+1));     //设定单元格的值
              }else{
                 cell.setCellValue(rs1.getString(j+1));        //设定单元格的值
               }
            }
            rs1.next();
        }
       //写到excel中!
       FileOutputStream fileOut = new FileOutputStream(filename);
       wb.write(fileOut);          //向fileout文件写
       fileOut.close();            //关闭文件输出流
       conn.close();               //关闭正常连接
}

}