Web (ASP.NET), C# รบกวนสอบถามเรื่อง การอ่านชื่อ sheet ของ ไฟล์ Excel

OleDbConnection objConn = new OleDbConnection();
OleDbDataAdapter dtAdapter = default(OleDbDataAdapter);
DataTable dt = new DataTable();
string strConnString = null;

strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=" + Server.MapPath("Upload/" + FileUpload1.FileName) + ";Extended Properties=Excel 8.0;";


objConn = new OleDbConnection(strConnString);
objConn.Open();

string strSQL = null;
SQL = "SELECT * FROM [Sheet1$]";
dtAdapter = new OleDbDataAdapter(strSQL, objConn);
dtAdapter.Fill(dt);

Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
 
Public Class Form1
 
    Dim SheetList As New ArrayList
   
    ' Part 1: Retrieve all WorkSheet names and store them into an ArrayList object
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim objExcel As Excel.Application
        Dim objWorkBook As Excel.Workbook
        Dim objWorkSheets As Excel.Worksheet
        Dim ExcelSheetName As String = ""
 
        objExcel = CreateObject("Excel.Application")
        objWorkBook = objExcel.Workbooks.Open("C:\myData.XLS")
 
        For Each objWorkSheets In objWorkBook.Worksheets
            SheetList.Add(objWorkSheets.Name)
            ' ListBox1.Items.Add(objWorkSheets.Name)
        Next
    End Sub
 
    ' Part 2: Retrieve specific WorkSheet content and display on DataGridView
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim DS As DataSet
        Dim MyCommand As OleDb.OleDbDataAdapter
        Dim MyConnection As OleDb.OleDbConnection
 
        MyConnection = New OleDb.OleDbConnection( _
             "provider=Microsoft.Jet.OLEDB.4.0; " & _
             "data source=C:\myData.XLS; " & _
             "Extended Properties=Excel 8.0;")
        ' Select the data from Sheet1 of the workbook.
        MyCommand = New OleDb.OleDbDataAdapter( _
             "select * from [" & SheetList(0) & "$]", MyConnection)
        DS = New System.Data.DataSet()
        MyCommand.Fill(DS)
        DataGridView1.DataSource = DS.Tables(0).DefaultView
        MyConnection.Close()
    End Sub
End Class

Public Function GetExcelSheetNames(excelFileName As String) As String()
	Dim con As OleDbConnection = Nothing
	Dim dt As DataTable = Nothing
	Dim conStr As [String] = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & excelFileName & ";Extended Properties=Excel 8.0;"
	con = New OleDbConnection(conStr)
	con.Open()
	dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

	If dt Is Nothing Then
		Return Nothing
	End If

	Dim excelSheetNames As [String]() = New [String](dt.Rows.Count - 1) 
	Dim i As Integer = 0

	For Each row As DataRow In dt.Rows
		excelSheetNames(i) = row("TABLE_NAME").ToString()
		i += 1
	Next

	Return excelSheetNames
End Function

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;

public class Form1
{

	ArrayList SheetList = new ArrayList();

	// Part 1: Retrieve all WorkSheet names and store them into an ArrayList object
	private void Form1_Load(System.Object sender, System.EventArgs e)
	{
		Excel.Application objExcel = default(Excel.Application);
		Excel.Workbook objWorkBook = default(Excel.Workbook);
		Excel.Worksheet objWorkSheets = default(Excel.Worksheet);
		string ExcelSheetName = "";

		objExcel = Interaction.CreateObject("Excel.Application");
		objWorkBook = objExcel.Workbooks.Open("C:\\myData.XLS");

		foreach ( objWorkSheets in objWorkBook.Worksheets) {
			SheetList.Add(objWorkSheets.Name);
			// ListBox1.Items.Add(objWorkSheets.Name)
		}
	}

	// Part 2: Retrieve specific WorkSheet content and display on DataGridView
	private void Button1_Click(System.Object sender, System.EventArgs e)
	{
		DataSet DS = null;
		System.Data.OleDb.OleDbDataAdapter MyCommand = null;
		System.Data.OleDb.OleDbConnection MyConnection = null;

		MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " + "data source=C:\\myData.XLS; " + "Extended Properties=Excel 8.0;");
		// Select the data from Sheet1 of the workbook.
		MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + SheetList[0] + "$]", MyConnection);
		DS = new System.Data.DataSet();
		MyCommand.Fill(DS);
		DataGridView1.DataSource = DS.Tables[0].DefaultView;
		MyConnection.Close();
	}
	public Form1()
	{
		Load += Form1_Load;
	}
}

public string[] GetExcelSheetNames(string excelFileName)
{
	OleDbConnection con = null;
	DataTable dt = null;
	String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFileName + ";Extended Properties=Excel 8.0;";
	con = new OleDbConnection(conStr);
	con.Open();
	dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

	if (dt == null) {
		return null;
	}

	String[] excelSheetNames = new String(dt.Rows.Count - 1);
	int i = 0;

	foreach (DataRow row in dt.Rows) {
		excelSheetNames[i] = row["TABLE_NAME"].ToString();
		i += 1;
	}

	return excelSheetNames;
}

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

if (dt.Rows.Count > 0)
{
Dropdownlist1.DataSource = dt;
Dropdownlist1.DataTextField = "TABLE_NAME";
Dropdownlist1.DataValueField = "TABLE_NAME";
Dropdownlist1.DataBind();
}