468,768 Members | 1,509 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Reading Data From Excelsheet in DataGridView

This code will read data from excelsheet in gridview......take a submit button and take a datagrid....and use below code...its works fine....


Expand|Select|Wrap|Line Numbers
  1. private void btnsubmit_Click(object sender, EventArgs e)
  2.         {
  3.  
  4.                 Excel.Application appExl;
  5.                 Excel.Workbook workbook;
  6.                 Excel.Worksheet NwSheet;
  7.                 Excel.Range ShtRange;
  8.                 appExl = new Excel.ApplicationClass();
  9.                 workbook = appExl.Workbooks.Open(("C:\\data.xlsx"), Missing.Value, Missing.Value,
  10.                     Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
  11.                     Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
  12.                 NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
  13.  
  14.                 int Cnum = 0;
  15.                 int Rnum = 0;
  16.  
  17.                 ShtRange = NwSheet.UsedRange;
  18.                 DataTable dt = new DataTable();
  19.                 dt.Columns.Add("name");
  20.                 dt.Columns.Add("address");
  21.                // dt.Columns.Add("Status");
  22.                 dt.Columns.Add("Phone");
  23.                 for (Rnum = 1; Rnum <= ShtRange.Rows.Count; Rnum++)
  24.                 {
  25.                     DataRow dr = dt.NewRow();
  26.                     for (Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
  27.                     {
  28.                         dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
  29.                     }
  30.                     dt.Rows.Add(dr);
  31.                     dt.AcceptChanges();
  32.                 }
  33.                 workbook.Close(true, Missing.Value, Missing.Value);
  34.                 appExl.Quit();
  35.  
  36.  
  37.                 //Session["data"] = dt;
  38.                 dataGridView1 .DataSource = dt;
  39.                 //dataGridView1.DataBind();
  40.  
  41.             }
Nov 21 '11 #1
2 7540
hi buddy.. yours code works good.. U r awesome.. But i need to know one more thing.. pls help.. how to display contents of different sheets in excel file.. for example my excel file contains sheet1,sheet2,sheet3.. i need to display contents of any sheet i want by selecting the desired sheet.
May 13 '15 #2
SioSio
252 128KB
This code can select a sheet.
Set two buttons, one Combobox and one DatagridView on the form.
Click Button1 to set the sheet name of the excel file in the Combobox1.
Select a sheet from the combobox1 and click Button2, and the values of the selected sheet will be displayed in the DatagridView.
Expand|Select|Wrap|Line Numbers
  1. public static class Global
  2.     {
  3.         public static string excelName;
  4.     }
  5.     public partial class MainForm : Form
  6.     {
  7.         public MainForm()
  8.         {
  9.             InitializeComponent();
  10.             Global.excelName = System.AppDomain.CurrentDomain.BaseDirectory + "\\sample.xlsx";
  11.             dataGridView1.AutoGenerateColumns = false;
  12.         }
  13.  
  14.         void Button1Click(object sender, EventArgs e)
  15.         {
  16.             Excel.Application mExcel;
  17.             mExcel = new Excel.Application();
  18.             try
  19.             {
  20.                 Excel.Workbook mWorkbook;
  21.                 mExcel.Visible =false;
  22.                 // excel open
  23.                 mWorkbook = (Excel.Workbook)(mExcel.Workbooks.Open(
  24.                     Global.excelName
  25.                 ));
  26.                 try
  27.                 {
  28.                     foreach (Microsoft.Office.Interop.Excel.Worksheet sh in mWorkbook.Sheets)
  29.                     {
  30.                         //ComboBox
  31.                         comboBox1.Items.Add(sh.Name);
  32.                     }
  33.                     comboBox1.SelectedIndex = 0;
  34.                 }
  35.                 finally
  36.                 {
  37.                     // close book
  38.                     mWorkbook.Close(false);
  39.                     System.Runtime.InteropServices.Marshal.ReleaseComObject(mWorkbook);
  40.                 }
  41.             }
  42.             finally
  43.             {
  44.                 if (null != mExcel)
  45.                 {
  46.                     try
  47.                     {
  48.                         mExcel.DisplayAlerts = false;
  49.                         mExcel.Quit();
  50.                     }
  51.                     finally
  52.                     {
  53.                         System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
  54.                     }
  55.                 }
  56.             }
  57.         }
  58.  
  59.         // set to datagridview from excel sheet
  60.         void Button2Click(object sender, EventArgs e)
  61.         {
  62.             // application object
  63.             Excel.Application mExcel;
  64.             // create instance
  65.             mExcel = new Excel.Application();
  66.             try
  67.             {
  68.                 // book object
  69.                 Excel.Workbook mWorkbook;
  70.                 // No display Excel
  71.                 mExcel.Visible = false;
  72.                 // open
  73.                 mWorkbook = (Excel.Workbook)(mExcel.Workbooks.Open(
  74.                     Global.excelName
  75.                 ));
  76.                 try
  77.                 {
  78.                     Excel.Worksheet SheetSample; // Worksheet object
  79.                     SheetSample = (Excel.Worksheet)mWorkbook.Sheets[comboBox1.SelectedIndex+1];
  80.                     try
  81.                     {
  82.                         var rowCount = SheetSample.UsedRange.Rows.Count;
  83.                         var columnCount = SheetSample.UsedRange.Columns.Count;
  84.                         dataGridView1.ColumnCount = columnCount;
  85.                         Microsoft.Office.Interop.Excel.Range xlCells = null;
  86.                         Microsoft.Office.Interop.Excel.Range xlRange = null;
  87.                         xlCells = SheetSample.Cells;
  88.                         try
  89.                         {
  90.                             for(int c = 1; c <= columnCount; c++)
  91.                             {
  92.                                 xlRange = (Microsoft.Office.Interop.Excel.Range)xlCells[1, c];
  93.                                 dataGridView1.Columns[c-1].HeaderText = Convert.ToString(xlRange.Value2);
  94.                             }
  95.                             for(int r = 2; r <= rowCount; r++)
  96.                             {
  97.                                 string[] stringArray = new string[columnCount];
  98.                                 for(int c = 1; c <= columnCount; c++)
  99.                                 {
  100.                                     xlRange = (Microsoft.Office.Interop.Excel.Range)xlCells[r, c];
  101.                                     stringArray[c-1] = Convert.ToString(xlRange.Value2);
  102.                                 }
  103.                                 dataGridView1.Rows.Add(stringArray);
  104.                             }
  105.                         }
  106.                         finally
  107.                         {
  108.                             System.Runtime.InteropServices.Marshal.ReleaseComObject(xlCells);
  109.                             System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
  110.                         }
  111.                     }
  112.                     finally
  113.                     {
  114.                         System.Runtime.InteropServices.Marshal.ReleaseComObject(SheetSample);
  115.                     }
  116.                 }
  117.                 finally
  118.                 {
  119.                     // close the book
  120.                     mWorkbook.Close(false);
  121.                     System.Runtime.InteropServices.Marshal.ReleaseComObject(mWorkbook);
  122.                 }
  123.             }
  124.             finally
  125.             {
  126.                 if (null != mExcel)
  127.                 {
  128.                     try
  129.                     {
  130.                         mExcel.DisplayAlerts = false;
  131.                         mExcel.Quit();
  132.                     }
  133.                     finally
  134.                     {
  135.                         System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
  136.                     }
  137.                 }
  138.             }
  139.         }
  140.     }
Jun 25 '21 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Dariusz | last post: by
reply views Thread by Andy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.