public static DataTable XlToDataTable1(this byte[] fileData)
{
var dt = new DataTable();
using (MemoryStream stream = new MemoryStream())
{
stream.Write(fileData, 0, (int)fileData.Length);
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Worksheet sheet = worksheetPart.Worksheet;
var usedrange = sheet.SheetDimension.Reference.ToString().Split(':'); // "A1:N175"
var i0 = usedrange[0].GetRowIndex();
var i1 = usedrange[1].GetRowIndex();
var j0 = usedrange[0].GetColumnIndex();
var j1 = usedrange[1].GetColumnIndex();
var sheetData = sheet.GetFirstChild <SheetData>();
var rows = sheetData.Descendants <Row>().ToList();
foreach (var row in rows) //this will also include your header row...
{
var tempRow = dt.NewRow();
var colCount = row.Descendants <Cell>().Count();
foreach (var cell in row.Descendants <Cell>())
{
var index = cell.CellReference.ToString().GetColumnIndex();
// Add Columns
for (var i = dt.Columns.Count; i <= index; i++)
{
dt.Columns.Add();
}
tempRow[index] = workbookPart.GetCellValue(cell);
}
dt.Rows.Add(tempRow);
}
var xx = dt.Columns;
}
}
return(dt);
}
public static DataTable XlToDataTable(this byte[] fileData) // , string sheetName=""
{
var dt = new DataTable();
using (MemoryStream stream = new MemoryStream())
{
stream.Write(fileData, 0, (int)fileData.Length);
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Worksheet sheet = worksheetPart.Worksheet;
var sheetData = sheet.GetFirstChild <SheetData>();
var rows = sheetData.Descendants <Row>().ToList();
foreach (var row in rows)
{
var dtRow = dt.NewRow();
var colCount = row.Descendants <Cell>().Count();
foreach (var cell in row.Descendants <Cell>())
{
var index = cell.CellReference.ToString().GetColumnIndex();
// Add Columns
for (var i = dt.Columns.Count; i <= index; i++)
{
dt.Columns.Add();
}
dtRow[index] = workbookPart.GetCellValue(cell);
}
dt.Rows.Add(dtRow);
}
var xx = dt.Columns;
}
}
return(dt);
}
