Reading excel file when MS Office is not installed..

[stoymigo]

Hi guys I would like to read an excel file via a vb6 program, reason why I want to do it bcos sometimes we run an app on a client's side but on the server and a server machine does not always have MS Office installed as client machines usually do:

I know I have two options, which I would have to try, they are:

1.Read excel file using ADO/OLEDB
2.Read excel file using the sql server - since it's on the server I can use the sql excel read functions.

I have heard of using OpenXML to read excel files, but this is used in .net.
Anyone have any other options before I try the above?

Thanks a lot

 

[ebendl]

The latest versions of Office (2007, 2010, 2013) uses the OpenXML format which is basically a zip-file with a bunch of XML files inside. There's huge specification for it here: http://www.ecma-international.org/publications/standards/Ecma-376.htm

You could thus unzip the file and use a VB6 XML parser to read the document - with a bit of work.

Alternatively you could look for an OpenXML library for VB6 - somebody might have developed one.

OF course, this is assuming the files you have to read has .xlsx extensions (not .xlsb or .xls).

 

[ChilliGirl]

Have you considered downloading MS excel viewer direct from MS? I know it is avail for 2007, have not tried looking for 2010.

You see it as you normally would, it does NOT allow you to save any changes you make while viewing...

 

[iblade]

You could use GemBox.Spreadsheet to do it for you or I prefer to open up Excel with OLEDB e.g.:

//Open Excel spreadsheet and get the sheets
            try
            {
                System.Data.DataTable dt = null;
                String connString = "";

                System.IO.FileInfo f = new System.IO.FileInfo(fileName);



                if (f.Extension == ".xls")
                {
                    connString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties='Excel 8.0';", fileName);
                }
                else
                {
                    connString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties='Excel 12.0 Xml';", fileName);
                }
                if (objConn.State != ConnectionState.Closed)
                    objConn.Close();

                objConn.ConnectionString = connString;
                objConn.Open();

                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);


                foreach (DataRow row in dt.Rows)
                {
                    cmbSheets.Properties.Items.Add(row["TABLE_NAME"].ToString());
                }
                

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

 

[Gozzy]

Why not just install Microsoft Excel Viewer (free) - http://www.microsoft.com/en-us/download/details.aspx?id=10

It weighs in at 78mb and you do not need to have any office components installed?

 

[stoymigo]

Thanks for the Microsof Excel Viewer suggestion to gozzy & chilligirl, but does it also install the relevant excel libraries that I could use to import normally as if ms office was installed?
I'm talking about reading it via code, not to view the file.

Thx.

 

[Cam]

You can use the ADODB connection/recordset to open/read the excel file in VB6. Try google for examples. Afaik you don't need excel installed to do this.

 

[The_Unbeliever]

Export it as a CSV file, then program your app to import and parse the CSV file?

 

[ChilliGirl]

Thanks for the Microsof Excel Viewer suggestion to gozzy & chilligirl, but does it also install the relevant excel libraries that I could use to import normally as if ms office was installed?
I'm talking about reading it via code, not to view the file.

Thx.

That you would have to experiment ... Then see if it works.

 

[guest2013-1]

Thanks for the Microsof Excel Viewer suggestion to gozzy & chilligirl, but does it also install the relevant excel libraries that I could use to import normally as if ms office was installed?
I'm talking about reading it via code, not to view the file.

Thx.

That you would have to experiment ... Then see if it works.

Very much doubt that the viewer has it's own API one can consume via your own application code to do any import/export work with. Not going to lecture, but why use VB6? I'm pretty sure .NET has the capability without having to have the server run Office etc

 

[stoymigo]

The csv option is logical, will consider it as a last resort though.

Very much doubt that the viewer has it's own API one can consume via your own application code to do any import/export work with. Not going to lecture, but why use VB6? I'm pretty sure .NET has the capability without having to have the server run Office etc

You're pretty sure?
I know .net is more versatile but I'm asking for vb6 not .net.

 

[stoymigo]

The csv option is logical, will consider it as a last resort though.
You're pretty sure?
I know .net is more versatile but I'm asking for vb6 not .net.

Although an app made separately in .net is a logical option, for some reason it totally went off my head that
most pcs will have .net framework installed.
But anyway I've done it in vb6, reading from excel and I did try using JET.OLEDB to read it into a recordset.
Does a pc have to have office installed for JET.OLEDB to exist?
Thanks

 

[The_Unbeliever]

Although an app made separately in .net is a logical option, for some reason it totally went off my head that
most pcs will have .net framework installed.
But anyway I've done it in vb6, reading from excel and I did try using JET.OLEDB to read it into a recordset.
Does a pc have to have office installed for JET.OLEDB to exist?
Thanks

Write a quick 'n dirty "test" app to check for certain functions... if it passes, then you know it's installed. If not, then you'll need to install office...

 

[scudsucker]

Beware: do you know 100% what version of Excel the file is?

Because some more modern types cant be opened the same way as older types, and vice versa.. so you do all your dev, everything seems to work, and then someone gives you a different format and you are screwed.

 

[stevenv]

As people previously mentioned, if you have the file in xlsx (OpenXML + MS extensions) format then you can just open the file as a ZIP archive and process the XML files in it. Nothing related to .NET.

 

[Rickster]

OpenOffice?

 

[stoymigo]

Beware: do you know 100% what version of Excel the file is?

Because some more modern types cant be opened the same way as older types, and vice versa.. so you do all your dev, everything seems to work, and then someone gives you a different format and you are screwed.

You're thinking ahead, plus you'll find between businesses there's a mix of 2007/10 and even the new office, I'll have to see if it's just a simple setting.
I'll have to test with the different versions, maybe even ask the client to tick what version of excel they use, or even better detect what version is the excel file.