demand

To export an Excel, the first line is a variable-length string. Merge cells (A-G) are known. Now we need to calculate the height of the first line.

thinking

The width unit of NPOI is known to be 1/256 English characters. In this case, the width of column A is 2048, or 2048/256 = 8 English characters. It can also be seen in cell A4. The following picture:

The default line height of the first line shows exactly one line of text. What I want to do is to calculate the number of Chinese characters that can be accommodated in a line of A-G column width, and then calculate the number of lines needed according to "line number = total word number / line word number", and set the first line height = original height.

The next problem to be solved is how to calculate the number of words in a line. Here we can use MeasureString in System.Drawing.Graphics in C # to get the width of letters and Chinese characters, and calculate them proportionally.

Of course, this method is not absolutely accurate, but the estimated value is enough for most cases.

code implementation

        public void SetRowHight(ISheet sheet, string text)
            {
                var width = 0.0;

                // Calculate cell length
                for (int i = 1; i <= sheet.GetRow(0).Cells.Count; i++)
                {
                    width += sheet.GetColumnWidth(i);
                }

                // Get the length of each line of Chinese characters
                double length = Util.GetChineseLength(
                    sheet.Workbook.GetFontAt(0),
                    this.cellStyles.TipStyle.GetFont(sheet.Workbook), // My Chinese Character Font
                    width);

                sheet.GetRow(0).Height = Convert.ToInt16(sheet.GetRow(0).Height * Math.Ceiling(text.Length / length));
            }

        /// <summary>
        /// Calculate the number of Chinese characters that can be accommodated
        /// </summary>
        /// <param name="eFont">English font </param>
        /// <param name="cFont">Chinese font </param>
        /// <param name="length">cell length </param>
        /// < returns > can accommodate the number of Chinese characters </returns >
        public static int GetChineseLength(IFont eFont, IFont cFont, double length)
        {
            using (var bitmap = new Bitmap(1, 1))
            {
                var graphics = Graphics.FromImage(bitmap);
                var size1 = graphics.MeasureString("abcdefg", new Font(eFont.FontName, eFont.FontHeightInPoints));
                var size2 = graphics.MeasureString("One, two, three, four, five, six, seven", new Font(cFont.FontName, cFont.FontHeightInPoints));
                return (int)Math.Floor((length / 256) * size1.Width / size2.Width);
            }
        }