But beside this, I have some problems with the given functions as well, like number the number format and some font issues.
1a) there are some predefined number formats (0-22), which will automatically be adapted to the regional settings (at least from excel). Most of the formats (0-20) work fine, as you can see when starting the example below. But if you change the for/next loop to include the format 21 or 22, the resulting file can't be opened without crashing.
Any ideas how to use number format 21 and 22 in a spreadsheet?
1b) does anyone know, how to create a user defined number format, like 'mm:ss' or '0.000' ?
2) there are still problems to create certain text cells, so the cell A2 in the example below stays white after loading the created 'test.xls' into excel. Also here, does anyone know, if this problem can be resolved?
Code: Select all
;~~~~
IncludeFile "ExcelWriter (Ori).pbi"
;~~~~
Global XlsActiveFile=#True
#Q=Chr(34)
#XlsFont1 = 0
#XlsFont2 = 64
#XlsFont3 = 128
#XlsFont4 = 192
#XlsCellNormal = 0
#XlsCellLocked = 64
#XlsCellHidden = 128
#XlsMarginLeft = 38
#XlsMarginRight = 39
#XlsMarginTop = 40
#XlsMarginBottom = 41
#XlsNoFormat = 0
#XlsBold = 1
#XlsItalic = 2
#XlsUnderline = 4
#XlsStrikeout = 8
#XlsNoAlignment = 0
#XlsAlignmentNull = 0
#XlsAlignmentLeft = 1
#XlsAlignmentCenter = 2
#XlsAlignmentRight = 3
#XlsCellFill = 4
#XlsNoBorder = 0
#XlsBorderNull = 0
#XlsBorderLeft = 8
#XlsBorderRight = 16
#XlsBorderTop = 32
#XlsBorderBottom = 64
#XlsCellShaded = 128
Enumeration
#XlsFormat_Nil
#XlsFormat_General
#XlsFormat_Number1
#XlsFormat_Number2
#XlsFormat_Number3
#XlsFormat_Number4
#XlsFormat_Currency1
#XlsFormat_Currency2
#XlsFormat_Currency3
#XlsFormat_Currency4
#XlsFormat_Percent1
#XlsFormat_Percent2
#XlsFormat_Scientific
#XlsFormat_DateLong
#XlsFormat_DateStandard
#XlsFormat_DateShort
#XlsFormat_TimeShort
#XlsFormat_TimeLong
#XlsFormat_TimeShort24
#XlsFormat_TimeLong24
#XlsFormat_DateTime
#XlsFormat_Scientific2
#XlsFormat_TimeMinutes
#XlsFormat_Text
#XlsFormat_PrivateNumber
#XlsFormat_PrivateTime
EndEnumeration
; ~~~
Structure XlsFontColorType
opcode.w; 69 (0x45)
length.w; 2 (0x02)
Color.w; color-code (2 byte)
EndStructure
Structure XlsNumberType
opcode.w; 03 (0x03)
length.w; 15 (0x0F)
Row.w; row (2 byte)
col.w; column (2 byte)
rgbAttr1.b; hidden [7], locked [6], XF-index [0-5] (1 byte)
rgbAttr2.b; font [6-7], format [0-5] (1 byte)
rgbAttr3.b; shade [7], borders [3-6], alignment [0-2] (1 byte)
Value.d; double (8 bytes)
EndStructure
Structure XlsFontType
opcode.w; 49 (0x31)
length.w; 5+len(fontname)
FontHeight.w; height (2 byte)
FontAttributes1.b; extended [7], condensed [6], shadowed [5], outlined [4], strikeout [3], underline [2], italic [1], bold [0]
FontAttributes2.b; reserved - always 0 (1 byte)
FontNameLength.b; (1 byte)
EndStructure
; ~~~
Procedure.d IpfTimeToDouble(hour,minute,second)
ProcedureReturn (hour*3600+minute*60+second)/86400.0
EndProcedure
Procedure.l IpfDateToJulian(year,month,day)
Protected Elapsed=day
If month<3
month+12
year-1
EndIf
Elapsed+Int((year+4712)*365.25)
Elapsed-year/100
Elapsed+year/400
Elapsed+Int(30.6*month-30.4)
ProcedureReturn Elapsed
EndProcedure
Procedure.l IpfWriteStringToFile(String.s)
Protected Bytes.l
CompilerIf #PB_Compiler_Unicode
Protected *Buffer=AllocateMemory(Len(String)+2)
If *Buffer
PokeS(*Buffer,String,Len(String),#PB_Ascii)
WriteFile_(XlsActiveFile,*Buffer,Len(String),@Bytes,0)
FreeMemory(*Buffer)
EndIf
CompilerElse
WriteFile_(XlsActiveFile,@String,Len(String),@Bytes,0)
CompilerEndIf
ProcedureReturn Bytes
EndProcedure
Procedure.l XlsSetFont(FontName$,FontHeight.l,FontFormat.l)
If XlsActiveFile=0
ProcedureReturn #False
EndIf
XlsFontData.XlsFontType
XlsFontData\opcode=49
XlsFontData\length=5+Len(FontName$)
XlsFontData\FontHeight=FontHeight*20
XlsFontData\FontAttributes1=FontFormat & $FF ;bold/underline etc...
XlsFontData\FontAttributes2=0 ;reserved-always zero!!
XlsFontData\FontNameLength=Len(FontName$) & $FF
If Ipf_WriteToFile(XlsFontData,SizeOf(XlsFontType))
ProcedureReturn IpfWriteStringToFile(FontName$)
EndIf
EndProcedure
Procedure.l XlsSetFontcolor(FontColor.l)
If XlsActiveFile=0
ProcedureReturn #False
EndIf
XlsFontColorData.XlsFontColorType
XlsFontColorData\opcode=69; $45
XlsFontColorData\length=2
Select FontColor
Case #Black
XlsFontColorData\Color=0
Case #White
XlsFontColorData\Color=1
Case #Red
XlsFontColorData\Color=2
Case #Green
XlsFontColorData\Color=3
Case #Blue
XlsFontColorData\Color=4
Case #Yellow
XlsFontColorData\Color=5
Case #Magenta
XlsFontColorData\Color=6
Case #Cyan
XlsFontColorData\Color=7
Default
XlsFontColorData\Color=0
EndSelect
If Ipf_WriteToFile(XlsFontColorData,SizeOf(XlsFontColorType))
ProcedureReturn #True
EndIf
EndProcedure
Procedure.l XlsWriteDate(year,month,day,hour,minute,second,row.l,col.l,CellFont.l,CellAlignment.l,HiddenLocked.l,CellFormat.l)
If XlsActiveFile=0
ProcedureReturn #False
EndIf
XlsNumberData.XlsNumberType
XlsNumberData\opcode=3
XlsNumberData\length=15
XlsNumberData\row=row & $FFFF
XlsNumberData\col=col & $FFFF
XlsNumberData\rgbAttr1=HiddenLocked & $FF
XlsNumberData\rgbAttr2=CellFont|CellFormat & $FF
XlsNumberData\rgbAttr3=CellAlignment & $FF
XlsNumberData\Value=IpfDateToJulian(year,month,day)-IpfDateToJulian(1899,12,31)+IpfTimeToDouble(hour,minute,second)
ProcedureReturn Ipf_WriteToFile(XlsNumberData,SizeOf(XlsNumberType))
EndProcedure
Procedure.l XlsWriteDateString(Date.s,DateFormat.s,row.l,col.l,CellFont.l,CellAlignment.l,HiddenLocked.l,CellFormat.l)
Protected o
o=ParseDate(DateFormat,Date)
If o
ProcedureReturn XlsWriteDate(Year(o),Month(o),Day(o),Hour(o),Minute(o),Second(o),row.l,col.l,CellFont.l,CellAlignment.l,HiddenLocked.l,CellFormat.l)
Else
ProcedureReturn #False
EndIf
EndProcedure
; ~~~
Xls_CreateFile("Test.xls")
Xls_PrintGridLines(#False)
Xls_SetFont("Calibri",12,#XlsNoFormat)
XlsSetFontcolor(#Black)
Xls_SetFont("Calibri",12,#XlsBold)
XlsSetFontcolor(#Blue)
Xls_SetFont("Calibri",18,#XlsBold)
XlsSetFontcolor(#Red)
Xls_SetFont("Calibri",12,#XlsBold)
XlsSetFontcolor(#Green)
Xls_SetColumnWidth(0,0,20)
Xls_SetColumnWidth(1,1,5)
Xls_SetRowHeight(1,24)
Xls_WriteText("Excel Writer 1.51",1,0,#XlsFont3,#XlsAlignmentLeft,#XlsCellNormal|#XlsCellLocked,#Null)
For i=1 To 4
Xls_WriteText(Chr('@'+i),3,i-1,#XlsFont2,#XlsAlignmentCenter|#XlsBorderBottom,#XlsCellNormal,#Null)
Xls_WriteNumber(Random(300)/10,4,i-1,#XlsFont1,#XlsAlignmentRight,#XlsCellNormal,#XlsFormat_Number3)
Xls_WriteNumber(Random(300)/10,5,i-1,#XlsFont2,#XlsAlignmentRight|#XlsBorderTop,#XlsCellNormal,#XlsFormat_Number3)
Next i
XlsWriteDateString("20121224112233","%YYYY%MM%DD%HH%II%SS",7,0,#XlsFont1,#XlsAlignmentLeft,#XlsCellNormal,#XlsFormat_DateTime)
XlsWriteDate(2012,12,24,11,22,33,6,0,#XlsFont1,#XlsAlignmentLeft,#XlsCellNormal,#XlsFormat_TimeLong24)
For i=0 To 20; 21, 22 !!!!!
Xls_WriteNumber(i,8+i,1,#XlsFont1,#XlsAlignmentRight,#XlsCellNormal,#XlsFormat_Nil)
Xls_WriteNumber(123.45,8+i,0,#XlsFont1,#XlsAlignmentRight,#XlsCellNormal,#XlsFormat_Nil+i)
Next i
Xls_CloseFile()
