• Welcome to PlanetSquires Forums.
 

Creating Excel WorkBooks without Automation

Started by Paul Squires, October 15, 2012, 10:50:42 PM

Previous topic - Next topic

Klaas Holland

#15
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!

Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

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.

:)
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

John Montenigro

#20
Quote from: TechSupport on October 21, 2012, 01:12:51 AM
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

Quotethe .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.
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Petrus Vorster

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

Nathan Durland

Quote from: TechSupport on September 04, 2013, 07:46:43 PM
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.

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


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:

  <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:\
<Style ss:ID="s3">
  <Font ss:FontName="Calibri" ss:Size="10" ss:Color="#000000"/>
  <Alignment ss:WrapText="1"/>
</Style>

Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Jean-pierre Leroy

QuoteCan 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).
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

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