User:Danielbw

From Apache OpenOffice Wiki
Revision as of 18:11, 12 March 2009 by Danielbw (Talk | contribs)

Jump to: navigation, search

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 Tab Colors
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
Testing Status: Cannot test. Test files are not available. If you own Excel 2002/XP, then please spend two minutes generating the test files according to the specifications above and add them to this wiki ;)
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
Excel 2003 Binary Test Results
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
Excel 2003 OOXML Test Results
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
Excel 2007 OOXML Results
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
Excel 2007 Binary Test Results
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 ODS


Danielbw 01:27, 22 January 2009 (UTC)

Personal tools