Problem with XLSX merging for files >20 MB

Aspose.Cells Product Family
1

Hi,

We are using Aspose.cells version 7.1.1.5 (licensed version) and facing a issue when merging excel files larger than 20 MB. It is working fine if the files are less than this.

I have zipped and attached the class file where we have the logic of merging excel files.

Our requirement is that we have to merge excel files sizing > 100 MB.Our requirement is that we have to merge excel files sizing > 100 MB. Please let us know how we can acheive this.

Thanks & Regards,

Praveen Parripati

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.1.2.6
and let us know your results.

Please try setting the OoxmlSaveOptions.ValidateMergedAreas as true, it will remove invalid
records in your files, let us know if it works.

If the problem still occurs, then please provide us your source files, so that we could test your code.

Please see the following sample code:

C#


Workbook workbook = new Workbook(@“D:\Filetemp\example.xls”);

OoxmlSaveOptions saveOptions = new OoxmlSaveOptions();

saveOptions.ValidateMergedAreas = true;

workbook.Save(@“D:\Filetemp\dest.xlsx”, saveOptions);


3

Hi,

I have tried with the latest dll and still getting the same error. Also i have included the ValidateMergedAreas but doesn't help as i get out of memory exception during copy of child sheets and saving only happens after that. I have included my complete project for your testing. Please note that due to size constraints i have provided only 1 excel file (ASPOSE-ExcelMerge\ExcelMerge\bin\Debug\ExcelFiles), Please do a copy of the same excel file to replicate the issue.

Note: I have removed the license file file the application.

Thanks & Regards,

Praveen Parripati

4

Hi,

Thanks for your project and testing it with the latest version.

We have logged this issue in our database. We will look into it and once there is some update or fix available relating to this issue, we will let you know asap.

This issue has been logged as CELLSNET-40576.

5

Hi,

This is an important functionality in our application and we are using Aspose.Cells only for this feature. There are several people using the application in real time. Quick resolution for this issue is much appreciated.

Thanks & Regards,

Praveen Parripati

6

Hi,

Thanks for your posting.

We can understand your needs.

I have logged your comment in our database against the issue id: CELLSNET-40576

We will get back to you asap.

7

Hi,

Please change your code to reduce the memory now, see attached code.

We are working on reducing the memory but it’s a complex feature, we could not complete it soon.

8

Thanks for the information.

This still doesn't help the situation. I even tried to reduce the memory in the loop after copying each sheet but still getting the out of memory exception.

Regards,

Praveen Parripati

9

Hi,

If you only need to merge the first worksheet, please only load the data of the first sheet. See the attached code.

10

Hi,

If you only need to combine the data, please try to save the file with LightCellsDataProvider.

Please try the following code with the attached code file:

C#


internal static void Merge()
{

string dir = @“D:\FileTemp\ASPOSE-ExcelMerge\ExcelMerge\bin\Debug\ExcelFiles”;

string[] files = Directory.GetFiles(dir);

Workbook parentExcel = new Workbook();

for (int i = 0; i < files.Length; i++)
{

if (Path.GetFileNameWithoutExtension(files[i]) != “MergedExcel”)
{

string childSheetName = Path.GetFileNameWithoutExtension(files[i]);

parentExcel.Worksheets.Add(childSheetName);
}
}

parentExcel.Worksheets.RemoveAt(0);

LightCellsMerge dataProvider = new LightCellsMerge(parentExcel, files);

OoxmlSaveOptions opt = new OoxmlSaveOptions();

opt.LightCellsDataProvider = (dataProvider);

parentExcel.Save(@“D:\FileTemp\dest.xlsx”, opt);

}

11

Yes. We only need to combine data from multiple large excel files.

I have tried the new code provided but still facing the issue. Please find the updated project and let me know if I have missed any thing.

Please note that due to size constraints i have provided only 1 excel file (ASPOSE-ExcelMerge\ExcelMerge\bin\Debug\ExcelFiles), Please do a copy of the same excel file to replicate the issue.

Thanks & Regards,

Praveen Parripati

12

Hi,

We did not find any issue in our develop machine (4G memory). We can work with your original project .

Could you share us your machine info?

13

@KAAP-Orion,
Please set LoadOptions.MemorySetting to load the files :

LoadOptions loadOpt = new LoadOptions(LoadFormat.Auto);            
loadOpt.MemorySetting = MemorySetting.MemoryPreference;
Workbook wb = new Workbook(Constants.sourcePath + "CELLSNET-44055.xls", loadOpt);

Let us know your feedback.