Save an Excel from to client using Asp.Net

  • Thread starter Thread starter Jae
  • Start date Start date
J

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.ContentType = "application/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charset = ""
' Turn off the view state.
Me.EnableViewState = False

Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
' Get the HTML for the control.
DataGrid1.RenderControl(hw)
' Write the HTML back to the browser.
Dim strFileName As String = "Roster_Export_" &
m_objCourse.CourseKey.ToString.Replace("/", "_")

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

Response.Flush()
Response.Write(tw.ToString())
' End the response.
Response.End()


Here is my import code :

Dim _files As HttpFileCollection = HttpContext.Current.Request.Files
Dim _message As System.Text.StringBuilder = New
System.Text.StringBuilder("File Uploaded:<br>")
Dim sConnectionString 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.FileName <> "" Then
_fileName = System.IO.Path.GetFullPath(_postedFile.FileName)
' Create connection string variable. Modify the "Data Source"
parameter as
' appropriate for your environment.
'Dim sConnectionString As String = BuildConnectionString(_fileName)

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnectionString += "Data Source=" & _fileName & ";"
sConnectionString += "Extended Properties=""Excel 8.0;HDR=YES;"""
' Create the connection object by using the preceding connection
string.
Dim objConn As New OleDbConnection(sConnectionString)
' 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("SELECT * FROM [Week2$] ",
objConn)
Dim objCmdSelect As New OleDbCommand("SELECT * FROM [RosterExport$]
", objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.

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

objAdapter1.SelectCommand = 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.Fill(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
 
This maybe helpful:

Export to Excel

http://www.aspnetpro.com/features/2003/10/asp200310so_f/asp200310so_f.asp

Requires an asp.netPRO subscription.

Greg


Jae said:
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.ContentType = "application/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charset = ""
' Turn off the view state.
Me.EnableViewState = False

Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
' Get the HTML for the control.
DataGrid1.RenderControl(hw)
' Write the HTML back to the browser.
Dim strFileName As String = "Roster_Export_" &
m_objCourse.CourseKey.ToString.Replace("/", "_")

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

Response.Flush()
Response.Write(tw.ToString())
' End the response.
Response.End()


Here is my import code :

Dim _files As HttpFileCollection = HttpContext.Current.Request.Files
Dim _message As System.Text.StringBuilder = New
System.Text.StringBuilder("File Uploaded:<br>")
Dim sConnectionString 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.FileName <> "" Then
_fileName = System.IO.Path.GetFullPath(_postedFile.FileName)
' Create connection string variable. Modify the "Data Source"
parameter as
' appropriate for your environment.
'Dim sConnectionString As String = BuildConnectionString(_fileName)

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnectionString += "Data Source=" & _fileName & ";"
sConnectionString += "Extended Properties=""Excel 8.0;HDR=YES;"""
' Create the connection object by using the preceding connection
string.
Dim objConn As New OleDbConnection(sConnectionString)
' 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("SELECT * FROM [Week2$] ",
objConn)
Dim objCmdSelect As New OleDbCommand("SELECT * FROM [RosterExport$]
", objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.

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

objAdapter1.SelectCommand = 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.Fill(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
Click to expand...
 
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 said:
This maybe helpful:

Export to Excel

http://www.aspnetpro.com/features/2003/10/asp200310so_f/asp200310so_f.asp

Requires an asp.netPRO subscription.

Greg


Jae said:
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.ContentType = "application/vnd.ms-excel"
' Remove the charset from the Content-Type header.
Response.Charset = ""
' Turn off the view state.
Me.EnableViewState = False

Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
' Get the HTML for the control.
DataGrid1.RenderControl(hw)
' Write the HTML back to the browser.
Dim strFileName As String = "Roster_Export_" &
m_objCourse.CourseKey.ToString.Replace("/", "_")

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

Response.Flush()
Response.Write(tw.ToString())
' End the response.
Response.End()


Here is my import code :

Dim _files As HttpFileCollection = HttpContext.Current.Request.Files
Dim _message As System.Text.StringBuilder = New
System.Text.StringBuilder("File Uploaded:<br>")
Dim sConnectionString 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.FileName <> "" Then
_fileName = System.IO.Path.GetFullPath(_postedFile.FileName)
' Create connection string variable. Modify the "Data Source"
parameter as
' appropriate for your environment.
'Dim sConnectionString As String = BuildConnectionString(_fileName)

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnectionString += "Data Source=" & _fileName & ";"
sConnectionString += "Extended Properties=""Excel 8.0;HDR=YES;"""
' Create the connection object by using the preceding connection
string.
Dim objConn As New OleDbConnection(sConnectionString)
' 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("SELECT * FROM [Week2$] ",
objConn)
Dim objCmdSelect As New OleDbCommand("SELECT * FROM [RosterExport$]
", objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.

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

objAdapter1.SelectCommand = 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.Fill(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
Click to expand...
Click to expand...
 
Here you go, there's a lot excluded for brevity. But all you need is in here

// get back the search results
DataSet dsReport = ExecuteReportReturnDataSet();
// write them out to disk
string exportPath = Facade.Export.ExportDataSet(
dsReport,
Session["UserLogin"].ToString(),
ConfigurationSettings.AppSettings["ApplicationPath"].ToString() +
ConfigurationSettings.AppSettings["ReportPath"].ToString(),
ConfigurationSettings.AppSettings["ApplicationPath"].ToString() +
@"xsl\gb_report_flash.xsl",
Convert.ToInt32(cUIHelper.ExportFormat.ExportXls).ToString());

// Write the Report to the Browser
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/vnd.ms-excel";
Response.WriteFile(exportPath);
Response.Flush();
Response.Close();

System.IO.File.Delete(exportPath);



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

switch ((ExportFormat)Convert.ToInt32(ExportFormatChoice))
{
case (ExportFormat.ExportXls):
exportPath = FilePath + UserName + "_SearchResults.xls";
break;
case (ExportFormat.ExportHtml):
exportPath = FilePath + UserName + "_SearchResults.html";
break;
}

// Create a FileStream to write with
System.IO.FileStream fs = new System.IO.FileStream(
exportPath, System.IO.FileMode.Create);
// Create an XmlTextWriter for the FileStream
System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(
fs, System.Text.Encoding.Unicode);
try
{
XmlDataDocument xmlDoc = new XmlDataDocument(SearchResults);
System.Xml.Xsl.XslTransform xslTran = new System.Xml.Xsl.XslTransform();
xslTran.Load(XslPath);
xslTran.Transform(xmlDoc, null, xtw);
xtw.Close();
return exportPath;
}
catch (Exception ex)
{
xtw.Close();
System.IO.File.Delete(exportPath);
ExceptionManager.Publish(ex);
throw(ex);
}
}
#endregion
 
George Durzi said:
Here you go, there's a lot excluded for brevity. But all you need is in here
Click to expand...

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.
 
Back
Top