Export data from MS Access to Excel in asp.net c# RRS feed

  • Question

  • User2003675111 posted

    Hi All,

    I want to export ms access data to excel.

    If any column of table has value 1 then set the back ground color red of cell in excel during exporting data.

    How to achive this? please help.


    Neeraj Yadav

    Monday, May 27, 2019 6:04 PM


  • 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");
            public static DataTable loadExternalDataSet(string sqlQuery)
                string strDSN = "your connection string";
                OleDbConnection myConn = new OleDbConnection(strDSN);
                OleDbDataAdapter myCmd = new OleDbDataAdapter(sqlQuery, myConn);
                DataTable dt = new DataTable();
                return dt;

    The result:

    Best regards,


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 28, 2019 5:55 AM