Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Klaas Holland]

This is a very useful tool Paul.

Just two questions:
a. How can one change the default RowHeight e.g with Fontsize > 16?
b. How can one save the file to a different folder e.g. on the server?

[Paul Squires]

Hi Klaas,

I have updated the xmlExcel file in the link found in the first post of this thread.

You can now specify a default row height for each spreadsheet in a workbook (ws.DefaultRowHeight).

If you want to save the file to a different folder then all you need to do is add path information to the the filename that you specify in wb.filename

Hope that helps!

[Klaas Holland]

Thanks Paul,

This works fine for me.

However I wonder why the Rowheight does not expand automatically when one chooses a large Fontsize?
I think this is the normal procedure in Excel.

[Nathan Durland]

Paul -- if you are still tinkering with this project, a nice addition would be the ability to  set the "Wrap Text" property on a cell.

[Paul Squires]

Hi Nathan, the reference that I used is located here:  http://msdn.microsoft.com/en-us/library/office/aa140066(v=office.10).aspx
It is the XML Spreadsheet Reference.

I have added the WrapText attribute. I have updated the download in the link in the first post of this thread. Try the new package to see if it works for you.

[John Montenigro]

An update to the classes and SLL.

. . .

For performance reasons, only rows up to 20,000 and columns up to 100 are checked (via an efficient bit array). This should be sufficient for the majority of workbooks you create. No checking outside that range so if you inadvertently add a duplicate cell then you will get a "corrupted file" from Excel when loading.

Hi Paul, thanks for this capability... I'm going to try writing that dump-a-ListView-to-XML function that someone else wrote about. Should be a healthy way of getting back into PB/FF coding...

Question about the row and column numbers you mentioned. Are they hard limits?

One project I'm working on has datasets with 300 columns. Is that going to be a problem?

Also, if I'm not mistaken, this is a write-only technique; the .SLL object is not able to read an .XML file?

Thanks,
-John

[Wilko Verweij]

the .SLL object is not able to read an .XML file?

That's right, John. The idea is that your (or my) program can create xml-files to enable users (or yourself) to read them with Excel.

Wilko

[Paul Squires]

There are no hard limits on rows or columns. You are only limited by whatever the limits are for Excel itself. The SLL only does checks on a certain range of rows and columns for performance reasons (it attempts to detect duplicate defined cells). If you add cells outside that range then it is your responsibility to ensure that it is not a duplicate cell that was previously defined. Duplicate cells will cause a corrupted xml spreadsheet file that Excel will not load.

[Petrus Vorster]

This tool had saved me eons of work in my last project.
I am SO thankful for it!

[Nathan Durland]

I have added the WrapText attribute. I have updated the download in the link in the first post of this thread. Try the new package to see if it works for you.

Thanks very much!  This tool is a very cool addition to our arsenal.

[Jean-pierre Leroy]

Hi Paul,

Today I used your "xmlExcel.sll" to replace old .csv extraction I had.

Your classes are very flexible; thanks a lot.

I have small issue with the WrapText attribute; with your sample code, I can see in the xml file a style with the WrapText attribute.

Code: [Select]

<Style ss:ID="s3">
  <Font ss:FontName="Calibri" ss:Size="10" ss:Color="#000000"/>
  <Alignment ss:WrapText="1"/>
 </Style>

In my source code, I put this code after adding a cell value :

Code: [Select]

iCell.WrapText  = %TRUE
But in the generated xml file there is no reference to a WrapText attribute.

Any ideas ?

Thanks
Jean-Pierre

[Paul Squires]

Can you post the generated xml for the cell that you set the WrapText = %TRUE. For example, in the sample program included with the SLL, the generated xml is:

Code: [Select]

  <Row ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">This is a very long string of text that should wrap.</Data></Cell>

What is it for the data you are using in your program?

That cell generates xml code that refers back to StyleID "s3". That style is defined as:\

Code: [Select]

<Style ss:ID="s3">
  <Font ss:FontName="Calibri" ss:Size="10" ss:Color="#000000"/>
  <Alignment ss:WrapText="1"/>
 </Style>


[Jean-pierre Leroy]

Can you post the generated xml for the cell that you set the WrapText = %TRUE.

Hi Paul,

You will find enclosed the generated xml and the source code of a test program in which I generated the first line of the file (the header).

As you will see in the generated xml file, there is no reference to WrapText in the style. In my source code I put the attribute iCell.WrapText  = %TRUE for three cells.

I hope that you can find something. Thanks for your support.
Jean-Pierre

[Paul Squires]

Thanks Jean-Pierre, looks like I was missing an IF/THEN test in my code when comparing the WrapText attribute of a cell to previously defined styles. I fixed the code and now it is generating the new style (in your sample case, you should be getting a "s3" style generated). Hopefully the new generated code will produced the desired results in your spreadsheet.

Please let me know if the problem is fixed or if I need to do more on it. If it is okay then I will update the general download zip file.

(see attached zip file for the new test SLL).

[Jean-pierre Leroy]

Thanks Paul,

Now it works as expected; The new style "s3" with WrapText="1" is correctly generated in the xml file.

Thanks for the quick fix.
Jean-Pierre