5

I'm working with an HTML table, that contains numbers (formated) and when I export this to xls file (just change extension... hehe) I loss some of the formated data.

Example:

in html I have " 1,000.00 | 500.00 | 20.00 " and in excel it shows like: "1,000.00 | 500 | 20"

I want it to know if it is possible to show the very same format as in html.

THankyou :P

pojomx
  • 780
  • 2
  • 11
  • 24
  • 1
    I doubt this is possible without producing an actual, native Excel file that can contain the correct number formatting rules. I can be wrong but I'm pretty sure. – Pekka Jun 08 '10 at 19:31
  • Well Excel just makes a guess based on the content seeing that you're just changing the default program that opens the file rather than anything else. I'm sure if you took a native Excel file and opened it up with your browser you'd get some funny results as well. – Jan K. Jun 08 '10 at 19:35
  • @Jan the approach is okay - Excel can deal with, and import, HTML tables. The question is whether there is some proprietary attribute that can make Excel format the columns of the table in a certain way. It could be that such an attribute exists but I don't think that is the case. – Pekka Jun 08 '10 at 19:40
  • Thank you all, Opening an HTMLExcel File, i saw some "mco-number-format" css attribute, but its not working with my HTML file excel XD – pojomx Jun 08 '10 at 19:47
  • @pojomx that sounds interesting, though! May be worth further research, the attribute is surely not there for nothing. – Pekka Jun 08 '10 at 19:48
  • Googling for the exact phrase "mco-number-format" does not yield any results. – Jan K. Jun 08 '10 at 19:51
  • http://webcache.googleusercontent.com/search?q=cache:wGvx65r-6cMJ:agoric.com/sources/software/htmltoExcel+mso-format-number&cd=1&hl=es&ct=clnk&gl=mx <- I made it work with style=mso-format-number:..., not on class :( – pojomx Jun 08 '10 at 20:45
  • in the end, i found the answer here: (today xD) http://stackoverflow.com/questions/4619909/format-html-table-cell-so-that-excel-formats-as-text?rq=1 – pojomx Aug 04 '12 at 23:34

3 Answers3

1

you can achive that by using class. for example:

first add class

writer.WriteLine("<style> .number{mso-number-format:\"\\#\\#0\\.00\";} </style>");

and then, in your iteration :

writer.Write("<td class=\"number\" >");
writer.Write(data);
writer.WriteLine("</td>");

as shown in : Export to Excel in ASP.NET, issue with decimal formatting for numbers greater than 1000

Community
  • 1
  • 1
arun.m
  • 131
  • 1
  • 3
1

I've done this. The best way to tell is to create an .xls file (not .xlsx) and then save it as an html file.

Then look at the source of the html file. You'll see some css classes at the top and then if you look at the data below you'll see them being applied to the sheet.

So just a bit of reverse engineering...

FYI - if you try to open this up in 2007 or later, you'll get an initial warning but then all works OK.

jhorback
  • 833
  • 8
  • 12
0

You can open Excel and import it as data where it will ask you for your delimiter, and the data type of each column. You can also just manually choose the columns and add formatting.

ashurexm
  • 6,209
  • 3
  • 45
  • 69