Remove From My Forums

export datatable to excel (.xlsx) using c# with cells formatting styles + email attachment ......( Resolved Calls % of Total Calls )should be( >80%-green,70% to 80%-yellow and <70%-red)

Question

User2130689380 posted

public static void ExportToExcel(DataTable tbl, string excelFilePath = null)
{
try
{
if (tbl == null || tbl.Columns.Count == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");

// load excel, and create a new workbook
var excelApp = new Excel.Application();
excelApp.Workbooks.Add();

// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;

// column headings
for (var i = 0; i < tbl.Columns.Count; i++)
{
workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;

}

// rows
for (var i = 0; i < tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (var j = 0; j < tbl.Columns.Count; j++)
{
workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
string cellRange = string.Format("J{0}", i + 1);
if (cellRange == "0")
{
workSheet.Cells[i + 1].Style.Fill.BackgroundColor = XLColor.GreenYellow;
}
else
{
workSheet.Cells[i + 1].Style.Fill.BackgroundColor = XLColor.Yellow;
}
}

}

// check file path
if (!string.IsNullOrEmpty(excelFilePath))
{
try
{
workSheet.SaveAs(excelFilePath);
excelApp.Quit();
//MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else
{ // no file path is given
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}

Particulars	Total New Calls for the month	Total Resolved Calls	Resolved Calls % of Total Calls	0-3 Days	4-7 Days	8-15 Days	16-30 Days	>31 Days	% of Resolved Calls under 3 Days	Total Pending Calls	Pending Calls % of Total Calls	Pending 0-3 Days	Pending 4-7 Days	Pending 8-15 Days	Pending 16-30 Days	Pending >31 Days	% of Pending Calls over 3 Days
Total	1312	741	45%	463	199	73	6	0	62%	570	34%	187	102	168	113	0	67%
East	101	46	46%	25	19	2	0	0	54%	55	54%	13	15	22	5	0	76%
BIHAR	1	0	0%	0	0	0	0	0	0%	1	50%	0	0	1	0	0	100%
JHARKHAND	21	0	0%	0	0	0	0	0	0%	21	100%	4	4	11	2	0	81%
ODISHA	77	46	40%	25	19	2	0	0	22%	31	27%	8	10	10	3	0	74%
WEST BENGAL	2	0	0%	0	0	0	0	0	0%	2	100%	1	1	0	0	0	50%
North	212	53	25%	23	19	8	3	0	43%	158	75%	31	25	48	54	0	80%
PUNJAB	11	3	27%	2	1	0	0	0	18%	8	73%	0	1	3	4	0	100%
CHANDIGARH	4	0	0%	0	0	0	0	0	0%	4	67%	0	2	1	1	0	100%
DELHI	11	4	36%	1	1	2	0	0	9%	7	64%	2	2	3	0	0	71%
HARYANA	23	0	0%	0	0	0	0	0	0%	23	100%	2	3	5	13	0	91%
UTTAR PRADESH	69	17	13%	9	8	0	0	0	7%	52	41%	11	7	17	17	0	79%
UTTARAKHAND	14	0	0%	0	0	0	0	0	0%	14	100%	2	2	4	6	0	86%
RAJASTHAN	80	29	33%	11	9	6	3	0	13%	50	57%	14	8	15	13	0	72%
South	78	27	35%	11	5	11	0	0	41%	51	65%	17	11	20	3	0	67%
TAMILNADU	1	0	0%	0	0	0	0	0	0%	1	100%	0	1	0	0	0	100%
KARNATAKA	32	11	28%	2	2	7	0	0	5%	21	54%	5	6	10	0	0	76%
TELANGANA	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
ANDHRA PRADESH	45	16	28%	9	3	4	0	0	16%	29	50%	12	4	10	3	0	59%
West	921	615	67%	404	156	52	3	0	66%	306	33%	126	51	78	51	0	59%
GUJARAT	503	370	63%	236	101	31	2	0	40%	133	23%	67	18	36	12	0	50%
GOA	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
CHATTISGARH	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
MADHYA PRADESH	99	46	37%	32	13	1	0	0	26%	53	43%	17	11	12	13	0	68%
MAHARASHTRA	319	199	49%	136	42	20	1	0	33%	120	30%	42	22	30	26	0	65%

Thursday, June 6, 2019 1:14 PM

Answers

User283571144 posted

Hi keshu59,

According to your description, I suggest you could try to use EPPlus library to exprot the datatable to the excel, then you could use system.mail.net library to send the email use the generated excel as the attechment.

More details, you could refer to below codes:

Notice: You should install the EPPlus from Nuget package firstly.

Install-Package EPPlus -Version 4.5.3.1

Aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SendEmailWithExcel.aspx.cs" Inherits="AspNetNormalIssue.Webform.SendEmailWithExcel" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClicK="Button1_Click" />
        </div>
    </form>
</body>
</html>

Code-behind:

using ClosedXML.Excel;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


namespace AspNetNormalIssue.Webform
{
    public partial class SendEmailWithExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            DataTable d1 = new DataTable();
            d1.Columns.Add("Particulars");
            d1.Columns.Add("Total New  Calls for the month");
            d1.Columns.Add("Total Resolved Calls");
            d1.Columns.Add("Resolved Calls % of Total Calls");
            d1.Rows.Add("Total", "1312", "741", "45%");
            d1.Rows.Add("East", "101", "46", "46%");
            d1.Rows.Add("BIHAR", "1", "0", "0%");
            d1.Rows.Add("BIHAR2", "1", "0", "75%");
            d1.Rows.Add("BIHAR3", "1", "0", "85%");

            ExportToExcel(d1, @"D:\Text.xls");
            email_send(@"D:\Text.xls");

        }

        public static void ExportToExcel(DataTable tbl, string excelFilePath = null)
        {

            //create a new ExcelPackage
            using (ExcelPackage excelPackage = new ExcelPackage())
            {


                //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(tbl, true);

              
                    for (int row = 1; row <= worksheet.Dimension.End.Row; row++)
                    {
                    var re = worksheet.Cells[row, 4].Value.ToString();
                    int value;
                    if (int.TryParse(worksheet.Cells[row, 4].Value.ToString().Split('%')[0], out value))
                    {

                        if (value >  80)
                        {
                            worksheet.Cells[row, 4].Style.Fill.PatternType = ExcelFillStyle.Solid;
                            worksheet.Cells[row, 4].Style.Fill.BackgroundColor.SetColor(Color.Green);
                        }
                        else if ( value <= 80 && value >= 70)
                        {
                            worksheet.Cells[row, 4].Style.Fill.PatternType = ExcelFillStyle.Solid;
                            worksheet.Cells[row, 4].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
                        }
                        else if (value <70)
                        {
                            worksheet.Cells[row, 4].Style.Fill.PatternType = ExcelFillStyle.Solid;
                            worksheet.Cells[row, 4].Style.Fill.BackgroundColor.SetColor(Color.Red);
                        }
                      
                    }

                }

               
                
                FileInfo fi = new FileInfo(excelFilePath);
                excelPackage.SaveAs(fi);

            }
        }

        public void email_send(string filepath)
        {
            MailMessage mail = new MailMessage();
            SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
            mail.From = new MailAddress("your mail@gmail.com");
            mail.To.Add("to_mail@gmail.com");
            mail.Subject = "Test Mail - 1";
            mail.Body = "mail with attachment";
            System.Net.Mail.Attachment attachment;
            attachment = new System.Net.Mail.Attachment(filepath);
            mail.Attachments.Add(attachment);

            SmtpServer.Port = 587;
            SmtpServer.Credentials = new System.Net.NetworkCredential("your mail@gmail.com", "your password");
            SmtpServer.EnableSsl = true;

            SmtpServer.Send(mail);

        }
    }
}

Result:

Best Regards,

Brando

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Friday, June 7, 2019 2:37 AM

All replies

0

Sign in to vote

User-821857111 posted

What's the question?

Thursday, June 6, 2019 1:20 PM
0

Sign in to vote

User2130689380 posted

sir ,

as u can see in my excel sheet i want to color in column (Resolved Calls % of Total Calls)

cells data with (>%80 -green , &70%-80% yellow and below 70%-red)

how to do in my code ,in whole scenario i want to convert datatable to excel with color formatting with mail attachment

Thursday, June 6, 2019 1:28 PM