People love reports. I do not know why but most of the time, the systems that I am working on always have this report module. The requirements of the report are usually given by the administrative staff. So normally the admin will always give me a sample of existing report as a reference during the development of the report module.
Previously, the admin was happy with just one report module giving them the ability to view reports by using their login id and password. After that, they wanted the function to export the report to Excel so that they could immediately work on the data analysis. Soon, they realized that logging in to the system just to view the report was a bit stupid. Thus, they required an email to be sent to them in midnight with the report in Excel format attached in the email.
Although there is this cool stuff called Excel Interactive View which can generate Excel table and charts of an HTML table on the fly, I don’t really like it. The generated Excel table looks very complicated with colourful bar appearing at the background of the cells containing numbers.
Also, as shown in the following screenshot, the Excel Interactive View does not do a good job because contact number and tutorial class number are wrongly taken as numerical data used to generate the charts. Hence, Excel Interactive View is great and convenient but it does not work in all kinds of reports.
In ASP.NET, I can have my own “Export to Excel” button by adding in the following codes in the Page_Load method of a web page.
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=\"Sales_Report.xls\"");
string excelBody = "";
excelBody +=
"<head>" +
"<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">" +
"<style>" +
"<!--table" +
"br {mso-data-placement:same-cell;}" +
"tr {vertical-align:top;}" +
"-->" +
"</style>" +
"</head>";
excelBody += "<body>" + <The HTML table goes here...> + "</body>";
excelBody += "</html>";
Response.Write(excelBody);
Then, I just need to redirect the user to this page when the “Export to Excel” button is pressed. This is the code used when admin staff was satisfied with just the functionality to export their reports to Excel.
Soon after that, I found another great library to help generating Excel spreadsheet in C#. It is called excellibrary, which is able to be downloaded on Google Code. Thanks to the library, I am able to do a system which will automatically send out an email attached with Excel report.
To do that, firstly, I need to generate the report in Excel format with the help of excellibrary.
Workbook workbook = new Workbook();
Worksheet wsReport = new Worksheet("Sales Report");
int startingRow = 0;
wsReport.Cells[startingRow, 0] = new Cell("Column 1 Row 1");
wsReport.Cells[startingRow, 1] = new Cell("Column 2 Row 2");
...
for (int i = startingRow + 1; i < 200; i++)
{
wsReport.Cells[i, 0] = new Cell(" "); // Some dummy empty cells
}
workbook.Worksheets.Add(wsReport);
workbook.Save("C:\\ExcelOutputs\\Sales_Report.xls");
The reason for adding some dummy empty cells in the end is because Excel will complain that it found unreadable content in Sales_Report.xls when the number of cells containing real data is too small. This is a reported issue in the excellibrary project and one workaround suggested by the users is to increase the file size by adding more rows and columns with a space.
Secondly, I send out the email by using the following code.
string sSmtpServer = "smtp.gmail.com";
MailMessage myMessage = new MailMessage();
myMessage.Body = "The report in Excel format is attached in this email.";
myMessage.Subject = "Sales Report in Excel!";
myMessage.To = "...";
// Add the file attachment to this e-mail message.
myMessage.Attachments.Add(new MailAttachment("C:\\ExcelOutputs\\Sales_Report.xls"));
myMessage.Fields["http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"] = 1;
myMessage.Fields["http://schemas.microsoft.com/cdo/configuration/sendusing"] = 2;
myMessage.Fields["http://schemas.microsoft.com/cdo/configuration/sendusername"] = "...";
myMessage.Fields["http://schemas.microsoft.com/cdo/configuration/sendpassword"] = "...";
myMessage.Fields["http://schemas.microsoft.com/cdo/configuration/smtpserverport"] = "465";
myMessage.Fields["http://schemas.microsoft.com/cdo/configuration/smtpusessl"] = "true";
myMessage.From = "...";
myMessage.BodyFormat = MailFormat.Html;
SmtpMail.SmtpServer = sSmtpServer;
SmtpMail.Send(myMessage);
Finally, I just need to create a scheduled task to run this little program daily.
Yup, this is how my automatic report exporting and emailing system is done in C#.
cuteprogramming 