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

Posted by Alok under C# on 3/24/2015 | Points: 10 | Views : 702 | Status : [Member] | Replies : 3
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



Responses

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

Up
0
Down

Resolved

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

html = html.Replace(">", ">");
html = html.Replace("&lt;", "<");
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

Up
0
Down
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

Up
0
Down
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

Login to post response