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