从数据库中读取表，导出成excel模板

Z时代
2024-01-10
分类：IT

图片描述

这是数据库中的一张表，根据不同的report_id存放了不同的报表模板，我想根据这个id导出不同的excel，其中table_name作为标题和sheetname,label_id和label_name为第二行和第三行的所有列

回答：


import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
public class ExportSql {
    private static String path = "D:\\test\\";
    public static void main(String[] args) {
        String sql = "select report_id, label_id, label_name from tableName order by report_id";
        List<List<Object>> listListTable = getTable(sql);
        List<List<Object>> listList = new ArrayList<List<Object>>();
        for (int i = 0; i < listListTable.size(); i++) {
            if (i > 1 && !listListTable.get(i).get(0).equals(listListTable.get(i - 1).get(0))) {
                //跟上一个id不同换个文件
                exportExcel(listList, listListTable.get(i - 1).get(1).toString());
                listList.clear();
                listList.add(listListTable.get(i));
            } else {
                listList.add(listListTable.get(i));
            }
            if (i == listListTable.size() - 1) {
                exportExcel(listList, listListTable.get(i).get(1).toString());
            }
        }
    }
    //根据sql获得数据
    public static List<List<Object>> getTable(String sql){
        List<List<Object>> listList = new ArrayList<List<Object>>();
        Connection conn = getConnection();
        PreparedStatement pstmt;
        ResultSet rs;
        try {
            pstmt = conn.prepareStatement(sql.trim());
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()){
                List<Object> list = new ArrayList<Object>();
                for (int i = 0; i < rsmd.getColumnCount(); i++){
                    list.add(rs.getString(rsmd.getColumnName(i+1)));
                }
                listList.add(list);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            close(conn);
        }
        return listList;
    }
    // 生成excel
    public static void exportExcel(List<List<Object>> listList, String tableName){
        listList.remove(1);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(tableName);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(1, true);
        for (int i = 0; i < listList.size(); i++){
            Row row = sheet.createRow(i + 4);
            List<Object> list = listList.get(i);
            for (int j = 0; j < list.size(); j++){
                Cell cell = row.createCell(j);
                if (list.get(j) != null){
                    cell.setCellValue(list.get(j).toString());
                }
            }
        }
        OutputStream os = null;
        try {
            os = new FileOutputStream(path + tableName + ".xls");
            wb.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                wb.close();
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    public static Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/databaseName", "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static void close(Connection conn){
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }}