Export DataTable to Excel and change and format cell color based on condition using ClosedXML Library in ASP.Net

Last Reply on Oct 24, 2016 09:12 AM By dharmendr

Posted on Oct 17, 2016 02:33 AM

Hi,

With the below code its successfully exporting to excel.

i want two things from below code

1) When Status="VALID" then background color should be green and when "NOTDONE" then red

2) After 

For Each dt As DataTable In ds.Tables
                                wb.Worksheets.Add(dt)
                            Next
// here i want to add multiple custom rows on Employee name column
after adding all employee on that dataset

 

Protected Sub ExportExcel(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click, btnExport.Click
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "SELECT EmployeeName,CourseNo,CourseName,Status FROM Employee"

        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand(query)
                Using sda As New SqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    Using ds As New DataSet()
                        sda.Fill(ds)

                        'Set Name of DataTables.
                        ds.Tables(0).TableName = "Employee"
                        Using wb As New XLWorkbook()
                            For Each dt As DataTable In ds.Tables
                                wb.Worksheets.Add(dt)
                            Next
                            Response.Clear()
                            Response.Buffer = True
                            Response.Charset = ""
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                            Response.AddHeader("content-disposition", "attachment;filename=" & Microsoft.VisualBasic.Strings.Format(Now, "dd-MMM-yyyy") & ".xlsx")
                            Using MyMemoryStream As New MemoryStream()
                                wb.SaveAs(MyMemoryStream)
                                MyMemoryStream.WriteTo(Response.OutputStream)
                                Response.Flush()
                                Response.End()
                            End Using
                        End Using
                    End Using
                End Using
            End Using
        End Using

    End Sub

 

You are viewing reply posted by: dharmendr on Oct 24, 2016 09:12 AM.
Posted on Oct 24, 2016 09:12 AM

Closed XML doesn't have AutoFit functionality. So you need to assigh the height and width for this.

ws.ColumnWidth = 30;
ws.RowHeight = 20;

for border

XLBorderStyleValues bs = XLBorderStyleValues.Thin;
ws.Style.Border.SetOutsideBorder(bs);