public static void SetFormatCell(IXLWorksheet ws,
int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn,
bool isFontBold = false, int fontSize = 10, bool isMergeCell = false,
bool isSetColorHeader = false, bool isSetBorder = true,
XLAlignmentHorizontalValues HorizontalAlgn = XLAlignmentHorizontalValues.Center,
XLAlignmentVerticalValues VerticalAlgn = XLAlignmentVerticalValues.Center)
{
// Defining ranges
// From worksheet
var rangeTable = ws.Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn);
rangeTable.Style.Alignment.Horizontal = HorizontalAlgn;
rangeTable.Style.Alignment.Vertical = VerticalAlgn;
rangeTable.Style.Font.Bold = isFontBold;
rangeTable.Style.Font.FontSize = fontSize;
if (isSetColorHeader)
{
rangeTable.Style.Fill.BackgroundColor = XLColor.FromArgb(184, 184, 184);
}
if (isMergeCell)
{
// Merge title cells
//rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge()
rangeTable.Merge();
}
if (isSetBorder)
{
rangeTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
rangeTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
}
private static void SetRangeStyle(IXLRangeBase range, XLAlignmentHorizontalValues alignmentHorizontal = XLAlignmentHorizontalValues.Left)
{
range.Style
.Font.SetBold()
.Alignment.Horizontal = alignmentHorizontal;
range.Style.Fill.BackgroundColor = XLColor.Silver;
}
public XLAlignment(IXLStylized container, IXLAlignment d = null)
{
_container = container;
if (d == null)
{
return;
}
_horizontal = d.Horizontal;
_vertical = d.Vertical;
_indent = d.Indent;
_justifyLastLine = d.JustifyLastLine;
_readingOrder = d.ReadingOrder;
_relativeIndent = d.RelativeIndent;
_shrinkToFit = d.ShrinkToFit;
_textRotation = d.TextRotation;
_wrapText = d.WrapText;
}
public XLAlignmentHorizontalValues SetAlign(int i)
{
XLAlignmentHorizontalValues align = XLAlignmentHorizontalValues.Right;
switch (i)
{
case 0:
align = XLAlignmentHorizontalValues.Left;
break;
case 1:
align = XLAlignmentHorizontalValues.Center;
break;
case 15:
align = XLAlignmentHorizontalValues.Left;
break;
}
return(align);
}
private void Export_EXCEL(string sFacility, string sYear)
{
int nFacility = SystemFunction.GetIntNullToZero(sFacility);
string[] alphabet = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
MemoryStream fsExport = new MemoryStream();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
string[] Arr_Column = { "Indicator", "Unit", "Target", "Q1 : Jan", "Q1 : Feb", "Q1 : Mar", "Q2 : Apr", "Q2 : May", "Q2 : Jun", "Q3 : Jul", "Q3 : Aug", "Q3 : Sep", "Q4 : Oct", "Q4 : Nov", "Q4 : Dec", "Remark" };
double[] Arr_ColumnWidth = { 50, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 50 };
using (XLWorkbook wb = new XLWorkbook())
{
#region LG
var ws = wb.Worksheets.Add("EPIFROM_Intensity");
//สร้างหัวข้อ
string sCol = alphabet[Arr_Column.Length - 1].ToUpper();
#region CreatHeadReport
ws.Cell("A1").Value = "Indicator : Intensity Denominator ";
ws.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A1").Style.Font.FontName = "Cordia New";
ws.Cell("A1").Style.Font.FontSize = 14.00;
ws.Cell("A2").Value = "Operation Type : Refinery";
ws.Cell("A2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A2").Style.Font.FontName = "Cordia New";
ws.Cell("A2").Style.Font.FontSize = 14.00;
var Facility = db.mTFacility.FirstOrDefault(w => w.cActive == "Y" && w.cDel == "N" && w.nLevel == 2 && w.ID + "" == sFacility);
if (Facility != null)
{
sFacility = Facility.Name;
}
ws.Cell("A3").Value = "Sub-facility : " + sFacility;
ws.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A3").Style.Font.FontName = "Cordia New";
ws.Cell("A3").Style.Font.FontSize = 14.00;
ws.Cell("A4").Value = "Year : " + sYear;
ws.Cell("A4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A4").Style.Font.FontName = "Cordia New";
ws.Cell("A4").Style.Font.FontSize = 14.00;
#endregion
//สร้างหัวตาราง
int nRow = 5;
var RowsHead = ws;
string sCC = "#dbea97";
int nRowna = 1;
#region สร้างตาราง
var lst = ssTRetunrLoadData_1;
lst.lstIn.ForEach(f =>
{
sCC = "#dbea97";
if (f.ProductID != 89 && f.ProductID != 90)
{
for (int i = 0; i < Arr_Column.Length; i++)
{
sCol = alphabet[i].ToUpper();
GetCellHead(RowsHead, Arr_ColumnWidth[i], sCol, nRow + "", Arr_Column[i] + "", sCC);
}
}
if (f.ProductID != 89 && f.ProductID != 90)
{
nRow++;
}
string sText = "";
XLAlignmentHorizontalValues align = XLAlignmentHorizontalValues.Center;
for (int j = 0; j < Arr_Column.Length; j++)
{
sCol = alphabet[j].ToUpper();
sCC = "#dbea97";
if (f.cTotalAll != "Y")
{
sCC = "#ffedc4";
}
#region CaseRow
if (f.ProductID != 89 && f.ProductID != 90)
{
sText = SetText(j, f);
}
else
{
sysGlobalClass.T_TIntensity_Other ftemp = new sysGlobalClass.T_TIntensity_Other();
ftemp.sIndicator = f.ProductName;
ftemp.sUnit = f.sUnit;
ftemp.sTarget = f.Target;
ftemp.M1 = f.M1;
ftemp.M2 = f.M2;
ftemp.M3 = f.M3;
ftemp.M4 = f.M4;
ftemp.M5 = f.M5;
ftemp.M6 = f.M6;
ftemp.M7 = f.M7;
ftemp.M8 = f.M8;
ftemp.M9 = f.M9;
ftemp.M10 = f.M10;
ftemp.M11 = f.M11;
ftemp.M12 = f.M12;
sCC = "#FFFFFF";
sText = SetTextDetail(j, ftemp);
}
align = SetAlign(j);
#endregion
GetCellText(RowsHead, Arr_ColumnWidth[j], sCol, nRow + "", sText, align, j + "", sCC);
}
nRow++;
f.lstarrDetail.ForEach(f2 =>
{
for (int k = 0; k < Arr_Column.Length; k++)
{
sCC = "#FFFFFF";
sCol = alphabet[k].ToUpper();
sText = SetTextDetail(k, f2);
align = SetAlign(k);
GetCellText(RowsHead, Arr_ColumnWidth[k], sCol, nRow + "", sText, align, k + "", sCC);
}
nRow++;
});
});
#endregion
#endregion
#region Deviate
var dv = wb.Worksheets.Add("Deviate");
//สร้างหัวข้อ
sCol = alphabet[Arr_Column.Length - 1].ToUpper();
#region CreatHeadReport
dv.Cell("A1").Value = "Indicator : Intensity Denominator ";
dv.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A1").Style.Font.FontName = "Cordia New";
dv.Cell("A1").Style.Font.FontSize = 14.00;
dv.Cell("A2").Value = "Operation Type : Refinery";
dv.Cell("A2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A2").Style.Font.FontName = "Cordia New";
dv.Cell("A2").Style.Font.FontSize = 14.00;
dv.Cell("A3").Value = "Sub-facility : " + sFacility;
dv.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A3").Style.Font.FontName = "Cordia New";
dv.Cell("A3").Style.Font.FontSize = 14.00;
dv.Cell("A4").Value = "Year : " + sYear;
dv.Cell("A4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A4").Style.Font.FontName = "Cordia New";
dv.Cell("A4").Style.Font.FontSize = 14.00;
#endregion
//สร้างหัวตาราง
nRow = 5;
RowsHead = dv;
sCC = "#9cb726";
nRowna = 1;
#region สร้างตาราง
var lstdv = SystemFunction.GetDeviate(nIndicator, nOperationType, nFacility, sYear);
GetCellHead(RowsHead, 10, "A", nRow + "", "No." + "", sCC);
GetCellHead(RowsHead, 20, "B", nRow + "", "Month" + "", sCC);
GetCellHead(RowsHead, 20, "C", nRow + "", "Remark" + "", sCC);
GetCellHead(RowsHead, 20, "D", nRow + "", "Action By" + "", sCC);
GetCellHead(RowsHead, 20, "E", nRow + "", "Date" + "", sCC);
nRow++;
sCC = "#ffffff";
lstdv.ForEach(f =>
{
XLAlignmentHorizontalValues align = XLAlignmentHorizontalValues.Center;
GetCellText(RowsHead, 10, "A", nRow + "", nRowna + "", align, 0 + "", sCC);
GetCellText(RowsHead, 20, "B", nRow + "", f.sMonth + "", align, 0 + "", sCC);
GetCellText(RowsHead, 20, "C", nRow + "", f.sRemark + "", XLAlignmentHorizontalValues.Left, 0 + "", sCC);
GetCellText(RowsHead, 20, "D", nRow + "", f.sActionBy + "", align, 0 + "", sCC);
GetCellText(RowsHead, 20, "E", nRow + "", f.sDate + "", align, 0 + "", sCC);
nRow++;
nRowna++;
});
#endregion
#endregion
wb.SaveAs(fsExport);
}
string saveAsFileName = "Input_Intensity_" + sFacility + "_" + DateTime.Now.ToString("ddMMyyHHmmss", new CultureInfo("en-US")) + ".xlsx";
fsExport.Position = 0;
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + saveAsFileName);
Response.ContentType = "application/vnd.ms-excel";
try
{
Response.BinaryWrite(fsExport.ToArray());
}
catch (Exception ee)
{ }
ScriptManager.RegisterStartupScript(this, this.GetType(), "SessionExpire", "UnblockUI();", true);
Response.End();
}
public IXLStyle SetHorizontal(XLAlignmentHorizontalValues value)
{
Horizontal = value;
return _container.Style;
}
private void addValueToCell(IXLWorksheet ws, string value, int row, int cell, XLColor color = null, bool isBold = false, XLAlignmentHorizontalValues alignement = XLAlignmentHorizontalValues.Center)
{
ws.Cell(row, cell).Value = value;
if (color != null)
{
ws.Cell(row, cell).Style.Fill.BackgroundColor = color;
}
ws.Cell(row, cell).Style.Font.Bold = isBold;
ws.Cell(row, cell).Style.Alignment.Horizontal = alignement;
}
internal async Task <IXLCell> SetStyleToCell(IXLCell cell, int fontSize = 12, string fontName = "Segoe UI", bool isFontBold = false,
XLColor backgroundColor = null, XLAlignmentHorizontalValues horizontalAlignment = XLAlignmentHorizontalValues.Center,
XLAlignmentVerticalValues verticalAlignment = XLAlignmentVerticalValues.Center, XLColor outsideBorderColor = null, XLBorderStyleValues outsideBorder = XLBorderStyleValues.Dashed)
{
cell.Style.Font.FontSize = fontSize;
cell.Style.Font.FontName = fontName;
cell.Style.Font.Bold = isFontBold;
cell.Style.Border.OutsideBorder = outsideBorder;
cell.Style.Border.OutsideBorderColor = outsideBorderColor ?? XLColor.LightSlateGray;
cell.Style.Alignment.SetHorizontal(horizontalAlignment);
cell.Style.Alignment.SetVertical(verticalAlignment);
cell.Style.Alignment.SetWrapText();
if (backgroundColor != null)
{
cell.Style.Fill.BackgroundColor = backgroundColor;
}
return(cell);
}
public void GetCellText(IXLWorksheet RowsHead, double nWidthColumn, string StringCell, string NumCell, string sValue, XLAlignmentHorizontalValues Align, string index, string sCC)
{
//RowsHead.Range(StringCell + NumCell+":")
var temColumn = RowsHead.Column(StringCell);
//temColumn.Style.Fill.BackgroundColor = XLColor.DarkOrange;
temColumn.Width = nWidthColumn;
var itemCell = RowsHead.Cell(StringCell + NumCell);
itemCell.Value = sValue;
itemCell.Style.Alignment.Horizontal = Align;
itemCell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
//itemCell.Style.Font.Bold = true;
itemCell.Style.Font.FontName = "Cordia New";
itemCell.Style.Font.FontSize = 12;
//itemCell.Style.Font.Underline = true;
itemCell.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
itemCell.Style.Border.RightBorder = XLBorderStyleValues.Thin;
itemCell.Style.Border.TopBorder = XLBorderStyleValues.Thin;
itemCell.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
itemCell.Style.Fill.BackgroundColor = XLColor.FromHtml(sCC);
itemCell.Style.Border.LeftBorderColor = XLColor.Black;
itemCell.Style.Border.RightBorderColor = XLColor.Black;
itemCell.Style.Border.TopBorderColor = XLColor.Black;
itemCell.Style.Border.BottomBorderColor = XLColor.Black;
itemCell.Style.Alignment.WrapText = true;
}
public ExcelColumnContent()
{
this.HorizontalAlignment = XLAlignmentHorizontalValues.Left;
}
public static IXLRange Align(this IXLRange cell, XLAlignmentHorizontalValues horizontal = XLAlignmentHorizontalValues.Center)
{
cell.Style.Alignment.Horizontal = horizontal;
return(cell);
}
public IXLStyle SetHorizontal(XLAlignmentHorizontalValues value)
{
Horizontal = value;
return(_style);
}
public static IXLCell SetAlignHorizontal(this IXLCell cell, XLAlignmentHorizontalValues align)
{
cell.AsRange().SetAlignHorizontal(align);
return(cell);
}
public void AddHeaderData(string cell, string cellvalue, string font, bool isBold, XLAlignmentHorizontalValues Alignment, double fontsize, string mergeCellReference, XLCellValues DataType)
{
ws.Cell(cell).Value = cellvalue;
ws.Cell(cell).Style.Font.FontName = font;
ws.Cell(cell).Style.Font.Bold = isBold;
ws.Cell(cell).Style.Font.FontSize = fontsize;
ws.Cell(cell).Style.Alignment.Horizontal = Alignment;
ws.Range(mergeCellReference).Merge();
ws.Cell(cell).DataType = DataType;
}
public void AddHeaderData(string cell, string cellvalue, string font, bool isBold, XLAlignmentHorizontalValues Alignment, double fontsize, XLCellValues DataType, XLColor bgColor)
{
ws.Cell(cell).Value = cellvalue;
ws.Cell(cell).Style.Font.FontName = font;
ws.Cell(cell).Style.Font.Bold = isBold;
ws.Cell(cell).Style.Font.FontSize = fontsize;
ws.Cell(cell).Style.Alignment.Horizontal = Alignment;
ws.Cell(cell).DataType = DataType;
ws.Cell(cell).Style.Fill.BackgroundColor = bgColor;
}
public void AddHeaderDataWrapText(string cell, string cellvalue, string font, bool isBold, XLAlignmentHorizontalValues Alignment, double fontsize, XLCellValues DataType, XLAlignmentVerticalValues VerticalAlignment)
{
ws.Cell(cell).Value = cellvalue;
ws.Cell(cell).Style.Font.FontName = font;
ws.Cell(cell).Style.Font.Bold = isBold;
ws.Cell(cell).Style.Font.FontSize = fontsize;
ws.Cell(cell).Style.Alignment.Horizontal = Alignment;
ws.Cell(cell).DataType = DataType;
ws.Cell(cell).Style.Alignment.Vertical = VerticalAlignment;
ws.Cell(cell).Style.Alignment.SetWrapText();
}
public void AddHeaderDataWrapText(string cell, string cellvalue, string font, bool isBold, XLAlignmentHorizontalValues Alignment, double fontsize, string mergeCellReference, XLCellValues DataType, XLColor bgColor, XLAlignmentVerticalValues VerticalAlignment)
{
ws.Cell(cell).Value = cellvalue;
ws.Cell(cell).Style.Font.FontName = font;
ws.Cell(cell).Style.Font.Bold = isBold;
ws.Cell(cell).Style.Font.FontSize = fontsize;
ws.Cell(cell).Style.Alignment.Horizontal = Alignment;
ws.Cell(cell).Style.Alignment.Vertical = VerticalAlignment;
ws.Range(mergeCellReference).Merge();
ws.Cell(cell).DataType = DataType;
ws.Cell(cell).Style.Fill.BackgroundColor = bgColor;
ws.Cell(cell).Style.Alignment.SetWrapText();
}
public void AddCellValueNumberFormat(string cell, string cellvalue, string font, bool isBold, XLAlignmentHorizontalValues Alignment, double fontsize, string numFormat, XLColor bgColor)
{
ws.Cell(cell).Value = cellvalue;
ws.Cell(cell).Style.NumberFormat.Format = numFormat;
ws.Cell(cell).Style.Font.FontName = font;
ws.Cell(cell).Style.Font.Bold = isBold;
ws.Cell(cell).Style.Font.FontSize = fontsize;
ws.Cell(cell).Style.Alignment.Horizontal = Alignment;
ws.Cell(cell).Style.Fill.BackgroundColor = bgColor;
}
public void AddCellValue(string cell, string cellvalue, string font, bool isBold, XLAlignmentHorizontalValues Alignment, double fontsize, XLCellValues DataType)
{
ws.Cell(cell).Value = cellvalue;
ws.Cell(cell).Style.Font.FontName = font;
ws.Cell(cell).Style.Font.Bold = isBold;
ws.Cell(cell).Style.Font.FontSize = fontsize;
ws.Cell(cell).Style.Alignment.Horizontal = Alignment;
ws.Cell(cell).DataType = DataType;
}
public XLAlignment(IXLStylized container, IXLAlignment d = null)
{
_container = container;
if (d == null) return;
_horizontal = d.Horizontal;
_vertical = d.Vertical;
_indent = d.Indent;
_justifyLastLine = d.JustifyLastLine;
_readingOrder = d.ReadingOrder;
_relativeIndent = d.RelativeIndent;
_shrinkToFit = d.ShrinkToFit;
_textRotation = d.TextRotation;
_wrapText = d.WrapText;
}
