[Solved]Copy and paste date from one spreadsheet to another

[Kprogrammer]

I used getDataArray() and setDataArray() to copy one column from one spreadsheet to another.
It is copying number and text as it is but not date.
I want date to exactly copied but it is showing some number which is formatted as text.
Please suggest solution for this.

[FJCC]

The number you are seeing is the value of the date. Dates are stored as the number of days since Dec 30, 1899. All you have to do is format the cells to have whichever date format you prefer.

[Kprogrammer]

Thanks for your reply.
Date is 26-Jul-2018. when i am pasting it to another file it shows 43307. As you said this is the value of date from 30 Dec 1899. Now i want date exactly like this 26-Jul-2018.Which format is this. How can i code for this.

[Villeroy]

Replace DataArray with FormulaArray. The formula of a date value is 2018-07-26. Of course, this may lead to problems when the source data have actual formulas starting with =.
If this is not an option, cell styles can help a lot when you need to apply the right formatting to imported raw data.

[Villeroy]

You should insert new cells where you dump your data array. This way you get all the formatting from the preceeding row and all references to the previous target area will automatically expand to the new size.
https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=93099
Merge sheets of spreadsheet documents

[Kprogrammer]

The file is opened in comma separated mode, then only dates are not getting copied exactly. when i am not using comma separated mode then it is copying correctly.
FOR COMMA SEPARATED MODE-
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1"

opening file in comma seperated mode date is 07/26/18 which is 26-Jul-2018 in the file.

why when opening it in comma separated mode 26-Jul-2018 is converting to 07/26/18. How can i stop this conversion?Please guide

[Villeroy]

No conversion takes place.
Calc interpretes your date strings correctly and applies the default format for dates according to your current locale which is English (USA).
With a German number format locale 26-Jul-2018 shows up as 26.07.18 which is the correct value 43307 regardless of formatting.
The one and only thing that matters is that Calc imports the correct values from plain text files.
If this is a problem, you can import database data (csv is a database exchange format) through the Base component into preformatted cell ranges in Calc (or better forget the whole spreadsheet. If you have read-access to the original database which exported the csv file, you may even drop the whole csv thing and use a database query to that database.

[Example] Loading CSV into preformatted spreadsheets

[Kprogrammer]

I am not using German number format locale . when manually i am opening it , it is 26-Jul-2018 .
my my macro code is not opening it in this format 26-Jul-2018 . This is the date the file already has.
I am not aware of importing database data.

This is my code that opens 2 URLs. 1) csv file in comma separated mode 2) xls file.
Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
Dim Dummy() 'An (empty) array of PropertyValues

rem ###############################################
rem for filter options for opening a csv file:
rem ##############################################3#
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1"

rem ####################################################

Url1 = "file:///C:\Users\Kk\Desktop\FILE-20180727-092810.csv"
Url2 = "file:///C:\Users\Kk\Desktop\DIVIDEND_PRICE_JULY_27.xls"

Doc1 = StarDesktop.loadComponentFromURL(Url1, "_blank",0, Dummy) rem *********dates are as in the file 26-Jul-2018. format cell->its number **********
rem Doc1 = StarDesktop.loadComponentFromURL(Url1, "_blank",0, FileProperties) rem *****dates are 07/26/18 . format cell->its date should be 26-Jul-2018*********
Doc2 = StarDesktop.loadComponentFromURL(Url2, "_blank",0, Dummy)

1)should i apply some filter option?
2)when not using filter option for comma separated , the dates are exactly as in the the file. SO does it mean by default it is opening in comma separated mode?
3)case 1 - when not applying comma separated filter
observations:- Date 26-Jul-2018 is formatted as number. This is the date in the file. Its all fine and it is copying exactly in another file.
case 1 - when applying comma separated filter
observations:- Date 26-Jul-2018 is formatted as date.Now it becomes 07/26/18 .This date is not in the file. Date value 43307
is copying in another file.

[Villeroy]

26-Jul-2018 is a text. It is certainly not a date value. If you don't need the date for evaluation/aggregation/filtering/sorting then it will be just fine.

[Villeroy]

