public void SetUp()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
HSSFSheet sheet = wb.CreateSheet("new sheet") as HSSFSheet;
cell11 = sheet.CreateRow(0).CreateCell(0) as HSSFCell;
cell11.SetCellType(CellType.Formula);
Evaluator = new HSSFFormulaEvaluator(wb);
}
finally
{
//wb.Close();
}
}
private 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 NPOI.SS.UserModel.CellType.STRING:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case NPOI.SS.UserModel.CellType.NUMERIC:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case NPOI.SS.UserModel.CellType.BLANK:
newCell.SetCellType(NPOI.SS.UserModel.CellType.BLANK);
break;
case NPOI.SS.UserModel.CellType.BOOLEAN:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case NPOI.SS.UserModel.CellType.ERROR:
newCell.SetCellValue(oldCell.ErrorCellValue);
break;
case NPOI.SS.UserModel.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)
{
try
{
newCell.SetCellType(CellType.STRING);
newCell.SetCellValue(oldCell.StringCellValue);
}
catch (Exception exInner)
{
}
}
}
break;
default:
break;
}
}
protected void WriteCellTypeValue(DateTime Value, HSSFCell cell, bool hasStyle)
{
cell.SetCellType(CellType.NUMERIC);
if(!hasStyle)
cell.CellStyle = _dateStyle;
cell.SetCellValue(Value);
}
protected void WriteCellTypeValue(double Value, HSSFCell cell, bool hasStyle)
{
cell.SetCellType(CellType.NUMERIC);
cell.SetCellValue(Value);
}
private static void SetCellType(HSSFCell cell, CellValue cv)
{
int cellType = cv.CellType;
switch (cellType)
{
case HSSFCell.CELL_TYPE_BOOLEAN:
case HSSFCell.CELL_TYPE_ERROR:
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_STRING:
cell.SetCellType(cellType);
return;
case HSSFCell.CELL_TYPE_BLANK:
// never happens - blanks eventually get translated to zero
break;
case HSSFCell.CELL_TYPE_FORMULA:
// this will never happen, we have already evaluated the formula
break;
}
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
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)
{
newCell.SetCellType(CellType.String);
newCell.SetCellValue(oldCell.ToString());
}
}
break;
default:
break;
}
}