public void TestWriteSheetFont()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
//ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color=(NPOI.HSSF.Util.HSSFColor.RED.index);
fnt.Boldweight=(short)FontBoldWeight.BOLD;
cs.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle=(cs);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
public void TestWriteSheetFont()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetFont",
".xls");
FileStream out1 = new FileStream(filepath,FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color=(NPOI.HSSF.Util.HSSFColor.Red.Index);
fnt.Boldweight= (short)FontBoldWeight.Bold;
cs.SetFont(fnt);
for (short rownum = ( short ) 0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
public void TestWriteSheetStyle()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetStyle",
".xls");
FileStream out1 = new FileStream(filepath,FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom= (BorderStyle.Thin);
cs.BorderLeft= (BorderStyle.Thin);
cs.BorderRight= (BorderStyle.Thin);
cs.BorderTop= (BorderStyle.Thin);
cs.FillForegroundColor= ( short ) 0xA;
cs.FillPattern = FillPattern.SolidForeground;
fnt.Color= ( short ) 0xf;
fnt.IsItalic= (true);
cs2.FillForegroundColor= ( short ) 0x0;
cs2.FillPattern= FillPattern.SolidForeground;
cs2.SetFont(fnt);
for (short rownum = ( short ) 0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
// r.SetRowNum(( short ) rownum);
for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
{
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
c.CellStyle = (cs);
c = r.CreateCell(cellnum + 1);
c.SetCellValue("TEST");
c.CellStyle = (cs2);
}
}
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
// assert((s.LastRowNum == 99));
}
public void TestDataStyle()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetStyleDate",
".xls");
FileStream out1 = new FileStream(filepath,FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
IRow row = s.CreateRow(0);
// with Date:
ICell cell = row.CreateCell(1);
cs.DataFormat=(HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cell.CellStyle = (cs);
cell.SetCellValue(DateTime.Now);
// with Calendar:
cell = row.CreateCell(2);
cs.DataFormat=(HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
cell.CellStyle = (cs);
cell.SetCellValue(DateTime.Now);
wb.Write(out1);
out1.Close();
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(0, s.LastRowNum, "LAST ROW ");
Assert.AreEqual(0, s.FirstRowNum,"FIRST ROW ");
}
public void TestDataStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
IRow row = s.CreateRow((short)0);
// with Date:
cs.DataFormat=(HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle=(cs);
row.CreateCell(0);
// with Calendar:
row = s.CreateRow((short)1);
cs.DataFormat= (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle=(cs);
row.CreateCell(0);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(1, s.LastRowNum, "LAST ROW ");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
}
public void TestNamedRange()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls");
//Creating new Named Range
IName newNamedRange = wb.CreateName();
//Getting Sheet Name for the reference
String sheetName = wb.GetSheetName(0);
//Setting its name
newNamedRange.NameName = "RangeTest";
//Setting its reference
newNamedRange.RefersToFormula = sheetName + "!$D$4:$E$8";
//Getting NAmed Range
IName namedRange1 = wb.GetNameAt(0);
//Getting it sheet name
sheetName = namedRange1.SheetName;
// sanity check
SanityChecker c = new SanityChecker();
c.CheckHSSFWorkbook(wb);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
IName nm = wb.GetNameAt(wb.GetNameIndex("RangeTest"));
Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName);
Assert.AreEqual(wb.GetSheetName(0) + "!$D$4:$E$8", nm.RefersToFormula);
}
public void TestWriteSheetStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom=(BorderStyle.THIN);
cs.BorderLeft=(BorderStyle.THIN);
cs.BorderRight=(BorderStyle.THIN);
cs.BorderTop=(BorderStyle.THIN);
cs.FillForegroundColor=((short)0xA);
cs.FillPattern = FillPatternType.SOLID_FOREGROUND;
fnt.Color=((short)0xf);
fnt.IsItalic=(true);
cs2.FillForegroundColor=((short)0x0);
cs2.FillPattern = FillPatternType.SOLID_FOREGROUND;
cs2.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle=(cs);
r.CreateCell(0);
rownum++;
if (rownum >= 100) break; // I feel too lazy to Check if this isreqd :-/
r = s.CreateRow(rownum);
r.RowStyle=(cs2);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
s = wb.GetSheetAt(0);
Assert.IsNotNull(s, "Sheet is not null");
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.GetRow(rownum);
Assert.IsNotNull(r, "Row is not null");
cs = r.RowStyle;
Assert.AreEqual(cs.BorderBottom, BorderStyle.THIN, "FillForegroundColor for row: ");
Assert.AreEqual(cs.BorderLeft, BorderStyle.THIN, "FillPattern for row: ");
Assert.AreEqual(cs.BorderRight, BorderStyle.THIN, "FillForegroundColor for row: ");
Assert.AreEqual(cs.BorderTop, BorderStyle.THIN, "FillPattern for row: ");
Assert.AreEqual(cs.FillForegroundColor, 0xA, "FillForegroundColor for row: ");
Assert.AreEqual((short)cs.FillPattern, (short)0x1, "FillPattern for row: ");
rownum++;
if (rownum >= 100) break; // I feel too lazy to Check if this isreqd :-/
r = s.GetRow(rownum);
Assert.IsNotNull(r, "Row is not null");
cs2 = r.RowStyle;
Assert.AreEqual(cs2.FillForegroundColor, (short)0x0, "FillForegroundColor for row: ");
Assert.AreEqual((short)cs2.FillPattern, (short)0x1, "FillPattern for row: ");
}
}
public void TestCheckRecordOrder()
{
SanityChecker c = new SanityChecker();
ArrayList records = new ArrayList();
records.Add(new BOFRecord());
records.Add(INTERFACEHDR);
records.Add(CreateBoundSheetRec());
records.Add(EOFRecord.instance);
TestSanityChecker.check = new SanityChecker.CheckRecord[] {
new SanityChecker.CheckRecord(typeof(BOFRecord), '1'),
new SanityChecker.CheckRecord(typeof(InterfaceHdrRecord), '0'),
new SanityChecker.CheckRecord(typeof(BoundSheetRecord), 'M'),
new SanityChecker.CheckRecord(typeof(NameRecord), '*'),
new SanityChecker.CheckRecord(typeof(EOFRecord), '1'),
};
// Check pass
c.CheckRecordOrder(records, check);
records.Insert(2, CreateBoundSheetRec());
c.CheckRecordOrder(records, check);
records.RemoveAt(1); // optional record missing
c.CheckRecordOrder(records, check);
records.Insert(3, new NameRecord());
records.Insert(3, new NameRecord()); // optional multiple record occurs more than one time
c.CheckRecordOrder(records, check);
// Check Assert.Fail
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
CreateBoundSheetRec(),
INTERFACEHDR,
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
INTERFACEHDR,
CreateBoundSheetRec(),
INTERFACEHDR,
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
CreateBoundSheetRec(),
new NameRecord(),
EOFRecord.instance,
new NameRecord(),
});
ConfirmBadRecordOrder(check, new Record[] {
INTERFACEHDR,
CreateBoundSheetRec(),
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
INTERFACEHDR,
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
INTERFACEHDR,
CreateBoundSheetRec(),
new BOFRecord(),
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
CreateBoundSheetRec(),
INTERFACEHDR,
EOFRecord.instance,
});
}
public void TestWriteSheetStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom = (BorderStyle.THIN);
cs.BorderLeft = (BorderStyle.THIN);
cs.BorderRight = (BorderStyle.THIN);
cs.BorderTop = (BorderStyle.THIN);
cs.FillForegroundColor = ((short)0xA);
cs.FillPattern = FillPatternType.SOLID_FOREGROUND;
fnt.Color = ((short)0xf);
fnt.IsItalic = (true);
cs2.FillForegroundColor = ((short)0x0);
cs2.FillPattern = FillPatternType.SOLID_FOREGROUND;
cs2.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle = (cs);
r.CreateCell(0);
rownum++;
if (rownum >= 100)
{
break; // I feel too lazy to Check if this isreqd :-/
}
r = s.CreateRow(rownum);
r.RowStyle = (cs2);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
s = wb.GetSheetAt(0);
Assert.IsNotNull(s, "Sheet is not null");
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.GetRow(rownum);
Assert.IsNotNull(r, "Row is not null");
cs = r.RowStyle;
Assert.AreEqual(cs.BorderBottom, BorderStyle.THIN, "FillForegroundColor for row: ");
Assert.AreEqual(cs.BorderLeft, BorderStyle.THIN, "FillPattern for row: ");
Assert.AreEqual(cs.BorderRight, BorderStyle.THIN, "FillForegroundColor for row: ");
Assert.AreEqual(cs.BorderTop, BorderStyle.THIN, "FillPattern for row: ");
Assert.AreEqual(cs.FillForegroundColor, 0xA, "FillForegroundColor for row: ");
Assert.AreEqual((short)cs.FillPattern, (short)0x1, "FillPattern for row: ");
rownum++;
if (rownum >= 100)
{
break; // I feel too lazy to Check if this isreqd :-/
}
r = s.GetRow(rownum);
Assert.IsNotNull(r, "Row is not null");
cs2 = r.RowStyle;
Assert.AreEqual(cs2.FillForegroundColor, (short)0x0, "FillForegroundColor for row: ");
Assert.AreEqual((short)cs2.FillPattern, (short)0x1, "FillPattern for row: ");
}
}
public void TestCheckRecordOrder()
{
SanityChecker c = new SanityChecker();
ArrayList records = new ArrayList();
records.Add(new BOFRecord());
records.Add(INTERFACEHDR);
records.Add(CreateBoundSheetRec());
records.Add(EOFRecord.instance);
TestSanityChecker.check = new SanityChecker.CheckRecord[]{
new SanityChecker.CheckRecord(typeof(BOFRecord), '1'),
new SanityChecker.CheckRecord(typeof(InterfaceHdrRecord), '0'),
new SanityChecker.CheckRecord(typeof(BoundSheetRecord), 'M'),
new SanityChecker.CheckRecord(typeof(NameRecord), '*'),
new SanityChecker.CheckRecord(typeof(EOFRecord), '1'),
};
// Check pass
c.CheckRecordOrder(records, check);
records.Insert(2, CreateBoundSheetRec());
c.CheckRecordOrder(records, check);
records.RemoveAt(1); // optional record missing
c.CheckRecordOrder(records, check);
records.Insert(3, new NameRecord());
records.Insert(3, new NameRecord()); // optional multiple record occurs more than one time
c.CheckRecordOrder(records, check);
// Check Assert.Fail
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
CreateBoundSheetRec(),
INTERFACEHDR,
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
INTERFACEHDR,
CreateBoundSheetRec(),
INTERFACEHDR,
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
CreateBoundSheetRec(),
new NameRecord(),
EOFRecord.instance,
new NameRecord(),
});
ConfirmBadRecordOrder(check, new Record[] {
INTERFACEHDR,
CreateBoundSheetRec(),
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
INTERFACEHDR,
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
INTERFACEHDR,
CreateBoundSheetRec(),
new BOFRecord(),
EOFRecord.instance,
});
ConfirmBadRecordOrder(check, new Record[] {
new BOFRecord(),
CreateBoundSheetRec(),
INTERFACEHDR,
EOFRecord.instance,
});
}
private static void ConfirmBadRecordOrder(SanityChecker.CheckRecord[] Check, Record[] recs)
{
TestSanityChecker.recs = recs;
ThreadStart ts = new ThreadStart(Run);
Thread thread = new Thread(ts);
thread.Start();
}
static void Run()
{
try
{
SanityChecker c = new SanityChecker();
IList recs1 = NPOI.Util.Arrays.AsList(recs);
c.CheckRecordOrder(recs1, check);
}
catch (AssertionException)
{
// expected during normal Test
return;
}
throw new AssertionException("Did not get Assert.Failure exception as expected");
}
