c# read specific columns from excel file

Question

i have a tables like this

and i added checkboxs elements to form like this

i want to add the checkbox element text to datagridview then read the checked columns from excel file

if Date, Time, Price are checked datagridview will be like this

then get full Date column from excel file and add it to Date column in datagrid

my code to add checked boxes text as a columns in datagridview

DataTable dt = new DataTable();
    foreach (Control checkbox in pnl.Controls)
        if (checkbox.GetType() == typeof(CheckBox) && ((CheckBox) checkbox).Checked)
        {
            string txt = ((CheckBox)checkbox).Text;
            dt.Columns.Add(new DataColumn(txt, typeof(object)));
        }
datagrid.DataSource = dt;

its not clear...are you working on excel file ? so if not i suggest you to put off the tag excel..so another suggestion is to show a minimal example what you have done..helpers could use your program and add the solution code — Frenchy, Sep 10 '22 at 07:19
Make columns in DGV visible = false : https://learn.microsoft.com/en-us/dotnet/desktop/winforms/controls/how-to-hide-columns-in-the-windows-forms-datagridview-control?view=netframeworkdesktop-4.8 — jdweng, Sep 10 '22 at 09:18
@Frenchy yes, i`m working on excel file i wanna create a c# program to read all file columns and rows then add what i need in datagridview — Done Nothing, Sep 10 '22 at 09:45
@jdweng i will try but if u can send to me good code for import excel to datagrib i`m pleasure for this ♥ — Done Nothing, Sep 10 '22 at 09:46
All you need to do to display a DataTable to a DataGridview is following : datagridview1.DataSource = dt. — jdweng, Sep 10 '22 at 09:48
@DoneNothing, ok so need to read your code because there lot of ways to read excel file — Frenchy, Sep 10 '22 at 10:47

mattjr747 · Accepted Answer · 2022-10-06T19:57:36.707

There are a few steps which are needed before being able to grab data from an Excel file. So without your code, I don't know how much of this you have done. But here is the full explanation.

First
You have to add a reference to the Microsoft.Office.Interop.Excel dll (this assumes you aren't using epplus or another Nuget package). This link describes how to do this: How to reference Microsoft.Office.Interop.Excel dll?

Second
Include this library in whichever source file it is needed, and initialize an excel application (you'll also want InteropServices included):

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

// Global excel app object to be used anywhere
public Application ExcelApp;

// Intitializes an excel application by looking for an active one,
// and creating a new one if none are active
public void InitExcelApp()
{
   try
   {
      ExcelApp = (Application)Marshal.GetActiveObject("Excel.Application")
   }
   catch(COMException ex)
   {
      ExcelApp = new Application
      {
         Visible = true
      };
   }
}

Third You must initialize a workbook object. Here is how I do it, but my solution assumes you know the path to the desired Excel workbook:

Workbook myWorkbook = null;

// Checks open workbooks first
// Note that the path must be windows style. Ex: "C:\\Desktop\\myWorkbook.xlsx"
foreach (Workbook openWorkbook in ExcelApp.Workbooks)
{
   if (openWorkbook.FullName == "<path to workbook>")
   {
      myWorkbook = openWorkbook;
   }
}

// If no open workbooks were found at the known path, try opening one 
if(myWorkbook is null)
{
   myWorkbook = Excelapp.Workbooks.Open("<path to workbook>", Editable: true);
}

Fourth Get the data you want. There are several ways to do this, and mine might not be the most efficient, but it works. In the code below I have included parameter names to hopefully make it more understandable.

// Gets the names of the checked items from the DataTable with columns you already added
// You could get these names from your checkboxes' names if you preferred
List<string> checkItems = new List<string>();
foreach (System.Data.DataColumn column in dt.Columns)
{
   checkItems.Add(column.ColumnName);
}

// This dictionary holds info helpful for getting the correct data from excel:
// Key: a string containing the name of the column header, i.e. Date, Time, Price, etc.
// Value: an integer containing the number of that column in excel
Dictionary<string, int> excelColumnsInfo = new Dictionary<string, int>();
for (int columnNum = 1; columnNum <= myWorkbook.UsedRange.Columns.Count; columnNum++)
{
   string columnHeader = myWorkbook.Cells[RowIndex: 1, ColumnIndex: columnNum].Value2.ToString();

   if (checkedItems.Contains(columnHeader))
   {
      excelColumnsInfo.add(columnHeader, columnNum);
   }
}

// Populates the data table with the data you need
// Start at row 2 to ignore the excel sheet's column headers
for (int rowNum = 2; rowNum <= myWorkbook.UsedRange.Rows.Count; rowNum++)
{
   System.data.dataRow newRow = dt.NewRow();

   foreach (KeyValuePair<string, int> columnInfo in excelColumnsInfo)
   {
      newRow[columnName: columnInfo.Key] = myWorkbook.Cells[RowIndex: rowNum, ColumnIndex: columnInfo.Value].Value2.ToString();
   }

   dt.Rows.Add(newRow);
}

c# read specific columns from excel file

1 Answers1