How to Use EPPlus in VB

Introduction

Many times, there is a requirement where user wants to take a print out of the data table/grid available.

So, here we are going to convert the grid available to Excel sheet. So, you can save it for future reference or you can a print out of it. EPPlus is a DLL (dynamic link library) which helps in creating an Excel worksheet.

Using the Code

Follow the steps given below:

1. First, include the EPPlus in your project

   In the Solution Explorer, click on references and right click on it. Then click on Add references.

   A dialog box will appear like this:

   

   Then browse the DLL and add it as a reference in your project.

2. Then import these:

   Imports OfficeOpenXml.Style
   Imports OfficeOpenXml
   

3. Suppose we are getting the list of folders and the numbers of fields that a folder object will have. Here, fields will be the column in the worksheet.

   Then, our web service code will be like this:

   <WebMethod(EnableSession:=True)>
   Public Function FolderGridExportToExcel(ByVal folderList As List(Of String), _
   ByVal fieldsCount As Integer) As ResponseModel
       Try
           Dim outputDir = CreateExcelWorksheet(fieldsCount, folderList)
           If outputDir Is Nothing Then
               response.Message = "Some error occurred while converting to xls."
           Else
               response.Message = outputDir
           End If
       Catch ex As Exception
           ClassError.ErrMsg(ex)
           response = GetErrorResponse()
           response.Message = "Some error occurred while converting to xls."
       End Try
       Return response
   End Function
   

   Here, we are calling this method CreateExcelWorksheet(fieldsCount, folderList), which will return the path of the file where the excelsheet will be stored. In the CreateExcelWorksheet(fieldsCount, folderList), we will write the code for creating and designing the worksheet and then adding the data into it.

    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

   Here, we have also written code for creating the directory if it is not available and also for deleting the files from the folder in the server if their creation time is more than 30 minutes from now. We are sending the downloadable path of the folder from the webservice to the JavaScript.

   In JavaScript, we will just set the current URL as the downloadable path. So, the user will be able to download the excelsheet.

4. Our Ajax code will be like this:

   window.masterShowAjaxProgress();
   $.ajax({
       type: "POST",
       url: url,
       data: data,
       contentType: "application/json; charset=utf-8",
       dataType: "json",
       success: function (response) {
           if (response.d.Code == globalItem.WebServiceStatus.Success) {
               window.location.href = currentURLOfYourPage + "/" +
               response.d.Message; //here we are setting the current Url
               //as the CurrentUrl/file path, so that the download dialog will come for user
           } else {
               window.openGlobalOperationMessage(response.d.Message, true);
           }
       },
       error: function (jqXHR, exception) {
           window.openGlobalOperationMessage
           ("Error occurred while exporting the grid to excel", true);
       },
       complete: function () {
           window.masterHideAjaxProgress();
       }
   });
   

The final excelsheet will look like this: