2022年8月22日-POI-drawing.

sheet中的单位

统一使用EMU

1EMU = (1 / 914400) US inch = (1 / 360000) cm

PIXEL_DPI = 96

POINT_DPI = 72

emu = (pixel / 96) * 914400

emu = (point / 72) * 914400

1 pixel = (914400 / 96 ) = 9525 emu

1 point = (914400 / 72) = 12700 emu

7 is single char pixel 

cell width:

((8*7+5)/7*256)/256 = 8.714285714

width to pixel:

(((256*8.714285714+(128/7))/256)*7) = 61.499999998

pixel to char width


31 is single char pixel

cell width:

((8*32+8)/32*256)/256 

((8*32+5)/32*256)/256 = 8.15625

width to pixel:

(((256*8.15625+(128/32))/256)*32) = 261.5

字体像素计算

计算

using System;
using System.Drawing;
using System.Drawing.Text;
using System.IO;
using System.Text;
using System.Windows.Forms;
namespace FontMesure
{
    public partial class FontMeasure : Form
    {
        int[] _font_size = new int[] { 6, 8, 9, 10, 11, 12, 14, 16, 18, 20, 22, 24, 26, 28, 36, 48, 72 };
        FontStyle[] _font_style = new FontStyle[] { FontStyle.Regular, FontStyle.Bold, FontStyle.Italic };
        String fontSizeDelimeter = "|";
        String fontDelimeter = "__";
        String fontPixelStr = "";
        public FontMeasure()
        {
            InitializeComponent();
            dumpToTxt.Enabled = false;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Bitmap bm = new Bitmap(10, 10);
            bm.SetResolution(96, 96);
            Graphics g = Graphics.FromImage(bm);
            InstalledFontCollection fonts = new InstalledFontCollection();
            StringBuilder str = new StringBuilder(2000);
            foreach (FontFamily family in fonts.Families)
            {
                str.Append(family.Name);
                str.Append("\n");
                foreach (FontStyle style in _font_style)
                {
                    str.Append(style.ToString());
                    str.Append("@");
                    foreach (int fontSize in _font_size)
                    {
                        Font tmp = new Font(family.Name, fontSize, style);
                        float w = 0;
                        for (int i = 0; i < 10; i++)
                        {
                            SizeF sf = g.MeasureString(i + "", tmp, Int32.MaxValue, StringFormat.GenericTypographic);
                            if (sf.Width > w)
                            {
                                w = sf.Width;
                            }
                        }
                        str.Append(fontSize + ":" + w +fontSizeDelimeter);
                    }
                    str.Remove(str.Length - 1, 1);
                    str.Append("\n");
                }
                str.Append(fontDelimeter);
                str.Append("\n");
            }
            fontPixelStr = str.ToString();
            richTextBox1.Text = fontPixelStr;
            g.Dispose();
            dumpToTxt.Enabled = true;
        }

        private void richTextBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void dumpToTxt_Click(object sender, EventArgs e)
        {
            TextWriter textWriter = new StreamWriter("fontPixelRaw.txt", false);
            textWriter.Write(fontPixelStr);
            textWriter.Close();
        }
    }
}

转json

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONObject;

import java.nio.charset.StandardCharsets;

public class FontMeasureBuild {
    public static void main(String[] args) {
        //把上面输出的fontPixelRaw.txt 转成json格式
        String raw = FileUtil.readString("fontPixelRaw.txt", StandardCharsets.UTF_8);
        String[] rawSplit = raw.split("\r\n__\r\n");
        JSONObject fontMapping = new JSONObject();
        for (String fontRaw : rawSplit) {
            if (StrUtil.isBlank(fontRaw)) continue;
            String[] split = fontRaw.split("\r\n");
            //font->bold->k->v
            JSONObject styleMapping = new JSONObject();
            fontMapping.put(split[0], styleMapping);
            String[] RegularSplit = split[1].split("@");
            buildFontStyleMapping("r", styleMapping, RegularSplit[1]);

            String[] BoldSplit = split[2].split("@");
            buildFontStyleMapping("b", styleMapping, BoldSplit[1]);


            String[] ItalicSplit = split[3].split("@");
            buildFontStyleMapping("i", styleMapping, ItalicSplit[1]);
        }
        FileUtil.writeString(fontMapping.toJSONString(),"fontPixel.json","utf8");
        System.out.println(fontMapping.toJSONString());
    }

