How to get all the changed columns in a sheet

The Application Programming Interface and the OASIS Open Document Format

How to get all the changed columns in a sheet

Postby libyenter » Thu Dec 27, 2012 11:20 am

Hi, guys

Thanks in advance. Let's talk about my problem.

An effective way can get all the changed columns in a sheet.

Define: the changed columns, the column is hidden or the its width is changed, not as the same as the default width.

I knew a way to walk through all the columns in the sheet and check the "IsVisible" and "Width", but it's a slow way, even there is only one column is changed in that sheet, I have to check all 1024 columns.
XColumnRowRange xColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSheet);
XTableColumns xColumns = xColRowRange.getColumns();
//here I can loop the xColumns and check the "Width" and "IsVisible" property by XProperty to find the changed columns

Also, I searched another way on Internet,

XSheetCellCursor xCursor = xSheet.createCursor();
XUsedAreaCursor xUsedCursor = (XUsedAreaCursor) UnoRuntime.queryInterface(XUsedAreaCursor.class, xCursor);
XColumnRowRange xCRRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xUsedCursor);
XTableColumns xColumns = xCRRange.getColumns();
The above code can help me to narrow the columns, but it has some problems. See the attached picture "Sample.PNG".

In the picture, if I use the above code, I will get column B:E, but the right result I need is column E(width changed), H(hidden) and I(width changed), because those are the columns hidden or width changed.

Does anyone know a fast way to get all the changed columns in a sheet?

OpenOffice 3.4 on Windows XP
Posts: 3
Joined: Thu Dec 27, 2012 10:52 am

Re: How to get all the changed columns in a sheet

Postby libyenter » Mon Jan 07, 2013 10:01 am

Come on, somebody help me, it has been a week
OpenOffice 3.4 on Windows XP
Posts: 3
Joined: Thu Dec 27, 2012 10:52 am

Re: How to get all the changed columns in a sheet

Postby ms777 » Fri Jan 11, 2013 1:14 am

libyenter wrote:Come on, somebody help me, it has been a week

... and it may take a week to program what you want ...

There is AFAIK (and I am quite sure) no API function you can use. You can access the UniqueCellFormatRanges, but they do not contain info about the column width

The column width is stored inside the content.xml in your file. You can unzip it and view in any xml viewer and see the structure.

You can access it by code with below example. Of course you have to write the real startElement and endElement functions yourself ...

Good luck,


Code: Select all   Expand viewCollapse view
Sub Main
  oStorage = ThisComponent.DocumentStorage.getByName("content.xml")

  oSaxParser = createUnoService( "" ) 

  oDocEventsHandler = CreateUnoListener( "DocHandler_", "" )
  oSaxParser.setDocumentHandler( oDocEventsHandler )
  oInputSource = createUnoStruct( "" )
  With oInputSource
    .aInputStream = oStorage.InputStream   ' plug in the input stream
  End With
  oSaxParser.parseStream( oInputSource )
End Sub


'   Xml Sax document handler.
Private goLocator As Object
Private glLocatorSet As Boolean

Function CreateDocumentHandler()
  glLocatorSet = False
  CreateDocumentHandler() = oDocHandler
End Function

'   Methods of our document handler call these
'    global functions.
'   These methods look strangely similar to
'    a SAX event handler.  ;-)
'   These global routines are called by the Sax parser
'    as it reads in an XML document.
'   These subroutines must be named with a prefix that is
'    followed by the event name of the interface.

Sub DocHandler_characters( cChars As String )
  if xNode = "lipsum" then
    cChars= Left(cChars,len(cChars)-1)
    if len(cChars)>1 then
      cChars= cChars+ Chr$(13)
    WriteLoremipsum (cChars, oWrite)
End Sub

Sub DocHandler_ignorableWhitespace( cWhitespace As String )
End Sub

Sub DocHandler_processingInstruction( cTarget As String, cData As String )
End Sub

Sub DocHandler_startDocument()
  Print "Start document"
End Sub

Sub DocHandler_endDocument()
  Print "End document"
End Sub

Sub DocHandler_startElement( cName As String, oAttributes As )
  Print "Start element............", cName
  xNode = cName
End Sub

Sub DocHandler_endElement( cName As String )
  'Print "End element", cName
End Sub

Posts: 138
Joined: Mon Oct 08, 2007 1:33 am

Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 2 guests