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 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; } }