    public static void buildFontStyleMapping(String style, JSONObject styleMapping, String strList) {

        String[] split = strList.split("\\|");
        for (String str : split) {
            if (StrUtil.isNotBlank(str)) {
                String[] sizeSplit = str.split(":");
                String key = style + "@" + sizeSplit[0];
                styleMapping.put(key, sizeSplit[1]);
            }
        }
    }

使用

根据workbook中的字体查表获取对应的digitWidth、cellWidth、cellPixelWidth

Font font = workbook.getFontAt(0);
String defaultFontName = "Calibri";
String fontName = font.getFontName();
short fontSize = font.getFontHeightInPoints();
JSONObject fontPixelConfig = JSON.parseObject(FileUtils.readStr("fontPixel.json"))
JSONObject jsonObject = fontPixelConfig.getJSONObject(fontName);
if (null == jsonObject) {
    jsonObject = fontPixelConfig.getJSONObject(defaultFontName);
}
if (null != jsonObject) {
    //fontStyle(Initial)@fontSize
    String key = "r@" + fontSize;
    String digitWidthStr = jsonObject.getString(key);
    if (!StringUtils.isEmpty(digitWidthStr)) {
        int dw = ColumnHelper.round(new BigDecimal(digitWidthStr).floatValue());
        int cellWidthPixel = ColumnHelper.dw2pixel(dw);
        float cellWidth = ColumnHelper.pixel2cw(cellWidthPixel, dw);
       
    }
} 

转换

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol;

import java.math.BigDecimal;
import java.math.RoundingMode;

public class ColumnHelper {

    public static int round(float v) {
        return (int) (v + 0.5f);
    }

    /**
     * @param dw_ digit width
     * @return default cell width of pixel
     */
    public static int dw2pixel(float dw_) {
        int dw = round(dw_);
        return align(Math.round(dw * 8f + round((dw) / 4f) * 2 + 1), 8);
    }

    /**
     * @param cw  cellWidth
     * @param dw_ digitWidth
     * @return cell width of pixel
     */
    public static int cw2pixel(double cw, float dw_) {
        int dw = round(dw_);
        return (int) Math.round(cw * dw + round(((dw) / 4f)) * 2d + 1d);
    }

    /**
     * @param pixel cell width of pixel
     * @param dw_   digit width
     * @return cellWidth of pixel
     */
    public static float pixel2cw(int pixel, float dw_) {
        int dw = round(dw_);
        BigDecimal value = BigDecimal.valueOf(pixel - (((dw) / 4f) * 2 + 1));
        value = value.divide(BigDecimal.valueOf(dw), 2, RoundingMode.HALF_UP);
        return value.floatValue();
    }

    /**
     * 按给定倍数对齐
     *
     * @param v 给定值
     * @param a 倍数
     * @return
     */
    public static int align(int v, int a) {
        return (v + a - 1) & -a;
    }

    public static float getCellWidthPixel(Sheet sheet, int colIndex) {
        WorkbookInfo workBookInfo = JxlsDrawingContextHolder.getWorkBookInfo();
        if (sheet instanceof XSSFSheet) {
            CTCol col = ((XSSFSheet) sheet).getColumnHelper().getColumn(colIndex, false);
            return (float) (col == null || !col.isSetWidth() ? workBookInfo.getDefaultColumnPixelWidth() : cw2pixel(col.getWidth(), workBookInfo.getDigitWidth()));
        } else {
            return workBookInfo.getDefaultColumnPixelWidth();
        }
    }


    public static void main(String[] args) {
        float dw = 32;
        int pixel1 = dw2pixel(dw);
        System.out.println(pixel1);//280
        System.out.println(pixel2cw(pixel1, dw));//8.22
    }


}

Reference

Excel列宽像素值计算方法详解

precisely-placing-images-in-an-open-xml-spreadsheet

ooxml-viewer

Calculating-Cell-Sizes

Convert Excel column width between characters unit and pixels (points)