repost -- asp .net / save Excel on server

[mattmerc]

Hi all,

This is a repost from a week or so ago. I'll try to reformat so it is
more clear.

I am looking for something in asp .net / vb .net that will export a
datagrid to Excel, BUT will save the file on the server (not the
client). Can this be done without Excel installed on the server?

Is there a was to send the contents of a datagrid as an excel
attachment to an email. Or even embedded in the email?

If possible, the solution should not require excel to be installed on
the server and should allow formatting of the excel to make it more
readable. Specifically, I would like the longer fields to word wrap
within the cell instead of being cut off or continuously on one line.


Any help is appreciated. I don't mind doing the research and learning
how to do this, but I am coming up blank so far. Anything with Excel
turns up so many hits it is hard to read it all.


Thanks again.

 

[Jacob]

Is your data from the datagrid stored in a dataset? I would assume so.
In that case you can create your Excel file in memory and store it
where ever you want (server, client, email).

Are you just doing a single worksheet? In that case you might just want
to create a CSV file that doesn't require Excel. If you need a more
complex workbook, then have a look at the CarlosAG ExcelWriter.

If you need more help drop me a private email.

 

[Alan Samet]

I copied this from a post I made a while back. Have you considered just
changing the MIMEType of your asp.net page such that it's opened by
Excel?

Example:

<%@ Page Language="C#"%>
<script runat=server>
void Page_Load(object sender, EventArgs e)
{
Response.ContentType = "application/vnd.ms-excel";
}
</script>
<table>
<tr>
<td><b>Column 1</b></td>
<td style="width: 300px;"><b>Column 2</b></td>
</tr>
<tr>
<td style="background-color: yellow; color:
red;">Value</td>
<td>Another Value</td>
</tr>
</table>

-Alan

 

[mattdaddym]

Thanks Alan, but I do not understand how this can save a file to the
server or send an attachment with email.

 

[Alan Samet]

It can't attach to an email. However, if you have control over the
server, you could save an HTML document to the server w/ an .xls
extension and write an HttpHandler that'll include the header when a
page w/ the .xls extension is requested.

-Alan

 

[boyof82]

Hi Matt,

I worked out the same problem today after I went through your post and its running just fine. I created a new datagrid object and bound it to the data set which I want to export to an excel sheet and finally send it as an attachment. I've also created a new folder called 'Excel' in my application where I'll save the excel file. The code is written using C# and not VB. Hope this helps.

Make sure you use System.IO; and System.Web.Mail;

DataSet ds = new DataSet();
ds.Clear();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
DataGrid dgd = new DataGrid();
dgd.DataSource = ds.Tables[0];
dgd.DataBind();
string strFilePath = string.Empty;

strFilePath = Server.MapPath("~/Excel/") + "Report" + ".xls";

if (File.Exists(strFilePath))
{
File.Delete(strFilePath);
}

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);



StreamWriter objStreamWriter = File.AppendText(strFilePath);

dgd.RenderControl(oHtmlTextWriter);

objStreamWriter.WriteLine(oStringWriter.ToString());

objStreamWriter.Close();

MailMessage mail = new MailMessage();

mail.From = "(e-mail address removed)";
mail.To = email;//here u'll fill the email address of the person u //want to send the attachment to.
mail.Subject = "List of products purchased today";
mail.Body = "Please find attached the list of products you purchased today";
mail.BodyFormat = MailFormat.Html;
mail.Attachments.Add(new MailAttachment(strFilePath));

try
{
SmtpMail.SmtpServer = "192.168.0.224";
SmtpMail.Send(mail);
}
catch (Exception ex)
{
Response.Write("<b> Exception Occured: </b>" + ex);
}
finally
{
Response.Write("Your Email has been sent successfully");
}

Best
Ashim

 

[Mio]

Thank you Ashim for that sample code, brpblam with Hebrew text in the Excel file

Thank you Ashim for that sample code, it is what i was looking for.
Just one more thing that i can't figure up, how do i set the Excel File Encoding to be utf-7 for showing Hebrew text in the Excel file i send as attached by Email from the Web Application.

Thank You