java - 在SQL IN子句中多次传递900个参数并使用Java导出到xlsx文件

Question

我在 SQL 查询中多次传递不同的 900 个参数，并且希望将所有数据以 xlsx 格式写入单个 Excel 工作表中

我已从 Excel 读取所有 12000 条数据并将其存储到 Arraylist 中。然后我将该数组列表分成包含数据 900 的 block ，并每次传递查询 900。现在，导出 900 第一次成功，但是当第二组 900 参数被推下时，它给我“尝试写入一行” [0] 在已写入磁盘的 [0,900] 范围内。”

package iCAPs;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class iCAPsRawData {
    public static final String SAMPLE_XLSX_FILE_PATH = "C:\\Users\\sm39766\\Desktop\\icaps\\A.xlsx";

    public static void main(String[] args) throws IOException, InvalidFormatException, SQLException, ClassNotFoundException {

        Workbook writeWorkbook;

        ArrayList<String> GOCIds = new ArrayList<String>();

        List<String> sublist=new ArrayList<String>();

        Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));


        System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");
        System.out.println("Retrieving Sheets using for-each loop");
        for (Sheet sheet : workbook) {
            System.out.println("=> " + sheet.getSheetName());
        }


        Sheet sheet = workbook.getSheetAt(0);
        System.out.println("Before Starting Process Excel Content "+GOCIds.size());
        DataFormatter dataFormatter = new DataFormatter();
        System.out.println("\n\nIterating over Rows and Columns using for-each loop\n");
        for (Row row : sheet) {
            for (Cell cell : row) {
                String tempValue = "'"+dataFormatter.formatCellValue(cell)+"'";
                System.out.println(tempValue);
                GOCIds.add(tempValue);
            }
        }
        Sheet desSheet = null;
        Statement stmt = null;
        ResultSet rs = null;
        int columnsNumber = 0;
        ResultSetMetaData rsmd = null;
        FileOutputStream fileOut = null;
        Connection cntn = null;
        writeWorkbook = new SXSSFWorkbook();
        desSheet = writeWorkbook.createSheet("GOC_");
        int size = 3;
        System.out.println("After Starting Process Excel Content "+GOCIds.size());
        for (int start = 0; start < GOCIds.size(); start += size) {
            int end = Math.min(start + size, GOCIds.size());
            sublist = GOCIds.subList(start, end);
            //System.out.println(sublist);
            String formattedString = sublist.toString()
                    .replace("[", "")
                    .replace("]", "")
                    .trim();
            //System.out.println(formattedString);

            Class.forName("oracle.jdbc.driver.OracleDriver");
            cntn = DriverManager.getConnection("jdbc:oracle:thin:@local:8888:OPDB", "tiger", "scoot");
            stmt = cntn.createStatement();
            rs = stmt.executeQuery("Select d.feed_id,\r\n" + 
                    "( Select path from COM_DATA.HIER_DOMAIN_DENORM where tree_id=1000 and node_id=d.ms_prnt_node_id) MS_Path,\r\n" + 
                    "( Select path from COM_DATA.HIER_DOMAIN_DENORM where tree_id=3000 and node_id=d.GEO_prnt_node_id) GEO_Path,\r\n" +
                    "( Select path from COM_DATA.HIER_DOMAIN_DENORM where tree_id=2000 and node_id=d.lv_prnt_node_id) LV_Path,\r\n" +  
                    "( Select path from COM_DATA.HIER_DOMAIN_DENORM where tree_id=4000 and node_id=d.func_prnt_node_id) FUNC_Path\r\n" + 
                    "from COM_DATA.HIER_GOC_DENORM d\r\n" + 
                    "where d.feed_id IN ("+formattedString+")");
            rsmd = rs.getMetaData();

            columnsNumber = rsmd.getColumnCount();

            /*ArrayList<String> feedID=new ArrayList<String>();
            ArrayList<String> MS=new ArrayList<String>();
            ArrayList<String> GEO=new ArrayList<String>();
            ArrayList<String> LV=new ArrayList<String>();
            ArrayList<String> FUNC=new ArrayList<String>();
            while (rs.next()){
            feedID.add(rs.getString(1));
            MS.add(rs.getString(2));
            GEO.add(rs.getString(3));
            LV.add(rs.getString(4));
            FUNC.add(rs.getString(5));
            }
            System.out.println("GOC_ID:"+feedID);
            System.out.println("MS:"+MS);
            System.out.println("GEO:"+GEO);
            System.out.println("LV:"+LV);
            System.out.println("FUNC:"+FUNC);*/


            Row desRow1 = desSheet.createRow(0);

            for (int col = 0; col < columnsNumber; col++) {
                Cell newpath = desRow1.createCell(col);
                newpath.setCellValue(rsmd.getColumnLabel(col + 1));
            }

            while (rs.next()) {
                Row desRow = desSheet.createRow(rs.getRow());
                for (int col = 0; col < columnsNumber; col++) {
                    Cell newpath = desRow.createCell(col);
                    newpath.setCellValue(rs.getString(col + 1));
                }
                System.out.println("Row Number -->" + rs.getRow());
            }
            fileOut = new FileOutputStream("GOCs.xlsx");
            writeWorkbook.write(fileOut);
        }
        workbook.close();
        writeWorkbook.close();

    }
}

当我在查询中传递超过 900 个值以每次获取数据时，请告诉我如何将这些值存储在一个位置并将其写入单个 Excel 文件中。

Answer 1

int rowNum = 0;
for (int start = 0; start < GOCIds.size(); start += size) {
    ...
    Row desRow1 = desSheet.createRow(rowNum);
    rowNum++;
    while (rs.next()) {
        Row desRow = desSheet.createRow(rowNum);
        rowNum++;
    ...
    }
}
//after for loop
fileOut = new FileOutputStream("GOCs.xlsx");
writeWorkbook.write(fileOut);
workbook.close();
writeWorkbook.close();

正如我的评论中所说:

• 为所有数据创建行计数器
• 使用行计数器创建新行
• 增加行计数器(切勿重置)
• 仅在添加所有数据后才将工作簿写入磁盘