RBLibrary: ExcelWriter

  1. ‹ Older
  2. 2 years ago

    @Simon B 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!

    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 "

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

  4. Simon B

    6 Jul 2015 Europe (Wiltshire, UK)

    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.

  5. @Simon B 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.

    I sent you a PM with my code. :D

    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

  6. Mark S

    28 Aug 2015 Pre-Release Testers, Xojo Pro

    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 WebFile

    on this line of code

    xl.OutputFile = DLfile

    Where DLfile is defined as a WebFile.

  7. Christian S

    28 Aug 2015 Pre-Release Testers, Xojo Pro Europe (Germany)

    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.

  8. Simon B

    28 Aug 2015 Europe (Wiltshire, UK)

    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.

    Application:
    Excel components:

  9. Alexandre C

    11 Sep 2015 Pre-Release Testers, Xojo Pro

    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

    // cabeçalho
    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

  10. Simon B

    11 Sep 2015 Europe (Wiltshire, UK)

    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-1

    and not

    For i As Integer = 1 To rs.FieldCount
  11. Dirk C

    14 Sep 2015 Pre-Release Testers, Xojo Pro Galactic Sector ZZ9 Plural Z A...

    On 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?

  12. last year

    Derek T

    19 Dec 2015 Pre-Release Testers, Xojo Pro Minneapolis

    Is there anyway to append to an xml spreadsheet already created?

  13. Simon B

    20 Dec 2015 Europe (Wiltshire, UK)

    @Derek T 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.

  14. Hi Simon!, Can I write Excel Formulas with your software?
    And How can I do that?

    Regards.

  15. @Gerardo Giacute;a Hi Simon!, Can I write Excel Formulas with your software?
    And How can I do that?

    Regards.

    Ok, I Answer myself:

      xl.SetCellFormula(2, 20, "=SUM(R[-16]C:R[-1]C)")
      xl.SetCellStyleID(2, 20, "SBS000005")

    But ow it Works?

    For Example If I want to SUM from B12 to B300?
    Thanks

  16. Simon B

    8 Apr 2016 Europe (Wiltshire, UK)

    The cell formula is relative to the cell itself.

    So, the answer to your question will depend upon where the result should be displayed (i.e. which cell). If it is the cell B301 then the formula would be

    =SUM(R[-288]C:R[-1]C)

    You need to set up a variable in your writing software to calculate the start and end points.

  17. @Simon B The cell formula is relative to the cell itself.

    So, the answer to your question will depend upon where the result should be displayed (i.e. which cell). If it is the cell B301 then the formula would be

    =SUM(R[-288]C:R[-1]C)

    You need to set up a variable in your writing software to calculate the start and end points.

    Thanks Simon, I got it. In fact I made a Variable in order to know how many rows Do I have and then pass it to the formula.

  18. 2 months ago

    Thats cool, I ve read in another forum that Einhugur that developed a new excel classes that creates Native xlsx files, but I can't find the link to download, I can only see the Old Excel Writer that generates a XML file, like Simon Berridge's classes

  19. Jean-Yves P

    Feb 16 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    xlsx files are in reality zipped files, they are easy to make
    xls files where only retro-engeneered so more difficult to make.

  20. Björn E

    Feb 16 Pre-Release Testers, Xojo Pro Iceland

    @Gerardo Giacute;a Thats cool, I ve read in another forum that Einhugur that developed a new excel classes that creates Native xlsx files, but I can't find the link to download, I can only see the Old Excel Writer that generates a XML file, like Simon Berridge's classes

    I have not released yet, I stalled it a little because of Xojo's 2016r4 new drawing system on windows, other plugins needed attention. But the Excel writer plugin is in really good shape and still coming soon.

  21. @Björn Eiacute;ksson I have not released yet, I stalled it a little because of Xojo's 2016r4 new drawing system on windows, other plugins needed attention. But the Excel writer plugin is in really good shape and still coming soon.

    Thanks Björn!, Its good to know that, And yes, Infact everyone are getting issues to adapt our codes to Newer version of Xojo :D

or Sign Up to reply!