Manipulating excel files in C#

C# Excel

Thanks to stackoverflow I found out that Microsoft.Office.Interop.Excel is not supported in server-side code such as Microsoft Active Server Pages (ASP), ASP.NET, DCOM, or a Windows NT service.

As an alternative, you can use a free SDK from Microsoft – Open XML SDK 2.5 for Office. It provides strongly-typed classes that can be used to manipulate documents that adhere to the Office Open XML File Formats Specification.

How to use it:

  1. Download and install the SDK.
  2. In Solution Explorer, right-click on References ยป Add Reference. On the .NET tab select WindowsBase and DocumentFormat.OpenXml.
    • If DocumentFormat.OpenXml is not available in the list, copy the dll file from GAC.
    • For .NET framework version 4.0 it is located under the folder %windir%\Microsoft.NET\assembly\GAC_MSIL\DocumentFormat.OpenXml
    • Copy DocumentFormat.OpenXml.dll into your solution and add a reference to it.
  3. As an example I have created a simple method in C#, that you can use inside an asp.net web site to create an excel file in memory and then download it. The excel file created with this sample method contains one column with a header row and a content row. The header has a dark blue background and the text is white and appears in bold.
    Excel Example
public void DownloadExcelFile()
{
	var memoryStream = new MemoryStream();

	//Create a spreadsheet document in memory (by default the type is xlsx)
	using (var spreadsheetDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
	{
		//Add a WorkbookPart to the document. (The workbook element is the top level element. It contains elements and attributes that encompass the data content of the workbook.)
		var workbookpart = spreadsheetDocument.AddWorkbookPart();
		workbookpart.Workbook = new Workbook();
		workbookpart.Workbook.AppendChild(new FileVersion {ApplicationName = "Microsoft Office Excel"});

		//Add a WorksheetPart to the WorkbookPart.
		var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();               
		worksheetPart.Worksheet = new Worksheet();

		//Set a custom width for the first column
		var columns = new Columns();
		columns.AppendChild(new Column { Min = 1, Max = 1, CustomWidth = true, Width = 15 });
		worksheetPart.Worksheet.AppendChild(columns);

		//Important! We must associate the sheet data after the columns otherwise the file will fail to open.
		var sheetData = new SheetData();
		worksheetPart.Worksheet.AppendChild(sheetData);

		//Add styles to format the header cells
		var workbookStylesPart = workbookpart.AddNewPart<WorkbookStylesPart>();
		workbookStylesPart.Stylesheet = new Stylesheet(
			new Fonts(
				new Font(), // Index 0 - default font
				new Font(   // Index 1 - The bold white font
					new Bold(),
					new Color {Rgb = new HexBinaryValue {Value = "FFFFFF"}}
					)
				),
			new Fills(
				new Fill(new PatternFill {PatternType = PatternValues.None}),                                                                            // Index 0 - default fill (should be always none)
				new Fill(new PatternFill { PatternType = PatternValues.Gray125 }),                                                                       // Index 1 - it's required and should always be gray 125
				new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue { Value = "000080" } }) { PatternType = PatternValues.Solid })   // Index 2 - dark blue fill
				),
			new Borders(
				new Border( // Index 0 - The default border
					new LeftBorder(),
					new RightBorder(),
					new TopBorder(),
					new BottomBorder(),
					new DiagonalBorder())
				),
			new CellFormats(
				new CellFormat {FontId = 0, FillId = 0, BorderId = 0}, // Index 0 - default cell style
				new CellFormat {FontId = 1, FillId = 2, BorderId = 0, ApplyFont = true, ApplyFill = true, } // Index 1 - Blue background cell with white bold text
				)
			);               

		//Add Sheets to the Workbook. (Sheets: represents the collection of worksheets in the workbook. The sheets are the central structure within a workbook, and contain the text, numbers, dates, formulas, and other elements of a workbook.)
		var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());

		//Append a new worksheet and associate it with the workbook.
		sheets.AppendChild(new Sheet
			{
				Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
				SheetId = 1,
				Name = "Sheet 1"
			});

		//Populate the sheet data
		var headerRow = sheetData.AppendChild(new Row());
		headerRow.AppendChild(new Cell
			{
				CellValue = new CellValue("Header text"),
				DataType = CellValues.String,
				StyleIndex = 1
			});

		var contentRow = sheetData.AppendChild(new Row());
		contentRow.AppendChild(new Cell
		{
			CellValue = new CellValue("Value"),
			DataType = CellValues.String
		});                               

		workbookpart.Workbook.Save();
	}
	
	Response.Clear();
	Response.CacheControl = "Private";
	Response.Cache.SetExpires(DateTime.Now.AddSeconds(30));
	Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
	Response.AddHeader("Content-Type", "application/octet-stream");
	Response.AppendHeader("content-disposition", string.Format("attachment; filename=\"test.xlsx\"; size={0}", memoryStream.Length));
	Response.Flush();            
	Response.BinaryWrite(memoryStream.ToArray());

	Response.Flush();
	Response.End(); 
}