473,226 Members | 1,470 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,226 software developers and data experts.

Enabling Users to Download MDB or XLS File (then save or view)

PB
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!
Nov 19 '05 #1
2 2159
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 &nbsp; and other html characters into normal text
dgText = HttpUtility.HtmlDecode(dgText);
response.Write(dgText);
response.End();
break;
}
}

hope this helps
tim

Nov 19 '05 #2
PB
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 &nbsp; and other html characters into normal text
dgText = HttpUtility.HtmlDecode(dgText);
response.Write(dgText);
response.End();
break;
}
}

hope this helps
tim

Nov 19 '05 #3

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

Similar topics

14
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...
1
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!
1
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....
2
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. ...
2
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...
1
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...
2
by: Bill nguyen | last post by:
How can I open an Excel file then SAVE AS .CSV file using VB.NET? Thanks Bill
16
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...
1
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...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

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.