Basit Khan

Basit Khan

  • 1.3k
  • 336
  • 114.1k

Closedxml.excel how to change the forecolor depend upon cond

Oct 13 2016 7:28 AM
Hi,

i successfully exported excel file in asp.net with help on of ClosedXML with below code.
 
but how to do conditional formating suppose Ec_EmpNo= 1 or 3 then forecolor should be red.
 
Protected Sub ExportExcel(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click, btnExport.Click
Dim LdbM_SN2 As Double
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "SELECT Ec_EmpNo,Ec_CsNo,Ec_CsExpiryDt,Ec_Status,Ec_Prvd FROM Mst_EmployeeCourse"
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 = "Mst_EmployeeCourse"
Using wb As New XLWorkbook()
For Each dt As DataTable In ds.Tables
wb.Worksheets.Add(dt)
Next
'Export the Excel file.
Response.Clear()
Response.Buffer = True
Response.Charset = ""
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment;filename=" & 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
 
 
Thanks
Basit 

Answers (1)