Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

vb.net Create excel sheet without Excel

Posted on 2011-04-29
6
Medium Priority
?
677 Views
Last Modified: 2012-06-27
Hey,

I am looking for a way to reading and writing to excel sheets without the requirement of a specific Excel version being installed on the client PC.  Even though the PC will most likely have some version Excel installed, my company isn't locked down to one particular version.  That has caused me problems in the past.

To read excel sheets i have been using ODBC and that works fine, but i would like to come up with some standard way of being able to create excel sheets and not have to depend on the PC's installed version.

I have seen a few 3rd part components that would seem to be what i am looking for.  Does anyone have any experience with these components or another method?  To give a little scope, i don't need to be doing anything too complex with the sheets.  Just reading, writting, and some basic formatting..

Any suggestions on components (you have actual experience with) or alternative methods of accomplishing my goal are appreciated.
0
Comment
Question by:MRS
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35491124
0
 
LVL 3

Accepted Solution

by:
Mrugesh1 earned 1500 total points
ID: 35491134
0
 

Author Comment

by:MRS
ID: 35491222
Thank you both for the suggestions...

Code,

I have looked at the Apose Suite before, but they are out of my price range.  at $6000 per developer license it would be cheaper to but everyone in the company office..

Mrugesh1,
Have you had any experience with the GemBox component.  The price seems a little more reasonable if it works as well as it says it does.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 2

Expert Comment

by:stuartmorris
ID: 35514266
I like using the Excel COM object.  It's free with the application and has been around for several versions.  The syntax may change a bit from version to version, but if you're truly working on basic functionality, that shouldn't impact you.  I'll include a basic VBScript version of interacting with the object.  There is a TON of documentation on it, and you can include a reference to it from Visual Studio as well.  The intellisense there should help you figure out the specifics for your application.

The object model is here.
http://msdn.microsoft.com/en-us/library/wss56bz7%28v=vs.80%29.aspx

 
'Set output file name
Dim xls
xls = xlFilePath & "\" & xlFileName

'Create the excel application object
Dim Excel
Set Excel = CreateObject("Excel.Application")
Excel.DisplayAlerts = False

'Open the xls file if it exists, or create a new file if it does not exist
Dim wb
On Error Resume Next
err.Clear
Set wb = Excel.Workbooks.Open(xls)
If err.Number = 1004 Then
	err.Clear
	Set wb = Excel.Workbooks.Add()
End If
If err.Number <> 0 Then
	Exit Sub
End If
On Error GoTo 0

'Create excel worksheet and cell objects
Dim ws, c, pg
Set ws = wb.Sheets(1)
Set c = ws.Cells

'Add values to the spreadsheet
c(1,1).value = "TESTING1"
c(1,2).value = "TESTING2"
c(1,3).value = "TESTING3"
c(1,4).value = "TESTING4"
c(1,5).value = "TESTING5"

'Save the xls file
On Error Resume Next
err.Clear
'Save the xls file
wb.SaveAs(xls)
If err.Number = 1004 Then
	Set c = Nothing
	Set ws = Nothing
	Set wb = Nothing
	Set Excel = Nothing
	Exit Sub	
End If
On Error GoTo 0

'Close the xls file
wb.Close

Open in new window

0
 

Author Comment

by:MRS
ID: 35514357
stuartmorris,

Your example is what I have done in the past.  I am trying to get away from untyped objects like that.

I have decided to go with the Gembox component.  The price is fair and the component seems do to everything i need.  Thank you all for your input.

0
 

Author Closing Comment

by:MRS
ID: 35514368
I went with the GemBox component that was linked at the bottom of the forum page you linked.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question