How can I clone a Workbook?

How can I clone a Workbook?

Posted by: alexander.heim on 2 June 2022, 12:09 am EST

• 

  alexander.heim

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 2 June 2022, 12:09 am EST

  Is it possible, to copy / clone a Workbook object in-memory? My use case is, that I have a template Excel file, which must be filled with data from another system. This template workbook can be pretty complex, so it can take a few seconds to read the template from the file system. When I have many files to process, the code always reads the same template, again and again. I think, it might be a lot faster, if the file is read / parsed only once, and the resulting workbook is then just cloned instead of loaded as *.xlsx(m) file.

  I know I can copy worksheets from one workbook to another. But I have found nothing, that copies the entire workbook, including the unparsed, but preserved information such as OLE objects, VBA macros and custom XML. Is there any way to achieve that?

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 5 June 2022, 6:29 pm EST

  Hello,

  We are discussing this with the development team.
  
  We will let you know as soon as we get any update on this from their end.
  
  [Internal Tracking ID: DOCXLS-6038]

  Regards,
  
  Prabhat Sharma.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 5 June 2022, 9:58 pm EST

  Hello,

  As per the developers, there’s no such method for cloning a workbook while they came up with some point that may help:

  Workbook wb = new Workbook();
  wb.getActiveSheet().getRange("A1").setValue(1);
  OutputStream os = new ByteArrayOutputStream();
  
  wb.save(os, SaveFileFormat.Xlsx);
  
  // save to byte array that could be reused.
  byte[] bytes = ((ByteArrayOutputStream) os).toByteArray();
  
  Workbook w1 = new Workbook();
  w1.open(new ByteArrayInputStream(bytes));
  w1.save("F:\\1.xlsx");
  
  Workbook w2 = new Workbook();
  w2.open(new ByteArrayInputStream(bytes));
  w2.save("F:\\2.xlsx");
  

  it can take a few seconds to read the template from the file system.

  If you still face any further issues then please provide us with the file so that we can see if there’s some performance optimization we can do?

  Regards,
  
  Prabhat Sharma.

• 

  alexander.heim

  • Post Options:
  • Link
    Copy

  Posted 7 June 2022, 8:01 pm EST

  Thanks for your detailed response. I understand the idea: use a byte-array instead of the file system. I have tried the solution for one of my template files. This is what I get:

  • "

  • “Read from filesystem: Average 1.435ms”

  • “Read from byte array: Average 1.037ms”

  "

  (Average over 10 repeated loads, 1st round excluded, virus scanner disabled)

  So for this file, it’s really better by ~25%. It’s not bad - but I would be glad to have it cloned even faster than that. I have a scenario where the template must be loaded ~ 500 to 1000 times in a row. A 1-second improvement for each, would result in ~10 - 15min overall speed-up.

  I am afraid, I cannot post the file in this public forum, because it’s a customer’s internal file, not intended to be released. If you like to check, if further optimization is possible, I will provide it in a support ticket.

  Thanks,
  
  Alexander

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 8 June 2022, 4:50 pm EST

  Hello Alexander,

  Your case has been replied on the support case CAS-31884-M6N3K4.
  
  You can find it in your My Support section:
  
  https://www.grapecity.com/my-account/my-support

  You can also find it in the below-given link:
  
  https://www.grapecity.com/my-account/my-support/case/d27b123f-b5e7-ec11-bb3c-000d3a55390c

  Please continue the thread there only to avoid confusion.

  Regards,
  
  Prabhat Sharma.