Get list of Excel sheet worksheet name using C#

Question

I am trying to show excel sheet name in messagebox but it seems like its not working , can somebody please help me.

I tired adding following , but it does not show anything.

string test = excelsheet.tostring(); 
messagebox.show(test);

I will really appreciate your response.

Thanks in advance

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.Odbc;
using System.Data.OleDb;
using System.IO;

namespace ST_2c44bffc2aef4e1295daeafee32354d2.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
        The execution engine calls this method when the task executes.
        To access the object model, use the Dts property. Connections, variables, events,
        and logging features are available as members of the Dts property as shown in the following examples.

        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
        To post a log entry, call Dts.Log("This is my log text", 999, null);
        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

        To use the connections collection use something like the following:
        ConnectionManager cm = Dts.Connections.Add("OLEDB");
        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

        To open Help, press F1.
    */

        public void Main()
        {
            GetExcelSheetNames_2007(@"\\AUSSMBSSOBI\Files\Public\Reports\APOS_Dashboard.xlsm");
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        public string[] GetExcelSheetNames_2007(string excelFile)
        {
            {

                OleDbConnection objConn = null;

                System.Data.DataTable dt = null;



                try
                {

                    FileInfo fileInfo = new FileInfo(excelFile);

                    String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + "D:\\ETL\\Sample.xlsx" + ";Extended Properties=\"Excel 12.0 XML;HDR=YES\";";

                    objConn = new OleDbConnection(connString);

                    objConn.Open();

                    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    if (dt == null)
                    {

                        return null;

                    }

                    String[] excelSheets = new String[dt.Rows.Count];

                    int i = 0;



                    foreach (DataRow row in dt.Rows)
                    {

                        excelSheets[i] = row["TABLE_NAME"].ToString();

                        i++;

                    }



                    for (int j = 0; j < excelSheets.Length; j++)
                    {

                    }

                    return excelSheets;



                }

                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);

                    return null;

                }

                finally
                {

                    if (objConn != null)
                    {

                        objConn.Close();

                        objConn.Dispose();

                    }

                    if (dt != null)
                    {

                        dt.Dispose();

                    }
                    Dts.TaskResult = (int)ScriptResults.Success;

                }

            }

        }
    }
}

there no errors , but I know on sample.xlsx there are sheet names but I don't see message box being populated with Sheet name even though I add string test = excelsheet.tostring(); messagebox.show(test); — user1570210
– user1570210, Commented Aug 3, 2013 at 4:51

Adel Khayata · Accepted Answer · 2013-08-03 05:00:13Z

1

Your excelSheets varaible is an array of Strings, try the following:

foreach(String str in excelSheets)
{
    messagebox.show(str);
}

answered Aug 3, 2013 at 5:00

Adel Khayata

2,83410 gold badges31 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1570210 Over a year ago

Hi Adel, I tried Adding this to my code but it shows each sheet names 3 times in iteration . Any help on this for (int j = 0; j < excelSheets.Length; j++) { foreach (String str in excelSheets) { MessageBox.Show(str); } } return excelSheets;

Adel Khayata Over a year ago

Just add the code I included, in your code remove the for loop, because foreach is already looping through the sheets.

Community · Accepted Answer · 2014-04-13 18:18:16Z

0

Why you use OLE? You can use VSTO and name your excelsheet like in this example:

var excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = true;
var excelWorkbook = excelApp.Workbooks.Add();
Worksheet excelSheet = excelWorkbook.Sheets.Add();
excelSheet.Name = "My sheetname";

edited Apr 13, 2014 at 18:18

CommunityBot

11 silver badge

answered Aug 3, 2013 at 5:04

Marcus Haßmann

1184 bronze badges

2 Comments

user1570210 Over a year ago

On our Prod server we don't have Excel installed.

Marcus Haßmann Over a year ago

You don't need an Excel installation. Only the VSTO (Visual Studio Tools for Office) is necessary: [link]msdn.microsoft.com/en-us/office/hh133430.aspx[/link]

Collectives™ on Stack Overflow

Get list of Excel sheet worksheet name using C#

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related