[Solved] Copy from SpreadSheet to text document

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

[Solved] Copy from SpreadSheet to text document

Post by sushantmalviya »

Hello everyone.

Well, I'm new to this forum and in Apache open office world too so in case if I'm not on the correct platform for this kind of post please do let me know.

I need help from your expertise to copy the data( from identified cell and sheet) to Text document, also with some extra text, Please see the below details for the same.

I have one spreadsheet having HEADER data in FIRST three columns named as "Step number", "Description", "Expected Result" and then I have data for these header columns like Step 1, Step description, expected result respectively.
I don't know how many rows of data under this header as it may depends upon the test case scenario .

I need to build a macro which will do the following function
1. Copy header title "Step Number" then ":" then the step description following.
2. Next line,Copy header title "description" then ":" then the description following.
3. Next line,Copy header title "Expected Result" then ":" then the description following.
4: Next line, only title "Actual Result"
5. Then Page insert( to write in new page in text document) , repeat the steps 1,2,3,4 and 5 for next data till the end of the spreadsheet.

format
Step Number : step number
Description: description
Expected Result: expected result
Actual Result:

Page insert
then again for next row of spreadsheet
Step Number : step number
Description: description
Expected Result: expected result
Actual Result:

Please help me as i need it badly to reduce the test execution time .
Many Thanks
Sushant
Last edited by Hagar Delest on Thu Jan 31, 2019 3:26 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 3.1
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Copy from SpreadSheet to text document

Post by keme »

Typically you would use a mail merge process for this. No macro programming required. However, you need to register your spreadsheet as a database source first. Then you activate mailmerge, create a template page with required layout and drag the data fields into place, and run the merge.

Posting from my phone so I can't provide detailed steps ATM, only this rough guide, sorry!
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Post by JeJe »

Here is a macro method though.

Code: Select all

