Hello!
When I copy worksheets from one workbook to another workbook, the destination workbook grows every time it has been saved.
Background: Users can open workbooks that come from a database through their webbrowser. Data will be edited and the workbook will be saved back to the database. But not the edited workbook will be saved to the database. The “original” workbook will be loaded from the database and all sheets of the edited workbook will be copied to the original workbook. Then the original workbook will be saved.
Every time, that is done, the file grows. It has about 400 kilobytes first. After saving it the first time by copying all sheets form the edited workbook to the original workbook, it grows about 4 kilobytes. That is not a big problem, but the size increases more and more. After saving it two times it has about 600 kb and after saving it 8 times, it has more than 2 megabytes.
This is my code:
public Workbook CopySheets(Workbook pWbSrc, Workbook pWbDest, ArrayList pSheetNames)
{
Workbook wbResult = pWbDest;
String result = “”;
MemoryStream ms = null;
try
{
ms = pWbSrc.SaveToStream();
ISG.Tracing.Trace.Current.Message("Size of SRC workbook before copy: " + ms.Length);
pWbSrc.Open(ms);
ms = wbResult.SaveToStream();
ISG.Tracing.Trace.Current.Message("Size of DST workbook before copy: " + ms.Length);
wbResult.Open(ms);
foreach (Worksheet srcSheet in pWbSrc.Worksheets)
{
if (pSheetNames.Contains(srcSheet.Name))
{
Worksheet dstSheet = wbResult.Worksheets[srcSheet.Name];
dstSheet.Copy(srcSheet);
result += srcSheet.Name + ", “;
}
}
if (result.Length > 0)
{
result = result.Substring(0, result.Length - 2);
ISG.Tracing.Trace.Current.Message(result + " copied”);
}
else
{
ISG.Tracing.Trace.Current.Message(“0 sheets copied”);
}
}
catch (Exception ex)
{
ISG.Tracing.Trace.Current.Error(ex.ToString());
}
ms = wbResult.SaveToStream();
ISG.Tracing.Trace.Current.Message("Size of workbook after copy: " + ms.Length);
wbResult.Open(ms);
return (wbResult);
}
Here is a typical output for that:
Size of SRC workbook before copy: 420352
Size of DST workbook before copy: 540160
Size of workbook after copy: 602624
One fascinating fact is, that the workbook has only been loaded from the server and saved back without changing anything. After that, the workbook shrinks from about 540K to about 420K. After copying all sheets from SRC to DST, the DST-Workbook grows to about 602K. If this will be done several times, the file gets really big without seeing any changes when opening the file with MS Excel.
I am using Aspose.Cells 4.3 for .NET. I know, it is not the newest version but the only one I have and the newest one, my lisence is valid for.
I hope, you can help regarding this problem. The XLS-File has nothing special in it. Some sheets, some data, no images but some VBA-code. I cannot upload it because it is owned by one of our customers.
-> EDIT: I just found out, that Aspose.Cells 4.5.1 is the newest version, my lisence is valid for. So, I tried this version. But now I get “Invalid Formula” when I save my workbook. It is the same workbook as before. The workbook still grows when sheets are copied.
best regards and thanks in advance,
Thomas.
Hi,
As we are not sure if there is some problem with the older version regarding your issue and we cannot change/update any previous or older version as this is against our company policy. We only evaluate issues based on the latest versions.
Please try our latest version Asponse.Cells for .Net v5.1.0. You may comment the licensing code to try the latest version.
If you still experience the same issue, then please provide us a demo console application that should help us to reproduce the issue on our end.
Thanks for your understanding!