PureBasic Forums - English

Posted: **Wed Dec 12, 2012 9:23 am**

When using ExcelWriter in Purebasic I've stumbled upon a rather annoying bug or limitation, hopefully someone knows the answer to this.

When I use

Code: Select all

XLS_WriteText("0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789",7,0,#XLS_Font2,#XLS_LeftAlign,#XLS_CellNormal,0)

It's supposed to write 400 characters, but when I open the created xls file it only shows the first 144 characters!
Now the weird thing is when I look inside the file, with for instance notepad, I can still see all 400 characters but in Excel it's truncated to 144..

Posted: **Wed Dec 12, 2012 9:32 am**

in Excel it's truncated to 144

Then the limitation has nothing to do with PB. If you post the output it can be tested in other spreadsheets.

Posted: **Wed Dec 12, 2012 9:36 am**

nospam wrote:
in Excel it's truncated to 144
Then the limitation has nothing to do with PB.

Still I would need some sort of work around, it might be a limitation of the older excel format excelwriter is writing. Are there any other libs or code that can create excel files from PB?

Posted: **Wed Dec 12, 2012 9:54 am**

Primus1 wrote:Still I would need some sort of work around

Code the output manually.

it might be a limitation of the older excel format excelwriter is writing

Then you need to take that up with the ExcelWriter developers.
http://sourceforge.net/projects/excel-writer/

Are there any other libs or code that can create excel files from PB?

I don't know. Post the output and I'll test it in LibreOffice and Excel 2007.

Posted: **Wed Dec 12, 2012 10:12 am**

nospam wrote:
Primus1 wrote:Still I would need some sort of work around
Code the output manually.
it might be a limitation of the older excel format excelwriter is writing
Then you need to take that up with the ExcelWriter developers.
http://sourceforge.net/projects/excel-writer/
Are there any other libs or code that can create excel files from PB?
I don't know. Post the output and I'll test it in LibreOffice and Excel 2007.

That's a different Excelwriter I'm using this onefrom Purebasic Power.

This is the output I'm using a 400 characters test string.

0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789

or do you mean something else?

Posted: **Wed Dec 12, 2012 10:35 am**

Primus1 wrote:That's a different Excelwriter I'm using this onefrom Purebasic Power.

Ok, you will probably still have to contact them.

or do you mean something else?

Something else

The output is the file you mentioned. Post the file.

Posted: **Wed Dec 12, 2012 12:24 pm**

Thanks,

Here is the code:

Code: Select all

XIncludeFile "excel.pbi"

xlsCreateFile("test1.xls")
  
xlsWriteText("abcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghij",1,1,#xlsFont1, #xlsLeftAlign, #xlsCellNormal, 0)

xlsCloseFile()

here is the output file test1.xls http://www.filedropper.com/test1_4

You can find excel.pbi here.

When you open the file you will see it's truncated to 144 chars even though all the 400 are actually there in file test1.xls file.

Posted: **Wed Dec 12, 2012 12:47 pm**

The file is imported as 144 characters in Excel 2007. LibreCalc won't open it. In the absence of talking to the developers, perhaps the best way to work around the issue is to create a csv file and import it.

Posted: **Wed Dec 12, 2012 1:51 pm**

nospam wrote:The file is imported as 144 characters in Excel 2007. LibreCalc won't open it. In the absence of talking to the developers, perhaps the best way to work around the issue is to create a csv file and import it.

Thanks, I've tried comma seperated file but it had it's own problems. It's a shame that stupid limit isn't allowing anymore characters.

I've noticed it in Excel 95 there was a max characters limit of 255 for a cell.

Posted: **Wed Dec 12, 2012 7:57 pm**

Primus1 wrote:I've tried comma seperated file but it had it's own problems.

What problems? The format couldn't be simpler.

Posted: **Wed Dec 12, 2012 8:31 pm**

That syntax is different?

Code: Select all

XLS_CreateFile("C:\test1.xls")
 
XLS_WriteText("abcdefghijabcdefghijabcdef......hijabcdefghijabcdefghijabcdefg",1,1,#XLS_Font1, #XLS_LeftAlign, #XLS_CellNormal, 23)

XLS_CloseFile()

The cell is not a "General" type, it has to be set as "Text". There isn't a specific cell type number for a text cell in the Lib's cell format list. I'm assuming that type 23 "@" is intended for email address format - if that is used, Excel actually creates a text cell but the char count limit is still there - not sure what the limit is for a text cell, but I have entered 1000 chars manually without issue (Excel2002). So, perhaps ABBKlaus, the author of the lib, can help. If you actually have the lib as a .pbi file, you should be able to see what is limiting the char count or indeed add the 'Text' cell type.

Posted: **Wed Dec 12, 2012 10:46 pm**

Seems to be a problem with the ExcelWriter...

In your example file, you can see the wrong value for the text length (0x90 on offset 0x193) - if you change this value to 0xF0 (OpenFile ... SeekFile($193) ... WriteByte($F0) ... CloseFile), Excel will show some characters more...

If you don't need special text and number formats, you could write a csv or text file, which will be imported without problems.

Code: Select all

m.s
For i=1 To 500
	n=Len(Str(i-i%10+10))
	If i%5=0
		If i%10=0
			m+Str(i)
		Else
			m+"+"
		EndIf
	ElseIf i%10<=10-n
		m+"."
	EndIf
Next i

CreateFile(...)
WriteStringN(...,"x"+#Tab$+m+#Tab$+"x")
WriteStringN(...,"x"+#Tab$+#DQuote$+m+#DQuote$+#Tab$+"x")
CloseFile(...)

Posted: **Thu Dec 13, 2012 10:10 am**

Using Michael's tip and changing 0x90 to 0xFF i can boost the 'limit' to 255 characters but it's still to small I need atleast double, I'll have a look inside the excel.pbi file to see if I can somehow change the character count it writes.

I might have read somewhere that in early versions of excel format you have to write a continue flag if the amount of characters exceeds a certain limit and then write a new cell which continues your previous one. On opening in excel they will be seen as one. Don't know if that's true.

I can't use csv because upon opening it Excel changes the dates, you can't say this cell must be treated as text in csv. I know you can import a csv instead of opening it but that requires to much additional steps for my users and isn't 'foolproof'. A date in csv plaintext like 2005-10-22 get's changed to 22/10/05 depending on country/language settings. Also each Excel version treats newlines different which is annoying.

This is the part the xlsWriteTextprocedure of excel.pbi, maybe it needs a simple change:

Code: Select all

Procedure.l xlsWriteText(value.s,  lrow.l,  lcol.l,  CellFont.l,  CellAlignment.l,  HiddenLocked.l,  CellFormat.l)

    ;convert the row, col from LONG to INTEGER.
    Row.w = ConvertRow(lrow.l)
    Col.w = ConvertCol(lcol.l)

    Protected b.b
    st.s = value
    l.l = Len(st)
   
    Protected TEXT_RECORD.tText
    TEXT_RECORD\opcode = 4
    TEXT_RECORD\length = 10
    ;Length of the text portion of the record
    TEXT_RECORD\TextLength = l.l
   
    ;Total length of the record
    TEXT_RECORD\length = 8 + l.l
   
    TEXT_RECORD\Row = Row
    TEXT_RECORD\col = col
     
    TEXT_RECORD\rgbAttr1 = (HiddenLocked.l) ;CBYT
    TEXT_RECORD\rgbAttr2 = (CellFont.l + CellFormat.l)
    TEXT_RECORD\rgbAttr3 = (CellAlignment.l)
    
    WriteData(xlsFileNumber,@TEXT_RECORD, SizeOf(TEXT_RECORD))
    Debug(SizeOf(TEXT_RECORD))
    ;Then the actual string data
    For a.l = 1 To l.l
        b = Asc(Mid(st, a, 1))
        WriteData(xlsFileNumber,@b, 1)
    Next

    ProcedureReturn  0   ;return with no error
   
EndProcedure

full here

Posted: **Thu Dec 13, 2012 11:34 am**

There are some syntax errors in there - once a var is declared, the type should omitted, otherwise you are declaring a new var. Looks as though all the longs could be integers anyway. Can't test the code here because it is dependent on other procedures. Have you tried asking ABB about his lib?

Posted: **Thu Dec 13, 2012 12:49 pm**

OK, this works for a 1200 char string, Excel2002 reads it without complaint, but to get it to work I have commented out the writing of the TEXT_RECORD data. In the snippet below I have omitted the super-long string because it messes up the forum post display:

Code: Select all

XIncludeFile "Excel.pbi"

#XLS = 0

Procedure.i xlsWriteText2(value.s,  lrow.i,  lcol.i,  CellFont.i,  CellAlignment.i,  HiddenLocked.i,  CellFormat.i)

    ;convert the row, col from LONG to INTEGER.
    Row.w = ConvertRow(lrow)
    Col.w = ConvertRow(lcol)

    Protected      b.b
    Protected      a.i
    Protected     st.s = value
    Protected Length.i = Len(st)
   
    Protected TEXT_RECORD.tText
    TEXT_RECORD\opcode = 4
    TEXT_RECORD\length = 10
    ;Length of the text portion of the record
    TEXT_RECORD\TextLength = Length
   
    ;Total length of the record
    TEXT_RECORD\length = 8 + Length
   
    TEXT_RECORD\Row = Row
    TEXT_RECORD\col = Col
     
    TEXT_RECORD\rgbAttr1 = (HiddenLocked.i) ;CBYT
    TEXT_RECORD\rgbAttr2 = (CellFont.i + CellFormat.i)
    TEXT_RECORD\rgbAttr3 = (CellAlignment.i)
   
    ;WriteData(#XLS,@TEXT_RECORD, SizeOf(TEXT_RECORD))
    ;Debug(SizeOf(TEXT_RECORD))
    ;Then the actual string Data
    For a = 1 To Length
        b = Asc(Mid(st, a, 1))
        WriteData(#XLS,@b, 1)
    Next

    ProcedureReturn  0   ;return with no error
   
EndProcedure

CreateFile(#XLS,"C:\test1.xls")
 
xlsWriteText2("a12345678....9a1234567",1,1,#XLS_Font1, #XLS_LeftAlign, #XLS_CellNormal, 0)

CloseFile(#XLS)

In Excel.pbi, change line 464 to this:

Code: Select all

value.l = (DateToJulian(DateString) - DateToJulian("19000100")) + 1

PureBasic Forums - English

Excelwriter Cell Limit?

Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?

Re: Excelwriter Cell Limit?