Export to Excell

jha_arindam · November 22nd, 2005, 02:17 AM

Hi
I am trying to export my datagrid values to my excell sheet. All are fine except one thing. Some values are enclosed with round brackets like (3.2) in my database as well as in my datagrid. But when I export it to excell it replaces round brackets with the negative sign. I use the following code to initialize the property to set it as in round bracket. But I don't know somehow it is not working. The code is....
public void datagridToExcell(DataGrid dgExport,HttpResponse response)
    {
            response.Clear();
            response.Buffer = true;
            response.Charset = "";
            response.Cache.SetCacheability(HttpCacheability.No Cache);
            response.ContentType = "application/vnd.ms-excel";
            this.EnableViewState = false;
            response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
            response.Write("\r\n");
            response.Write(@"<style>.NumberString {mso-number-format:\@;}</style>");
            StringWriter objStrWriter = new StringWriter();
            HtmlTextWriter objHTMLTextWriter = new HtmlTextWriter(objStrWriter);
            DataGrid dg = new DataGrid();
            dg = dgExport ;
            dg.GridLines = GridLines.None;
            dg.HeaderStyle.Font.Bold = true;
            dg.DataBind();
            for (int rowPos = 0; rowPos < dg.Items.Count; rowPos++)
            {
                for (int colPos = 0; colPos < dg.Items[rowPos].Cells.Count; colPos++)
                {
                    dg.Items[rowPos].Cells[colPos].Attributes.Add("class","NumberString");
                }
            }
            dg.RenderControl(objHTMLTextWriter);
            response.Write(objStrWriter.ToString());
        }
If possible please any one help me. I am using Visual Studio .NET 1.1 and Microsoft Office 2003. I want to set the bracketed figuers with the bracket in red color. Yor can see this property in excel. Just go to the format option in the cell and then the number option.
Thanks in advance.....

Arindam Jha

Ankur_Verma · November 22nd, 2005, 08:52 AM

Thats happening because values withing parantheses, by conventions, are taken as negative for accounting calculations etc. Set the NumberFormat property of the said cell to "#,##0.00_);[Red](#,##0.00)" from your program code. This format string will leave the positive numbers alone but turn the negative numbers red. Change the padding or formating as you please.

Regards
Ankur Verma