Export data to OpenOffice Calc and MS Office Excel

[danielmaximiliano]

*****************

[mol]

no, I didn't found solution....
Sorry for my delay, but I'm on the short vacation in Egipt.
Best regards,Marek

[danielmaximiliano]

Hi Mol :

The method Paste () belongs to the Excel API, I think that this method supports the range enclosed in parentheses
example: Paste (Rango. ..)
I did not find similar in the API OpenOffice Calc.

TextCrawler.jpg (125.04 KiB) Viewed 4969 times

[danielmaximiliano]

Hi Mol:
Paste and copy methods do not exist in Open Office, have another Wooden structure for handling of objects called

DataTransfer
http://www.openoffice.org/api/docs/comm ... le-ix.html

http://www.openoffice.org/api/docs/comm ... plier.html

http://blog.oio.de/2010/10/27/copy-and- ... e-org-api/

[Rathinagiri]

Hi Marek,

I think I have found the solution. Please find the following code useful for you.

Code: Select all

   LOCAL oServiceManager, oDesktop, oDoc, oSheet, oCtrl, oDispatcher
   IF ( oServiceManager := win_oleCreateObject( "com.sun.star.ServiceManager" ) ) != NIL
      oDesktop := oServiceManager:createInstance( "com.sun.star.frame.Desktop" )
      oDispatcher = oServiceManager:createInstance ( "com.sun.star.frame.DispatchHelper" )
      oDoc := oDesktop:loadComponentFromURL( "private:factory/scalc", "_blank", 0, {} )
   ELSE
      msginfo( "Error. OpenOffice not available.", win_oleErrorText() )
   ENDIF
   oCtrl := oDoc:getCurrentController()
   oSheet := oDoc:getSheets:getByIndex( 0 )      
   oCell := oSheet:getCellByPosition( 0, 0 )
   oCtrl:Select( oCell )
   system.clipboard := 'Rathinagiri' + chr( 9 ) + '123'
   oDispatcher:executeDispatch( oCtrl, ".uno:Paste", "", 0, { } ) 
      return nil

[mol]

Many thanks Rathi for your help!
I'm worry about Import window, if user can select right options. It wold be better to do import automatically.
Maybe somewhere the solution is described....


I want to share with my tests with setting borders in Excel.
Simply way is to write code:

Code: Select all

  cRange := "A1:"+chr(asc("A")+nColumnCount) +ltrim(str(nRowCount))
  oSheet:Range(cRange):Borders():LineStyle := xlContinuous

It works fine under one condition - Column count is less or equal 26 (number of letters in alphabet).
When nColumnCount is greater than 26 - it causes runtime error.

To avoid this problem, you can use below code

Code: Select all

  oSheet:Range(oSheet:Cells(1, 1),oSheet:Cells(nRowCount, nColumnCount)):Borders():LineStyle := xlContinuous 

I've spent some time to search thi solution. I hope it will be useful for you.
Regards, Marek

[Rathinagiri]

That's why I don't ever use the cell name. Instead we can use the row id and column id.

[mol]

That's why I don't ever use the cell name. Instead we can use the row id and column id.

But, If you want to use range, you need to present it as "A1:B10" for expample.

[mol]

Hi guys!
I want to refresh this topic, because everyone knows how to export data to open/libre office, but I'm looking solution for formatting created sheet.
There is no problem with coloring sheet, bu, I can't find how to set border lines.
I'm trying such a code:

Code: Select all

				BasicBorder := TOleAuto():New("com.sun.star.table.BorderLine")
				oBorder := oSheet:getCellRangeByPosition(nIndeksKolumny,0,nIndeksKolumny,nIleWierszy*(nStrona+1)):TableBorder
				
				//BasicBorder:SetPropertyValue("Color", RGB(255,0,0))
				BasicBorder:InnerLineWidth := 100
				BasicBorder:OuterLineWidth := 100
				BasicBorder:LineDistance := 200
				oBorder:LeftLine = BasicBorder
				oBorder:TopLine = BasicBorder
				oBorder:RightLine = BasicBorder
				oBorder:BottomLine = BasicBorder
				oSheet:getCellRangeByPosition(4*i-2,0,4*i-2,nIleWierszy*(nStrona+1)):TableBorder := oBorder

but after first line:

Code: Select all

BasicBorder := TOleAuto():New("com.sun.star.table.BorderLine")

BasicBorder is set to NIL and program crashes.

Do you have any idea?

[mol]

Hi guys!
After searching over few forums, I found solution to set border lines of some cells in open/libre office calc.
I want to share this solution with us, maybe it will be useful for someone:

1. First, we need to create/open document, after that we need to create BorderLine objects

Code: Select all

oLineBorder := oServiceManager:Bridge_GetStruct("com.sun.star.table.BorderLine")
				oLineBorder:Color := RGB(0, 0, 0)
				oLineBorder:InnerLineWidth = 0
				oLineBorder:OuterLineWidth = 10	// było 26
				oLineBorder:LineDistance = 0	// było 24
				
				// this border will be used for horizontal and vertical lines inside selected area
				oLineBorder1 = oServiceManager:Bridge_GetStruct("com.sun.star.table.BorderLine")
				//here, we can declare color of lines, remember  RGB(nBlue, nGreen, nRed)
				oLineBorder1:Color = RGB(0, 0, 0)
				oLineBorder1:InnerLineWidth = 0
				oLineBorder1:OuterLineWidth = 10
				oLineBorder1:LineDistance = 0

				oBorder = oServiceManager:Bridge_GetStruct("com.sun.star.table.TableBorder")
				oLineBorder:OuterLineWidth = 10
				oLineBorder:InnerLineWidth = 0
				//oLineBorder:LineDistance = 30
				//oLineBorder:Color = 0

				oBorder:IsTopLineValid = 1
				oBorder:IsBottomLineValid = 1
				oBorder:IsLeftLineValid = 1
				oBorder:IsRightLineValid = 1
				oBorder:IsHorizontalLineValid = 1
				oBorder:IsVerticalLineValid = 1

				//Outer lines of selected area
				oBorder:TopLine = oLineBorder
				oBorder:BottomLine = oLineBorder
				oBorder:LeftLine = oLineBorder
				oBorder:RightLine = oLineBorder
				
				//Inner lines
				oBorder:HorizontalLine = oLineBorder1
				oBorder:VerticalLine = oLineBorder1

2. When we set parameters of borders, we can apply them to selected area:

Code: Select all

			oSheet:getCellRangeByPosition(nStartCol, nStartRow, nEndCol, nEndRow):TableBorder := oBorder

Aft3. er that, region described by nStartCol, nStartRow, nEndCol, nEndRow will get border lines!

4. When you want to create double line border of cell, you can combine with OuterLineWidth, InnerLineWidth and LineDistance.
These parameters are described in openOffice documentation:
http://www.openoffice.org/api/docs/comm ... rLine.html


Nice testing!