OpenXML: Auto Size column width in Excel
I finally found a solution that uses the graphics engine GDI+ to do this accurately. avg. char width * number of chars doesn't cut it ever.
There's a caveat here in that you will need a couple of libraries if your target platform is linux - one for GDI+ interop and the other to add the open microsoft web fonts.
It uses a base unit of pixel, considering excel fonts are pt and column width INCHES! (lol)
Here is the method. It takes a JSchema to map to columns but you get the gist.
private static Columns CreateColumnDefs(JSchema jsonSchema)
{
const double cellPadding = .4;
const double minCellWidth = 10;
var columnDefs = new Columns();
var columnIndex = 0U;
// set up graphics for calculating column width based on heading text width
var bmp = new Bitmap(1, 1);
// todo: ensure linux host has gdi and "Microsoft core fonts" libs installed
using var graphics = Graphics.FromImage(bmp);
graphics.TextRenderingHint = TextRenderingHint.AntiAlias;
graphics.PageUnit = GraphicsUnit.Pixel;
// excel fonts are in points - Arial 10pt matches the excel default of Calibri 11pt pretty well...
using var font = new Font("Arial", 10F, FontStyle.Bold, GraphicsUnit.Point);
// currently only handles 1 level (no nested objects)
foreach (var (_, value) in jsonSchema.Properties)
{
var pixelWidth = graphics.MeasureString(value.Title, font).Width;
// see: https://stackoverflow.com/questions/7716078/formula-to-convert-net-pixels-to-excel-width-in-openxml-format/7902415
var openXmlWidth = (pixelWidth - 12 + 5) / 7d + 1; // from pixels to inches.
columnIndex++;
var column = new Column
{
BestFit = true,
CustomWidth = true,
Width = Math.Max(openXmlWidth + cellPadding, minCellWidth),
Min = columnIndex,
Max = columnIndex
};
columnDefs.Append(column);
}
return columnDefs;
}
Edit
To run on linux install e.g.:
RUN apt update && apt-get install -y libgdiplus
(I didn't need any extra font libs, so perhaps system font works)
I haven't had the time to look into it, but instead of just leaving a comment and a link, I thought I'd share a comment from somebody who has seemingly done some research on this.
I personally had issues getting the official formulas to fit with reality. I.e. Short strings got too small cells, longer strings got too big cells and most of all, the value presented in Excel was proportionally smaller than the value I inserted into the DocumentFormat.OpenXml.Spreadsheet.Column's Width-property. My quick solution was just to have a minimum width.
Anyway, here's the comment:
I had to do this in the end because the xlsx files I am interested in are auto generated and should look nice as soon as they are opened so I looked into this a little further and found there are a couple of issues to accurately sizing columns in Excel.
Need to use accurate character sizing, which means that instead of using MeasureString you need to use MeasureCharacterRanges, see http://groups.google.com/group/microsoft.public.office.developer.com.add_ins/browse_thread/thread/2fc33557feb72ab4/adaddc50480b8cff?lnk=raot
Despite the spec saying to add 5 pixels (1 for border and 2 for each side margin) Excel seems to use 9 – 1 for the border, 5 for the leading space and 3 for the trailing space – I only found this by using the accessibility app. Magnifier and counting the pixels after using Excel to auto fit the columns
Actually I was basing my calculations on underlying font metrics so I don’t actually use either MeasureCharacterRanges or MeasureString. If anyone is interested in doing this from font metrics then:
Width = Truncate( {DesiredWidth} + 9 / {MaxDigitWidth} ) / 256{MaxDigitWidth} is an integer rounded to the nearest pixel of any of the 0..9 digits at 96 dpi {DesiredWidth} is the sum of adding all character widths together where each character width is the width of the character at 96 dpi rounded to the nearest integer. Note that each character is rounded not the overall sum
The BestFit property is an information property (possibly for optimisation by Excel). You still need to provide the Width for the Column. This means you have to actually calculate the column width depending on the cell contents. Open XML SDK doesn't do this automatically for you, so it's better that you use a third-party library for this.
You have to calculate it your self unfortunately
This is what I've got. It works for my data that's tabular with some extra code in to take care of some styles I have set. Its not perfect by any means but works for what I need it for.
private WorksheetPart mySheetPart;
private void WriteToTable()
{
//Get your sheet data - write Rows and Cells
SheetData sheetData = GetSheetData();
//get your columns (where your width is set)
Columns columns = AutoSize(sheetData);
//add to a WorksheetPart.WorkSheet
mySheetPart.Worksheet = new Worksheet();
mySheetPart.Worksheet.Append(columns);
mySheetPart.Worksheet.Append(sheetData);
}
private Columns AutoSize(SheetData sheetData)
{
var maxColWidth = GetMaxCharacterWidth(sheetData);
Columns columns = new Columns();
//this is the width of my font - yours may be different
double maxWidth = 7;
foreach (var item in maxColWidth)
{
//width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256;
//pixels=Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})
double pixels = Math.Truncate(((256 * width + Math.Truncate(128 / maxWidth)) / 256) * maxWidth);
//character width=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100
double charWidth = Math.Truncate((pixels - 5) / maxWidth * 100 + 0.5) / 100;
Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width };
columns.Append(col);
}
return columns;
}
private Dictionary<int, int> GetMaxCharacterWidth(SheetData sheetData)
{
//iterate over all cells getting a max char value for each column
Dictionary<int, int> maxColWidth = new Dictionary<int, int>();
var rows = sheetData.Elements<Row>();
UInt32[] numberStyles = new UInt32[] { 5, 6, 7, 8 }; //styles that will add extra chars
UInt32[] boldStyles = new UInt32[] { 1, 2, 3, 4, 6, 7, 8 }; //styles that will bold
foreach (var r in rows)
{
var cells = r.Elements<Cell>().ToArray();
//using cell index as my column
for (int i = 0; i < cells.Length; i++)
{
var cell = cells[i];
var cellValue = cell.CellValue == null ? string.Empty : cell.CellValue.InnerText;
var cellTextLength = cellValue.Length;
if (cell.StyleIndex != null && numberStyles.Contains(cell.StyleIndex))
{
int thousandCount = (int)Math.Truncate((double)cellTextLength / 4);
//add 3 for '.00'
cellTextLength += (3 + thousandCount);
}
if (cell.StyleIndex != null && boldStyles.Contains(cell.StyleIndex))
{
//add an extra char for bold - not 100% acurate but good enough for what i need.
cellTextLength += 1;
}
if (maxColWidth.ContainsKey(i))
{
var current = maxColWidth[i];
if (cellTextLength > current)
{
maxColWidth[i] = cellTextLength;
}
}
else
{
maxColWidth.Add(i, cellTextLength);
}
}
}
return maxColWidth;
}