public override System.Data.DataTable GetTableContent(bool hasHeader = false) { System.Data.DataTable table = new System.Data.DataTable(); int iRowCount = _comWorksheet.UsedRange.Rows.Count; int iColCount = _comWorksheet.UsedRange.Columns.Count; object[,] a = new string[iRowCount, iColCount]; a = _comWorksheet.Range[_comWorksheet.Cells[1, 1], _comWorksheet.Cells[iRowCount, iColCount]].Value2; if (hasHeader == true) { List <string> headers = new List <string>(); for (int i = 0; i < iColCount; i++) { headers.Add(a[0, i].ToString()); } if (headers.Count > headers.Distinct().Count()) { throw new Exception(string.Format("The sheet: {0} contains duplicate headers", _sheetName)); } foreach (var header in headers) { table.Columns.Add(header); } } else { for (int i = 0; i < iColCount; i++) { string columnName = WorksheetAddress.GetColumnAddress(i + 1); table.Columns.Add(columnName); } DataRow row = table.NewRow(); for (int j = 1; j < iColCount; j++) { row[j] = a[1, j]; } table.Rows.Add(row); } for (int i = 2; i < iRowCount; i++) { DataRow row = table.NewRow(); for (int j = 1; j < iColCount; j++) { if (a[i, j] == null) { row[j] = ""; } else { row[j] = a[i, j].ToString(); } } table.Rows.Add(row); } return(table); }
public override DataTable GetTableContent(bool hasHeader = false) { System.Data.DataTable table = new System.Data.DataTable(); int iRowCount = _epWorksheet.Dimension.End.Row; int iColCount = _epWorksheet.Dimension.End.Column; if (hasHeader == true) { List <string> headers = new List <string>(); for (int i = 1; i <= iColCount; i++) { headers.Add(GetCellValue(1, i)); } if (headers.Count > headers.Distinct().Count()) { throw new Exception(string.Format("The sheet: {0} contains duplicate headers", _sheetName)); } foreach (var header in headers) { table.Columns.Add(header); } } else { for (int i = 0; i < iColCount; i++) { string columnName = WorksheetAddress.GetColumnAddress(i + 1); table.Columns.Add(columnName); } DataRow row = table.NewRow(); for (int j = 1; j <= iColCount; j++) { row[j - 1] = GetCellValue(1, j); } table.Rows.Add(row); } for (int i = 2; i <= iRowCount; i++) { DataRow row = table.NewRow(); for (int j = 1; j <= iColCount; j++) { row[j - 1] = GetCellValue(i, j); } table.Rows.Add(row); } return(table); }