|
Excel Class - write to an XLS file without DLL's or Excel automation
|
Email
|
| Submitted on: |
10/4/2000 3:29:37 PM |
| By: |
Paul Squires
|
| Level: |
Intermediate |
| User Rating: |
By 77 Users |
| Compatibility: |
VB 4.0 (32-bit), VB 5.0, VB 6.0 |
| Views: |
126061 |
|
|
|
|
|
Writes an Excel BIFF 2.1 spreadsheet file DIRECTLY. No external DLL's or Excel automation needed. Great for exporting data from your application to an Excel spreadsheet without the overhead of JET or ADO.
This is a small update that includes the ability to create Horizontal Page Breaks and to specify default formats for numbers and dates (for example, the number 20000 could be given a format of #,### in the file - thanks to Dieter Hauk).
Updated June 20/2001: Fixed lost row 32768 if more than 32767 rows are output to the BIFF file. Thanks to Sid Eaton for noticing this problem.
Updated November 10/2001: Added support for default row height and the ability to set the row height of individual rows. Example on how to save dates to the file.
|
 |
| |
|
 Download code
Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come
out every day, so no prevention program can catch 100% of them. For your own safety, please:
- Re-scan downloaded files using your personal virus checker before using it.
- NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.
- Scan the source code with Minnow's Project Scanner
If you don't have a virus scanner, you can get one at many places on the net
including:McAfee.com
|
Terms of Agreement:
By using this code, you agree to the following terms...
- You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
- You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
- You may link to this code from another website, but ONLY if it is not wrapped in a frame.
- You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
|
Report Bad Submission
|
Your Vote
|
| |
Other User Comments
|
 10/5/2000 3:01:02 AM: Robert Croll
Thats EXCELent. Very nice, well written code. If you continue developing this module please repost. Ran it on NT4 and error 52 occured, Bad file name or number, at "Put #FileNumber, , END_FILE_MARKER" in the Close File Function. I skipped the line and if saved the file no worries.
(If this comment was disrespectful, please report it.)
|
10/5/2000 5:05:06 AM: jan van herp
Very fast and usefull. Thank you.
(If this comment was disrespectful, please report it.)
|
10/5/2000 8:42:25 AM: gjones
Good coding, lots of comments!
Fast way of making XLS files.
BTW I have NT 4 SP5 and had no problems running the sample
(If this comment was disrespectful, please report it.)
|
10/5/2000 1:09:21 PM: Chris
Excellent! You get 5 globes from me. :) Keep up the good work.
(If this comment was disrespectful, please report it.)
|
10/5/2000 1:16:50 PM: Mohit Vij
Great information and very nicely presented. 5 Star.
(If this comment was disrespectful, please report it.)
|
10/5/2000 2:00:49 PM: Tom de Lange - Ariel Systems
Excellent! Well commented. 5 Star work! Runs perfectly on Win98SE, Excel97
(If this comment was disrespectful, please report it.)
|
10/5/2000 3:36:10 PM: carl.tice@vacsew.com
deffinately would like to see some formulas. Does anyone know how to import data into VB from an Excel spreedsheet?
(If this comment was disrespectful, please report it.)
|
10/5/2000 4:08:58 PM: eric650
Great code, really useful for generating charts from a database app.
(If this comment was disrespectful, please report it.)
|
10/5/2000 4:26:02 PM: Euskaldun bat
On Win.95, I got the 52 error, the
same as Robert Croll, anyway good
code, my vote "excellent" for you.
(If this comment was disrespectful, please report it.)
|
10/11/2000 11:01:23 PM: Kunal Johar
Gee, this code seems awful familiar... It is available on www.vb-helper.com and it has been for about 2 years
(If this comment was disrespectful, please report it.)
|
10/12/2000 4:02:38 PM: Paul Squires
Kunal,
You obviously haven't looked at the vb-helper code very closely. If you did you would see that this class is vastly different. I have spoken to Dan Gardner about his class and he was extremely pleased with my class because it does 99% more than his. For example, Dan's class does not do fonts, bolds, strikeouts, underlines, borders, alignment, locked and hidden cells, column widths, and margins. Also, his class was limited to only 256 rows!
Before you accuse me of copying code you should have all the facts. The BIFF file specification is not a secret. I'm sure many people have created code for it.
By the way, I spoke with Rod Stephens (of vb-helper) two weeks ago and he has posted this Excel class in his advanced code section.
(If this comment was disrespectful, please report it.)
|
10/12/2000 6:52:49 PM: Kunal Johar
I solemly apologize for my comment. You are 100% right, I did not look at the code carefully and I was wrong. I hope you'll accept my apology, next time I will not be quick to judge without knowing the facts. Once again I am sorry. Also I took a look at your code and it is worth an excellent, even more so after the trouble I have caused. Sorry again, Kunal johar
(If this comment was disrespectful, please report it.)
|
10/12/2000 9:02:02 PM: Paul Squires
Kunal,
No problem and no need for apologies.
: )
(If this comment was disrespectful, please report it.)
|
11/16/2000 5:26:32 PM: zpadawer@net2phone.com
This code rates A1! I've tried various different ways of exporting to excel and so far this is the fastest and easiest. It also allows me to have multiple instances of the program running at the same time as it doesn't open and close excel.
thank you very much.
PS: Does anyone know how to autoformat the column widths?
Creating Charts would be great as well. Any idea anyone?
thanks!
(If this comment was disrespectful, please report it.)
|
11/20/2000 3:39:04 AM: xiyuan
This code is very good. But I can not write some Chinese words into it. Do you know how to add Chinese font supporting?
(If this comment was disrespectful, please report it.)
|
11/20/2000 3:40:35 AM: xiyuan
Nice code. But I can not write even one Chinese word into the excel file. Do you know how to add Chinese font surporting in this project?
(If this comment was disrespectful, please report it.)
|
11/26/2000 1:11:12 PM: antiduh
whoa. perfect. i'm writing a comprehensive gui version of netstat (that does tons more than give tcp and udp connections, such as icmp, tcp, ip, udp connection stats, traceroute, ping...) and i wanted a way to export some of the tables (such as the traceroute output) to a xls, and didnt want to use the object library (yuck, eww eww eww. *gag*) so this is quite handy. it also exports to mdb, but thats not quite a popular, and i AM using the object library for that, but that is more popular. perhaps xls to mdb w/o object libray usage would be another cool project for you *wink* *wink*. thanks
-antiduh.
ps, i plan on releaseing core components of my source code on psc, when im happy with it.
(If this comment was disrespectful, please report it.)
|
11/30/2000 4:39:29 AM: N Kruse
Bravo! Very well done, I can think of many place use this code. '5' IT IS.
(If this comment was disrespectful, please report it.)
|
11/30/2000 9:26:23 AM: Carlos
Nice code, but can I write to an existing Office 2000 xls.
(If this comment was disrespectful, please report it.)
|
11/30/2000 11:21:12 AM: Brad McDonald
Excellent!!!!
What is the trick to do formulas?
(If this comment was disrespectful, please report it.)
|
1/9/2001 2:55:14 PM: Toby
This Rocks! I've been searching everywhere for BIFF export code. No more CSV files for me!!! Thanks Paul.
(If this comment was disrespectful, please report it.)
|
2/19/2001 2:05:11 PM: Markito
Do you know how to write an Excel File with more than one sheet?
Tks!
(If this comment was disrespectful, please report it.)
|
2/19/2001 9:02:11 PM: Paul Squires
Unfortunately the BIFF 2.1 specification does not allow multiple worksheets as far as I know.
Sorry.
(If this comment was disrespectful, please report it.)
|
3/27/2001 9:46:05 PM: Emil Nadinic
Great code Paul, been looking for something like this for a long time. I ended up compiling it into an ActiveX control for use within an ASP page, and it works like a charm.
Great Work, again.
Emil.
(If this comment was disrespectful, please report it.)
|
4/1/2001 10:25:53 PM: QQQ
excellent! 5 stars for u
(If this comment was disrespectful, please report it.)
|
4/22/2001 10:39:21 PM: Paul Squires
Thanks to everyone who has posted comments on this site and who have emailed me personally. I never in my wildest dreams thought that this code would have been so well received by everybody. Now if only I could figure out formulas...... :)
(If this comment was disrespectful, please report it.)
|
6/22/2001 4:33:41 AM: Jun Santiago
regarding your updates in the missing row 32768 you fixed it by skipping to the next row and you did'nt write anyting on row 32768, is there a way to write a value inside row 32768?
(If this comment was disrespectful, please report it.)
|
7/27/2001 9:52:10 PM: Houston McClung III
Wonderful Code! Top of the LINE! You don't know it, but this code practically saved my hide.
(If this comment was disrespectful, please report it.)
|
8/10/2001 6:54:26 AM: R C Sharma
Nice. How do I use it in my project where I want to save data on a grid to Excel file??
(If this comment was disrespectful, please report it.)
|
1/14/2002 5:17:48 PM: Rob Gerwing
You wouldn't happen to now where to find BIFF 7 or 8, or is the BIFF 2.1 compatible with all Office 97,2000, and XP? Can this utilize charting?
(If this comment was disrespectful, please report it.)
|
2/25/2002 1:03:53 PM: wileecoy
How about reading an Excel file without the Excel Object reference?
Anything on that?
btw - code is excellent.
(If this comment was disrespectful, please report it.)
|
2/25/2002 3:15:46 PM: Paul Squires
wileecoy,
You lost me. The code doesn't use any Excel Object reference. The whole purpose of the code is to avoid that. :-) If you don't have the latest code then maybe the code at my site is more up to date. http://www.planetsquires.com
(If this comment was disrespectful, please report it.)
|
5/13/2002 2:33:33 AM: kookai
Fast solution of Excel for me
(If this comment was disrespectful, please report it.)
|
5/23/2002 4:39:36 PM: Javier
Hello,
I Didn't Test the class yet, but the idea is great!!
I'll tell you when I test it.
(If this comment was disrespectful, please report it.)
|
6/25/2002 12:15:24 PM: Steve
If you dim lrow and lcol as variants instead of longs you can pass variable values to the write value function. Makes it easier to work with loops and record sets. Public Function WriteValue(ValueType As ValueTypes, CellFontUsed As CellFont, Alignment As CellAlignment, HiddenLocked As CellHiddenLocked, lrow As Variant, lcol As Variant, value As Variant, Optional CellFormat As Long = 0) As Integer
(If this comment was disrespectful, please report it.)
|
7/2/2002 9:25:21 PM: Ryan
The Code is very useful. But how can i export Chinese words to Excel because i had tried but failed. Can you help me?
(If this comment was disrespectful, please report it.)
|
7/4/2002 1:08:20 AM: andreas
nice code
i havent test it yet but with so many comment
i can't wait to try it
^_^
oh yeah
anyone now how to read from the excel?
(If this comment was disrespectful, please report it.)
|
8/7/2002 10:58:23 AM: Sergio
Hi, this code is great, but I have a problem, when I try to export a long text string like 264, 400 or longer, I get an overflow error in the textlength property, I dont know how to solve this, please, if the author can help me, that would be great, my email is sgil@ceticostacna.com.pe. Thank you
(If this comment was disrespectful, please report it.)
|
8/24/2002 2:17:56 AM: webJose
Sergio: I haven't tested it, but for what I have read about the BIFF 2.1 specification for worksheets, you cannot write string values of more than 256 bytes. That is just the way it is. :( But please, if you prove me wrong, I'd appreciate if you let me know.
(If this comment was disrespectful, please report it.)
|
10/23/2002 9:33:05 PM:
Fantastic idea, saves on office licences thats for sure
(If this comment was disrespectful, please report it.)
|
11/4/2002 9:27:51 AM:
Paul,
Code is very impressive. I do have one question. I create an excel file using this class and when I open it with excel I get a memory error. (The instruction at
(If this comment was disrespectful, please report it.)
|
11/6/2002 9:12:08 AM:
Hi, great piece of code, thanks.
I discovered that manipulating a row's height will hide this row, when opening the file in Excel 2000, after saving this file in Excel it will be displayed again. Maybe you can do something about this.
Rob
(If this comment was disrespectful, please report it.)
|
11/12/2002 8:33:37 PM:
Great Code. But I'm having
some difficult to make it work on vb .NET
(If this comment was disrespectful, please report it.)
|
11/27/2002 10:41:43 PM:
Because of your code my wife has had a better time at work
counting records. I wrote a program that counts the items
she has to count at the end of every month for work.
Of course I could have used the ‘CountIf’ function in excel
but it was more fun to write a stand alone app that exported
to excel. Thanks for the code man I got a little extra this month if you know
what I mean.
(If this comment was disrespectful, please report it.)
|
1/6/2003 10:42:43 AM:
Hi,
great code,
PS: Does anyone know how
to put color in the cell
(If this comment was disrespectful, please report it.)
|
1/13/2003 6:02:30 AM: The Saint
HI, REALY GREAT CODE!!!
DO YOU HAVE ANY IDEA HOW TO SUPPORT GREEK CHARACTERS?
THANKS
tsitsirigos_fotis@yahoo.gr
(If this comment was disrespectful, please report it.)
|
1/24/2003 6:28:46 AM:
Russian support chars solved !!
tjn.web@mail.ee
(If this comment was disrespectful, please report it.)
|
1/30/2003 11:58:10 AM:
Hi,
This code is exellent. I have a requirement where I have to insert a image(jpg) and some statistical data into excel. Pls let me know if this is possible.
My email : ycmahesh@hotmail.com
(If this comment was disrespectful, please report it.)
|
3/26/2003 4:17:01 AM:
Hi,
This code is very excellent !
I'm just lookin how to display a number with 3 or more decimal and how to use the function
(If this comment was disrespectful, please report it.)
|
4/8/2003 2:52:05 PM:
Impressive testimonials!! I was searching for reading from excel file without the need to open excel object....Is that possible?
(If this comment was disrespectful, please report it.)
|
4/21/2003 7:24:04 PM:
Could you please let me know a work around to write text into the .xls having more than 256 characters. Appreciate your help in this.
Thank you.
(If this comment was disrespectful, please report it.)
|
4/22/2003 3:00:13 AM:
I changed some lines to support Chinese. Do you need my version?
(If this comment was disrespectful, please report it.)
|
4/22/2003 9:29:27 PM:
That will be great.
Please do send it.
Thank you.
My email anilemail17@yahoo.com
(If this comment was disrespectful, please report it.)
|
4/25/2003 12:45:43 PM:
To support Chinese:
Case ValueTypes.xlsText
Dim b As Byte
Dim barray() As Byte
st$ = CStr(value)
barray = StrConv(st$, vbFromUnicode)
l% = UBound(barray) + 1
Dim TEXT_RECORD As tText
With TEXT_RECORD
.opcode = 4
.length = 10
'Length of the text portion of the record
.TextLength = l%
'Total length of the record
.length = 8 + l
.Row = Row%
.col = col%
.rgbAttr1 = CByte(HiddenLocked)
.rgbAttr2 = CByte(CellFontUsed + CellFormat)
.rgbAttr3 = CByte(Alignment)
'Put record header
Put #FileNumber, , TEXT_RECORD
'Then the actual string data
For a = 0 To UBound(barray)
Put #FileNumber, , barray(a)
Next
End With
(If this comment was disrespectful, please report it.)
|
8/9/2003 2:59:19 PM:
Excellent code, but isn't it possible to make some functions to READ from an excel sheet?
I mean the reading an excel file without excel dll's version of this class?
And is there a possibility to make a word document creator? (at least from a template)
(If this comment was disrespectful, please report it.)
|
8/11/2003 6:28:23 PM:
Outstanding Code!
I've found some minor bugs and changed formatting, declarations (-> option explicit) etc.
If someone want that
(If this comment was disrespectful, please report it.)
|
9/16/2003 9:12:17 PM:
Better than Excellent!!
You saved me a lot of time & code is good and very well commented.
Five Stars from me!!
(If this comment was disrespectful, please report it.)
|
9/23/2003 3:55:23 AM: Parvez
Excellent code 5* from me!
Only if theres autowidth and color options then its a masterpiece
(If this comment was disrespectful, please report it.)
|
1/2/2004 12:37:28 PM:
Merge is possible?
(If this comment was disrespectful, please report it.)
|
2/11/2004 2:10:18 PM: Quick Sliver
Kick ass!! I've been trying to write something like this for a while and failing badly. a '5' and then some!
(If this comment was disrespectful, please report it.)
|
3/15/2004 9:08:33 PM: Pietro ing. Cecchi
great work
(If this comment was disrespectful, please report it.)
|
4/1/2004 8:34:49 PM: Unruled Boy
I port it to VB.NET: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=2246&lngWId=10
(If this comment was disrespectful, please report it.)
|
4/21/2004 5:17:47 PM:
very usefull!
Is there a way to include functions within the created spreradsheets?
(If this comment was disrespectful, please report it.)
|
7/20/2004 12:41:26 AM:
Excellent job....
But, how to insert or embedd picture to Excel file using this class?
(If this comment was disrespectful, please report it.)
|
7/23/2004 4:03:42 AM:
It's Nice
But when the result file open by
Oppen Office it's open in Text format
and when open by excel then I save as
in other file name (with no change contents) , the size is different
(If this comment was disrespectful, please report it.)
|
9/1/2004 9:16:03 PM:
Awesome code!!!...
How to go about appending data to the xls file??
(If this comment was disrespectful, please report it.)
|
3/22/2005 1:30:37 PM:
Excellent code,
does anyone know how to change worksheet name instead of using filename as default? Any help will be greatly appreciated
(If this comment was disrespectful, please report it.)
|
3/29/2006 6:59:06 AM: Alberto
Very good code,
does anyone know how to add more than 1 worksheet to the same xls file?
(If this comment was disrespectful, please report it.)
|
6/5/2006 11:38:03 PM: LIM
Prefect code....
But, how to insert picture/image to Excel file using this class?
i think the code should look like this format
Case ValueTypes.xlsPicture
Dim PICTURE_RECORD As tPicture
With PICTURE_RECORD
.opcode = 3
.length = 15
.Row = Row%
.col = col%
.rgbAttr1 = CByte(HiddenLocked)
.rgbAttr2 = CByte(CellFontUsed + CellFormat)
.rgbAttr3 = CByte(Alignment)
.picture = value
End With
Put #FileNumber, , PICTURE_RECORD
End Select
anyone help
(If this comment was disrespectful, please report it.)
|
7/14/2006 12:25:08 PM: ali ansi
so gooooooooooooood Paul Squires you got it
thank you send me a message
my mail is ans_sw2003@yahoo.com
i'm prof. in VB like you
(If this comment was disrespectful, please report it.)
|
9/6/2006 8:04:17 AM: Øystein Selbekk
Jolly good code... Exactly what i needed.
Thanks!!!!
(If this comment was disrespectful, please report it.)
|
2/22/2007 11:39:29 AM: Jyotiraditya Rath
Nice code and faster than excel automation.Need some more addition like cell color formatting (Background & Font) to complete the basic excel file creation.
(If this comment was disrespectful, please report it.)
|
3/12/2007 1:53:09 PM: Carlos Hidalgo
hello guys, i have this problem.
For x = 1 To MyGrid.Rows - 1
For Y = 1 To MyGrid.Cols
'Code commented because was given an error and forced excel(2000) to be closed when i tryed to open files created with the class, with excel 2003 work fine
'If IsNumeric(MyGrid.TextMatrix(x, Y - 1)) And Len(Trim(MyGrid.TextMatrix(x, Y - 1))) <= 3 Then
' .WriteDefaultFormats
' .WriteValue xlsnumber, xlsFont0, xlsLeftAlign, xlsNormal, x + 1, Y, CDbl(MyGrid.TextMatrix(x, Y - 1))
'Else
.WriteValue xlsText, xlsFont0, xlsLeftAlign, xlsNormal, x + 1, Y, MyGrid.TextMatrix(x, Y - 1)
'End If
Next Y
Next x
hope you can help me.
Thanks and nice code
(If this comment was disrespectful, please report it.)
|
8/10/2007 9:41:03 AM: Amit
Dear Sir,
The Class is excellent.How to change the color of the text in a cell using the class.
Thanking You,
yours faithfully,
Amit
(If this comment was disrespectful, please report it.)
|
5/14/2008 1:54:12 AM: devdas kamath
Is there any code to export it BIFF 8.0 format.
(If this comment was disrespectful, please report it.)
|
8/4/2008 11:33:51 PM: web-bugs
how to create the excel file in multiple(more than one) worksheet?
(If this comment was disrespectful, please report it.)
|
8/4/2008 11:34:45 PM: web-bugs
How to create a multiple (more than one) worksheet
(If this comment was disrespectful, please report it.)
|
2/17/2009 1:52:49 AM: Rupali
I want to paste(write) image in xls file.
in vb.net.
can u plz help me..
(If this comment was disrespectful, please report it.)
|
11/19/2009 1:22:03 PM: Alberto
Hi, the code is very good, but I think that the trouble "Fixed lost row 32768 if more than 32767 rows are output to the BIFF file" isn´t resolved. Can you say me if you are solved this trouble and the piece of code for it?
Thanks
(If this comment was disrespectful, please report it.)
|
5/6/2010 8:10:18 AM:
VB6 version Class is excellent and works fine. Is there a .net version (vb or c#)?
(If this comment was disrespectful, please report it.)
|
4/6/2011 9:10:56 AM: Alex
I've made a VB .net version of this code - I'm just confirming with Paul if he wants to post it here.
(If this comment was disrespectful, please report it.)
|
10/10/2011 12:52:01 AM: mauricio
Hi: Is the VB.net version available? txs.
(If this comment was disrespectful, please report it.)
|
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
To post feedback, first please login.
|
