PlanetSquires Forums

Please login or register.

Login with username, password and session length
Advanced search  
Pages: 1 [2] 3

Author Topic: Creating Excel WorkBooks without Automation  (Read 6859 times)

Klaas Holland

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 90
  • FF3 User
Re: Creating Excel WorkBooks without Automation
« Reply #15 on: March 16, 2013, 08:03:21 AM »

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?

« Last Edit: March 18, 2013, 01:03:07 PM by Klaas Holland »
Logged

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #16 on: March 18, 2013, 02:59:36 PM »

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!

Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Klaas Holland

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 90
  • FF3 User
Re: Creating Excel WorkBooks without Automation
« Reply #17 on: March 19, 2013, 12:52:09 PM »

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

Nathan Durland

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 81
Re: Creating Excel WorkBooks without Automation
« Reply #18 on: September 04, 2013, 11:25:59 AM »

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

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #19 on: September 04, 2013, 07:46:43 PM »

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.

:)
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

John Montenigro

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 310
Re: Creating Excel WorkBooks without Automation
« Reply #20 on: September 06, 2013, 06:08:15 PM »

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
« Last Edit: September 06, 2013, 06:10:25 PM by John Montenigro »
Logged

Wilko Verweij

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 148
  • PB10 - FF3.7 User; Win 10
    • CHEAQS
Re: Creating Excel WorkBooks without Automation
« Reply #21 on: September 06, 2013, 06:14:37 PM »

Quote
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
Logged

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #22 on: September 06, 2013, 06:25:11 PM »

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.
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Petrus Vorster

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 356
Re: Creating Excel WorkBooks without Automation
« Reply #23 on: September 07, 2013, 03:47:45 PM »

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

Nathan Durland

  • FireFly3 Registered User
  • Junior FireFly Member
  • *
  • Posts: 81
Re: Creating Excel WorkBooks without Automation
« Reply #24 on: October 04, 2013, 02:06:42 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.
Logged

Jean-pierre Leroy

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 461
  • expect nothing, be prepared for everything ...
Re: Creating Excel WorkBooks without Automation
« Reply #25 on: January 16, 2014, 01:43:21 PM »

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
Logged

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #26 on: January 16, 2014, 07:01:10 PM »

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>
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Jean-pierre Leroy

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 461
  • expect nothing, be prepared for everything ...
Re: Creating Excel WorkBooks without Automation
« Reply #27 on: January 17, 2014, 12:28:26 PM »

Quote
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
Logged

Paul Squires

  • Administrator
  • Master FireFly Member
  • *****
  • Posts: 8104
  • Windows 10
    • PlanetSquires Software
Re: Creating Excel WorkBooks without Automation
« Reply #28 on: January 17, 2014, 01:38:43 PM »

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).
Logged
Paul Squires
PlanetSquires Software
FireFly Visual Designer, WinFBE Editor

Jean-pierre Leroy

  • FireFly3 Registered User
  • Senior FireFly Member
  • *
  • Posts: 461
  • expect nothing, be prepared for everything ...
Re: Creating Excel WorkBooks without Automation
« Reply #29 on: January 17, 2014, 06:20:41 PM »

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
Logged
Pages: 1 [2] 3