I want create a xls workbook that the sheets are greater than 64 and every sheet have comment, but it not work at the version 3.12 and later version.

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

I want create a xls workbook that the sheets are greater than 64 and every sheet have comment, but it not work at the version 3.12 and later version.

Zouchengguang (Ben)

 

Hello All

       I want create a xls workbook that the sheets number are greater than 64 and every sheet has comment, it work at the version 3.10, but it not work at 3.12 and later version. The following code is my test code. The first 63 sheets is ok, but when the sheets are greater than 64 , it throw the exception “The exception is Cannot add more than 65535 shapes. ”

 

import java.io.FileOutputStream;
import
java.io.IOException;

import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import
org.apache.poi.hssf.usermodel.HSSFComment;
import
org.apache.poi.hssf.usermodel.HSSFPatriarch;
import
org.apache.poi.hssf.usermodel.HSSFRichTextString;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class
CellComments {

 
public static void main(String[] args) {
    HSSFWorkbook wb =
new HSSFWorkbook();
    for
(int i = 0; i < 256; i++) {
      HSSFSheet sheet = wb.createSheet(
"Cell comments in POI HSSF" + (i + 1));
     
// Create the drawing patriarch. This is the top level container for all shapes including cell comments.
     
HSSFPatriarch patr = sheet.createDrawingPatriarch();
      try
{
       
//create a cell in row 3
       
HSSFCell cell = sheet.createRow(0).createCell(0);
       
cell.setCellValue(new HSSFRichTextString("Hello, World"));
       
//anchor defines size and position of the comment in worksheet
       
HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 11, (short) 6, 4));
       
// set text in the comment
       
comment.setString(new HSSFRichTextString("We can set comments in POI" + 0));
       
//set comment author.
        //you can see it in the status bar when moving mouse over the commented cell
       
comment.setAuthor("Apache Software Foundation");
       
// The first way to assign comment to a cell is via HSSFCell.setCellComment method
       
cell.setCellComment(comment);
     
} catch (Exception e) {
        System.
out.printf("Add comment fail at sheet%d. The exception is %s%n", i+1, e.getMessage());
     
}
    }
   
try (FileOutputStream out = new FileOutputStream("poi_comment.xls")) {
      wb.write(out)
;
   
} catch (IOException e) {
      e.printStackTrace()
;
   
}
  }
}

 

   I have checked the bug list. At the 3.12 version,  to fix the bug 56380, added the following code. The bug of 56380 is fixed, but when the sheet number is greater than 64, the sharpId will greater than 65535.

 

 

Reply | Threaded
Open this post in threaded view
|

RE: I want create a xls workbook that the sheets are greater than 64 and every sheet have comment, but it not work at the version 3.12 and later version.

Murphy, Mark
Please Open an issue at https://bz.apache.org/bugzilla/buglist.cgi?product=POI

From: Zouchengguang (Ben) [mailto:[hidden email]]
Sent: Thursday, April 06, 2017 2:28 AM
To: [hidden email]
Subject: I want create a xls workbook that the sheets are greater than 64 and every sheet have comment, but it not work at the version 3.12 and later version.


Hello All
       I want create a xls workbook that the sheets number are greater than 64 and every sheet has comment, it work at the version 3.10, but it not work at 3.12 and later version. The following code is my test code. The first 63 sheets is ok, but when the sheets are greater than 64 , it throw the exception "The exception is Cannot add more than 65535 shapes. "

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class CellComments {

  public static void main(String[] args) {
    HSSFWorkbook wb = new HSSFWorkbook();
    for (int i = 0; i < 256; i++) {
      HSSFSheet sheet = wb.createSheet("Cell comments in POI HSSF" + (i + 1));
      // Create the drawing patriarch. This is the top level container for all shapes including cell comments.
      HSSFPatriarch patr = sheet.createDrawingPatriarch();
      try {
        //create a cell in row 3
        HSSFCell cell = sheet.createRow(0).createCell(0);
        cell.setCellValue(new HSSFRichTextString("Hello, World"));
        //anchor defines size and position of the comment in worksheet
        HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1,  1, (short) 6, 4));
        // set text in the comment
        comment.setString(new HSSFRichTextString("We can set comments in POI" + 0));
        //set comment author.
        //you can see it in the status bar when moving mouse over the commented cell
        comment.setAuthor("Apache Software Foundation");
        // The first way to assign comment to a cell is via HSSFCell.setCellComment method
        cell.setCellComment(comment);
      } catch (Exception e) {
        System.out.printf("Add comment fail at sheet%d. The exception is %s%n", i+1, e.getMessage());
      }
    }
    try (FileOutputStream out = new FileOutputStream("poi_comment.xls")) {
      wb.write(out);
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
}

   I have checked the bug list. At the 3.12 version,  to fix the bug 56380, added the following code. The bug of 56380 is fixed, but when the sheet number is greater than 64, the sharpId will greater than 65535.
[http://hi3ms-image.huawei.com/hi/showimage-1430757821-49904-d36a984ffb4f0d897adedc492147c1f9.jpg]


[http://hi3ms-image.huawei.com/hi/showimage-1430757965-49904-a4e3bb6a39f32c188812741600a33bae.jpg]