Bug 49066 - [PATCH] worksheet/cell formatting, with view and HTML converter
Summary: [PATCH] worksheet/cell formatting, with view and HTML converter
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2010-04-07 21:51 UTC by Ken Arnold
Modified: 2015-08-18 16:01 UTC (History)
3 users (show)

Patch and new files (338.32 KB, application/x-bzip2)
2010-04-07 21:51 UTC, Ken Arnold

Note You need to log in before you can comment on or make changes to this bug.
Description Ken Arnold 2010-04-07 21:51:27 UTC
Created attachment 25244 [details]
Patch and new files

This patch has updated org.apache.poi.hssf.contrib.view.SViewer to include the ability to handle most border types, cell formats, and conditional formatting.

The cell formats include almost all formats as excel understands them for numbers, dates, and strings.  The only format parts that aren't handled are those that require the width of the cell in characters.  These classes are separated out so they can be used in other contexts (see below).

The viewer panel and the JTable for showing the spreadsheet are built as separate components that can be used in other applications.

The tests for excel formats are done by comparison to the actual results of using the formats in an excel spreadsheet — that is, the tests compare the results of the formatting with a row in a spreadsheet that formats the same number according to the same format.

The same is done for conditional formatting.

Besides the view, a new example is provided that converts an spreadsheet into HTML (read-only).  This can be included in any HTML page.

(The formatting stuff in Excel is very picky and very poorly documented, but I tried to be as exhaustive as I could be.)

[Hm.  The instruction say to upload both patch and tar files, but bugzilla seems to be allowing me to only upload one file.  I have therefore put the "diff.txt" file into the tar.]
Comment 1 Yegor Kozlov 2010-05-10 12:14:30 UTC
Thanks for the excellent patch. Sorry it took so long to review and apply.

I committed it in r942809 with some tweaks:

 (1) The poi-contrib module is obsolete. Eventually, all its contents will be repackaged and the module itself will be zapped. 

 I put org.apache.poi.ss.format.* in poi-main, it is a general-purpose code that can be re-used across POI. ToHtml and SViewer were moved to poi-examples. 
 (2) SVSheetTable#setupScroll(JScrollPane scroll) did not compile. The problem line was 
    SVRowHeader rowHeader = new SVRowHeader(sheet, this);

The constructor of SVRowHeader takes 3 arguments and I changed it to 

    SVRowHeader rowHeader = new SVRowHeader(sheet, this, 0);

Please confirm that it makes sense.

 (3) I added the Apache Licence Header to almost all new classes. ASF header is a must. 

 (4) org.apache.poi.ss.format.CellNumberFormatter depends on a class from Apache Commons-Math. I would rather avoid dependency on the Commons-Math jar and created an inner class CellNumberFormatter.Fraction based on org.apache.commons.math.fraction.Fraction. When POI uses more classes from Commons-Math then I'm OK to drop this inner class and use Commons-Math, but for a single dependency and I would rather duplicate code. 

 (5) The preferred way of constructing Workbook from the given InputStream is to use WorkbookFactory.create(InputStream in). I changed ToHtml to use WorkbookFactory and removed ToHtml#createXSSF and ToHtml#createHSSF. 

 (6) By convention, names of test classes start with Test*, the build script runs junit only for files matching "**/Test*.java" . I renamed classes in org.apache.poi.ss.format.* to follow this convention.

Other than that, very cool! Thanks again.  

Comment 2 Ken Arnold 2010-05-13 11:53:20 UTC
Yes, that all looks fine.  Thanks!
Comment 3 Javen O'Neal 2015-08-18 16:01:30 UTC
I've opened bug 58254 because of an inconsistency between the documented behavior and the actual behavior.

Ken/Yegor, since you are the authors of this code, do you have a preference towards resolving bug 58254?