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
This?
http://www.planetsquires.com/files/excelapi_pb.zip
http://www.planetsquires.com/files/excelapi_vb.zip
That would be it.
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
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
thanks for the update
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
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...
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.
:(
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
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
Thanks for your offer, Michael,
I appreciate it!
-John
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.
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 (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
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
<?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>
I gave up on trying to make consistently working XLS files some time ago, and switched to XML files.
I made a "template" XLSX file, saved it as XML, then used that to write files that can be opened fairly consistently in Excel.
Still, I'd love to have an Excel DLL plug in that worked well with both old and modern versions of Excel, WITHOUT having to have Excel installed. XML does that pretty well.
John,
You and maybe a few others might be interested in these format equates used by the PB Excel Demo.
I'm still searching through my files and will post more when I find them.
The zip file contains a single INC file.
Regards
;D
Dear all,
You can try http://libxl.com
With the latest version they provide headers (.inc) for PowerBASIC.
Regards,
Jean-Pierre
Jean-pierre Leroy,
I downloaded the files - but no PowerBasic Include file is included. I am a registered user of that dll.
If you have a copy - please post it.
Thanks
ExcelFormats.inc File updated. I had a couple of errors.
Thanks
I have used the .SLL for excel by Paul without a hitch, but the need for some serious Excel output had become a great necessity.
Things like opening a new document in the latest (Office 2013) file format, creating graphs, backgrounds, sized & sorted columns, opening and altering content in existing Excel documents, and so forth would be great.
A DLL would be heaven if one can just make simple calls to make it work. Anyone here done work in that direction?
I've just posted some useful (I think) information about LibXL on the PowerBASIC forum:
https://forum.powerbasic.com/forum/user-to-user-discussions/special-interest-groups/programming-microsoft-office/757750-about-libxl-excel-library-for-developers
Jean-Pierre
After some research into Excel XLXS files it is quite surprising how they assembled it.
This also explains why programmers look for a tool to create it as it is a lot of work.
The XlXS file is nothing more than a ZIP file with different folders inside with lots of XML files for every purpose.
It will be a lot of work to create a tool that can do that with ease hence the smallest developer tools starting at $200.
Probably not going to be easy to DIY for hobbyists.
For now Paul's SLL is just fine.