c# - exporttoexcel - open xml sdk

De Excel a DataTable en C#con Open XML

Question

Estoy usando Visual Studio 2008 y necesito crear una DataTable desde una hoja de Excel usando Open XML SDK 2.0. Necesito crearlo con las columnas DataTable con la primera fila de la hoja y completarlo con el resto de los valores.

¿Alguien tiene un código de ejemplo o un enlace que me puede ayudar a hacer esto?

Answer 1

Creo que esto debería hacer lo que estás preguntando. La otra función está ahí para tratar si has compartido cadenas, lo cual supongo que harías en los encabezados de tus columnas. No estoy seguro de que esto sea perfecto, pero espero que ayude.

static void Main(string[] args)
{
DataTable dt = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"../../example.xlsx", false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
foreach (Cell cell in rows.ElementAt(0))
{
dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
foreach (Row row in rows) //this will also include your header row...
{
DataRow tempRow = dt.NewRow();
for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));
}
dt.Rows.Add(tempRow);
}
}
dt.Rows.RemoveAt(0); //...so i''m taking it out here.
}
public static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
string value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
else
{
return value;
}
}

Answer 2

Esta solución funciona para hojas de cálculo sin celdas vacías.

Para manejar celdas vacías, deberá reemplazar esta línea:

tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));

con algo como esto:

Cell cell = row.Descendants<Cell>().ElementAt(i);
int index = CellReferenceToIndex(cell);
tempRow[index] = GetCellValue(spreadSheetDocument, cell);

Y agrega este método:

private static int CellReferenceToIndex(Cell cell)
{
int index = 0;
string reference = cell.CellReference.ToString().ToUpper();
foreach (char ch in reference)
{
if (Char.IsLetter(ch))
{
int value = (int)ch - (int)''A'';
index = (index == 0) ? value : ((index + 1) * 26) + value;
}
else
return index;
}
return index;
}

Answer 3

Hola, el código anterior está funcionando bien, excepto un cambio

reemplace la siguiente línea de código

tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));

con

tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));

Si usa (i-1) lanzará una excepción:

specified argument was out of the range of valid values. parameter name index.

Answer 4

si el valor de las filas es nulo o está vacío, los valores funcionan incorrectamente.

todas las columnas están llenas de datos si funciona correctamente. pero tal vez todas las filas no

Answer 5

 Public Shared Function ExcelToDataTable(filename As String) As DataTable
Try
Dim dt As New DataTable()
Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False)
Dim workbookPart As WorkbookPart = doc.WorkbookPart
Dim sheets As IEnumerable(Of Sheet) = doc.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)()
Dim relationshipId As String = sheets.First().Id.Value
Dim worksheetPart As WorksheetPart = DirectCast(doc.WorkbookPart.GetPartById(relationshipId), WorksheetPart)
Dim workSheet As Worksheet = worksheetPart.Worksheet
Dim sheetData As SheetData = workSheet.GetFirstChild(Of SheetData)()
Dim rows As IEnumerable(Of Row) = sheetData.Descendants(Of Row)()
For Each cell As Cell In rows.ElementAt(0)
dt.Columns.Add(GetCellValue(doc, cell))
Next
For Each row As Row In rows
''this will also include your header row...
Dim tempRow As DataRow = dt.NewRow()
For i As Integer = 0 To row.Descendants(Of Cell)().Count() - 1
tempRow(i) = GetCellValue(doc, row.Descendants(Of Cell)().ElementAt(i))
Next
dt.Rows.Add(tempRow)
Next
End Using
dt.Rows.RemoveAt(0)
Return dt
Catch ex As Exception
Throw ex
End Try
End Function
Public Shared Function GetCellValue(document As SpreadsheetDocument, cell As Cell) As String
Try
If IsNothing(cell.CellValue) Then
Return ""
End If
Dim value As String = cell.CellValue.InnerXml
If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
Dim stringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart
Return stringTablePart.SharedStringTable.ChildElements(Int32.Parse(value)).InnerText
Else
Return value
End If
Catch ex As Exception
Return ""
End Try
End Function