(NET) NET (2018)

Processing Office Open XML (xlsx) files by EPPlus.

There are some way to read Excel file - by Microsoft.Jet.OLEDB.4.0 & Microsoft.ACE.OLEDB - Cайтік з web-сервісом, спеціфічним membership-провайдером та даними Excel. and by native Excel - Шаблон кода для роботи з MS Excel.. But in this page I describe modern and best way - by EPPlus.



Write XLSX

There are two direction to use EPPlus. First direction is to process data in Datatable structure, and eventually save data to XLSX format. In this case you can be read firstly CSV by Processing CSV files by CsvHelper to Datatable. Below you can see this pattern.


   7:  Imports OfficeOpenXml
 ...   
 543:      Sub SaveDtToExcel(DTF As DataTable, ExcelFullName As FileInfo)
 544:          Dim Excel As ExcelPackage = New ExcelPackage()
 545:          Dim WorkSheet As ExcelWorksheet = Excel.Workbook.Worksheets.Add("Sheet 1")
 546:          WorkSheet.Cells("A1").LoadFromDataTable(DTF, True)
 547:          Excel.SaveAs(ExcelFullName)
 548:      End Sub
 ...   
 669:      Private Sub BGW5_DoWork(sender As Object, e As DoWorkEventArgs) Handles BGW5.DoWork
 670:          Dim DT1 As DataTable = ReadCSV(IO.Path.Combine(SelectedTempPath, WestlawSelectedListViewFileName))
 671:          Dim DV2 As DataView = DT1.DefaultView
 672:          DV2.Sort() = e.Argument & " asc"
 673:          Dim DT2 As DataTable = DV2.ToTable
 674:          '
 675:          Dim AttachmentFileNameInfo As New FileInfo(IO.Path.Combine(SelectedTempPath, WestlawSelectedListViewFileName & ".xlsx"))
 676:          SaveDtToExcel(DT2, AttachmentFileNameInfo)
 677:          WestLawAttachmentFileName = AttachmentFileNameInfo.FullName
 678:      End Sub

Read XLSX as untyped values.

In this form I read Excel file, show something fields and processing another fields.



This is template of this form.


   1:  Imports System.IO
   2:  Imports OfficeOpenXml
   3:  Imports Microsoft.VisualBasic
   4:   
   5:  Public Class SearchAddress
   6:      Public Property FileName As String
   7:      Dim FI As FileInfo
   8:      Dim WorkBook As ExcelWorkbook
   9:      Dim WorkSheet As ExcelWorksheet
  10:      Dim ColCount As Integer
  11:      Dim RowCount As Integer
  12:      Dim CurRow As Integer
  13:   
  14:   
  15:      Private Sub SearchAddress_Load(sender As Object, e As EventArgs) Handles Me.Load
 ...   
  22:              FI = New FileInfo(FileName)
  23:              Dim Package = New ExcelPackage(FI)
  24:              WorkBook = Package.Workbook
  25:              WorkSheet = WorkBook.Worksheets.First()
  26:              'For i As Integer = 1 To RowCount
  27:              '    Debug.Print(WorkSheet.Cells(i, 1).Value.ToString())
  28:              'Next
  29:              ColCount = WorkSheet.Dimension.End.Column
  30:              RowCount = WorkSheet.Dimension.End.Row
  31:              CurRow = 2
  32:              ShowRowInfo(2)
 ...   
  38:      End Sub
 ...   
  40:      Sub ShowRowInfo(RowNumber As Integer)
  41:          CurRowsLabel.Text = GetCurRowLabel(CurRow)
 ...   
  48:          AddressTextBox.SelectText(StreetTextBox.Text & "," & CityTextBox.Text & "," & StateComboBox.SelectedValue & "," & ZipTextBox.Text)
  49:      End Sub
 ...   
  51:      Dim RepeatRows As List(Of String)
  52:      Function GetCurRowLabel(RowNumber As Integer) As String
  53:          RepeatRows = New List(Of String)
  54:          Dim Str1 As New Text.StringBuilder
  55:          For i As Integer = 1 To RowCount
  56:              If WorkSheet.Cells(i, 1).Value = WorkSheet.Cells(RowNumber, 1).Value Then
  57:                  RepeatRows.Add(i)
  58:                  Str1.Append(i & ",")
  59:              End If
  60:          Next
  61:          Return Microsoft.VisualBasic.Left(Str1.ToString, Str1.Length - 1) & " / " & RowCount
  62:      End Function

