Hi,
Is there a way to change background color when using a ‘template.xls’ ?
I would like to write a list of database records into a excel sheet and use a
template for this.
It seems, that when using a template, I cannot change the format of a cell.
In my work, I insert a formula in one column and I would like to change the
background color of that cell as well.
If I send a formula without format, then everything displays correctly. But if
I send the format then the cell remains empty.
My actual code looks like this:
===========
Dim book As New XLBookMBS(False)
Dim totalFormat As XLFormatMBS
TotalFormat.FillPattern = XLFormatMBS.FillPatternSolid
TotalFormat.PatternForegroundColor = book.PackColor(&cFFFF0000)
// =IF(NOT(AND(ISBLANK(E3);ISBLANK(F3)));(G2-F3)+E3;”")
Dim formula1 As String = "=IF(NOT(AND(ISBLANK(E" + Str(xlRow+1) +
");ISBLANK(F" + Str(xlRow+1) _
+ ")));(G" + Str(xlRow) + "-F" + Str(xlRow+1) + ")+E" + Str(xlRow+1) +
";" + Chr(34) + Chr(34) + ")"
Call sheet.WriteFormula(xlRow,col,formula1) // writes a working formula
into the cell -> ok
‘ Call sheet.WriteFormula(xlRow,col,formula1,TotalFormat) // creates an
empty cell -> not ok
===========
Using a XLFormatMBS seems to work only if I create a new sheet from scratch,
but not when I use a template.
To verify, I also tested this with the MBS "invoice with template" example and
I added the format to it, but it does not change background color as well:
===========
dim book as new XLBookMBS(false)
// == Changed original code from here
Dim format As XLFormatMBS
format = book.AddFormat
format.FillPattern = XLFormatMBS.FillPatternSolid
format.PatternForegroundColor = XLFontMBS.ColorYellow
// == Changed original code to here
// load template
call book.Load(GetFolderItem("Template.xls"))
dim sheet as XLSheetMBS = book.Sheet(0)
// Write Customer address
call sheet.WriteString 5, 1, "Smith & Co", format // <— Changed code here
call sheet.WriteString 6, 1, "First Avenue 123"
call sheet.WriteString 7, 1, "12345 New York"
call sheet.WriteString 9, 1, "Bob Smith”
============
Oliver
_______________________________________________
Mbsplugins_monkeybreadsoftware.info mailing list
[email protected]
https://ml01.ispgateway.de/mailman/listinfo/mbsplugins_monkeybreadsoftware.info
