473,324 Members | 2,248 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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.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
Nov 17 '05 #1
4 4498
This maybe helpful:

Export to Excel

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

Requires an asp.netPRO subscription.

Greg
"Jae" <ak*********@earthlink.net> wrote in message
news:22**************************@posting.google.c om...
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

Nov 17 '05 #2
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********@hotmail.com> wrote in message
news:e$**************@TK2MSFTNGP09.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*********@earthlink.net> wrote in message
news:22**************************@posting.google.c om...
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


Nov 17 '05 #3
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
Nov 17 '05 #4
Jae
"George Durzi" <gdurzi@nospam_hotmail.com> wrote in message news:<eH**************@tk2msftngp13.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.
Nov 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Kelmen Wong | last post by:
Greeting, We have an ASP.NET app, developed using c#. There's a feature which a page (UserControl) when postback, it will download a PDF/Excel file through typical Response and Content Header...
2
by: PB | last post by:
I need to enable users to download data in either an Excel file or an Access database (MDB file). The data they need exists in a SQL Server database: the XLS or MDB file does not actually exist...
5
by: Patrick | last post by:
Following on from the excellent example at http://www.c-sharpcorner.com/Code/2003/Sept/ExportASPNetDataGridToExcel.asp on how to save a data-grid to excel file, how can I extend the example such...
6
by: Michael Groeger | last post by:
Hi, I have an aspx page which generates an excel document and transfers it to the browser as attachment. Normally, once the document is transferred the open save dialog prompts to open or save...
2
by: iloveprincess | last post by:
Hi, I'm developing windows application using VB.Net 2005. I would like to send 'save' message using 'SendMessage' API to the excel appication. I've already got a handle of the excel window with...
3
by: S_K | last post by:
Hi, I have a problem when I try to save a .CSV file from an ASP.NET web page and the client has Excel open already. The symptoms are: 1) The web page is currently displaying the Excel...
6
scubak1w1
by: scubak1w1 | last post by:
Hello, I have been Googling for a bit now as to how to let the user save the current page via a 'clickable icon' to an HTML file (i.e., on the client side.) That is, I am presenting a lot of...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
3
by: evenlater | last post by:
I have an Access application on a terminal server. Sometimes my users need to export reports to pdf, rtf or xls files and save them to their own client device hard drives. They can do that right...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.