Read XLSX as typed data.

Pay attention how I can access to untyped data - only as object - WorkSheet.Cells(RowNumber, ColNumber).Value, if you have access to typed data, you need sophisticated converter object value to named typed fields.

In this case you need firstly define class ExcelData with definition of each fields of you Excel file. Then create extension below and call it:


  82:  Dim XSLData = WorkSheet.Tables.First().ConvertTableToObjects(of ExcelData)()

   1:  Imports OfficeOpenXml
   2:  Imports OfficeOpenXml.Table
   3:  Imports System.Runtime.CompilerServices
   4:   
   5:  Module ExcelExtensions
   6:   
   7:      <Extension()>
   8:      Public Function ConvertTableToObjects(Of T As New)(ByVal Table As ExcelTable) As IEnumerable(Of T)
   9:          Dim ConvertDateTime = New Func(Of Double, DateTime)(Function(ExcelDate)
  10:                                                                  If ExcelDate < 1 Then Throw New ArgumentException("Excel dates cannot be smaller than 0.")
  11:                                                                  Dim DateOfReference = New DateTime(1900, 1, 1)
  12:                                                                  If ExcelDate > 60.0R Then
  13:                                                                      ExcelDate = ExcelDate - 2
  14:                                                                  Else
  15:                                                                      ExcelDate = ExcelDate - 1
  16:                                                                  End If
  17:                                                                  Return DateOfReference.AddDays(ExcelDate)
  18:                                                              End Function)
  19:          Dim Tprops = (New T()).GetType().GetProperties().ToList()
  20:          Dim Groups = Table.WorkSheet.Cells(Table.Address.Start.Row, Table.Address.Start.Column, Table.Address.End.Row, Table.Address.End.Column).GroupBy(Function(Cell) Cell.Start.Row).ToList()
  21:          Dim Types = Groups.Skip(1).First().Select(Function(Rcell) Rcell.Value.GetType()).ToList()
  22:          Dim Colnames = Groups.First().Select(Function(Hcell, idx) New With {.Name = Hcell.Value.ToString(), .index = idx}).Where(Function(O) Tprops.Select(Function(P) P.Name).Contains(O.Name)).ToList()
  23:          Dim Rowvalues = Groups.Skip(1).Select(Function(Cg) Cg.Select(Function(c) c.Value).ToList())
  24:          Dim Collection = Rowvalues.Select(Function(Row)
  25:                                                Dim Tnew = New T()
  26:                                                Colnames.ForEach(Function(ColName)
  27:                                                                     Dim Val = Row(ColName.index)
  28:                                                                     Dim Type = Types(ColName.index)
  29:                                                                     Dim Prop = Tprops.First(Function(p) p.Name = ColName.Name)
  30:                                                                     If Type = GetType(Double) Then
  31:                                                                         Dim UnboxedVal = CDbl(Val)
  32:                                                                         If Prop.PropertyType = GetType(Int32) Then
  33:                                                                             Prop.SetValue(Tnew, CInt(UnboxedVal))
  34:                                                                         ElseIf Prop.PropertyType = GetType(Double) Then
  35:                                                                             Prop.SetValue(Tnew, UnboxedVal)
  36:                                                                         ElseIf Prop.PropertyType = GetType(DateTime) Then
  37:                                                                             Prop.SetValue(Tnew, ConvertDateTime(UnboxedVal))
  38:                                                                         Else
  39:                                                                             Throw New NotImplementedException(String.Format("Type '{0}' not implemented yet!", Prop.PropertyType.Name))
  40:                                                                         End If
  41:                                                                     Else
  42:                                                                         Prop.SetValue(Tnew, Val)
  43:                                                                     End If
  44:                                                                 End Function)
  45:                                                Return Tnew
  46:                                            End Function)
  47:          Return Collection
  48:      End Function
  49:  End Module

Last function is not a my classic VB-style of programming, I publishing this style of VB.NET only for fun.

