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

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

namespace ExcelOpenXmlWithCustomWidths
{
	class Program
	{
		static void Main(string[] args)
		{
			string sFile = "ExcelOpenXmlWithCustomWidths.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();

					Columns columns = new Columns();
					columns.Append(CreateColumnData(1, 1, 11));
					columns.Append(CreateColumnData(2, 4, 23.5703125));
					columns.Append(CreateColumnData(6, 6, 6.5703125));
					ws.Append(columns);

					Row r;
					Cell c;

					// header
					r = new Row();
					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "A1";
					c.CellValue = new CellValue("Product ID");
					r.Append(c);

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

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

					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "D1";
					c.CellValue = new CellValue("Discount Description");
					r.Append(c);

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

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

					// content
					r = new Row();
					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "A2";
					c.CellValue = new CellValue("PROD12345");
					r.Append(c);

					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "B2";
					c.CellValue = new CellValue("Iced Lemon Tea");
					r.Append(c);

					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "C2";
					c.CellValue = new CellValue("Special Iced Lemon Tea");
					r.Append(c);

					c = new Cell();
					c.DataType = CellValues.String;
					c.CellReference = "D2";
					c.CellValue = new CellValue("Iced Lemon Tea (50% off)");
					r.Append(c);

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

					c = new Cell();
					c.StyleIndex = 3;
					c.DataType = CellValues.Number;
					c.CellReference = "F2";
					c.CellValue = new CellValue("5.95");
					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;
		}
	}
}