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 2187
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(d ataset1, "somefile.x ls", ExportFormat.Ex cel);
public enum ExportFormat{Ex cel, CSV};
/// <summary>
/// Write a dataset to the HttpResponse as an excel file.
/// </summary>
public static void ExportDataSet(D ataSet 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.Cur rent.Response;
// first let's clean up the response.object
response.Clear( );
response.Charse t = "";
response.Conten tEncoding = Encoding.UTF8;
response.Charse t = "";
response.AddHea der("Content-Disposition", "attachment;fil ename=\"" +
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.RenderContro l(htw);
dgText = sw.ToString();
}
}
// set the response mime type
switch(format)
{
case ExportFormat.Ex cel:
response.Conten tType = "applicatio n/vnd.ms-excel";
response.Write( dgText);
response.End();
break;
case ExportFormat.CS V:
response.Conten tType = "text/txt";
string rowDelim = System.Environm ent.NewLine;
string colDelim = ",";
Regex rex = new Regex(@"(>\s+<) ",RegexOptions. IgnoreCase);
dgText = rex.Replace(dgT ext,"><");
// remove new lines from html
dgText = dgText.Replace( System.Environm ent.NewLine,"") ;
// replace end of rows html with the row separator
dgText = dgText.Replace( "</td></tr><tr><td>",ro wDelim);
// 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>",colDel im);
// remove other tags
rex = new Regex(@"<[^>]*>",RegexOption s.IgnoreCase);
dgText = rex.Replace(dgT ext,"");
// convert and other html characters into normal text
dgText = HttpUtility.Htm lDecode(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.i e> wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.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(d ataset1, "somefile.x ls", ExportFormat.Ex cel);
public enum ExportFormat{Ex cel, CSV};
/// <summary> /// Write a dataset to the HttpResponse as an excel file. /// </summary> public static void ExportDataSet(D ataSet 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.Cur rent.Response;
// first let's clean up the response.object response.Clear( ); response.Charse t = ""; response.Conten tEncoding = Encoding.UTF8; response.Charse t = ""; response.AddHea der("Content-Disposition", "attachment;fil ename=\"" + 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.RenderContro l(htw); dgText = sw.ToString(); } }
// set the response mime type switch(format) { case ExportFormat.Ex cel: response.Conten tType = "applicatio n/vnd.ms-excel"; response.Write( dgText); response.End(); break;
case ExportFormat.CS V: response.Conten tType = "text/txt"; string rowDelim = System.Environm ent.NewLine; string colDelim = ",";
Regex rex = new Regex(@"(>\s+<) ",RegexOptions. IgnoreCase); dgText = rex.Replace(dgT ext,"><"); // remove new lines from html dgText = dgText.Replace( System.Environm ent.NewLine,"") ; // replace end of rows html with the row separator dgText = dgText.Replace( "</td></tr><tr><td>",ro wDelim); // 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>",colDel im); // remove other tags rex = new Regex(@"<[^>]*>",RegexOption s.IgnoreCase); dgText = rex.Replace(dgT ext,""); // convert and other html characters into normal text dgText = HttpUtility.Htm lDecode(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 afterwards, you are
prompted to do a 'Save-As' for a file to download.
How do they do that without the classic "Headers already sent" error?
|
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. http://www.downloadpdf.com/pdf/abc.pdf
However, I needs to restrict those non-member to download the pdf file
directly by input the URL http://www.downloadpdf.com/pdf/abc.pdf.
|
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. How do
I redirect the users to a new friendly page after the download
completes. The aspx page comes back with a ugly "action canceled". I
prefer not to use popup windows as alot of people are blocking them.
I just want them to click a download...
|
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 a good way to allow downloading
files? When users download a file I would like to show them a folder browser
that will let them save the downloaded file. How can I do this?
Thanks.
Nikhil
| |
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 file stored on my web site,
final visitor will see open/save file dialog,
how to do that, or is there an example code do this work?
thank you,
|
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 not print in the
username or email amddress of the person doing the download - which I am
collecting from a form on the previous page. I can get the name and
email address to print out normally, just not into the email sending
body. I have...
|
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 mysql database .Should I use POST method to retrieve the browse box
value that have been uploaded with a text file or image file.For example :-
<html>
<body>
<form method="post" action="test.php">
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |