locked
Excel to DataTable - restrict Hidden columns from Excel to be moved RRS feed

  • 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
    Monday, April 6, 2020 3:58 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 7, 2020 1:19 PM
  • 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();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 15, 2020 3:03 AM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 7, 2020 1:19 PM
  • 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();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 15, 2020 3:03 AM