public void TestEvaluateAllInWindow()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
s.CreateRow(1).CreateCell(1).CellFormula = (/*setter*/ "A1+10");
s.CreateRow(2).CreateCell(2).CellFormula = (/*setter*/ "B2+100");
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
eval.EvaluateAll();
Assert.AreEqual(3, (int)s.GetRow(0).GetCell(0).NumericCellValue);
Assert.AreEqual(13, (int)s.GetRow(1).GetCell(1).NumericCellValue);
Assert.AreEqual(113, (int)s.GetRow(2).GetCell(2).NumericCellValue);
wb.Close();
}
void SetCell(IWorkbook workbook, SXSSFSheet sheet, int r, int tor, int c, int toc, dynamic value, short fontSize = 11, bool isBorder = false, BorderStyle borderStyle = BorderStyle.Medium, bool isCenter = false, short?rowHeight = null, short?color = null)
{
var rang = new CellRangeAddress(r, tor, c, toc);
sheet.AddMergedRegion(rang);
var fff = sheet.GetType().GetField("_rows").GetValue(sheet);
var row = (fff as SortedDictionary <int, SXSSFRow>).ContainsKey(r) ? sheet.GetRow(r) : sheet.CreateRow(r);// sheet.GetRow(r) == null ? sheet.CreateRow(r) : sheet.GetRow(r);
if (rowHeight.HasValue)
{
row.Height = rowHeight.Value;
}
var cell = row.CreateCell(c);
if (value != null)
{
cell.SetCellValue(value);
}
var style = workbook.CreateCellStyle();
//设置颜色
if (color.HasValue)
{
style.FillForegroundColor = color.Value;
style.FillPattern = FillPattern.SolidForeground;
}
//设置字体
var font = workbook.CreateFont();
font.FontHeightInPoints = fontSize;
style.SetFont(font);
//设置边框
//if (isBorder)
//{
// style.BorderLeft = borderStyle;
// style.BorderRight = borderStyle;
// style.BorderTop = borderStyle;
// style.BorderBottom = borderStyle;
// for (int i = rang.FirstRow; i <= rang.LastRow; i++)
// {
// var borderRow = CellUtil.GetRow(i, sheet);
// for (int j = rang.FirstColumn; j <= rang.LastColumn; j++)
// {
// var singleCell = CellUtil.GetCell(borderRow, (short)j);
// singleCell.CellStyle = style;
// }
// }
//}
//else
//{
// cell.CellStyle = style;
//}
cell.CellStyle = style;
//设置内容居中
if (isCenter)
{
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
}
}