PlanetSquires Forums

EDIT:  2017-07-19, I have attached the source code to this post. I haven't touched the code in two years (at least) so I am unsure of its quality

EDIT:  The latest version of the library can always be downloaded from this link:    http://www.planetsquires.com/files/xmlExcel.zip (http://www.planetsquires.com/files/xmlExcel.zip)

Hi Guys,

I have been busy lately writing an application for my work. A requirement is to output Excel compatible workbooks. As you may know, I wrote old school BIFF2 Excel files years and years ago. That file type is now considered a security risk by Excel so I can't use it.

I thought about using COM automation but is seems so slow and at times unreliable if an instance fails or goes screwy under Windows.

I looked at the current Excel Open XML format. Very powerful but somewhat complicated and requires the resulting files to be compressed (zip format) into an .xlsx file.

My research allowed me to stumble on the Excel 2003 XML format (SpreadsheetML). I can't believe that we as a PowerBasic community have not written code for this before. It is pretty easy to create the XML files and incredibly fast.

The reason for this post is to let you know that I have a class to handle all of this stuff just about complete. It will allow us to output our data into Excel ready files.... no more .CSV files!

Here is what the class does so far:

- Create Workbooks with as many Worksheets in them that you wish.
- Rows up to 1,000,000 and columns to 16,000 (that is the current Excel limits).
- Create default style for the entire Workbook but still allow individual cells to have their own styles.
- Styles include things like: Font names, font size, font color, bold, underline, italic, background cell colors, borders (left/right/top/bottom), text and number alignment (left/right/center).
- Cells can be spanned across as many columns as you wish. For example, you can span four columns and center the text.
- Formulas. That's right, embed formulas directly into your file. Stuff like =SUM(A1:A3) is easy.
- Fast!  Create 100,000 cells in 0.28 seconds  and then write them to the XML file in 2.56 seconds... and that is not even optimized fully yet.

I still have a few things left to write like allowing setting the printer and print preview settings directly in your file. I will be testing the class this week in my application and then I'll post it here for you guys. Maybe not the source code but at least an SLL.

I usually don't post much about the code I write but this code is pretty exciting because it solves a long standing issue that many of us face everyday when trying to get our reports into Excel.

Stay tuned...

Sounds exciting. I certainly will try this!
Wilko

Wow. Sounds very cool! Would it handle images too? (Always need the company logo  :D )

Sounds interesting Paul (as your projects usually do), I'll look forward to testing it.

Quote from: Robert Eaton on October 16, 2012, 08:28:57 AM
Wow. Sounds very cool! Would it handle images too? (Always need the company logo  :D )

Unfortunately not. I am pretty sure (not 100% though) that the specification can not handle binary data. I'd have to check into that. Would be cool though to be able to display logos and stuff.

BTW, here is the specification: http://msdn.microsoft.com/en-us/library/office/aa140066(v=office.10).aspx

I just used the class in a real world application of mine. Works very well however the method I am using for creating styles and applying them to cells is a little cumbersome in practice. I need to change that stuff in order to make it easier and more intuitive for the programmer to use. Will a need a few days on this one.

Quick update: The code is working great now. I am very happy with the internal workings and generated xml output. It mirrors the Microsoft generated xml pretty well.

Here is what the code looks like:

#Include "clsWorkBook.inc"

Function PBMain() As Long

   Local wb       As iExcelWorkBook
   Local ws       As iExcelWorkSheet
   Local iCell    As iExcelCell
   

   Let wb = Class "clsExcelWorkBook"

   wb.filename = "test.xml"
   wb.author   = "Paul Squires"
   wb.company  = "PlanetSquires"

   ' Set some default font info that will apply to the
   ' entire workbook. The defaults are:
   '   .FontName      = "Calibri"
   '   .FontSize      = 11
   '   .FontColor     = "#000000"   ' black
   ' Change our default font to Arial 10pt.
   wb.DefaultFontName = "Arial"
   wb.DefaultFontSize = 10


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

   iCell = ws.AddCell( 1, 2, "Right" )
   iCell.AlignRight = %TRUE

   iCell = ws.AddCell( 3, 2, "Yellow!" )
   iCell.FontSize  = 10
   iCell.CellColor = "#FFFF00"

   iCell = ws.AddCell( 5, 2, "Box" )
   iCell.BorderLeft   = %TRUE
   iCell.BorderTop    = %TRUE
   iCell.BorderRight  = %TRUE
   iCell.BorderBottom = %TRUE


   ' Merge/Span across 3 columns (4 total)
   iCell = ws.AddCell( 5, 5, "Span and Center" )
   iCell.MergeAcross = 3     ' cols 5,6,7,8
   iCell.AlignCenter = %TRUE         
   iCell.FontColor   = "#FF0000"   'red
   iCell.CellColor   = "#AFEEEE"   'blue

   
   ' Add 3 values and then use a SUM formula to total them
   iCell = ws.AddCell( 8, 1, 100 )
   iCell = ws.AddCell( 8, 2, 200 )
   iCell = ws.AddCell( 8, 3, 300 )
   iCell = ws.AddCell( 8, 4, 600 )
   iCell.Formula = "=SUM(RC[-3]:RC[-1])"

   ' Add 3 values and then use a + formula to total them
   iCell = ws.AddCell( 9, 1, 100 )
   iCell = ws.AddCell( 9, 2, 200 )
   iCell = ws.AddCell( 9, 3, 300 )
   iCell = ws.AddCell( 9, 4, 600 )
   iCell.Formula = "=RC[-3]+RC[-2]+RC[-1]"


   ' Add another worksheet...
   Let ws = wb.AddWorkSheet("Sheet2")

   ' Add another worksheet...
   Let ws = wb.AddWorkSheet("Sheet3")

   ' Write our workbook to disk
   wb.WriteXML

   ' Clean up after ourselves
   Set ws = Nothing
   Set wb = Nothing

   ? "Done."

End Function


I have attached a screenshot of the resulting spreadsheet.

Here is what the resulting xml code looks like:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Paul Squires</Author>
<LastAuthor>Paul Squires</LastAuthor>
<Created>2012-10-18T00:00:00Z</Created>
<LastSaved>2012-10-18T00:00:00Z</LastSaved>
<Company>PlanetSquires</Company>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>7416</WindowHeight>
<WindowWidth>17220</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>48</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
  <Alignment ss:Vertical="Bottom"/>
  <Borders/>
  <Font ss:FontName="Arial" ss:Size="10" ss:Color="#000000"/>
  <Interior/>
  <NumberFormat/>
  <Protection/>
</Style>
<Style ss:ID="s1">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s2">
  <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s3">
  <Borders>
   <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
   <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
   <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
  </Borders>
</Style>
<Style ss:ID="s4">
  <Font ss:FontName="Arial" ss:Size="10" ss:Color="#FF0000"/>
  <Interior ss:Color="#AFEEEE" ss:Pattern="Solid"/>
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
</Styles>

<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="8" ss:ExpandedRowCount="9" x:FullColumns="1"
  x:FullRows="1" ss:DefaultRowHeight="14.55">
  <Row ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s1"><Data ss:Type="String">Right</Data></Cell>
  </Row>
  <Row ss:Index="3" ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s2"><Data ss:Type="String">Yellow!</Data></Cell>
  </Row>
  <Row ss:Index="5" ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">Box</Data></Cell>
    <Cell ss:Index="5" ss:MergeAcross="3" ss:StyleID="s4"><Data ss:Type="String">Span and Center</Data></Cell>
  </Row>
  <Row ss:Index="8" ss:AutoFitHeight="0">
    <Cell><Data ss:Type="Number">100</Data></Cell>
    <Cell><Data ss:Type="Number">200</Data></Cell>
    <Cell><Data ss:Type="Number">300</Data></Cell>
    <Cell ss:Formula="=SUM(RC[-3]:RC[-1])"><Data ss:Type="Number">600</Data></Cell>
  </Row>
  <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="Number">100</Data></Cell>
    <Cell><Data ss:Type="Number">200</Data></Cell>
    <Cell><Data ss:Type="Number">300</Data></Cell>
    <Cell ss:Formula="=RC[-3]+RC[-2]+RC[-1]"><Data ss:Type="Number">600</Data></Cell>
  </Row>
</Table>
</Worksheet>

<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
  x:FullRows="1" ss:DefaultRowHeight="14.55">
</Table>
</Worksheet>

<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
  x:FullRows="1" ss:DefaultRowHeight="14.55">
</Table>
</Worksheet>

</Workbook>


Now I need to work on the numeric and date formatting.

Okay guys, here it is. The first SLL for you to play with. I will be using it tomorrow to test Excel xml generation in one of my applications. If I encounter any issues with the classes or make any changes then I will upload the changes/updates here.

If you use the SLL then please let me know what you like or hate about it. Can only make it better through feedback from everyone.

Please do not share this SLL outside of this forum. I am not ready to unleash this stuff on the PowerBASIC masses yet.

Obviously you need PB 10+ to use this SLL.

Do not create multiple cells at the same row/col. The class does not check to see if a cell already exists at a given row/column. This is done for performance reasons.

EDIT: Download link in first post of this thread)

Thanks,

Paul

I like it already!
With your example with BIFF2 was the problem that
openoffice could not open this Format.

More and more people are using openoffice or libreoffice,
so I had to change the exportformat in some of my Programs from xls to csv.

The use of your class is very simple, I will definitely use it.

Thank you for your great work!

Rudolf Fürstauer

Very nice, Paul.  MLG or SQLite straight to Excel.  No tab-delimited in between.  What fun!

I just finished using the classes in my application to output a perfectly formatted, formula enabled, Excel Workbook. Works perfectly and coding it was a breeze.

The only gotcha at this point is a couple of times I inadvertantly created more than one cell at the same row/col. That ended up causing a "can not load, corrupted file" message from Excel. I plan to build in a couple of bit-arrays into the class to determine if a row/col already exists thus preventing a second one from being added (may simply return the existing cell).

Very nice... of course... the only drawback of .SLL is that we can't look-up the methods...

And, if we have UNICODE or not... will we hiccup?

You know me, a fan of Jose's "includes" so I can scan that source...

: )

Quote from: Jim Dunn on October 19, 2012, 02:23:49 PM
Very nice... of course... the only drawback of .SLL is that we can't look-up the methods...

And, if we have UNICODE or not... will we hiccup?

You know me, a fan of Jose's "includes" so I can scan that source...

: )

Hi Jim,

All of the methods/properties are shown in the xmlExcelTest.bas sample code. The code is built using ANSI methods.

An update to the classes and SLL.

New properties added to the Workbook class to better customize print properties:

PageMarginHeader, Single
PageMarginFooter, Single
PageMarginTop, Single
PageMarginLeft, Single
PageMarginRight, Single
PageMarginBottom, Single
PageCenterVertical, Long     ' true/false
PageCenterHorizontal, Long   ' true/false
PageLandscape, Long     ' true/false
PagePortrait, Long      ' true/false
FitToPage, Long       ' true/false


The class will now check for the case where you try to create a duplicate cell at a row/column location. 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.

Added a property called "Version" to the Workbook class so you can return the version of this library.

(EDIT: Download link in first post of this thread)

This will be a useful class Paul!

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?

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!

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.

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.

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.

:)

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

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

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.

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

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.

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

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>


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

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

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

I've come across a difficulty that as far as I can tell, looks like I'm bumping up against something in the SLL, although I'm probably doing something dumb.  I'm attempting to create a pretty wide spreadsheet (211 columns in all), but get a crash not too far in.  Here's relevant code; if the bits between the #IF 0/#ENDIF are added, it runs OK, otherwise it crashes after 50 iterations of the xLoop.  The SLL file I'm using is dated 1/17/2014.



  local xLoop   as long
  local xlRow, xlCol   as long
  local FirstDate   as string

  For xLoop = 104 To 1 Step -1

#IF 0
    If xLoop > 55 Then
      xlRow += 2
      xlCol = 3
    End If

    If xLoop > 5 Then
      xlRow += 2
      xlCol = 3
    End If
#endif

    FF_StatusBar_SetText HWND_MAINFORM_STATUSBAR, 1, "XLoop: " + Format$(xLoop) + " Row: " + Format$(xlRow) + " Col: " + Format$(XLCol)
    FF_DoEvents
    Sleep 10
 
    xlCol += 1
    iCell = ws.AddCell( xlRow, xlCol, FirstDate)
    iCell.AlignCenter = %True
    iCell.MergeAcross = 1
    iCell.CellColor = fnMakeExcelColor(%RGB_KHAKI)
   
    iCell = ws.AddCell( xlRow+1, xlCol, "Orders")
    iCell.AlignRight = %True
    iCell.CellColor = fnMakeExcelColor(%RGB_KHAKI)

    xlCol += 1
    iCell = ws.AddCell( xlRow+1, xlCol, "Qty")
    iCell.AlignRight = %True
    iCell.CellColor = fnMakeExcelColor(%RGB_KHAKI)
   
    FirstDate = SQLDateMath(FirstDate, 7)   
 
  Next xLoop


Any insight appreciated.

I am having a hard time following why you are using a STEP -1 loop and then using that value to determine how to set the row value. If you comment out the "Orders" and "Qty" lines then it will compile and run with no problems. That would lead me to believe that the problem would be the xlRow + 1 being used. Maybe the loop counting backwards and then adding to the rows is causing some type of overflow error or something. Not sure at this point.

The library is designed to build the spreadsheet from low row/col numbers to high. Any reason why you are using a backwards stepping loop with incrementing rows? Could the logic be reversed?

Quote from: TechSupport on March 25, 2014, 02:47:15 PM
I am having a hard time following why you are using a STEP -1 loop and then using that value to determine how to set the row value. If you comment out the "Orders" and "Qty" lines then it will compile and run with no problems. That would lead me to believe that the problem would be the xlRow + 1 being used. Maybe the loop counting backwards and then adding to the rows is causing some type of overflow error or something. Not sure at this point.

The library is designed to build the spreadsheet from low row/col numbers to high. Any reason why you are using a backwards stepping loop with incrementing rows? Could the logic be reversed?

The backward loop is because the objective is a rolling 2 year weekly data dump (i.e. the previous 104 weeks).  I used the negative step planning on adding some other code once I have this sorted out.  The variable 'FirstDate' comes in seeded with the oldest date, then as the loop progresses gets closer and closer to today. The loop variable (xLoop) value is not used to calculate the rows or columns; that's what xlRow and xlCol are for.   Each of those starts with a small value and are then incremented as needed.  I've been tinkering, as it occurred to me also that the problem was with writing a cell will the (xlRow+1).  I changed the code to the following (including losing the negative step loop).  This code will go successfully through the first loop, then fail on the first pass of the second.  It fails on the ws.AddCell call.


  xlCol = 4
  LOGIT "--> row " + Format$(xlRow)
  For xLoop = 1 to 104

    FF_StatusBar_SetText HWND_MAINFORM_STATUSBAR, 1, "XLoop: " + Format$(xLoop) + " Row: " + Format$(xlRow) + " Col: " + Format$(XLCol)
    FF_DoEvents
    Sleep 10
    If gUserCancel Then GoTo ExitFunc

    iCell = ws.AddCell( xlRow, xlCol, FirstDate)
    iCell.AlignCenter = %True
    iCell.MergeAcross = 1
    xlCol += 2

    FirstDate = SQLDateMath(FirstDate, 7)   
  Next xLoop
 
  xlRow += 1
  LOGIT "--> row " + Format$(xlRow)
 
  '//-  We really created 208 columns in the first loop
  '     So, now we do 208; even numbered are for orders, odd ar for quantity for that week
  For xLoop = 1 To 208 

    xlCol = 3 + xLoop
    tLong1 = (xlCol Mod 2)
    tStr1 = IIF$(tLong1 = 0, "Ords", "Qty")
    If gUserCancel Then GoTo ExitFunc
    LOGIT "XLoop: " + Format$(xLoop) + " Row: " + Format$(xlRow) + " Col: " + Format$(xlCol) + " " + tStr1
    LOGIT "--> add cell"
    iCell = ws.AddCell( xlRow, xlCol, tStr1)
 
  Next xLoop


As John noticed some time ago, this class is for writing XML-files. But there is a GetCell option. I tried to use that to read data from an .XML-file but could not get it working. Does anybody have a simple example? Or is it just impossible?

Thanks for help
Wilko

The GetCell option is just for retrieving data that you have already created in memory prior to writing the file to disk.
GetCell does not read from an existing xml file. Sorry.

