Merge Existing Excel files and more

rudrvij · April 24, 2009, 10:38am

Hello,

I am evaluating 3rd party tools and I want to find out if ASPOSE.cells can do the following:

Merge existing Excel files into a new excel file, on a single worksheet.
Merge existing Excel files into a new excel file, with each in its own worksheet.

Please let me know if this is possible and point me to relevant examples.

Thanks

Vijay

amjad.sahi · April 24, 2009, 12:10pm

Hi,

Thanks for considering Aspose.

rudrvij:

Merge existing Excel files into a new excel file, on a single worksheet.

Merge existing Excel files into a new excel file, with each in its own worksheet.

Well, I think you may utilize Workbook.Combine() and Workbook.Merge() methods for your requirements, check the sample codes below:

1) Combine Workbooks

Note: Currently, for combine operation, the cell data, styles of the second Workbook object can be combined. There are some drawing object which might be lost in this operation.

Workbook wb1 = new Workbook();
wb1.Open("f:\\test\\bk1.xls");

Workbook wb2 = new Workbook();
wb2.Open("f:\\test\\bk2.xls");
wb2.Combine(wb1);
wb2.Save("f:\\test\\outbk2.xls");

2) Copy Workbooks

Note: All the worksheets of the source workbooks are copied to the destination workbook.

Aspose.Cells.Workbook xlOutput = new Aspose.Cells.Workbook();
Aspose.Cells.Workbook xlOutputTemplate = new Workbook();
xlOutputTemplate.Open("f:\\test\\templatebook.xls");
xlOutput.Copy(xlOutputTemplate);
//make changes to xlOutput if required.
//SaveAs the file
xlOutput.Save("f:\\test\\outputmyfile.xls");

Also, if you need to copy some selective worksheet in a workbook to other workbook, you may use Worksheet.Copy method.

Thank you.

rudrvij · April 27, 2009, 3:02pm

Hi Amjad,

I tried the sample codes you sent me but they do not work for my first requirement.

Merge existing Excel files into a new excel file, on a single worksheet.
Merge existing Excel files into a new excel file, with each in its own worksheet.

The "Combine Workbook" example works for my second requirement.

The "Copy Workbooks" example does not work because any time I use copy it overwrites on the existing sheet.

Please let me know if there is a way to do my first requirement.

Thanks

Vijay

amjad.sahi · April 28, 2009, 2:20am

Hi,

rudrvij:

Merge existing Excel files into a new excel file, on a single worksheet.

Well, if you want to put everything in a single worksheet, you have to manually copy data, objects from your existing excel files to the sheet of the new workbook. I think you may try some APIs in this regards, e.g

Cell.StringValue and Cell.PutValue().......get the values in the cells from a sheet and then paste it into new book's sheet.

Cells.CopyRow and Cells.CopyColumn --> copy rows and columns from one sheet to other sheet. check: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/copying-rows-and-columns.html

Fill data into an array and then use Cells.ImportObjectArray to extract the array into the sheet.

Cells.ExportDataTableAsString() and Cells.ImportDataTableAsString() method, see the following example code:

Sample code:

Workbook workbook2 = new Workbook();
workbook2.Open("f:\\test\\test10.xls");
Workbook wb3 = new Workbook();
wb3.Open("f:\\test\\test11.xls");
Workbook workbook1 = new Workbook();

DataTable dt;

//Copy data from the first workbook2 sheets.
for (int i = 0; i < workbook2.Worksheets.Count; i++)
{
dt = workbook2.Worksheets[i].Cells.ExportDataTableAsString(0, 0, workbook2.Worksheets[i].Cells.MaxDataRow + 1, workbook2.Worksheets[i].Cells.MaxDataColumn + 1);
workbook1.Worksheets[0].Cells.ImportDataTable(dt, false, workbook1.Worksheets[0].Cells.MaxDataRow +1, 0);

}

//Copy data from the wb3 sheets.
for (int i = 0; i < wb3.Worksheets.Count; i++)
{
dt = wb3.Worksheets[i].Cells.ExportDataTableAsString(0, 0, wb3.Worksheets[i].Cells.MaxDataRow + 1, wb3.Worksheets[i].Cells.MaxDataColumn + 1);
workbook1.Worksheets[0].Cells.ImportDataTable(dt,false, workbook1.Worksheets[0].Cells.MaxDataRow + 1, 0);

}

workbook1.Save("f:\\test\\outputbook.xls");

Thank you.

rudrvij · April 29, 2009, 10:58am

