Introduction
In my previous blog, i shared details on how to generate the spreadsheet dynamically without using Microsoft interop excel dll and now in the below blog, I would like to explain how we can read the various spreadsheets like xlsx, xls and csv and store the data in the dataset using free third party tools in dotnet.
Below are various free third-party references used to read the different files in the below code:
EPPlus: EPPlus is a .NET library which reads and writes Excel 2007/2010 or higher files, using Open Office XML format. It supports .XLSX, .XLSM Excel file format.
NPOI : NPOI is open-source third-party library which can be used for xls, xlsx, docx files reading and writing.
FileIO: The file class, from the Syste.IO namespace, makes it very easy to do simple reading and writing of a csv file.
Now add the below references in project solution and import the namespace as shown below:
Imports OfficeOpenXml;
Imports NPOI.HSSF.UserModel
Imports NPOI.SS.UserModel
Imports Microsoft.VisualBasic.FileIO
Private Sub BtnFileUpload_Click(sender As System.Object, e As System.EventArgs) Handles BtnFileUpload.Click
Try
Dim FileDialog As New OpenFileDialog()
FileDialog.Filter = "Excel files|*.csv;*.xls;*.xlsx"
FileDialog.Title = "Select File"
Dim filePath As String
If FileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
filePath = FileDialog.FileName.Trim()
If FileDialog.FileName.Trim() = String.Empty Then
MessageBox.Show("Please select data file.", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
Else
Dim rtnDataSet As New DataSet()
Dim fileName As String = FileDialog.SafeFileName
If fileName.Substring(fileName.LastIndexOf("."c)).ToLower() = ".xlsx" Then
rtnDataSet = GenerateDataTableFromXlSX(filePath)
ElseIf fileName.Substring(fileName.LastIndexOf("."c)).ToLower() = ".xls" Then
rtnDataSet = GenerateDataTableFromXlS(filePath)
Else
rtnDataSet = GenerateDataTableFromCSV(filePath)
End If
Catch ex As Exception
End Try
End Sub
'The below file uses NPOI dll to read the xls file type.
Private Function GenerateDataTableFromXlS(ByVal FilePath As String) As DataSet
Dim ds As New DataSet
Dim ext As String = Path.GetExtension(FilePath)
Try
'Get the excel from filepath
Dim workbook As IWorkbook
Dim worksheet As ISheet
Dim sheetCount As Integer
Dim file = New FileStream(FilePath, FileMode.Open)
workbook = New HSSFWorkbook(file)
sheetCount = workbook.NumberOfSheets
For i As Integer = 0 To sheetCount - 1
Dim table As New DataTable
worksheet = workbook.GetSheetAt(i)
'Get Excel rows
Dim rows As Integer = worksheet.PhysicalNumberOfRows
'Get the column count
Dim columns As Integer = worksheet.GetRow(0).PhysicalNumberOfCells
For k As Integer = 0 To columns - 1
table.Columns.Add(k.ToString())
Next
'Now we add each row to our new DataTable
For x As Integer = 0 To rows - 1
Dim row As DataRow = table.NewRow()
For y As Integer = 0 To columns - 1
If (Not worksheet.GetRow(x).GetCell(y) Is Nothing) Then
row(y) = worksheet.GetRow(x).GetCell(y).ToString()
Else
row(y) = ""
End If
Next
table.Rows.Add(row)
Next
ds.Tables.Add(table)
Next
worksheet = Nothing
workbook = Nothing
Catch ex As Exception
End Try
Return ds
End Function
'The below file uses EPPLUS dll to read the xlsx file type.
Public Function GenerateDataTableFromXlSX(ByVal FilePath As String) As DataSet
Dim ds As DataSet = New DataSet()
Try
Dim dt As New DataTable
Dim fi As FileInfo = New FileInfo(FilePath)
'Check if the file exists
If Not fi.Exists Then
Throw New Exception("File” + FilePath + " Does Not Exists")
End If
Using xlPackage As New ExcelPackage(fi)
'Get the first worksheet in the workbook
For Each worksheet As OfficeOpenXml.ExcelWorksheet In xlPackage.Workbook.Worksheets
dt = New DataTable()
Dim startCell As ExcelCellAddress = worksheet.Dimension.Start
Dim endCell As ExcelCellAddress = worksheet.Dimension.End
Dim col As Integer = startCell.Column
Do While (col <= endCell.Column)
dt.Columns.Add(col.ToString)
col = (col + 1)
Loop
Try
If dt.Columns.Count > 0 Then
Dim rowNumber = 2
Do While (rowNumber <= worksheet.Dimension.End.Row)
Dim row = worksheet.Cells(rowNumber, 1, rowNumber,
worksheet.Dimension.End.Column)
Dim newRow = dt.NewRow
For Each cell In row
newRow(cell.Start.Column - 1) = cell.Text
Next
rowNumber = (rowNumber + 1)
dt.Rows.Add(newRow)
Loop
ds.Tables.Add(dt)
End If
Catch ex As Exception
End Try
Next
End Using
Return ds
Catch ex As Exception
End Try
Return ds
End Function
'The below file uses SYSTEM.IO dll to read the csv file type.
Public Function GenerateDataTableFromCSV(ByVal FilePath As String) As DataSet
Dim ds As New DataSet
Dim dt As New System.Data.DataTable
Try
Dim firstLine As Boolean = True
Dim separator As Char = DetectSeparatorforCSV(FilePath)
If IO.File.Exists(FilePath) Then
Using sr As New StreamReader(FilePath)
While Not sr.EndOfStream
If firstLine Then
firstLine = False
Dim cols = sr.ReadLine.Split(separator)
For Each col In cols
dt.Columns.Add(New DataColumn(col, GetType(String)))
Exit For
Next
Else
Dim data() As String
Try
data = sr.ReadLine.Split(separator)
For Each row In data
dt.Rows.Add(row)
Next
Catch ex As Exception
data = sr.ReadLine.Replace(",", "").Split(separator)
dt.Rows.Add(data)
End Try
End If
End While
End Using
End If
ds.Tables.Add(dt)
Catch ex As Exception
End Try
Return ds
End Function
Public Function DetectSeparatorforCSV(ByVal FilePath As String) As Char
Dim separator As String = ""
Try
If IO.File.Exists(FilePath) Then
Using sr As New StreamReader(FilePath)
While Not sr.EndOfStream
Dim cols = sr.ReadLine()
If CBool((CStr(cols.Contains(ControlChars.Tab)))) Then
separator = ControlChars.Tab
ElseIf CBool((CStr(cols.Contains(ControlChars.VerticalTab)))) Then
separator = ControlChars.VerticalTab
ElseIf CBool((CStr(cols.Contains(ControlChars.NewLine)))) Then
separator = ControlChars.NewLine
ElseIf CBool((CStr(cols.Contains(":")))) Then
separator = ":"
Else
separator = ","
End If
Exit While
End While
End Using
End If
Catch ex As Exception
End Try
Return Convert.ToChar(separator)
End Function
So, this blog will help you to read various excel files in different ways and manipulate them as per your requirements in SQL server database table, store procedure etc. If you like this blog then please share your comments.
