// /**
// * Drawing context to measure text
// */
//private static FontRenderContext fontRenderContext = new FontRenderContext(null, true, true);
/**
* Compute width of a column and return the result
*
* @param sheet the sheet to calculate
* @param column 0-based index of the column
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells)
{
//AttributedString str;
//TextLayout layout;
IWorkbook wb = sheet.Workbook;
DataFormatter formatter = new DataFormatter();
IFont defaultFont = wb.GetFontAt((short)0);
//str = new AttributedString((defaultChar));
//copyAttributes(defaultFont, str, 0, 1);
//layout = new TextLayout(str.Iterator, fontRenderContext);
//int defaultCharWidth = (int)layout.Advance;
Font font = IFont2Font(defaultFont);
int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width;
DummyEvaluator dummyEvaluator = new DummyEvaluator();
double width = -1;
using (Bitmap bmp = new Bitmap(2048, 100))
{
Graphics g = Graphics.FromImage(bmp);
//rows:
bool skipthisrow = false;
for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext();)
{
IRow row = (IRow)it.Current;
ICell cell = row.GetCell(column);
if (cell == null)
{
continue;
}
int colspan = 1;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.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.
//continue rows;
skipthisrow = true;
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
if (skipthisrow)
{
continue;
}
ICellStyle style = cell.CellStyle;
Zephyr.Utils.NPOI.SS.UserModel.IFont font1 = wb.GetFontAt(style.FontIndex);
CellType cellType = cell.CellType;
// for formula cells we compute the cell width for the cached formula result
if (cellType == CellType.FORMULA)
{
cellType = cell.CachedFormulaResultType;
}
if (cellType == CellType.STRING)
{
IRichTextString rt = cell.RichStringCellValue;
String[] lines = rt.String.Split("\n".ToCharArray());
for (int i = 0; i < lines.Length; i++)
{
String txt = lines[i] + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
font = IFont2Font(font1);
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
//layout = new TextLayout(str.Iterator, fontRenderContext);
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;
//AffineTransform trans = new AffineTransform();
//trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.Concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
SizeF sf = g.MeasureString(txt, font);
double x1 = Math.Abs(sf.Height * Math.Sin(angle));
double x2 = Math.Abs(sf.Width * Math.Cos(angle));
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
//width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
}
else
{
//width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
else
{
String sval = null;
if (cellType == CellType.NUMERIC)
{
// Try to Get it formatted to look the same as excel
try
{
sval = formatter.FormatCellValue(cell, dummyEvaluator);
}
catch (Exception)
{
sval = cell.NumericCellValue.ToString("F", CultureInfo.InvariantCulture);
}
}
else if (cellType == CellType.BOOLEAN)
{
sval = cell.BooleanCellValue.ToString().ToUpper();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
//layout = new TextLayout(str.Iterator, fontRenderContext);
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;
//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).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
SizeF sf = g.MeasureString(txt, font);
double x1 = sf.Height * Math.Sin(angle);
double x2 = sf.Width * Math.Cos(angle);
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
else
{
//width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
}
return(width);
}
// /**
// * Drawing context to measure text
// */
//private static FontRenderContext fontRenderContext = new FontRenderContext(null, true, true);
/**
* Compute width of a column and return the result
*
* @param sheet the sheet to calculate
* @param column 0-based index of the column
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells)
{
//AttributedString str;
//TextLayout layout;
IWorkbook wb = sheet.Workbook;
DataFormatter formatter = new DataFormatter();
IFont defaultFont = wb.GetFontAt((short)0);
//str = new AttributedString((defaultChar));
//copyAttributes(defaultFont, str, 0, 1);
//layout = new TextLayout(str.Iterator, fontRenderContext);
//int defaultCharWidth = (int)layout.Advance;
Font font = IFont2Font(defaultFont);
int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width;
DummyEvaluator dummyEvaluator = new DummyEvaluator();
double width = -1;
using (Bitmap bmp = new Bitmap(2048, 100))
{
Graphics g = Graphics.FromImage(bmp);
//rows:
bool skipthisrow = false;
for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext(); )
{
IRow row = (IRow)it.Current;
ICell cell = row.GetCell(column);
if (cell == null)
{
continue;
}
int colspan = 1;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.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.
//continue rows;
skipthisrow = true;
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
if (skipthisrow)
{
continue;
}
ICellStyle style = cell.CellStyle;
NPOI.SS.UserModel.IFont font1 = wb.GetFontAt(style.FontIndex);
CellType cellType = cell.CellType;
// for formula cells we compute the cell width for the cached formula result
if (cellType == CellType.FORMULA) cellType = cell.CachedFormulaResultType;
if (cellType == CellType.STRING)
{
IRichTextString rt = cell.RichStringCellValue;
String[] lines = rt.String.Split("\n".ToCharArray());
for (int i = 0; i < lines.Length; i++)
{
String txt = lines[i] + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
font = IFont2Font(font1);
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
//layout = new TextLayout(str.Iterator, fontRenderContext);
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;
//AffineTransform trans = new AffineTransform();
//trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.Concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
SizeF sf = g.MeasureString(txt, font);
double x1 = Math.Abs(sf.Height * Math.Sin(angle));
double x2 = Math.Abs(sf.Width * Math.Cos(angle));
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
//width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
}
else
{
//width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
else
{
String sval = null;
if (cellType == CellType.NUMERIC)
{
// Try to Get it formatted to look the same as excel
try
{
sval = formatter.FormatCellValue(cell, dummyEvaluator);
}
catch (Exception)
{
sval = cell.NumericCellValue.ToString("F", CultureInfo.InvariantCulture);
}
}
else if (cellType == CellType.BOOLEAN)
{
sval = cell.BooleanCellValue.ToString().ToUpper();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
//layout = new TextLayout(str.Iterator, fontRenderContext);
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;
//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).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
SizeF sf = g.MeasureString(txt, font);
double x1 = sf.Height * Math.Sin(angle);
double x2 = sf.Width * Math.Cos(angle);
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
else
{
//width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
}
return width;
}