Solved

Connect to an open Excel spreadsheet and get data from it C#

Posted on 2008-10-15
5
1,225 Views
Last Modified: 2012-05-05
I have a spreadsheet that connects to another API to update fields on the sheet with live values.  I want to be able to have the spreadsheet open and pull the current values without having to wait for the spreadsheet to open and update.

The attached code snippet allows me to open a fresh copy and then validates the data in the spreadsheet and waits until all the values have been updated.  But I would like to be able to use an already open copy of the file so that I don't have to go through the process of waiting as my customers want to need the latest numbers to calculate their outputs and don't want to wait 10-20 seconds for that to occur.
ApplicationClass app = new ApplicationClass(); // the Excel application.
Workbook book = null;
 
// Get Empty Object array Structure
object[][,] sheets = new object[4][,];
 
// Ensures faster processing
app.Visible = true;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
 
 
try
{
    // Open the book
    string path = Path.Combine(ConfigurationSettings.AppSettings["QuotePath"], "PMQuote.xls");
    book = app.Workbooks.Open(path,
           3, Missing.Value, Missing.Value,
           Missing.Value, Missing.Value, true, Missing.Value,
           Missing.Value, Missing.Value, Missing.Value, Missing.Value,
           Missing.Value, Missing.Value, Missing.Value);
 
    // Sleep 5 seconds
    System.Threading.Thread.Sleep(5000);
 
    // Wait for the sheet to update all entries
    bool goodPull = false;
    int count = 0;
    while (!goodPull && count++ < 10)
    {
        // Sleep a second
        System.Threading.Thread.Sleep(1000);
 
        // Get Sheets
        sheets = GetQuoteSheets(sheets, book);
 
        // Validate sheets
        goodPull = ValidateQuoteSheets(sheets);
    }
 
    // Check if was good pull
    if (!goodPull)
    {
        throw new Exception("Failed to retieve good quotes from spreadsheet.");
    }

Open in new window

0
Comment
Question by:johnnie240
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22730674
i just know one thing that excel does not allow you to open 2 copies of the same file simultaneously

i dont know if open office provides you with functionality
might like to investiage in that direction
0
 

Author Comment

by:johnnie240
ID: 22732397
Maybe the API allows that to happen, but I have tested having it open and it will still open another window.  One option I am going to look at is that it might be opening a read only copy, which would be allowed.  So if I open a read/write copy it might work.

I tried that but it looks like that causes something to lock up.  Probably need to allow notifications.
0
 

Author Comment

by:johnnie240
ID: 22732860
So it looks like when you construct the ApplicationClass it automatically opens a new instance of Excel.  Then when you do the app.Workbooks.Open it opens a Read-Only copy even if you set the Read-Only value to false:

book = app.Workbooks.Open(path,
                       3,false, Missing.Value,
                       Missing.Value, Missing.Value, true, Missing.Value,
                       Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                       Missing.Value, Missing.Value, Missing.Value);

Does anyone know how to connect to an existing open instance of Excel?  I

 think if I was able to do that I could then access the workbooks available.
0
 

Author Comment

by:johnnie240
ID: 22735561
Well I figured out how to connect to an open Excel spreadsheet using a OLEDB connection.  The problem I have now is that you are limited to 255 entries that you can pull from a sheet and I have some that have thousands of values.  Has anyone used this before and figured out a way around it.  Here is the code I wrote:

DataSet returnSet = new DataSet("Quotes");
               
//Name of Workbook
string path = Path.Combine(ConfigurationSettings.AppSettings["QuotePath"], "PMQuote.xls");

//Create Connection String
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=2;IMEX=1\";";

//Connect to Workbook
OleDbConnection oleConn = new OleDbConnection();
oleConn.ConnectionString = strConn;
oleConn.Open();

    // Get names of sheets to retieve
    string[] sheetnames = ConfigurationSettings.AppSettings["QuoteSheets"].Split(new char[] { ',' });

    // Get all the sheets
    foreach (string sheetName in sheetnames)
    {
        // Query to get the sheet we are interested in
        string query = "SELECT * FROM [" + sheetName + "$]";

        //Fill array with queried information
        OleDbDataAdapter oleCmd = new OleDbDataAdapter(query, oleConn);
        DataSet oDS = new DataSet();
        oleCmd.Fill(oDS);

        // Get the table and name it
        System.Data.DataTable table = oDS.Tables[0].Copy();
        table.TableName = sheetName;

        // Add to the return set
        returnSet.Tables.Add(table);
    }

if (oleConn.State == ConnectionState.Open)
    {
        oleConn.Close();
    }
}

return returnSet;
0
 

Accepted Solution

by:
johnnie240 earned 0 total points
ID: 22799992
So I modified the code to use Microsoft.Office.Interop.Excel.Application instead of the ApplicationClass. And this works great.
                try
                {
                        app = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
                }
                catch
                {
                    app = new Microsoft.Office.Interop.Excel.Application();
                }
 
                try
                {
                        book = app.Workbooks.get_Item("PMQUOTE.xls");
                }
                catch
                {
                    book = null;
                }
 
                // Make it viewable to user and allow alerts and updates
                app.Visible = true;
                app.ScreenUpdating = true;
                app.DisplayAlerts = true;
 
                // Open the book if not found
                if (book == null || book.Name!="PMQUOTE.xls")
                {
                    string path = Path.Combine(ConfigurationSettings.AppSettings["QuotePath"], "PMQUOTE.xls");
                    book = app.Workbooks.Open(path,
                           3, false, Missing.Value,
                           Missing.Value, Missing.Value, true, Missing.Value,
                           Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                           Missing.Value, Missing.Value, Missing.Value);
 
                    // Sleep 5 seconds to allow for updating
                           //System.Threading.Thread.Sleep(5000);
                }

Open in new window

0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

738 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