Importing data from Excel using C#

Question

I am new to the C# platform. I have made the application for importing Excel data in which I have showed the two text fields. The first takes path of Excel file, the second takes sheet name, and when I pressed the load button then it imports the data from Excel.

But there is a problem when I entered the invalid sheet name then application crashed and because of the system.Data.OleDb.OleDbException. The only thing I want to display the message `please enter the correct sheet number' on entering invalid sheet name.

Here is the code:

string PathConn = "Provider =Microsoft.Jet.OLEDB.4.0;Data Source=" + opentextfeild.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";";
OleDbConnection conn = new OleDbConnection(PathConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select email from [" + loadtextfeild.Text + "$] where email like '%@%'     ", conn);
DataTable dt = new DataTable();
myDataAdapter.Fill(dt);
displayviewgrid.DataSource = dt;

When I entered the invalid sheet name then it creates the exception on the line myDataAdapter.Fill(dt);.

Community · Accepted Answer · 2017-05-23 12:06:16Z

1

Add a try/catch block - Example

try
{
using (var myObject = new MyClass())
{
  // something here...

}
catch(Exception ex)
{
   // Handle exception
}

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Dec 5, 2014 at 14:54

Blake

1,0771 gold badge14 silver badges25 bronze badges

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

Comments

ron tornambe · Accepted Answer · 2014-12-05 14:54:08Z

You can use the OleDbSchemaTable method to retrieve the sheet names in an excel file. You can then check to see if the sheet name exists as follows: (I converted this from a VB function that is also included in case the conversion is incorrect)

private static bool IsValidExcelWorksheetName(OleDbConnection m_connexcel, string Filepath, string SheetName)
{
    try {
        DataTable ExcelSheets = m_connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {
            null,
            null,
            null,
            "TABLE"
        });
        foreach (DataRow Row in ExcelSheets.Rows) {
            if (Row.Item("TABLE_NAME") == SheetName)
                return true;
        }
        return false;
    } catch (Exception ex) {
        throw new Exception(ex.Message);
    }
}

VB

Private Shared Function IsValidExcelWorksheetName(ByVal m_connexcel As OleDbConnection, _
                                                  ByVal Filepath As String, _
                                                  ByVal SheetName As String) As Boolean
    Try
        Dim ExcelSheets As DataTable = m_connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
        For Each Row As DataRow In ExcelSheets.Rows
            If Row.Item("TABLE_NAME") = SheetName Then Return True
        Next
        Return False
    Catch ex As Exception
        Throw New Exception(ex.Message)
    End Try
End Function

Collectives™ on Stack Overflow

Importing data from Excel using C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related