Hi,
if I set a new standard template with a custom font, I get that every new file has that font.
But when I open a CSV using calc, I do not have the font of my custom standard template, I have always the Liberations Sans font.
How to have the same custom font I have set in my default standard template?
Thank you
Copy-Paste-Special the imported data into your template or import csv via Base and link the raw database data from Base to your spreadsheet.
Okay, so I can’t generally and directly force the use of another font.
I’m sorry that’s the case, thank you
Create a new spreadsheet document from your template where you modified the font of cell style “Default”.
Copy the csv file to the same directory as the spreadsheet under some fixed name like “Linked.csv”.
menu:Sheet>Insert Sheet From File…
Specify Linked.csv with proper input options and check option “Link”.
Next time, when another csv file arrived, replace Linked.csv with the new file before loading the spreadsheet document.
Thank you @Villeroy I’m not looking for a trick to get the result: I’d like to open a CSV in whatever folder it is, using the default procedure, and have a different default font (in example the one I set in the custom default template).
I realize this can’t be done and I do not like it 
Thank you very much. I thought it was bad behavior, and I am not surprised to see that it is a bug.
Thank you very much
I think, it is intentional to not make any assumptions about formatting when opening a file with no information about formatting.
When I open calc, I do not want to format anything. I have an empty spreadsheet, but fortunately I have the font I chose globally.
Instead, when I open calc to open a CSV, the behavior is inconsistent. So, for me it is right that it is a bug.
If the default template would be used to import csv, thousands of users would complain because of the explicit formatting they want to use with new “hand made” spreadsheets but not with imported data. People use all kinds of default templates. Therefore, we would need yet another setup option about the template to be used with raw data import.
Is there any way, after importing a CSV, to quickly apply the default template? Can a shortcut be created?
Thank you
It is possible to write a macro that changes the default cell style and assign a hotkey.
Example:
Option Explicit
Sub ChangeDefaultCellStyle()
Dim oStyle
oStyle=ThisComponent.StyleFamilies.getByName("CellStyles").getByName("Default")
With oStyle
.CharFontName="Calibri"
.CharHeight=11.0
End With
End Sub
I have no idea what is wrong with the default font, but yes, changing the font of the default cell style surely works in this particular case. However, everything is lost if you don’t change the file format.
I tried a macro which transfers the imported csv data into a new document. This shows the following side effects:
The following code tries to do the right things with any given template and with a given set of style names existing in that template.
EDIT: extended version with its own file picker, and you can specify any template. Use the Main routine as a template for your own specific csv imports.
Sub Main()
Const cImportOptions = "59,34,ANSI,1,,1031,false,true,true,false,false,0,false,false,true"
REM file picker options
Const cFilterLabel = "Text (csv)"
Const cPattern = "*.csv"
Const cInitPath = "/home/andreas/Downloads/"
REM template options
Const cTemplate = "MyDefault"
Const cTemplateRegion = "" 'optional
REM sample arguments for formatting, ignored if style names don't exist
sHeaderStyle = "ColumnLabel"
aColumnStyles = Array("MyDate", "MyTime", "", "", "CurrEuro")
REM ------ END OF CONFIG ------
Dim aProps(1) as new com.sun.star.beans.PropertyValue
aProps(0).Name = "FilterOptions"
aProps(0).Value = cImportOptions
aProps(1).Name = "FilterName"
aProps(1).Value = "Text - txt - csv (StarCalc)"
sFile = pickFile(sTitle:="Open CSV", sInit:=convertToURL(cInitPath), sFilterLabel:=cFilterLabel, sPattern:=cPattern)
if sFile = "" then exit sub
doc1 = StarDesktop.loadComponentFromURL(sFile, "_blank", 0, aProps())
doc2 = getOpenTemplate(cTemplate, "scalc", cTemplateRegion)
transfer2DefaultTemplate doc1, doc2, sHeaderStyle, aColumnStyles
doc1.close(false)
End Sub
Sub transfer2DefaultTemplate(oSourceDoc, oTargetDoc, Optional sHeaderStyle, Optional aColumnStyles())
If isMissing(sHeaderStyle) then sHeaderStyle = ""
If isMissing(aColumnStyles) then aColumnStyles = Array()
cell = oSourceDoc.Sheets(0).getCellByPosition(0,0)
src = cell.getSpreadSheet.createCursorByRange(cell)
src.collapseToCurrentRegion()
a() = src.getDataArray()
r = uBound(a())
c = uBound(a(0))
tgt = oTargetDoc.Sheets(0).getCellRangeByPosition(0, 0, c, r)
tgt.setDataArray(a())
addr = tgt.getRangeAddress()
styles = oTargetDoc.StyleFamilies.getByName("CellStyles")
if styles.hasByName(sHeaderStyle) then
tgt.getCellRangeByPosition(addr.StartColumn, addr.StartRow, addr.EndColumn, addr.StartRow).CellStyle = sHeaderStyle
addr.StartRow = addr.StartRow +1
endif
iub = uBound(aColumnStyles())
for i = 0 to addr.EndColumn
if i <= iub then
s = aColumnStyles(i)
if styles.hasByName(s) then
tgt.getCellRangeByPosition(i, addr.StartRow, i, addr.EndRow).CellStyle = aColumnStyles(i)
next
endif
tgt.Columns.getByIndex(i).OptimalWidth = True
next
End Sub
Function getOpenTemplate(sTemplateName$, Optional sComponentName$, Optional sTemplateRegionName$)
If isMissing(sComponentName) or sComponentName ="" then sComponentName = "swriter"
If isMissing(sTemplateRegionName) or sTemplateRegionName = "" then sTemplateRegionName = "My Templates"
Dim a(1) as new com.sun.star.beans.PropertyValue
a(0).Name = "TemplateName"
a(0).Value = sTemplateName
a(1).Name = "TemplateRegionName"
a(1).Value = sTemplateRegionName
REM the component name does not matter with unambigous template names
getOpenTemplate = StarDesktop.loadComponentFromURL("private:factory/"& sComponentName,"_blank",0,a())
End Function
Function pickFile(sTitle$, sInit$, sFilterLabel$, sPattern$) As String
REM return a single file URL or ""
REM dialog starts at office default directory if sInit = ""
Dim oPicker, x()
oPicker = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
oPicker.setTitle(sTitle)
oPicker.setDisplayDirectory(sInit)
oPicker.setMultiSelectionMode(False)
oPicker.appendFilter(sFilterLabel, sPattern)
if oPicker.execute() then
x() = oPicker.getFiles()
pickFile = x(0)
endif
End Function
You get the right import options (first line in main routine) by opening your csv manually and runing the following code. Copy the displayed string with surrounding double quotes to cFilterOptions in your own macro.
Sub showFilterOptions()
Dim args(),i%
args() = thisComponent.getArgs
for i = 0 to uBound(Args())
if args(i).Name = "FilterOptions" then inputbox args(i).Name,"",cStr(args(i).value)
next
End Sub