public void TestCloneStyleSameWB()
{
HSSFWorkbook wb = new HSSFWorkbook();
Font fnt = wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(5, wb.NumberOfFonts);
NPOI.SS.UserModel.CellStyle orig = wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.RIGHT);
orig.SetFont(fnt);
orig.DataFormat = ((short)18);
Assert.AreEqual(HorizontalAlignment.RIGHT, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont(wb));
Assert.AreEqual(18, orig.DataFormat);
NPOI.SS.UserModel.CellStyle clone = wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont(wb));
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont(wb));
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(5, wb.NumberOfFonts);
}
public void TestWriteSheetFont()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
Font fnt = wb.CreateFont();
NPOI.SS.UserModel.CellStyle 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 TestDoesNoHarmIfNothingToDo()
{
HSSFWorkbook wb = new HSSFWorkbook();
Font f = wb.CreateFont();
f.FontName = ("Testing");
NPOI.SS.UserModel.CellStyle 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));
}
/// <summary>
/// Creates the excel workbook.
/// </summary>
/// <param name="subject">The subject.</param>
public void CreateWorkbook(string subject)
{
//Creating the excel workbook
NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
//Creating summary information to the document
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Thoris";
//Applying summary information to the document
wb.DocumentSummaryInformation = dsi;
//Creating summary information for the data
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = subject;
//Applying summary information to the data
wb.SummaryInformation = si;
_workbook = wb;
//Creating the current styles
Font font = _workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle();
cellStyle.SetFont(font);
_headerStyle = CreateHeaderStyle(_workbook);
_dataStyle = CreateStyle(_workbook, false);
}
private static NPOI.SS.UserModel.CellStyle CreateStyle(HSSFWorkbook wb, HorizontalAlignment h_align, short color,
bool bold)
{
Font font = wb.CreateFont();
if (bold)
{
font.Boldweight = (short)FontBoldWeight.BOLD;
}
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.SetFont(font);
cellStyle.FillForegroundColor = (color);
cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
cellStyle.Alignment = (h_align);
cellStyle.BorderLeft = (CellBorderType.THIN);
cellStyle.LeftBorderColor = (HSSFColor.BLACK.index);
cellStyle.BorderTop = (CellBorderType.THIN);
cellStyle.TopBorderColor = (HSSFColor.BLACK.index);
cellStyle.BorderRight = (CellBorderType.THIN);
cellStyle.RightBorderColor = (HSSFColor.BLACK.index);
cellStyle.BorderBottom = (CellBorderType.THIN);
cellStyle.BottomBorderColor = (HSSFColor.BLACK.index);
return(cellStyle);
}
/// <summary>
/// Creates the header style.
/// </summary>
/// <param name="wb">The wb.</param>
/// <returns></returns>
public static NPOI.SS.UserModel.CellStyle CreateHeaderStyle(HSSFWorkbook wb)
{
Font font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.SetFont(font);
return(cellStyle);
}
public void TestWriteSheetStyle()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetStyle",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
Font fnt = wb.CreateFont();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom = (CellBorderType.THIN);
cs.BorderLeft = (CellBorderType.THIN);
cs.BorderRight = (CellBorderType.THIN);
cs.BorderTop = (CellBorderType.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.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));
}
/// <summary>
/// Creates the style.
/// </summary>
/// <param name="wb">The wb.</param>
/// <param name="bold">if set to <c>true</c> [bold].</param>
/// <returns></returns>
public static NPOI.SS.UserModel.CellStyle CreateStyle(HSSFWorkbook wb, bool bold)
{
Font font = wb.CreateFont();
if (bold)
{
font.Boldweight = (short)FontBoldWeight.BOLD;
}
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.SetFont(font);
return(cellStyle);
}
public void TestWriteSheetFont()
{
string filepath = TempFile.GetTempFilePath("TestWriteSheetFont",
".xls");
FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Cell c = null;
Font fnt = wb.CreateFont();
NPOI.SS.UserModel.CellStyle 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 TestFormulaStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet("TestSheet1");
Row r = null;
Cell c = null;
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
Font f = wb.CreateFont();
f.FontHeightInPoints = ((short)20);
f.Color = (HSSFColor.RED.index);
f.Boldweight = (short)FontBoldWeight.BOLD;
f.FontName = ("Arial Unicode MS");
cs.FillBackgroundColor = ((short)3);
cs.SetFont(f);
cs.BorderTop = CellBorderType.THIN;
cs.BorderRight = CellBorderType.THIN;
cs.BorderLeft = CellBorderType.THIN;
cs.BorderBottom = CellBorderType.THIN;
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellStyle = (cs);
c.CellFormula = ("2*3");
wb = WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue((c.CellType == NPOI.SS.UserModel.CellType.FORMULA), "Formula Cell at 0,0");
cs = c.CellStyle;
Assert.IsNotNull(cs, "Formula Cell Style");
Assert.AreEqual(cs.FontIndex, f.Index, "Font Index Matches");
Assert.AreEqual((short)cs.BorderTop, (short)1, "Top Border");
Assert.AreEqual((short)cs.BorderLeft, (short)1, "Left Border");
Assert.AreEqual((short)cs.BorderRight, (short)1, "Right Border");
Assert.AreEqual((short)cs.BorderBottom, (short)1, "Bottom Border");
}
public void TestCloneStyleDiffWB()
{
HSSFWorkbook wbOrig = new HSSFWorkbook();
Font fnt = wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(5, wbOrig.NumberOfFonts);
DataFormat fmt = wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
NPOI.SS.UserModel.CellStyle orig = wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.RIGHT);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.AreEqual(HorizontalAlignment.RIGHT, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont(wbOrig));
Assert.AreEqual(fmt.GetFormat("Test##"), orig.DataFormat);
// Now a style on another workbook
HSSFWorkbook wbClone = new HSSFWorkbook();
Assert.AreEqual(4, wbClone.NumberOfFonts);
DataFormat fmtClone = wbClone.CreateDataFormat();
NPOI.SS.UserModel.CellStyle clone = wbClone.CreateCellStyle();
Assert.AreEqual(4, wbClone.NumberOfFonts);
Assert.AreNotEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreNotEqual("TestingFont", clone.GetFont(wbClone).FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont(wbClone).FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.AreNotEqual(fmtClone.GetFormat("Test##"), fmt.GetFormat("Test##"));
Assert.AreEqual(5, wbClone.NumberOfFonts);
}
private static NPOI.SS.UserModel.CellStyle CreateHeaderStyle(HSSFWorkbook wb)
{
Font font = wb.CreateFont();
font.Color = (HSSFColor.WHITE.index);
font.Boldweight = (short)FontBoldWeight.BOLD;
NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
cellStyle.FillForegroundColor = (HSSFColor.BLUE_GREY.index);
cellStyle.FillPattern = (FillPatternType.SOLID_FOREGROUND);
cellStyle.Alignment = (HorizontalAlignment.CENTER);
cellStyle.VerticalAlignment = (NPOI.SS.UserModel.VerticalAlignment.CENTER);
cellStyle.BorderLeft = (CellBorderType.THIN);
cellStyle.LeftBorderColor = (HSSFColor.WHITE.index);
cellStyle.BorderTop = (CellBorderType.THIN);
cellStyle.TopBorderColor = (HSSFColor.WHITE.index);
cellStyle.BorderRight = (CellBorderType.THIN);
cellStyle.RightBorderColor = (HSSFColor.WHITE.index);
cellStyle.BorderBottom = (CellBorderType.THIN);
cellStyle.BottomBorderColor = (HSSFColor.WHITE.index);
cellStyle.SetFont(font);
return(cellStyle);
}
/// <summary>
/// Sets the format properties of the given style based on the given map.
/// </summary>
/// <param name="style">The cell style</param>
/// <param name="workbook">The parent workbook.</param>
/// <param name="properties">The map of format properties (String -> Object).</param>
private static void SetFormatProperties(
NPOI.SS.UserModel.CellStyle style, HSSFWorkbook workbook, Hashtable properties)
{
style.Alignment = (NPOI.SS.UserModel.HorizontalAlignment)GetShort(properties, ALIGNMENT);
style.BorderBottom = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_BOTTOM);
style.BorderLeft = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_LEFT);
style.BorderRight = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_RIGHT);
style.BorderTop = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_TOP);
style.BottomBorderColor = (GetShort(properties, BOTTOM_BORDER_COLOR));
style.DataFormat = (GetShort(properties, DATA_FORMAT));
style.FillBackgroundColor = (GetShort(properties, FILL_BACKGROUND_COLOR));
style.FillForegroundColor = (GetShort(properties, FILL_FOREGROUND_COLOR));
style.FillPattern = (NPOI.SS.UserModel.FillPatternType)GetShort(properties, FILL_PATTERN);
style.SetFont(workbook.GetFontAt(GetShort(properties, FONT)));
style.IsHidden = (GetBoolean(properties, HIDDEN));
style.Indention = (GetShort(properties, INDENTION));
style.LeftBorderColor = (GetShort(properties, LEFT_BORDER_COLOR));
style.IsLocked = (GetBoolean(properties, LOCKED));
style.RightBorderColor = (GetShort(properties, RIGHT_BORDER_COLOR));
style.Rotation = (GetShort(properties, ROTATION));
style.TopBorderColor = (GetShort(properties, TOP_BORDER_COLOR));
style.VerticalAlignment = (NPOI.SS.UserModel.VerticalAlignment)GetShort(properties, VERTICAL_ALIGNMENT);
style.WrapText = (GetBoolean(properties, WRAP_TEXT));
}
public void TestWriteSheetStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = null;
Font fnt = wb.CreateFont();
NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle();
cs.BorderBottom = (CellBorderType.THIN);
cs.BorderLeft = (CellBorderType.THIN);
cs.BorderRight = (CellBorderType.THIN);
cs.BorderTop = (CellBorderType.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, CellBorderType.THIN, "FillForegroundColor for row: ");
Assert.AreEqual(cs.BorderLeft, CellBorderType.THIN, "FillPattern for row: ");
Assert.AreEqual(cs.BorderRight, CellBorderType.THIN, "FillForegroundColor for row: ");
Assert.AreEqual(cs.BorderTop, CellBorderType.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: ");
}
}
private void SetHead(Sheet sheet)
{
//sheet = hssWorkBook.CreateSheet("CustomerInfo");
Row rowHead = sheet.CreateRow(0);
Cell cell = rowHead.CreateCell(0);
cell.SetCellValue("新客户注册统计表");
Cell cellhead1 = rowHead.CreateCell(1);
Cell cellhead2 = rowHead.CreateCell(2);
Cell cellhead3 = rowHead.CreateCell(3);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
NPOI.SS.UserModel.CellStyle style = hssWorkBook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
Font font = hssWorkBook.CreateFont();
font.FontName = "黑体";
font.Boldweight = 700;
font.FontHeightInPoints = 14;
style.SetFont(font);
//style.BorderBottom = CellBorderType.THIN;
//style.BorderLeft = CellBorderType.THIN;
//style.BorderRight = CellBorderType.THIN;
//style.BorderTop = CellBorderType.THIN;
cell.CellStyle = style;
cellhead1.CellStyle = style;
cellhead2.CellStyle = style;
cellhead3.CellStyle = style;
rowHead.Height = 2 * 256;
NPOI.SS.UserModel.CellStyle stylefirst = hssWorkBook.CreateCellStyle();
stylefirst.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
Font fontfirst = hssWorkBook.CreateFont();
fontfirst.FontName = "宋体";
fontfirst.FontHeightInPoints = 10;
fontfirst.Boldweight = 700;
stylefirst.SetFont(fontfirst);
//stylefirst.BorderBottom = CellBorderType.THIN;
//stylefirst.BorderLeft = CellBorderType.THIN;
//stylefirst.BorderRight = CellBorderType.THIN;
//stylefirst.BorderTop = CellBorderType.THIN;
// rowHead.Height = 1 * 256;
Row rowfirst = sheet.CreateRow(1);
rowfirst.CreateCell(0).SetCellValue("");
Cell celfirst = rowfirst.CreateCell(1);
DateTime dateTiem = DateTime.Now.Date.AddDays(-7);
string dateBegin = dateTiem.Year.ToString() + "-" + dateTiem.Month.ToString() + "-" + dateTiem.Day.ToString();
DateTime dateEnd = DateTime.Now.AddDays(-1);
string date = dateEnd.Year.ToString() + "-" + dateEnd.Month.ToString() + "-" + dateEnd.Day.ToString();
celfirst.SetCellValue(dateBegin + " 00:00——" + date + " 24:00");
celfirst.CellStyle = stylefirst;
rowfirst.CreateCell(2).SetCellValue("");
Cell cellTitle3 = rowfirst.CreateCell(3);
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 2));
NPOI.SS.UserModel.CellStyle styleTitle = hssWorkBook.CreateCellStyle();
styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
Font fontTitle = hssWorkBook.CreateFont();
fontTitle.Boldweight = 700;
styleTitle.SetFont(fontTitle);
styleTitle.BorderBottom = CellBorderType.THIN;
styleTitle.BorderLeft = CellBorderType.THIN;
styleTitle.BorderRight = CellBorderType.THIN;
styleTitle.BorderTop = CellBorderType.THIN;
// cellTitle3.CellStyle = styleTitle;
Row row = sheet.CreateRow(2);
Cell cell0 = row.CreateCell(0);
cell0.SetCellValue("序号");
cell0.CellStyle = styleTitle;
Cell cell1 = row.CreateCell(1);
cell1.SetCellValue("顾客ID");
cell1.CellStyle = styleTitle;
Cell cell2 = row.CreateCell(2);
cell2.SetCellValue("电子邮件");
cell2.CellStyle = styleTitle;
Cell cell3 = row.CreateCell(3);
cell3.SetCellValue("状态");
cell3.CellStyle = styleTitle;
sheet.SetColumnWidth(0, 5 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 50 * 256);
sheet.SetColumnWidth(3, 5 * 256);
}
public void TestOptimiseFonts()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Add 6 fonts, some duplicates
Font f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
Font f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
Font f3 = wb.CreateFont();
f3.FontHeight = ((short)33);
f3.FontName = ("Unique");
Font f4 = wb.CreateFont();
f4.FontHeight = ((short)11);
f4.FontName = ("Testing");
Font f5 = wb.CreateFont();
f5.FontHeight = ((short)22);
f5.FontName = ("Also Testing");
Font f6 = wb.CreateFont();
f6.FontHeight = ((short)66);
f6.FontName = ("Also Unique");
// Use all three of the four in cell styles
Assert.AreEqual(21, wb.NumCellStyles);
NPOI.SS.UserModel.CellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
Assert.AreEqual(5, cs1.FontIndex);
NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f4);
Assert.AreEqual(8, cs2.FontIndex);
NPOI.SS.UserModel.CellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f5);
Assert.AreEqual(9, cs3.FontIndex);
NPOI.SS.UserModel.CellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f6);
Assert.AreEqual(10, cs4.FontIndex);
Assert.AreEqual(25, wb.NumCellStyles);
// And three in rich text
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row r = s.CreateRow(0);
HSSFRichTextString rtr1 = new HSSFRichTextString("Test");
rtr1.ApplyFont(0, 2, f1);
rtr1.ApplyFont(3, 4, f2);
r.CreateCell(0).SetCellValue(rtr1);
HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");
rtr2.ApplyFont(0, 2, f3);
rtr2.ApplyFont(3, 5, f5);
rtr2.ApplyFont(6, 8, f6);
r.CreateCell(1).SetCellValue(rtr2);
// Check what we have now
Assert.AreEqual(10, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Optimise
HSSFOptimiser.OptimiseFonts(wb);
// Check font count
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Check font use in cell styles
Assert.AreEqual(5, cs1.FontIndex);
Assert.AreEqual(5, cs2.FontIndex); // duplicate of 1
Assert.AreEqual(6, cs3.FontIndex); // duplicate of 2
Assert.AreEqual(8, cs4.FontIndex); // two have gone
// And in rich text
// RTR 1 had f1 and f2, unchanged
Assert.AreEqual(5, r.GetCell(0).RichStringCellValue.GetFontAtIndex(0));
Assert.AreEqual(5, r.GetCell(0).RichStringCellValue.GetFontAtIndex(1));
Assert.AreEqual(6, r.GetCell(0).RichStringCellValue.GetFontAtIndex(3));
Assert.AreEqual(6, r.GetCell(0).RichStringCellValue.GetFontAtIndex(4));
// RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
Assert.AreEqual(7, r.GetCell(1).RichStringCellValue.GetFontAtIndex(0));
Assert.AreEqual(7, r.GetCell(1).RichStringCellValue.GetFontAtIndex(1));
Assert.AreEqual(6, r.GetCell(1).RichStringCellValue.GetFontAtIndex(3));
Assert.AreEqual(6, r.GetCell(1).RichStringCellValue.GetFontAtIndex(4));
Assert.AreEqual(8, r.GetCell(1).RichStringCellValue.GetFontAtIndex(6));
Assert.AreEqual(8, r.GetCell(1).RichStringCellValue.GetFontAtIndex(7));
}
public void TestOptimiseStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
Assert.AreEqual(4, wb.NumberOfFonts);
Font f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
Font 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.CellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f2);
NPOI.SS.UserModel.CellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f1);
NPOI.SS.UserModel.CellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f1);
cs4.Alignment = HorizontalAlignment.CENTER_SELECTION;// ((short)22);
NPOI.SS.UserModel.CellStyle cs5 = wb.CreateCellStyle();
cs5.SetFont(f2);
cs5.Alignment = HorizontalAlignment.FILL; //((short)111);
NPOI.SS.UserModel.CellStyle cs6 = wb.CreateCellStyle();
cs6.SetFont(f2);
Assert.AreEqual(27, wb.NumCellStyles);
// Use them
NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
Row 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);
}
