/// <summary>
/// 高版本获取自定义颜色
/// </summary>
/// <param name="workBook"></param>
/// <param name="rgb"></param>
/// <returns></returns>
private static short GetCustomColor(this XSSFWorkbook workBook, string rgb)
{
short indexed = defaultColorIndexed;
if (string.IsNullOrEmpty(rgb))
{
return(indexed);
}
string[] colors = rgb.Split(',');
if (colors.Length != 3)
{
return(indexed);
}
byte red = 0;
byte green = 0;
byte blue = 0;
bool result = DealRGB(colors, ref red, ref green, ref blue);
if (result == false)
{
return(indexed);
}
byte[] bytes = { red, green, blue };
XSSFColor color = new XSSFColor();
color.SetRgb(bytes);
indexed = color.Indexed;
return(indexed);
}
/// <summary>
/// create the colours for the COBie sheet
/// </summary>
/// <param name="colourName"></param>
/// <param name="red"></param>
/// <param name="green"></param>
/// <param name="blue"></param>
private void CreateColours(string colourName, byte red, byte green, byte blue)
{
IColor colour = null;
if (IsXlsx)
{
byte[] rgb = new byte[3] {
red, green, blue
};
colour = new XSSFColor(rgb) as IColor;
}
else
{
HSSFPalette palette = ((HSSFWorkbook)ExcelWorkbook).GetCustomPalette();
colour = palette.FindSimilarColor(red, green, blue);
if (colour == null)
{
// First 64 are system colours
//srl this code does not work with the latest version of NPOI
//if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64 )
//{
// NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
//}
//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE++;
colour = palette.AddColor(red, green, blue);
}
}
_colours.Add(colourName, colour);
}
public ExcelConverter()
{
this._workbook = new XSSFWorkbook();
var props = _workbook.GetProperties();
props.CoreProperties.Title = "iPemSystem";
props.CoreProperties.Subject = "iPemSystem";
props.CoreProperties.Creator = "Steven H";
props.CoreProperties.Created = DateTime.Now;
props.CoreProperties.Description = "Delta GreenTech(China) Co., Ltd. All Rights Reserved";
this._worksheets = new Dictionary <int, XSSFSheet>();
this._ffffff = new XSSFColor(new byte[3] {
254, 254, 254
});
this._fafafa = new XSSFColor(new byte[3] {
249, 249, 249
});
this._f5f5f5 = new XSSFColor(new byte[3] {
244, 244, 244
});
this._c0c0c0 = new XSSFColor(new byte[3] {
191, 191, 191
});
this._666666 = new XSSFColor(new byte[3] {
101, 101, 101
});
this._157fcc = new XSSFColor(new byte[3] {
20, 126, 203
});
this._000000 = new XSSFColor(new byte[3] {
0, 0, 0
});
}
public JsValue CreateColor(XSSFColor color)
{
var result = Color.Construct();
result.FromColor(color);
return(result);
}
public void TestThemesTableColors()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook(testFile);
String[] rgbExpected = new string[] {
"ffffff", // Lt1
"000000", // Dk1
"eeece1", // Lt2
"1f497d", // DK2
"4f81bd", // Accent1
"c0504d", // Accent2
"9bbb59", // Accent3
"8064a2", // Accent4
"4bacc6", // Accent5
"f79646", // Accent6
"0000ff", // Hlink
"800080" // FolHlink
};
int i = 0;
foreach (IRow row in workbook.GetSheetAt(0))
{
XSSFFont font = (XSSFFont)row.GetCell(0).CellStyle.GetFont(workbook);
XSSFColor color = ((XSSFFont)font).GetXSSFColor();
Assert.AreEqual(rgbExpected[i], BitConverter.ToString(color.GetRgb()).Replace("-", "").ToLower(), "Failed color theme " + i);
long themeIdx = font.GetCTFont().color[0].theme;
Assert.AreEqual(i, themeIdx, "Failed color theme " + i);
i++;
}
}
public void TestRgbColor()
{
CT_Font ctFont = new CT_Font();
CT_Color color = ctFont.AddNewColor();
//Integer.toHexString(0xFFFFFF).getBytes() = [102, 102, 102, 102, 102, 102]
color.SetRgb(Encoding.ASCII.GetBytes("ffffff"));
ctFont.SetColorArray(0, color);
XSSFFont xssfFont = new XSSFFont(ctFont);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[0], xssfFont.GetXSSFColor().RGB[0]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[1], xssfFont.GetXSSFColor().RGB[1]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], xssfFont.GetXSSFColor().RGB[2]);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[3], xssfFont.GetXSSFColor().RGB[3]);
xssfFont.Color = ((short)23);
byte[] bytes = Encoding.ASCII.GetBytes(HexDump.ToHex(0xF1F1F1));
color.rgb = (bytes);
XSSFColor newColor = new XSSFColor(color);
xssfFont.SetColor(newColor);
Assert.AreEqual(ctFont.GetColorArray(0).GetRgb()[2], newColor.RGB[2]);
CollectionAssert.AreEqual(bytes, xssfFont.GetXSSFColor().RGB);
Assert.AreEqual(0, xssfFont.Color);
}
private void button5_Click(object sender, EventArgs e)
{
if (job.CurrentIndex != job.Count)
{
Comment com = new Comment();
com.ShowDialog();
string txt = com.CommentText;
XSSFWorkbook workbook = ExcelDataTableUtil.LoadExcel(job.JobPath);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
XSSFRow row = (XSSFRow)sheet.GetRow(job.CurrentIndex);
row.Cells[dt.Columns.Count - 2].SetCellValue("U");
XSSFColor XlColour = new XSSFColor(button5.BackColor);
XSSFCellStyle cellstyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellstyle.SetFillForegroundColor(XlColour);
cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
row.Cells[dt.Columns.Count - 2].CellStyle = cellstyle;
row.Cells[dt.Columns.Count - 1].SetCellValue(txt);
ExcelDataTableUtil.WriteExcel(workbook, job.JobPath);
job.CurrentIndex++;
job.HasFinished++;
Judge();
}
else
{
MessageBox.Show("已经是最后一条了");
}
}
private ICellStyle _markBorderBoldStyle()
{
ICellStyle style = book.CreateCellStyle();
//添加系统颜色到XSSFColor中
System.Drawing.Color cl = Color.FromArgb(252, 228, 214);
XSSFColor color = new XSSFColor(cl);
((XSSFCellStyle)style).SetFillForegroundColor(color);
style.FillPattern = FillPattern.SolidForeground;
//右边框描黑
style.BorderRight = BorderStyle.Thin;
//border描黑
style.BorderBottom = BorderStyle.Thin;
//对齐方式
style.Alignment = HorizontalAlignment.Center;
//字体设置
IFont font = book.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 11;
font.FontName = "calibri";
style.SetFont(font);
return(style);
}
internal IColor CreateColor(string htmlColor)
{
Color color = ColorTranslator.FromHtml(htmlColor);
byte[] array = new byte[]
{
color.R,
color.G,
color.B
};
IColor result;
switch (this.ExcelVersion)
{
case ExcelVersion.XLS:
{
HSSFPalette customPalette = this.ExcelXls.GetCustomPalette();
if (this._palleteColorSize >= 63)
{
HSSFColor hSSFColor = customPalette.FindColor(color.R, color.G, color.B);
if (hSSFColor == null)
{
hSSFColor = customPalette.FindSimilarColor(color.R, color.G, color.B);
}
short?palleteColorSize = this._palleteColorSize;
this._palleteColorSize = (palleteColorSize.HasValue
? new short?((short)(palleteColorSize.GetValueOrDefault() + 1))
: null);
result = hSSFColor;
}
else
{
if (!this._palleteColorSize.HasValue)
{
this._palleteColorSize = new short?(8);
}
else
{
short?palleteColorSize = this._palleteColorSize;
this._palleteColorSize = (palleteColorSize.HasValue
? new short?((short)(palleteColorSize.GetValueOrDefault() + 1))
: null);
}
customPalette.SetColorAtIndex(this._palleteColorSize.Value, color.R, color.G, color.B);
HSSFColor hSSFColor = customPalette.GetColor(this._palleteColorSize.Value);
result = hSSFColor;
}
break;
}
case ExcelVersion.XLSX:
result = new XSSFColor(color);
break;
default:
throw new Exception(ErrorMessage.Excel_BadVersion);
}
return(result);
}
public void FromColor(XSSFColor color)
{
var values = color.GetRgb();
R = values[0];
G = values[1];
B = values[2];
}
public void TestRGBColour()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("50299.xlsx");
// Check the CTColor is as expected
XSSFColor rgb3 = (XSSFColor)((XSSFCellStyle)wb.GetCellStyleAt((short)25)).FillForegroundXSSFColor;
Assert.AreEqual(false, rgb3.GetCTColor().IsSetIndexed());
Assert.AreEqual(0, rgb3.GetCTColor().indexed);
Assert.AreEqual(true, rgb3.GetCTColor().IsSetTint());
Assert.AreEqual(-0.34999, rgb3.GetCTColor().tint, 0.00001);
Assert.AreEqual(true, rgb3.GetCTColor().IsSetRgb());
Assert.AreEqual(3, rgb3.GetCTColor().GetRgb().Length);
// Now check the XSSFColor
Assert.AreEqual(0, rgb3.Indexed);
Assert.AreEqual(-0.34999, rgb3.Tint, 0.00001);
Assert.IsFalse(rgb3.HasAlpha);
Assert.IsTrue(rgb3.HasTint);
Assert.AreEqual("FFFFFFFF", rgb3.ARGBHex);
Assert.AreEqual(3, rgb3.RGB.Length);
Assert.AreEqual(255, rgb3.RGB[0]);
Assert.AreEqual(255, rgb3.RGB[1]);
Assert.AreEqual(255, rgb3.RGB[2]);
Assert.AreEqual(4, rgb3.ARGB.Length);
Assert.AreEqual(255, rgb3.ARGB[0]);
Assert.AreEqual(255, rgb3.ARGB[1]);
Assert.AreEqual(255, rgb3.ARGB[2]);
Assert.AreEqual(255, rgb3.ARGB[3]);
// Tint doesn't have the alpha
// tint = -0.34999
// 255 * (1 + tint) = 165 truncated
// or (byte) -91 (which is 165 - 256)
Assert.AreEqual(3, rgb3.GetRgbWithTint().Length);
Assert.AreEqual(-91, (sbyte)rgb3.GetRgbWithTint()[0]);
Assert.AreEqual(-91, (sbyte)rgb3.GetRgbWithTint()[1]);
Assert.AreEqual(-91, (sbyte)rgb3.GetRgbWithTint()[2]);
// Set the colour to black, will Get translated internally
// (Excel stores 3 colour white and black wrong!)
// Set the color to black (no theme).
rgb3.SetRgb(new byte[] { 0, 0, 0 });
Assert.AreEqual("FF000000", rgb3.ARGBHex);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[0]);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[1]);
Assert.AreEqual(0, rgb3.GetCTColor().GetRgb()[2]);
// Set another, is fine
rgb3.SetRgb(new byte[] { 16, 17, 18 });
Assert.IsFalse(rgb3.HasAlpha);
Assert.AreEqual("FF101112", rgb3.ARGBHex);
Assert.AreEqual(0x10, rgb3.GetCTColor().GetRgb()[0]);
Assert.AreEqual(0x11, rgb3.GetCTColor().GetRgb()[1]);
Assert.AreEqual(0x12, rgb3.GetCTColor().GetRgb()[2]);
}
private void BuildStyle_Border(IWorkbook workbook, StringBuilder style,
String type, BorderStyle xlsBorder, short borderColor)
{
if (xlsBorder == BorderStyle.None)
{
return;
}
StringBuilder borderStyle = new StringBuilder();
borderStyle.Append(ExcelToHtmlUtils.GetBorderWidth(xlsBorder));
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetBorderStyle(xlsBorder));
if (workbook is HSSFWorkbook)
{
var customPalette = ((HSSFWorkbook)workbook).GetCustomPalette();
HSSFColor color = null;
if (customPalette != null)
{
color = customPalette.GetColor(borderColor);
}
if (color != null)
{
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
}
}
else
{
IndexedColors clr = IndexedColors.ValueOf(borderColor);
if (clr != null)
{
borderStyle.Append(' ');
borderStyle.Append(clr.HexString);
}
else
{
XSSFColor color = null;
var stylesSource = ((XSSFWorkbook)workbook).GetStylesSource();
if (stylesSource != null)
{
var theme = stylesSource.GetTheme();
if (theme != null)
{
color = theme.GetThemeColor(borderColor);
}
}
if (color != null)
{
borderStyle.Append(' ');
borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
}
}
}
style.AppendFormat("border-{0}: {1}; ", type, borderStyle);
}
private ExportingCell(byte[] color)
{
if (color != null && color.Length != 3)
{
throw new ArgumentException(@"Dimension of color not equals three.", nameof(color));
}
Color = new XSSFColor(color ?? new HSSFColor.White().RGB);
}
private void GetSalaryList(ISheet mainSheet)
{
FileStream salaryFileStream = new FileStream(@"e:/2018年3月发薪名单.xlsx", FileMode.Open);
XSSFWorkbook salaryWorkbook = new XSSFWorkbook(salaryFileStream);
XSSFSheet salarySheet = (XSSFSheet)salaryWorkbook.GetSheet("Sheet1");
for (int i = 0; i < salarySheet.PhysicalNumberOfRows; i++)
{
XSSFRow tRow = (XSSFRow)mainSheet.CreateRow(i);
XSSFRow sRow = (XSSFRow)salarySheet.GetRow(i);
if (sRow != null && tRow != null)
{
for (int j = 0; j < 2; j++)
{
XSSFCell sCell = (XSSFCell)sRow.GetCell(j);
if (sCell == null)
{
break;
}
string cellValue = sCell.ToString();
XSSFCell tCell = (XSSFCell)tRow.CreateCell(j);
//CopyCellStyle(mainWorkbook, salaryWorkbook, tCell, sCell);
XSSFCellStyle style = (XSSFCellStyle)sCell.CellStyle;
XSSFCellStyle style1 = (XSSFCellStyle)mainWorkbook.CreateCellStyle();
XSSFColor color = null;
if (style.FillForegroundColorColor != null)
{
byte[] pa = style.FillForegroundColorColor.RGB;
string key = pa[0] + "," + pa[1] + "," + pa[2];
if (dictionary.ContainsKey(key))
{
style1.FillForegroundColor = dictionary[key];
}
else
{
Console.WriteLine("找不到该颜色!" + key);
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
}
else
{
Console.WriteLine("找不到该颜色!");
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
//byte[] pa1 = style.FillBackgroundColorColor.RGB;
//style1.FillForegroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
style1.FillPattern = sCell.CellStyle.FillPattern;
//style1.FillBackgroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
tCell.CellStyle = style1;
tCell.SetCellValue(cellValue);
}
}
}
salaryFileStream.Close();
salaryWorkbook.Close();
}
private IFont Getfont(int size, XSSFColor color)
{
XSSFFont titleFont = _wb.CreateFont() as XSSFFont;
titleFont.FontHeight = size;
titleFont.SetColor(color);
titleFont.IsBold = true;
return(titleFont);
}
private void CreateStyles()
{
_excelStyle = new Dictionary <string, XSSFCellStyle>();
IFont titleFont = Getfont(18, new XSSFColor(new byte[] { 255, 255, 255 }));
IFont normalFont = Getfont(10, new XSSFColor(new byte[] { 39, 51, 89 }));
IFont leaveFont = Getfont(10, new XSSFColor(new byte[] { 255, 255, 255 }));
IFont notWorkDayFont = Getfont(10, new XSSFColor(new byte[] { 255, 0, 0 }));
XSSFCellStyle style = _wb.CreateCellStyle() as XSSFCellStyle;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFillForegroundColor(new XSSFColor(new byte[] { 63, 81, 181 }));
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(titleFont);
_excelStyle["title"] = style;
XSSFColor workDayBorderColor = new XSSFColor(new byte[] { 39, 51, 89 });
XSSFColor foreGroundColor = new XSSFColor(new byte[] { 255, 255, 255 });
XSSFColor notWorkDayBorderColor = new XSSFColor(new byte[] { 255, 0, 0 });
_excelStyle["workday"] = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
_excelStyle["notWorkDay"] = GetCellCommonStyle(notWorkDayFont, notWorkDayBorderColor, foreGroundColor, BorderStyle.Thin);
style = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
style.Alignment = HorizontalAlignment.Left;
_excelStyle["employe"] = style;
_excelStyle["workdayMonthEnd"] = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Double);
_excelStyle["notWorkDayMonthEnd"] = GetCellCommonStyle(notWorkDayFont, notWorkDayBorderColor, foreGroundColor, BorderStyle.Double);
foreGroundColor = new XSSFColor(new byte[] { 239, 243, 255 });
_excelStyle["workdayOdd"] = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
_excelStyle["notWorkDayOdd"] = GetCellCommonStyle(notWorkDayFont, notWorkDayBorderColor, foreGroundColor, BorderStyle.Thin);
style = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
style.Alignment = HorizontalAlignment.Left;
_excelStyle["employeOdd"] = style;
_excelStyle["workdayMonthEndOdd"] = GetCellCommonStyle(normalFont, workDayBorderColor, foreGroundColor, BorderStyle.Double);
_excelStyle["notWorkDayMonthEndOdd"] = GetCellCommonStyle(notWorkDayFont, notWorkDayBorderColor, foreGroundColor, BorderStyle.Double);
foreGroundColor = new XSSFColor(new byte[] { 63, 81, 181 });
IDataFormat format = _wb.CreateDataFormat();
_excelStyle["leave"] = GetCellCommonStyle(leaveFont, workDayBorderColor, foreGroundColor, BorderStyle.Thin);
_excelStyle["leave"].DataFormat = format.GetFormat(";;;");
_excelStyle["leave"].FillPattern = FillPattern.BigSpots;
}
public void InheritFromThemeAsRequired(XSSFColor color)
{
if (color == null || !color.GetCTColor().themeSpecified)
{
return;
}
XSSFColor themeColor = this.GetThemeColor(color.GetTheme());
color.GetCTColor().SetRgb(themeColor.GetCTColor().GetRgb());
}
/// <summary>
/// Constructor
/// </summary>
public ReporteTabularDisponibilidadSemana()
{
InitializeComponent();
GrisClaro = new XSSFColor(rgbGrisClaro);
GrisObscuro = new XSSFColor(rgbGrisObscuro);
InicializaContexto();
InicializarDatosUsuario();
}
public static XSSFSheet ChangeColor(XSSFSheet sheet, int x, int y, Color color, XSSFCellStyle cellstyle)
{
XSSFRow row = (XSSFRow)sheet.GetRow(x);
XSSFCell cell = (XSSFCell)row.GetCell(y);
XSSFColor XlColour = new XSSFColor(color);
cellstyle.SetFillForegroundColor(XlColour);
cellstyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
cell.CellStyle = cellstyle;
return(sheet);
}
private XSSFFont CreateFontColorAqua(XSSFWorkbook workbook)
{
var font = CreateDefaultFont(workbook);
var auaColor = new XSSFColor(new byte[] { 0, 100, 230 });
font.SetColor(auaColor);
//font.Color = IndexedColors.Aqua.Index;
return(font);
}
public bool CrearArchivoEmail()
{
bool resultado = true;
try
{
string tempFile = Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx";
XSSFWorkbook wb = new XSSFWorkbook();
using (var ms = new MemoryStream()) {
ISheet sheet = wb.CreateSheet("Destinatarios");
int indexCabecera = 0;
var rowCabecera = sheet.CreateRow(indexCabecera);
XSSFCellStyle cellStyle = (XSSFCellStyle)wb.CreateCellStyle();
cellStyle.FillBackgroundColor = HSSFColor.DarkBlue.Index;
XSSFFont font = (XSSFFont)wb.CreateFont();
var color = new XSSFColor(new byte[] { 255, 255, 255 });
font.SetColor(color);
cellStyle.SetFont(font);
cellStyle.FillPattern = FillPattern.SolidForeground;
var cell1 = rowCabecera.CreateCell(0);
var cell2 = rowCabecera.CreateCell(1);
cell1.CellStyle = cellStyle;
cell1.SetCellValue(Enumeradores.EXCEL_CABECERA_CORREO);
cell2.CellStyle = cellStyle;
cell2.SetCellValue(Enumeradores.EXCEL_CABECERA_NOMBRE);
wb.Write(ms);
File.WriteAllBytes(tempFile, ms.ToArray());
}
System.Diagnostics.Process.Start(tempFile);
}
catch (Exception)
{
throw;
}
return(resultado);
}
static ExcelCreate()
{
_scoreColor = new XSSFColor();
_scoreColor.SetRgb(new byte[] { 255, 153, 58 });
_winRateColor = new XSSFColor();
_winRateColor.SetRgb(new byte[] { 255, 0, 0 });
_bgColor1 = new XSSFColor();
_bgColor1.SetRgb(new byte[] { 112, 206, 188 });
_bgColor2 = new XSSFColor();
_bgColor2.SetRgb(new byte[] { 220, 230, 241 });
}
/**
* Set the color to use for the selected border
*
* @param side - where to apply the color defInition
* @param color - the color to use
*/
public void SetBorderColor(BorderSide side, XSSFColor color)
{
CT_BorderPr borderPr = GetBorder(side, true);
if (color == null)
{
borderPr.UnsetColor();
}
else
{
borderPr.color = color.GetCTColor();
}
}
public static IArgbColor GetRgbColor(XSSFColor color)
{
var bytes = color.RGB;
if (bytes is null)
{
return(ExcelColor.Automatic);
}
else
{
return(RgbColor.Create(bytes[0], bytes[1], bytes[2]));
}
}
protected override void AssertColour(String hexExpected, IColor actual)
{
Assert.IsNotNull(actual, "Colour must be given");
XSSFColor colour = (XSSFColor)actual;
if (hexExpected.Length == 8)
{
Assert.AreEqual(hexExpected, colour.ARGBHex);
}
else
{
Assert.AreEqual(hexExpected, colour.ARGBHex.Substring(2));
}
}
public static void CloneWorkbookFormatInfo(IWorkbook destWorkbook, IWorkbook srcWorkbook)
{
// you have to clone many of the items associated with formatting at the workbook level in
// order to have the resource avaliable for the cell styles, etc.
StylesTable srcStyles = ((XSSFWorkbook)srcWorkbook).GetStylesSource();
StylesTable destStyles = ((XSSFWorkbook)destWorkbook).GetStylesSource();
foreach (var font in srcStyles.GetFonts())
{
destStyles.PutFont(font, true);
}
foreach (var fill in srcStyles.GetFills())
{
XSSFCellFill newFill = new XSSFCellFill();
XSSFColor foregroundColor = ((XSSFCellFill)fill).GetFillForegroundColor();
XSSFColor backgroundColor = ((XSSFCellFill)fill).GetFillBackgroundColor();
if (newFill != null)
{
// if (foregroundColor != null) {
// Console.WriteLine($"creating fill. foregroundColor is {foregroundColor}.");
// newFill.SetFillForegroundColor(foregroundColor);
// }
// else {
// Console.WriteLine($"Whoops! foregroundColor is {foregroundColor}.");
// }
// if (backgroundColor != null) {
// Console.WriteLine($"creating fill. backgroundColor is {backgroundColor}.");
// newFill.SetFillBackgroundColor(backgroundColor);
// }
// else {
// Console.WriteLine($"Whoops! backgroundColor is {backgroundColor}.");
// }
// destStyles.PutFill(newFill);
destStyles.PutFill(fill);
}
else
{
Console.WriteLine($"Whoops! newFill was not created!");
}
}
foreach (var border in srcStyles.GetBorders())
{
destStyles.PutBorder(new XSSFCellBorder(border.GetCTBorder()));
}
}
private static void createHeader <T>(T item, XSSFWorkbook xlBook, ISheet sheet, int columnIndex, IEnumerable <ExcelOnWritingColumnConfiguration> columnMaps, CellRangeAddress mergedCellRange = null) where T : class
{
var headerRow = sheet.CreateRow(sheet.LastRowNum + 1);
foreach (var column in columnMaps)
{
var headerConfig = column.Header;
var headerStyle = createCellStyle(xlBook, true);
var cell = headerRow.CreateCell(columnIndex + column.ColumnIndex, CellType.String);
cell.CellStyle = headerStyle;
cell.SetCellValue(column.ColumnName);
if (!headerConfig.BackgroundColor.IsEmpty)
{
var backgroundColor = new XSSFColor(headerConfig.BackgroundColor);
headerStyle.SetFillForegroundColor(backgroundColor);
headerStyle.FillPattern = FillPattern.SolidForeground;
}
if (!headerConfig.TextColor.IsEmpty)
{
var textColor = new XSSFColor(headerConfig.TextColor);
headerStyle.GetFont().SetColor(textColor);
}
// add merged cell
CellRangeAddress mergedArea = null;
if (column.Children != null)
{
// add merged cell horizontally
mergedArea = new CellRangeAddress(headerRow.RowNum, headerRow.RowNum, column.ColumnIndex, (column.ColumnIndex + column.Children.Length - 1));
createHeader(item, xlBook, sheet, column.ColumnIndex, ((ExcelOnWritingColumnConfiguration[])column.Children).ToList(), mergedArea);
}
else
{
// add merged cell vertically
if (columnMaps.Any(x => x.Children != null && x.Children.Count() > 0))
{
mergedArea = new CellRangeAddress(headerRow.RowNum, headerRow.RowNum + 1, column.ColumnIndex, column.ColumnIndex);
}
}
if (mergedArea != null)
{
sheet.AddMergedRegion(mergedArea);
}
}
}
public void TestARGBColour()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48779.xlsx");
// Check the CTColor is as expected
XSSFColor rgb4 = (XSSFColor)wb.GetCellStyleAt(1).FillForegroundColorColor;
Assert.AreEqual(false, rgb4.GetCTColor().IsSetIndexed());
Assert.AreEqual(0, rgb4.GetCTColor().indexed);
Assert.AreEqual(true, rgb4.GetCTColor().IsSetRgb());
Assert.AreEqual(4, rgb4.GetCTColor().GetRgb().Length);
// Now check the XSSFColor
Assert.AreEqual(0, rgb4.Indexed);
Assert.AreEqual(0.0, rgb4.Tint);
Assert.IsFalse(rgb4.HasTint);
Assert.IsTrue(rgb4.HasAlpha);
Assert.AreEqual("FFFF0000", rgb4.ARGBHex);
Assert.AreEqual(3, rgb4.RGB.Length);
Assert.AreEqual(255, rgb4.RGB[0]);
Assert.AreEqual(0, rgb4.RGB[1]);
Assert.AreEqual(0, rgb4.RGB[2]);
Assert.AreEqual(4, rgb4.ARGB.Length);
Assert.AreEqual(255, rgb4.ARGB[0]);
Assert.AreEqual(255, rgb4.ARGB[1]);
Assert.AreEqual(0, rgb4.ARGB[2]);
Assert.AreEqual(0, rgb4.ARGB[3]);
// Tint doesn't have the alpha
Assert.AreEqual(3, rgb4.GetRgbWithTint().Length);
Assert.AreEqual(255, rgb4.GetRgbWithTint()[0]);
Assert.AreEqual(0, rgb4.GetRgbWithTint()[1]);
Assert.AreEqual(0, rgb4.GetRgbWithTint()[2]);
// Turn on tinting, and check it behaves
// TODO These values are suspected to be wrong...
rgb4.Tint = (0.4);
Assert.IsTrue(rgb4.HasTint);
Assert.AreEqual(0.4, rgb4.Tint);
Assert.AreEqual(3, rgb4.GetRgbWithTint().Length);
Assert.AreEqual(255, rgb4.GetRgbWithTint()[0]);
Assert.AreEqual(102, rgb4.GetRgbWithTint()[1]);
Assert.AreEqual(102, rgb4.GetRgbWithTint()[2]);
}
public static XSSFColor?WithIndex(this XSSFColor xSSFColor)
{
if (xSSFColor == null)
{
return(null);
}
if (hexStringIndexColorCache.Count() == 0)
{
CacheIndexColors();
}
if (hexStringIndexColorCache.ContainsKey("#" + ByteArrayToString(xSSFColor.RGB)))
{
xSSFColor.Indexed = hexStringIndexColorCache["#" + ByteArrayToString(xSSFColor.RGB)].Index;
}
return(xSSFColor);
}
private static ICellStyle CreateDateStyle(XSSFWorkbook workbook, XSSFColor fillColor, bool withTime)
{
var dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
var dataFormat = workbook.CreateDataFormat();
string format = withTime ? ExcelUtil.GetDateTimeFormat() : ExcelUtil.GetDateFormat();
dateStyle.DataFormat = dataFormat.GetFormat(format);
if (fillColor != null)
{
dateStyle.FillPattern = FillPattern.SolidForeground;
dateStyle.FillForegroundColorColor = fillColor;
}
return(dateStyle);
}
/**
* Set the background fill color represented as a {@link XSSFColor} value.
*
* @param color
*/
public void SetFillBackgroundColor(XSSFColor color)
{
CT_PatternFill ptrn = EnsureCTPatternFill();
ptrn.bgColor = (color.GetCTColor());
}
/**
* Set the foreground fill color represented as a {@link XSSFColor} value.
*
* @param color - the color to use
*/
public void SetFillForegroundColor(XSSFColor color)
{
CT_PatternFill ptrn = EnsureCTPatternFill();
ptrn.fgColor = color.GetCTColor();
}
