Click here to Skip to main content
14,132,833 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

15.7K views
5 bookmarked
Posted 19 Jan 2017
Licenced CPOL

How to Use EPPlus in VB

, 19 Jan 2017
Rate this:
Please Sign up or sign in to vote.
This tip describes how to convert the data table/grid into Excel sheet.

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:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


You may also be interested in...

Pro

Comments and Discussions

 
GeneralMy vote of 5 Pin
Suvendu Shekhar Giri20-Jan-17 1:36
professionalSuvendu Shekhar Giri20-Jan-17 1:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.190518.1 | Last Updated 19 Jan 2017
Article Copyright 2017 by Neha Ambasta
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid