} // !joinColumns()
private Color _translateColor(ExcelColors color)
{
switch (color)
{
case ExcelColors.Yellow:
{
return(System.Drawing.Color.FromArgb(255, 192, 0));
}
case ExcelColors.Green:
{
return(System.Drawing.Color.FromArgb(0, 128, 0));
}
}
return(System.Drawing.Color.FromArgb(255, 255, 255));
} // !_translateColor()
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
workbook.Version = ExcelVersion.Version2007;
int maxColor = Enum.GetValues(typeof(ExcelColors)).Length;
Random random = new Random((int)System.DateTime.Now.Ticks);
for (int i = 2; i < 40; i++)
{
ExcelColors backKnownColor = (ExcelColors)(random.Next(1, maxColor / 2));
sheet.Range["A1"].Text = "Color Name";
sheet.Range["B1"].Text = "Red";
sheet.Range["C1"].Text = "Green";
sheet.Range["D1"].Text = "Blue";
sheet.Range["E1:K1"].Merge();
sheet.Range["E1:K1"].Text = "Gradient";
sheet.Range["A1:K1"].Style.Font.IsBold = true;
sheet.Range["A1:K1"].Style.Font.Size = 11;
string colorName = backKnownColor.ToString();
sheet.Range[string.Format("A{0}", i)].Text = colorName;
sheet.Range[string.Format("B{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).R.ToString();
sheet.Range[string.Format("C{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).G.ToString();
sheet.Range[string.Format("D{0}", i)].Text = workbook.GetPaletteColor(backKnownColor).B.ToString();
sheet.Range[string.Format("E{0}:K{0}", i)].Merge();
sheet.Range[string.Format("E{0}:K{0}", i)].Text = colorName;
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.FillPattern = ExcelPatternType.Gradient;
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.BackKnownColor = backKnownColor;
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.ForeKnownColor = ExcelColors.White;
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.GradientStyle = GradientStyleType.Vertical;
sheet.Range[string.Format("E{0}:K{0}", i)].Style.Interior.Gradient.GradientVariant = GradientVariantsType.ShadingVariants1;
}
sheet.AutoFitColumn(1);
workbook.SaveToFile("Sample.xlsx");
ExcelDocViewer(workbook.FileName);
}
public ExcelCell setBackground(ExcelColors color)
{
this._worksheet.Cells[this._cell].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
this._worksheet.Cells[this._cell].Style.Fill.BackgroundColor.SetColor(_translateColor(color));
return(this);
}
} // !getCellAddress()
public ExcelCell setColor(ExcelColors color)
{
this._worksheet.Cells[this._cell].Style.Font.Color.SetColor(_translateColor(color));
return(this);
}
/// <summary>
/// Highlights the background color of the cell
/// </summary>
/// <param name="row">0-based row number</param>
/// <param name="column">0-based column number</param>
/// <param name="color">Excel color</param>
public void HighlightCell(int row, int column, ExcelColors color)
{
this.initializeCell(row, column);
_currentCell.Style.BackColor = color;
_currentCell.WriteCellValue();
}
/// <summary>
/// Translates a known ExcelColor into the [short] HSSF version - allows a limited subset of colors
/// </summary>
/// <param name="color">Color to translate</param>
/// <returns>A short representation of the color</returns>
public static short GetColor(ExcelColors color)
{
if (color == ExcelColors.Black)
return NPOI.HSSF.Util.HSSFColor.BLACK.index;
else if (color == ExcelColors.Blue)
return NPOI.HSSF.Util.HSSFColor.BLUE.index;
else if (color == ExcelColors.Green)
return NPOI.HSSF.Util.HSSFColor.GREEN.index;
else if (color == ExcelColors.Orange)
return NPOI.HSSF.Util.HSSFColor.ORANGE.index;
else if (color == ExcelColors.Red)
return NPOI.HSSF.Util.HSSFColor.RED.index;
else if (color == ExcelColors.White)
return NPOI.HSSF.Util.HSSFColor.WHITE.index;
else if (color == ExcelColors.Yellow)
return NPOI.HSSF.Util.HSSFColor.YELLOW.index;
else if (color == ExcelColors.LightBlue)
return NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index;
else if (color == ExcelColors.LightGreen)
return NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index;
else if (color == ExcelColors.LightYellow)
return NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index;
return NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index;
}
/// <summary>
/// Sets the font color of a cell
/// </summary>
/// <param name="row">0-based row number</param>
/// <param name="column">0-based column number</param>
/// <param name="color">Excel Color</param>
public void SetCellFontColor(int row, int column, ExcelColors color)
{
this.initializeCell(row, column);
_currentCell.Style.ForeColor = color;
_currentCell.WriteCellValue();
}
/// <summary>
/// 设置单元格背景色及填充方式
/// </summary>
/// <param name="wsn">工作表的名称</param>
/// <param name="startRange">开始单元格</param>
/// <param name="endRange">结束单元格</param>
/// <param name="color">颜色索引：ExcelColors.Green</param>
public void SetCellsBackColor(string wsn, string startRange, string endRange, ExcelColors color)
{
Worksheet ws = GetSheet(wsn);
string range = startRange + ":" + endRange;
CellStyle style = ws.Range[range].Style;
style.KnownColor = color;
}
public StyleColorSelectedEventArgs(ExcelColors SelectedColor)
{
ColorIndex = SelectedColor.Id;
RGBValue = SelectedColor.RGBValue;
ColorName = SelectedColor.Name;
}
