Inserting and updating data in Excel

Question

Hello,

So far all the Excel stuff revolves around opening a file, writing values to it and saving it.

Is there a way to update the data in the Excel (while it's opened) automatically?

Thanks!

P.S. I am looking for more information with respect to Microsoft.Office.Interop.Excel;

Answer 1

You could use a Timer and have it restart for a periodic update or to do whatever. This links describes the concept in detail. http://www.ehow.com/how_5052033_insert-timer-ms-excel.html
The way it would work in your case is to periodically allow you to get data from an external resource and then you could write some VBA code (Or a Macro) to update your worksheet.
Just like a stock ticker.

Answer 2

Why not access excel via ADO and treat it like a database? http://www.connectionstrings.com/excel

Answer 3

Not sure how you're writing the data, but for reading data from Excel, this is what I do:

select the Range I need (can be from one cell to several rows and columns - it's a single cell here):

Excel.Range cell = m_worksheet.get_Range(_CellName(end, column), _CellName(end, column));

then get the values you need from the selected cell:

string value = cell.Value2.ToString().Substring(...);

you can also manipulate the colors if there are any:

if(Convert.ToInt32(cell.Interior.ColorIndex) == 6) //we have yellow background

be careful with colors if your users have Excel 2007, though. You'll have to use the RGB codes.

Also, while processign the Excel file, it's locked for other applications, I think. You'll have to check that for the 3rd party readign the values.

And for _CellName:

private string _CellName(int row, int col)
 {
      string result = "";
      if (col > 26) // for columns like 'AA' 
      {
           result = ((char)((char)(col / 26) + 'A' - 1)).ToString(); //get the first letter
      }
      result += ((char)((char)(col % 26) + 'A' - 1)).ToString(); //get the second/only letter
      return result + Convert.ToString(row);
 }