将数据库中表格的数据导出到Excel表中亲测有效

柔光的暖阳◎ 2023-01-01 02:54 156阅读 0赞

好东西分享给大家，  
需要引入的maven依赖

<dependencies>
            
            
            <dependency>
                <groupId>commons-fileupload</groupId>
                <artifactId>commons-fileupload</artifactId>
                <version>1.3.1</version>
            </dependency>
            <dependency>
                <groupId>commons-io</groupId>
                <artifactId>commons-io</artifactId>
                <version>2.5</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.21</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>
        </dependencies>

package com.fuyuan.ssm;
    
    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;
    
    
    import java.io.FileOutputStream;
    import java.sql.*;
    
    /** * @author supperman * @version 1.0 */
    public class TestPicture { 
    
        /** * 将数据库dname中的表名为tname的数据表写入Excel表格 * @param dname * @param tname */
        public static String writeDbtoExcel(String dname,String tname){ 
    
    
            String path="E:/axls/"+tname+".xls";
    
            HSSFWorkbook book=new HSSFWorkbook();
            HSSFSheet sheet=book.createSheet("表");
            try { 
                Class.forName("com.mysql.cj.jdbc.Driver");
                Connection con;
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+dname+"?useSSL=true&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");
                Statement st=con.createStatement();
                String sql="select * from "+tname;
                ResultSet rs=st.executeQuery(sql);
                //得到结果集的字段名
                ResultSetMetaData rsmd=rs.getMetaData();
                //得到数据表的结果集的字段的数量
                int c=rsmd.getColumnCount();
                //生成表单的第一行，即表头
                HSSFRow row0=sheet.createRow(0);//创建第一行
                for(int i=0;i<c;i++){ 
                    HSSFCell cel=row0.createCell(i);//创建第一行的第i列
                    cel.setCellValue(rsmd.getColumnName(i+1));
    // cel.setCellStyle(style);
                }
                //将数据表中的数据按行导入进Excel表中
                int r=1;
                while(rs.next()){ 
                    HSSFRow row=sheet.createRow(r++);//创建非第一行的其他行
                    for(int i=0;i<c;i++){ //仍然是c列，导入第r行的第i列
                        HSSFCell cel=row.createCell(i);
                        //以下两种写法均可
    // cel.setCellValue(rs.getString(rsmd.getColumnName(i+1)));
                        cel.setCellValue(rs.getString(i+1));
                    }
                }
                //用文件输出流类创建名为table的Excel表格
                FileOutputStream out=new FileOutputStream(path);
                book.write(out);//将HSSFWorkBook中的表写入输出流中
                book.close();
            } catch (Exception e) { 
                e.printStackTrace();
            }
            return path;
        }
    
        public static void main(String[] args) { 
            TestPicture.writeDbtoExcel("testdatabase","tb_supplier");
        }
    }