Excel to DataTable - restrict Hidden columns from Excel to be moved

Question

User-73514677 posted

Hi.

I am moving my Excel data to DataTable using the oledb command in C# . In the excel, I am not able to see a particular column, but it gets shown in DataTable. I am not sure if the columns are hidden in Excel. I have used the below code to push the data to DataTable. The below code works almost all files.

 if ((str1.ToLower().Contains("xlsx")))
 {
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;"
                              + @"Data Source=" + FilePath + ";"
                              + "Extended Properties='Excel 12.0 XML;IMEX=1;HDR=YES;TypeGuessRows=0;MAXSCANROWS=0;ImportMixedTypes=Text'";
}
 if ((str1.ToLower().Contains("xls")))
 {
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
                              + @"Data Source=" + FilePath + ";"
                              + "Extended Properties='Excel 8.0;IMEX=1;HDR=YES;TypeGuessRows=0;MAXSCANROWS=0;ImportMixedTypes=Text'";
 }  

 OleDbConnection excelConnection;

                excelConnection = new OleDbConnection(excelConnectionString);
                if (excelConnection.State == ConnectionState.Closed)
                {
                    excelConnection.Open();
                }

                DataTable excelDataTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string excelSheetName = string.Empty;
                DataSet excelDataSet = new DataSet();
                excelDataSet.Clear();

                excelSheetName = excelDataTable.Rows[0]["TABLE_NAME"].ToString();

                OleDbDataAdapter excelAdapter = new OleDbDataAdapter();
                OleDbCommand excelCommand = new OleDbCommand();

                excelCommand.Connection = excelConnection;
                excelCommand.CommandText = "Select * from [" + excelSheetName + "]";
                excelAdapter.SelectCommand = excelCommand;

                excelAdapter.Fill(excelDataSet);
                excelConnection.Close();

How to restrict the columns which are hidden/ not getting shown in Excel  to be not moved to DataTable?

Thanks

Answer 1

User-1330468790 posted

Hi venkatzeus,

  

I am afraid that "OLEDB" is not able to detect whether a cell of the excel file is hidden or not.

Instead, you could use EPPlus package (using OfficeOpenXml) to check the visibilty of excel cells and store the value to data table.

Note that "OLEDB" is more efficient in dealing with data when you simply want to move all the data to a data table.

  

More details, you can refer to below code where I use a gridview control to display the table data.

Code behind:

public void BindGridView2()
        {
            string FilePath = Server.MapPath("~/Files/Test.xlsx");
            bool hasHeader = true;

            using(var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = File.OpenRead(FilePath))
                {
                    pck.Load(stream);
                }

                var ws = pck.Workbook.Worksheets.First();
                DataTable tbl = new DataTable();

                // rows and columns in excel is 1-based
                foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
                {
                    // Check whether a header is visible or not
                    if (!ws.Column(firstRowCell.Start.Column).Hidden)
                    {
                        tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                    }
                }

                var startRow = hasHeader ? 2 : 1;
                for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    //Check row's visibility
                    if (!ws.Row(rowNum).Hidden)
                    {
                        var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                        DataRow row = tbl.Rows.Add();

                        //Check columns visibility
                        int columnCount = 0;
                        foreach (var cell in wsRow)
                        {
                            if (!ws.Column(cell.Start.Column).Hidden)
                            {
                                row[columnCount++] = cell.Text;
                            }
                            
                        }
                    }
                    
                }

                GridView2.DataSource = tbl;
                GridView2.DataBind();
               
            }
        }

.aspx Page:

<div>
            <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="true"></asp:GridView>
        </div>

Excel example:

Result:

  

Hope this can help you.

Best regards,

Sean

Answer 2

User426857743 posted

You can search and install spire.xls for .net library via nuget and add the below code to read only visible rows and columns in excel to datatable.

Workbook workbook = new Workbook();
//Load Excel file
workbook.LoadFromFile(@"E:\Sample.xlsx");//Supports both .xls and .xlsx
            
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

//Delete hidden rows 
for (int i = sheet.Rows.Count(); i > 0; i--)
{
    if (!sheet.IsRowVisible(i))
    {
        sheet.DeleteRow(i); //Index parameter in DeleteRow method starts from 1
    }
}

//Delete hidden columns
for (int j = sheet.Columns.Count(); j > 0; j--)
{
    if (!sheet.IsColumnVisible(j))
    {
        sheet.DeleteColumn(j); //Index parameter in DeleteColumn method starts from 1
    }
}

//Export to datatable
DataTable dt = sheet.ExportDataTable();