In my template SheetMerger.ots you find a little tool which translates the GUI settings of an already opened csv file to a FilterOptions string.
Open my file then your csv and call setFilterOptions with your csv being the active window. You will find the filter options in the named cell FilterOptions

[Kprogrammer]

FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1,2,3,4,5"

FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1,5,6,7,8"

these values are working fine. with 44,34,0,1 any four values ranging 2-10 is working fine.the date is exactly what the file contains.

Is there any problem to use this?

[Kprogrammer]

When I am sending my macro spreadsheet file saved as .ods or lost to another system. Macro code is not there in the file.
As I am sending my file containing macro it should be there when sending to another system.
Why macro is not there? It only shows standard folder and no macros.

[Villeroy]

[Tutorial] How to install a code snippet

and a snippet for your global Calc library showing the current file's FilterOptions in an input box from where you can copy the string:

Code: Select all

Sub showFilterOptions
Dim args(),i%
   args() = thisComponent.getArgs
   for i = 0 to uBound(Args())
      if args(i).Name = "FilterOptions" then inputbox "","",args(i).value
   next
End Sub 

[Zizi64]

When I am sending my macro spreadsheet file saved as .ods or lost to another system. Macro code is not there in the file.
As I am sending my file containing macro it should be there when sending to another system.
Why macro is not there? It only shows standard folder and no macros.

You can save/store your macros in two different places:

- In the documents. In this case the macros are available only for the document containing the macro. And you can send the document together the macros inside it.

- In the "MyMacros" under the Office Suite software. In this case the macros are available for all of documents that you open on that computer. But the macros will not be sent together with the documents.

[Kprogrammer]

I understood if I place my macro in "MyMacros" after sharing files macro will not be inside it.

But where to keep it so that while sharing it will be there?
I can see MyMacro and OpenOffice macro folders only.
Please guide.

[Villeroy]

I can see MyMacro and OpenOffice macro folders only.

and macro folders of loaded documents.

Forget "OpenOffice Macros".
Macros that can be used with many documents without document specific preconditions, should be placed in "My Macros". You can distribute this code easily by means of extensions or by the import featue. All this is described in full detail in the link I gave you.

Macros that rely on names, styles, links or document specific layout, should be stored in a document or in a template.

Code: Select all

oNames = ThisCOmponent.NamedRanges
oName = oNames.getByName("MyRange")
oRange = oName.getReferredCells()
oRange.CellStyle = "My Cell Style"

These lines can save you writing hundreds of lines of code.
The code is stored in a or template.
The template has a named range "MyRange" (no matter where) and a whole set of formatting attributes called "My Cell Style" and it is a matter of a few lines to apply the whole set of formatting attributes to the right cell range.
ThisComponent always refer to the document embedding the code.
All documents derived from that template share the same code, the same cell styles and the same range names.

If the above code is stored in MyMacros, you may call it when ThisComponent does not fulfill the precondition or when it is not even a Calc document.

The following is the kind of code I store in "My Macros":

Code: Select all

Function getOffsetRange(oRg, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
	addr = oRg.getRangeAddress()
	addr.StartRow = addr.StartRow + nRowOffset
	addr.EndRow = addr.EndRow + nRowOffset
	addr.StartColumn = addr.StartColumn + nColOffset
	addr.EndColumn = addr.EndColumn + nColOffset
	if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize -1
	if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize -1
	getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function

'pass a spreadsheet-document, sheet or range  together with a c.s.s.table.CellRangeAddress
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
	getRangeByAddress = Null
End Function

Works with any Calc document, sheet, cell range with no preconditions.

[UnklDonald418]

But where to keep it so that while sharing it will be there?
I can see MyMacro and OpenOffice macro folders only.

In the macro library dialog, when you see My Macros, OpenOffice Macros, you may need to scroll down to select the name of your spreadsheet. If you have other OpenOffice documents open they will also appear there.
If you click on the + in front of the name of your spreadsheet and it disappears, select New from the buttons on the right, That will create a Standard folder for your document and open a New Module dialog. You can accept the default name or type in a different one if you like, then press OK
Now you can enter your macro code and it will be stored with the document.