Solved

Excel Automation ... Accomodate Excel 2003 and 2007 Workbooks

Posted on 2009-05-04
3
2,302 Views
Last Modified: 2013-12-17
Hi -

I have a application that works with Excel 2003 but now users are using Excel 2007.  We want to make the app work with 2003 and 2007.

Background:  I developed an application that automates Excel 2003 using C#.Net in VS 2005.  The development machine has Excel 2003 loaded on it.  The C#.Net application set references the the Office and Excel 11.0 object library.  The User machine also had Excel 2003 loaded on it.  The application "builded", deployed, installed and ran just fine on the user's machine.

Change:  The users are now saving Excel workbooks in both Excel 2003 and Excel 2007 and of course we want the C#.Net program to be able to read both formats (i.e., the Excel 2003 format (.xls) and the three Excel 2007 formats ... .xlsx, .xlsm and .xlsb).

Question:  What do I have to do to the original program to make it work with Excel 2003 and 2007?

It seems to me that I have to load Excel 2007 on the development machine and revise the reference to the Office/Excel 2007 Object Library 12.0 ... do I then drop the reference to the Office/Excel 2003 11.0?

Also need to adjust the connection string.  If Excel 2003 then the Provider is Jet; If Excel 2007 then Provider is Ace.  Something like this:

if (extension == ".xls")
  conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + ";" +      "Extended Properties=Excel 8.0;");
else
   conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + file + ";" + "Extended Properties

I've also included the (abbreviated) original Code in case there is something else I need to do:


using System;

using System.Collections.Generic;

using System.Text;
 

using System.Collections;

using System.Data;

using System.Data.OleDb;
 

using Excel = Microsoft.Office.Interop.Excel;

using System.Reflection;

using System.Windows.Forms;
 

using System.Data.SqlClient;

using System.Diagnostics;
 

namespace BusinessTier

{
 

    public class GetExcelData

    {

        //Holds Map of Excel Field Names to Database Field Names
 

        //private Hashtable ht = new Hashtable();
 

        public GetExcelData()

        {

            //LoadColNames();

        }
 

        public DataTable GetExcelDataTable(string filepath, string filename, string sheetname)

        {

            string cnstr = BuildConnectionString(filepath, filename);

            DataTable dt = GetExcelDataTable(cnstr, sheetname, filepath, filename);

            return dt;

        }
 

        private string BuildConnectionString(string filepath, string filename)

        {

            //The "IMEX=1" parameter was added to force fields with mixed coding (both numeric and text) to import to C# as a text field.

            //  This bypasses Excel's nature of picking a data type based on the majority datatypes in the column of cell (e.g., if more cells 

            //  in the column contained text than numeric, Excel/C# assigns a string datatype to the field, nullifying numeric values)
 

            return ("Provider=Microsoft.Jet.OLEDB.4.0;" +

                    "Data Source=" +

                    filepath + filename + ";" +

                    @"Extended Properties=""Excel 8.0;IMEX=1;""");

                    //"Extended Properties=Excel 8.0;");

        }     
 

        private DataTable GetExcelDataTable(string cnstr, string sheetname, string pathname, string filename)

        {

            string cn = cnstr;

            DataTable dtTreatyType = new DataTable();

            string select = String.Empty;

            DataTable dt = new DataTable();

            try

            {

                OleDbDataAdapter daTreatyType = new OleDbDataAdapter("SELECT * FROM [Data Capture$E1:E2] ", cn);

                daTreatyType.Fill(dtTreatyType);

                if (dtTreatyType.Rows[0][0].ToString() == "CAT")

                    select = "SELECT * FROM [Data Capture$A1:HJ7] ";

                else

                    select = "SELECT * FROM [Data Capture$A1:HJ17] ";

                OleDbDataAdapter da = new OleDbDataAdapter(select , cn);

                da.Fill(dt);

            }
 

            catch (Exception e)

            {

               throw new Exception(e.Message); 

            }
 

            //If excel decides that the column of "Reinsurance Rate Changes" is

            //  of type string then we did not get the change information.

            //  Open the Excel sheet and get the data cell by cell.

            //

            if (dt.Columns[64].DataType == Type.GetType("System.String"))

            {

                try

                {

                    GetReinsuranceRateChange(pathname, filename, dt);

                }

                catch (Exception ex)

                {

                    throw new Exception(ex.Message);

                }

            }

            dt.AcceptChanges();
 

            //This little loop dumps column infomation to the Debug window

            //  Comment out for now

            //foreach (DataColumn c in dt.Columns)

            //    Debug.WriteLine (c.ColumnName + ":  " + c.DataType.ToString());
 

            return dt;

        }
 

        private void GetReinsuranceRateChange(string pathname, string filename, DataTable dt)

        {

            //Declare excel variables

            Excel.Application xlApp = null;

            Excel.Workbook xlBook = null;

            Excel.Sheets xlSheets = null;

            Excel.Worksheet xlWkSheet = null;

            //Excel.Range xlEPIRange = null;

            Excel.Range xlRange = null;

            try

            {

                object oMissing = Missing.Value;

                //Instantiate the Excel and open the file

                xlApp = new Excel.Application();

                xlBook = xlApp.Workbooks.Open(pathname + filename,

                    false, true, oMissing, oMissing, oMissing, oMissing,

                    oMissing, oMissing, oMissing, oMissing, oMissing,

                    oMissing, oMissing, oMissing);

                xlSheets = xlBook.Worksheets;

                xlWkSheet = (Excel.Worksheet)xlSheets.get_Item("Data Capture");
 

                int rowCount = 0;

                foreach (DataRow row in dt.Rows)

                {

                    rowCount++;

                    xlRange = (Excel.Range)xlWkSheet.get_Range("BM" + (rowCount + 1).ToString() + ":BM" + (rowCount + 1).ToString(), Type.Missing);

                    if (IsNumeric(xlRange.Cells.Value2.ToString()))

                        row[64] = xlRange.Cells.Value2.ToString();

                    else

                        row[64] = null;
 

                }
 

                xlBook.Close(false, oMissing, oMissing);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);

                xlBook = null;
 

                xlApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                xlApp = null;

            }
 

            catch (Exception e)

            {

                //Throw exception if there is trouble

                throw new Exception(e.Message);

            }
 

            return;

        }
 

        private static bool IsNumeric(string p)

        {

            try

            {

                Convert.ToDouble(p);

                return true;

            }
 

            catch

            {

               return false;

            }

        }

    }

}

Open in new window

0
Comment
Question by:MarkMahon
  • 2
3 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 24301045
Could you use a 3rd party components that could read both 2003/2007 spreadsheets: http://www.aspose.com/categories/file-format-components/aspose.cells-for-.net-and-java/default.aspx
0
 

Author Comment

by:MarkMahon
ID: 24303569
emoreau -

Thanks for the suggestion.  But I'd like to try to solve this before turning to a 3rd party vendor.

Mark
0
 

Accepted Solution

by:
MarkMahon earned 0 total points
ID: 24420774
I got it to work using the changes indicated in the original question.

The thing that is different between Excel 2003 and Excel 2007 was the interpretation of what a cell in the excel worksheet with the formula  ="" means.  In Excel 2003 ="" is interpreted as a null value, whereas Excel 2007 interprets it as a text value ... which of course stinks.  It forces me to read the cells in the worksheet cell by cell.

Hopes this helps the next person.

Mark
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Replace &lt; with < 14 53
Consolidate xl 2010 worksheets with text 2 19
Run time Error 4 34
Filling Blank Cells 14 8
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

948 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now