public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
IFont f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
s.SetFont(f);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
// Optimise fonts
HSSFOptimiser.OptimiseFonts(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
// Optimise styles
HSSFOptimiser.OptimiseCellStyles(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
}
public void TestOptimiseStylesCheckActualStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
HSSFCellStyle cs1 = (HSSFCellStyle)wb.CreateCellStyle();
cs1.BorderBottom = (BorderStyle.Thick);
HSSFCellStyle cs2 = (HSSFCellStyle)wb.CreateCellStyle();
cs2.BorderBottom = (BorderStyle.DashDot);
HSSFCellStyle cs3 = (HSSFCellStyle)wb.CreateCellStyle(); // = cs1
cs3.BorderBottom = (BorderStyle.Thick);
Assert.AreEqual(24, wb.NumCellStyles);
// Use them
HSSFSheet s = (HSSFSheet)wb.CreateSheet();
HSSFRow r = (HSSFRow)s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
Assert.AreEqual(23, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(23, wb.NumCellStyles);
Assert.AreEqual(BorderStyle.Thick, r.GetCell(0).CellStyle.BorderBottom);
Assert.AreEqual(BorderStyle.DashDot, r.GetCell(1).CellStyle.BorderBottom);
Assert.AreEqual(BorderStyle.Thick, r.GetCell(2).CellStyle.BorderBottom);
}
public void TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
// New files start with 4 built in fonts, and 21 built in styles
Assert.AreEqual(4, wb.NumberOfFonts);
Assert.AreEqual(21, wb.NumCellStyles);
// Create a test font and style, and use them
IFont f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
s.SetFont(f);
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.CreateCell(0).CellStyle = (s);
// Should have one more than the default of each
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
// Optimise fonts
HSSFOptimiser.OptimiseFonts(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
// Optimise styles
HSSFOptimiser.OptimiseCellStyles(wb);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(22, wb.NumCellStyles);
Assert.AreEqual(f, s.GetFont(wb));
}
public void TestOptimiseStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
Assert.AreEqual(4, wb.NumberOfFonts);
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
Assert.AreEqual(6, wb.NumberOfFonts);
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f2);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f1);
cs4.Alignment = HorizontalAlignment.CenterSelection;// ((short)22);
NPOI.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle();
cs5.SetFont(f2);
cs5.Alignment = HorizontalAlignment.Fill; //((short)111);
NPOI.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle();
cs6.SetFont(f2);
Assert.AreEqual(27, wb.NumCellStyles);
// Use them
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
r.CreateCell(3).CellStyle = (cs4);
r.CreateCell(4).CellStyle = (cs5);
r.CreateCell(5).CellStyle = (cs6);
r.CreateCell(6).CellStyle = (cs1);
r.CreateCell(7).CellStyle = (cs2);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(6, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
Assert.AreEqual(22, r.GetCell(1).CellStyle.GetFont(wb).FontHeight);
// cs3 = cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// cs4 --> 24 -> 23
Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex);
// cs5 --> 25 -> 24
Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex);
// cs6 = cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex);
// Add a new duplicate, and two that aren't used
HSSFCellStyle csD = (HSSFCellStyle)wb.CreateCellStyle();
csD.SetFont(f1);
r.CreateCell(8).CellStyle = (csD);
HSSFFont f3 = (HSSFFont)wb.CreateFont();
f3.FontHeight = ((short)23);
f3.FontName = ("Testing 3");
HSSFFont f4 = (HSSFFont)wb.CreateFont();
f4.FontHeight = ((short)24);
f4.FontName = ("Testing 4");
HSSFCellStyle csU1 = (HSSFCellStyle)wb.CreateCellStyle();
csU1.SetFont(f3);
HSSFCellStyle csU2 = (HSSFCellStyle)wb.CreateCellStyle();
csU2.SetFont(f4);
// Check before the optimise
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(28, wb.NumCellStyles);
// Optimise, should remove the two un-used ones and the one duplicate
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// csD -> cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(8)).CellValueRecord.XFIndex);
}
public void TestOptimiseStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
Assert.AreEqual(4, wb.NumberOfFonts);
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
Assert.AreEqual(6, wb.NumberOfFonts);
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f2);
NPOI.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f1);
NPOI.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f1);
cs4.Alignment = HorizontalAlignment.CENTER_SELECTION;// ((short)22);
NPOI.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle();
cs5.SetFont(f2);
cs5.Alignment = HorizontalAlignment.FILL; //((short)111);
NPOI.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle();
cs6.SetFont(f2);
Assert.AreEqual(27, wb.NumCellStyles);
// Use them
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
r.CreateCell(3).CellStyle = (cs4);
r.CreateCell(4).CellStyle = (cs5);
r.CreateCell(5).CellStyle = (cs6);
r.CreateCell(6).CellStyle = (cs1);
r.CreateCell(7).CellStyle = (cs2);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(6, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
// cs3 = cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// cs4 --> 24 -> 23
Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex);
// cs5 --> 25 -> 24
Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex);
// cs6 = cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex);
}
