johnnie240
asked on
Connect to an open Excel spreadsheet and get data from it C#
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.
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.");
}
ASKER
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.
I tried that but it looks like that causes something to lock up. Probably need to allow notifications.
ASKER
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.
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.
ASKER
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(Configuration Settings.A ppSettings ["QuotePat h"], "PMQuote.xls");
//Create Connection String
string strConn = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta 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.AppS ettings["Q uoteSheets "].Split(n ew 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;
DataSet returnSet = new DataSet("Quotes");
//Name of Workbook
string path = Path.Combine(Configuration
//Create Connection String
string strConn = "Provider=Microsoft.Jet.OL
//Connect to Workbook
OleDbConnection oleConn = new OleDbConnection();
oleConn.ConnectionString = strConn;
oleConn.Open();
// Get names of sheets to retieve
string[] sheetnames = ConfigurationSettings.AppS
// 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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i dont know if open office provides you with functionality
might like to investiage in that direction