public static ExcelRichText Add(this ExcelRichTextCollection richTextCollection,
string text,
bool bold = false,
bool italic = false,
Color?color = null,
float size = 11,
bool underline = false,
bool strike = false,
string fontName = null)
{
var richText = richTextCollection.Add(text);
richText.Color = color ?? Color.Black;
richText.Bold = bold;
richText.Strike = strike;
richText.Italic = italic;
richText.Size = size;
if (fontName != null)
{
richText.FontName = fontName;
}
richText.UnderLine = underline;
return(richText);
}
/// <summary>
/// Visits a single HTML node and updates the supplied rich text collection accordingly
/// </summary>
private void VisitHtmlNode(ExcelRichTextCollection richText, HtmlNode node, List <Action <ExcelRichText> > pendingActions)
{
switch (node.NodeType)
{
case HtmlNodeType.Document:
case HtmlNodeType.Element:
var toAdd = GetModifiersForNode(node).ToList();
pendingActions.AddRange(toAdd.Select(i => (Action <ExcelRichText>)i.Apply));
foreach (var child in node.ChildNodes)
{
VisitHtmlNode(richText, child, pendingActions);
}
pendingActions.AddRange(toAdd.Select(i => (Action <ExcelRichText>)i.Undo));
switch (node.Name.ToUpperInvariant())
{
case "P":
if (node.NextSibling != null && node.NextSibling.Name.ToUpperInvariant() == "P")
{
AddRichText(richText, Environment.NewLine, pendingActions);
}
break;
}
break;
case HtmlNodeType.Text:
AddRichText(richText, node.InnerText, pendingActions);
break;
}
}
internal ExcelComment(XmlNamespaceManager ns, XmlNode commentTopNode, ExcelRangeBase cell)
: base(null, cell, cell.Worksheet.VmlDrawings.NameSpaceManager)
{
//_commentHelper = new XmlHelper(ns, commentTopNode);
_commentHelper = XmlHelperFactory.Create(ns, commentTopNode);
var textElem = commentTopNode.SelectSingleNode("d:text", ns);
if (textElem == null)
{
textElem = commentTopNode.OwnerDocument.CreateElement("text", ExcelPackage.schemaMain);
commentTopNode.AppendChild(textElem);
}
if (!cell.Worksheet._vmlDrawings.ContainsKey(cell.Start.Row, cell.Start.Column))
{
cell.Worksheet._vmlDrawings.AddComment(cell);
}
TopNode = cell.Worksheet.VmlDrawings[cell.Start.Row, cell.Start.Column].TopNode;
RichText = new ExcelRichTextCollection(ns, textElem);
var tNode = textElem.SelectSingleNode("d:t", ns);
if (tNode != null)
{
_text = tNode.InnerText;
}
}
/// <summary>
/// Creates a new RichText node with the supplied text and applies all the pending actions
/// </summary>
private static void AddRichText(ExcelRichTextCollection richText, string text, List <Action <ExcelRichText> > pendingActions)
{
var rt = richText.Add(text);
foreach (var action in pendingActions)
{
action(rt);
}
pendingActions.Clear();
}
private Core.CellStore.ExcelValue GetCellStoreValue(int row, int col)
{
var v = _worksheet.GetCoreValueInner(row, col);
if (_worksheet._flags.GetFlagValue(row, col, CellFlags.RichText))
{
var xml = new XmlDocument();
XmlHelper.LoadXmlSafe(xml, "<d:si xmlns:d=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" >" + v._value.ToString() + "</d:si>", Encoding.UTF8);
var rt = new ExcelRichTextCollection(_worksheet.NameSpaceManager, xml.SelectSingleNode("d:si", _worksheet.NameSpaceManager), this);
v._value = rt.Text;
}
return(v);
}
internal ExcelComment(XmlNamespaceManager ns, XmlNode commentTopNode, ExcelRangeBase cell)
: base(null, cell, cell.Worksheet.VmlDrawings.NameSpaceManager)
{
_commentHelper = new XmlHelper(ns, commentTopNode);
var textElem=commentTopNode.SelectSingleNode("d:text", ns);
if (textElem == null)
{
textElem = commentTopNode.OwnerDocument.CreateElement("text", ExcelPackage.schemaMain);
commentTopNode.AppendChild(textElem);
}
if (!cell.Worksheet.VmlDrawings.ContainsKey(ExcelAddress.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column)))
{
cell.Worksheet.VmlDrawings.Add(cell);
}
TopNode = cell.Worksheet.VmlDrawings[ExcelCellBase.GetCellID(cell.Worksheet.SheetID, cell.Start.Row, cell.Start.Column)].TopNode;
RichText = new ExcelRichTextCollection(ns,textElem);
}
public byte[] gate_pass(string session, int gate_pass_no, string image)
{
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
string query = @"SELECT
a.session,
gate_pass_no,
b.sr_number,
date_time,
std_relation,
escorter_name,
escorter_address,
reason,
CONCAT(IFNULL(std_first_name, ''),
' ',
IFNULL(std_last_name, '')) std_name,
CONCAT(IFNULL(class_name, ''),
' ',
IFNULL(section_name, '')) std_class,
std_father_name,
COALESCE(std_contact, std_contact1, std_contact2) contact_no,
g.pickup_point
FROM
std_halfday_log a,
sr_register b,
mst_std_class c,
mst_std_section d,
mst_class e,
mst_section f,
mst_transport g
WHERE
a.session = @session
AND a.session = c.session
AND c.session = d.session
AND d.session = e.session
AND e.session = f.session
AND f.session = g.session
AND a.sr_number = b.sr_number
AND b.sr_number = c.sr_num
AND c.sr_num = d.sr_num
AND c.class_id = e.class_id
AND d.section_id = f.section_id
AND g.pickup_id = b.std_pickup_id
AND a.gate_pass_no = @gate_pass_no";
var result = con.Query <ExcelGatePass>(query, new { session = session, gate_pass_no = gate_pass_no }).SingleOrDefault();
string gatePassNo = gate_pass_no.ToString().PadLeft(4, '0');
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Gate Pass");
ws.PrinterSettings.TopMargin = 1m / 2.54m;
ws.PrinterSettings.BottomMargin = 1m / 2.54m;
ws.PrinterSettings.LeftMargin = 1.8m / 2.54m;
ws.PrinterSettings.RightMargin = 1.8m / 2.54m;
ws.PrinterSettings.HeaderMargin = 0.0m / 2.54m;
ws.PrinterSettings.FooterMargin = 0.0m / 2.54m;
ws.PrinterSettings.HorizontalCentered = false;
ws.Column(1).Width = ExcelTc_form.GetTrueColumnWidth(13.18);
ws.Column(2).Width = ExcelTc_form.GetTrueColumnWidth(11.09);
ws.Column(3).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(4).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(5).Width = ExcelTc_form.GetTrueColumnWidth(9.18);
ws.Column(6).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(7).Width = ExcelTc_form.GetTrueColumnWidth(8.09);
ws.Column(8).Width = ExcelTc_form.GetTrueColumnWidth(14.55);
for (int i = 2; i <= 15; i++)
{
ws.Row(i).Height = 17;
}
ws.Row(1).Height = 108.8;
using (ExcelRange Rng = ws.Cells["A1:H1"])
{
Rng.Merge = true;
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
Rng.Style.WrapText = true;
ExcelRichTextCollection RichTxtCollection = Rng.RichText;
ExcelRichText RichText = RichTxtCollection.Add("Gate Pass\n");
RichText.Size = 14;
RichText = RichTxtCollection.Add(SchoolName + "\n");
RichText.Size = 28;
RichText.Bold = true;
RichText = RichTxtCollection.Add(Address + "\n");
RichText.Size = 11;
RichText = RichTxtCollection.Add(Affiliation);
RichText.Size = 11;
RichText.Bold = false;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
if (image == "../../images/person.png")
{
using (System.Drawing.Image img = System.Drawing.Image.FromFile(HttpContext.Current.Server.MapPath("/images/person.png")))
{
var excelImage = ws.Drawings.AddPicture("Picture", img);
//add the image to row 20, column E
excelImage.SetPosition(3, 0, 4, 0);
excelImage.SetSize(233, 134);
}
}
else
{
var img = Base64StringToBitmap(image.Remove(0, 22));
var excelImage = ws.Drawings.AddPicture("Picture", img);
//add the image to row 20, column E
excelImage.SetPosition(3, 0, 4, 0);
excelImage.SetSize(233, 134);
}
ws.Cells["A2"].Value = "Gate Pass No: ";
ws.Cells["A2"].Style.Font.Name = "Calibri";
ws.Cells["A2"].Style.Font.Size = 11;
ws.Cells["A2"].Style.Font.Bold = true;
ws.Cells["A2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B2"].Value = session + "/" + gatePassNo.ToString();;
ws.Cells["B2"].Style.Font.Name = "Calibri";
ws.Cells["B2"].Style.Font.Size = 11;
ws.Cells["B2"].Style.Font.UnderLine = true;
ws.Cells["B2"].Style.Font.Italic = true;
ws.Cells["B2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["D2"].Value = "Adm No: ";
ws.Cells["D2"].Style.Font.Name = "Calibri";
ws.Cells["D2"].Style.Font.Size = 11;
ws.Cells["D2"].Style.Font.Bold = true;
ws.Cells["D2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["D2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["E2"].Value = result.sr_number.ToString();
ws.Cells["E2"].Style.Font.Name = "Calibri";
ws.Cells["E2"].Style.Font.Size = 11;
ws.Cells["E2"].Style.Font.UnderLine = true;
ws.Cells["E2"].Style.Font.Italic = true;
ws.Cells["E2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["E2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["G2"].Value = "Date: ";
ws.Cells["G2"].Style.Font.Name = "Calibri";
ws.Cells["G2"].Style.Font.Size = 11;
ws.Cells["G2"].Style.Font.Bold = true;
ws.Cells["G2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["G2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["H2"].Value = result.date_time.ToString("dd/MM/yyyy hh:mm");
ws.Cells["H2"].Style.Font.Name = "Calibri";
ws.Cells["H2"].Style.Font.Size = 11;
ws.Cells["H2"].Style.Font.UnderLine = true;
ws.Cells["H2"].Style.Font.Italic = true;
ws.Cells["H2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["H2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A4"].Value = "Name ";
ws.Cells["A4"].Style.Font.Name = "Calibri";
ws.Cells["A4"].Style.Font.Size = 11;
ws.Cells["A4"].Style.Font.Bold = true;
ws.Cells["A4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A4"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B4"].Value = result.std_name;
ws.Cells["B4"].Style.Font.Name = "Calibri";
ws.Cells["B4"].Style.Font.Size = 11;
ws.Cells["B4"].Style.Font.UnderLine = true;
ws.Cells["B4"].Style.Font.Italic = true;
ws.Cells["B4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B4"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A5"].Value = "Class ";
ws.Cells["A5"].Style.Font.Name = "Calibri";
ws.Cells["A5"].Style.Font.Size = 11;
ws.Cells["A5"].Style.Font.Bold = true;
ws.Cells["A5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A5"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B5"].Value = result.std_class;
ws.Cells["B5"].Style.Font.Name = "Calibri";
ws.Cells["B5"].Style.Font.Size = 11;
ws.Cells["B5"].Style.Font.UnderLine = true;
ws.Cells["B5"].Style.Font.Italic = true;
ws.Cells["B5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B5"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A6"].Value = "Father Name ";
ws.Cells["A6"].Style.Font.Name = "Calibri";
ws.Cells["A6"].Style.Font.Size = 11;
ws.Cells["A6"].Style.Font.Bold = true;
ws.Cells["A6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B6"].Value = result.std_father_name;
ws.Cells["B6"].Style.Font.Name = "Calibri";
ws.Cells["B6"].Style.Font.Size = 11;
ws.Cells["B6"].Style.Font.UnderLine = true;
ws.Cells["B6"].Style.Font.Italic = true;
ws.Cells["B6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B6"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A7"].Value = "Contact No ";
ws.Cells["A7"].Style.Font.Name = "Calibri";
ws.Cells["A7"].Style.Font.Size = 11;
ws.Cells["A7"].Style.Font.Bold = true;
ws.Cells["A7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B7"].Value = result.contact_no;
ws.Cells["B7"].Style.Font.Name = "Calibri";
ws.Cells["B7"].Style.Font.Size = 11;
ws.Cells["B7"].Style.Font.UnderLine = true;
ws.Cells["B7"].Style.Font.Italic = true;
ws.Cells["B7"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B7"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A8"].Value = "Person Name ";
ws.Cells["A8"].Style.Font.Name = "Calibri";
ws.Cells["A8"].Style.Font.Size = 11;
ws.Cells["A8"].Style.Font.Bold = true;
ws.Cells["A8"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A8"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B8"].Value = result.escorter_name;
ws.Cells["B8"].Style.Font.Name = "Calibri";
ws.Cells["B8"].Style.Font.Size = 11;
ws.Cells["B8"].Style.Font.UnderLine = true;
ws.Cells["B8"].Style.Font.Italic = true;
ws.Cells["B8"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B8"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A9"].Value = "Relationship ";
ws.Cells["A9"].Style.Font.Name = "Calibri";
ws.Cells["A9"].Style.Font.Size = 11;
ws.Cells["A9"].Style.Font.Bold = true;
ws.Cells["A9"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A9"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B9"].Value = result.std_relation;
ws.Cells["B9"].Style.Font.Name = "Calibri";
ws.Cells["B9"].Style.Font.Size = 11;
ws.Cells["B9"].Style.Font.UnderLine = true;
ws.Cells["B9"].Style.Font.Italic = true;
ws.Cells["B9"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B9"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A10"].Value = "Person Address ";
ws.Cells["A10"].Style.Font.Name = "Calibri";
ws.Cells["A10"].Style.Font.Size = 11;
ws.Cells["A10"].Style.Font.Bold = true;
ws.Cells["A10"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A10"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B10"].Value = result.escorter_address;
ws.Cells["B10"].Style.Font.Name = "Calibri";
ws.Cells["B10"].Style.Font.Size = 11;
ws.Cells["B10"].Style.Font.UnderLine = true;
ws.Cells["B10"].Style.Font.Italic = true;
ws.Cells["B10"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B10"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A11"].Value = "Reason ";
ws.Cells["A11"].Style.Font.Name = "Calibri";
ws.Cells["A11"].Style.Font.Size = 11;
ws.Cells["A11"].Style.Font.Bold = true;
ws.Cells["A11"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A11"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B11"].Value = result.reason;
ws.Cells["B11"].Style.Font.Name = "Calibri";
ws.Cells["B11"].Style.Font.Size = 11;
ws.Cells["B11"].Style.Font.UnderLine = true;
ws.Cells["B11"].Style.Font.Italic = true;
ws.Cells["B11"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B11"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A12"].Value = "Transport ";
ws.Cells["A12"].Style.Font.Name = "Calibri";
ws.Cells["A12"].Style.Font.Size = 11;
ws.Cells["A12"].Style.Font.Bold = true;
ws.Cells["A12"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A12"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["B12"].Value = result.pickup_point;
ws.Cells["B12"].Style.Font.Name = "Calibri";
ws.Cells["B12"].Style.Font.Size = 11;
ws.Cells["B12"].Style.Font.UnderLine = true;
ws.Cells["B12"].Style.Font.Italic = true;
ws.Cells["B12"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["B12"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["A16"].Value = "Parents Sign ";
ws.Cells["A16"].Style.Font.Name = "Calibri";
ws.Cells["A16"].Style.Font.Size = 11;
ws.Cells["A16"].Style.Font.Bold = true;
ws.Cells["A16"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["A16"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["D16"].Value = "Auth Sign. ";
ws.Cells["D16"].Style.Font.Name = "Calibri";
ws.Cells["D16"].Style.Font.Size = 11;
ws.Cells["D16"].Style.Font.Bold = true;
ws.Cells["D16"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["D16"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells["H16"].Value = "Incharge Sign. ";
ws.Cells["H16"].Style.Font.Name = "Calibri";
ws.Cells["H16"].Style.Font.Size = 11;
ws.Cells["H16"].Style.Font.Bold = true;
ws.Cells["H16"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["H16"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
return(pck.GetAsByteArray());
}
}
catch (Exception ex)
{
throw ex;
}
}
internal ExcelRichText(XmlNamespaceManager ns, XmlNode topNode, ExcelRichTextCollection collection) :
base(ns, topNode)
{
SchemaNodeOrder=new string[] {"rPr", "t", "b", "i","strike", "u", "vertAlign" , "sz", "color", "rFont", "family", "scheme", "charset"};
_collection = collection;
}
private void MachineMtnReportDatas_GetExcel_Daily(MachineMtnReportDataSearch machineMtnReportDataSearch, IEnumerable <MachineMtnReportData> data, ExcelPackage ep, List <BuildArgs> buildArgs)
{
//init value.
//fix cứng số cột: số thự tự tên nước, saleperson, Year và danh sách 12 tháng (orderMonthFromIndex --> orderMonthToIndex)
string prefixTotalColumn = "_*_";
int startRowIdx = 8; // title table index.
int machinePartName_ColIdx = 2;
int contentMtn_ColIdx = 3;
int toolMtn_ColIdx = 4;
int methodMtn_ColIdx = 5;
int standard_ColIdx = 6;
int runningFrom_ColIdx = 7;
int DayAndShiftOrder_FromColIdx = runningFrom_ColIdx;
int DayAndShiftOrder_ToColIdx = 0;
int totalColumns_ColIdx = 0; // tổng số cột.
int rowIndex = startRowIdx;
//total Result index.
int totalResult_RowIdx = 0;
int OperatorRowIdx;
int CheckerRowIdx;
// workbook
var wb = ep.Workbook;
// new worksheet
var ws = wb.Worksheets.Add("Sheet01");
#region Title.
ws.Cells[2, 7].Value = ("Bảng kiểm tra bảo trì máy/Machine maintenance checksheet").ToUpper();
ws.Cells[2, 7].Style.Font.Bold = true;
ws.Cells[2, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[2, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = 2,
FromColumn = 7,
ToRow = 2,
ToColumn = 14
});
using (ExcelRange Rng = ws.Cells[3, 7]) //machineid.
{
Rng.Style.Font.Size = 11;
ExcelRichTextCollection RichTxtCollection = Rng.RichText;
ExcelRichText RichText = RichTxtCollection.Add("MachineID: ");
RichText.Bold = false;
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.MachineID);
RichText.Bold = true;
RichText = RichTxtCollection.Add(" Machine Name: ");
RichText.Bold = false;
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.MachineName);
RichText.Bold = true;
}
ws.Cells[3, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[3, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = 3,
FromColumn = 7,
ToRow = 3,
ToColumn = 14
});
using (ExcelRange Rng = ws.Cells[4, 7]) //FromDate ToDate
{
Rng.Style.Font.Size = 11;
ExcelRichTextCollection RichTxtCollection = Rng.RichText;
ExcelRichText RichText = RichTxtCollection.Add("Từ ngày/FromDate: ");
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.FromDate.ToString("MM/dd/yyyy"));
RichText.Bold = true;
RichText = RichTxtCollection.Add(" Đến ngày/ToDate: ");
RichText.Bold = false;
RichText = RichTxtCollection.Add(machineMtnReportDataSearch.ToDate.ToString("MM/dd/yyyy"));
RichText.Bold = true;
}
ws.Cells[4, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[4, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = 4,
FromColumn = 7,
ToRow = 4,
ToColumn = 14
});
#endregion
#region Header.
// Header Value.
ws.Cells[rowIndex, machinePartName_ColIdx].Value = "Part Name";
ws.Cells[rowIndex, contentMtn_ColIdx].Value = "Content";
ws.Cells[rowIndex, toolMtn_ColIdx].Value = "Tool";
ws.Cells[rowIndex, methodMtn_ColIdx].Value = "Method";
ws.Cells[rowIndex, standard_ColIdx].Value = "Standard";
//-- MaintenanceDate headers
var dict_DayOrder = new Dictionary <string, int>();
int countDayAndShiftOrderColumn = 0;
var dict_DayAndShiftOrder = new Dictionary <string, int>();
for (var day = machineMtnReportDataSearch.FromDate; day <= machineMtnReportDataSearch.ToDate; day = day.AddDays(1))
{
int oneDay_FullShift_FromColIndex = DayAndShiftOrder_FromColIdx + countDayAndShiftOrderColumn;
for (int i = 0; i <= 2; i++) // i = order number column in excel, i++ = shift number.
{
//date header.
ws.Cells[rowIndex, (countDayAndShiftOrderColumn) + runningFrom_ColIdx].Value = day.ToString("MM/dd", enUS);
//shift header.
ws.Cells[rowIndex + 1, countDayAndShiftOrderColumn + runningFrom_ColIdx].Value = (i + 1).ToString();
dict_DayOrder.Add(day.ToString("MM/dd", enUS) + "_" + (i + 1).ToString(), countDayAndShiftOrderColumn); // sample "15/3_1" = date is 15/3 and shift = 1
dict_DayAndShiftOrder.Add(day.ToString("MM/dd", enUS) + (i + 1).ToString(), countDayAndShiftOrderColumn);
countDayAndShiftOrderColumn++;
}
int oneDay_FullShift_ToColIndex = oneDay_FullShift_FromColIndex + 2;
//merge cell MaintenanceDate.
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = rowIndex,
FromColumn = oneDay_FullShift_FromColIndex,
ToRow = rowIndex,
ToColumn = oneDay_FullShift_ToColIndex
});
//intDayOrder++;
}
DayAndShiftOrder_ToColIdx = DayAndShiftOrder_FromColIdx + countDayAndShiftOrderColumn - 1;
totalColumns_ColIdx = DayAndShiftOrder_ToColIdx;
// Headers style
using (var cell = ws.Cells[rowIndex, machinePartName_ColIdx, rowIndex + 1, totalColumns_ColIdx])
{
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 11;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
rowIndex++;
rowIndex++;
#endregion
//checking data.
if (data == null)
{
return;
}
#region Add TotalResult.
var maintenanceDateGroups = data.GroupBy(d => d.MaintenanceDate);
foreach (var maintenanceDateGroup in maintenanceDateGroups)
{
MachineMtnReportData tMachineMtn = maintenanceDateGroup.FirstOrDefault();
MachineMtnReportData TotalResult_Row = new MachineMtnReportData();
TotalResult_Row.MaintenanceDate = tMachineMtn.MaintenanceDate;
TotalResult_Row.Shift = tMachineMtn.Shift;
TotalResult_Row.FrequencyID = tMachineMtn.FrequencyID;
TotalResult_Row.MachinePartID = int.MaxValue;
TotalResult_Row.MachinePartName = "zzzzz01" + prefixTotalColumn + "Total Result";
TotalResult_Row.MtnDetailResult = tMachineMtn.TotalResult;
TotalResult_Row.MtnDetailResultContents = tMachineMtn.TotalResultContents;
MachineMtnReportData Operator_Row = new MachineMtnReportData();
Operator_Row.MaintenanceDate = tMachineMtn.MaintenanceDate;
Operator_Row.Shift = tMachineMtn.Shift;
Operator_Row.MachinePartID = int.MaxValue;
Operator_Row.MachinePartName = "zzzzz02" + prefixTotalColumn + "Operator";
Operator_Row.MtnDetailResult = tMachineMtn.OperatorName;
MachineMtnReportData Checker_Row = new MachineMtnReportData();
Checker_Row.MaintenanceDate = tMachineMtn.MaintenanceDate;
Checker_Row.Shift = tMachineMtn.Shift;
Checker_Row.MachinePartID = int.MaxValue;
Checker_Row.MachinePartName = "zzzzz03" + prefixTotalColumn + "Checker";
Checker_Row.MtnDetailResult = tMachineMtn.CheckerName;
IEnumerable <MachineMtnReportData> x = new MachineMtnReportData[] { TotalResult_Row, Operator_Row, Checker_Row };
data = data.Concat(x);
}
#endregion
//***************** Data *************
#region MachinePartName.
var machinePartNameGroups = data.GroupBy(d => new { d.MachinePartID, d.MachinePartName }).OrderBy(g => g.Key.MachinePartName);
#endregion
//var Start_RunningFromRowIndex = rowIndex;
foreach (var machinePartNameGroup in machinePartNameGroups)
{
int machinePartNameGroup_FromRowIndex = rowIndex;
#region ContentMtn.
int contentMtnFromRowIndex = rowIndex;
var contentMtnGroups = machinePartNameGroup.GroupBy(d => d.ContentMtn).OrderBy(g => g.Key);
#endregion
foreach (var contentMtnGroup in contentMtnGroups)
{
int contentMtnGroup_FromRowIndex = rowIndex;
#region ToolMtn.
int toolMtnFromRowIndex = rowIndex;
var toolMtnGroups = contentMtnGroup.GroupBy(d => d.ToolMtn).OrderBy(g => g.Key);
#endregion
foreach (var toolMtnGroup in toolMtnGroups)
{
int toolMtnGroup_FromRowIndex = rowIndex;
#region MethodMtn.
int methodMtnFromRowIndex = rowIndex;
var methodMtnGroups = toolMtnGroup.GroupBy(d => d.MethodMtn).OrderBy(g => g.Key);
#endregion
foreach (var methodMtnGroup in methodMtnGroups)
{
#region Standard.
int standard_FromRowIndex = rowIndex;
var standardGroups = methodMtnGroup.GroupBy(d => d.Standard).OrderBy(g => g.Key);
foreach (var standardGroup in standardGroups)
{
//insert MachinePartName, Tool, Content, ......
string _machinePartName = machinePartNameGroup.Key.MachinePartName;
if (_machinePartName.Contains(prefixTotalColumn)) //totalResult.
{
if (_machinePartName.Contains("zzzzz01")) //get totalResult Row index.
{
totalResult_RowIdx = rowIndex;
}
_machinePartName = _machinePartName.Remove(0, _machinePartName.IndexOf(prefixTotalColumn) + prefixTotalColumn.Length);
}
ws.Cells[rowIndex, machinePartName_ColIdx].Value = _machinePartName;
ws.Cells[rowIndex, contentMtn_ColIdx].Value = contentMtnGroup.Key;
ws.Cells[rowIndex, toolMtn_ColIdx].Value = toolMtnGroup.Key;
ws.Cells[rowIndex, methodMtn_ColIdx].Value = methodMtnGroup.Key;
ws.Cells[rowIndex, standard_ColIdx].Value = standardGroup.Key;
#region Maintenance Days.
int maintenanceDayFromRowIndex = rowIndex;
var maintenanceDayGroups = standardGroup.GroupBy(d => d.MaintenanceDate.ToString("MM/dd")).OrderBy(g => g.Key);
#endregion
int orderDay = 1; // *************** checking aaaa : orderday: phai lay theo thu tu cua methodMtnGroup
ws.Cells[rowIndex, DayAndShiftOrder_FromColIdx, rowIndex, DayAndShiftOrder_ToColIdx].Value = "";
foreach (var maintenanceDayGroup in maintenanceDayGroups)
{
var mtnDayShiftGroups = maintenanceDayGroup.GroupBy(d => d.Shift);
#region one day.
foreach (var mtnDayShiftGroup in mtnDayShiftGroups)
{
#region one day and one shift.
// ******************* Gán cụ thể vào Excel.
DateTime currentMtnDate = mtnDayShiftGroup.FirstOrDefault().MaintenanceDate; //.ToString("MM/dd", enUS);
MachineMtnReportData currentMtnReportData = mtnDayShiftGroup.FirstOrDefault();
int currentOrderDayShift = dict_DayOrder[currentMtnDate.ToString("MM/dd", enUS) + "_" + currentMtnReportData.Shift];
var currentCells = ws.Cells[rowIndex, currentOrderDayShift - 1 + runningFrom_ColIdx];
//set value for cell.
currentCells.Value = currentMtnReportData.MtnDetailResult;
//set color for OK, NG.
if (currentMtnReportData.MtnDetailResult == SMCommon.MachineMtnResult_OK)
{
currentCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
currentCells.Style.Fill.BackgroundColor.SetColor(SMCommon.ReportMachineMtn_Result_OK_Color);
}
else if (currentMtnReportData.MtnDetailResult == SMCommon.MachineMtnResult_NG)
{
currentCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
currentCells.Style.Fill.BackgroundColor.SetColor(SMCommon.ReportMachineMtn_Result_NG_Color);
}
orderDay++;
#endregion
}
#endregion
}
rowIndex++;
//Borders.
}
int standard_ToRowIndex = rowIndex - 1;
#endregion
}
int toolMtnGroup_ToRowIndex = rowIndex - 1;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = toolMtnGroup_FromRowIndex,
FromColumn = toolMtn_ColIdx,
ToRow = rowIndex - 1,
ToColumn = toolMtn_ColIdx
});
}
int contentMtnGroup_ToRowIndex = rowIndex - 1;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = contentMtnGroup_FromRowIndex,
FromColumn = contentMtn_ColIdx,
ToRow = rowIndex - 1,
ToColumn = contentMtn_ColIdx
});
}
int machinePartNameGroup_ToRowIndex = rowIndex - 1;
// merge cells
buildArgs.Add(new BuildArgs()
{
Action = BuildAction.Merge,
Worksheet = ws,
FromRow = machinePartNameGroup_FromRowIndex,
FromColumn = machinePartName_ColIdx,
ToRow = rowIndex - 1,
ToColumn = machinePartName_ColIdx
});
}
#region Footer
#endregion
#region Border Style.
int toRowIndex = rowIndex - 1;
// cells borders
ws.View.ShowGridLines = false;
foreach (var cell in ws.Cells[startRowIdx - 1, machinePartName_ColIdx, toRowIndex, totalColumns_ColIdx])
{
cell.Style.Border.Top.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Top.Color.SetColor(Color.Black);
cell.Style.Border.Bottom.Color.SetColor(Color.Black);
cell.Style.Border.Left.Color.SetColor(Color.Black);
cell.Style.Border.Right.Color.SetColor(Color.Black);
}
#endregion
#region Manipulate Cells (merge).
// merge cells
foreach (var args in buildArgs.Where(ba => ba.Action == BuildAction.Merge))
{
using (var cells = args.Worksheet.Cells[args.FromRow, args.FromColumn, args.ToRow, args.ToColumn])
{
cells.Merge = true;
}
}
// Vertical Alignment body table.
ws.Cells[startRowIdx - 1, machinePartName_ColIdx, toRowIndex, totalColumns_ColIdx].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// new line Cell.
foreach (var cell in ws.Cells[startRowIdx, machinePartName_ColIdx, toRowIndex, standard_ColIdx])
{
if (cell.Value != null)
{
cell.Value = cell.Value.ToString().Replace("/", Environment.NewLine);
}
}
// wordwrap entire table.
using (var AllCellsTables = ws.Cells[startRowIdx - 1, machinePartName_ColIdx, toRowIndex, totalColumns_ColIdx])
{
AllCellsTables.Style.WrapText = true;
//AllCellsTables.AutoFitColumns();
}
//set height Row.
for (int intRow = startRowIdx; intRow < toRowIndex; intRow++)
{
if (ws.Cells[intRow, machinePartName_ColIdx] != null && ws.Cells[intRow, machinePartName_ColIdx].Value != null)
{
ws.Row(intRow).Height = SMCommon.MeasureTextHeight(ws.Cells[intRow, machinePartName_ColIdx].Value.ToString(), ws.Cells[intRow, machinePartName_ColIdx].Style.Font, 1);
}
}
//set width column.
ws.Column(machinePartName_ColIdx).Width = 25;
ws.Column(contentMtn_ColIdx).Width = 25;
ws.Column(toolMtn_ColIdx).Width = 25;
ws.Column(methodMtn_ColIdx).Width = 20;
ws.Column(standard_ColIdx).Width = 20;
#endregion
}
public void SetValueInCellExport(ExcelWorksheet ew1, object value, UI_SELECT_GRID_SETTINGSResult setting, bool?is_date_time)
{
bool is_write = false;
if (setting == null)
{
is_write = true;
}
else if ((bool)setting.global_visible && (bool)setting.is_visible)
{
is_write = true;
}
if (is_write)
{
if (value != null && value is string | value is DateTime)
{
if (value is DateTime)
{
if (is_date_time == true)
{
value = DateTime.Parse(Convert.ToString(value));
}
else
{
value = DateTime.Parse(Convert.ToString(value)).ToShortDateString();
}
}
string[] arrayHTMLValue = Convert.ToString(value).Split(new string[] { "<br>" }, StringSplitOptions.None);
if (arrayHTMLValue != null && arrayHTMLValue.Length != 0)
{
ExcelRichTextCollection rtfCollection = ew1.Cells[rowCount, columnCount].RichText;
ExcelRichText ert = rtfCollection.Add(CommonMethods.HtmlToText(arrayHTMLValue[0]));
if (arrayHTMLValue[0].IndexOf("orange") > 0)
{
ert.Color = System.Drawing.Color.Orange;
}
if (arrayHTMLValue.Length > 1)
{
for (int f = 1; f < arrayHTMLValue.Length; f++)
{
rtfCollection.Add(CommonMethods.HtmlToText("\n"));
ExcelRichText ert1 = rtfCollection.Add(CommonMethods.HtmlToText(arrayHTMLValue[f]));
if (arrayHTMLValue[f].IndexOf("orange") > 0)
{
ert1.Color = System.Drawing.Color.Orange;
}
}
}
}
else
{
ew1.Cells[rowCount, columnCount].Value = CommonMethods.HtmlToText(Convert.ToString(value));
}
ew1.Cells[rowCount, columnCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
if (value != null && value is double | value is int | value is float | value is decimal)
{
ew1.Cells[rowCount, columnCount].Value = double.Parse(CommonMethods.HtmlToText(CommonMethods.ObjectToString(value)).Replace(".", ","));
ew1.Cells[rowCount, columnCount].Style.Numberformat.Format = CommaFind(CommonMethods.HtmlToText(CommonMethods.ObjectToString(value)).Replace(".", ","));
}
//ew1.Cells[rowCount, columnCount].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//ew1.Cells[rowCount, columnCount].Style.Border.Left.Style = ExcelBorderStyle.Thin;
//ew1.Cells[rowCount, columnCount].Style.Border.Top.Style = ExcelBorderStyle.Thin;
//ew1.Cells[rowCount, columnCount].Style.Border.Right.Style = ExcelBorderStyle.Thin;
//ew1.Cells[rowCount, columnCount].Style.WrapText = true;
//ew1.Cells[rowCount, columnCount].Style.Font.Name = "Calibri";
//ew1.Cells[rowCount, columnCount].Style.Font.Size = 8;
if (value is string)
{
ew1.Cells[rowCount, columnCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
}
if (value is double | value is int | value is float)
{
ew1.Cells[rowCount, columnCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
ew1.Cells[rowCount, columnCount].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
columnCount++;
}
}
