Interoperation & Processing EXCEL Sheet Data

COM Component

COM Framework provides components to developers to interact with Windows OS. .NET Framework supports to import COM Component. By importing COM Components in .net application we are able to work with Windows components like MS Office components.

Mechanism used by .NET Runtime to communicate with COM Components is called RUNTIME CALLABLE WRAPPER [RCW].

RCF handles all works like marshalling data type, handling events b/t .NET & COM.

Here we are taking example of Excel sheet processing in .NET Win Forms Application. Here we are importing data from excel sheet to database.



How to use it & what is the purpose of this utility also provided on HELP button



Excel sheet data format has been shown below.



Data type column name, & no of rows/column specified in above figure will be used in program to fetch value in each cell & push in database corresponding to their column name.

Import COM Component



Using COM Object

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (txtFilePath.Text.Trim() != "")
            {
                if(cmbDatabaseList.SelectedItem.ToString()!= "--Select Database--")
                {
                ImportExcelToSQLServer();
                }
                else
                {
                    MessageBox.Show("Please select Database!!");
                }
            }
            else
            {
                MessageBox.Show("Please enter Excel file path!!");
            }
        }

//HERE IN THIS METHOD, WE PROCESS EXCEL SHEET FOR EACH CELL AND //CHECKING ITS DATATYPE SO ACCORDINGLY CREATED A TABLE HAVING THOSE //DATATYPE FIELDS. THEN DATA ENTERED IN THE TABLE FROM EXCEL SHEET.

//FINALLY IT IS MOVED TO SQL DATABASE.

public void ImportExcelToSQLServer()
        {
            Microsoft.Office.Interop.Excel.Application excel = null;
            Microsoft.Office.Interop.Excel.Workbook wb = null;
            object missing = Type.Missing;
            try
            {
                btnImport.Text = "Data importing in Process";
                btnImport.Enabled = false;
                excel = new Microsoft.Office.Interop.Excel.Application();
                string ExcelFile = txtFilePath.Text.Trim();
                wb = excel.Workbooks.Open(ExcelFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                foreach (Microsoft.Office.Interop.Excel.Worksheet x in wb.Worksheets)
                {
                    object rowIndex = 1;
                    object colIndex1 = 2;
                    //Getting total no of rows available in Excel sheet.
                    int rowCount = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString());
                    rowIndex = 1;
                    colIndex1 = 4;
                    //Getting total no of columns available in Excel sheet.
                    int columnCount = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString());
                    string tblName = x.Name;
                    System.Data.DataTable dtnew = new System.Data.DataTable(tblName);
                    for (int i = 4; i <= rowCount; i++)
                    {
                        DataRow drn = dtnew.NewRow();
                        for (int j = 1; j <= columnCount; j++)
                        {
                            //Creating Table structure with assigning Column name & types
                            if (i == 4)
                            {
                                rowIndex = i - 1;
                                colIndex1 = j;
                                string columnType = ((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString().Trim();
                                rowIndex = i;
                                colIndex1 = j;
                                string columnName = ((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString().Trim();
                                string columnTypeValue = "";
                                switch (columnType)
                                {
                                    case "INT":
                                        columnTypeValue = "System.Int32";
                                        break;
                                    case "Varchar":
                                        columnTypeValue = "System.String";
                                        break;
                                    case "Bool":
                                        columnTypeValue = "System.Boolean";
                                        break;
                                    case "Date-Time":
                                        columnTypeValue = "System.DateTime";
                                        break;
                                    case "Bit":
                                        columnTypeValue = "System.Int32";
                                        break;
                                    case "Decimal":
                                        columnTypeValue = "System.Decimal";
                                        break;
                                    default:
                                        columnTypeValue = "System.String";
                                        break;
                                }
                                dtnew.Columns.Add(columnName, Type.GetType(columnTypeValue));
                            }
                            else
                            {
                                //Pushing data from each column to DataTable. Note: Here 4 as hardcoded appearing used as upto 4 rows sheet description given.
                                if (i > 4)
                                {
                                    rowIndex = i;
                                    colIndex1 = j;
                                    string columnValue = ((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString().Trim();
                                    //Setting default value in case of column having null value.
                                    if (columnValue == "")
                                    {
                                        rowIndex = 2;
                                        columnValue = ((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString().Trim();
                                    }
                                    if (columnValue == "")
                                    {
                                    }
                                    drn[j - 1] = columnValue;
                                }
 
                            }
                        }
                        if (i != 4)
                        {
                            dtnew.Rows.Add(drn);
                        }
                    }
                    //
                    if (dtnew.Rows.Count > 0)
                    {
                        // SQL Server Connection String
                        string dataBaseName = cmbDatabaseList.SelectedItem.ToString();
                        string sqlConnectionString = "";
                        //Getting connection value on behalf of selected database.
                        switch (dataBaseName)
                        {
                            case "A":
                                sqlConnectionString = ConfigurationSettings.AppSettings["A_Database"].ToString();
                                break;
                            case "B":
                                sqlConnectionString = ConfigurationSettings.AppSettings["B"].ToString();
                                break;
                            case "C":
                                sqlConnectionString = ConfigurationSettings.AppSettings["C"].ToString();
                                break;
                            case "D":
                                sqlConnectionString = ConfigurationSettings.AppSettings["D"].ToString();
                                break;
                            case "E":
                                sqlConnectionString = ConfigurationSettings.AppSettings["E"].ToString();
                                break;
                            default:
                                sqlConnectionString = "Not available";
                                break;
                        }
                        if (sqlConnectionString == "Not available")
                        {
                            MessageBox.Show("Proper database connection not available!!\n Please check it.");
                            return;
                        }
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy((sqlConnectionString), SqlBulkCopyOptions.KeepIdentity))
                        {
                            //Truncating the table before entring data from Excel sheets.
                            if (chkbxAppendData.Checked == false)
                            {
                                SqlConnection con = new SqlConnection(sqlConnectionString);
                                SqlCommand CMD = new SqlCommand("truncate table " + dtnew.TableName, con);
                                if (con.State == ConnectionState.Closed)
                                    con.Open();
                                CMD.ExecuteNonQuery();
                                con.Close();
                            }
                            //Inserting data in bulk in SQL Table from excel sheet.
                            bulkCopy.DestinationTableName = dtnew.TableName;
                            bulkCopy.BatchSize = 1000;
                            bulkCopy.WriteToServer(dtnew);
                            MessageBox.Show(dtnew.TableName + " Table" + " successfuly imported from Excel sheet to SQL Server.");
                            if(chkbxAppendData.Checked == true)
                              chkbxAppendData.Checked = false;
                        }
                    }