Page 2 of 2
Re: Excelwriter Cell Limit?
Posted: Thu Dec 13, 2012 2:17 pm
by Primus1
Just tried it but can't open the output xls file in Excel 2007 or 2003, it says content unreadable.
I'll try if I can just hardwrite a character amount of above 255 in the xls file and get all to be displayed in Excel.
Re: Excelwriter Cell Limit?
Posted: Thu Dec 13, 2012 3:36 pm
by Primus1
Well I've found that the current Excel version, BIFF 2.1, excel.pbi writes is limited to 255 characters so there isn't any other good solution except to create a newer xls sheet but I can't find any good purebasic libraries for it. I do have found a crude workaround.
I'll just split the long text into multiple cells and then do a formula like =E20&E21&E22 into the main cell, this might work.
Anyway thanks for the help PB'ers.
Re: Excelwriter Cell Limit?
Posted: Thu Dec 13, 2012 4:28 pm
by IdeasVacuum
BIFF 2.1 is not current, it's old version Excel. It definitely does not have a 255 char limit in Excel2002, which might be the last of the Biffs?
You might like to try XML format files if they are to be read by later Excel versions.
Re: Excelwriter Cell Limit?
Posted: Fri Dec 14, 2012 12:58 am
by ABBKlaus
Hi there,
ExcelWriter is a bit outdated. You might want to try Flypes PureXLS, its much better
You find it here :
http://www.purebasicpower.de/downloads/PureXLS.pbi
BR Klaus
Re: Excelwriter Cell Limit?
Posted: Fri Dec 14, 2012 2:36 am
by IdeasVacuum
Re: Excelwriter Cell Limit?
Posted: Mon Dec 17, 2012 12:13 am
by Michael Vogel
PureXLS looks fine and gives you another alternative. So you have to decide to ignore older excel versions (incompatible format by PureXLS) or having limited possibilities (text format and antic BIFF 2.1 standard). Whatever you do, it may get funny – CSV and text import may differ, as the number and date format (=TEXT(DATE(2012;12;17);"TT.MM.YYYY")) etc.
For simple data sheets, the BIFF 2.1 should be enough – maybe the maximum cell size could be optimized a little bit, then I would only add the simple coloring scheme of BIFF 2.1 which can be done easily...
Code: Select all
XLS_SetFont("Arial",14,#XLS_NoFormat)
XLS_SetFontcolor(#Black)
Code: Select all
Structure XLS_FONTCOLOR_RECORD
opcode.w ;69
length.w
Color.w
EndStructure
:
ProcedureDLL.l XLS_SetFontcolor(FontColor.l)
If xlsFileNumber=0
ProcedureReturn #False
EndIf
FONTCOLOR_RECORD.XLS_FONTCOLOR_RECORD
FONTCOLOR_RECORD\opcode=69; $45
FONTCOLOR_RECORD\length=2
Select FontColor
Case #Black
FONTCOLOR_RECORD\Color=0
Case #White
FONTCOLOR_RECORD\Color=1
Case #Red
FONTCOLOR_RECORD\Color=2
Case #Green
FONTCOLOR_RECORD\Color=3
Case #Blue
FONTCOLOR_RECORD\Color=4
Case #Yellow
FONTCOLOR_RECORD\Color=5
Case #Magenta
FONTCOLOR_RECORD\Color=6
Case #Cyan
FONTCOLOR_RECORD\Color=7
Default
FONTCOLOR_RECORD\Color=0
EndSelect
If IPF_WriteToFile(FONTCOLOR_RECORD,SizeOf(XLS_FONTCOLOR_RECORD))
ProcedureReturn #True
EndIf
EndProcedure
For a better Excel file format support this library could be the "solution" for all problems:
http://www.codeproject.com/Articles/425 ... at-Library – now it only has to be translated to PB
Re: Excelwriter Cell Limit?
Posted: Tue Dec 18, 2012 4:15 pm
by Primus1
Thanks! I've just tried it and not only is the maximum characters in a cell enough it also keeps the correct date format. I'll use this in my current code.
