User288213138 posted
Hi Neeraj Yadav,
According to your description, I made a demo for you as a reference.
I am using epplus to operate Excel.
The code:
static void Main(string[] args)
{
//create a new ExcelPackage
using (ExcelPackage excelPackage = new ExcelPackage())
{
//the query or stored procedure name for the database
string sqlQuery = "SELECT * FROM customer";
//create a datatable
DataTable dataTable = loadExternalDataSet(sqlQuery);
//create a WorkSheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet 1");
//add all the content from the DataTable, starting at cell A1
worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);
for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
{
for (int row = 1; row <= worksheet.Dimension.End.Row; row++)
{
int value;
if (int.TryParse(worksheet.Cells[col, row].Value.ToString(), out value))
{
if (value == 1)
{
worksheet.Cells[col, row].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[col, row].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#FF0000"));
}
}
}
}
FileInfo fi = new FileInfo(@"C:\Users\samwu\Desktop\File.xlsx");
excelPackage.SaveAs(fi);
}
}
public static DataTable loadExternalDataSet(string sqlQuery)
{
string strDSN = "your connection string";
OleDbConnection myConn = new OleDbConnection(strDSN);
OleDbDataAdapter myCmd = new OleDbDataAdapter(sqlQuery, myConn);
myConn.Open();
DataTable dt = new DataTable();
myCmd.Fill(dt);
myConn.Close();
return dt;
}
The result:

Best regards,
Sam