xmlexcel

[Marc van Cauwenberghe]

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 Expand


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


[Paul Squires]

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.

[Marc van Cauwenberghe]

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

[Marc van Cauwenberghe]

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

[Paul Squires]

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.

[Marc van Cauwenberghe]

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 Expand


Replace "ë" With "e" In sText
Replace "é" With "e" In sText
Replace "ï" With "i" In sText


Marc

[Jean-pierre Leroy]

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 Expand


Function ReplaceAccentedCharacters(ByVal pSource As wString) As wString
    Replace Any "àáâãåäçèéêëìíîïñòóôõöùúûüÿýÀÁÃ,ÃÃ...Ã,,ÇÈÉÊËÌÍÎÏÃ'Ã'Ã"Ã"ÕÖÙÚÛÜÝ" With _
                "aaaaaaceeeeiiiinooooouuuuyyAAAAAACEEEEIIIINOOOOOUUUUY" In pSource
    Function = pSource 
End Function


Jean-Pierre

[Marc van Cauwenberghe]

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

Marc