Remove From My Forums

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

Question
0

Sign in to vote

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

Answered by:

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

Question

Answers