Bring back the Excel API

[Pat Dooley]

Paul, I just wanted to put in my vote to return your Excel API as a download. The little bugger works really well. Glad I got it while I could.
The other day I thought I would put together some basic code to export an SQLitening set to Excel. It didn't take long. And Excel 2007 opens it, too. I don't know about Excel 2010 yet.
Here's a tip. If you use Paul's Excel API to convert a csv text file to Excel, don't use a generic Close to close your text file. This prevents the Excel file from being written to disk. It took me an hour to figure that out way back when.
Pat

[Roger Garstang]

This?

http://www.planetsquires.com/files/excelapi_pb.zip
http://www.planetsquires.com/files/excelapi_vb.zip

[Pat Dooley]

That would be it.

[Shawn Anderson]

I'd love to be able to write formulas using this.
I found information here http://www.opennet.ru/docs/formats/xls.txt
but don't know how to add it to excel.inc for PB.

Any takers?

thanks

[John Montenigro]

For anyone skulking around...

I just compiled and ran the .BAS in the .ZIP file, using the Excel.INC, and I found:
- the program ran fine and created the vbtest.xls file.
- MS Excel could open the file, but OpenOffice's scalc.exe could not.

In MS Excel, I unprotected the sheet and typed about 135 characters of text into
a cell (by hand), then did a File/SaveAs/filetype: "Excel 97-2003 Workbook (*.xls)"

Now OpenOffice's scalc opens the file, and all the stuff is there.

The original file was 950,881 bytes, the edited/SavedAs file was 1,854,464 bytes.

My laptop is running Win7 64-bit, OO version 4.1.1.
The company server is running Windows Server 2008 R2 STD, SP1, and MS Office Excel 2007 v12.0), SP3 (12.0)

PB compiler is v10.04, tried it with both PB and JR headers, no difference - OO still crashed before opening...

I will try to determine and report back (with a fix?) whatever is causing OO to crash during file read, but don't hold your breath! I'm only able to do a few hours of programming stuff each week...

-John

[Shawn Anderson]

thanks for the update

[John Montenigro]

OK, a few steps forward:

1. Near the end, commented out:
    stat& = xlsProtectSpreadsheet(%XLSTRUE) 
> no improvement

2. Re-instated the above line, and near the beginning, commented out:
    stat& = xlsBuffer(%XLSTRUE, (512 * 1024))  'a 512K buffer
> no improvement

3. Re-instated the above line, and near the end, commented out the loop of 50,000 entries
!!! OO did NOT crash during startup, and offered the "Text Import" popup panel.
Selected the displayed options, and the spreadsheet opened, with a lot of Chinese characters and only 5 rows or so of data.
None of the data appeared to match what's in the source code file...
> Closed the file without saving.

4. Tried to open the file again, and in the "Text Import", I selected "Western Europe (ASCII/US)"
This time, got 8 rows of readable English text from the source file, mixed with lots of control-type characters...
Repeated this again, selecting "System", and got the same result.

5.Went back and re-instated the loop, but reduced the number from 50000 to 20000.
> OO crashed on startup
reduced the number from 20000 to 10000
> OO crashed on startup
reduced the number from 10000 to 5000
> OO crashed on startup

reduced the number from 5000 to 1000
> OO opened the file and presented the "Text Import" screen.
I selected "Western Europe (ASCII/US)", and it showed 4 rows, the first 8 had text from the source file, and the rest
were mixed @-signs and punctuation characters, some Chinese, and some control chars...

At the "Text Import" panel, I tried turning on/off various choices of delimiters, but the preview never showed the expected results...

6. I began to suspect the xlsWriteNumber() function call in the loop, but it had been used earlier to create a shaded underlined number, and hadn't given any complaint.
But I commented out both, just to see if the other statements would give a readable file.
Also, looking for something simple as a replacement, I copied this line into the loop:
    stat& = xlsWriteText("F", 10, 1, %xlsFont3, %xlsFillCell, %xlsCellNormal, 0)

> After the Text Import panel, got an error message: "The maximum number of rows has been exceeded. Excess rows not imported." 

7. Went back and changed Number from 1000 to 100
> After the Text Import panel, got 8 lines as above...

8. Suspecting Cell locking, I commented out:
    stat& = xlsWriteText("This cell is locked.", 9, 1, %xlsFont3, %xlsLeftAlign, %xlsCellLocked, 0)
> After the Text Import panel, got 8 lines as above...

9. As I'm doing all these tests, I'm noticing that the .xls file size keeps getting smaller, till now it's only 2,130 bytes!!!
I don't think I've ever seen an .XLS that small! (Maybe part of the header is not being created properly?)

10. Removed commenting from all lines, but left the loop number at 100.
> After the Text Import panel, got 8 lines as above...
...now I'm beginning to suspect the font statements...

11. Frustrated, I went the opposite direction: commented out ALL lines that did anything in between Create and Close, except for one WriteText function. Still no good, and the file size is down to 574 bytes!

OK, now to plunge into the .INC file to see what I've been missing...

[ADDED]
After a quick review of the .INC, I can tell this is going to take me awhile to study...
Probably won't know anything new till next week...

I'm going to work off the hypothesis that OO has changed its understanding of the .XLS header...
Remember, the first file was able to be opened by MS Excel without any problems whatsoever...

-John

[John Montenigro]

Just to ensure I'm not entirely off track, I took a quick look at the fist few dozen bytes of two files in hex...

Here's the original file generated by the .bas file in the .zip:
It appears to match the xls.txt description file that's in the .zip.



And here's the file that was read into MS Excel and SavedAs an .XLS (97)


Very different structure! Wondering where to turn next...

-John

[ADDED]
Slogging thru toward the end (not shown) of the file SavedAs by MSExcel, I see that it's tagged BIFF8...
So, I'm going to check that out now...

[James Klutho]

If that is Paul's old Excel API (not the html version dll he did a couple of years ago), then it is pre Excel Workbook API and likely have a lot of compatibility problems with current Excel versions. Even Excel 97 is a WorkBook.

[John Montenigro]

 
Thanks James, I appreciate the info! Unfortunately, of all the code I've seen, Paul's old code is the easiest for me to understand. It uses TYPEs and Functions, and writes to the files in headers and data blocks -- stuff I "grew up with".

I suppose I'd be able to use the new COM stuff, if only I could find documentation that shows the object hierarchy... for example, from my PB code, I want to write "JOHN" into cell B2 of my spreadsheet. I can't figure out which objects I need to create in order to get to a Cell. I just don't have a body of information in which I can "reverse search" in order to identify all the other objects I need to have, or how to create and/or structure them. With Jose's Includes, I get really lost searching for interfaces that often have repeating members, but you have to know how to recognize which one is the one you need...

(That's why I was attracted to the title of this thread!)

I would be really grateful if anyone can provide a "map" to Excel-related COM objects! Help me get to the point where I can write my name into a spreadsheet cell!!!

Thanks!
-John

[Michael Meeks]

John

Give me a couple of days - I'll take a look at my excel code notes and get back to you. 
Hopefully, I can at least give you some point or place to start.

Regards

[John Montenigro]

Thanks for your offer, Michael,
I appreciate it!
-John

[José Roca]

Don't expect to find anything related with Office in my include files because the only reliable way to work with Excel and Word is to use late binding, and you don't need interface declarations.

[John Montenigro]

Thanks Jose,
Maybe little tidbits to others, guidance like that is a great help to me! I could spend a week scrounging around and wondering why I'm not getting anywhere!

Meanwhile, I'm going to use Dominic Mitchell's code in this thread:
http://www.powerbasic.com/support/pbforums/showthread.php?p=476145#post476145
as a guide to the first problems I need to solve.

But I will soon need to go further, into Rows, Columns, Ranges, and Cells in order to finish my project.

Anyone with hints, tips, or suggestions about how to get there?
Thanks,
-John

[James Klutho]

A few years ago Paul used the MS xml spreadsheet format to create Excel workbooks without COM.  Paul never released his source code but you can probably recreate it by studying up on MS documentation.

XML Spreadsheet Reference
https://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx

Save the source below to Test.xml and open in up in Excel

Code Select Expand


<?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>Someone</Author>
    <LastAuthor>Self</LastAuthor>
    <Created>2012-03-15T23:04:04Z</Created>
    <Company>Eaton Corporation</Company>
    <Version>11.8036</Version>
  </DocumentProperties>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>6795</WindowHeight>
    <WindowWidth>8460</WindowWidth>
    <WindowTopX>120</WindowTopX>
    <WindowTopY>15</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
      <Alignment ss:Vertical="Bottom" />
      <Borders />
      <Font />
      <Interior />
      <NumberFormat />
      <Protection />
    </Style>
    <Style ss:ID="s21">
      <Font x:Family="Swiss" ss:Bold="1" />
    </Style>
  </Styles>
  <Worksheet ss:Name="Sheet1">
    <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="5"
   x:FullColumns="1" x:FullRows="1">
      <Row>
        <Cell>
          <Data ss:Type="String">Text in cell A1</Data>
        </Cell>
      </Row>
      <Row>
        <Cell ss:StyleID="s21">
          <Data ss:Type="String">Bold text in A2</Data>
        </Cell>
      </Row>
      <Row ss:Index="4">
        <Cell ss:Index="2">
          <Data ss:Type="Number">43</Data>
        </Cell>
      </Row>
      <Row>
        <Cell ss:Index="2" ss:Formula="=R[-1]C/2">
          <Data ss:Type="Number">21.5</Data>
        </Cell>
      </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <Print>
        <ValidPrinterInfo />
        <HorizontalResolution>600</HorizontalResolution>
        <VerticalResolution>600</VerticalResolution>
      </Print>
      <Selected />
      <Panes>
        <Pane>
          <Number>3</Number>
          <ActiveRow>5</ActiveRow>
          <ActiveCol>1</ActiveCol>
        </Pane>
      </Panes>
      <Table>
      </Table>
      <ProtectObjects>False</ProtectObjects>
      <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
  </Worksheet>
</Workbook>