Hello Amjad,

Thanks for your response. I need to preserve the formatting so I cannot use the Data Table method. But I did try the CopyRows option you specified in the response.

Problems I am having with this is:

Images (Charts) are not getting copied over.
If there are some blank lines they are not getting copied over.

I am providing a sample code and excel file. In the excel file I have an image in the second line and I want to copy this row into the 18 line. I am using the following code.

Workbook wb1 = new Workbook();

wb1.Open("c:\\Temp\\File3.xls");

wb1.Worksheets[0].Cells.CopyRow(wb1.Worksheets[0].Cells, 1, 17);

wb1.Save(path + "result.xls");

Thanks

Vijay

amjad.sahi · April 29, 2009, 1:33pm

Hi Vijay,

Thanks for providing us the template file.

Well, if you select the chart picture in the worksheet, you may see that the chart is expanded upto 7th row, so it is not contained in the 2nd row either. If you select the whole row(2nd row) manually in MS Excel and paste it somewhere in the sheet, the chart won't get copied either. Moreover, we also find an issue with CopyRow method regarding copying images/objects, we will look into it soon.

I think you may try to copy the row first and then copy the source picture to it. See the following sample code:

e.g

Workbook wb1 = new Workbook();

wb1.Open("c:\\Temp\\File3.xls");

wb1.Worksheets[0].Cells.CopyRow(wb1.Worksheets[0].Cells, 1, 17);

Picture source = wb1.Worksheets[0].Pictures[0];

MemoryStream ms = new MemoryStream(source.Data);

//Copy the picture

wb1.Worksheets[0].Pictures.Add(17, source.UpperLeftColumn, ms, source.WidthScale, source.HeightScale);

wb1.Save(path + "result.xls");

For further reference, please check the doc article: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/copy-shapes-between-worksheets.html

Also, I think you may try to create a range based on the cells in the sheet, create another range of cells, copy the first range into the second range.

e.g

// Get the first worksheet in the Excel file

Worksheet worksheet = wb1.Worksheets[0];

// Create a named range of the Cells

Range range1 = worksheet.Cells.CreateRange("A1", "Q7");

// Create another Range

Range range2 = worksheet.Cells.CreateRange("A17", "Q24");

// Copy the range

range2.Copy(range1);

This way everything (data, contents, drawing objects etc.) would get copied. But, it is to be noted here, the width/heights of the source cells in rows/columns are not copied and you have to manually set the width/heights of the destination cells. For reference, check: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/copying-rows-and-columns.html

Thank you.

nausherwan.aslam · May 4, 2009, 8:14am

Hi Vijay,

Thank you for considering Aspose.

Please try the attached latest fix of Aspose.Cells. We have fixed your mentioned issue regarding copying rows.

Thank You & Best Regards,

rudrvij · May 11, 2009, 1:55pm

Thank you for sending the "CopyRow" fix.

But I am having the following issues with Copy Row.

I am sending sample code and 2 sample files. In this code I am copying the first 10 rows(1-10) to 31-40 rows in the same sheet. I am using CopyRow method and if the images are not copied I am copying them manully. To figure out if the image was copied or not I am getting the count of images before and after.

From my understanding I thought that CopyRow does not copy pictures if the picture is over more than one row. In these 2 sample files, the image is over more than one row.

Please run my sample code against the 2 files. But in one case(File3.xls) it does not copy the image and in another case(File4.xls) it copies the image.

Issues with File4.xls:

It copies the image twice (They are on top of each other). In the resulting file if you click on the image in the 32nd row and move it, you will see that there is another image underneath it.
If you are debugging the code, wb1.Worksheets[0].Pictures.Count gives 4 before copying and after the for loop it says the picture count is 15. But there atre only 3 pictures in the reslting file.

So the issues I am facing are:

CopyRow when the image is on more than one row sometimes copies picture and at other times it doesn't. I need a way to figure out if the image was copied or not so that I don't have to copy it manually.
Pictures.Count gives wrong count of images.

Thanks for your help.

Please find my sample code below.

Vijay

Workbook wb1 = new Workbook();

wb1.Open(path + "File3.xls");// Please use both samples

int originalPics = wb1.Worksheets[0].Pictures.Count;

int destinationRow = 30;

for (int i = 0; i < 9; i++)

{

wb1.Worksheets[0].Cells.CopyRow(wb1.Worksheets[0].Cells, i, destinationRow);

Double d = wb1.Worksheets[0].Cells.GetRowHeight(i);

wb1.Worksheets[0].Cells.SetRowHeight(destinationRow, d);

destinationRow++;

}

