using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Drawing;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;

namespace ExcelOpenXmlCalculateColumnWidth
{
	class Program
	{
		static void Main(string[] args)
		{
			string sFile = "ExcelOpenXmlCalculateColumnWidth.xlsx";
			if (File.Exists(sFile))
			{
				File.Delete(sFile);
			}
			BuildWorkbook(sFile);
		}

		private static void BuildWorkbook(string filename)
		{
			try
			{
				using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
				{
					WorkbookPart wbp = xl.AddWorkbookPart();
					WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
					Workbook wb = new Workbook();
					FileVersion fv = new FileVersion();
					fv.ApplicationName = "Microsoft Office Excel";
					Worksheet ws = new Worksheet();
					SheetData sd = new SheetData();

					WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
					wbsp.Stylesheet = CreateStylesheet();
					wbsp.Stylesheet.Save();

					string sILT = "Iced Lemon Tea Is An Awesome Drink!";
					double fSimpleWidth = 0.0f;
					double fWidthOfZero = 0.0f;
					double fDigitWidth = 0.0f;
					double fMaxDigitWidth = 0.0f;
					double fTruncWidth = 0.0f;

					System.Drawing.Font drawfont = new System.Drawing.Font("Calibri", 11);
					// I just need a Graphics object. Any reasonable bitmap size will do.
					Graphics g = Graphics.FromImage(new Bitmap(200,200));
					fWidthOfZero = (double)g.MeasureString("0", drawfont).Width;
					fSimpleWidth = (double)g.MeasureString(sILT, drawfont).Width;
					fSimpleWidth = fSimpleWidth / fWidthOfZero;

					for (int i = 0; i < 10; ++i)
					{
						fDigitWidth = (double)g.MeasureString(i.ToString(), drawfont).Width;
						if (fDigitWidth > fMaxDigitWidth)
						{
							fMaxDigitWidth = fDigitWidth;
						}
					}
					g.Dispose();
					
					// Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256
					fTruncWidth = Math.Truncate((sILT.ToCharArray().Count() * fMaxDigitWidth + 5.0) / fMaxDigitWidth * 256.0) / 256.0;

					Columns columns = new Columns();
					columns.Append(CreateColumnData(1, 1, fSimpleWidth));
					columns.Append(CreateColumnData(2, 2, fTruncWidth));
					columns.Append(CreateColumnData(3, 3, 35.42578125));
					ws.Append(columns);

					Row r;
					Cell c;

					r = new Row();
					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "A1";
					c.CellValue = new CellValue(sILT);
					r.Append(c);

					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "B1";
					c.CellValue = new CellValue(sILT);
					r.Append(c);

					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "C1";
					c.CellValue = new CellValue(sILT);
					r.Append(c);
					sd.Append(r);

					r = new Row();
					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "A2";
					c.CellValue = new CellValue(string.Format("Simple width: {0}", fSimpleWidth));
					r.Append(c);
					sd.Append(r);

					r = new Row();
					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "A3";
					c.CellValue = new CellValue(string.Format("Truncation width: {0}", fTruncWidth));
					r.Append(c);
					sd.Append(r);

					r = new Row();
					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "A4";
					c.CellValue = new CellValue(string.Format("Width of '0': {0}", fWidthOfZero));
					r.Append(c);
					sd.Append(r);

					r = new Row();
					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "A5";
					c.CellValue = new CellValue(string.Format("Max Width of Digits: {0}", fMaxDigitWidth));
					r.Append(c);
					sd.Append(r);

					ws.Append(sd);
					wsp.Worksheet = ws;
					wsp.Worksheet.Save();
					Sheets sheets = new Sheets();
					Sheet sheet = new Sheet();
					sheet.Name = "Sheet1";
					sheet.SheetId = 1;
					sheet.Id = wbp.GetIdOfPart(wsp);
					sheets.Append(sheet);
					wb.Append(fv);
					wb.Append(sheets);

					xl.WorkbookPart.Workbook = wb;
					xl.WorkbookPart.Workbook.Save();
					xl.Close();
				}
			}
			catch (Exception e)
			{
				Console.WriteLine(e.ToString());
				Console.ReadLine();
			}
		}

		private static Column CreateColumnData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
		{
			Column column;
			column = new Column();
			column.Min = StartColumnIndex;
			column.Max = EndColumnIndex;
			column.Width = ColumnWidth;
			column.CustomWidth = true;
			return column;
		}

