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;
}
}
}
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;
}
}
}
This helped quite a bit. Thanks.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteVery nice, Thanks!
ReplyDelete