Error while combining workbooks (Critical)

Aspose.Cells Product Family
1

I have some 20+ workbooks with only one sheet (each 17 M size) . I am tyring to combine the worksheet in these 20+ worbooks into one workbook as separare sheets. The first 3 or 4 worksheets worked fine, after that the system started slowing down, started consuming more and more memory and finally ended with Out of memory Exception. I have 2G RAM on my machine.

Here is my sample code

public void Merge(string[] files)
{
this.book.Worksheets[0].Name = "Page 1";

for (int i = 1; i < files.Length; i++)
{
if (files[i] != this.fileName)
{
LoadDataOption ldo = new LoadDataOption();
ldo.SheetIndexes = new int[] { 0 };

LoadOptions lo = new LoadOptions(LoadFormat.Excel97To2003);
lo.LoadDataOptions = ldo;

Workbook mergeBook = new Workbook(files[i], lo);

mergeBook.Worksheets[0].Name = string.Format("Page {0}", i);

this.book.Combine(mergeBook);
}
}

this.book.Save(files[0]);
this.book = null;
}

Thank you.

Tomy

2

Hi Tomy,

Thank you for considering Aspose.
We are looking in to this issue. It is helpfull if you please forward us your sample application & file having size 17MB. This will help us finding the issue.

Thanks,

3

Hi Tomy,

Update version of Aspose.Cells.dll is attached. Please use this version.
Code below is the work around. It is a temporary solution; we are in process of fixing this issue.

Code snippet:

string[] files = new string[2];
files[0] = @"F:\FileTemp\6249.xls";
files[1] = @"F:\FileTemp\A0071 - Class I Operations - TO005 CDRL - Afghanistan - 20100803.xls";
string cacheFile = @"F:\FileTemp\temp.txt";
string dest = @"F:\FileTemp\dest.xls";
CellsHelper.MergeFiles(files,cacheFile,dest);

Thanks,

4

Thank you for the quick respose. It worked for most part. It successfully combined the sheets.

But on the destination worksheet, some of the formatting is incorrect. For example, The column width and row height on the destination sheet is different from what is in the source sheet. Also the image on the source sheet is missing from the target sheet.

Other than these two issues, everything look great.

Thank you.

Tomy

5

Would it also be possible to set the sheet names with user specified names rather than Sheet1, Sheet2 etc.

I am trying load the entitre sheet after merging and updating sheetnames, it is throwing a memory exception when saving the sheet.

Is it possible to pass the sheet name array to the methods as well.

Thank you,

Tomy

6

Hi,

Well, I am afraid renaming sheets while merging is not possible but you may do it using Worksheet.Name attribute later. I have tested with the following sample codes with my custom excel files and it works fine.

Sample code:
string[] files = new string[2];
files[0] = @“e:\test\Book1.xls”;
files[1] = @“e:\test\Book2.xls”;
string cacheFile = @“e:\test\temp.txt”;
string dest = @“e:\test\dest.xls”;
CellsHelper.MergeFiles(files, cacheFile, dest);
Workbook workbook = new Workbook(“e:\test\dest.xls”);
int i = 1;
foreach (Worksheet sheet in workbook.Worksheets)
{

sheet.Name = “My_Custom_Sheet_” + i.ToString();
i++;

}

workbook.Save(“e:\test\dest2.xls”);


If you find the issue, kindly do post your template files here, we will check it soon.

Thank you.

7

Thanks for the response. The new solution seems like work fine.


But some of my sheets have images on them. Looks like the merge is not moving the images to the target sheet.

Can you please look into it.

Thank you
Tomy

8

Hi Tomy,

<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<w:DoNotOptimizeForBrowser/>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

<![endif]–>

We only support to combine the data and styles in CellsHelper.MergeFiles method. We may look into it. If we have some update, we will let you know about it.


Thank you.