private void SS_LoadSheet(Worksheet sheet, DataRow r, int iFilterCompany = 0) { // Disable cell editing sheet.BeforeCellEdit += (s, e) => e.IsCancelled = true; // Set up cell selection sheet.CellMouseDown += sheet_CellMouseDown; sheet.CellMouseEnter += sheet_CellMouseEnter; sheet.CellMouseLeave += sheet_CellMouseLeave; sheet.SelectionRangeChanged += sheet_SelectionRangeChanged; // Set column headers int count = 0; DateTime date_from = DateTime.Parse(r["Start"].ToString()); DateTime date_to = DateTime.Parse(r["Finish"].ToString()); List<DateTime> dates = new List<DateTime>(); for (DateTime dt = date_from; dt <= date_to; dt = dt.AddDays(1)) { dates.Add(dt); count++; } sheet.SetCols(count + 1); sheet.SetColumnsWidth(1, count, 92); sheet.SetColumnsWidth(0, 1, 225); count = 1; foreach (DateTime date in dates) { if (!IsWeekend(date)) { sheet[0, count] = date.ToString("ddd"); sheet[1, count] = Convert.ToString(date.ToShortDateString()); } else { sheet[0, count] = "S"; sheet[1, count] = Convert.ToString(date.Day); sheet.SetColumnsWidth(count, 1, 30); } sheet.Cells[0, count].Style.HAlign = ReoGridHorAlign.Center; sheet.Cells[0, count].Style.VAlign = ReoGridVerAlign.Middle; sheet.Cells[0, count].Style.FontName = "Arial"; sheet.Cells[0, count].Style.FontSize = 8; sheet.Cells[0, count].Style.Bold = true; sheet.Cells[1, count].Style.HAlign = ReoGridHorAlign.Center; sheet.Cells[1, count].Style.VAlign = ReoGridVerAlign.Middle; sheet.Cells[1, count].Style.FontName = "Arial"; sheet.Cells[1, count].Style.FontSize = 8; sheet.Cells[1, count].Style.Bold = true; count++; } int iColumnCount = count + 1; sheet.RowHeaders[0].Height = 30; sheet[0, 0] = "RAW Zone Planner"; sheet.Cells[0, 0].Style.VAlign = ReoGridVerAlign.Middle; sheet.Cells[0, 0].Style.FontName = "Arial"; sheet.Cells[0, 0].Style.FontSize = 8; sheet.Cells[0, 0].Style.Bold = true; sheet.RowHeaders[1].Height = 30; sheet[1, 0] = r["Name"].ToString(); sheet.Cells[1, 0].Style.VAlign = ReoGridVerAlign.Middle; sheet.Cells[1, 0].Style.FontName = "Arial"; sheet.Cells[1, 0].Style.FontSize = 8; sheet.Cells[1, 0].Style.Bold = true; // Set row headers DataSet rooms = Program.DB.SelectAll("SELECT ID,Name,IsZone FROM Rooms WHERE ID IN (" + r["Rooms"].ToString() + ");"); Dictionary<string, string> rows = new Dictionary<string, string>(); int iZ = 0, iR = 0; foreach (DataRow row in rooms.Tables[0].Rows) { if (row["IsZone"] != DBNull.Value && row["IsZone"].Equals("Y")) { rows.Add("z" + row["ID"].ToString(), row["Name"].ToString().Replace("|", " - ")); iZ++; continue; } rows.Add("r" + row["ID"].ToString(), row["Name"].ToString()); iR++; } sheet.SetRows(rows.Count + iZ + 2); count = 2; foreach (KeyValuePair<string, string> entry in rows) { if (entry.Key.Contains("z")) { sheet.SetRowsHeight(count, 1, 15); count++; ReoGridRange rgr = new ReoGridRange(count, 0, 1, iColumnCount); sheet.SetRangeStyles(rgr, new WorksheetRangeStyle { Flag = PlainStyleFlag.BackColor | PlainStyleFlag.FontStyleBold, BackColor = Color.LightSteelBlue, Bold = true }); } else { sheet.Cells[count, 0].Style.Italic = true; } sheet[count, 0] = entry.Value; sheet.SetRowsHeight(count, 1, 23); sheet.Cells[count, 0].Tag = entry.Key.Replace("z", "").Replace("r", ""); sheet.Cells[count, 0].Style.VAlign = ReoGridVerAlign.Middle; sheet.Cells[count, 0].Style.HAlign = ReoGridHorAlign.Left; sheet.Cells[count, 0].Style.FontName = "Arial"; sheet.Cells[count, 0].Style.FontSize = 8; string sDates = ""; foreach (DateTime date in dates) { if (!IsWeekend(date)) { sDates += sDates.Length > 0 ? " OR " : ""; sDates += "BDates LIKE '" + date.ToString("dd/MM/yyyy") + "%'"; } } if (sDates.Length > 0) { DataSet d = Program.DB.SelectAll("SELECT ID,BName,BDates,BTimes,BRoom,BComments,Confirmed,Completed FROM Jobs WHERE " + sDates + (iFilterCompany > 0 ? " AND BCompany=" + iFilterCompany : "") + ";"); if (d.Tables.Count > 0 && d.Tables[0].Rows.Count > 0) { foreach (DataRow dr in d.Tables[0].Rows) { int iColumn = 1; string[] sRooms = new string[] { }; if (dr["BRoom"] != DBNull.Value && dr["BRoom"].ToString().Length > 0) { sRooms = dr["BRoom"].ToString().Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries); } if (dr["BRoom"] != DBNull.Value && sRooms.Contains(entry.Key.Replace("r", "").Replace("z", ""))) { foreach (DateTime date in dates) { string[] sD = dr["BDates"].ToString().Split('|'); for (int i = 0; i < sD.Length; i++) { sD[i] = sD[i].Replace(" 00:00:00", ""); } if (sD[0] == date.ToString("dd/MM/yyyy")) { ReoGridRange rgr = new ReoGridRange(count, iColumn, 1, 1); DateTime dtF = Convert.ToDateTime(sD[1]); double iDays = (dtF - date).TotalDays; if (iDays > 0) { rgr.Cols = Convert.ToInt32(iDays) + 1; } if (sRooms.Length > 1) { rgr.Rows = sRooms.Length; } bool rs = sheet.HasIntersectedMergingRange(rgr); if (!rs && (rgr.Rows > 1 || rgr.Cols > 1)) { sheet.MergeRange(rgr); } sheet[count, iColumn] = dr["BName"].ToString(); sheet.Cells[count, iColumn].Tag = Convert.ToInt32(dr["ID"]); sheet.Cells[count, iColumn].Style.HAlign = ReoGridHorAlign.Center; sheet.Cells[count, iColumn].Style.VAlign = ReoGridVerAlign.Middle; if (dr["Completed"] != DBNull.Value && dr["Completed"].ToString() == "Y") { sheet.Cells[count, iColumn].Style.BackColor = Color.MediumVioletRed; //sheet.SetRangeBorders(rgr, BorderPositions.Outside, new RangeBorderStyle { Color = Color.IndianRed, Style = BorderLineStyle.Solid }); } else { sheet.Cells[count, iColumn].Style.BackColor = Color.PaleVioletRed; //sheet.SetRangeBorders(rgr, BorderPositions.Outside, new RangeBorderStyle { Color = Color.MediumVioletRed, Style = BorderLineStyle.Solid }); } if (dr["BComments"].ToString().Length > 0) { sheet.Cells[count, iColumn].Style.Bold = true; } break; } iColumn++; } } } } } count++; } }