使用Open XML从C#中的Excel到DataTable,可以通过以下步骤完成:
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
using System.Linq;
public static DataTable ReadExcelToDataTable(string filePath, string sheetName)
{
DataTable dataTable = new DataTable();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
if (sheet == null)
{
return null;
}
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
Row row = sheetData.Descendants<Row>().FirstOrDefault();
if (row == null)
{
return null;
}
foreach (Cell cell in row.Descendants<Cell>())
{
dataTable.Columns.Add(GetCellValue(spreadsheetDocument, cell));
}
foreach (Row dataRow in sheetData.Descendants<Row>().Skip(1))
{
DataRow newRow = dataTable.NewRow();
int i = 0;
foreach (Cell cell in dataRow.Descendants<Cell>())
{
newRow[i] = GetCellValue(spreadsheetDocument, cell);
i++;
}
dataTable.Rows.Add(newRow);
}
}
return dataTable;
}
private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
int index = int.Parse(cell.CellValue.Text);
return stringTablePart.SharedStringTable.ChildElements[index].InnerText;
}
return cell.CellValue.Text;
}
string filePath = "path/to/excel/file.xlsx";
string sheetName = "Sheet1";
DataTable dataTable = ReadExcelToDataTable(filePath, sheetName);
通过以上步骤,可以使用Open XML从C#中的Excel到DataTable。
领取专属 10元无门槛券
手把手带您无忧上云