public void TestStdev()
{
IWorkbook wb = new HSSFWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.CreateSheet();
ICell a1 = sh.CreateRow(0).CreateCell(0);
a1.SetCellValue(1);
ICell a2 = sh.CreateRow(1).CreateCell(0);
a2.SetCellValue(3);
ICell a3 = sh.CreateRow(2).CreateCell(0);
a3.CellFormula = ("SUBTOTAL(7,A1:A2)");
ICell a4 = sh.CreateRow(3).CreateCell(0);
a4.SetCellValue(1);
ICell a5 = sh.CreateRow(4).CreateCell(0);
a5.SetCellValue(7);
ICell a6 = sh.CreateRow(5).CreateCell(0);
a6.CellFormula = ("SUBTOTAL(7,A1:A5)*2 + 2");
ICell a7 = sh.CreateRow(6).CreateCell(0);
a7.CellFormula = ("SUBTOTAL(7,A1:A6)");
fe.EvaluateAll();
Assert.AreEqual(1.41421, a3.NumericCellValue, 0.0001);
Assert.AreEqual(7.65685, a6.NumericCellValue, 0.0001);
Assert.AreEqual(2.82842, a7.NumericCellValue, 0.0001);
}
private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet)
{
var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2);
sheet.AddMergedRegion(merge);
var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
var caminho = $"{diretorioAtual}/Recursos/logoVLI.png";
byte[] data = ArquivosUtil.RetornarArquivo(caminho);
int pictureIndex = workbook.AddPicture(data, PictureType.JPEG);
ICreationHelper helper = workbook.GetCreationHelper();
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = helper.CreateClientAnchor();
anchor.Col1 = 1;
anchor.Row1 = 1;
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/
return(sheet);
}
public static void insertExcelData(string folderPath, string fileName, string[] args)
{
try
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCreationHelper createHelper = (HSSFCreationHelper)wb.GetCreationHelper();
using (FileStream file = new FileStream(folderPath + "\\" + fileName, FileMode.Open, FileAccess.Read))
{
wb = new HSSFWorkbook(file);
}
ISheet sheet = wb.GetSheet("219Option");
IRow row = sheet.CreateRow(sheet.LastRowNum + 1);
row.CreateCell(0).SetCellValue(createHelper.CreateRichTextString(args[1]));
row.CreateCell(1).SetCellValue(createHelper.CreateRichTextString(args[2]));
row.CreateCell(2).SetCellValue(createHelper.CreateRichTextString(args[3]));
using (FileStream file = new FileStream(folderPath + "\\" + fileName, FileMode.Create, FileAccess.Write))
{
wb.Write(file);
file.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("Occur error insertExcelData function : " + ex.ToString());
}
}
public void TestFunctionsFromTestSpreadsheet()
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("SubtotalsNested.xls");
ISheet sheet = workbook.GetSheetAt(0);
IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(10.0, sheet.GetRow(1).GetCell(1).NumericCellValue, "B2");
Assert.AreEqual(20.0, sheet.GetRow(2).GetCell(1).NumericCellValue, "B3");
//Test simple subtotal over one area
ICell cellA3 = sheet.GetRow(3).GetCell(1);
ConfirmExpectedResult(evaluator, "B4", cellA3, 30.0);
//Test existence of the second area
Assert.IsNotNull(sheet.GetRow(1).GetCell(2), "C2 must not be null");
Assert.AreEqual(7.0, sheet.GetRow(1).GetCell(2).NumericCellValue, "C2");
ICell cellC1 = sheet.GetRow(1).GetCell(3);
ICell cellC2 = sheet.GetRow(2).GetCell(3);
ICell cellC3 = sheet.GetRow(3).GetCell(3);
//Test Functions SUM, COUNT and COUNTA calculation of SUBTOTAL
//a) areas A and B are used
//b) first 2 subtotals don't consider the value of nested subtotal in A3
ConfirmExpectedResult(evaluator, "SUBTOTAL(SUM;B2:B8;C2:C8)", cellC1, 37.0);
ConfirmExpectedResult(evaluator, "SUBTOTAL(COUNT;B2:B8;C2:C8)", cellC2, 3.0);
ConfirmExpectedResult(evaluator, "SUBTOTAL(COUNTA;B2:B8;C2:C8)", cellC3, 5.0);
}
private HSSFWorkbook OpenXLSFile(string fileName)
{
retry:
try
{
string newFileName = Path.GetFullPath(Path.Combine(Directory.GetCurrentDirectory(), filePath + fileName));
FileStream file = new FileStream(newFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
HSSFWorkbook workbook = new HSSFWorkbook(file);
file.Close();
//WORKAROUND: Replace values for unsupported formulas @"Utils" worksheet with the exact formula stored as string
TempUtils(workbook.GetSheet("Utils"));
workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
return(workbook);
}
catch (IOException)
{
MessageBox.Show(new Form()
{
TopMost = true
}, "Please close the test case file: " + fileName + " and click on OK.", "File Access Conflict", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
goto retry;
}
}
public static void WriteDataToExcel(string path, IEnumerable <dynamic> list)
{
using (FileStream file = new FileStream(path, FileMode.Create, FileAccess.ReadWrite))
{
var wb = new HSSFWorkbook();
var sheet = wb.CreateSheet("Sheet1");
var ch = wb.GetCreationHelper();
int table = 1;
int seat = 1;
int i = 0;
foreach (var item in list)
{
var row = sheet.CreateRow(i);
var cell = row.CreateCell(0);
cell.SetCellValue(table);
cell = row.CreateCell(1);
cell.SetCellValue(seat);
if (seat == 10)
{
seat = 1;
table++;
}
else
{
seat++;
}
cell = row.CreateCell(2);
cell.SetCellValue(item.lanid);
cell = row.CreateCell(3);
cell.SetCellValue(item.name);
i++;
}
wb.Write(file);
}
}
public void TestAvg()
{
IWorkbook wb = new HSSFWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.CreateSheet();
ICell a1 = sh.CreateRow(1).CreateCell(1);
a1.SetCellValue(1);
ICell a2 = sh.CreateRow(2).CreateCell(1);
a2.SetCellValue(3);
ICell a3 = sh.CreateRow(3).CreateCell(1);
a3.CellFormula = ("SUBTOTAL(1,B2:B3)");
ICell a4 = sh.CreateRow(4).CreateCell(1);
a4.SetCellValue(1);
ICell a5 = sh.CreateRow(5).CreateCell(1);
a5.SetCellValue(7);
ICell a6 = sh.CreateRow(6).CreateCell(1);
a6.CellFormula = ("SUBTOTAL(1,B2:B6)*2 + 2");
ICell a7 = sh.CreateRow(7).CreateCell(1);
a7.CellFormula = ("SUBTOTAL(1,B2:B7)");
fe.EvaluateAll();
Assert.AreEqual(2.0, a3.NumericCellValue);
Assert.AreEqual(8.0, a6.NumericCellValue);
Assert.AreEqual(3.0, a7.NumericCellValue);
}
public void TestEvaluate()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sh = wb.CreateSheet();
IRow row1 = sh.CreateRow(0);
// Create cells
row1.CreateCell(0, CellType.String);
// Create references
CellReference a1Ref = new CellReference("A1");
// Set values
ICell cellA1 = sh.GetRow(a1Ref.Row).GetCell(a1Ref.Col);
ICell cell1 = row1.CreateCell(1);
cell1.CellFormula = "IFS(A1=\"A\", \"Value for A\", A1=\"B\",\"Value for B\")";
IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
cellA1.SetCellValue("A");
Assert.AreEqual(CellType.String, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric");
Assert.AreEqual("Value for A", evaluator.Evaluate(cell1).StringValue, "IFS should return 'Value for B'");
cellA1.SetCellValue("B");
evaluator.ClearAllCachedResultValues();
Assert.AreEqual(CellType.String, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric");
Assert.AreEqual("Value for B", evaluator.Evaluate(cell1).StringValue, "IFS should return 'Value for B'");
}
public void TestResultOutsideRange()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0);
cell.CellFormula = "D2:D5"; // IF(TRUE,D2:D5,D2) or OFFSET(D2:D5,0,0) would work too
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
CellValue cv;
try
{
cv = fe.Evaluate(cell);
}
catch (ArgumentException e)
{
if ("Specified row index (0) is outside the allowed range (1..4)".Equals(e.Message))
{
throw new AssertionException("Identified bug in result dereferencing");
}
throw;
}
Assert.AreEqual(CellType.Error, cv.CellType);
Assert.AreEqual(ErrorConstants.ERROR_VALUE, cv.ErrorValue);
// verify circular refs are still detected properly
fe.ClearAllCachedResultValues();
cell.CellFormula = "OFFSET(A1,0,0)";
cv = fe.Evaluate(cell);
Assert.AreEqual(CellType.Error, cv.CellType);
Assert.AreEqual(ErrorEval.CIRCULAR_REF_ERROR.ErrorCode, cv.ErrorValue);
}
public void TestCount()
{
IWorkbook wb = new HSSFWorkbook();
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.CreateSheet();
ICell a1 = sh.CreateRow(1).CreateCell(1);
a1.SetCellValue(1);
ICell a2 = sh.CreateRow(2).CreateCell(1);
a2.SetCellValue(3);
ICell a3 = sh.CreateRow(3).CreateCell(1);
a3.CellFormula = ("SUBTOTAL(2,B2:B3)");
ICell a4 = sh.CreateRow(4).CreateCell(1);
a4.SetCellValue("POI"); // A4 is string and not counted
ICell a5 = sh.CreateRow(5).CreateCell(1); // A5 is blank and not counted
ICell a6 = sh.CreateRow(6).CreateCell(1);
a6.CellFormula = ("SUBTOTAL(2,B2:B6)*2 + 2");
ICell a7 = sh.CreateRow(7).CreateCell(1);
a7.CellFormula = ("SUBTOTAL(2,B2:B7)");
fe.EvaluateAll();
Assert.AreEqual(2.0, a3.NumericCellValue);
Assert.AreEqual(6.0, a6.NumericCellValue);
Assert.AreEqual(2.0, a7.NumericCellValue);
}
/// <summary>
/// 导出xls结尾的Excel数据，每个实体必须含有ExcelColNameAttr
/// </summary>
/// <param name="dataList">集合类型数据，分多个工作簿</param>
/// <param name="outputStream">输出流</param>
/// <param name="groupName">组名称</param>
public static void ListToExcelXls <T>(List <T>[] dataListArrays, Stream outputStream, string groupName = null)
{
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
ICellStyle dateCellStyle = hssfWorkbook.CreateCellStyle();
ICreationHelper creationHelper = hssfWorkbook.GetCreationHelper();
dateCellStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat("yyyy-MM-dd hh:mm:ss");
foreach (List <T> dataList in dataListArrays)
{
if (dataList == null || dataList.Count == 0)
{
throw new Exception("数据列表为空，未能导出数据！");
}
List <ExcelColInfo> excelColInfoList = ColFilter(typeof(T), groupName);
if (excelColInfoList.Count == 0)
{
throw new Exception("字段列表为空，不能导出数据！");
}
HSSFSheet hssfSheet = (HSSFSheet)hssfWorkbook.CreateSheet(GetSheetName(typeof(T), groupName));
HSSFRow hssfRow = (HSSFRow)hssfSheet.CreateRow(0);
ExcelCol excelCol;
int[] colWidths = new int[excelColInfoList.Count];
int width;
foreach (ExcelColInfo excelColInfo in excelColInfoList)
{
excelCol = excelColInfo.ExcelCol;
width = SetCellValue(hssfRow.CreateCell(excelCol.ColIndex), excelCol.ColName);
if (width > colWidths[excelCol.ColIndex])
{
colWidths[excelCol.ColIndex] = width;
}
}
T temp;
for (int i = 0, len = dataList.Count; i < len; i++)
{
temp = dataList[i];
hssfRow = (HSSFRow)hssfSheet.CreateRow(i + 1);
foreach (ExcelColInfo excelColInfo in excelColInfoList)
{
excelCol = excelColInfo.ExcelCol;
width = SetCellValue(
(HSSFCell)hssfRow.CreateCell(excelCol.ColIndex),
excelColInfo.PropertyInfo.GetValue(temp),
dateCellStyle
);
if (width > colWidths[excelCol.ColIndex])
{
colWidths[excelCol.ColIndex] = width;
}
}
}
UpdateColWidth(hssfSheet, colWidths);
}
hssfWorkbook.Write(outputStream);
}
public void SetUp()
{
wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRandBetween.xls");
Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sheet = wb.CreateSheet("RandBetweenSheet");
IRow row = sheet.CreateRow(0);
bottomValueCell = row.CreateCell(0);
topValueCell = row.CreateCell(1);
formulaCell = row.CreateCell(2, CellType.Formula);
}
public void TestCallFunction_invalidArgs()
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("test");
IRow row1 = sheet.CreateRow(0);
ICell cellA1 = row1.CreateCell(0, CellType.Formula);
cellA1.CellFormula = (/*setter*/ "COUNTIFS()");
IFormulaEvaluator Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
CellValue Evaluate = Evaluator.Evaluate(cellA1);
Assert.AreEqual(15, Evaluate.ErrorValue);
cellA1.CellFormula = (/*setter*/ "COUNTIFS(A1:C1)");
Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
Evaluate = Evaluator.Evaluate(cellA1);
Assert.AreEqual(15, Evaluate.ErrorValue);
cellA1.SetCellFormula("COUNTIFS(A1:C1,2,2)");
Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
Evaluate = Evaluator.Evaluate(cellA1);
Assert.AreEqual(15, Evaluate.ErrorValue);
}
public void TestEvaluate()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sh = wb.CreateSheet();
IRow row1 = sh.CreateRow(0);
IRow row2 = sh.CreateRow(1);
// Create cells
row1.CreateCell(0, CellType.Numeric);
row1.CreateCell(1, CellType.Numeric);
row1.CreateCell(2, CellType.Numeric);
row2.CreateCell(0, CellType.Numeric);
row2.CreateCell(1, CellType.Numeric);
// Create references
CellReference a1 = new CellReference("A1");
CellReference a2 = new CellReference("A2");
CellReference b1 = new CellReference("B1");
CellReference b2 = new CellReference("B2");
CellReference c1 = new CellReference("C1");
// Set values
sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(210);
sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(55);
sh.GetRow(b1.Row).GetCell(b1.Col).SetCellValue(35);
sh.GetRow(b2.Row).GetCell(b2.Col).SetCellValue(0);
sh.GetRow(c1.Row).GetCell(c1.Col).SetCellFormula("A1/B2");
ICell cell1 = sh.CreateRow(3).CreateCell(0);
cell1.SetCellFormula("IFERROR(A1/B1,\"Error in calculation\")");
ICell cell2 = sh.CreateRow(3).CreateCell(0);
cell2.SetCellFormula("IFERROR(A2/B2,\"Error in calculation\")");
ICell cell3 = sh.CreateRow(3).CreateCell(0);
cell3.SetCellFormula("IFERROR(C1,\"error\")");
double accuracy = 1E-9;
IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(CellType.Numeric, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric");
Assert.AreEqual(6.0, evaluator.Evaluate(cell1).NumberValue, accuracy, "Divides 210 by 35 and returns 6.0");
Assert.AreEqual(CellType.String, evaluator.Evaluate(cell2).CellType, "Checks that the cell is numeric");
Assert.AreEqual("Error in calculation", evaluator.Evaluate(cell2).StringValue, "Rounds -10 to a nearest multiple of -3 (-9)");
Assert.AreEqual(CellType.String, evaluator.Evaluate(cell3).CellType, "Check that C1 returns string");
Assert.AreEqual("error", evaluator.Evaluate(cell3).StringValue, "Check that C1 returns string \"error\"");
}
}//解压结束
/// <summary>
/// 初始化导出表格样式
/// </summary>
/// LZ Add 2016-08-04
/// <param name="book">Excel文件</param>
/// <param name="param"></param>
/// <param name="paramComment">批注字典 注意:字典的key要与表头key一致</param>
/// <returns></returns>
public static ISheet CreateSheet(HSSFWorkbook book, Dictionary <string, int> param, Dictionary <string, string> paramComment, string sheetName = "Sheet1")
{
//添加一个sheet
ISheet sheet1 = book.CreateSheet(sheetName);
IRow row = sheet1.CreateRow(0);
//初始化样式
ICellStyle mStyle = book.CreateCellStyle();
mStyle.Alignment = HorizontalAlignment.Center;
mStyle.VerticalAlignment = VerticalAlignment.Center;
IFont mfont = book.CreateFont();
mfont.FontHeight = 10 * 20;
mStyle.SetFont(mfont);
HSSFPatriarch patr = sheet1.CreateDrawingPatriarch() as HSSFPatriarch;
NPOI.SS.UserModel.ICreationHelper facktory = book.GetCreationHelper();
HSSFComment comment = null;
NPOI.SS.UserModel.IClientAnchor anchor = facktory.CreateClientAnchor();
int i = 0;
foreach (var item in param)
{
//设置列宽
sheet1.SetColumnWidth(i, item.Value * 256);
sheet1.SetDefaultColumnStyle(i, mStyle);
row.CreateCell(i).SetCellValue(item.Key.ToString());
if (paramComment.ContainsKey(item.Key.ToString()))
{
//设置批注
anchor = facktory.CreateClientAnchor();
anchor.Col1 = row.GetCell(i).ColumnIndex;
anchor.Col2 = row.GetCell(i).ColumnIndex + 1;
anchor.Row1 = row.RowNum;
anchor.Row2 = row.RowNum + 3;
comment = patr.CreateCellComment(anchor) as HSSFComment;
comment.String = new HSSFRichTextString(paramComment[item.Key.ToString()].ToString());
comment.Author = ("CySoft");
row.GetCell(i).CellComment = (comment);
}
i++;
}
i = 0;
sheet1.GetRow(0).Height = 28 * 20;
return(sheet1);
}
public void TestOffsetWithEmpty23Arguments()
{
IWorkbook workbook = new HSSFWorkbook();
ICell cell = workbook.CreateSheet().CreateRow(0).CreateCell(0);
cell.SetCellFormula("OFFSET(B1,,)");
string value = "EXPECTED_VALUE";
ICell valueCell = cell.Row.CreateCell(1);
valueCell.SetCellValue(value);
workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
Assert.AreEqual(CellType.String, cell.CachedFormulaResultType);
Assert.AreEqual(value, cell.StringCellValue);
}
public void Test49658()
{
// test if inserted EscherMetafileBlip will be read again
IWorkbook wb = new HSSFWorkbook();
byte[] pictureDataEmf = POIDataSamples.GetDocumentInstance().ReadFile("vector_image.emf");
int indexEmf = wb.AddPicture(pictureDataEmf, PictureType.EMF);
byte[] pictureDataPng = POIDataSamples.GetSpreadSheetInstance().ReadFile("logoKarmokar4.png");
int indexPng = wb.AddPicture(pictureDataPng, PictureType.PNG);
byte[] pictureDataWmf = POIDataSamples.GetSlideShowInstance().ReadFile("santa.wmf");
int indexWmf = wb.AddPicture(pictureDataWmf, PictureType.WMF);
ISheet sheet = wb.CreateSheet();
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
ICreationHelper ch = wb.GetCreationHelper();
IClientAnchor anchor = ch.CreateClientAnchor();
anchor.Col1 = (/*setter*/ 2);
anchor.Col2 = (/*setter*/ 5);
anchor.Row1 = (/*setter*/ 1);
anchor.Row2 = (/*setter*/ 6);
patriarch.CreatePicture(anchor, indexEmf);
anchor = ch.CreateClientAnchor();
anchor.Col1 = (/*setter*/ 2);
anchor.Col2 = (/*setter*/ 5);
anchor.Row1 = (/*setter*/ 10);
anchor.Row2 = (/*setter*/ 16);
patriarch.CreatePicture(anchor, indexPng);
anchor = ch.CreateClientAnchor();
anchor.Col1 = (/*setter*/ 6);
anchor.Col2 = (/*setter*/ 9);
anchor.Row1 = (/*setter*/ 1);
anchor.Row2 = (/*setter*/ 6);
patriarch.CreatePicture(anchor, indexWmf);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook);
byte[] pictureDataOut = (wb.GetAllPictures()[0] as HSSFPictureData).Data;
Assert.IsTrue(Arrays.Equals(pictureDataEmf, pictureDataOut));
byte[] wmfNoHeader = new byte[pictureDataWmf.Length - 22];
Array.Copy(pictureDataWmf, 22, wmfNoHeader, 0, pictureDataWmf.Length - 22);
pictureDataOut = (wb.GetAllPictures()[2] as HSSFPictureData).Data;
Assert.IsTrue(Arrays.Equals(wmfNoHeader, pictureDataOut));
}
public void TestCountifBug51498()
{
int REF_COL = 4;
int EVAL_COL = 3;
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("51498.xls");
IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
ISheet sheet = workbook.GetSheetAt(0);
// numeric criteria
for (int i = 0; i < 8; i++)
{
CellValue expected = evaluator.Evaluate(sheet.GetRow(i).GetCell(REF_COL));
CellValue actual = evaluator.Evaluate(sheet.GetRow(i).GetCell(EVAL_COL));
Assert.AreEqual(expected.FormatAsString(), actual.FormatAsString());
}
// boolean criteria
for (int i = 0; i < 8; i++)
{
HSSFCell cellFmla = (HSSFCell)sheet.GetRow(i).GetCell(8);
HSSFCell cellRef = (HSSFCell)sheet.GetRow(i).GetCell(9);
double expectedValue = cellRef.NumericCellValue;
double actualValue = evaluator.Evaluate(cellFmla).NumberValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " +
new CellReference(cellFmla).FormatAsString() + "[" + cellFmla.CellFormula + "] ");
}
// string criteria
for (int i = 1; i < 9; i++)
{
ICell cellFmla = sheet.GetRow(i).GetCell(13);
ICell cellRef = sheet.GetRow(i).GetCell(14);
double expectedValue = cellRef.NumericCellValue;
double actualValue = evaluator.Evaluate(cellFmla).NumberValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " +
new CellReference(cellFmla).FormatAsString() + "[" + cellFmla.CellFormula + "] ");
}
}
public void TestNamesInFormulas()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IName name1 = wb.CreateName();
name1.NameName = "aConstant";
name1.RefersToFormula = "3.14";
IName name2 = wb.CreateName();
name2.NameName = "aFormula";
name2.RefersToFormula = "SUM(Sheet1!$A$1:$A$3)";
IName name3 = wb.CreateName();
name3.NameName = "aSet";
name3.RefersToFormula = "Sheet1!$A$2:$A$4";
IRow row0 = sheet.CreateRow(0);
IRow row1 = sheet.CreateRow(1);
IRow row2 = sheet.CreateRow(2);
IRow row3 = sheet.CreateRow(3);
row0.CreateCell(0).SetCellValue(2);
row1.CreateCell(0).SetCellValue(5);
row2.CreateCell(0).SetCellValue(3);
row3.CreateCell(0).SetCellValue(7);
row0.CreateCell(2).SetCellFormula("aConstant");
row1.CreateCell(2).SetCellFormula("aFormula");
row2.CreateCell(2).SetCellFormula("SUM(aSet)");
row3.CreateCell(2).SetCellFormula("aConstant+aFormula+SUM(aSet)");
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(3.14, fe.Evaluate(row0.GetCell(2)).NumberValue);
Assert.AreEqual(10.0, fe.Evaluate(row1.GetCell(2)).NumberValue);
Assert.AreEqual(15.0, fe.Evaluate(row2.GetCell(2)).NumberValue);
Assert.AreEqual(28.14, fe.Evaluate(row3.GetCell(2)).NumberValue);
}
public void TestMissingWorkbookMissing()
{
IFormulaEvaluator evaluator = mainWorkbook.GetCreationHelper().CreateFormulaEvaluator();
HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
HSSFRow lARow = (HSSFRow)lSheet.GetRow(0);
HSSFCell lA1Cell = (HSSFCell)lARow.GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
try
{
evaluator.EvaluateFormulaCell(lA1Cell);
Assert.Fail("Missing external workbook reference exception expected!");
}
catch (RuntimeException re)
{
Assert.IsTrue(re.Message.IndexOf(SOURCE_DUMMY_WORKBOOK_FILENAME) != -1, "Unexpected exception: " + re);
}
}
public void TestPlainValueCache()
{
HSSFWorkbook wb = new HSSFWorkbook();
int numberOfSheets = 4098; // Bug 51448 reported that Evaluation Cache got messed up After 256 sheets
IRow row;
ICell cell;
//create summary sheet
ISheet summary = wb.CreateSheet("summary");
wb.SetActiveSheet(wb.GetSheetIndex(summary));
//formula referring all sheets Created below
row = summary.CreateRow(0);
ICell summaryCell = row.CreateCell(0);
summaryCell.CellFormula = "SUM(A2:A" + (numberOfSheets + 2) + ")";
//create sheets with cells having (different) numbers
// and add a row to summary
for (int i = 1; i < numberOfSheets; i++)
{
ISheet sheet = wb.CreateSheet("new" + i);
row = sheet.CreateRow(0);
cell = row.CreateCell(0);
cell.SetCellValue(i);
row = summary.CreateRow(i);
cell = row.CreateCell(0);
cell.CellFormula = "new" + i + "!A1";
}
//calculate
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.EvaluateFormulaCell(summaryCell);
Assert.AreEqual(8394753.0, summaryCell.NumericCellValue);
}
//private static DataTable InitReimbursementSlipCoverDetail()
//{
// return dtVal;
//}
public static byte[] CreateVoucher(DataTable dt, string path, ref string msg)
{
msg = "";
byte[] data = null;
using (FileStream fs = new FileStream(path, FileMode.Open))
{
HSSFWorkbook wbHssf = new HSSFWorkbook(fs);
ISheet sheet = wbHssf.GetSheetAt(0);
sheet.ForceFormulaRecalculation = true;//
int index = 1;
foreach (DataRow r in dt.Rows)
{
IRow row = sheet.CreateRow(index);
foreach (DataColumn c in dt.Columns)
{
if (string.IsNullOrEmpty(r[c.ColumnName].ToString()))
{
continue;
}
int cIndex = Convert.ToInt32(c.ColumnName) - 1;
ICell cell = row.CreateCell(cIndex);
cell.SetCellValue(r[c.ColumnName].ToString());
}
index++;
}
//填完数据后，统一更新公式计算
wbHssf.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
using (MemoryStream ms = new MemoryStream())
{
wbHssf.Write(ms);
ms.Flush();
ms.Position = 0;
data = ms.GetBuffer();
}
wbHssf = null;
sheet = null;
}
return(data);
}
public void Test50209()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sh = wb.CreateSheet();
ICell a1 = sh.CreateRow(1).CreateCell(1);
a1.SetCellValue(1);
ICell a2 = sh.CreateRow(2).CreateCell(1);
a2.CellFormula = ("SUBTOTAL(9,B2)");
ICell a3 = sh.CreateRow(3).CreateCell(1);
a3.CellFormula = ("SUBTOTAL(9,B2:B3)");
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
fe.EvaluateAll();
Assert.AreEqual(1.0, a2.NumericCellValue);
Assert.AreEqual(1.0, a3.NumericCellValue);
}
public void TestCallFunction()
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("test");
IRow row1 = sheet.CreateRow(0);
ICell cellA1 = row1.CreateCell(0, CellType.Formula);
ICell cellB1 = row1.CreateCell(1, CellType.Numeric);
ICell cellC1 = row1.CreateCell(2, CellType.Numeric);
ICell cellD1 = row1.CreateCell(3, CellType.Numeric);
ICell cellE1 = row1.CreateCell(4, CellType.Numeric);
cellB1.SetCellValue(1);
cellC1.SetCellValue(1);
cellD1.SetCellValue(2);
cellE1.SetCellValue(4);
cellA1.SetCellFormula("COUNTIFS(B1:C1,1, D1:E1,2)");
IFormulaEvaluator Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
CellValue Evaluate = Evaluator.Evaluate(cellA1);
Assert.AreEqual(1.0d, Evaluate.NumberValue);
}
private static void TestPlainValueCache(HSSFWorkbook wb, int numberOfSheets)
{
IRow row;
ICell cell;
//create summary sheet
ISheet summary = wb.CreateSheet("summary");
wb.SetActiveSheet(wb.GetSheetIndex(summary));
//formula referring all sheets Created below
row = summary.CreateRow(0);
ICell summaryCell = row.CreateCell(0);
summaryCell.CellFormula = ("SUM(A2:A" + (numberOfSheets + 2) + ")");
//create sheets with cells having (different) numbers
// and add a row to summary
for (int i = 1; i < numberOfSheets; i++)
{
ISheet sheet = wb.CreateSheet("new" + i);
row = sheet.CreateRow(0);
cell = row.CreateCell(0);
cell.SetCellValue(i);
row = summary.CreateRow(i);
cell = row.CreateCell(0);
cell.CellFormula = ("new" + i + "!A1");
}
//calculate
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.EvaluateFormulaCell(summaryCell);
}
public void TestRefToBlankCellInArrayFormula()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cellA1 = row.CreateCell(0);
ICell cellB1 = row.CreateCell(1);
ICell cellC1 = row.CreateCell(2);
IRow row2 = sheet.CreateRow(1);
ICell cellA2 = row2.CreateCell(0);
ICell cellB2 = row2.CreateCell(1);
ICell cellC2 = row2.CreateCell(2);
IRow row3 = sheet.CreateRow(2);
ICell cellA3 = row3.CreateCell(0);
ICell cellB3 = row3.CreateCell(1);
ICell cellC3 = row3.CreateCell(2);
cellA1.SetCellValue("1");
// cell B1 intentionally left blank
cellC1.SetCellValue("3");
cellA2.SetCellFormula("A1");
cellB2.SetCellFormula("B1");
cellC2.SetCellFormula("C1");
sheet.SetArrayFormula("A1:C1", CellRangeAddress.ValueOf("A3:C3"));
wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
Assert.AreEqual(cellA2.StringCellValue, "1");
Assert.AreEqual(cellB2.NumericCellValue, 0, 0.00001);
Assert.AreEqual(cellC2.StringCellValue, "3");
Assert.AreEqual(cellA3.StringCellValue, "1");
Assert.AreEqual(cellB3.NumericCellValue, 0, 0.00001);
Assert.AreEqual(cellC3.StringCellValue, "3");
}
public void TestEvaluate()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sh = wb.CreateSheet();
ICell cell1 = sh.CreateRow(0).CreateCell(0);
cell1.CellFormula = ("MROUND(10, 3)");
ICell cell2 = sh.CreateRow(0).CreateCell(0);
cell2.CellFormula = ("MROUND(-10, -3)");
ICell cell3 = sh.CreateRow(0).CreateCell(0);
cell3.CellFormula = ("MROUND(1.3, 0.2)");
ICell cell4 = sh.CreateRow(0).CreateCell(0);
cell4.CellFormula = ("MROUND(5, -2)");
ICell cell5 = sh.CreateRow(0).CreateCell(0);
cell5.CellFormula = ("MROUND(5, 0)");
double accuracy = 1E-9;
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(9.0, Evaluator.Evaluate(cell1).NumberValue, accuracy, "Rounds 10 to a nearest multiple of 3 (9)");
Assert.AreEqual(-9.0, Evaluator.Evaluate(cell2).NumberValue, accuracy, "Rounds -10 to a nearest multiple of -3 (-9)");
Assert.AreEqual(1.4, Evaluator.Evaluate(cell3).NumberValue, accuracy, "Rounds 1.3 to a nearest multiple of 0.2 (1.4)");
Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, Evaluator.Evaluate(cell4).ErrorValue, "Returns an error, because -2 and 5 have different signs (#NUM!)");
Assert.AreEqual(0.0, Evaluator.Evaluate(cell5).NumberValue, "Returns 0 because the multiple is 0");
}
public static void createExcelFile(string folderPath, string fileName)
{
try
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("219Option");
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
HSSFCreationHelper createHelper = (HSSFCreationHelper)wb.GetCreationHelper();
row.CreateCell(0).SetCellValue(createHelper.CreateRichTextString("Option Name"));
row.CreateCell(1).SetCellValue(createHelper.CreateRichTextString("Input Value"));
row.CreateCell(2).SetCellValue(createHelper.CreateRichTextString("Output Value"));
using (FileStream file = new FileStream(folderPath + "\\" + fileName, FileMode.Create, FileAccess.Write))
{
wb.Write(file);
file.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("Occur error createExcelFile function : " + ex.ToString());
}
}
public void TestMissingWorkbookMissingOverride()
{
mainWorkbook = HSSFTestDataSamples.OpenSampleWorkbook(MAIN_WORKBOOK_FILENAME);
HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0);
HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0);
HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
Assert.AreEqual(CellType.Formula, lB1Cell.CellType);
Assert.AreEqual(CellType.Formula, lC1Cell.CellType);
HSSFFormulaEvaluator evaluator = (HSSFFormulaEvaluator)mainWorkbook.GetCreationHelper().CreateFormulaEvaluator();
evaluator.IgnoreMissingWorkbooks = (true);
Assert.AreEqual(CellType.Numeric, evaluator.EvaluateFormulaCell(lA1Cell));
Assert.AreEqual(CellType.String, evaluator.EvaluateFormulaCell(lB1Cell));
Assert.AreEqual(CellType.Boolean, evaluator.EvaluateFormulaCell(lC1Cell));
Assert.AreEqual(10.0d, lA1Cell.NumericCellValue, 0.00001d);
Assert.AreEqual("POI rocks!", lB1Cell.StringCellValue);
Assert.AreEqual(true, lC1Cell.BooleanCellValue);
}
public void TestAddPictures()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sh = wb.CreateSheet("Pictures");
IDrawing dr = sh.CreateDrawingPatriarch();
Assert.AreEqual(0, ((HSSFPatriarch)dr).Children.Count);
IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor();
//register a picture
byte[] data1 = new byte[] { 1, 2, 3 };
int idx1 = wb.AddPicture(data1, PictureType.JPEG);
Assert.AreEqual(1, idx1);
IPicture p1 = dr.CreatePicture(anchor, idx1);
Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)p1).PictureData.Data));
// register another one
byte[] data2 = new byte[] { 4, 5, 6 };
int idx2 = wb.AddPicture(data2, PictureType.JPEG);
Assert.AreEqual(2, idx2);
IPicture p2 = dr.CreatePicture(anchor, idx2);
Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count);
Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)p2).PictureData.Data));
// confirm that HSSFPatriarch.Children returns two picture shapes
Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data));
// Write, read back and verify that our pictures are there
wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb);
IList lst2 = wb.GetAllPictures();
Assert.AreEqual(2, lst2.Count);
Assert.IsTrue(Arrays.Equals(data1, (lst2[(0)] as HSSFPictureData).Data));
Assert.IsTrue(Arrays.Equals(data2, (lst2[(1)] as HSSFPictureData).Data));
// confirm that the pictures are in the Sheet's Drawing
sh = wb.GetSheet("Pictures");
dr = sh.CreateDrawingPatriarch();
Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count);
Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data));
// add a third picture
byte[] data3 = new byte[] { 7, 8, 9 };
// picture index must increment across Write-read
int idx3 = wb.AddPicture(data3, PictureType.JPEG);
Assert.AreEqual(3, idx3);
IPicture p3 = dr.CreatePicture(anchor, idx3);
Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)p3).PictureData.Data));
Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count);
Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data));
// write and read again
wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb);
IList lst3 = wb.GetAllPictures();
// all three should be there
Assert.AreEqual(3, lst3.Count);
Assert.IsTrue(Arrays.Equals(data1, (lst3[(0)] as HSSFPictureData).Data));
Assert.IsTrue(Arrays.Equals(data2, (lst3[(1)] as HSSFPictureData).Data));
Assert.IsTrue(Arrays.Equals(data3, (lst3[(2)] as HSSFPictureData).Data));
sh = wb.GetSheet("Pictures");
dr = sh.CreateDrawingPatriarch();
Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count);
// forth picture
byte[] data4 = new byte[] { 10, 11, 12 };
int idx4 = wb.AddPicture(data4, PictureType.JPEG);
Assert.AreEqual(4, idx4);
dr.CreatePicture(anchor, idx4);
Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count);
Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data));
wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb);
IList lst4 = wb.GetAllPictures();
Assert.AreEqual(4, lst4.Count);
Assert.IsTrue(Arrays.Equals(data1, (lst4[(0)] as HSSFPictureData).Data));
Assert.IsTrue(Arrays.Equals(data2, (lst4[(1)] as HSSFPictureData).Data));
Assert.IsTrue(Arrays.Equals(data3, (lst4[(2)] as HSSFPictureData).Data));
Assert.IsTrue(Arrays.Equals(data4, (lst4[(3)] as HSSFPictureData).Data));
sh = wb.GetSheet("Pictures");
dr = sh.CreateDrawingPatriarch();
Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count);
Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data));
Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data));
}
