Hey Simon, Is this possible to make a multiline cell?
And is it possible to add an image to the spreadsheet?
At this time, no.
Hey Simon.
I want to make bigger the border lines, I saw that you have this function: xl.SetStyleBorders(0,0,0,0). It says that needs four arguments. How can I use it?
Thanks
The four parameters are Top, Bottom, Left and Right. These are boolean values that say that the border should be shown.
The class does not allow for any line style other than continuous at this time.
Simon.
[quote=196969:@Simon Berridge]The four parameters are Top, Bottom, Left and Right. These are boolean values that say that the border should be shown.
The class does not allow for any line style other than continuous at this time.
Simon.[/quote]
Hey Simon, I got a problem. I can read the XLS generated by your class with Excel on Mac, but when I try to open on Windows, it says me that This is not the format.
I know that in fact its an XML file with XLS extension.
The Issue is that when I clic “Yes” it shows me an error "File couldn’t be displayed " Error on “Table”
What can I Do?
Thanks
Parsing the Error provided by Microsoft on the Error Log it says me this:
XML error on Table
Reason: Incorrect Value
File: C:\Users\ancy Rodsan\Desktop\Reporte de Facturas Recibidas- RFC (MCE0708241W5).xml
GRoup: Row
Label: Cell
ATRIB: StyleID
Value: centro
and so says the same with each row but only this column “RFC”
WHat am I doing wrong?
Thanks
A lot depends on what version of XLS the XML is for vs what version of XLS you are using.
This is why our corporate XLS writer software always makes a Excel 2003 compatible file
If it works on the Mac then it should work on a PC.
Check how you are creating styles within your source code. This is where the error is being reported.
Also, as Dave said above, you need to be running an Excel version that can read the XML format. As far as I can remember the format is Excel 2004 but don’t quote me on that!
[quote=198550:@Simon Berridge]If it works on the Mac then it should work on a PC.
Check how you are creating styles within your source code. This is where the error is being reported.
Also, as Dave said above, you need to be running an Excel version that can read the XML format. As far as I can remember the format is Excel 2004 but don’t quote me on that![/quote]
Yeah, You were right. I probed the code, and the error happens when I try to Style the Cells.
For Example:
xl.SetCellStyleID(10,i+4, “Moneda”)
Where 10 = the Column Number
i= the row Number, I plus 4 to start to write in the fourth row.
And “Moneda” is the name of the Style declared at the beginning of code:
xl.AddStyle(“moneda”)
xl.StyleHorizontal = xlAlignRight
xl.StyleNumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Also I tried with xl.StyleColumn(x,“style”) and Have the same result. It opens In Office 2016 Mac, but doesn’t in Office 2015 Windows.
If I don’t Use Styles, I Can open successfully the File on both platforms.
Please will you either post the xl create code or send it to me via a PM so I can look at what you are doing.
I cannot recreate your issue at all! When I set up a style it just works on both platforms.
[quote=198925:@Simon Berridge]Please will you either post the xl create code or send it to me via a PM so I can look at what you are doing.
I cannot recreate your issue at all! When I set up a style it just works on both platforms.[/quote]
I sent you a PM with my code. 
So, I also noticed when I parse a big listbox, for example with 2,200 rows. The XML excel file get malformed and I got Table Error.
When I have a listbox with 800 rows I got no problem
Can this work with a Webfile? I have a couple of WebApps and this would be a free way to download an Excel file.
If not I guess I can use the MBS LibXL functions or a CSV formatted file.
I get this error when I compile:
Type mismatch error. Expected class FolderItem, but got class WebFileon this line of code
xl.OutputFile = DLfileWhere DLfile is defined as a WebFile.
LibXL works in web apps just fine.
Anyway, you can create a temp file or create file in memory as string and pass both to Webfile for download to user.
My excel classes are now available from my free Xojo components page. There is, also, a companion free application (RBSourceFromXLS) that will take an XLS file (formatted as an XML file - 2004 of Excel) and create the source code that you would use in an application to output an XLS file.
Hi Simon!
I’m trying to use this your SimonsExcel.zip for the very first time.
It’s very easy to understand and to use.
I made some changes on code, to export from RECORDSET to EXCEL.
// EXCEL
dim xl As new xlWorkbook
xl.OutputFile = destination
xl.SetZoom(100)
xl.SheetDisplayGrid = False
xl.SheetDisplayZeroes = False
’ Styles
xl.AddStyle(“CABECALHO”)
xl.StyleHorizontal = xlAlignCenter
xl.StyleVertical = xlAlignCenter
xl.StyleBold = True
// EXCEL
dim i as integer = 1
dim j as integer = 1
// cabealho
for i=1 to rs.FieldCount // I’m getting an UnsupportedOperationException here!!!
xl.SetCellValue(i, 1, rs.IdxField(i).Name.DefineEncoding(encodings.UTF8) )
xl.SetCellStyleID(i, 1, “CABECALHO”)
next
Do you know why?
Best regards!
Alex
This is a database issue and not an Excel issue. Your loop is wrong. It should be:
For i As Integer = 0 To rs.FieldCount-1and not
For i As Integer = 1 To rs.FieldCountOn top of what Simon said:
The Xojo manual says:
If you try to use a RecordSet after it has been closed, an UnsupportedOperationException is raised.
Are you sure your recordset is open when you call the fieldcount?
Is there anyway to append to an xml spreadsheet already created?
Not at present.
I have created code that will read an XML spreadsheet but have not combined this with the write option.
Sorry.