Creating an Excel Spreadsheet from code on a computer that does nothave Excel installed

[zacks]

I have an application that can read an Excel spreadsheet on a computer
that doesn't have Excel installed on using the Jet ODBC 4.0 driver.

I know need to modify the application to also be able to create a new
Excel spreadsheet and write data to it on the same computer, a
computer that does not have Excel, or even Office installed.

I have done some research on this and it appears it cannot be done.

Can anyone verify that or better yet, point me to a link that explains
how to do it?

 

[Trevor Benedict]

would you consider Excel XML.

http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx

Regards,

Trevor Benedict

MCSD

 

[Michel Posseth [MCP]]

Well you can write data files with a xls extension that will inmediatly be
converted by Excel when openned ( the user will not notice this )
this works for me ( office 2003 and higher )

if this might work for you i can show an example



hth

Michel

 

[zacks]

Well you can write data files with a xls extension that will inmediatly be
converted by Excel when openned  ( the user will not notice this )
this works for me ( office 2003 and higher  )

if this might work for you i can show an example

I would like to see that please.

 

[Michel Posseth [MCP]]

Well one method i personally like an use a lot is this one

Imports System.Web

Imports System.IO


Private Sub CreateXLSFromDt(ByVal ds As DataSet, ByVal tablename As String,
ByVal location As String)
Dim grid As New System.Web.UI.WebControls.DataGrid

grid.HeaderStyle.Font.Bold = True

grid.DataSource = ds

grid.DataMember = tablename

grid.DataBind()

'// render the DataGrid control to a file

Using sw As New StreamWriter(location)

Using hw As New UI.HtmlTextWriter(sw)

grid.RenderControl(hw)

End Using

End Using

End Sub

usage

CreateXLSFromDt(dataset, TableNameInDataset , fullpath of file to create )

so in my situation
CreateXLSFromDt(dsBl, "tblBlok", "C:\testMp.xls") '


you just throw in a dataset and it will render the table data to a nicely
formated ( table with headers ) Excel sheet


Hope this works for you to

Michel




<[email protected]> schreef in bericht

Well you can write data files with a xls extension that will inmediatly be
converted by Excel when openned ( the user will not notice this )
this works for me ( office 2003 and higher )

if this might work for you i can show an example

I would like to see that please.

 

[Kevin S Gallagher]

The following code is not mine and long since forgot where I found it. The
object TheGrid is a populated DataGridView with the data you want to place
into the Excel file. There are countless ways to approach the file i/o which
is up to you. If you need to get fancy surf to MSDN and search for Excel XML
where the tags are documented (no real samples).


Public Sub ExportGridToExcel(ByVal FileName As String)

Try
Dim fs As New IO.StreamWriter(FileName, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook
xmlns:ss=""urn:schemas-microsoft-comffice:spreadsheet"">")
fs.WriteLine(" <ss:Styles>")
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
fs.WriteLine(" <ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")

For x As Integer = 0 To TheGrid.Columns.Count - 1
fs.WriteLine(" <ss:Column ss:Width=""{0}""/>",
TheGrid.Columns.Item(x).Width)
Next

fs.WriteLine(" <ss:Row ss:StyleID=""1"">")

For i As Integer = 0 To TheGrid.Columns.Count - 1
fs.WriteLine(" <ss:Cell>")
fs.WriteLine(String.Format(" <ssata
ss:Type=""String"">{0}</ssata>", TheGrid.Columns.Item(i).HeaderText))
fs.WriteLine(" </ss:Cell>")
Next
fs.WriteLine(" </ss:Row>")
For intRow As Integer = 0 To TheGrid.RowCount - 2
fs.WriteLine(String.Format(" <ss:Row ss:Height
=""{0}"">", TheGrid.Rows(intRow).Height))
For intCol As Integer = 0 To TheGrid.Columns.Count - 1
fs.WriteLine(" <ss:Cell>")
fs.WriteLine(String.Format(" <ssata
ss:Type=""String"">{0}</ssata>", TheGrid.Item(intCol,
intRow).Value.ToString))
fs.WriteLine(" </ss:Cell>")
Next
fs.WriteLine(" </ss:Row>")
Next
fs.WriteLine(" </ss:Table>")
fs.WriteLine(" </ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
Catch ex As Exception
' For demoing I am simply telling you we had a problem, in a real
application
' we at least remove the MsgBox and uncomment the next line. What
should also
' be considered is not using --> Throw new exception... because
now the exception
' points to the line with that statement rather then the original
problem.
'
'
MsgBox(ex.Message)
'Throw
End Try

OpenExcelFile(FileName)
Application.DoEvents()
End Sub
''' <summary>
''' Opens a file with the Association under Shell in the system registry
''' </summary>
''' <param name="FileName">Opens file by shell association</param>
''' <remarks></remarks>
Private Sub OpenExcelFile(ByVal FileName As String)
If System.IO.File.Exists(FileName) Then
Process.Start(FileName)
End If
End Sub