Excelwriter Cell Limit?

[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.

[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.

[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.

[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

[IdeasVacuum]

.....and the example file: http://www.purebasicpower.de/?download=PureXLS_PB4.zip

Looks good

[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

[Primus1]

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

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.