A way to correctly determine the width of characters in Microsoft Excel

calculate_char_width.rb

$widths =  {1 => 68.28515625,
2 => 35,
3 => 112.7109375,
4 => 57.140625,
5 => 134.85546875,
6 => 68.28515625,
7 => 157,
8 => 79.42578125,
9 => 157,
10 => 79.42578125,
11 => 245.85546875,
12 => 123.7109375,
13 => 223.5703125,
14 => 112.7109375,
15 => 68.28515625,
16 => 35,
17 => 112.7109375,
18 => 57.140625,
19 => 112.7109375,
20 => 57.140625,
21 => 157,
22 => 79.42578125,
23 => 157,
24 => 79.42578125,
25 => 90.42578125,
26 => 46,
27 => 112.7109375,
28 => 57.140625,
29 => 90.42578125,
30 => 46,
31 => 134.85546875,
32 => 68.28515625,
33 => 157,
34 => 79.42578125,
35 => 157,
36 => 79.42578125,
37 => 157,
38 => 79.42578125,
39 => 157,
40 => 79.42578125,
41 => 157,
42 => 79.42578125,
43 => 157,
44 => 79.42578125,
45 => 157,
46 => 79.42578125,
47 => 157,
48 => 79.42578125,
49 => 157,
50 => 79.42578125,
51 => 157,
52 => 79.42578125,
53 => 90.42578125,
54 => 46,
55 => 90.42578125,
56 => 46,
57 => 157,
58 => 79.42578125,
59 => 157,
60 => 79.42578125,
61 => 157,
62 => 79.42578125,
63 => 157,
64 => 79.42578125,
65 => 255.7109375,
66 => 146,
67 => 201.42578125,
68 => 101.5703125,
69 => 179.28515625,
70 => 90.42578125,
71 => 179.28515625,
72 => 90.42578125,
73 => 201.42578125,
74 => 101.5703125,
75 => 157,
76 => 79.42578125,
77 => 157,
78 => 79.42578125,
79 => 201.42578125,
80 => 101.5703125,
81 => 201.42578125,
82 => 101.5703125,
83 => 90.42578125,
84 => 46,
85 => 112.7109375,
86 => 57.140625,
87 => 179.28515625,
88 => 90.42578125,
89 => 134.85546875,
90 => 68.28515625,
91 => 255.7109375,
92 => 134.85546875,
93 => 223.5703125,
94 => 112.7109375,
95 => 223.5703125,
96 => 112.7109375,
97 => 179.28515625,
98 => 90.42578125,
99 => 223.5703125,
100 => 112.7109375,
101 => 179.28515625,
102 => 90.42578125,
103 => 157,
104 => 79.42578125,
105 => 157,
106 => 79.42578125,
107 => 201.42578125,
108 => 101.5703125,
109 => 201.42578125,
110 => 101.5703125,
111 => 255.7109375,
112 => 146,
113 => 179.28515625,
114 => 90.42578125,
115 => 157,
116 => 79.42578125,
117 => 157,
118 => 79.42578125,
119 => 112.7109375,
120 => 57.140625,
121 => 134.85546875,
122 => 68.28515625,
123 => 112.7109375,
124 => 57.140625,
125 => 157,
126 => 79.42578125,
127 => 157,
128 => 79.42578125,
129 => 90.42578125,
130 => 46,
131 => 157,
132 => 79.42578125,
133 => 179.28515625,
134 => 90.42578125,
135 => 134.85546875,
136 => 68.28515625,
137 => 179.28515625,
138 => 90.42578125,
139 => 179.28515625,
140 => 90.42578125,
141 => 112.7109375,
142 => 57.140625,
143 => 157,
144 => 79.42578125,
145 => 179.28515625,
146 => 90.42578125,
147 => 90.42578125,
148 => 46,
149 => 90.42578125,
150 => 46,
151 => 157,
152 => 79.42578125,
153 => 90.42578125,
154 => 46,
155 => 255.7109375,
156 => 134.85546875,
157 => 179.28515625,
158 => 90.42578125,
159 => 179.28515625,
160 => 90.42578125,
161 => 179.28515625,
162 => 90.42578125,
163 => 179.28515625,
164 => 90.42578125,
165 => 112.7109375,
166 => 57.140625,
167 => 134.85546875,
168 => 68.28515625,
169 => 112.7109375,
170 => 57.140625,
171 => 179.28515625,
172 => 90.42578125,
173 => 157,
174 => 79.42578125,
175 => 245.85546875,
176 => 123.7109375,
177 => 157,
178 => 79.42578125,
179 => 157,
180 => 79.42578125,
181 => 134.85546875,
182 => 68.28515625,
183 => 112.7109375,
184 => 57.140625,
185 => 157,
186 => 79.42578125,
187 => 112.7109375,
188 => 57.140625,
189 => 157,
190 => 79.42578125}

def get_width index
  start = 2 * index + 1
  ($widths[start] - $widths[start + 1]) / 75
end
$chars = (0..255).to_a.map {|a| a.chr }.select {|a| a =~ /^[[:print:]]$/ }

$hash= Hash[$chars.each_with_index.map {|c, i| [c, get_width(i).round(5)] }]

p $hash

generate_csv.rb

CSV.open('aa.csv', 'w') { |csv| csv << (0..255).to_a.map {|a| a.chr }.select {|a| a =~ /^[[:print:]]$/ }.map {|c| ["|#{c*150}", "|#{c*75}"]}.flatten  }

gistfile1.md

Open test.csv in Excel and apply auto width to all columns and save the file to xlsx format. Extract the sheet1.xml file and create a hash of all columns and their widths ( element in the file) Add those values to calculate_char_width.rb and run it