2

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. ;)

Improve this question
4
  • The XML format is 2007 (but you can open the XML files in 2003 after using the 2007 compatibility pack). Commented Dec 3, 2008 at 23:29
  • Actually, 2003 had it's own XML format. Believe me, as was surprised as anyone to learn that. Commented Dec 3, 2008 at 23:31
  • This isn't an answer, but I'm curious why the excel files were created in the first place? If it's just a report that the user views online or prints, why not use HTML? Commented Dec 3, 2008 at 23:42
  • Well... the short answer is that the users download them and actually edit them in excel - sometimes. And, this process got written some years ago when they thought they'd only need one file / page at a time. Now they're looking for a way to print / download 20 pages at once. Commented Dec 3, 2008 at 23:47

5 Answers 5

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Sometimes you can't see the forest, when all you see is trees.
1

Perhaps ADO would suit? Here is a script example.

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

Set cn = CreateObject("ADODB.Connection")
''EDIT I not that MSDASQL is deprecated by Microsoft, so 
''Please see below.
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.

EDIT re Comment #2

I have tested this connection string with .Net, and it seems to work:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Docs\Test.xls;" & _
"Mode=ReadWrite;Extended Properties=""Excel 8.0;HDR=No"""
Improve this answer

1 Comment

That's pretty spectacular, but those connection strings don't work under .net 2.0 or with an excel 2003 xml file. Any chance of an ado.net version?
1

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

Improve this answer

Comments

1

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.

Improve this answer

Comments

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.

Improve this answer

1 Comment

Essentially, when another user hits another button, the process would take all the xls files in the directory and concatenate them into a new file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.