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++;
}
}
