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;
}
}