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

    Regards,

    Neeraj Yadav

    Monday, May 27, 2019 6:04 PM

Answers

  • 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

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