PlanetSquires Forums

Please login or register.

Login with username, password and session length
Advanced search  

Author Topic: xmlexcel  (Read 725 times)

Marc van Cauwenberghe

  • Senior Member
  • ***
  • Posts: 339
  • Vista Home Premium sp2
xmlexcel
« on: March 07, 2013, 10:16:04 AM »

Hi Paul,

have been trying to use the sll you made but I am getting error opening the xml file in excel 2007.
Following .bas gives me an corrupt xml:
It just sets a fex column widths.
Any ideas?

Code: [Select]
LOCAL wb       AS iExcelWorkBook
   LOCAL ws       AS iExcelWorkSheet
   LOCAL iCell    AS iExcelCell

   ' Create a Workbook class
   LET wb = CLASS "clsExcelWorkBook"

   ' Assign some Workbook info. This stuff is optional.
   wb.filename = "Simple.xml"

   ' Add a worksheet and call it "Sheet1"
   LET ws = wb.AddWorkSheet("Sheet1")

   ws.SetColumnWidth 1, 70
   ws.SetColumnWidth 2, 200
   ws.SetColumnWidth 3, 58
   ws.SetColumnWidth 4, 58
   ws.SetColumnWidth 5, 18


   ' Write our workbook to disk
   wb.WriteXML

   ' Clean up after ourselves
   SET ws = NOTHING
   SET wb = NOTHING

   '
   ? "Done."
                                         
Logged

Paul Squires

  • Administrator
  • Guru Member
  • *****
  • Posts: 8848
  • Windows 10
    • PlanetSquires Software
Re: xmlexcel
« Reply #1 on: March 07, 2013, 03:32:25 PM »

Hi Marc,

I am pretty sure that Excel expects there to be a defined "cell" within the column that you setting the width for. So, for example, if you do a ws.AddCell to put data into the second column then you will also be able to set the widths of the first and second columns... you would not be able to set the width of the third, fourth, etc...

I have tried this for a few scenarios and it seems to be the case.

So, I guess the moral of the story is, do not set column widths for column numbers greater than the highest numbered cell that you have created.
 
Logged
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Marc van Cauwenberghe

  • Senior Member
  • ***
  • Posts: 339
  • Vista Home Premium sp2
Re: xmlexcel
« Reply #2 on: March 08, 2013, 09:24:02 AM »

Ok,

I kinda figured something like that.
I actually have to make an overview with collered cell green and red so I do not really need to put anything in the cell.
I will try with a space char or something like that.

Thanks,

Marc
Logged

Marc van Cauwenberghe

  • Senior Member
  • ***
  • Posts: 339
  • Vista Home Premium sp2
Re: xmlexcel
« Reply #3 on: March 09, 2013, 07:27:24 AM »

Hi Paul,

Thank you very much for this sll. This is really excellent and just what I needed.
I have 2 questions:

1) is it possible to set the cell border color?

2) is it possible to retrieve a reference of a cell without using the addcell method?

Thanks all the same.

Marc
« Last Edit: March 09, 2013, 11:53:33 AM by Marc van Cauwenberghe »
Logged

Paul Squires

  • Administrator
  • Guru Member
  • *****
  • Posts: 8848
  • Windows 10
    • PlanetSquires Software
Re: xmlexcel
« Reply #4 on: March 09, 2013, 11:21:36 AM »

Hi Marc,

I have made some changes to the library. You should now be able to specify colors for cell borders (iCell.BorderColor) and you can retrieve previously created cells using the new GetCell method (iCell = ws.GetCell).

You can download the new package from the first post in the xmlExcel topic:  http://www.planetsquires.com/protect/forum/index.php?topic=3208.msg23580

Please let me know if you run into any trouble.
Logged
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Marc van Cauwenberghe

  • Senior Member
  • ***
  • Posts: 339
  • Vista Home Premium sp2
Re: xmlexcel
« Reply #5 on: March 09, 2013, 01:19:17 PM »

No problem with the new update Paul, works fine. Thank you again.

Now there is one thing I had to do to get special char working. It is the 'quick and dirty' method but I will look for another method.

Code: [Select]
Replace "" With "e" In sText
 Replace "" With "e" In sText
 Replace "" With "i" In sText

Marc
Logged

Jean-pierre Leroy

  • Senior Member
  • ***
  • Posts: 469
  • expect nothing, be prepared for everything ...
Re: xmlexcel
« Reply #6 on: March 10, 2013, 07:52:07 AM »

Hello Marc,

I don't know if that could be useful for you, I use this function to replace special and accented characters:

Code: [Select]
Function ReplaceAccentedCharacters(ByVal pSource As wString) As wString
    Replace Any "" With _
                "aaaaaaceeeeiiiinooooouuuuyyAAAAAACEEEEIIIINOOOOOUUUUY" In pSource
    Function = pSource 
End Function

Jean-Pierre
Logged

Marc van Cauwenberghe

  • Senior Member
  • ***
  • Posts: 339
  • Vista Home Premium sp2
Re: xmlexcel
« Reply #7 on: March 10, 2013, 11:53:37 AM »

That is very handy!
Thank you very much Jean-Pierre.

Marc
Logged