Exporting to Excel the simple way (but not CSV)_问答_开发者

Exporting to Excel the simple way (but not CSV)

开发者 https://www.devze.com 2022-12-12 03:01 出处：网络

Yesterday I found thi开发者_高级运维s How-To on exporting data to Excel spreadsheets by simply producing HTML. Seemed like a straightforward way of exporting cells with formatting, what one can not ac

My idea was to process some data in a Java app, export the results to Excel spreadsheet and make use of Excel's capabilities to further analyse the data, draw charts etc... (avoid re-implementing Excel's powerful features). However, before I export the data I wanted to color the background of some of the cells. The 'conditional formatting' feature of Excel would not suffice (plus, I don't want to do that manually). I find it rather convenient to prepare the spreadsheet with all formatting in the Java app and go from there in Excel.

The approach mentioned in the How-To, is simple and works well, except for two problems. I was hoping some of you may have previous experience with this and will be able to provide some light into this issue.

Coloring the individual cells like <td bgcolor="#ff2323"> will look exactly the way I want when I open the XLS file in Firefox, but Excel changes the colors to what I believe they call 'web-safe' color palette. Which means, instead of having 256 different shades of red, I get a 'red', 'fuchsia', 'pink', 'orange', 'light yellow' and 'white'. Is there any way to stop this color conversion from happening?
When I open the XLS file (which in reality is just HTML on the inside), all looks good but the thin grey lines separating individual cells are gone and I can't figure out how to 'turn them on'. What's with that?

Problem 1 is the main issue which I would really like to solve.

You can use the special XML formatting options to force gridlines inside the spreadsheet:

<html xmlns:o="urn:schemas-microsoft-com:office:office"
                        xmlns:x="urn:schemas-microsoft-com:office:excel"
                         xmlns="http://www.w3.org/TR/REC-html40">
<head>
<xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet</x:Name>
    <x:WorksheetOptions>
     <x:Print>
        <x:Gridlines />
     </x:Print>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
 </x:ExcelWorkbook>
</xml>
</head>                      
<body>
<table>
<tr><td bgcolor="#ff2323">hello</td></tr>
</table>
</body>
</html>

More generally, see this MSDN link for detailed documentation on this format.

I would actually expect Excel not to give you the standard Web-safe colors but rather choose from its own palette (which is even smaller; only 56 slots on "traditional" versions of Excel, perhaps 2007 has lifted this). It is possible to select your own custom colors within Excel (Tools->Options->Color in the version I use at work), but each new color you choose would replace one of the "factory" defaults.

I suspect the automatic HTML-to-Excel import just doesn't want to go through the trouble of setting up these colors for you, especially since the possibility exists that your HTML pseudo-Excel file contains more colors than Excel's palette has slots. Then it would be forced to disappoint you anyway. Excel's implementers probably decided it would be easier and simpler to just disappoint you up front. ;)

For Gridlines...