assetsstill.blogg.se

• Opens with microsoft open xml converter code#

Private static string GetCellValue(SpreadsheetDocument document, Cell cell) Var index = GetIndex(cell.CellReference) įor (var i = dt.Columns.Count i <= index i++) Var colCount = row.Descendants().Count() Var rows = sheetData.Descendants().ToList() įoreach (var row in rows) //this will also include your header row. Var sheetData = workSheet.GetFirstChild() Var worksheetPart = (WorksheetPart) (relationshipId) Var relationshipId = sheets.First().Id.Value Using (var ssDoc = SpreadsheetDocument.Open(path, false)) Public static DataTable Read(string path) Return (Int32.Parse(value)).InnerTextįirst Add ExcelUtility.cs to your project : If cell.DataType IsNot Nothing AndAlso = CellValues.SharedString Thenĭim stringTablePart As SharedStringTablePart = Public Shared Function GetCellValue(document As SpreadsheetDocument, cell As Cell) As Stringĭim value As String = TempRow(i) = GetCellValue(doc, row.Descendants(Of Cell)().ElementAt(i)) 'this will also include your header row.įor i As Integer = 0 To row.Descendants(Of Cell)().Count() - 1 Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False)ĭim workbookPart As WorkbookPart = doc.WorkbookPartĭim sheets As IEnumerable(Of Sheet) = (Of Sheets)().Elements(Of Sheet)()ĭim relationshipId As String = sheets.First().Id.Valueĭim worksheetPart As WorksheetPart = DirectCast((relationshipId), WorksheetPart)ĭim workSheet As Worksheet = worksheetPart.Worksheetĭim sheetData As SheetData = workSheet.GetFirstChild(Of SheetData)()ĭim rows As IEnumerable(Of Row) = sheetData.Descendants(Of Row)()įor Each cell As Cell In rows.ElementAt(0) Public Shared Function ExcelToDataTable(filename As String) As DataTable Always get from the header count, because the index of the row changes where empty cell is not counted

Int totalHeaderCount = sheetdata.Descendants().ElementAt(0).Descendants().Count() įor (int i = 1 i ().ElementAt(0).Elements().ToList(), i)) įoreach (Row r in sheetdata.Descendants()) SheetData sheetdata = ().FirstOrDefault() WorksheetPart wsPart = (WorksheetPart)wbPart.GetPartById(sheetId)

String sheetId = sheetName != "" ? sheets.Where(q => q.Name = sheetName).First().Id.Value : sheets.First().Id.Value WorkbookPart wbPart = document.WorkbookPart Using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false)) Public static DataTable GetDataTableFromExcelFile(string filePath, string sheetName = "") Private static string GetExcelColumnName(int columnNumber)ĬolumnName = Convert.ToChar(65 + modulo).ToString() + columnName ĭividend = (int)((dividend - modulo) / 26) Return GetCellValue(wbPart, theCells, GetExcelColumnName(index)) Private static string GetCellValue(WorkbookPart wbPart, List theCells, int index) Otherwise, look up the correct text in the table. If the shared string table is missing, something is wrong. Var stringTable = wbPart.GetPartsOfType().FirstOrDefault() For shared strings, look up the value in the shared strings table.

Opens with microsoft open xml converter code#

For Booleans, the code converts the value into the words TRUE or FALSE. For shared strings, the code looks up the corresponding value in the shared string table. For dates, this code returns the serialized value that represents the date. If the cell represents an integer number, you are done. Private static string GetCellValue(WorkbookPart wbPart, List theCells, string cellColumnReference)

To get the value of the cell, even it's empty. This is my complete solution where empty cell is also taken into consideration. If (cell.DataType != null & = CellValues.SharedString) SharedStringTablePart stringTablePart = Public static string GetCellValue(SpreadsheetDocument document, Cell cell) TempRow = GetCellValue(spreadSheetDocument, row.Descendants().ElementAt(i-1)) ĭt.Rows.RemoveAt(0) //.so i'm taking it out here. IEnumerable rows = sheetData.Descendants() ĭt.Columns.Add(GetCellValue(spreadSheetDocument, cell)) įoreach (Row row in rows) //this will also include your header row. SheetData sheetData = workSheet.GetFirstChild() Worksheet workSheet = worksheetPart.Worksheet WorksheetPart worksheetPart = (WorksheetPart)(relationshipId) String relationshipId = sheets.First().Id.Value WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart Using (SpreadsheetDocument spreadSheetDocument = false))

Not sure this is perfect, but I hope it helps. The other function is there just to deal with if you have shared strings, which I assume you do in your column headers. I think this should do what you're asking.