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 at the time the users request it.
My initial plan is that when users request their data, they will click a
button or link indicating that they want their data in the respective file
format. Then, on the server I want to execute a stored procedure that
retrieves the data; then insert it into a new/blank XLS or MDB file - and
then send that new file down to the browser.
My question: What are my options for sending the newly created XLS or MDB
file down to the browser? I would like for the users to be able to chose to
save the file or view it in its respective host application.
Thanks! 2 2167
hi,
here is some useful code i have put together from various other
solutions i found online.
it is very simple because you don't ever create a file and hence don't
need to save it to disk on the server. the DataSet is bound to a
runtime-created datagrid, which is then sent to the client in a format
that Excel can understand. i would recommend excel more than access
simply because of license availability, i.e. most people have excel as
standard but not access. also access is more fussy about versions.
in your scenario, to use the code below, get a dataset from the stored
procedure, and call:
ExportDataSet(dataset1, "somefile.xls", ExportFormat.Excel);
public enum ExportFormat{Excel, CSV};
/// <summary>
/// Write a dataset to the HttpResponse as an excel file.
/// </summary>
public static void ExportDataSet(DataSet ds, string filename,
ExportFormat format)
{
DataGrid dg = new DataGrid();
dg.DataSource = ds.Tables[0];
dg.DataBind();
ExportDataGrid(dg, filename, format);
}
/// <summary>
/// Write a dataset to the HttpResponse as an excel file.
/// </summary>
public static void ExportDataGrid(DataGrid dg, string filename,
ExportFormat format)
{
HttpResponse response = HttpContext.Current.Response;
// first let's clean up the response.object
response.Clear();
response.Charset = "";
response.ContentEncoding = Encoding.UTF8;
response.Charset = "";
response.AddHeader("Content-Disposition", "attachment;filename=\"" +
filename + "\"");
// get the text of the rendered datagrid
string dgText;
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// instantiate a datagrid
dg.RenderControl(htw);
dgText = sw.ToString();
}
}
// set the response mime type
switch(format)
{
case ExportFormat.Excel:
response.ContentType = "application/vnd.ms-excel";
response.Write(dgText);
response.End();
break;
case ExportFormat.CSV:
response.ContentType = "text/txt";
string rowDelim = System.Environment.NewLine;
string colDelim = ",";
Regex rex = new Regex(@"(>\s+<)",RegexOptions.IgnoreCase);
dgText = rex.Replace(dgText,"><");
// remove new lines from html
dgText = dgText.Replace(System.Environment.NewLine,"");
// replace end of rows html with the row separator
dgText = dgText.Replace("</td></tr><tr><td>",rowDelim);
// replace any instances of the column delimiter with an escaped
delimiter
dgText = dgText.Replace(colDelim, "\\" + colDelim);
// replace column separators with the column delimiter
dgText = dgText.Replace("</td><td>",colDelim);
// remove other tags
rex = new Regex(@"<[^>]*>",RegexOptions.IgnoreCase);
dgText = rex.Replace(dgText,"");
// convert and other html characters into normal text
dgText = HttpUtility.HtmlDecode(dgText);
response.Write(dgText);
response.End();
break;
}
}
hope this helps
tim
Thank you so much! The perspective on Access is helpful - and all that
code... very very much appreciated.
-PB
"Tim_Mac" <ti*@mackey.ie> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com... hi, here is some useful code i have put together from various other solutions i found online. it is very simple because you don't ever create a file and hence don't need to save it to disk on the server. the DataSet is bound to a runtime-created datagrid, which is then sent to the client in a format that Excel can understand. i would recommend excel more than access simply because of license availability, i.e. most people have excel as standard but not access. also access is more fussy about versions.
in your scenario, to use the code below, get a dataset from the stored procedure, and call: ExportDataSet(dataset1, "somefile.xls", ExportFormat.Excel);
public enum ExportFormat{Excel, CSV};
/// <summary> /// Write a dataset to the HttpResponse as an excel file. /// </summary> public static void ExportDataSet(DataSet ds, string filename, ExportFormat format) { DataGrid dg = new DataGrid(); dg.DataSource = ds.Tables[0]; dg.DataBind(); ExportDataGrid(dg, filename, format); }
/// <summary> /// Write a dataset to the HttpResponse as an excel file. /// </summary> public static void ExportDataGrid(DataGrid dg, string filename, ExportFormat format) { HttpResponse response = HttpContext.Current.Response;
// first let's clean up the response.object response.Clear(); response.Charset = ""; response.ContentEncoding = Encoding.UTF8; response.Charset = ""; response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
// get the text of the rendered datagrid string dgText; using (StringWriter sw = new StringWriter()) { using (HtmlTextWriter htw = new HtmlTextWriter(sw)) { // instantiate a datagrid dg.RenderControl(htw); dgText = sw.ToString(); } }
// set the response mime type switch(format) { case ExportFormat.Excel: response.ContentType = "application/vnd.ms-excel"; response.Write(dgText); response.End(); break;
case ExportFormat.CSV: response.ContentType = "text/txt"; string rowDelim = System.Environment.NewLine; string colDelim = ",";
Regex rex = new Regex(@"(>\s+<)",RegexOptions.IgnoreCase); dgText = rex.Replace(dgText,"><"); // remove new lines from html dgText = dgText.Replace(System.Environment.NewLine,""); // replace end of rows html with the row separator dgText = dgText.Replace("</td></tr><tr><td>",rowDelim); // replace any instances of the column delimiter with an escaped delimiter dgText = dgText.Replace(colDelim, "\\" + colDelim); // replace column separators with the column delimiter dgText = dgText.Replace("</td><td>",colDelim); // remove other tags rex = new Regex(@"<[^>]*>",RegexOptions.IgnoreCase); dgText = rex.Replace(dgText,""); // convert and other html characters into normal text dgText = HttpUtility.HtmlDecode(dgText); response.Write(dgText); response.End(); break; } }
hope this helps tim This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: edykstra |
last post by:
Hello,
If you point your browser to this page,
http://prdownloads.sourceforge.net/vnc-tight/tightvnc-1.2.9_x86_viewer.zip?use_mirror=umn
the server sends you the HTML page, and very soon...
|
by: amy |
last post by:
what is the method to send a binary file to a client from
a webservice ?
what is the method to receive/download this binary file
and save it
to the local machine in a client application ?
THANKS!
|
by: Tom |
last post by:
Hi,
I have a website providing pdf file downloading for registered member.
Member needs to login and be able to download pdf file.
I save pdf file in a directory. e.g....
|
by: mboyda |
last post by:
I have a block of code that useses outputstream to save a pdf to a
client computer. It prompts the user with the save dialog, works
great but the response finishes after the download completes. ...
|
by: Nikhil Patel |
last post by:
Hi all,
I am writing an asp.net application that will allow users to upload and
download files. I know I can use the Input html control with type=file to
allow the uploading of files. But what is...
|
by: user2008 |
last post by:
Hi all,
I want to track how many times visitor download file from my website,
for example, when visitor click on a download link, a ASPX page will be
requested, after that it will redirect to a...
|
by: Bill nguyen |
last post by:
How can I open an Excel file then SAVE AS .CSV file using VB.NET?
Thanks
Bill
|
by: matt |
last post by:
I have used some free code for listing files for download, but I want to
send an email to the administrator when the file has been downloaded. I
have got some code in here that does it, but it will...
|
by: Ashu Saxena |
last post by:
Hi !
I'm a php developer.
I'm developing a site into which I have to upload a text file and a image file
from users through a browse box and then I have to retrieve that file and save it
into...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |