Difference between revisions of "User:Danielbw"
From Apache OpenOffice Wiki
m (→Excel 2002 / Excel XP: add Excel 2002/XP test files) |
|||
(10 intermediate revisions by one other user not shown) | |||
Line 3: | Line 3: | ||
==== Goal ==== | ==== Goal ==== | ||
: This project is in response to {{bug|5560}} | : This project is in response to {{bug|5560}} | ||
− | : To gain the functionality provided by Microsoft Excel versions 2002 and higher. Microsoft Excel 2002 introduced ''Colored Tab Support'' for Excel. This gave users the ability to color code their spreadsheet tabs. The Goal of this project is to bring this functionality to OpenOffice.org. I realize that ods does not have the capability to store this information and that this project only benefits those who save and open files in excel formats. I am interested in anyone's help to find a way to | + | : To gain the functionality provided by Microsoft Excel versions 2002 and higher. Microsoft Excel 2002 introduced ''Colored Tab Support'' for Excel. This gave users the ability to color code their spreadsheet tabs. The Goal of this project is to bring this functionality to OpenOffice.org. <strike>I realize that ods does not have the capability to store this information and that this project only benefits those who save and open files in excel formats.</strike> I am interested in anyone's help to find a way to amend OASIS File Format specs for spreadsheets support sheet tab color information. I have not spent a lot of time researching this yet. For simplicity's sake, I will start a second project for that job.<br><br> |
: '''Thanks goes to Kohei Yoshida for adding the ability to open / save ODS files with Tab Color support'''<br><br> | : '''Thanks goes to Kohei Yoshida for adding the ability to open / save ODS files with Tab Color support'''<br><br> | ||
: If code is accepted, I would like to collect on the bounty for {{bug|5560}} [[http://wiki.services.openoffice.org/wiki/BugBountyProgram/Issues Bounty Issues Page]] | : If code is accepted, I would like to collect on the bounty for {{bug|5560}} [[http://wiki.services.openoffice.org/wiki/BugBountyProgram/Issues Bounty Issues Page]] | ||
+ | |||
+ | ==== Blog Coverage ==== | ||
+ | :[http://eric.bachard.free.fr/news/ Eric Bachard] [http://eric.bachard.free.fr/news/2009/01/colored-tabs-implementation-in-calc.html Colored tabs implementation in Calc] 2009-02-23 | ||
+ | :[http://kohei.us/ Kohei Yoshida] [http://kohei.us/2009/03/07/coloring-tabs-in-calc/ Coloring tabs in Calc] 2009-03-07 | ||
==== Specifications ==== | ==== Specifications ==== | ||
Line 152: | Line 156: | ||
::Binary format only. | ::Binary format only. | ||
::File extensions supported by OpenOffice.org: '''.xls''', '''.xlt''' | ::File extensions supported by OpenOffice.org: '''.xls''', '''.xlt''' | ||
− | :: | + | ::Excel 2002/XP Test Files |
+ | ::*[[Media:calc tabcolortest xl 2002 bin.xls|calc tabcolortest xl 2002 bin.xls]] | ||
+ | ::*[[Media:calc tabcolortest xl 2002 bin.xlt.zip|calc tabcolortest xl 2002 bin.xlt.zip]] | ||
====== Excel 2003 ====== | ====== Excel 2003 ====== | ||
Line 235: | Line 241: | ||
| | | | ||
|} | |} | ||
− | + | ||
− | |||
− | |||
====== Excel 2007 ====== | ====== Excel 2007 ====== | ||
<!--:'''Excel 2007'''--> | <!--:'''Excel 2007'''--> | ||
Line 347: | Line 351: | ||
<!-- Project: ODS Spec changes... --> | <!-- Project: ODS Spec changes... --> | ||
− | === Add Tab Color Information to | + | === Add Tab Color Information to Oasis File Specs === |
---- | ---- | ||
[[User:Danielbw|Danielbw]] 01:27, 22 January 2009 (UTC) | [[User:Danielbw|Danielbw]] 01:27, 22 January 2009 (UTC) |
Latest revision as of 10:29, 21 July 2009
Projects
Calc: Tab Background Color
Goal
- This project is in response to Issue 5560
- To gain the functionality provided by Microsoft Excel versions 2002 and higher. Microsoft Excel 2002 introduced Colored Tab Support for Excel. This gave users the ability to color code their spreadsheet tabs. The Goal of this project is to bring this functionality to OpenOffice.org.
I realize that ods does not have the capability to store this information and that this project only benefits those who save and open files in excel formats.I am interested in anyone's help to find a way to amend OASIS File Format specs for spreadsheets support sheet tab color information. I have not spent a lot of time researching this yet. For simplicity's sake, I will start a second project for that job. - Thanks goes to Kohei Yoshida for adding the ability to open / save ODS files with Tab Color support
- If code is accepted, I would like to collect on the bounty for Issue 5560 [Bounty Issues Page]
Blog Coverage
- Eric Bachard Colored tabs implementation in Calc 2009-02-23
- Kohei Yoshida Coloring tabs in Calc 2009-03-07
Specifications
- TabBgColor_specs.odt
- 2009-02-01_TabBgColor_specs_QuickUXReview.odt First review by UX team.
Media:2009-02-13_TabBgColor_specs.odt Updated specs in response to review.- Media:2009-03-03_TabBgColor_specs.odt Updated specs in response to review.
Patches / Macro Sample
- Calc:Tab_Color_Patches
- Macro Sample1: Set the select sheet(s) tab color
sub SetTabColor rem ---------------------------------------------------------------------- rem define variables dim document as object dim dispatcher as object rem ---------------------------------------------------------------------- rem get access to the document If ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument" ) Then document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") rem ---------------------------------------------------------------------- dim args1(0) as new com.sun.star.beans.PropertyValue args1(0).Name = "TabBgColor" args1(0).Value = RGB(255,128,0) dispatcher.executeDispatch(document, ".uno:SetTabBgColor", "", 0, args1()) End If End Sub
- Macro Sample2: Set all sheet(s) tab color
Sub SetTabColor2 rem ---------------------------------------------------------------------- rem define variables dim document as object dim dispatcher as object rem ---------------------------------------------------------------------- rem get access to the document If ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument" ) Then document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document, ".uno:TableSelectAll", "", 0, Array()) rem ---------------------------------------------------------------------- dim args2(0) as new com.sun.star.beans.PropertyValue args2(0).Name = "TabBgColor" args2(0).Value = RGB(0,128,255) dispatcher.executeDispatch(document, ".uno:SetTabBgColor", "", 0, args2()) End If End Sub
Testing / Progress
Test File Specifications
- All Test Files Must conform to the following rules:
- Workbook must have 13 Tabs...
- 10 Worksheets, named Sheet1 through Sheet10
- 1 Chart named Chart1
- 1 Ms Excel 4.0 Macro named Macro1
- 1 Ms Excel 5.0 Dialog named Dialog1
- Test File name: calc_tabcolortest_xl_<Excel Version>_<Excel Format: Binary=bin;XML=xml>.<Excel File Extension> e.g. calc_tabcolortest_xl_2002_bin.xls for Excel 2002 binary format.
- The Thirteen Tabs need to be colored according to the table below...
Sheet Name | Sample | Color Name | Color RGB |
---|---|---|---|
Sheet1 | Black | 000000 | |
Sheet2 | White | FFFFFF | |
Sheet3 | Bright Red | FF0000 | |
Sheet4 | Bright Green | 00FF00 | |
Sheet5 | Bright Blue | 0000FF | |
Sheet6 | Bright Yellow | FFFF00 | |
Sheet7 | Magenta | FF00FF | |
Sheet8 | Cyan | 00FFFF | |
Sheet9 | Maroon | 800000 | |
Sheet10 | Green | 008000 | |
Chart1 | Blue | 000080 | |
Macro1 | Olive | 808000 | |
Dialog1 | Purple | 800080 |
Results / Progress
Excel 2002 / Excel XP
- Binary format only.
- File extensions supported by OpenOffice.org: .xls, .xlt
- Excel 2002/XP Test Files
Excel 2003
- Binary Format and OOXML formats.
- File extensions supported by OpenOffice.org
- Binary formats: .xls, .xlt
- OOXML formats: .xml (XML Spreadsheet)
- Excel 2003 Test Files
- YouTube Video Demo of Excel 2003 Tabbed Color Support
- Testing Status
File Type | Test | Result | Comments |
---|---|---|---|
.xls, .xlt (Binary) | Import color index for Worksheet tabs | OK | |
.xls, .xlt (Binary) | Import color index for Chart tabs | OK | |
.xls, .xlt (Binary) | Import color index for MS Excel 4.0 Macro tabs | OK | |
.xls, .xlt (Binary) | Import Color index for MS Excel 5.0 Dialog tabs | OK | |
.xls, .xlt (Binary) | Export color index for Worksheet tabs | OK | |
.xls, .xlt (Binary) | Export color index for Chart tabs | OK | |
.xls, .xlt (Binary) | Export color index for MS Excel 4.0 Macro tabs | OK | |
.xls, .xlt (Binary) | Export Color index for MS Excel 5.0 Dialog tabs | OK |
File Type | Test | Result | Comments |
---|---|---|---|
.xml (XML Spreadsheet) | Import color index for Worksheet tabs | OK | I had to reconfigure my build with: --with-system-saxon --with-system-libxslt for this to work. See Issue 99098 |
.xml (XML Spreadsheet) | Export color index for Worksheet tabs | OK |
Excel 2007
- Binary Format and OOXML formats.
- File extensions supported by OpenOffice.org
- OOXML formats: .xlsx, .xlsm, .xltx, .xltm
- Binary formats: .xlsb
- Download Excel 2007 Test Files -> http://wiki.services.openoffice.org/w/images/3/30/Excel_2007_colortabs.zip
- Testing Status
File Type | Test | Result | Comments |
---|---|---|---|
.xlsx .xlsm .xltx .xltm (OOX) | Import color index for Worksheet tabs | OK | |
.xlsx .xlsm .xltx .xltm (OOX) | Import color index for Chart tabs | OK | |
.xlsx .xlsm .xltx .xltm (OOX) | Import color index for MS Excel 4.0 Macro tabs | OK | |
.xlsx .xlsm .xltx .xltm (OOX) | Import color index for MS Excel 5.0 Dialog tabs | OK | Had to add dialogsheet support to the OoxWorksheetFragment::onCreateContext method. |
.xlsx .xlsm .xltx .xltm (OOX) | Export color index for Worksheet tabs | Not Ready | No save support in OpenOffice.org |
.xlsx .xlsm .xltx .xltm (OOX) | Export color index for Chart tabs | Not Ready | No save support in OpenOffice.org |
.xlsx .xlsm .xltx .xltm (OOX) | Export color index for MS Excel 4.0 Macro tabs | Not Ready | No save support in OpenOffice.org |
.xlsx .xlsm .xltx .xltm (OOX) | Export color index for Dialog tabs | Not Ready | No save support in OpenOffice.org |
File Type | Test | Result | Comments |
---|---|---|---|
.xlsb (Binary) | Import color index for Worksheet tabs | OK | |
.xlsb (Binary) | Import color index for Chart tabs | OK | |
.xlsb (Binary) | Import color index for MS Excel 4.0 Macro tabs | OK | |
.xlsb (Binary) | Import color index for MS Excel 5.0 Dialog tabs | OK | |
.xlsb (Binary) | Export color index for Worksheet tabs | Not Ready | No save support in OpenOffice.org |
.xlsb (Binary) | Export color index for Chart tabs | Not Ready | No save support in OpenOffice.org |
.xlsb (Binary) | Export color index for MS Excel 4.0 Macro tabs | Not Ready | No save support in OpenOffice.org |
.xlsb (Binary) | Export color index for MS Excel 5.0 Dialog tabs | Not Ready | No save support in OpenOffice.org |
Add Tab Color Information to Oasis File Specs
Danielbw 01:27, 22 January 2009 (UTC)