public void TestCloneSheetBasic()
{
HSSFWorkbook b = new HSSFWorkbook();
ISheet s = b.CreateSheet("Test");
s.AddMergedRegion(new CellRangeAddress(0, 1, 0, 1));
ISheet clonedSheet = b.CloneSheet(0);
Assert.AreEqual(1, clonedSheet.NumMergedRegions, "One merged area");
b.Close();
}
public void SetUp()
{
IWorkbook wb = new HSSFWorkbook();
try
{
buildWorkbook(wb);
}
finally
{
wb.Close();
}
}
/// <summary>
/// 读取明源的报表
/// </summary>
/// <returns></returns>
private static List <Model> ReadRep(string change_File)
{
Model model;
int rowNum;
List <Model> lst = new List <Model>();
//从明源获取实收文件
// string file_old = @"c:\Users\1\Downloads\实收款项明细表.xls";
change_File = change_File.Split('.')[0] + ".xls";
using (FileStream fs_old = new FileStream(change_File, FileMode.Open, FileAccess.ReadWrite))
{
IWorkbook workbook_old = new NPOI.HSSF.UserModel.HSSFWorkbook(fs_old);
ISheet sheet_old = workbook_old.GetSheetAt(0);
rowNum = sheet_old.LastRowNum;
for (int i = 8; i < rowNum; i++)
{
model = new Model();
model.序号 = sheet_old.GetRow(i).GetCell(1).NumericCellValue.ToString();
model.项目名称 = sheet_old.GetRow(i).GetCell(2).StringCellValue;
model.楼栋名称 = sheet_old.GetRow(i).GetCell(5).StringCellValue;
// CellType ct = sheet_old.GetRow(i).GetCell(14).CellType;
model.房号 = sheet_old.GetRow(i).GetCell(9).StringCellValue;
model.客户名称 = sheet_old.GetRow(i).GetCell(11).StringCellValue;
model.收款日期 = sheet_old.GetRow(i).GetCell(14).DateCellValue;
model.票据类型 = sheet_old.GetRow(i).GetCell(17).StringCellValue;
model.票据编号 = sheet_old.GetRow(i).GetCell(18).StringCellValue;
model.款项类型 = sheet_old.GetRow(i).GetCell(19).StringCellValue;
model.款项名称 = sheet_old.GetRow(i).GetCell(20).StringCellValue;
model.金额 = sheet_old.GetRow(i).GetCell(22).NumericCellValue;
model.支付方式 = sheet_old.GetRow(i).GetCell(25).StringCellValue;
model.银付方式 = sheet_old.GetRow(i).GetCell(27).StringCellValue;
model.摘要 = sheet_old.GetRow(i).GetCell(26).StringCellValue;
lst.Add(model);
}
workbook_old.Close();
}
return(lst);
}
public void TestPageBreakClones()
{
HSSFWorkbook b = new HSSFWorkbook();
ISheet s = b.CreateSheet("Test");
s.SetRowBreak(3);
s.SetColumnBreak((short)6);
ISheet clone = b.CloneSheet(0);
Assert.IsTrue(clone.IsRowBroken(3), "Row 3 not broken");
Assert.IsTrue(clone.IsColumnBroken((short)6), "Column 6 not broken");
s.RemoveRowBreak(3);
Assert.IsTrue(clone.IsRowBroken(3), "Row 3 still should be broken");
b.Close();
}
public void TestFindComments()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
HSSFRow row = sh.CreateRow(5) as HSSFRow;
HSSFCell cell = row.CreateCell(4) as HSSFCell;
cell.CellComment = (comment);
HSSFTestModelHelper.CreateCommentShape(0, comment);
Assert.IsNotNull(sh.FindCellComment(5, 4));
Assert.IsNull(sh.FindCellComment(5, 5));
HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wbBack.GetSheetAt(0) as HSSFSheet;
Assert.IsNotNull(sh.FindCellComment(5, 4));
Assert.IsNull(sh.FindCellComment(5, 5));
wb.Close();
wbBack.Close();
}
public void TestParseStringLiterals_bug28754()
{
StringPtg sp;
try
{
sp = (StringPtg)ParseSingleToken("\"test\"\"ing\"", typeof(StringPtg));
}
catch (Exception e)
{
if (e.Message.StartsWith("Cannot Parse"))
{
throw new AssertionException("Identified bug 28754a");
}
throw e;
}
Assert.AreEqual("test\"ing", sp.Value);
HSSFWorkbook wb = new HSSFWorkbook();
try
{
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("right(\"test\"\"ing\", 3)");
String actualCellFormula = cell.CellFormula;
if ("RIGHT(\"test\"ing\",3)".Equals(actualCellFormula))
{
throw new AssertionException("Identified bug 28754b");
}
Assert.AreEqual("RIGHT(\"test\"\"ing\",3)", actualCellFormula);
}
finally
{
wb.Close();
}
}
public void TestMultiSheetReference()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("Cash_Flow");
wb.CreateSheet("Test Sheet");
HSSFSheet sheet = wb.CreateSheet("Test") as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(0) as HSSFCell;
String formula = null;
// References to a single cell:
// One sheet
cell.CellFormula = (/*setter*/"Cash_Flow!A1");
formula = cell.CellFormula;
Assert.AreEqual("Cash_Flow!A1", formula);
// Then the other
cell.CellFormula = (/*setter*/"\'Test Sheet\'!A1");
formula = cell.CellFormula;
Assert.AreEqual("\'Test Sheet\'!A1", formula);
// Now both
cell.CellFormula = (/*setter*/"Cash_Flow:\'Test Sheet\'!A1");
formula = cell.CellFormula;
Assert.AreEqual("Cash_Flow:\'Test Sheet\'!A1", formula);
// References to a range (area) of cells:
// One sheet
cell.CellFormula = ("Cash_Flow!A1:B2");
formula = cell.CellFormula;
Assert.AreEqual("Cash_Flow!A1:B2", formula);
// Then the other
cell.CellFormula = ("\'Test Sheet\'!A1:B2");
formula = cell.CellFormula;
Assert.AreEqual("\'Test Sheet\'!A1:B2", formula);
// Now both
cell.CellFormula = ("Cash_Flow:\'Test Sheet\'!A1:B2");
formula = cell.CellFormula;
Assert.AreEqual("Cash_Flow:\'Test Sheet\'!A1:B2", formula);
wb.Close();
}
public void TestNumbers()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("Cash_Flow");
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Test");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
String formula = null;
// starts from decimal point
cell.CellFormula = (".1");
formula = cell.CellFormula;
Assert.AreEqual("0.1", formula);
cell.CellFormula = ("+.1");
formula = cell.CellFormula;
Assert.AreEqual("0.1", formula);
cell.CellFormula = ("-.1");
formula = cell.CellFormula;
Assert.AreEqual("-0.1", formula);
// has exponent
cell.CellFormula = ("10E1");
formula = cell.CellFormula;
Assert.AreEqual("100", formula);
cell.CellFormula = ("10E+1");
formula = cell.CellFormula;
Assert.AreEqual("100", formula);
cell.CellFormula = ("10E-1");
formula = cell.CellFormula;
Assert.AreEqual("1", formula);
wb.Close();
}
public void TestShapeId()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
comment.ShapeId = 2024;
Assert.AreEqual(comment.ShapeId, 2024);
CommonObjectDataSubRecord cod = (CommonObjectDataSubRecord)comment.GetObjRecord().SubRecords[0];
Assert.AreEqual(2024, cod.ObjectId);
EscherSpRecord spRecord = (EscherSpRecord)comment.GetEscherContainer().GetChild(0);
Assert.AreEqual(2024, spRecord.ShapeId);
Assert.AreEqual(2024, comment.ShapeId);
Assert.AreEqual(2024, comment.NoteRecord.ShapeId);
wb.Close();
}
public void TestBooleanNamedSheet()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("true");
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellFormula = ("'true'!B2");
Assert.AreEqual("'true'!B2", cell.CellFormula);
wb.Close();
}
/// <summary>
/// 依据参数，选择生成SQL语言的方法。
/// </summary>
/// <param name="filesPath">文件完整的路径名,如D:\\test.xls</param>
/// <param name="filesTypes">文件类型，如*.xls将会引用NPOI组件</param>
/// <param name="sqlLangTypes">SQL语言类别，如Insert、Update、Delete、Up-Only</param>
/// <returns></returns>
public static void npoiPrintSQLLangTypesAndMethods(string filesPath, int filesTypes, int sqlLangTypes)
{
if(filesTypes == 2003)
{
#region //xls文件的处理
try
{
FileStream fs = new FileStream(filesPath, FileMode.Open);
HSSFWorkbook HBook = new HSSFWorkbook(fs);
ISheet isheet = HBook.GetSheetAt(FormMain.defaultTables);
#region //回传当前读取的Sheet表名！
FormMain.selectTableName = isheet.SheetName;
#endregion
switch (sqlLangTypes)
{
case 1:
npoiPrintSQLLangInsertMulti(isheet);
break;
case 2:
npoiPrintSQLLangDelete(isheet);
break;
case 3:
npoiPrintSQLLangUpdate(isheet);
break;
case 4:
npoiPrintSQLLangUpdateOnly(isheet);
break;
case 5:
npoiPrintSQLLangInsertEachLineASentence(isheet);
break;
default:
break;
}
//释放过程中使用的资源！
HBook.Close();
fs.Close();
FormMain.isSqlLangCreatedSuccessful = true;
}
catch (Exception ex)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
#endregion
}
else if (filesTypes == 2007) //*.XLSX
{
try
{
ExcelPackage excelPackage;
FileInfo newFile = new FileInfo(filesPath);
excelPackage = new ExcelPackage(newFile);
ExcelWorkbook myWorkbook = excelPackage.Workbook;
ExcelWorksheet myWorksheet = myWorkbook.Worksheets[FormMain.defaultTables + 1];
#region //回传当前读取的Sheet表名！
FormMain.selectTableName = myWorksheet.Name;
#endregion
switch (sqlLangTypes)
{
case 1:
excelPackagePrintSQLLangInsertMulti(myWorksheet);
break;
case 2:
excelPackagePrintSQLLangDelete(myWorksheet);
break;
case 3:
excelPackagePrintSQLLangUpdate(myWorksheet);
break;
case 4:
excelPackagePrintSQLLangUpdateOnly(myWorksheet);
break;
case 5:
excelPackagePrintSQLLangInsertEachLineASentence(myWorksheet);
break;
default:
break;
}
//貌似很有必要释放内存，不然没法连续执行，不关掉程序文档打不开。
excelPackage.Dispose();
FormMain.isSqlLangCreatedSuccessful = true;
}
catch (Exception ex)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
public void TestMissingArgPtg()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
ICell cell = wb.CreateSheet("Sheet1").CreateRow(4).CreateCell(0);
cell.CellFormula = ("IF(A1=\"A\",1,)");
}
finally
{
wb.Close();
}
}
public void TestComplexSheetRefs()
{
HSSFWorkbook sb = new HSSFWorkbook();
try
{
NPOI.SS.UserModel.ISheet s1 = sb.CreateSheet("Sheet a.1");
NPOI.SS.UserModel.ISheet s2 = sb.CreateSheet("Sheet.A");
s2.CreateRow(1).CreateCell(2).CellFormula = ("'Sheet a.1'!A1");
s1.CreateRow(1).CreateCell(2).CellFormula = ("'Sheet.A'!A1");
string tmpfile = TempFile.GetTempFilePath("TestComplexSheetRefs", ".xls");
FileStream fs = new FileStream(tmpfile, FileMode.Create);
try
{
sb.Write(fs);
}
finally
{
fs.Close();
}
}
finally
{
sb.Close();
}
}
public void TestFormulasWithUnderscore()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
IName nm1 = wb.CreateName();
nm1.NameName = ("_score1");
nm1.RefersToFormula = ("A1");
IName nm2 = wb.CreateName();
nm2.NameName = ("_score2");
nm2.RefersToFormula = ("A2");
ISheet sheet = wb.CreateSheet();
ICell cell = sheet.CreateRow(0).CreateCell(2);
cell.CellFormula = ("_score1*SUM(_score1+_score2)");
Assert.AreEqual("_score1*SUM(_score1+_score2)", cell.CellFormula);
}
finally
{
wb.Close();
}
}
public void TestResultEqualsToAbstractShape()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
HSSFRow row = sh.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(0) as HSSFCell;
cell.CellComment = (comment);
CommentShape commentShape = HSSFTestModelHelper.CreateCommentShape(1025, comment);
Assert.AreEqual(comment.GetEscherContainer().ChildRecords.Count, 5);
Assert.AreEqual(commentShape.SpContainer.ChildRecords.Count, 5);
//sp record
byte[] expected = commentShape.SpContainer.GetChild(0).Serialize();
byte[] actual = comment.GetEscherContainer().GetChild(0).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
expected = commentShape.SpContainer.GetChild(2).Serialize();
actual = comment.GetEscherContainer().GetChild(2).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
expected = commentShape.SpContainer.GetChild(3).Serialize();
actual = comment.GetEscherContainer().GetChild(3).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
expected = commentShape.SpContainer.GetChild(4).Serialize();
actual = comment.GetEscherContainer().GetChild(4).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
ObjRecord obj = comment.GetObjRecord();
ObjRecord objShape = commentShape.ObjRecord;
expected = obj.Serialize();
actual = objShape.Serialize();
Assert.AreEqual(expected.Length, actual.Length);
//assertArrayEquals(expected, actual);
TextObjectRecord tor = comment.GetTextObjectRecord();
TextObjectRecord torShape = commentShape.TextObjectRecord;
expected = tor.Serialize();
actual = torShape.Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
NoteRecord note = comment.NoteRecord;
NoteRecord noteShape = commentShape.NoteRecord;
expected = note.Serialize();
actual = noteShape.Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
wb.Close();
}
public void TestDefaultStyles()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFCellStyle style1 = (XSSFCellStyle)wb1.CreateCellStyle();
Assert.AreEqual(IndexedColors.Automatic.Index, style1.FillBackgroundColor);
Assert.IsNull(style1.FillBackgroundColorColor);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb1));
//compatibility with HSSF
HSSFWorkbook wb2 = new HSSFWorkbook();
HSSFCellStyle style2 = (HSSFCellStyle)wb2.CreateCellStyle();
Assert.AreEqual(style2.FillBackgroundColor, style1.FillBackgroundColor);
Assert.AreEqual(style2.FillForegroundColor, style1.FillForegroundColor);
Assert.AreEqual(style2.FillPattern, style1.FillPattern);
Assert.AreEqual(style2.LeftBorderColor, style1.LeftBorderColor);
Assert.AreEqual(style2.TopBorderColor, style1.TopBorderColor);
Assert.AreEqual(style2.RightBorderColor, style1.RightBorderColor);
Assert.AreEqual(style2.BottomBorderColor, style1.BottomBorderColor);
Assert.AreEqual(style2.BorderBottom, style1.BorderBottom);
Assert.AreEqual(style2.BorderLeft, style1.BorderLeft);
Assert.AreEqual(style2.BorderRight, style1.BorderRight);
Assert.AreEqual(style2.BorderTop, style1.BorderTop);
wb2.Close();
}
public void TestWorksheetReferences()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("NoQuotesNeeded");
wb.CreateSheet("Quotes Needed Here &#$@");
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Test");
IRow row = sheet.CreateRow(0);
ICell cell;
cell = row.CreateCell((short)0);
cell.CellFormula = ("NoQuotesNeeded!A1");
cell = row.CreateCell((short)1);
cell.CellFormula = ("'Quotes Needed Here &#$@'!A1");
wb.Close();
}
public void TestRangeOperator()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
ICell cell = sheet.CreateRow(0).CreateCell(0);
wb.SetSheetName(0, "Sheet1");
cell.CellFormula = ("Sheet1!B$4:Sheet1!$C1"); // explicit range ':' operator
Assert.AreEqual("Sheet1!B$4:Sheet1!$C1", cell.CellFormula);
cell.CellFormula = ("Sheet1!B$4:$C1"); // plain area ref
Assert.AreEqual("Sheet1!B1:$C$4", cell.CellFormula); // note - area ref is normalised
cell.CellFormula = ("Sheet1!$C1...B$4"); // different syntax for plain area ref
Assert.AreEqual("Sheet1!B1:$C$4", cell.CellFormula);
// with funny sheet name
wb.SetSheetName(0, "A1...A2");
cell.CellFormula = ("A1...A2!B1");
Assert.AreEqual("A1...A2!B1", cell.CellFormula);
wb.Close();
}
public void TestNamesWithUnderscore()
{
HSSFWorkbook wb = new HSSFWorkbook(); //or new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("NamesWithUnderscore");
IName nm;
nm = wb.CreateName();
nm.NameName = ("DA6_LEO_WBS_Number");
nm.RefersToFormula = ("33");
nm = wb.CreateName();
nm.NameName = ("DA6_LEO_WBS_Name");
nm.RefersToFormula = ("33");
nm = wb.CreateName();
nm.NameName = ("A1_");
nm.RefersToFormula = ("22");
nm = wb.CreateName();
nm.NameName = ("_A1");
nm.RefersToFormula = ("11");
nm = wb.CreateName();
nm.NameName = ("A_1");
nm.RefersToFormula = ("44");
nm = wb.CreateName();
nm.NameName = ("A_1_");
nm.RefersToFormula = ("44");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula = ("DA6_LEO_WBS_Number*2");
Assert.AreEqual("DA6_LEO_WBS_Number*2", cell.CellFormula);
cell.CellFormula = ("(A1_*_A1+A_1)/A_1_");
Assert.AreEqual("(A1_*_A1+A_1)/A_1_", cell.CellFormula);
cell.CellFormula = ("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))");
Assert.AreEqual("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))", cell.CellFormula);
wb.Close();
}
public void TestBackSlashInNames()
{
HSSFWorkbook wb = new HSSFWorkbook();
IName name = wb.CreateName();
name.NameName = ("POI\\2009");
name.RefersToFormula = ("Sheet1!$A$1");
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell_C1 = row.CreateCell(2);
cell_C1.CellFormula = ("POI\\2009");
Assert.AreEqual("POI\\2009", cell_C1.CellFormula);
ICell cell_D1 = row.CreateCell(2);
cell_D1.CellFormula = ("NOT(POI\\2009=\"3.5-final\")");
Assert.AreEqual("NOT(POI\\2009=\"3.5-final\")", cell_D1.CellFormula);
wb.Close();
}
public void TestInitState()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
EscherAggregate agg = HSSFTestHelper.GetEscherAggregate(patriarch);
Assert.AreEqual(agg.TailRecords.Count, 0);
HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
Assert.AreEqual(agg.TailRecords.Count, 1);
HSSFSimpleShape shape = patriarch.CreateSimpleShape(new HSSFClientAnchor());
Assert.IsNotNull(shape);
Assert.AreEqual(comment.GetOptRecord().EscherProperties.Count, 10);
wb.Close();
}
public void TestUnderscore()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("Cash_Flow");
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Test");
IRow row = sheet.CreateRow(0);
ICell cell;
cell = row.CreateCell((short)0);
cell.CellFormula = ("Cash_Flow!A1");
wb.Close();
}
public void TestCachedTypeChange()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheet1");
HSSFCell cell = (HSSFCell)sheet.CreateRow(0).CreateCell(0);
cell.CellFormula = ("A1");
cell.SetCellValue("abc");
ConfirmStringRecord(sheet, true);
cell.SetCellValue(123);
NPOI.HSSF.Record.Record[] recs = RecordInspector.GetRecords(sheet, 0);
if (recs.Length == 28 && recs[23] is StringRecord)
{
wb.Close();
throw new AssertionException("Identified bug - leftover StringRecord");
}
ConfirmStringRecord(sheet, false);
// string to error code
cell.SetCellValue("abc");
ConfirmStringRecord(sheet, true);
cell.SetCellErrorValue(FormulaError.REF.Code);
ConfirmStringRecord(sheet, false);
// string to boolean
cell.SetCellValue("abc");
ConfirmStringRecord(sheet, true);
cell.SetCellValue(false);
ConfirmStringRecord(sheet, false);
wb.Close();
}
public void TestExponentialInSheet()
{
// This Test depends on the american culture.
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("Cash_Flow");
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Test");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
String formula = null;
cell.CellFormula = ("1.3E21/3");
formula = cell.CellFormula;
Assert.AreEqual("1.3E+21/3", formula, "Exponential formula string");
cell.CellFormula = ("-1.3E21/3");
formula = cell.CellFormula;
Assert.AreEqual("-1.3E+21/3", formula, "Exponential formula string");
cell.CellFormula = ("1322E21/3");
formula = cell.CellFormula;
Assert.AreEqual("1.322E+24/3", formula, "Exponential formula string");
cell.CellFormula = ("-1322E21/3");
formula = cell.CellFormula;
Assert.AreEqual("-1.322E+24/3", formula, "Exponential formula string");
cell.CellFormula = ("1.3E1/3");
formula = cell.CellFormula;
Assert.AreEqual("13/3", formula, "Exponential formula string");
cell.CellFormula = ("-1.3E1/3");
formula = cell.CellFormula;
Assert.AreEqual("-13/3", formula, "Exponential formula string");
cell.CellFormula = ("1.3E-4/3");
formula = cell.CellFormula;
Assert.AreEqual("0.00013/3", formula, "Exponential formula string");
cell.CellFormula = ("-1.3E-4/3");
formula = cell.CellFormula;
Assert.AreEqual("-0.00013/3", formula, "Exponential formula string");
cell.CellFormula = ("13E-15/3");
formula = cell.CellFormula;
Assert.AreEqual("0.000000000000013/3", formula, "Exponential formula string");
cell.CellFormula = ("-13E-15/3");
formula = cell.CellFormula;
Assert.AreEqual("-0.000000000000013/3", formula, "Exponential formula string");
cell.CellFormula = ("1.3E3/3");
formula = cell.CellFormula;
Assert.AreEqual("1300/3", formula, "Exponential formula string");
cell.CellFormula = ("-1.3E3/3");
formula = cell.CellFormula;
Assert.AreEqual("-1300/3", formula, "Exponential formula string");
cell.CellFormula = ("1300000000000000/3");
formula = cell.CellFormula;
Assert.AreEqual("1300000000000000/3", formula, "Exponential formula string");
cell.CellFormula = ("-1300000000000000/3");
formula = cell.CellFormula;
Assert.AreEqual("-1300000000000000/3", formula, "Exponential formula string");
cell.CellFormula = ("-10E-1/3.1E2*4E3/3E4");
formula = cell.CellFormula;
Assert.AreEqual("-1/310*4000/30000", formula, "Exponential formula string");
wb.Close();
}
public void TestMaxTextLength()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
HSSFCell cell = (HSSFCell)sheet.CreateRow(0).CreateCell(0);
int maxlen = NPOI.SS.SpreadsheetVersion.EXCEL97.MaxTextLength;
Assert.AreEqual(32767, maxlen);
StringBuilder b = new StringBuilder();
// 32767 is okay
for (int i = 0; i < maxlen; i++)
{
b.Append("X");
}
cell.SetCellValue(b.ToString());
b.Append("X");
// 32768 produces an invalid XLS file
try
{
cell.SetCellValue(b.ToString());
Assert.Fail("Expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("The maximum length of cell contents (text) is 32,767 characters", e.Message);
}
wb.Close();
}
public void TestRanges()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet("Cash_Flow");
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Test");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
String formula = null;
cell.CellFormula = ("A1.A2");
formula = cell.CellFormula;
Assert.AreEqual("A1:A2", formula);
cell.CellFormula = ("A1..A2");
formula = cell.CellFormula;
Assert.AreEqual("A1:A2", formula);
cell.CellFormula = ("A1...A2");
formula = cell.CellFormula;
Assert.AreEqual("A1:A2", formula);
wb.Close();
}
public void TestCellType()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(0) as HSSFCell;
cell.SetCellType(CellType.Blank);
Assert.AreEqual("9999-12-31 23:59:59.999", cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff"));
Assert.IsFalse(cell.BooleanCellValue);
Assert.AreEqual("", cell.ToString());
cell.SetCellType(CellType.String);
Assert.AreEqual("", cell.ToString());
cell.SetCellType(CellType.String);
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellType(CellType.Boolean);
Assert.AreEqual("TRUE", cell.ToString());
cell.SetCellType(CellType.Boolean);
cell.SetCellValue("" + FormulaError.VALUE.String);
cell.SetCellType(CellType.Error);
Assert.AreEqual("#VALUE!", cell.ToString());
cell.SetCellType(CellType.Error);
cell.SetCellType(CellType.Boolean);
Assert.AreEqual("FALSE", cell.ToString());
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellType(CellType.Boolean);
cell.SetCellType(CellType.String);
cell.SetCellType(CellType.Error);
cell.SetCellType(CellType.String);
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
cell.SetCellType(CellType.String);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellValue((String)null);
cell.SetCellValue((IRichTextString)null);
wb.Close();
}
public void TestToFormulaStringZeroArgFunction()
{
HSSFWorkbook book = new HSSFWorkbook();
Ptg[] ptgs = {
FuncPtg.Create(10),
};
Assert.AreEqual("NA()", HSSFFormulaParser.ToFormulaString(book, ptgs));
book.Close();
}
public void TestCloneComment()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch p = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment c = p.CreateComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)5, 5));
c.Column=(1);
c.Row=(2);
c.String=(new HSSFRichTextString("qwertyuio"));
HSSFSheet sh2 = wb.CloneSheet(0) as HSSFSheet;
HSSFPatriarch p2 = sh2.DrawingPatriarch as HSSFPatriarch;
HSSFComment c2 = (HSSFComment)p2.Children[0];
Assert.AreEqual(c.String, c2.String);
Assert.AreEqual(c.Row, c2.Row);
Assert.AreEqual(c.Column, c2.Column);
// The ShapeId is not equal?
// assertEquals(c.getNoteRecord().getShapeId(), c2.getNoteRecord().getShapeId());
Assert.IsTrue(Arrays.Equals(c2.GetTextObjectRecord().Serialize(), c.GetTextObjectRecord().Serialize()));
// ShapeId is different
CommonObjectDataSubRecord subRecord = (CommonObjectDataSubRecord)c2.GetObjRecord().SubRecords[0];
subRecord.ObjectId = (1025);
Assert.IsTrue(Arrays.Equals(c2.GetObjRecord().Serialize(), c.GetObjRecord().Serialize()));
// ShapeId is different
c2.NoteRecord.ShapeId = (1025);
Assert.IsTrue(Arrays.Equals(c2.NoteRecord.Serialize(), c.NoteRecord.Serialize()));
//everything except spRecord.shapeId must be the same
Assert.IsFalse(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
EscherSpRecord sp = (EscherSpRecord)c2.GetEscherContainer().GetChild(0);
sp.ShapeId=(1025);
Assert.IsTrue(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
wb.Close();
}
public void TestSetFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
if ("SUM(A-32767:A-32766)".Equals(cell.CellFormula))
{
Assert.Fail("Identified bug 44539");
}
Assert.AreEqual("SUM(A32769:A32770)", cell.CellFormula);
wb.Close();
}
public void TestSetGetProperties()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
comment.String = new HSSFRichTextString("comment1");
Assert.AreEqual(comment.String.String, "comment1");
comment.Author = ("poi");
Assert.AreEqual(comment.Author, "poi");
comment.Column = (3);
Assert.AreEqual(comment.Column, 3);
comment.Row = (4);
Assert.AreEqual(comment.Row, 4);
comment.Visible = (false);
Assert.AreEqual(comment.Visible, false);
HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wbBack.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
comment = (HSSFComment)patriarch.Children[0];
Assert.AreEqual(comment.String.String, "comment1");
Assert.AreEqual("poi", comment.Author);
Assert.AreEqual(comment.Column, 3);
Assert.AreEqual(comment.Row, 4);
Assert.AreEqual(comment.Visible, false);
comment.String = new HSSFRichTextString("comment12");
comment.Author = ("poi2");
comment.Column = (32);
comment.Row = (42);
comment.Visible = (true);
HSSFWorkbook wbBack2 = HSSFTestDataSamples.WriteOutAndReadBack(wbBack);
sh = wbBack2.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
comment = (HSSFComment)patriarch.Children[0];
Assert.AreEqual(comment.String.String, "comment12");
Assert.AreEqual("poi2", comment.Author);
Assert.AreEqual(comment.Column, 32);
Assert.AreEqual(comment.Row, 42);
Assert.AreEqual(comment.Visible, true);
wb.Close();
wbBack.Close();
wbBack2.Close();
}