Wednesday, August 8, 2012

Set Two Different Colour For Two Differennt Text In Same Cell Using EPPlus

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OfficeOpenXml;
using System.Drawing;
using OfficeOpenXml.Style;
using System.Data;

namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    //Create the worksheet
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");               


                    AddRecords(ws);
                    pck.Workbook.Properties.Title = "Excel Export";
                    pck.Workbook.Properties.Author = "Me";
                    pck.Workbook.Properties.Comments = "Sample Record Details";
                    pck.Workbook.Properties.Company = "ABC Tech";


                    byte[] fileBytes = pck.GetAsByteArray();

                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.ClearContent();
                    HttpContext.Current.Response.ClearHeaders();
                    HttpContext.Current.Response.Cookies.Clear();

                    //Add the header & other information
                    HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.Private);
                    HttpContext.Current.Response.CacheControl = "private";
                    HttpContext.Current.Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
                    HttpContext.Current.Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
                    HttpContext.Current.Response.AppendHeader("Content-Length", fileBytes.Length.ToString());
                    HttpContext.Current.Response.AppendHeader("Pragma", "cache");
                    HttpContext.Current.Response.AppendHeader("Expires", "60");

                    string DateTimes = DateTime.Now.TimeOfDay.ToString().Replace(":", "_").Replace(".", "_");

                    //Default Download Filename
                    string FileName = "ExportBiblio_" + DateTimes + ".xlsx";

                    HttpContext.Current.Response.AppendHeader("Content-Disposition",
                    "attachment; " +
                    "filename=\"" + FileName + "\"; " +
                    "size=" + fileBytes.Length.ToString() + "; " +
                    "creation-date=" + DateTime.Now.ToString("R") + "; " +
                    "modification-date=" + DateTime.Now.ToString("R") + "; " +
                    "read-date=" + DateTime.Now.ToString("R"));
                    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.sampleDemo.sheet";

                    //Write it back to the client
                    HttpContext.Current.Response.BinaryWrite(fileBytes);
                    HttpContext.Current.Response.End();

                }
            }

            catch (Exception ex)
            {
                ex.ToString();

            }

        }


        public void AddRecords(ExcelWorksheet _Wsheet1)
        {

            _Wsheet1.Column(1).Width = 15;
            _Wsheet1.Column(2).Width = 25;
            _Wsheet1.Column(3).Width = 20;

            _Wsheet1.Cells["B2"].Value = "Emp No ";
            _Wsheet1.Cells["B2"].Style.Font.Bold = true;
            _Wsheet1.Cells["C2"].Value = "777";

            _Wsheet1.Cells["C2"].Style.Font.Color.SetColor(Color.FromArgb(0, 112, 192));
            _Wsheet1.Cells["C2"].Style.Font.Bold = true;
            _Wsheet1.Cells["C2"].Style.Font.Size = 14;


            _Wsheet1.Cells["B3"].Value = "Name";
            _Wsheet1.Cells["B3"].Style.Font.Bold = true;
            _Wsheet1.Cells["C3"].Value = "My Name";
            _Wsheet1.Cells["C3"].Style.WrapText = true;
            _Wsheet1.Cells["C3:G3"].Merge = true;

            _Wsheet1.Cells["C3"].Style.Font.Color.SetColor(Color.FromArgb(0, 112, 192));
            _Wsheet1.Cells["C3"].Style.Font.Bold = true;
            _Wsheet1.Cells["C3"].Style.Font.Size = 14;

            _Wsheet1.Cells["B1:G2"].Style.WrapText = true;


            _Wsheet1.Cells["C3"].IsRichText = true;

            var rtDir1 = _Wsheet1.Cells["C3"].RichText.Add(" is Karthi.");
            rtDir1.Color = Color.Red;          

            // Set two colors for different text in same cell
            _Wsheet1.Cells["C5"].Value = " This is an ";
            _Wsheet1.Cells["C5"].Style.WrapText = true;
            _Wsheet1.Cells["C5"].IsRichText = true;
            _Wsheet1.Cells["C5"].Style.Font.Bold = true;
            _Wsheet1.Cells["C5"].Style.Font.Size = 12;
            _Wsheet1.Cells["C5"].Style.Font.Color.SetColor(Color.Blue);

            var rtDir2 = _Wsheet1.Cells["C5"].RichText.Add(" Example.");
            rtDir2.Color = Color.Red;

            _Wsheet1.Cells["B1:G2"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            _Wsheet1.Cells["B1:G2"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            _Wsheet1.Cells["B1:G2"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            _Wsheet1.Cells["B1:G2"].Style.Border.Right.Style = ExcelBorderStyle.Thin;

            var CellVal = _Wsheet1.Cells["C1"].RichText;

            var part = CellVal.Add("A");
            part.Color = Color.Blue;

        }
    }
}

3 comments: