Export to excel not showing format for character less than 6 [Resolved]

Posted by Alok under C# on 3/24/2015 | Points: 10 | Views : 2296 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi,

i have used ("#,##0.00) format to show price 10000 as 10,000.00
It show good format in Grid but when i export this to Excel then this value is shown as 10000 not as 10,000.00.

however if the character length is greater than 5 digits (10,0000) then both in grid and export to excel it shows good format
as 10,0000.00

So please provide me good solution so that in Excel i can also see good format for character less than 6 digits also.

Thanks & Regards,
Alokssm

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Alok on: 4/8/2015 [Member] Starter | Points: 25

0	This is Solution, public void ExportToExcel(ref string html, string fileName) { html = html.Replace(">", ">"); html = html.Replace("<", "<"); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls"); string style = "<style> .textmode { mso-number-format:\"\\#\\,##0\\.00\"; } </style>"; Response.Write(style); HttpContext.Current.Response.Write(html); HttpContext.Current.Response.End(); } Thanks & Regards, Alokssm Alok, if this helps please login to Mark As Answer. \| Alert Moderator

This is Solution,
public void ExportToExcel(ref string html, string fileName)
{

html = html.Replace(">", ">");
html = html.Replace("<", "<");
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
string style = "<style> .textmode { mso-number-format:\"\\#\\,##0\\.00\"; } </style>";
Response.Write(style);
HttpContext.Current.Response.Write(html);
HttpContext.Current.Response.End();
}

Thanks & Regards,
Alokssm

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Yesi on: 3/25/2015 [Member] Starter | Points: 25

0	Seem you have to format the numbewrs,refer this link https://social.msdn.microsoft.com/Forums/en-US/407e59ae-08bc-4b05-a086-c719ffd8212b/formatting-data-in-a-gridview?forum=csharpgeneral e.Row.Cells(i).Text = String.Format("{0:c}", Double.Parse(e.Row.Cells(i).Text)) ; as to export data,see here:http://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html /connect database OleDbConnection connection = new OleDbConnection(); connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password=" OleDbCommand command = new OleDbCommand(); command.CommandText = "select * from parts"; DataSet dataSet = new System.Data.DataSet(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection); dataAdapter.Fill(dataSet); DataTable t = dataSet.Tables[0]; //export datatable to excel Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; sheet.InsertDataTable(t, true, 1, 1); book.SaveToFile("insertTableToExcel.xls"); System.Diagnostics.Process.Start("insertTableToExcel.xls"); Alok, if this helps please login to Mark As Answer. \| Alert Moderator

Seem you have to format the numbewrs,refer this link
https://social.msdn.microsoft.com/Forums/en-US/407e59ae-08bc-4b05-a086-c719ffd8212b/formatting-data-in-a-gridview?forum=csharpgeneral

e.Row.Cells(i).Text = String.Format("{0:c}", Double.Parse(e.Row.Cells(i).Text)) ;

as to export data,see here:http://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html

            /connect database
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
            OleDbCommand command = new OleDbCommand();
            command.CommandText = "select * from parts";
            DataSet dataSet = new System.Data.DataSet();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
            dataAdapter.Fill(dataSet);
            DataTable t = dataSet.Tables[0];
            //export datatable to excel
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            sheet.InsertDataTable(t, true, 1, 1);
            book.SaveToFile("insertTableToExcel.xls");
            System.Diagnostics.Process.Start("insertTableToExcel.xls");

Alok, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Alok on: 3/26/2015 [Member] Starter | Points: 25

0	There is no issue in showing formatted data in Gridview. Problem comes when we Export this grid to ExCel. In Excel if any value is less than 6 then it does not show me data in the same format as data in Gridview. Thanks & Regards, Alokssm Alok, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts