[Solved] Copy from SpreadSheet to text document

Creating a macro - Writing a Script - Using the API

[Solved] Copy from SpreadSheet to text document

Postby sushantmalviya » Sun Jan 27, 2019 4:03 pm

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
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Postby keme » Sun Jan 27, 2019 5:34 pm

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!
User avatar
keme
Volunteer
 
Posts: 3130
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Copy from SpreadSheet to text document

Postby JeJe » Sun Jan 27, 2019 7:44 pm

Here is a macro method though.

Code: Select all   Expand viewCollapse view
'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

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Postby JeJe » Sun Jan 27, 2019 8:33 pm

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   Expand viewCollapse view


'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

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Postby sushantmalviya » Sun Jan 27, 2019 8:40 pm

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

Postby sushantmalviya » Mon Jan 28, 2019 7:12 pm

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

Postby sushantmalviya » Tue Jan 29, 2019 11:32 am

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
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Postby RoryOF » Tue Jan 29, 2019 11:41 am

Make an empty file with that name and see how the macro works then.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28740
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Copy from SpreadSheet to text document

Postby sushantmalviya » Tue Jan 29, 2019 11:55 am

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
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Postby RoryOF » Tue Jan 29, 2019 11:56 am

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.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28740
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Copy from SpreadSheet to text document

Postby sushantmalviya » Tue Jan 29, 2019 12:13 pm

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
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Postby JeJe » Tue Jan 29, 2019 12:31 pm

"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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Postby sushantmalviya » Tue Jan 29, 2019 1:28 pm

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

Postby sushantmalviya » Tue Jan 29, 2019 1:30 pm

Not sure where i'm commiting mistake :(
OpenOffice 3.1
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Postby JeJe » Tue Jan 29, 2019 1:47 pm

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

Re: Copy from SpreadSheet to text document

Postby JeJe » Tue Jan 29, 2019 2:04 pm

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

Code: Select all   Expand viewCollapse view

'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


Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Postby Villeroy » Tue Jan 29, 2019 2:21 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26623
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy from SpreadSheet to text document

Postby sushantmalviya » Tue Jan 29, 2019 2:56 pm

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
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Postby JeJe » Tue Jan 29, 2019 3:05 pm

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Postby sushantmalviya » Tue Jan 29, 2019 3:45 pm

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
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Postby JeJe » Tue Jan 29, 2019 4:00 pm

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)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Postby sushantmalviya » Tue Jan 29, 2019 9:18 pm

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
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Postby Villeroy » Tue Jan 29, 2019 9:43 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26623
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy from SpreadSheet to text document

Postby JeJe » Tue Jan 29, 2019 10:59 pm

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   Expand viewCollapse view
'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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 481
Joined: Wed Mar 09, 2016 2:40 pm

Re: Copy from SpreadSheet to text document

Postby sushantmalviya » Wed Jan 30, 2019 9:53 am

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
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm

Re: Copy from SpreadSheet to text document

Postby keme » Wed Jan 30, 2019 10:16 am

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.
User avatar
keme
Volunteer
 
Posts: 3130
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Copy from SpreadSheet to text document

Postby sushantmalviya » Wed Jan 30, 2019 10:30 am

Thanks Keme!
OpenOffice 3.1
sushantmalviya
 
Posts: 13
Joined: Sun Jan 27, 2019 3:32 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests