asked on

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

Bob Learned

Can you give me some idea of what kind of help that you are going to need?

welcome 123

ASKER

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

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Learned

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

welcome 123

ASKER

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?

Bob Learned

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

welcome 123

ASKER

sorry for the delay, am using it now got busy with some other work , will update you in an hour from now

welcome 123

ASKER

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

Bob Learned

I haven't used ClosedXml, so are you getting any errors?

welcome 123

ASKER

yes I am , and what else I can use in order to show the excel to the users apart from closed xml

welcome 123

ASKER

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

Bob Learned

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).

welcome 123

ASKER

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

Bob Learned

Try setting ContentType to this:

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

Open in new window

welcome 123

ASKER

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;
}

welcome 123

ASKER

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

welcome 123

ASKER

I got it corrected, it works now thanks a lot