export to excel using generic list in vb.net

I am trying to export data to excel using a generic list and till now I have been doing it using datatable which I got the code ready using openxml as below, and if someone can help me either changing the code to completely directly replace the datatable to list or convert the list to datatable
Public Function ConvertDataTableToExcel(ByVal dt As DataTable, ByVal WorkBookName As String) As MemoryStream
        Dim excelStreamObj As New MemoryStream

        Try
            Dim objWB As New ClosedXML.Excel.XLWorkbook
            Dim objWorksheet As ClosedXML.Excel.IXLWorksheet = objWB.Worksheets.Add(WorkBookName)
            Dim i As Integer = 1

            For Each col As DataColumn In dt.Columns
                Dim excelColumn As IXLColumn = objWorksheet.Column(i)
                excelColumn.Style.Border.RightBorder = XLBorderStyleValues.Thin
                excelColumn.Style.Border.LeftBorder = XLBorderStyleValues.Thin
                excelColumn.Style.Border.BottomBorder = XLBorderStyleValues.Thin
                excelColumn.Style.Border.TopBorder = XLBorderStyleValues.Thin
                objWorksheet.Cell(1, i).Value = col.ColumnName
                objWorksheet.Cell(1, i).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center
                objWorksheet.Cell(1, i).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center
                objWorksheet.Cell(1, i).Style.Fill.BackgroundColor = XLColor.LightSkyBlue
                objWorksheet.Cell(1, i).Style.Font.Bold = True
                i = i + 1
            Next

            Dim rowCount As Integer = 2
            For Each row As DataRow In dt.Rows
                Dim colCount As Integer = 1
                For Each col As DataColumn In dt.Columns
                    objWorksheet.Cell(rowCount, colCount).Value = CatchNull(row(col))
                    colCount = colCount + 1
                Next
                rowCount = rowCount + 1
            Next

            objWorksheet.Columns.AdjustToContents()
            objWB.SaveAs(excelStreamObj)
        Catch ex As Exception
            Throw ex
        End Try

        Return excelStreamObj
    End Function

Open in new window

welcome 123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Can you give me some idea of what kind of help that you are going to need?
0
welcome 123Author Commented:
I need a function where I can just convert the list to datatable and use the above function or send me some code for export to excel using generic list either way is fine
0
Bob LearnedCommented:
Hmmm...if you convert a generics list to a DataTable, then you have double the work, I think that getting a row, column implementation for a list would be a better choice.

Imports System.IO
Imports System.Reflection
Imports ClosedXML.Excel

Public Class ExcelWorkbookService

    Public Shared Function ConvertGenericsListToExcel(Of TElement)(ByVal elementList As List(Of TElement),
        ByVal workBookName As String) As MemoryStream

        Dim excelStream = New MemoryStream

        Dim workbook As New XLWorkbook
        Dim workSheet As IXLWorksheet = workbook.Worksheets.Add(workBookName)

        Dim propertyList = GetType(TElement).GetProperties().ToList()

        CreateColumnHeaders(propertyList, workSheet)
        CreateWorksheetData(propertyList, elementList, workSheet)

        workSheet.Columns.AdjustToContents()
        workbook.SaveAs(excelStream)

        Return excelStream
    End Function

    Private Shared Sub CreateWorksheetData(Of TElement)(ByVal propertyList As List(Of PropertyInfo),
                                                 ByVal elementList As List(Of TElement),
                                                 ByVal worksheet As IXLWorksheet)

        Dim row = 1
        For Each element In elementList
            Dim column = 1
            For Each propertyInfo In propertyList
                Dim value = propertyInfo.GetValue(element, Nothing)
                worksheet.Cell(row, column).Value = value
                column += 1
            Next propertyInfo
            row += 1
        Next element
    End Sub

    Private Shared Sub CreateColumnHeaders(ByVal propertyList As List(Of PropertyInfo),
                                    ByVal workSheet As IXLWorksheet)

        Dim column = 1
        For Each propertyInfo In propertyList

            Dim excelColumn As IXLColumn = workSheet.Column(column)

            With excelColumn.Style.Border
                .RightBorder = XLBorderStyleValues.Thin
                .LeftBorder = XLBorderStyleValues.Thin
                .BottomBorder = XLBorderStyleValues.Thin
                .TopBorder = XLBorderStyleValues.Thin
            End With

            With workSheet.Cell(1, column)
                .Value = propertyInfo.Name

                With .Style
                    .Alignment.Vertical = XLAlignmentVerticalValues.Center
                    .Alignment.Horizontal = XLAlignmentHorizontalValues.Center
                    .Fill.BackgroundColor = XLColor.LightSkyBlue
                    .Font.Bold = True
                End With
                column += 1
            End With
        Next propertyInfo
    End Sub

