Is there a way to programmatically concatenate excel files?

Question

The scenario: we have a web system that automatically generates office 2003 excel files "on the fly" (using the 2003 XML file format, not the binary format.) These files do get kept on the web server for various things.

Now, we're in a situation where the client would really like us to take the xls files generated by this process and glue them together into a single sheet of one big file. (largely so that they can press "print" only once.)

I assume the .net framework must have some way to do this (and things like this) but I can't seem to tease what I need out of MSDN.

For the record: .net 2.0, using VB.net and ASP.net. Excel can be installed on the server if needed, but something that opens excel in the background on every web user hit might not scale so well. ;)

Answer 1

so your user would like to append the latest file generated every time? or just glue" them together when they make a request?

EDIT:

what kind of data is in the excel file? can it be temporarily converted to a simpler format such as csv?

even if you cant, you could create a new file and as you loop through every line in every file you could write it to the new file. The problem here is that if you have any functions in the cells those would be lost as you are basically only copying the data.

Other than that, you will have to use the Excel Interop. And although i know its a pain, and it is really not MS supported for automation, we have managed to create very reliable automation system as a web service.

Answer 2

I don't know what the Excel files consist of, but would it be possible to at least start out with a simpler format that Excel can read? IE, start with a CSV file, join them together and then convert that into the Excel format.

It seems to me that it would be easier to convert to the Excel format as late as possible in the process.

Answer 3

Pretty sure .Net framework doesn't have what you're looking for and you'll have to create it manually. You can does this via the Excel interop:

Microsoft.Office.Interop.Excel from MSDN

Edit: Just so you have something to look forward to, it can be a pain to work with! :X

Answer 4

1) Open each XML file.
2) Grab the Worksheet XML for each Worksheet.
3) Insert all the Worksheet XML into a new "wrapper" Excel 2003 XML document.
4) Deliver the document back from the web request.

Make sure the Worksheets have unique names.

I would avoid the 2007 XML format since you are working with the easier to handle 2003 format.

Answer 5

Perhaps ADO would suit? Here is a script example.

strXLToOpen = "C:\Docs\ltd.xls" 
strXLToImport = "C:\Docs\ltd2.xls"

Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "MSDASQL"
    .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & strXLToOpen & "; ReadOnly=False;"
    .Open
End With

strSQL = "INSERT INTO [Sheet2$] (H1, H2) " _
& "SELECT H1, H2 FROM [Sheet2$] IN '' " _
& "'Excel 8.0;database=" & strXLToImport & "';"       

cn.Execute strSQL

Further information: http://support.microsoft.com/kb/257819/EN-US/

http://support.microsoft.com/kb/278973

Edit re comment.

Would this suit? It is from: http://www.codemaker.co.uk/it/tips/ado_conn.htm#ODBCDriverForExcel

cn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "DBQ=" & strXLToOpen & "; ReadOnly=False;" & _
           "DefaultDir=c:\somepath"

You can also use Jet drivers with Excel.