		private static Stylesheet CreateStylesheet()
		{
			Stylesheet ss = new Stylesheet();

			Fonts fts = new Fonts();
			DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
			FontName ftn = new FontName();
			ftn.Val = "Calibri";
			FontSize ftsz = new FontSize();
			ftsz.Val = 11;
			ft.FontName = ftn;
			ft.FontSize = ftsz;
			fts.Append(ft);
			fts.Count = (uint)fts.ChildElements.Count;

			Fills fills = new Fills();
			Fill fill;
			PatternFill patternFill;
			fill = new Fill();
			patternFill = new PatternFill();
			patternFill.PatternType = PatternValues.None;
			fill.PatternFill = patternFill;
			fills.Append(fill);
			fill = new Fill();
			patternFill = new PatternFill();
			patternFill.PatternType = PatternValues.Gray125;
			fill.PatternFill = patternFill;
			fills.Append(fill);
			fills.Count = (uint)fills.ChildElements.Count;

			Borders borders = new Borders();
			Border border = new Border();
			border.LeftBorder = new LeftBorder();
			border.RightBorder = new RightBorder();
			border.TopBorder = new TopBorder();
			border.BottomBorder = new BottomBorder();
			border.DiagonalBorder = new DiagonalBorder();
			borders.Append(border);
			borders.Count = (uint)borders.ChildElements.Count;

			CellStyleFormats csfs = new CellStyleFormats();
			CellFormat cf = new CellFormat();
			cf.NumberFormatId = 0;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			csfs.Append(cf);
			csfs.Count = (uint)csfs.ChildElements.Count;

			uint iExcelIndex = 164;
			NumberFormats nfs = new NumberFormats();
			CellFormats cfs = new CellFormats();

			cf = new CellFormat();
			cf.NumberFormatId = 0;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cfs.Append(cf);

			NumberFormat nf;
			nf = new NumberFormat();
			nf.NumberFormatId = iExcelIndex++;
			nf.FormatCode = "dd/mm/yyyy hh:mm:ss";
			nfs.Append(nf);
			cf = new CellFormat();
			cf.NumberFormatId = nf.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = true;
			cfs.Append(cf);

			nf = new NumberFormat();
			nf.NumberFormatId = iExcelIndex++;
			nf.FormatCode = "#,##0.0000";
			nfs.Append(nf);
			cf = new CellFormat();
			cf.NumberFormatId = nf.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = true;
			cfs.Append(cf);

			// #,##0.00 is also Excel style index 4
			nf = new NumberFormat();
			nf.NumberFormatId = iExcelIndex++;
			nf.FormatCode = "#,##0.00";
			nfs.Append(nf);
			cf = new CellFormat();
			cf.NumberFormatId = nf.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = true;
			cfs.Append(cf);

			// @ is also Excel style index 49
			nf = new NumberFormat();
			nf.NumberFormatId = iExcelIndex++;
			nf.FormatCode = "@";
			nfs.Append(nf);
			cf = new CellFormat();
			cf.NumberFormatId = nf.NumberFormatId;
			cf.FontId = 0;
			cf.FillId = 0;
			cf.BorderId = 0;
			cf.FormatId = 0;
			cf.ApplyNumberFormat = true;
			cfs.Append(cf);

			nfs.Count = (uint)nfs.ChildElements.Count;
			cfs.Count = (uint)cfs.ChildElements.Count;

			ss.Append(nfs);
			ss.Append(fts);
			ss.Append(fills);
			ss.Append(borders);
			ss.Append(csfs);
			ss.Append(cfs);

			CellStyles css = new CellStyles();
			CellStyle cs = new CellStyle();
			cs.Name = "Normal";
			cs.FormatId = 0;
			cs.BuiltinId = 0;
			css.Append(cs);
			css.Count = (uint)css.ChildElements.Count;
			ss.Append(css);

			DifferentialFormats dfs = new DifferentialFormats();
			dfs.Count = 0;
			ss.Append(dfs);

			TableStyles tss = new TableStyles();
			tss.Count = 0;
			tss.DefaultTableStyle = "TableStyleMedium9";
			tss.DefaultPivotStyle = "PivotStyleLight16";
			ss.Append(tss);

			return ss;
		}
	}
}