I'm using VS2010 + Office Interop 2007 to attempt to get a few specific spreadsheet names from an Excel spreadsheet with 5-6 pages. All I am doing from there is saving those few spreadsheets I need in a tab delimited text file for further processing. So for the three spreadsheet names I get, each one will have its own tab delimited text file.
I can save a file as tab delimited just fine through Interop, but that's assuming I know what the given page name is. I have been informed that each page name will not follow a strict naming convention, but I can account for multiple names like "RCP", "rcp", "Recipient", etc when looking for a desired name.
My question is, can I get all spreadsheet page names in some sort of index so I may iterate through them and try to find the three names I need? That would be so much nicer than trying to grab "RCP", "rcp", "Recipient" pages via a bajillion try/catches.
I'm close, because I can get the COUNT of pages in an Excel spreadsheet via the following:
Excel.Application excelApp = new Excel.Application(); // Creates a new Excel Application
excelApp.Visible = true; // Makes Excel visible to the user.
// The following code opens an existing workbook
string workbookPath = path;
Excel.Workbook excelWorkbook = null;
try
{
excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,
false, 0, true, false, false);
}
catch
{
//Create a new workbook if the existing workbook failed to open.
excelWorkbook = excelApp.Workbooks.Add();
}
// The following gets the Worksheets collection
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
Console.WriteLine(excelSheets.Count.ToString()); //dat count
Thank you for your time.
An index column is also added to an Excel worksheet when you load it. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel (Power Query). Select Add Column > Index Column.
foreach ( Worksheet worksheet in excelWorkbook.Worksheets )
{
MessageBox.Show( worksheet.Name );
}
You could use a dictionary:
Dictionary<string, Worksheet> dict = new Dictionary<string, Worksheet>();
foreach ( Worksheet worksheet in excelWorkbook.Worksheets )
{
dict.Add( worksheet.Name, worksheet );
}
// accessing the desired worksheet in the dictionary
MessageBox.Show( dict[ "Sheet1" ].Name );
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With