Thanks mudassarkhan, your articles are really helpful to duplicate data between spreadsheets. But I'm sorry that I didn't make my question clear: what i want to copy between sheets is not only data, but also including images and charts .
I have got some achievements using Interop by studing these refrences in MSDN:
I must say Worksheets.Copy() is really a good way to meet my requirement because it does copies objects(including data, images and charts) from one works sheet to another.
However, I found that some style is gone or wrong, for example, the background color is gone and some font color is changed in the destination excel file.
So does any guy know how to keep the original styles of a worksheet/workbook when they are being copied to another place?
Try Worksheet.Move() function.It preserves all format and styles with position.
Don't call WorkBook.Save() or Workbook.Close() after calling Move() function for the source workbook because it automatically closes the source workbook without any changes.
Thanks,
Manish
Please 'Mark As Answer', if one or multiple posts helped you in your problem so that it might be useful for others.
I tried with WorkSheet.Move() function to check the behavior for the case but it is resulting the same as WorkSheet.Copy().Ideally Move() should completely move sheets from one workbook to another.If you try it manually on excel files and record a macro
it displays Move() function and work properly but from C# the same behavior is not working.
I found a article where stated that Excel maintains a clipboard while doing all operation on windows but C# doesn't consider any clipboard.
I am still finding an appropriate way to do this using C#.Maybe some experts can help us on this.
Thanks,
Manish
Please 'Mark As Answer', if one or multiple posts helped you in your problem so that it might be useful for others.
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
au_rossli
Member
2 Points
19 Posts
How to merge sheets from multiple excel files into one .xls file?
Mar 05, 2009 05:23 AM|LINK
I have several excel spreadsheet fils, each of them contains multiple work sheets that may storing images or data.
How could I merge all of these work sheets to just one .xls file and keep their original layout and style using asp.net?
Thanks in advance.
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: How to merge sheets from multiple excel files into one .xls file?
Mar 05, 2009 06:14 AM|LINK
you can readand write data using ADO.Net refer my articles
http://www.aspsnippets.com/post/2009/02/04/Read-Excel-using-ADONet.aspx
http://www.aspsnippets.com/post/2009/01/31/Export-Data-To-Excel-using-ADONet.aspx
Contact me
au_rossli
Member
2 Points
19 Posts
Re: How to merge sheets from multiple excel files into one .xls file?
Mar 06, 2009 12:18 AM|LINK
I have got some achievements using Interop by studing these refrences in MSDN:
http://msdn.microsoft.com/en-us/library/ms173186(VS.80).aspx
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._workbook.saveas(VS.80).aspx
http://msdn.microsoft.com/fr-fr/library/microsoft.office.interop.excel.workbooks.open(VS.80).aspx
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheets.copy(VS.80).aspx
I must say Worksheets.Copy() is really a good way to meet my requirement because it does copies objects(including data, images and charts) from one works sheet to another. However, I found that some style is gone or wrong, for example, the background color is gone and some font color is changed in the destination excel file.
So does any guy know how to keep the original styles of a worksheet/workbook when they are being copied to another place?
Thanks a lot!
singhmanish_...
Participant
894 Points
167 Posts
Re: How to merge sheets from multiple excel files into one .xls file?
Nov 14, 2009 06:03 AM|LINK
Try Worksheet.Move() function.It preserves all format and styles with position.
Don't call WorkBook.Save() or Workbook.Close() after calling Move() function for the source workbook because it automatically closes the source workbook without any changes.
Manish
Please 'Mark As Answer', if one or multiple posts helped you in your problem so that it might be useful for others.
minguva
Member
4 Points
2 Posts
Re: How to merge sheets from multiple excel files into one .xls file?
Dec 29, 2009 07:18 PM|LINK
Can you please provide me some example. Just want to reconfirm... We are coping from two diffrent Excel books.../
singhmanish_...
Participant
894 Points
167 Posts
Re: How to merge sheets from multiple excel files into one .xls file?
Dec 30, 2009 06:49 AM|LINK
I tried with WorkSheet.Move() function to check the behavior for the case but it is resulting the same as WorkSheet.Copy().Ideally Move() should completely move sheets from one workbook to another.If you try it manually on excel files and record a macro it displays Move() function and work properly but from C# the same behavior is not working.
I found a article where stated that Excel maintains a clipboard while doing all operation on windows but C# doesn't consider any clipboard.
I am still finding an appropriate way to do this using C#.Maybe some experts can help us on this.
Manish
Please 'Mark As Answer', if one or multiple posts helped you in your problem so that it might be useful for others.
TATWORTH
All-Star
72415 Points
14017 Posts
MVP
Re: How to merge sheets from multiple excel files into one .xls file?
Jan 01, 2010 05:50 PM|LINK
This is an ASP.NET forum, for Microsoft Office questions, please ask at:
Microsoft Office
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
jeeshenlee
Member
22 Points
29 Posts
Re: How to merge sheets from multiple excel files into one .xls file?
Aug 29, 2011 02:07 AM|LINK
I wrote a simple Excel VBA script that merge multiple excel files into one. Here's the link
http://jeeshenlee.wordpress.com/2010/09/23/jmc-excel-join-merge-combine-multiple-excel-sheets-or-excel-workbooks/
Thanks.
tarwara99
Member
12 Points
6 Posts
Re: How to merge sheets from multiple excel files into one .xls file?
Apr 20, 2013 11:37 AM|LINK
Find a readymade macro at:
http://www.tarwara.info/blog/merge-excel-sheets-2007-2010-2003/
It can merge excel 2010, 07 and 03 files into one file. Just two shortcut keys and it will merge all your sheets.