Extract Data from Excel to fill DataGridView

**ThatThatGuy** · Jun 11 '10, 06:02 AM

Originally posted by lenniekuah

Hi Good Guys
I need your help. Please help me. I have asked to migrate VBNET2008 to C#Net2008 version of extracting data from Excel to fill DataGridView.

I tried to convert it and here are the not working new C# version:

Code:

 using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Drawing;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Xml;

    private void btnImportExcel_Click(object sender, EventArgs e)
        {  // open up excel and fill datagridview

            Excel.Application objExcel = new Excel.Application();
            Excel.Workbook objBook = new Excel.Workbook(); 
            objBook.Open(ExcelPathName);
            Excel.Worksheet objSheet = new Excel.Worksheet();
            objSheet.Activate;
            objExcel.Visible= false;
          
            //common variable
            Boolean bolFlag = true;
            int excelRow = 2;
            int excelCol = 0;
            int DGVRow = 1;
            int intRecCnt = 0;

            string strCell1 = "";
            string strCell2 = "";
            string strCell3 = "";
            string strCell4 = "";
            string strCell5 = "";
            string strCell6 = "";
            string strCell7 = "";
            string strCell8 = "";
            string strCell9 = "";
            string strCell10= "";

            try
            {
                while (bolFlag == true)
                {
                  
                    strCell1 = (string)objSheet.Cells[excelRow, 1];
                    strCell2 = (string)objSheet.Cells[excelRow, 2];
                    strCell3 = (string)objSheet.Cells[excelRow, 3];
                    strCell4 = (string)objSheet.Cells[excelRow, 4];
                    strCell5 = (string)objSheet.Cells[excelRow, 5];
                    strCell6 = (string)objSheet.Cells[excelRow, 6];
                    strCell7 = (string)objSheet.Cells[excelRow, 7];
                    strCell8 = (string)objSheet.Cells[excelRow, 8];
                    strCell9 = (string)objSheet.Cells[excelRow, 9];
                    strCell10 = (string)objSheet.Cells[excelRow, 10];

                    if ( strCell1 == "")
                    {
                        bolFlag = false;
                        break;
                    }
                    
                   this.dataGridView1.Rows.Add(new string()( strCell1, strCell2, strCell3,strCell4,strCell5,strCell6,strCell7,strCell8,strCell9,strCell10)) ; 
                                      
                    //DataGridView1.Rows.Add()
                    DGVRow += 1;
                    excelRow += 1;
                    
                    //initialise variables
                    strCell1 = "";
                    strCell2 = "";
                    strCell3 = "";
                    strCell4 = "";
                    strCell5 = "";
                    strCell6 = "";
                    strCell7 = "";
                    strCell8 = "";
                    strCell9 = "";
                    strCell10= "";

                    intRecCnt += 1;
                }
                  
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);

            }
            finally
            {
                this.dataGridView1.ClearSelection();
                this.toolStripStatusLabel1.Text = "Total Rows from Excel are " + Convert.ToString(intRecCnt);
                MessageBox.Show("Excel Spreadsheet import Completed", "Import Completed");
                objBook.Close();
                objExcel.Quit();
            }

        }

Thank You.

Have a Good Day.
cheers,
Lennie

What's the error you're facing

**lenniekuah** · Jun 11 '10, 07:15 AM

Hi ThatThatGuy,
Here are the error message caused by the coding.

objBook.Open = ExcelPathName;

Error 1 The event 'Excel.Workbook Events_Event.Op en' can only appear on the left hand side of += or -=

---------------------------------------------------------

this.dataGridVi ew1.Rows.Add(ne w string()(strCel l1, strCell2, strCell3, strCell4, strCell5, strCell6, strCell7, strCell8, strCell9, strCell10));

Error 2 'string' does not contain a constructor that takes '0' arguments

---------------------------------------------------------
objBook.Close() ;
Error 3 No overload for method 'Close' takes '0' arguments

**ThatThatGuy** · Jun 11 '10, 10:45 AM

Originally posted by lenniekuah

Hi ThatThatGuy,
Here are the error message caused by the coding.

objBook.Open = ExcelPathName;

Error 1 The event 'Excel.Workbook Events_Event.Op en' can only appear on the left hand side of += or -=

---------------------------------------------------------

this.dataGridVi ew1.Rows.Add(ne w string()(strCel l1, strCell2, strCell3, strCell4, strCell5, strCell6, strCell7, strCell8, strCell9, strCell10));

Error 2 'string' does not contain a constructor that takes '0' arguments

---------------------------------------------------------
objBook.Close() ;
Error 3 No overload for method 'Close' takes '0' arguments

Error1 Ans:
Since 'Excel.Workbook Events_Event.Op en' being an event... this has to be on the left side of += or -= so that event delegates can be assigned to them....

Error 2 Ans:

this.dataGridVi ew1.Rows.Add(ne w string()(strCel l1, strCell2, strCell3, strCell4, strCell5, strCell6, strCell7, strCell8, strCell9, strCell10));

The Array symbol you specified is valid for VB.Net Code..

use string[] for C#

**lenniekuah** · Jun 12 '10, 02:19 AM

Hi AhNaa ThatThatGuy,
Thank you for your suggestion. I will try it out and get back to you. I like you to be my friend very much.

Hope you don't mind share your personal email with me. I was formerlly from Malaysia and my Indian name is Saravana.

Have a Good Day.

Cheers.
Lennie

Extract Data from Excel to fill DataGridView

Extract Data from Excel to fill DataGridView

Comment

Comment

Comment

Comment