'PUT THIS CODE IN A MODULE IN YOUR BLANK WRITER DOCUMENT AND RUN "MAIN"
'ASSUMES USE OF SHEET(0) AND COLUMNS 0 TO 3 AND DATA STARTS AT ROW 1 (HEADINGS ROW 0)
'CHANGE THESE VALUES IF NOT
'STOPS AT THE FIRST EMPTY ROW
Sub Main
	dim ovc,calcdoc
	calcdoc = getCalcDoc("C:\tmp\Untitled 3.ods") 'REPLACE WITH THE PATH TO YOUR CALC FILE
	ovc = thiscomponent.currentcontroller.viewcursor
	sts= array("Step Number:","description:" ,"Expected Result:","Actual Result:"
	with calcdoc.sheets(0)
	rowno = 1
		do
			for columnNo = 0 to 3
				if columnNo<>3 then
				cellstring = .getCellByPosition(columnNo,rowNo).string
				if cellstring = "" then exit do
				else
				cellstring = ""
				end if
				ovc.string= sts(columnNo) &  cellstring
				ovc.collapsetoend
				thiscomponent.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
				ovc.collapsetoend
			next
		OvC.BreakType = com.sun.star.style.BreakType.PAGE_BEFORE
		rowno = rowno+1
		loop
	end with
End Sub

	'modified from https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=87507
function getCalcDoc(pth)
	FileURL = convertToURL(pth)
	Components = StarDesktop.getComponents()
	CompEnum = Components.createEnumeration
	While CompEnum.hasMoreElements
		OneComp = CompEnum.nextElement
		If OneComp.URL = FileURL Then
			getCalcDoc = OneComp
			exit function
		End If
		Wend
		getCalcDoc = StarDesktop.loadComponentFromURL(FileURL, "_blank", 0, Array())
end function

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Post by JeJe »

Oops there was a flaw in the above code - if the column number wasn't zero it wouldn't work properly.
This is the problem with macros.
The below is simpler if more long winded and should be easy to follow.

Code: Select all



'PUT THIS CODE IN A MODULE IN YOUR BLANK WRITER DOCUMENT AND RUN "MAIN"
'ASSUMES USE OF SHEET(0) AND COLUMNS 0 TO 3 AND DATA STARTS AT ROW 1 (HEADINGS ROW 0)
'CHANGE THESE VALUES IF NOT
'STOPS AT THE FIRST EMPTY ROW
Sub Main
	dim ovc,calcdoc,pth as string,sheetno

	pth= "C:\tmp\Untitled 3.ods" 'REPLACE WITH THE PATH TO YOUR CALC FILE
	sheetno = 0 'sheet no
	rowno = 1 'set row no
	columnNo = 0 'set column no


	calcdoc = getCalcDoc(pth)
	ovc = thiscomponent.currentcontroller.viewcursor

	with calcdoc.sheets(sheetno)
		do
			
				cellstring = .getCellByPosition(columnNo,rowNo).string
				if cellstring = "" then exit do
			
				ovc.string= "Step Number:" &  cellstring
				ovc.collapsetoend
				thiscomponent.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
				ovc.collapsetoend
				
				cellstring = .getCellByPosition(columnNo + 1,rowNo).string				
				ovc.string= "Description:" &  cellstring
				ovc.collapsetoend
				thiscomponent.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
				ovc.collapsetoend
				
				cellstring = .getCellByPosition(columnNo + 2 ,rowNo).string
				ovc.string= "Expected Result:" &  cellstring
				ovc.collapsetoend
				thiscomponent.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
				ovc.collapsetoend
				
				
				ovc.string= "Actual Result:"
				ovc.collapsetoend
				thiscomponent.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
				ovc.collapsetoend

		OvC.BreakType = com.sun.star.style.BreakType.PAGE_BEFORE
		rowno = rowno+1
		loop
	end with
End Sub

	'modified from https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=87507
function getCalcDoc(pth)
	FileURL = convertToURL(pth)
	Components = StarDesktop.getComponents()
	CompEnum = Components.createEnumeration
	While CompEnum.hasMoreElements
		OneComp = CompEnum.nextElement
		If OneComp.URL = FileURL Then
			getCalcDoc = OneComp
			exit function
		End If
		Wend
		getCalcDoc = StarDesktop.loadComponentFromURL(FileURL, "_blank", 0, Array())
end function

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Thank you so much JeJe...I will try both the methods as suggested.
OpenOffice 3.1
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Thank you Keme for the suggestion.
OpenOffice 3.1
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Hi Jeje,

I tried you code , but its getting an error -
An exception occured
Type:com.sun.star.lang.IllegalArgumentException
Message: URL seems to be an unsupported one..

pth in the macro - "pth= "C:\ExportCHG2003593.ods"

Please help
OpenOffice 3.1
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Copy from SpreadSheet to text document

Post by RoryOF »

Make an empty file with that name and see how the macro works then.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Thanks RoryOF for quick reply, unfortunately I can't make this file blank as this file contains the data which will be copy to text writer document.
OpenOffice 3.1
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Copy from SpreadSheet to text document

Post by RoryOF »

Make a blank file before running the macro, so that a file exists when the macro wants to write into it.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Actually File do exists with the data in the path specified.
Also tried, I have kept one blank file in path and get below error message
Property or method not found: viewcursor
OpenOffice 3.1
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Post by JeJe »

"Property or method not found: viewcursor"

The code uses "thiscomponent" which refers to the last active document before the macro was called... make sure your spreadsheet is active before you call the macro eg call the macro from the spreadsheet menu or click in the spreasheet go back to the IDE and press F5.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Now the view cursor is not appearing on the screen but back with an old error message -

getCalcDoc = StarDesktop.loadComponentFromURL(FileURL, "_blank", 0, Array()) ---- URL seems to be unsupported one
OpenOffice 3.1
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Not sure where i'm commiting mistake :(
OpenOffice 3.1
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Post by JeJe »

Put the code in a blank Writer document, not the calc one, and make the Writer document active (Sorry, ignore my last post).
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Post by JeJe »

Here's a version that will run from your Calc document.

Code: Select all


'PUT THIS CODE IN A MODULE IN YOUR CALC DOCUMENT AND RUN "MAIN"
'ASSUMES USE OF SHEET(0) AND COLUMNS 0 TO 3 AND DATA STARTS AT ROW 1 (HEADINGS ROW 0)
'CHANGE THESE VALUES IF NOT
'STOPS AT THE FIRST EMPTY ROW
Sub Main
   dim ovc,calcdoc,pth as string,sheetno

	NewDoc = StarDesktop.loadComponentFromURL("private:factory/swriter","_blank",0,Array())


   sheetno = 0 'sheet no
   rowno = 1 'set row no
   columnNo = 0 'set column no


   calcdoc = Thiscomponent
   ovc = newdoc.currentcontroller.viewcursor

   with calcdoc.sheets(sheetno)
      do
         
            cellstring = .getCellByPosition(columnNo,rowNo).string
            if cellstring = "" then exit do
         
            ovc.string= "Step Number:" &  cellstring
            ovc.collapsetoend
            Newdoc.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
            ovc.collapsetoend
            
            cellstring = .getCellByPosition(columnNo + 1,rowNo).string            
            ovc.string= "Description:" &  cellstring
            ovc.collapsetoend
            newdoc.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
            ovc.collapsetoend
            
            cellstring = .getCellByPosition(columnNo + 2 ,rowNo).string
            ovc.string= "Expected Result:" &  cellstring
            ovc.collapsetoend
            newdoc.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
            ovc.collapsetoend
            
            
            ovc.string= "Actual Result:"
            ovc.collapsetoend
            newdoc.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
            ovc.collapsetoend

      OvC.BreakType = com.sun.star.style.BreakType.PAGE_BEFORE
      rowno = rowno+1
      loop
   end with
End Sub


Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy from SpreadSheet to text document

Post by Villeroy »

Simple and more flexible than a macro:
menu:File>New>Database...
[X] Connect to existing database
Type: Spreadsheet
Specify your spreadsheet
[X] Register your database (share data with other documents)
Save the database document.
Your database-like list should appear as a database table.
Under Tools>Table Filter you may hide non-relevant sections of your spreadsheet document.
Save, close and forget the databsae document for now but don't move it.
Nothing has been copied, converted, imported. The data are still in your spreadsheet and only a spreadsheet program can edit your data. This Base document is just a database-like view on that spreadsheet.
------------------------------------
Open your Writer document
Call menu:Edit>Change database... and point to the right table in your database.
Insert database fields where you want to fill in database content. menu:Insert>Fields>Other... tab:Database
alterntaive method: Hit F4 for the data source window, open your table and drag column headers into your document.
----------------------------------
When you print the document, you will be prompted if you are going to print a serial letter. Answer yes, and you get one document for each row in your databsae table.
-----------------------------------
If you want to print only one instance of the document with one record, hit F4 for the data source window, open your table, click a row selector (grey box in front of a row), klick the "data to fields" button.
----------------------------------
Off topic: if you want to import the table into another spreadsheet, hit F4 in Calc and drag a table icon from the left pane into a spreadshet.
It is very easy to store a filtered and/or sorted view on the database table. Open the database document and create a query. A query can be used exactly like a table.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Sorry Jeje , I tried but end with the same error.
This time I kept the code in the blank writer document,writer document is open and active, CALC file path name mentioned in the code that file is open and then run the code but no luck.
OpenOffice 3.1
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Post by JeJe »

The modified code I posted to run from the Calc document doesn't use the function that loads a document - it just creates a blank Writer document - so if you try that one.

Changing the values to your sheet, and the row and column no of the first cell of your data.

sheetno = 0 'sheet no
rowno = 1 'set row no
columnNo = 0 'set column no
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Thanks JeJe..I tried the updated code , it generates new blank writer document but at the same time it throws an error - Property/Methods not found: Sheets, Also i need the calc data to be copied in the writer document( appears blank now). please suggest
OpenOffice 3.1
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Post by JeJe »

Put this code at the top of a blank module. Make sure your sheet is active and run it by pressing F5 or by running the macro from the menu in your calc document. If you can't get this to work, the other code won't work either.

sub Main
msgbox Thiscomponent.title 'should give the title of your calc document
msgbox Thiscomponent.sheets(0).name
end sub

To make sure you're putting the code in the right place... you go to Tools/Macros/organise macros/openoffice basic
Select your document from the Macro from listbox in the dialog that pops up and click new
then click okay when prompted
There will be a module called "Module1" in the Standard library and you paste the code there. [edit: corrected]

(I assume oo 3.1 is the same as my later version here)
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Great.. Thanks alot JeJe..It's starts working now..
One more help - Can you please suggest how to make header title in Bold like Step number in Bold, then Description in Bold etc.
Also, In order to use this macro for all similar Calc sheet we need to insert this macro in all the calc sheet is there any way I can dynamically use the calc sheet which i need to copy data to writer text.
for example - I can select the source file dynamically and then run the macro to get the desired result.

Thank you so much anyways
OpenOffice 3.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy from SpreadSheet to text document

Post by Villeroy »

Basically you are in need for a programmer who operates the application for you.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Post by JeJe »

I've changed it to set the titles to bold.

Where to put the code:
Go to Tools/Macros/organise macros/openoffice macros/
Choose MyMacros/Standard library


(Programming is not the way to do this: you should look at Villeroy's method,
I moved calcdoc = Thiscomponent to before the NewDoc = line as that was causing the problems.)

Code: Select all

'ASSUMES USE OF SHEET(0) AND COLUMNS 0 TO 3 AND DATA STARTS AT ROW 1 (HEADINGS ROW 0)
'CHANGE THESE VALUES IF NOT
'STOPS AT THE FIRST EMPTY ROW
Sub Main
   dim ovc,calcdoc,pth as string,sheetno
   calcdoc = Thiscomponent

   NewDoc = StarDesktop.loadComponentFromURL("private:factory/swriter","_blank",0,Array())


   sheetno = 0 'sheet no
   rowno = 1 'set row no
   columnNo = 0 'set column no



   ovc = newdoc.currentcontroller.viewcursor

   with calcdoc.sheets(sheetno)
      do
         
            cellstring = .getCellByPosition(columnNo,rowNo).string
            if cellstring = "" then exit do
            ovc.string =("Step Number:")
			ovc.charweight = 150         
            ovc.collapsetoend
            ovc.charweight = 100
            ovc.string = cellstring & chr(10)
            ovc.collapsetoend
             
            cellstring = .getCellByPosition(columnNo + 1,rowNo).string           
            ovc.string= "Description:"
			ovc.charweight = 150         
            ovc.collapsetoend
            ovc.charweight = 100
            ovc.string = cellstring & chr(10)
            ovc.collapsetoend
           
            cellstring = .getCellByPosition(columnNo + 2 ,rowNo).string
            ovc.string= "Expected Result:"
			ovc.charweight = 150         
            ovc.collapsetoend
            ovc.charweight = 100
            ovc.string = cellstring & chr(10)
            ovc.collapsetoend
           
           
            ovc.string= "Actual Result:" & chr(10)
			ovc.charweight = 150         
            ovc.collapsetoend
	
	   newdoc.text.insertControlCharacter( ovc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false )
      OvC.BreakType = com.sun.star.style.BreakType.PAGE_BEFORE
      rowno = rowno+1
      loop
   end with
End Sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Thank you so much JeJe for the help...I will try this out.
Actually I tried Villeroy's method too but I believe in this method we need to create/change a database everytime you receive a calc file for conversion.
Thanks Villeroy's for your suggestion and help.

I have developed a Macro for Excel files which convert the spreadsheet to word document using VBA but the same code is not working in open office applications( for obvious reasons).
And I'm very much new to BASIC( Started learning though)..
we are working on local machines as well as client Remote desktop, in my local machines we're using Microsoft application where as in Remote desktop we are limited to Apache open office applications. My Code is working in local machines and now your help makes my life much easier in remote desktop too.

Sorry JeJe , I troubled you alot.
OpenOffice 3.1
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Copy from SpreadSheet to text document

Post by keme »

sushantmalviya wrote:Thank you so much JeJe for the help...I will try this out.
Actually I tried Villeroy's method too but I believe in this method we need to create/change a database everytime you receive a calc file for conversion.
...
Most likely you don't need that.
  • You create the database "definition file" once. For a "spreadsheet database", that file contains no actual data, only data descriptors and pointers to your data file.
    The same goes for a "csv database", and essentially every type of database definition except for the standalone odb database file you get when you choose to "create new" in Base.
  • You then create the form template once, and save it.
If the received spreadsheet files have roughly the same structure, you can just replace the new one for the old, and the data description in the odb file will connect nicely. You then open your template to run the mailmerge.

You can also manually check the source data through the database tool in Writer. Press function key F4 to toggle display of registered data sources.

If the source file structure changes dramatically you may need to redo the connection setup, or perhaps redo the fields in your template. In this situation, it is still likely that mailmerge workflow will be more reliable and less laborious than the macro approach.
sushantmalviya
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Post by sushantmalviya »

Thanks Keme!
OpenOffice 3.1
Post Reply