Save an Excel from to client using Asp.Net

[Jae]

I'm writing a web application that exports and imports excel files.
The application gets a list of users and their info and displays it in
a datagrid .The user then selects to save the file as a tab delimited
file or an excel file. The application then saves the file in the
correct format.
The flip side is for the user to import/upload the file to the server
The application must be able to import the excel file and read the
contents.
I have searched here and kB 316934 How to Use Ado.Net to Retrieve and
Modify Records. That example assumes permissions on the client
machinme which I will not have

Here is a code snippet of the excel export :
' Set the content type to Excel.
Response.Conten tType = "applicatio n/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charse t = ""
' Turn off the view state.
Me.EnableViewSt ate = False

Dim tw As New System.IO.Strin gWriter()
Dim hw As New System.Web.UI.H tmlTextWriter(t w)
' Get the HTML for the control.
DataGrid1.Rende rControl(hw)
' Write the HTML back to the browser.
Dim strFileName As String = "Roster_Export_ " &
m_objCourse.Cou rseKey.ToString .Replace("/", "_")

Response.Clear( )
Response.Buffer = True
Response.Expire s = 0
' Change the HTTP header to reflect that an image is being passed.
strFileName = strFileName & ".xls"
Response.AddHea der("Content-Disposition", "attachment ; filename=" &
strFileName)

Response.Flush( )
Response.Write( tw.ToString())
' End the response.
Response.End()
Here is my import code :

Dim _files As HttpFileCollect ion = HttpContext.Cur rent.Request.Fi les
Dim _message As System.Text.Str ingBuilder = New
System.Text.Str ingBuilder("Fil e Uploaded:<br>")
Dim sConnectionStri ng As String
Try
Dim _iFile As Integer
For _iFile = 0 To _files.Count - 1
Dim _postedFile As HttpPostedFile = _files(_iFile)
Dim _fileName As String
If _postedFile.Fil eName <> "" Then
_fileName = System.IO.Path. GetFullPath(_po stedFile.FileNa me)
' Create connection string variable. Modify the "Data Source"
parameter as
' appropriate for your environment.
'Dim sConnectionStri ng As String = BuildConnection String(_fileNam e)

sConnectionStri ng = "Provider=Micro soft.Jet.OLEDB. 4.0;"
sConnectionStri ng += "Data Source=" & _fileName & ";"
sConnectionStri ng += "Extended Properties=""Ex cel 8.0;HDR=YES;"""
' Create the connection object by using the preceding connection
string.
Dim objConn As New OleDbConnection (sConnectionStr ing)
' Open connection with the database.
objConn.Open()

' The code to follow uses a SQL SELECT command to display the data
from the worksheet.
' Create new OleDbCommand to return data from worksheet.
'Dim objCmdSelect As New OleDbCommand("S ELECT * FROM [Week2$] ",
objConn)
Dim objCmdSelect As New OleDbCommand("S ELECT * FROM [RosterExport$]
", objConn)
' Create new OleDbDataAdapte r that is used to build a DataSet
' based on the preceding SQL SELECT statement.

Dim objAdapter1 As New OleDbDataAdapte r()
' Pass the Select command to the adapter.

objAdapter1.Sel ectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.

Dim objDataset1 As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter1.Fil l(objDataset1, "XLData")

This will save the file but requires the user to save the file as an
excel file. A step I do not wish to take. A test of the application is
to do an export and then do an import (same file) with no hiccup.
Currently the file has to be opened and saved to an actual excel for
it is really an html file with an xls extension.

Any help would be greatly appreciated

JAE

[Greg Burns]

This maybe helpful:

Export to Excel

http://www.aspnetpro.com/features/20...200310so_f.asp

Requires an asp.netPRO subscription.

Greg
"Jae" <ak*********@ea rthlink.net> wrote in message
news:22******** *************** ***@posting.goo gle.com...

I'm writing a web application that exports and imports excel files.
The application gets a list of users and their info and displays it in
a datagrid .The user then selects to save the file as a tab delimited
file or an excel file. The application then saves the file in the
correct format.
The flip side is for the user to import/upload the file to the server
The application must be able to import the excel file and read the
contents.
I have searched here and kB 316934 How to Use Ado.Net to Retrieve and
Modify Records. That example assumes permissions on the client
machinme which I will not have

Here is a code snippet of the excel export :
' Set the content type to Excel.
Response.Conten tType = "applicatio n/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charse t = ""
' Turn off the view state.
Me.EnableViewSt ate = False

Dim tw As New System.IO.Strin gWriter()
Dim hw As New System.Web.UI.H tmlTextWriter(t w)
' Get the HTML for the control.
DataGrid1.Rende rControl(hw)
' Write the HTML back to the browser.
Dim strFileName As String = "Roster_Export_ " &
m_objCourse.Cou rseKey.ToString .Replace("/", "_")

Response.Clear( )
Response.Buffer = True
Response.Expire s = 0
' Change the HTTP header to reflect that an image is being passed.
strFileName = strFileName & ".xls"
Response.AddHea der("Content-Disposition", "attachment ; filename=" &
strFileName)

Response.Flush( )
Response.Write( tw.ToString())
' End the response.
Response.End()
Here is my import code :

Dim _files As HttpFileCollect ion = HttpContext.Cur rent.Request.Fi les
Dim _message As System.Text.Str ingBuilder = New
System.Text.Str ingBuilder("Fil e Uploaded:<br>")
Dim sConnectionStri ng As String
Try
Dim _iFile As Integer
For _iFile = 0 To _files.Count - 1
Dim _postedFile As HttpPostedFile = _files(_iFile)
Dim _fileName As String
If _postedFile.Fil eName <> "" Then
_fileName = System.IO.Path. GetFullPath(_po stedFile.FileNa me)
' Create connection string variable. Modify the "Data Source"
parameter as
' appropriate for your environment.
'Dim sConnectionStri ng As String = BuildConnection String(_fileNam e)

sConnectionStri ng = "Provider=Micro soft.Jet.OLEDB. 4.0;"
sConnectionStri ng += "Data Source=" & _fileName & ";"
sConnectionStri ng += "Extended Properties=""Ex cel 8.0;HDR=YES;"""
' Create the connection object by using the preceding connection
string.
Dim objConn As New OleDbConnection (sConnectionStr ing)
' Open connection with the database.
objConn.Open()

' The code to follow uses a SQL SELECT command to display the data
from the worksheet.
' Create new OleDbCommand to return data from worksheet.
'Dim objCmdSelect As New OleDbCommand("S ELECT * FROM [Week2$] ",
objConn)
Dim objCmdSelect As New OleDbCommand("S ELECT * FROM [RosterExport$]
", objConn)
' Create new OleDbDataAdapte r that is used to build a DataSet
' based on the preceding SQL SELECT statement.

Dim objAdapter1 As New OleDbDataAdapte r()
' Pass the Select command to the adapter.

objAdapter1.Sel ectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.

Dim objDataset1 As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter1.Fil l(objDataset1, "XLData")

This will save the file but requires the user to save the file as an
excel file. A step I do not wish to take. A test of the application is
to do an export and then do an import (same file) with no hiccup.
Currently the file has to be opened and saved to an actual excel for
it is really an html file with an xls extension.

Any help would be greatly appreciated

JAE

[George Durzi]

A method I use is to transform the XML into HTML, save it to a file with a
..xls extension, then write the file to the browser.

Excel does a great job of displaying simple HTML. And you trick it into
displaying HTML in Excel when you save the file with an excel extension

"Greg Burns" <gr********@hot mail.com> wrote in message
news:e$******** ******@TK2MSFTN GP09.phx.gbl...

This maybe helpful:

Export to Excel

http://www.aspnetpro.com/features/20...200310so_f.asp

Requires an asp.netPRO subscription.

Greg
"Jae" <ak*********@ea rthlink.net> wrote in message
news:22******** *************** ***@posting.goo gle.com...

I'm writing a web application that exports and imports excel files.
The application gets a list of users and their info and displays it in
a datagrid .The user then selects to save the file as a tab delimited
file or an excel file. The application then saves the file in the
correct format.
The flip side is for the user to import/upload the file to the server
The application must be able to import the excel file and read the
contents.
I have searched here and kB 316934 How to Use Ado.Net to Retrieve and
Modify Records. That example assumes permissions on the client
machinme which I will not have

Here is a code snippet of the excel export :
' Set the content type to Excel.
Response.Conten tType = "applicatio n/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charse t = ""
' Turn off the view state.
Me.EnableViewSt ate = False

Dim tw As New System.IO.Strin gWriter()
Dim hw As New System.Web.UI.H tmlTextWriter(t w)
' Get the HTML for the control.
DataGrid1.Rende rControl(hw)
' Write the HTML back to the browser.
Dim strFileName As String = "Roster_Export_ " &
m_objCourse.Cou rseKey.ToString .Replace("/", "_")

Response.Clear( )
Response.Buffer = True
Response.Expire s = 0
' Change the HTTP header to reflect that an image is being passed.
strFileName = strFileName & ".xls"
Response.AddHea der("Content-Disposition", "attachment ; filename=" &
strFileName)

Response.Flush( )
Response.Write( tw.ToString())
' End the response.
Response.End()
Here is my import code :

Dim _files As HttpFileCollect ion = HttpContext.Cur rent.Request.Fi les
Dim _message As System.Text.Str ingBuilder = New
System.Text.Str ingBuilder("Fil e Uploaded:<br>")
Dim sConnectionStri ng As String
Try
Dim _iFile As Integer
For _iFile = 0 To _files.Count - 1
Dim _postedFile As HttpPostedFile = _files(_iFile)
Dim _fileName As String
If _postedFile.Fil eName <> "" Then
_fileName = System.IO.Path. GetFullPath(_po stedFile.FileNa me)
' Create connection string variable. Modify the "Data Source"
parameter as
' appropriate for your environment.
'Dim sConnectionStri ng As String = BuildConnection String(_fileNam e)

sConnectionStri ng = "Provider=Micro soft.Jet.OLEDB. 4.0;"
sConnectionStri ng += "Data Source=" & _fileName & ";"
sConnectionStri ng += "Extended Properties=""Ex cel 8.0;HDR=YES;"""
' Create the connection object by using the preceding connection
string.
Dim objConn As New OleDbConnection (sConnectionStr ing)
' Open connection with the database.
objConn.Open()

' The code to follow uses a SQL SELECT command to display the data
from the worksheet.
' Create new OleDbCommand to return data from worksheet.
'Dim objCmdSelect As New OleDbCommand("S ELECT * FROM [Week2$] ",
objConn)
Dim objCmdSelect As New OleDbCommand("S ELECT * FROM [RosterExport$]
", objConn)
' Create new OleDbDataAdapte r that is used to build a DataSet
' based on the preceding SQL SELECT statement.

Dim objAdapter1 As New OleDbDataAdapte r()
' Pass the Select command to the adapter.

objAdapter1.Sel ectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.

Dim objDataset1 As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter1.Fil l(objDataset1, "XLData")

This will save the file but requires the user to save the file as an
excel file. A step I do not wish to take. A test of the application is
to do an export and then do an import (same file) with no hiccup.
Currently the file has to be opened and saved to an actual excel for
it is really an html file with an xls extension.

Any help would be greatly appreciated

JAE

[George Durzi]

Here you go, there's a lot excluded for brevity. But all you need is in here

// get back the search results
DataSet dsReport = ExecuteReportRe turnDataSet();
// write them out to disk
string exportPath = Facade.Export.E xportDataSet(
dsReport,
Session["UserLogin"].ToString(),
ConfigurationSe ttings.AppSetti ngs["ApplicationPat h"].ToString() +
ConfigurationSe ttings.AppSetti ngs["ReportPath "].ToString(),
ConfigurationSe ttings.AppSetti ngs["ApplicationPat h"].ToString() +
@"xsl\gb_report _flash.xsl",
Convert.ToInt32 (cUIHelper.Expo rtFormat.Export Xls).ToString() );

// Write the Report to the Browser
Response.ClearC ontent();
Response.ClearH eaders();
Response.Conten tType = "applicatio n/vnd.ms-excel";
Response.WriteF ile(exportPath) ;
Response.Flush( );
Response.Close( );

System.IO.File. Delete(exportPa th);

#region ExportDataSet UserName, FilePath, XslPath, ExportFormatCho ice
public static string ExportDataSet(
DataSet SearchResults,
string UserName,
string FilePath,
string XslPath,
string ExportFormatCho ice)
{
string exportPath = "";

switch ((ExportFormat) Convert.ToInt32 (ExportFormatCh oice))
{
case (ExportFormat.E xportXls):
exportPath = FilePath + UserName + "_SearchResults .xls";
break;
case (ExportFormat.E xportHtml):
exportPath = FilePath + UserName + "_SearchResults .html";
break;
}

// Create a FileStream to write with
System.IO.FileS tream fs = new System.IO.FileS tream(
exportPath, System.IO.FileM ode.Create);
// Create an XmlTextWriter for the FileStream
System.Xml.XmlT extWriter xtw = new System.Xml.XmlT extWriter(
fs, System.Text.Enc oding.Unicode);
try
{
XmlDataDocument xmlDoc = new XmlDataDocument (SearchResults) ;
System.Xml.Xsl. XslTransform xslTran = new System.Xml.Xsl. XslTransform();
xslTran.Load(Xs lPath);
xslTran.Transfo rm(xmlDoc, null, xtw);
xtw.Close();
return exportPath;
}
catch (Exception ex)
{
xtw.Close();
System.IO.File. Delete(exportPa th);
ExceptionManage r.Publish(ex);
throw(ex);
}
}
#endregion

[Jae]

"George Durzi" <gdurzi@nospam_ hotmail.com> wrote in message news:<eH******* *******@tk2msft ngp13.phx.gbl>. ..

Here you go, there's a lot excluded for brevity. But all you need is in here

Thanks George I tried that code snippet but was unable to get the
importer to understand the excel file. I'm not sure why jet is having
a problem but I still get the table is not in a recognized format
error.