java - 如果第一个工作表已满，如何在 Excel 中添加其他工作表

Question

我正在使用 jExcel API 将数据写入 Excel 工作表。下面是我的代码，运行良好。但是如果我将大量数据写入 Excel 工作表，我会收到此异常 -

jxl.write.biff.RowsExceededException: The maximum number of rows permitted on
a worksheet been exceeded

下面是代码。如果第一张纸无法容纳更多行，如何将数据写入另一张纸。有人可以帮我吗？

 public void write() throws IOException, WriteException {

    readFileIPAddress();

    File file = new File(inputFile);
    WorkbookSettings wbSettings = new WorkbookSettings();

    wbSettings.setLocale(new Locale("en", "EN"));

    WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
    workbook.createSheet("Report", 0);
    WritableSheet excelSheet = workbook.getSheet(0);
    createLabel(excelSheet);
    createContent(excelSheet);

    workbook.write();
    workbook.close();
    }


    private void createLabel(WritableSheet sheet) throws WriteException {

    WritableFont times15pt = new WritableFont(WritableFont.TAHOMA, 11);
    times = new WritableCellFormat(times15pt);
    times.setWrap(true);

    WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TAHOMA, 13, WritableFont.BOLD, false, UnderlineStyle.SINGLE);
    timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
    timesBoldUnderline.setWrap(true);

    CellView cv = new CellView();
    cv.setFormat(times);
    cv.setFormat(timesBoldUnderline);
    cv.setAutosize(true);

    addCaption(sheet, 0, 0, "IP Address");
    addCaption(sheet, 1, 0, "Digital Element Country Code");
    addCaption(sheet, 2, 0, "Maxmind Country Code");
    addCaption(sheet, 3, 0, "Neustar City");
    addCaption(sheet, 4, 0, "Maxmind City");
    addCaption(sheet, 5, 0, "Neustar Zip Code");
    addCaption(sheet, 6, 0, "Maxmind Zip Code");
    }

     private void createContent(WritableSheet sheet) throws WriteException, RowsExceededException {

    NaEdgeDb edge = null;
    try {
    while (!workQueue.isEmpty()) {
        String ipAddress = workQueue.take();

        edge = new NaEdgeDb();
        edge.set_server_addr(serverIPAddress);
        edge.set_id(10);
        if (checkIPDuplicate.add(ipAddress)) {

            resp = GeoLocationService.getLocationIp(ipAddress, 0);
            edge.naQueryEdge(ipAddress);

            addNumber(sheet, 0, j, ipAddress);

            addNumber(sheet, 1, j, (edge.edge_twoLetterCountry));

            addNumber(sheet, 2, j, (resp.getLocation() != null ? resp.getLocation().getCountry() : null));

            addNumber(sheet, 3, j, (edge.edge_city));

            addNumber(sheet, 4, j, (resp.getLocation() != null ? resp.getLocation().getCity() : null));

            addNumber(sheet, 5, j, (edge.edge_postalCode));

            addNumber(sheet, 6, j, (resp.getLocation() != null ? resp.getLocation().getZipCode() : null));
            j++;
        } 
        } 
    } catch (Exception e) {
        getLogger().log(LogLevel.ERROR, e.fillInStackTrace());
        e.printStackTrace();
    }
  }

    private void addCaption(WritableSheet sheet, int column, int row, String s) throws RowsExceededException,
        WriteException {
    Label label;
    label = new Label(column, row, s, timesBoldUnderline);
    sheet.addCell(label);
    }

    private void addNumber(WritableSheet sheet, int column, int row, String str) throws WriteException,
        RowsExceededException {
    Label number;
    number = new Label(column, row, str);
    sheet.addCell(number);
    }

更新的代码:-

这仍然抛出相同的异常。不知道为什么？我已经在这段代码中处理了新工作表的创建？这段代码有什么问题吗？

 private void createContent(WritableSheet sheet, WritableWorkbook workbook2) throws WriteException, RowsExceededException {

    NaEdgeDb edge = null;
    WritableSheet ws2 = workbook2.createSheet("sheet2", 2);
    try {
    while (!workQueue.isEmpty()) {
        String ipAddress = workQueue.take(); // generateIPAddress();

        edge = new NaEdgeDb();
        edge.set_server_addr(serverIPAddress);
        edge.set_id(10);
        System.out.println(sheet.getRows());
        if (checkIPDuplicate.add(ipAddress) && sheet.getRows() <= 65536 ) {

            resp = GeoLocationService.getLocationIp(ipAddress, 0);
            edge.naQueryEdge(ipAddress);

            addNumber(sheet, 0, j, ipAddress);

            addNumber(sheet, 1, j, (edge.edge_twoLetterCountry));

            addNumber(sheet, 2, j, (resp.getLocation() != null ? resp.getLocation().getCountry() : null));

            addNumber(sheet, 3, j, (edge.edge_city));

            addNumber(sheet, 4, j, (resp.getLocation() != null ? resp.getLocation().getCity() : null));

            addNumber(sheet, 5, j, (edge.edge_postalCode));

            addNumber(sheet, 6, j, (resp.getLocation() != null ? resp.getLocation().getZipCode() : null));
            j++;
        } else {
            resp = GeoLocationService.getLocationIp(ipAddress, 0);
            edge.naQueryEdge(ipAddress);

            addNumber(ws2, 0, j, ipAddress);

            addNumber(ws2, 1, j, (edge.edge_twoLetterCountry));

            addNumber(ws2, 2, j, (resp.getLocation() != null ? resp.getLocation().getCountry() : null));

            addNumber(ws2, 3, j, (edge.edge_city));

            addNumber(ws2, 4, j, (resp.getLocation() != null ? resp.getLocation().getCity() : null));

            addNumber(ws2, 5, j, (edge.edge_postalCode));

            addNumber(ws2, 6, j, (resp.getLocation() != null ? resp.getLocation().getZipCode() : null));
            k++;
        }
        } 
    } catch (Exception e) {
        getLogger().log(LogLevel.ERROR, e.fillInStackTrace());
        e.printStackTrace();
    }
    System.out.println("Size of HashSet:" + checkIPDuplicate.size());
    }

Answer 1

我认为你可以使用WritableWorkbook#createSheet(java.lang.String, int) )以在需要时创建新工作表。

在 createContent 方法中传递 WritableWorkbook，而不是 WritableSheet。使用某些计数器检查当前工作表中添加的行数。如果工作表已满(计数器 == MAX_ROWS)，则创建一个新工作表并将行计数器重置为 0。