End Class

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Bob LearnedCommented:
C# example for completeness:

using System.Linq;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using ClosedXML.Excel;

public class ExcelWorkbookService
{

    public static MemoryStream ConvertGenericsListToExcel<TElement>(List<TElement> elementList, string workBookName)
    {
        var excelStream = new MemoryStream();

        var workbook = new XLWorkbook();
        var workSheet = workbook.Worksheets.Add(workBookName);

        var propertyList = typeof(TElement).GetProperties().ToList();

        CreateColumnHeaders(propertyList, workSheet);
        CreateWorksheetData(propertyList, elementList, workSheet);

        workSheet.Columns().AdjustToContents();
        workbook.SaveAs(excelStream);

        return excelStream;
    }


    private static void CreateWorksheetData<TElement>(List<PropertyInfo> propertyList, List<TElement> elementList, IXLWorksheet worksheet)
    {
        var row = 1;
        foreach (var element in elementList) {
            var column = 1;
            foreach (var propertyInfo in propertyList) {
                var value = propertyInfo.GetValue(element, null);
                worksheet.Cell(row, column).Value = value;
                column += 1;
            }
            row ++;
        }
    }


    private static void CreateColumnHeaders(List<PropertyInfo> propertyList, IXLWorksheet workSheet)
    {
        var column = 1;

        foreach (var propertyInfo in propertyList) {
            var excelColumn = workSheet.Column(column);

            excelColumn.Style.Border.RightBorder = XLBorderStyleValues.Thin;
            excelColumn.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            excelColumn.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
            excelColumn.Style.Border.TopBorder = XLBorderStyleValues.Thin;

            workSheet.Cell(1, column).Value = propertyInfo.Name;

            workSheet.Cell(1, column).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            workSheet.Cell(1, column).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            workSheet.Cell(1, column).Style.Fill.BackgroundColor = XLColor.LightSkyBlue;
            workSheet.Cell(1, column).Style.Font.Bold = true;
            column ++;
        }
    }

}

Open in new window

0
welcome 123Author Commented:
I will try this today so all I need to do is put all the above code in a class and then create a object of the class and call the function and pass my list and a name of the excel I want to create right?
0
Bob LearnedCommented:
Usage example:

public class Author
{
     public string FirstName { get; set; }
     public string LastName { get; set; }
     public string Genre { get; set; }
}

List<Author> authorList = _dataLayer.GetAuthorList();

using (var stream = ExcelWorkbookService.ConvertGenericsListToExcel<Author>(authorList))
{
}

Open in new window

0
welcome 123Author Commented:
sorry for the delay, am using it now got busy with some other work , will update you in an hour from now
0
welcome 123Author Commented:
well I got everything right looks like but when it comes to showing the excel in response object or saving it to a location to see the results is where I fail, can you help as I don't know if the excel is well formed or not and below is my code:

I put all the above code converted to vb.net in a class as below:


Imports System.Linq
Imports System.Collections.Generic
Imports System.IO
Imports System.Reflection
Imports ClosedXML.Excel

Public Class ExcelWorkbookService



    Public Shared Function ConvertGenericsListToExcel(Of TElement)(ByVal elementList As List(Of TElement), ByVal workBookName As String) As MemoryStream
        Dim excelStream = New MemoryStream()

        Dim workbook = New XLWorkbook()
        Dim workSheet = workbook.Worksheets.Add(workBookName)

        Dim propertyList = GetType(TElement).GetProperties().ToList()

        CreateColumnHeaders(propertyList, workSheet)
        CreateWorksheetData(propertyList, elementList, workSheet)

        workSheet.Columns().AdjustToContents()
        workbook.SaveAs(excelStream)

        Return excelStream
    End Function


    Private Shared Sub CreateWorksheetData(Of TElement)(ByVal propertyList As List(Of PropertyInfo), ByVal elementList As List(Of TElement), ByVal worksheet As IXLWorksheet)
        Dim row = 1
             For Each element As TElement In elementList
                      Dim column = 1
            For Each propertyInfo As PropertyInfo In propertyList

                Dim value = propertyInfo.GetValue(element, Nothing)
                worksheet.Cell(row, column).Value = value
                column += 1
            Next
            row += 1
        Next
    End Sub


    Private Shared Sub CreateColumnHeaders(ByVal propertyList As List(Of PropertyInfo), ByVal workSheet As IXLWorksheet)
        Dim column = 1
     

              For Each propertyInfo As PropertyInfo In propertyList
            Dim excelColumn = workSheet.Column(column)

            excelColumn.Style.Border.RightBorder = XLBorderStyleValues.Thin
            excelColumn.Style.Border.LeftBorder = XLBorderStyleValues.Thin
            excelColumn.Style.Border.BottomBorder = XLBorderStyleValues.Thin
            excelColumn.Style.Border.TopBorder = XLBorderStyleValues.Thin

            workSheet.Cell(1, column).Value = propertyInfo.Name
       

            workSheet.Cell(1, column).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center
            workSheet.Cell(1, column).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center
            workSheet.Cell(1, column).Style.Fill.BackgroundColor = XLColor.LightSkyBlue
            workSheet.Cell(1, column).Style.Font.Bold = True
            column += 1
        Next
    End Sub

End Class




Then I call the method of that class passing y list which I stored in a session variable by the way and then try to see the result through the response object which throws an error:
 Dim objExcelWorkbookService As New ExcelWorkbookService
        Dim excelStream As MemoryStream

        With objExcelWorkbookService
            excelStream = .ConvertGenericsListToExcel(Session("SearchAgencydata"), "Test")
             WriteToXSLStream(excelStream)

   Sub WriteToXSLStream(ByVal streamOutput As MemoryStream)
        With Response
            .ClearHeaders()
            .ClearContent()
            .Clear()
            .Buffer = True
            .Charset = ""
             .AddHeader("content-disposition", "attachment; filename=ECB Report.xlsx")
            streamOutput.WriteTo(.OutputStream)
            .Flush()
            .End()
        End With
    End Sub
0
Bob LearnedCommented:
I haven't used ClosedXml, so are you getting any errors?
0
welcome 123Author Commented:
yes I am , and what else I can use in order to show the excel to the users apart from closed xml
0
welcome 123Author Commented:
oh sorry I didn't see that, maybe its a mistake, I can take that off

Imports ClosedXML.Excel (right) but the method in that class is not throwing any errors though its at WriteToXSLStream I am getting the error
0
Bob LearnedCommented:
Well, it looked like you were using ClosedXml, so you would need to set the response content type to XML and the file extension differently, so that it doesn't try to process the file as a packaged Excel workbook (xlsx).
0
welcome 123Author Commented:
can you help me giving example as even if I use a datatable my code fails at

    Sub WriteToXSLStream(ByVal streamOutput As MemoryStream)
        With Response
            .ClearHeaders()
            .ClearContent()
            .Clear()
            .Buffer = True
            .Charset = ""
            .AddHeader("content-disposition", "attachment; filename=ECB Report.xlsx")
            streamOutput.WriteTo(.OutputStream)
            .Flush()
            .End()
        End With

    End Sub
0
Bob LearnedCommented:
Try setting ContentType to this:

.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
.AddHeader("content-disposition", "attachment; filename=ECB Report.xlsx")

Open in new window

0
welcome 123Author Commented:
its failing in a javascript function (don't know who calls it, maybe the master page and the function  is   _endPostBack: function PageRequestManager$_endPostBack(error, executor, data) {
        if (this._request === executor.get_webRequest()) {
            this._processingRequest = false;
            this._additionalInput = null;
            this._request = null;
        }

        var eventArgs = new Sys.WebForms.EndRequestEventArgs(error, data ? data.dataItems : {}, executor);
        Sys.Observer.raiseEvent(this, "endRequest", eventArgs);
        if (error && !eventArgs.get_errorHandled()) {
            throw error;
        }
0
welcome 123Author Commented:
the above got resolved after I added a trigger in my script manager but I get the file corrupted or not the correct format and am unable to open the xlxs file even though I save to desk and am attaching one of those:
test.xlsx
0
welcome 123Author Commented:
I got it corrected, it works now thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.