Answered by:
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?
ThanksMonday, 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