Read XLSX as SharedStringTablePart

 100:      Public Async Function SeedCities(Stream As Stream) As Task Implements IDataService.SeedCities
 ...   
 106:          If Stream Is Nothing Then
 107:              Throw New WebFaultException(Of String)("MISSING_PARAMETERS", HttpStatusCode.BadRequest)
 108:          End If
 109:   
 110:          Dim Parser As MultipartFormDataParser = MultipartFormDataParser.Parse(Stream)
 111:   
 112:          Dim ExcelFile As FilePart = Parser.Files.Find(Function(X) X.Name.Equals("City"))
 113:   
 114:          If ExcelFile IsNot Nothing Then
 115:              Dim Root As String = HostingEnvironment.MapPath("~")
 116:              Dim Location As String = Guid.NewGuid().ToString().ToUpperInvariant()
 117:              LocalStorage.WriteFileToTempDataStore(Root, Location, ExcelFile.Data)
 118:   
 119:              Dim FilePath As String = Path.Combine(LocalStorage.GetTemporaryStorageDirectory(Root), Location)
 ...   
 134:              Using Connection As New SqlConnection(Data.Constants.ConnectionString)
 135:                  Connection.Open()
 136:   
 137:                  Using Transaction As SqlTransaction = Connection.BeginTransaction()
 138:                      Try
 139:                          If String.IsNullOrWhiteSpace(FilePath) Then
 140:                              Throw New WebFaultException(Of String)("MISSING_FILE_PATH", HttpStatusCode.BadRequest)
 141:                          End If
 142:   
 143:                          Using Document As SpreadsheetDocument = SpreadsheetDocument.Open(FilePath, False)
 144:                              Dim WorkbookPart As WorkbookPart = Document.WorkbookPart
 145:                              Dim Sheet As Sheet = WorkbookPart.Workbook.Sheets.ElementAt(0)
 146:                              Dim SheetData As SheetData = CType(WorkbookPart.GetPartById(Sheet.Id), WorksheetPart).Worksheet.Elements(Of SheetData)().ElementAt(0)
 147:   
 148:                              Dim Rows As IEnumerable(Of Row) = SheetData.Elements(Of Row)()
 149:                              Dim RowsCount As Integer = Rows.Count()
 150:   
 151:                              Dim StringTable As SharedStringTablePart = WorkbookPart.GetPartsOfType(Of SharedStringTablePart).FirstOrDefault()
 152:   
 153:                              For Counter As Integer = 2 To RowsCount - 1
 154:                                  Dim Cells As IEnumerable(Of Cell) = Rows.ElementAt(Counter).Elements(Of Cell)()
 155:   
 156:                                  Dim City1HCell As Cell = Cells.ElementAt(0)
 157:                                  Dim City1HName As String = GetCellValue(City1HCell, StringTable)
 158:   
 159:                                  Dim City1ECell As Cell = Cells.ElementAt(2)
 160:                                  Dim City1EName As String = GetCellValue(City1ECell, StringTable)
 161:   
 162:                                  Dim CountryIDCell As Cell = Cells.ElementAt(3)
 163:                                  Dim CountryID As String = GetCellValue(CountryIDCell, StringTable)
 164:   
 165:                                  Dim ECity As City = City.GetByName(Connection, Transaction, City1EName)
 166:                                  Dim HCity As City = City.GetByName(Connection, Transaction, City1HName)
 167:                                  If ECity Is Nothing AndAlso HCity Is Nothing Then
 168:                                      Dim City As New City()
 169:                                      City.EName = City1EName
 170:                                      City.HName = City1HName
 171:                                      City.ToCountry = Guid.Parse(CountryID)
 172:                                      City.InsertedOn = DateTime.UtcNow
 173:                                      City.Add(Connection, Transaction)
 174:                                  End If
 175:   
 176:                                  Dim City2HCell As Cell = Cells.ElementAt(1)
 177:                                  Dim City2HName As String = GetCellValue(City2HCell, StringTable)
 178:   
 179:                                  Dim City2ECell As Cell = Cells.ElementAt(3)
 180:                                  Dim City2EName As String = GetCellValue(City2ECell, StringTable)
 181:   
 182:                                  ECity = City.GetByName(Connection, Transaction, City2EName)
 183:                                  HCity = City.GetByName(Connection, Transaction, City2HName)
 184:                                  If ECity Is Nothing AndAlso HCity Is Nothing Then
 185:                                      Dim City As New City()
 186:                                      City.EName = City2EName
 187:                                      City.HName = City2HName
 188:                                      City.InsertedOn = DateTime.UtcNow
 189:                                      City.Add(Connection, Transaction)
 190:                                  End If
 191:                              Next
 192:                          End Using
 193:   
 194:                          Transaction.Commit()
 ...   

