create excel spreadsheet

问题:

Is there any way to create an excel spreadsheets using VBScript? I can create a text file using a FileSystemObject and use any extension I want, but when I try and download this and it gives me the open in excel option a message then appears stating that it is in a different format, and that is what I want to avoid:

set fs=Server.CreateObject("Scripting.FileSystemObject")
set tfile=fs.CreateTextFile(Server.MapPath("xls/streamtest.xls"),true,false)

I know this is pushing text into the file in ASCII format.

Is there 'something' character sequence, specific formatting that I can use to get around all of this?

Thanks

回答1:

The best and fastest way, without using Excel.application on the server, which is really not recommended, is to generate an Excel Xml file. The only feature you won't be able to do is insert a picture.

Unlike using html table, you'll be able to support native data in excel (like date), and you'll be able to format, insert formula, ...

Save your excel as Excel XML and see the generate XML file. This is little bit tricky, but works very well.

回答2:

I agree with the comments above. I have always done this using Response.ContentType and Response.AppendHeader

Response.ContentType = "application/vnd.ms-excel"
Response.AppendHeader "content-disposition", "filename=MySpreadsheet.xls"

回答3:

If Excel is installed and accessible on the server and sFSpec is the full (server mapped, accessible) file spec of the (empty) .xls you want to create, then

 Dim oExcel : Set oExcel = [Server.]CreateObject( "Excel.Application" )
 oExcel.Workbooks.Add.SaveAs sFSpec
 oExcel.Quit

should work. If you can't use "Excel.Application", you may use ADO by opening a Excel connection and execute a suitable CREATE TABLE statement.

ADDED

The low tech approach would be to use an empty .xls stolen from some workstation as a template; but you can create an .xls on the fly:

  Dim sFSpec : sFSpec = resolvePath("..\data\byado.xls")
  If goFS.FileExists(sFSpec) Then goFS.DeleteFile sFSpec
  Dim oXDb : Set oXDb = CreateObject("ADODB.Connection")
  Dim sCS  : sCS      = Join(Array(_
        "Provider=Microsoft.Jet.OLEDB.4.0" _
      , "Data Source=" & sFSpec            _
      , "Extended Properties="""           _
          & Join(Array(     _
                "Excel 8.0" _
              , "HDR=Yes"   _
              , "IMEX=0"    _
            ), ";" )        _
          & """"            _
  ), ";")
  oXDb.Open sCS
  oXDb.Execute "CREATE TABLE [WhatEver] (DontCare INTEGER)"
  oXDb.Close
  If goFS.FileExists(sFSpec) Then WScript.Echo "qed"

(You may have to tinker with the "Excel !Version!"; the "IMEX=0" is important)

回答4:

I answered this on a another question. correct formatting for excel spreadsheet You should be able to use CreateObject("Excel.Application") on your server if office (at least Excel) is installed.

Xls (and the other types of Excel 2007/2010 are binary fiels that you can not easily create like that, you need to use Com objects to create and manipulate them. Here an example from the scripting guys http://blogs.technet.com/b/heyscriptingguy/archive/2005/01/31/how-can-i-make-changes-to-and-then-re-save-an-existing-excel-spreadsheet.aspx

Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 
objExcel.DisplayAlerts = FALSE 

Set objWorkbook = objExcel.Workbooks.Add 
Set objWorksheet = objWorkbook.Worksheets(1) 

objWorksheet.Cells(1, 1).Value = Now 
objWorkbook.SaveAs("C:\Scripts\Test.xls") 
objExcel.Quit 

see http://msdn.microsoft.com/en-us/library/aa223697(v=office.11).aspx for a list of vba functions you can use