Saqib

Saqib

  • NA
  • 1
  • 0

How to fix Garbage Char in xls row 1?

Dec 8 2006 2:22 PM

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