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> /// 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(); }
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; } }
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++; } }