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!

 

[Tim_Mac]

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

 

[PB]

Thank you so much! The perspective on Access is helpful - and all that
code... very very much appreciated.

-PB

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