SQL MDF Database every cell displays: "System.__ComObject"

Question

When I execute this program the Excel part generates and string array: cellValue just fine.

When it inserts into the SQL MDF Database: MDFExcel every cell displays: "System.__ComObject".

How do you display the string value instead of the "System.__ComObject"?

protected void Button1_Click(Object sender, EventArgs e)
    {
        DataSet ds = new DataSet();
        //From Excel
        Microsoft.Office.Interop.Excel.Application exlApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook exlWb = exlApp.Workbooks.Open(@"C:\Users\Optiplex760\Documents\a  Excel\ExcelToMDF.xls");
        Microsoft.Office.Interop.Excel.Worksheet exlWs= exlWb.Sheets["Sheet1"];
        Microsoft.Office.Interop.Excel.Range usedRange = exlWs.UsedRange;
        int col = Convert.ToInt32(usedRange.Columns.Count);
        int row = Convert.ToInt32(usedRange.Rows.Count);
        exlApp.Visible = true;
        string[,] cellValue = new string[row + 1, col + 1];
        for (int j = 1; j <= row-1; j++)
        {
            for (int k = 1; k <= col-1; k++)
            {
                cellValue[j, k] = exlWs.Cells[j+1,k+1].ToString();
            }
        }
        exlWb.Close();
        exlWs = null;
        exlWb = null;
        exlApp.Quit();
        exlApp = null;
        //To MSSQL
        String connStr, cmdStr;
        connStr = ConfigurationManager.ConnectionStrings["MDFExceldb"].ConnectionString; 
        for (int h = 1; h<row; h++)
        {
            cmdStr = "INSERT INTO [Table1] (col1,col2,col3) VALUES (@col1,@col2,@col3);";
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@col1", cellValue[1, h]);
                        cmd.Parameters.AddWithValue("@col2", cellValue[2, h]);
                        cmd.Parameters.AddWithValue("@col3", cellValue[3, h]);
                        cmd.ExecuteNonQuery();
                        conn.Close();
                        cmd.Dispose();
                        conn.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                Label2.Text = ex.ToString();
            }
        }
    }

score 0 · Accepted Answer · edited May 23 '17 at 10:26

Worksheet.Cells returns a Range and not a single value - and a Range (an RCW object) does not implemented a sensible ToString; thus it defaults to the "System.__ComObject" value shown.

Use the Text property of the Range, eg.

cellValue[j, k] = Convert.ToString(exlWs.Cells[j+1,k+1].Text);

While this should fix the immediate problem, it is also an inefficient process. See this response (using the Value/Value2 property) for how to access the Range values as a 2-dimensional array and reduce excessive range-slicing.

SQL MDF Database every cell displays: "System.__ComObject"

1 Answers1