public void TestStyleNames()
{
HSSFWorkbook wb = OpenSample("WithExtendedStyles.xls");
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0);
ICell c1 = s.GetRow(0).GetCell(0);
ICell c2 = s.GetRow(1).GetCell(0);
ICell c3 = s.GetRow(2).GetCell(0);
HSSFCellStyle cs1 = (HSSFCellStyle)c1.CellStyle;
HSSFCellStyle cs2 = (HSSFCellStyle)c2.CellStyle;
HSSFCellStyle cs3 = (HSSFCellStyle)c3.CellStyle;
Assert.IsNotNull(cs1);
Assert.IsNotNull(cs2);
Assert.IsNotNull(cs3);
// Check we got the styles we'd expect
Assert.AreEqual(10, cs1.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(9, cs2.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(12, cs3.GetFont(wb).FontHeightInPoints);
Assert.AreEqual(15, cs1.Index);
Assert.AreEqual(23, cs2.Index);
Assert.AreEqual(24, cs3.Index);
Assert.IsNull(cs1.ParentStyle);
Assert.IsNotNull(cs2.ParentStyle);
Assert.IsNotNull(cs3.ParentStyle);
Assert.AreEqual(21, cs2.ParentStyle.Index);
Assert.AreEqual(22, cs3.ParentStyle.Index);
// Now Check we can get style records for
// the parent ones
Assert.IsNull(wb.Workbook.GetStyleRecord(15));
Assert.IsNull(wb.Workbook.GetStyleRecord(23));
Assert.IsNull(wb.Workbook.GetStyleRecord(24));
Assert.IsNotNull(wb.Workbook.GetStyleRecord(21));
Assert.IsNotNull(wb.Workbook.GetStyleRecord(22));
// Now Check the style names
Assert.AreEqual(null, cs1.UserStyleName);
Assert.AreEqual(null, cs2.UserStyleName);
Assert.AreEqual(null, cs3.UserStyleName);
Assert.AreEqual("style1", cs2.ParentStyle.UserStyleName);
Assert.AreEqual("style2", cs3.ParentStyle.UserStyleName);
// now apply a named style to a new cell
ICell c4 = s.GetRow(0).CreateCell(1);
c4.CellStyle = (cs2);
Assert.AreEqual("style1", ((HSSFCellStyle)c4.CellStyle).ParentStyle.UserStyleName);
}
private void SetCellHyperlink(HSSFWorkbook workbook, HSSFCell cell, String linkAddress)
{
HSSFCellStyle cellStyle = (HSSFCellStyle)cell.CellStyle;
HSSFFont font = (HSSFFont)cellStyle.GetFont(workbook);
font.Underline = FontUnderlineType.Single;
cellStyle.SetFont(font);
HSSFHyperlink hyperlink = new HSSFHyperlink(HyperlinkType.Url);
hyperlink.Address = linkAddress;
cell.Hyperlink = hyperlink;
cell.CellStyle = cellStyle;
}
protected String BuildStyle(HSSFWorkbook workbook, HSSFCellStyle cellStyle)
{
StringBuilder style = new StringBuilder();
HSSFPalette palette = workbook.GetCustomPalette();
style.Append("white-space: pre-wrap; ");
ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);
if (cellStyle.FillPattern == FillPatternType.NO_FILL)
{
// no fill
}
else if (cellStyle.FillPattern == FillPatternType.SOLID_FOREGROUND)
{
//cellStyle.
//HSSFColor.
HSSFColor foregroundColor = palette.GetColor(cellStyle.FillForegroundColor);
if (foregroundColor != null)
{
style.Append("background-color: " + ExcelToHtmlUtils.GetColor(foregroundColor) + "; ");
}
}
else
{
HSSFColor backgroundColor = palette.GetColor(cellStyle.FillBackgroundColor);
if (backgroundColor != null)
{
style.Append("background-color: " + ExcelToHtmlUtils.GetColor(backgroundColor) + "; ");
}
}
BuildStyle_Border(workbook, style, "top", cellStyle.BorderTop, cellStyle.TopBorderColor);
BuildStyle_Border(workbook, style, "right", cellStyle.BorderRight, cellStyle.RightBorderColor);
BuildStyle_Border(workbook, style, "bottom", cellStyle.BorderBottom, cellStyle.BottomBorderColor);
BuildStyle_Border(workbook, style, "left", cellStyle.BorderLeft, cellStyle.LeftBorderColor);
HSSFFont font = cellStyle.GetFont(workbook) as HSSFFont;
BuildStyle_Font(workbook, style, font);
return(style.ToString());
}
public static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary <Int32, HSSFCellStyle> styleMap, Dictionary <short, short> paletteMap, Boolean keepFormulas)
{
if (styleMap != null)
{
if (oldCell.CellStyle != null)
{
if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook)
{
newCell.CellStyle = oldCell.CellStyle;
}
else
{
int styleHashCode = oldCell.CellStyle.GetHashCode();
if (styleMap.ContainsKey(styleHashCode))
{
newCell.CellStyle = styleMap[styleHashCode];
}
else
{
HSSFCellStyle newCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
RemapCellStyle(newCellStyle, paletteMap); //Clone copies as-is, we need to remap colors manually
newCell.CellStyle = newCellStyle;
//Clone of cell style always clones the font. This makes my life easier
IFont theFont = newCellStyle.GetFont(newCell.Sheet.Workbook);
if (theFont.Color > 0 && paletteMap.ContainsKey(theFont.Color))
{
theFont.Color = paletteMap[theFont.Color]; //Remap font color
}
styleMap.Add(styleHashCode, newCellStyle);
}
}
}
else
{
newCell.CellStyle = null;
}
}
switch (oldCell.CellType)
{
case CellType.String:
HSSFRichTextString rts = oldCell.RichStringCellValue as HSSFRichTextString;
newCell.SetCellValue(rts);
if (rts != null)
{
for (int j = 0; j < rts.NumFormattingRuns; j++)
{
short fontIndex = rts.GetFontOfFormattingRun(j);
int startIndex = rts.GetIndexOfFormattingRun(j);
int endIndex = 0;
if (j + 1 == rts.NumFormattingRuns)
{
endIndex = rts.Length;
}
else
{
endIndex = rts.GetIndexOfFormattingRun(j + 1);
}
FontRecord fr = newCell.BoundWorkbook.CreateNewFont();
fr.CloneStyleFrom(oldCell.BoundWorkbook.GetFontRecordAt(fontIndex));
HSSFFont font = new HSSFFont((short)(newCell.BoundWorkbook.GetFontIndex(fr)), fr);
newCell.RichStringCellValue.ApplyFont(startIndex, endIndex, font);
}
}
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
if (keepFormulas)
{
newCell.SetCellType(CellType.Formula);
newCell.CellFormula = oldCell.CellFormula;
}
else
{
try
{
newCell.SetCellType(CellType.Numeric);
newCell.SetCellValue(oldCell.NumericCellValue);
}
catch (Exception ex)
{
newCell.SetCellType(CellType.String);
newCell.SetCellValue(oldCell.ToString());
}
}
break;
default:
break;
}
}
public static void CopyCell(HSSFCell oldCell, HSSFCell newCell, IDictionary <Int32, HSSFCellStyle> styleMap, Dictionary <short, short> paletteMap, Boolean keepFormulas)
{
if (styleMap != null)
{
if (oldCell.CellStyle != null)
{
if (oldCell.Sheet.Workbook == newCell.Sheet.Workbook)
{
newCell.CellStyle = oldCell.CellStyle;
}
else
{
int styleHashCode = oldCell.CellStyle.GetHashCode();
if (styleMap.ContainsKey(styleHashCode))
{
newCell.CellStyle = styleMap[styleHashCode];
}
else
{
HSSFCellStyle newCellStyle = (HSSFCellStyle)newCell.Sheet.Workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
RemapCellStyle(newCellStyle, paletteMap); //Clone copies as-is, we need to remap colors manually
newCell.CellStyle = newCellStyle;
//Clone of cell style always clones the font. This makes my life easier
IFont theFont = newCellStyle.GetFont(newCell.Sheet.Workbook);
if (theFont.Color > 0 && paletteMap.ContainsKey(theFont.Color))
{
theFont.Color = paletteMap[theFont.Color]; //Remap font color
}
styleMap.Add(styleHashCode, newCellStyle);
}
}
}
else
{
newCell.CellStyle = null;
}
}
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
if (keepFormulas)
{
newCell.SetCellType(CellType.Formula);
newCell.CellFormula = oldCell.CellFormula;
}
else
{
try
{
newCell.SetCellType(CellType.Numeric);
newCell.SetCellValue(oldCell.NumericCellValue);
}
catch (Exception ex)
{
newCell.SetCellType(CellType.String);
newCell.SetCellValue(oldCell.ToString());
}
}
break;
default:
break;
}
}
