Requirement:Provided the user has authorization to request reports (Vols.reports=”Y”), display report names (Report.rname) as report choices and a “Return to the Volunteer Menu” button. When the user clicks on a name, retrieve the report script from the Reports table, execute the script, email the result to the user as an Excel spreadsheet attachment, and display the message “Report Successfully Sent.”
Problem:The requirement is mentioned above. I have attached 'report.aspx.cs' file (I'm using C#.NET) which I have developed to meet above requirement. Everything works fine except that the excel sheet has garbage characters in first row like '´ ' etc. Any ideas to solve this problem? Any help will be greatly appreciated. Thanks, [email protected]
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.Web.Mail;using System.Net.Mail;using System.Data.OleDb;using System.Data.Common;using System.IO;
public partial class development_Reports : System.Web.UI.Page{ string connstr = ConfigurationSettings.AppSettings["connstr"]; string smtpserver = ConfigurationSettings.AppSettings["smtpserver"]; string smtpusername = ConfigurationSettings.AppSettings["smtpusername"]; string smtppassword = ConfigurationSettings.AppSettings["smtppassword"];
protected void CreateMessageAlert(System.Web.UI.Page senderPage, String alertMsg, String alertKey, string url) { String strScript = "<script language=JavaScript>alert('" + alertMsg + "');location.href('" + url + "');</script>"; if (!(senderPage.IsStartupScriptRegistered(alertKey))) { senderPage.RegisterStartupScript(alertKey, strScript); } } public void sendmail(int report,string frm, string to, string sub, string bdy, string smtpserver, string smtpusername, string smtppassword) { string DocFileName=""; DocFileName = "report.xls"; string FilePathName = Request.PhysicalPath; FilePathName = FilePathName.Substring(0, FilePathName.LastIndexOf("\\")); FilePathName = FilePathName + "\\" + DocFileName;
if (smtpusername != "" && smtppassword != "") { System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage(frm, to, sub, bdy); msg.Attachments.Add(new Attachment(FilePathName)); msg.IsBodyHtml = true; System.Net.Mail.SmtpClient smtpClient = new System.Net.Mail.SmtpClient(smtpserver); smtpClient.UseDefaultCredentials = false; smtpClient.Credentials = new System.Net.NetworkCredential(smtpusername, smtppassword); smtpClient.Send(msg); } else { System.Web.Mail.MailMessage msg = new System.Web.Mail.MailMessage(); msg.From = frm; msg.To = to; msg.Subject = sub; msg.Attachments.Add(new Attachment(FilePathName)); msg.BodyFormat = MailFormat.Html; msg.Body = bdy; SmtpMail.SmtpServer = smtpserver; SmtpMail.Send(msg); } } protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { SqlConnection sqlconn = new SqlConnection(connstr); SqlDataAdapter sqlda = new SqlDataAdapter("select * from reports", sqlconn); DataSet ds = new DataSet(); sqlda.Fill(ds, "reports"); sqlconn.Open(); ddlReportName.DataSource = ds; ddlReportName.DataTextField = "rname"; ddlReportName.DataValueField = "rcode"; ddlReportName.DataBind(); ddlReportName.Items.Add("Choose a Report"); ddlReportName.SelectedIndex = ddlReportName.Items.Count - 1; sqlconn.Close(); } } protected void ddlReportName_SelectedIndexChanged(object sender, EventArgs e) { SqlConnection sqlconn = new SqlConnection(connstr); SqlCommand sqlcomm = new SqlCommand("select rsql from reports where rcode=" + ddlReportName.SelectedValue.ToString() + " ", sqlconn); sqlconn.Open(); SqlDataReader dr; dr = sqlcomm.ExecuteReader(); string rsql = ""; while (dr.Read()) { rsql = dr.GetValue(0).ToString(); } dr.Close(); sqlconn.Close();
SqlDataAdapter sqlada = new SqlDataAdapter(rsql, sqlconn); DataSet ds = new DataSet(); sqlada.Fill(ds, "dtreports");
int report = int.Parse(ddlReportName.SelectedValue); //dgtoexcel(report); //dstoexcel(); dg2excel(ds); //DataGrid1.Visible = false; //Convert(ds,Response,"report.xls"); sendmail(report, "[email protected]", Session["userid"].ToString(), "osv-Reports", "The report is attached", smtpserver, smtpusername, smtppassword); CreateMessageAlert(this, "Report Successfully Sent.", "alertKey", "volunteermenu.aspx"); } public void dgtoexcel(int report) { string DocFileName=""; DocFileName = "report.xls"; try { //Export data from DataGrid to Excel Sample Codes //Write DataGrid1 html code to StringWriter this.DataGrid1.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); this.DataGrid1.RenderControl(hw); string HtmlInfo = tw.ToString().Trim(); string FilePathName = Request.PhysicalPath; FilePathName = FilePathName.Substring(0, FilePathName.LastIndexOf("\\")); //Get the physical address of Excel file FilePathName = FilePathName + "\\" + DocFileName; if (System.IO.File.Exists(FilePathName)) { System.IO.File.Delete(FilePathName); } FileStream Fs = new FileStream(FilePathName, FileMode.Create); BinaryWriter BWriter = new BinaryWriter(Fs); //Write the data information of DataGrid to Excel BWriter.Write(HtmlInfo); hw.Dispose(); tw.Dispose(); Fs.Flush(); BWriter.Flush(); Fs.Close(); Fs.Dispose(); BWriter.Close(); } catch { } }
void dstoexcel() { //// Create a DataSet from an XML file and retrieve an order table. //String xmlfile = Server.MapPath("files/spiceorder.xml"); //System.Data.DataSet dataset = new System.Data.DataSet(); //dataset.ReadXml(xmlfile); //System.Data.DataTable datatable = dataset.Tables["OrderItems"];
//System.Data.DataTable datatable1 = ds.Tables[0]; //// Open the template workbook, which contains number formats and //// formulas, and get an IRange from a defined name //String filename = Server.MapPath("report.xls"); //SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(filename); //SpreadsheetGear.IRange range = workbook.Names["SetDataRange"].RefersToRange;
//// Insert the DataTable into the template worksheet range. The InsertCells //// flag will cause the formatted range to be adjusted for the inserted data. //range.CopyFromDataTable(datatable1, SpreadsheetGear.Data.SetDataFlags.InsertCells);
//// Retrieve a DataSet from a defined name which includes the formatted range. //System.Data.DataSet datasetOutput = workbook.GetDataSet("GetDataRange", //SpreadsheetGear.Data.GetDataFlags.FormattedText);
// //Bind a DataGrid to the formatted DataSet //DataGrid1.DataSource = datasetOutput; //DataGrid1.DataBind(); }
void dg2excel(DataSet ds) { string DocFileName = ""; DocFileName = "report.xls"; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite); System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
//dg.DataSource = FitDataTableToExcel(ds.Tables[0]); dg.DataSource = ds.Tables[0]; dg.DataBind(); dg.RenderControl(htmlWrite);
string HtmlInfo = stringWrite.ToString().Trim();
string FilePathName = Request.PhysicalPath; FilePathName = FilePathName.Substring(0, FilePathName.LastIndexOf("\\")); //Get the physical address of Excel file FilePathName = FilePathName + "\\" + DocFileName; if (System.IO.File.Exists(FilePathName)) { System.IO.File.Delete(FilePathName); } FileStream Fs = new FileStream(FilePathName, FileMode.Create); BinaryWriter BWriter = new BinaryWriter(Fs); //BinaryWriter BWriter = new BinaryWriter(Fs, System.Text.Encoding.GetEncoding("utf-8")); //Write the data information of DataGrid to Excel BWriter.Write(HtmlInfo);
htmlWrite.Dispose(); stringWrite.Dispose(); Fs.Flush(); BWriter.Flush(); Fs.Close(); Fs.Dispose(); BWriter.Close(); } public void Convert(DataSet ds, HttpResponse response, string xlsFileName) { response.Clear(); response.AddHeader("content-disposition", "attachment;filename=" + xlsFileName); response.Charset = ""; response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite); System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
dg.DataSource = ds.Tables[0]; dg.DataBind(); dg.RenderControl(htmlWrite);
response.Write(stringWrite.ToString()); response.End(); } private DataTable FitDataTableToExcel(DataTable dt) { int ExcelRowLimit = 65534; int TotTableRowCounter = 0; int TotExcelRowCounter = 0; int TempExcelRowCounter = 0; int TableCounter = 0; DataRow dr; DataTable ExcelTempTable = new DataTable(); DataTable ExcelTable = new DataTable(); do { ExcelTempTable = dt.Clone(); TableCounter += 1; // Create Excel Temporary Table TempExcelRowCounter = 0; do { TotTableRowCounter += 1; TempExcelRowCounter += 1; dr = dt.Rows[TotTableRowCounter - 1]; ExcelTempTable.NewRow(); ExcelTempTable.ImportRow(dr); } while (TotTableRowCounter < dt.Rows.Count & TempExcelRowCounter < ExcelRowLimit); // Join Excel Temporary Table to Excel Table as columns // Create columns of Excel Table // Line No column ExcelTable.Columns.Add(new DataColumn("No [" + TableCounter.ToString() + "]", typeof(Int32))); for (int i = 0; i <= ExcelTempTable.Columns.Count - 1; i++) { ExcelTable.Columns.Add(new DataColumn(ExcelTempTable.Columns[i].ColumnName + " [" + TableCounter.ToString() + "]", ExcelTempTable.Columns[i].DataType)); } // Table seperator column ExcelTable.Columns.Add(new DataColumn("[*" + TableCounter.ToString() + "*]", typeof(String))); // Fill data into Excel Table from Excel Temporary Table int ExcelTableRow, ExcelTableCol = 0; for (ExcelTableRow = 0; ExcelTableRow <= ExcelTempTable.Rows.Count - 1; ExcelTableRow++) { try { ExcelTable.Rows[ExcelTableRow].BeginEdit(); } catch { dr = ExcelTable.NewRow(); ExcelTable.Rows.Add(dr); ExcelTable.Rows[ExcelTableRow].BeginEdit(); } // Row Number value TotExcelRowCounter += 1; ExcelTable.Rows[ExcelTableRow][(TableCounter - 1) + ((TableCounter - 1) * (ExcelTempTable.Columns.Count + 1))] = TotExcelRowCounter; // Data column's value for (ExcelTableCol = 0; ExcelTableCol <= ExcelTempTable.Columns.Count - 1; ExcelTableCol++) { int CurrenColPositon = (ExcelTableCol + 1) + ((TableCounter - 1) * (ExcelTempTable.Columns.Count + 2)); ExcelTable.Rows[ExcelTableRow][CurrenColPositon] = ExcelTempTable.Rows[ExcelTableRow].ItemArray[ExcelTableCol]; } // Seperator column's value ExcelTable.Rows[ExcelTableRow][(TableCounter - 1) + (((TableCounter - 1) * (ExcelTempTable.Columns.Count + 1)) + ExcelTempTable.Columns.Count + 1)] = " ";
ExcelTable.Rows[ExcelTableRow].EndEdit(); ExcelTable.Rows[ExcelTableRow].AcceptChanges(); } } while (TotTableRowCounter < dt.Rows.Count); return ExcelTable; }}