Calculate formula

This is simplest idea to store formula in string.

 279:                          Dim Formulas() As String = {
 280:                          "J{0}",
 281:                          "If(Or(TEXT(C{0};""ddd"") = ""sat"";TEXT(C{0};""ddd"")=""sun"");0;If((G{0}>H{0})*(G{0}>=0.75);0;If(G{0}>H{0};(0.75-G{0})*24;If(G{0}<0.25;MIN(8-(0.25-G{0})*24;Q{0});MIN(8;Q{0};(0.75-G{0})*24)))))",
 282:                          "If(Or(TEXT(C{0};""ddd"")=""sat"";TEXT(C{0};""ddd"")=""sun"");If(Or(G{0}>=0.75;H{0}<=0.25);0;If(G{0}>=0.25;MIN(Q{0};(0.75-G{0})*24);MIN(12;(H{0}-0.25)*24)));MIN((Q{0}-V{0});If(V{0}<8;(0.75-G{0})*24-V{0};(0.75-G{0})*24-V{0}-I{0}/60)))",
 283:                          "Q{0}-V{0}-W{0}",
 284:                          "If(Q{0}>=9.5;1;0)",
 285:                          "If((N{0}=""Union"")*(Q{0}>10);1;0)",
 286:                          "If(T{0}<>""";1;0)",
 287:                          "If(K{0}>0;Q{0};0)",
 288:                          "If(L{0}=""";0;Q{0})",
 289:                          "VALUE(If(RIGHT(M{0};2)=""AB"";LEFT(M{0};LEN(M{0})-2);If(RIGHT(M{0};3)=""VMS"";LEFT(M{0};LEN(M{0})-3);0)))"
 290:                          }
 291:   
 292:                          For I As Integer = 2 To RowCount
 293:                              WorkSheet.Cells(I, 17).Formula = String.Format(Formulas(0), I)
 294:                              WorkSheet.Cells(I, 17).Calculate()
 295:                              WorkSheet.Cells(I, 22).Formula = String.Format(Formulas(1), I)
 296:                              WorkSheet.Cells(I, 22).Calculate()
 297:                              WorkSheet.Cells(I, 23).Formula = String.Format(Formulas(2), I)
 298:                              WorkSheet.Cells(I, 23).Calculate()
 299:                              WorkSheet.Cells(I, 24).Formula = String.Format(Formulas(3), I)
 300:                              WorkSheet.Cells(I, 24).Calculate()
 301:                              WorkSheet.Cells(I, 25).Formula = String.Format(Formulas(4), I)
 302:                              WorkSheet.Cells(I, 25).Calculate()
 303:                              WorkSheet.Cells(I, 26).Formula = String.Format(Formulas(5), I)
 304:                              WorkSheet.Cells(I, 26).Calculate()
 305:                              WorkSheet.Cells(I, 27).Formula = String.Format(Formulas(6), I)
 306:                              WorkSheet.Cells(I, 27).Calculate()
 307:                              WorkSheet.Cells(I, 28).Formula = String.Format(Formulas(7), I)
 308:                              WorkSheet.Cells(I, 28).Calculate()
 309:                              WorkSheet.Cells(I, 29).Formula = String.Format(Formulas(8), I)
 310:                              WorkSheet.Cells(I, 29).Calculate()
 311:                              WorkSheet.Cells(I, 30).Formula = String.Format(Formulas(9), I)
 312:                              WorkSheet.Cells(I, 30).Calculate()
 313:                          Next

Use EPplus in Mutlithreading parsers

More details examples to use EPplus (extension function and check if needed column is exist) please see in page Multithreading Parsers with Parallel, CsQuery, Newtonsoft.Json, OfficeOpenXml and IAsyncResult/AsyncCallback..

Universal loader from EPlus

More example to use EPlus Generic VB.NET function for loading ExcelFile to DataBase (EF6 CodeFirst) with Reflection and avoiding Linq-to-Entity Linq.Expressions (use Linq-to-Object IEnumerable)



Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23
Link to this page: //www.vb-net.com/EPPlus-Office-Open-XML/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>