private Dictionary <short, List <string> > AnalysisCellString(HSSFCell cell)
{
Dictionary <short, List <string> > _cellList = new Dictionary <short, List <string> >();
HSSFRichTextString rich = (HSSFRichTextString)cell.RichStringCellValue;
int formattingRuns = cell.RichStringCellValue.NumFormattingRuns;
if (formattingRuns == 0)
{
return(_cellList);
}
IFont font2 = _workBook.GetFontAt(cell.CellStyle.FontIndex);
string st2 = rich.String.Substring(0, rich.GetIndexOfFormattingRun(0));
if (_cellList.Keys.Where(t => t == font2.Color).Count() == 0)
{
_cellList.Add(font2.Color, new List <string>()
{
st2
});
}
else
{
_cellList[font2.Color].Add(st2);
}
for (int i = 0; i < formattingRuns; i++)
{
int startIdx = rich.GetIndexOfFormattingRun(i);
int length;
if (i == formattingRuns - 1)
{
length = rich.Length - startIdx;
}
else
{
length = rich.GetIndexOfFormattingRun(i + 1) - startIdx;
}
string st = rich.String.Substring(startIdx, length);
short fontIndex = rich.GetFontOfFormattingRun(i);
IFont font = _workBook.GetFontAt(fontIndex);
if (_cellList.Keys.Where(t => t == font.Color).Count() == 0)
{
_cellList.Add(font.Color, new List <string>()
{
st
});
}
else
{
_cellList[font.Color].Add(st);
}
}
return(_cellList);
}
private void AnalysisCellString(HSSFCell cell)
{
string st3 = string.Empty;
HSSFRichTextString rich = (HSSFRichTextString)cell.RichStringCellValue;
int formattingRuns = cell.RichStringCellValue.NumFormattingRuns;
if (formattingRuns == 0)
{
return;
}
IFont font2 = _workBook.GetFontAt(cell.CellStyle.FontIndex);
string st2 = rich.String.Substring(0, rich.GetIndexOfFormattingRun(0));
if (font2.Color != IndexedColors.White.Index && font2.Color != 0)
{
st3 += st2;
}
for (int i = 0; i < formattingRuns; i++)
{
int startIdx = rich.GetIndexOfFormattingRun(i);
int length;
if (i == formattingRuns - 1)
{
length = rich.Length - startIdx;
}
else
{
length = rich.GetIndexOfFormattingRun(i + 1) - startIdx;
}
string st = rich.String.Substring(startIdx, length);
short fontIndex = rich.GetFontOfFormattingRun(i);
IFont font = _workBook.GetFontAt(fontIndex);
if (font.Color != IndexedColors.White.Index && font.Color != 0)
{
font2 = font;
st3 += st;
}
}
HSSFRichTextString rich2 = new HSSFRichTextString(st3);
rich2.ApplyFont(font2);
cell.SetCellValue(rich2);
}
public void TestSetDeafultTextFormat()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox1 = patriarch.CreateTextbox(new HSSFClientAnchor(0, 0, 0, 0, 1, 1, 3, 3)) as HSSFTextbox;
HSSFRichTextString rt1 = new HSSFRichTextString("Hello, World!");
Assert.AreEqual(0, rt1.NumFormattingRuns);
textbox1.String = (rt1);
HSSFRichTextString rt2 = (HSSFRichTextString)textbox1.String;
Assert.AreEqual(1, rt2.NumFormattingRuns);
Assert.AreEqual(HSSFRichTextString.NO_FONT, rt2.GetFontOfFormattingRun(0));
}
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;
}
}