"epplus excel vb.net" Code Answer's

epplus excel vb.net

By Cruel Cardinal on May 15, 2020

 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

Source: www.codeproject.com

Add Comment