if (wb1.Worksheets[0].Pictures.Count <= originalPics){

/*

// We need to add the pics

Picture p = wb1.Worksheets[0].Pictures[0];

MemoryStream tempImageStream = new MemoryStream(p.Data);

wb1.Worksheets[0].Pictures.Add(30+p.UpperLeftRow, p.UpperLeftColumn,30+p.LowerRightRow,p.LowerRightColumn, tempImageStream);

*/

}

wb1.Save(path + "result.xls");

nausherwan.aslam · May 12, 2009, 1:57am

Hi,

Thank you for sharing the template files.

We have found your mentioned issues after an initial test. We will fix them and get back to you soon.

Thank You & Best Regards,

amjad.sahi · May 12, 2009, 2:12am

Hi,

Well, we normally follow the MS Excel standards. For File3.xls issue, we do the same as MS Excel. The image in that file is splitted to A2:Q7 and if you manually copy each row individually from 1 to 7 in MS Excel, the image will not copied with rows(you may check it yourself). So, when using your code with File3.xls file, image won't get copied with the rows, I think Aspose.Cells does the same as MS Excel.

For File4.xls issues, we are still looking into the issue(s) you have mentioned and we will get back to you soon.

Thank you.

amjad.sahi · May 12, 2009, 2:47am

Hi,

After further investigation of your file "File4.xls", we come to know that MS Excel can also perform duplications of shapes when copying rows (one by one). For example, In MS Excel, when you copy Row2 and Row3 of Sheet1 in "File4.xls" file individually to some other places, it will copy the picture twice for each copy operation.

Therefore, we are now considering to add CopyRows (new method) for your need, which will resolve your issue and is more reliable than CopyRow method in some cases.

Thank you.

rudrvij · May 12, 2009, 10:17am

Hi Amjad,

Thanks for your quick response.

Can you please tell me a little bit about this new method?

What are the arguments that we can pass to this method?
Can we copy multiple rows using the CopyRows method?
Does it preserve the styles and shapes when copying?

Please let me know.

Thanks

Vijay

amjad.sahi · May 12, 2009, 12:46pm

Hi Vijay,

rudrvij:

What are the arguments that we can pass to this method?

We will provide the argument list for the method soon.

rudrvij:

Can we copy multiple rows using the CopyRows method?

Yes, you can.

rudrvij:

Does it preserve the styles and shapes when copying?

Yes, it will preserve the formatting and shapes.

Thank you.

nausherwan.aslam · May 13, 2009, 9:29am

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have supported to copy rows. Please see the following sample code,

Workbook wb1 = new Workbook();

wb1.Open(@"C:\File4.xls");// Please use both samples

int originalPics = wb1.Worksheets[0].Pictures.Count;

int destinationRow = 30;

wb1.Worksheets[0].Cells.CopyRows(wb1.Worksheets[0].Cells, 0, destinationRow,10);

Assert.AreEqual(wb1.Worksheets[0].Pictures.Count, 5);

wb1.Save((@"C:\dest.xls");

Thank You & Best Regards,

rudrvij · May 13, 2009, 10:51am

Hi,

Thanks for adding the CopyRows method. This will cover my needs.

I have one followup question. In File4.xls, there is only one image but why the picture count is 4 in this line?

int originalPics = wb1.Worksheets[0].Pictures.Count;

Thanks

Vijay

amjad.sahi · May 14, 2009, 1:01am

Hi Vijay,

Well, there are 3 more shapes (whose heights are Zero actually) placed and are not visible in your template file. Please check it with the following code for confirmation.

Workbook workbook = new Workbook();

workbook.Open(@"F:\FileTemp\File4.xls");

Pictures pics = workbook.Worksheets[0].Pictures;

Console.WriteLine(pics.Count);

for (int i = 0; i < pics.Count; i++)

{

Console.WriteLine(pics[i].Width + ":" + pics[i].Height);

}

Thank you.

aspose.notifier · August 30, 2009, 2:04am

The issues you have found earlier (filed as 8740) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

Note: Just for your knowledge. In the new release v4.8.0, we have merged Aspose.Grid suite to Aspose.Cells for .NET msi installer as Aspose.Grid would be no longer offered as a separate product now. You need to install the installer (uninstall older one first if you have) and use only Aspose.Cells.dll library in your project for your need. You may also take advantage using Aspose.Cells Grid suite though.