Thank for your quick reply. It's a pity, but the option to create XML-files was very useful in other projects already, so thanks.
Wilko

Original post in thread has been updated with a new download to address a memory load issue. Internally, the class destructor for the spreadsheet class was not ERASE'ing an array allocated that held the cell data. Simply setting the Spreadsheet class to NOTHING was not enough. The array held references to the cells as well.

Thank you Paul for this update I use your SLL every day in my projects.

Regards,
JEan-Pierre

I use this tool extensively but haven't used the function to draw borders between cells.
This function :  iCell = ws.AddCell( 5, 2, "Box" )
   iCell.BorderLeft   = %TRUE
   iCell.BorderTop    = %TRUE
   iCell.BorderRight  = %TRUE
   iCell.BorderBottom = %TRUE
I assume it draws a border around a cell? I haven't tried this yet.
Can the Sll help me change line thickness and color of borders as well?
It would make my reports look SO MUCH better!!!

Finally, i want the Excel sheet have CUSTOM FILTER on the page so the user can only select the dropdowns in the sheet without having to go through the whole process of setting up the Custom Filter. Some of the users are not really Excel Savvy.

Just started using this today. It is going to be very useful.

Paul, I know you have moved on from PB, but if you ever get the urge to look at this class again:

If the requested output file is already open when writing the file with wb.WriteXML an error 70 occurs that
I can't seem to trap, presumably because it occurs in code in the SLL.
I can of course check if the file is open before using the class, but it is not exactly elegant.

It would be nice if there was something like wb.GetLastError that could be examined and any errors trapped within the SLL and reported by wb.GetLastError.

And, if you have the time and energy, could you include the NumberFormat tag?

As always, thanks for your great work.

Hi Ian,

I have attached the source code the first post in this thread. It's been a very long time since I looked at the code so it may be good or it may be able to be improved on a lot.

Good luck!  :)

Thanks Paul.

Quote from: TechSupport on July 19, 2017, 09:40:33 PM
Hi Ian,

I have attached the source code the first post in this thread. It's been a very long time since I looked at the code so it may be good or it may be able to be improved on a lot.

Good luck!  :)

Paul, I know this is resurrecting an old topic, but the link to your xmlExcel.sll is no longer valid. I've apparently misplaced my previous download. Is the zip file still available somewhere?

Thanks!

Quote from: On Top Systems LLC  on November 11, 2023, 07:49:48 AMPaul, I know this is resurrecting an old topic, but the link to your xmlExcel.sll is no longer valid. I've apparently misplaced my previous download. Is the zip file still available somewhere?

Thanks!

Link still seems to work for me. You could try the https secure link. Maybe that is the problem?
https://www.planetsquires.com/files/xmlExcel.zip

BTW, I have just finished a FreeBasic version of this code. I am waiting for Peter to download and try it. I sent it to his email but he'll probably not see it until he returns to work after the weekend.

Thanks, Paul. The https link works fine.

The FB version works like a charm.

Well done Paul!

Ps: How do one do a Shellexecute in FB?
Shell is slow and shows a DOs window, so that cannot be right.

This is perfect for my needs. Thank you.

-Peter

Quote from: Petrus Vorster on November 13, 2023, 09:25:33 AMPs: How do one do a Shellexecute in FB?

Maybe something like this?

   dim ShExecInfo     As SHELLEXECUTEINFOW 
   dim wszCommand     As CWSTR
   dim wszParameters  As CWSTR

   With ShExecInfo
      .cbSize       = Len(SHELLEXECUTEINFOW)
      .fMask        = SEE_MASK_NOCLOSEPROCESS
      .HWnd         = 0
      .lpVerb       = Null
      .lpFile       = wszCommand
      .lpParameters = wszParameters  
      .lpDirectory  = 0
      .nShow        = SW_SHOWNORMAL
      .hInstApp     = 0
   End With

   ShellExecuteEx(@ShExecInfo)



Hi Paul

That works pretty well, and it opens the file-open dialogue.

After some struggles, I came up with this :
shellexecute (NULL,"Open","TEST.XML",0,0,1)

This opens the file immediately in Excel, but I was hoping you could take a look and tell me if this is correct or is there a better way to do this?

-Thank you

Peter