epplus excel vb.net
Private Function CreateExcelWorksheet_
(ByVal fieldsCount As Integer, ByVal folderList As List(Of String)) As String
Dim message As String
Try
' Set the file name and get the output directory
Dim fileName = "ItemList-" + _
DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx"
Dim outputDir = 'D:\FolderReports'
' Create directory, if not exist
If Not My.Computer.FileSystem.DirectoryExists(outputDir) Then
System.IO.Directory.CreateDirectory(outputDir)
End If
' Create the file using the FileInfo object
Dim file = New System.IO.FileInfo(outputDir + fileName)
'For deleting the files if they are created before 10 minutes
Dim fileStoredInDirectory() As String = System.IO.Directory.GetFiles(outputDir)
For Each item As String In fileStoredInDirectory
Dim fileInfo As System.IO.FileInfo = New System.IO.FileInfo(item)
Try
If fileInfo.CreationTime <= DateTime.Now.AddMinutes(-30) Then
fileInfo.Delete()
End If
Catch ex As Exception
ClassError.ErrMsg(ex)
message = Nothing
End Try
Next
' Creating the package
Using package = New ExcelPackage(file)
'adding the worksheet
' Customizing the worksheet
Dim worksheet As ExcelWorksheet = _
package.Workbook.Worksheets.Add("Item list - " + _
DateTime.Now.ToShortDateString())
' Formatting of the worksheet
worksheet.TabColor = System.Drawing.Color.Black
worksheet.DefaultRowHeight = 12
worksheet.HeaderFooter.FirstFooter.LeftAlignedText = _
String.Format("Generated: {0}", DateTime.Now.ToShortDateString())
'Adding style to the header
worksheet.Row(1).Height = 20
Dim headerRowStyle = worksheet.Row(1).Style
headerRowStyle.Fill.PatternType = ExcelFillStyle.SolidheaderRowStyle._
Fill.BackgroundColor.SetColor_
(System.Drawing.ColorTranslator.FromHtml_
("#d9d9d9"))
headerRowStyle.Font.Bold = True
headerRowStyle.Font.Color.SetColor(System.Drawing.Color.Black)
worksheet.Row(1).Style.ShrinkToFit = False
'Filling values for all the columns and rows
Dim rowLength As Integer = 1
If attachmentList Is Nothing Then
Dim index As Integer = 0
For row As Integer = 1 To folderList.Count / fieldsCount
For colLength As Integer = 1 To fieldsCount
worksheet.Cells(row, colLength).Value = folderList(index)
index += 1
Next
Next
'Fit the columns according to its content
For colLength As Integer = 1 To fieldsCount
worksheet.Column(colLength).AutoFit()
Next
'Set some document properties
package.Workbook.Properties.Title = "Item List"
package.Workbook.Properties.Author = "Developer"
package.Workbook.Properties.Company = "Developer"
'save your worksheet and we are done!
package.Save()
End Using
Dim downloadablePath() As String
downloadablePath = Split(outputDir, "\")
Dim downloadablePathLength = downloadablePath.Length - 2
outputDir = downloadablePath(downloadablePathLength)
outputDir = outputDir + "/" + fileName
message = outputDir
Catch ex As Exception
ClassError.ErrMsg(ex)
message = Nothing
End Try
Return message
End Function