/// <summary>
/// Convert a system color to Excel color
/// </summary>
/// <param name="color">The system color to convert</param>
/// <returns>Excel color</returns>
public static XlRgbColor ConvertSytemColorToXlColor(Color color)
{
int rgbColor = VB.Information.RGB(color.R, color.G, color.B);
XlRgbColor xlColor = (XlRgbColor)rgbColor;
return(xlColor);
}
public static XlRgbColor GetExcelColor(ProductMasterItem item)
{
XlRgbColor color = XlRgbColor.rgbWhite;
if (MasterBrushDictionary.ContainsKey(item))
{
Brush brush = MasterBrushDictionary[item];
if (Equals(brush, Brushes.Aquamarine))
{
color = XlRgbColor.rgbAquamarine;
}
else if (Equals(brush, Brushes.LightGreen))
{
color = XlRgbColor.rgbLightGreen;
}
else if (Equals(brush, Brushes.DarkSalmon))
{
color = XlRgbColor.rgbDarkSalmon;
}
else if (Equals(brush, Brushes.CadetBlue))
{
color = XlRgbColor.rgbCadetBlue;
}
else if (Equals(brush, Brushes.BurlyWood))
{
color = XlRgbColor.rgbBurlyWood;
}
}
return(color);
}
public void changeColor(int Row, int Line, XlRgbColor color)
{
Row++;
Line++;
Range range = ws.Range[ws.Cells[Row, Line], ws.Cells[Row, Line]];
range.Interior.Color = color;
}
private void ApplyRowStyle(int rowIndex, int columnsCount, XlRgbColor bgColor, bool needBold)
{
Range range = InvoicesSheet.Rows.Range[InvoicesSheet.Rows.Cells[rowIndex, 1],
InvoicesSheet.Rows.Cells[rowIndex, columnsCount]];
range.Font.Bold = needBold;
range.Interior.Color = bgColor;
range.Borders.LineStyle = XlLineStyle.xlContinuous;
}
private static void SchoolInfo(Dictionary <string, School> schools, _Worksheet resultSheet)
{
double[] mon;
int totalWrite = 2;
int moneyWrite = 2;
int itemWrite = 2;
int itemTot;
int orderTotalsStart = 1;
int totalsColStart = 8;
int itemSchoolStart = 14;
string itemType = "Small";
XlRgbColor[] Colours = new XlRgbColor[] { XlRgbColor.rgbLightGreen, XlRgbColor.rgbLightYellow, XlRgbColor.rgbLavender, XlRgbColor.rgbLightBlue, XlRgbColor.rgbLightSalmon };
int schoolColourIndex = 0;
resultSheet.Cells[1, orderTotalsStart] = "School Name";
resultSheet.Cells[1, orderTotalsStart + 1] = "Order Number";
resultSheet.Cells[1, orderTotalsStart + 2] = "Net";
resultSheet.Cells[1, orderTotalsStart + 3] = "Fee";
resultSheet.Cells[1, orderTotalsStart + 4] = "Amount";
resultSheet.Cells[1, totalsColStart] = "School Name";
resultSheet.Cells[1, totalsColStart + 1] = "Total Net";
resultSheet.Cells[1, totalsColStart + 2] = "Total Fee";
resultSheet.Cells[1, totalsColStart + 3] = "Total Amount";
resultSheet.Cells[1, totalsColStart + 4] = "Number of Orders";
resultSheet.Cells[1, itemSchoolStart] = "School Name";
resultSheet.Cells[1, itemSchoolStart + 1] = "Item Name";
resultSheet.Cells[1, itemSchoolStart + 2] = "Number of Items";
resultSheet.Cells[1, itemSchoolStart + 3] = "SKU";
resultSheet.Cells[1, itemSchoolStart + 4] = "Items per type";
foreach (string schoolName in schools.Keys)
{
mon = schools[schoolName].GetAllTotals();
resultSheet.Cells[totalWrite, totalsColStart] = schoolName;
resultSheet.Cells[totalWrite, totalsColStart + 1] = mon[0];
resultSheet.Cells[totalWrite, totalsColStart + 2] = mon[1];
resultSheet.Cells[totalWrite, totalsColStart + 3] = mon[2];
resultSheet.Cells[totalWrite, totalsColStart + 4] = mon[3];
resultSheet.Range[resultSheet.Cells[totalWrite, totalsColStart], resultSheet.Cells[totalWrite, totalsColStart + 4]].Interior.Color = Colours[schoolColourIndex];
totalWrite++;
resultSheet.Cells[moneyWrite, orderTotalsStart] = schoolName;
Dictionary <int, double[]> money = schools[schoolName].GetMoney();
foreach (int orderNum in money.Keys)
{
mon = money[orderNum];
resultSheet.Cells[moneyWrite, orderTotalsStart + 1] = orderNum;
resultSheet.Cells[moneyWrite, orderTotalsStart + 2] = mon[0];
resultSheet.Cells[moneyWrite, orderTotalsStart + 3] = mon[1];
resultSheet.Cells[moneyWrite, orderTotalsStart + 4] = mon[2];
resultSheet.Range[resultSheet.Cells[moneyWrite, orderTotalsStart], resultSheet.Cells[moneyWrite, orderTotalsStart + 4]].Interior.Color = Colours[schoolColourIndex];
moneyWrite++;
}
resultSheet.Cells[itemWrite, itemSchoolStart] = schoolName;
itemType = "Small";
itemTot = 0;
foreach (string[] item in schools[schoolName].GetSortItems())
{
resultSheet.Cells[itemWrite, itemSchoolStart + 1] = item[0];
resultSheet.Cells[itemWrite, itemSchoolStart + 2] = item[1];
resultSheet.Cells[itemWrite, itemSchoolStart + 3] = item[2];
resultSheet.Range[resultSheet.Cells[itemWrite, itemSchoolStart], resultSheet.Cells[itemWrite, itemSchoolStart + 4]].Interior.Color = Colours[schoolColourIndex];
if (itemType.Equals(item[0].Substring(item[0].LastIndexOf(" ") + 1)))
{
itemTot += Convert.ToInt32(item[1]);
}
else
{
resultSheet.Cells[itemWrite - 1, itemSchoolStart + 4] = itemTot.ToString();
itemType = item[0].Substring(item[0].LastIndexOf(" ") + 1);
itemTot = Convert.ToInt32(item[1]);
}
itemWrite++;
}
resultSheet.Cells[itemWrite - 1, itemSchoolStart + 4] = itemTot.ToString();
schoolColourIndex = (schoolColourIndex + 1) % Colours.Length;
}
}
public static void SetCellBackground(Worksheet worksheet, CellIndex from, CellIndex to, XlRgbColor color)
{
worksheet.Range[worksheet.Cells[from.x, from.y],
worksheet.Cells[to.x, to.y]].Interior.Color = color;
}
/// <summary>
/// Writes the text into the given range. Does not add a row.
/// </summary>
/// <param name="text">Text to be written out</param>
/// <param name="startColumn">Zero indexed column</param>
/// <param name="columns">Total number of columns the text takes</param>
/// <param name="row"></param>
/// <param name="worksheet"></param>
private static void OutputText(string text, int startColumn, int columns, int row, Worksheet worksheet, XlRgbColor color = XlRgbColor.rgbBlack, XlRgbColor bg = XlRgbColor.rgbWhite)
{
columns--;
Range rng = GetRange(_minCol + startColumn, row, _minCol + startColumn + columns, row, worksheet);
rng.UnMerge();
rng.Cells.Font.Size = TextFontSize;
rng.Merge();
rng.WrapText = true;
rng.Value = text;
rng.Font.Color = color; // text color
rng.Interior.Color = bg;
}
void SelectionLocationReceived(object sender, EventArgs e)
{
RangePacket packet = (RangePacket)sender;
Range selection = ActiveWorksheet.Range[packet.RangeInfo];
ExcelUser user = packet.User;
if (lastRange != null)
{
Debug.WriteLine(lastLeftColor);
lastRange.Borders.Item[XlBordersIndex.xlEdgeLeft].Color = lastLeftColor;
lastRange.Borders.Item[XlBordersIndex.xlEdgeLeft].Weight = lastLeftWeight;
lastRange.Borders.Item[XlBordersIndex.xlEdgeLeft].LineStyle = lastLeftStyle;
lastRange.Borders.Item[XlBordersIndex.xlEdgeTop].Color = lastTopColor;
lastRange.Borders.Item[XlBordersIndex.xlEdgeTop].Weight = lastTopWeight;
lastRange.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle = lastTopStyle;
lastRange.Borders.Item[XlBordersIndex.xlEdgeRight].Color = lastRightColor;
lastRange.Borders.Item[XlBordersIndex.xlEdgeRight].Weight = lastRightWeight;
lastRange.Borders.Item[XlBordersIndex.xlEdgeRight].LineStyle = lastRightStyle;
lastRange.Borders.Item[XlBordersIndex.xlEdgeBottom].Color = lastBottomColor;
lastRange.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight = lastBottomWeight;
lastRange.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle = lastBottomStyle;
lastRange.ClearComments();
if (lastComments.Count >= 0)
{
if (lastComments[0] != null)
{
lastRange.AddComment(lastComments[0]);
}
}
//all you have to do is .copy!!!!!!!!!!!!!!!!!!!!!!
//selection.Copy(lastRange);
//ActiveWorksheet.Range["A1"].Value = "test";
}
lastRange = selection;
Debug.WriteLine("got selection change");
ActiveRange = selection;
lastComments = new List <string>();
lastLeftColor = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Color;
lastLeftWeight = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Weight;
lastLeftStyle = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeLeft].LineStyle;
lastTopColor = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeTop].Color;
lastTopWeight = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeTop].Weight;
lastTopStyle = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle;
lastRightColor = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeRight].Color;
lastRightWeight = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeRight].Weight;
lastRightStyle = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeRight].LineStyle;
lastBottomColor = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Color;
lastBottomWeight = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight;
lastBottomStyle = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle;
selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Color = XlRgbColor.rgbBlue;
selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlMedium;
selection.Borders.Item[XlBordersIndex.xlEdgeTop].Color = XlRgbColor.rgbBlue;
selection.Borders.Item[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlMedium;
selection.Borders.Item[XlBordersIndex.xlEdgeRight].Color = XlRgbColor.rgbBlue;
selection.Borders.Item[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlMedium;
selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Color = XlRgbColor.rgbBlue;
selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlMedium;
lastComments.Add(selection.Comment?.Text());
selection.ClearComments();
Debug.WriteLine("Made it to comment adding");
if (selection.Count < 2)
{
selection.AddComment(user.ToString() + ": Updating this cell at the moment");
}
else
{
selection[selection.Columns.Count]?.AddComment(user.ToString() + ": Updating these cells at the moment");
}
}
