public void Test45338()
{
HSSFWorkbook wb = new HSSFWorkbook();
Assert.AreEqual(4, wb.NumberOfFonts);
ISheet s = wb.CreateSheet();
s.CreateRow(0);
s.CreateRow(1);
ICell c1 = s.GetRow(0).CreateCell(0);
ICell c2 = s.GetRow(1).CreateCell(0);
Assert.AreEqual(4, wb.NumberOfFonts);
IFont f1 = wb.GetFontAt((short)0);
Assert.AreEqual(400, f1.Boldweight);
// Check that asking for the same font
// multiple times gives you the same thing.
// Otherwise, our Tests wouldn't work!
Assert.AreEqual(
wb.GetFontAt((short)0),
wb.GetFontAt((short)0)
);
Assert.AreEqual(
wb.GetFontAt((short)2),
wb.GetFontAt((short)2)
);
Assert.IsTrue(
wb.GetFontAt((short)0)
!=
wb.GetFontAt((short)2)
);
// Look for a new font we have
// yet to Add
Assert.IsNull(
wb.FindFont(
(short)11, (short)123, (short)22,
"Thingy", false, true, (short)2, (byte)2
)
);
IFont nf = wb.CreateFont();
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(5, nf.Index);
Assert.AreEqual(nf, wb.GetFontAt((short)5));
nf.Boldweight = ((short)11);
nf.Color = ((short)123);
nf.FontHeight = ((short)22);
nf.FontName = ("Thingy");
nf.IsItalic = (false);
nf.IsStrikeout = (true);
nf.TypeOffset = ((short)2);
nf.Underline = ((byte)2);
Assert.AreEqual(5, wb.NumberOfFonts);
Assert.AreEqual(nf, wb.GetFontAt((short)5));
// Find it now
Assert.IsNotNull(
wb.FindFont(
(short)11, (short)123, (short)22,
"Thingy", false, true, (short)2, (byte)2
)
);
Assert.AreEqual(
5,
wb.FindFont(
(short)11, (short)123, (short)22,
"Thingy", false, true, (short)2, (byte)2
).Index
);
Assert.AreEqual(nf,
wb.FindFont(
(short)11, (short)123, (short)22,
"Thingy", false, true, (short)2, (byte)2
)
);
}
/// <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.ICellStyle style, HSSFWorkbook workbook, Hashtable properties)
{
style.Alignment = (NPOI.SS.UserModel.HorizontalAlignment)GetShort(properties, ALIGNMENT);
style.BorderBottom = (NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_BOTTOM);
style.BorderLeft = (NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_LEFT);
style.BorderRight = (NPOI.SS.UserModel.BorderStyle)GetShort(properties, BORDER_RIGHT);
style.BorderTop = (NPOI.SS.UserModel.BorderStyle)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));
}
/// <summary>
/// Adjusts the column width to fit the contents.
/// This Process can be relatively slow on large sheets, so this should
/// normally only be called once per column, at the end of your
/// Processing.
/// You can specify whether the content of merged cells should be considered or ignored.
/// Default is to ignore merged cells.
/// </summary>
/// <param name="column">the column index</param>
/// <param name="useMergedCells">whether to use the contents of merged cells when calculating the width of the column</param>
public void AutoSizeColumn(int column, bool useMergedCells)
{
/**
* Excel measures columns in Units of 1/256th of a Char width
* but the docs say nothing about what particular Char is used.
* '0' looks to be a good choice.
*/
char defaultChar = '0';
/**
* This is the multiple that the font height is scaled by when determining the
* boundary of rotated text.
*/
double fontHeightMultiple = 2.0;
//FontRenderContext frc = new FontRenderContext(null, true, true);
HSSFWorkbook wb = new HSSFWorkbook(book);
NPOI.SS.UserModel.Font defaultFont = wb.GetFontAt((short)0);
//str = new AttributedString("" + defaultChar);
//CopyAttributes(defaultFont, str, 0, 1);
//layout = new TextLayout(str.GetEnumerator(), frc);
System.Drawing.Font font = HSSFFont2Font((HSSFFont)defaultFont);
int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width;
double width = -1;
bool skipthisrow = false;
for (IEnumerator it = rows.Values.GetEnumerator(); it.MoveNext(); )
{
HSSFRow row = (HSSFRow)it.Current;
NPOI.SS.UserModel.Cell cell = (HSSFCell)row.GetCell(column);
if (cell == null) continue;
int colspan = 1;
for (int i = 0; i < NumMergedRegions; i++)
{
NPOI.SS.Util.CellRangeAddress region = GetMergedRegion(i);
if (ContainsCell(region, row.RowNum, column))
{
if (!useMergedCells)
{
// If we're not using merged cells, skip this one and move on to the next.
skipthisrow = true;
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
if (skipthisrow)
{
continue;
}
NPOI.SS.UserModel.CellStyle style = cell.CellStyle;
NPOI.SS.UserModel.Font font1 = wb.GetFontAt(style.FontIndex);
if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
{
HSSFRichTextString rt = (HSSFRichTextString)cell.RichStringCellValue;
String[] lines = rt.String.Split(new char[] { '\n' });
for (int k = 0; k < lines.Length; k++)
{
String txt = lines[k] + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font1, str, 0, txt.Length);
if (rt.NumFormattingRuns > 0)
{
for (int j = 0; j < lines[k].Length; j++)
{
int idx = rt.GetFontAtIndex(j);
if (idx != 0)
{
NPOI.SS.UserModel.Font fnt = wb.GetFontAt((short)idx);
//copyAttributes(fnt, str, j, j + 1);
}
}
}
//layout = new TextLayout(str.GetEnumerator(), frc);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the Unrotated top and bottom of the text that normally wouldn't be present if Unscaled, but
* is Added by the standard Excel autosize.
*/
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
//Tony Qu
//TODO:: text rotated width measure
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
throw new NotImplementedException();
}
else
{
//width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
width = Math.Max(width, (TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
else
{
String sval = null;
if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC)
{
NPOI.SS.UserModel.DataFormat dataformat = wb.CreateDataFormat();
short idx = style.DataFormat;
String format = "General";
if (idx >= 0)
{
format = dataformat.GetFormat(idx).Replace("\"", "");
}
double value = cell.NumericCellValue;
try
{
if ("General".Equals(format))
sval = "" + value;
else
{
sval = value.ToString("F");
}
}
catch (Exception)
{
sval = "" + value;
}
}
else if (cell.CellType == NPOI.SS.UserModel.CellType.BOOLEAN)
{
sval = cell.BooleanCellValue.ToString();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
//layout = new TextLayout(str.GetEnumerator(), frc);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the Unrotated top and bottom of the text that normally wouldn't be present if Unscaled, but
* is Added by the standard Excel autosize.
*/
//Tony Qu
//TODO:: text rotated width measure
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
//width = Math.Max(width, ((layout.GetOutline(trans).GetBounds().Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
throw new NotImplementedException();
}
else
{
//width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
width = Math.Max(width, (TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
if (width != -1)
{
if (width > short.MaxValue)
{ //width can be bigger that Short.MAX_VALUE!
width = short.MaxValue;
}
_sheet.SetColumnWidth(column, (short)(width * 256));
}
}