Open Xml Sdk 2.0 for microsoft office

Introduction
In this article i am going to explain about the common requirement of exporting the data to excel. Most of the product in the market now will be having this functionality for their business needs and there are many SDKs available too to fulfill this requirement.
When i got this requirement for my project i initially thought to use -  Interop Objects  (using Microsoft.Office.Interop.Excel). The drawback of this is - Since it is dependent on Microsoft Office after deploying to the production it expects Microsoft Office to be installed in the deployed environment. Sometimes it is feasible but most of the time it is not. Installing Microsoft Office only for this requirement does not make any sense isn't it ?
Now its time to go to other option - "Open XML SDK", which solves the above problem and its open source too :-)  


Little more on OpenXML
Open XML is an open and standardized format for Office files. The standards used are:

• ECMA-376   
• ISO/IEC 29500-1:2008   

The Open XML file formats use an open standard and are based on well-known technologies: ZIP and XML. Now lets move on to the code part -

OpenXML in Code
Lets create a sample MVC application to demonstrate this - 

• Create New Project -> ASP.Net Web Application -> MVC  ( here MVC is not mandatory as this article focus on OpenXML, since i am comfortable in that i am using it)
• In Home Controller, Index Action call ExportExcel() method like below - 

public ActionResult Index()
{
    ExportExcel();
    return View();
}

Now lets create a sample dataset with sample data to populate the excel file -

public DataTable GetDatatable()
{
 DataTable dt = new DataTable();

 dt.Columns.Add("FirstName", typeof(String));
 dt.Columns.Add("LastName", typeof(String));
 dt.Columns.Add("Address", typeof(String));

 DataRow dr1 = dt.NewRow();
 dr1["FirstName"] = "John";
 dr1["LastName"] = "Martin";
 dr1["Address"] = "CA";

 DataRow dr2 = dt.NewRow();
 dr2["FirstName"] = "Rick";
 dr2["LastName"] = "Fleming";
 dr2["Address"] = "OH";

 DataRow dr3 = dt.NewRow();
 dr3["FirstName"] = "David";
 dr3["LastName"] = "Langer";
 dr3["Address"] = "SA";

 dt.Rows.Add(dr1);
 dt.Rows.Add(dr2);
 dt.Rows.Add(dr3);

 return dt;
}

As you can see the first snippet of code, we have method called - ExportExcel() which is the parent method to export the excel. The definition of this method - 

public void ExportExcel()
{
 DataTable dt = GetDatatable();
 string strFileFullPath= "D:\\Test.xlsx";

 ExportExcelFile.CreateExcel(dt, strFileFullPath);
}

In the above method path of excel is passed, if excel file not found in that location then the new file will be created in that location or else existing file will be used.
So below is the code snippet of "CreateExcel" method and its child methods -

public static class ExportExcelFile
{

 public static bool CreateExcel(DataTable dt, string xlsxFilePath)
 {
  DataSet ds = new DataSet();
  ds.Tables.Add(dt);

  try
  {
   using (SpreadsheetDocument document = SpreadsheetDocument.Create(xlsxFilePath
                                               , SpreadsheetDocumentType.Workbook))
   {
    WriteToTExcelFile(ds, document);
   }
   return true;
  }
  catch (Exception ex)
  {
   return false;
  }

 }

 private static void WriteToTExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
 {
  spreadsheet.AddWorkbookPart();
  spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

  spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

  WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart("rIdStyles");
  Stylesheet stylesheet = new Stylesheet();
  workbookStylesPart.Stylesheet = stylesheet;

  uint worksheetNumber = 1;
  foreach (DataTable dt in ds.Tables)
  {
   string workSheetID = "Sheet" + worksheetNumber.ToString();
   string worksheetName = dt.TableName;

   WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart();
   newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

   newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

   WriteDataTableToExcel(dt, newWorksheetPart);
   newWorksheetPart.Worksheet.Save();

   if (worksheetNumber == 1)
    spreadsheet.WorkbookPart.Workbook
                                               .AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

   spreadsheet.WorkbookPart.Workbook.GetFirstChild()
                                               .AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
   {
    Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
    SheetId = (uint)worksheetNumber,
    Name = dt.TableName
   });

   worksheetNumber++;
  }

  spreadsheet.WorkbookPart.Workbook.Save();
 }


 private static void WriteDataTableToExcel(DataTable dt, WorksheetPart worksheetPart)
 {
  var worksheet = worksheetPart.Worksheet;
  var sheetData = worksheet.GetFirstChild();

  string cellValue = "";

  int numberOfColumns = dt.Columns.Count;
  bool[] IsNumericColumn = new bool[numberOfColumns];

  string[] excelColumnNames = new string[numberOfColumns];
  for (int n = 0; n < numberOfColumns; n++)
   excelColumnNames[n] = GetExcelColumnName(n);

  uint rowIndex = 1;

  var headerRow = new Row { RowIndex = rowIndex };  
  sheetData.Append(headerRow);

  for (int colInx = 0; colInx < numberOfColumns; colInx++)
  {
   DataColumn col = dt.Columns[colInx];
   AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
   IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || 
                                                         (col.DataType.FullName == "System.Int32");
  }

  double cellNumericValue = 0;
  foreach (DataRow dr in dt.Rows)
  {
   ++rowIndex;
   var newExcelRow = new Row { RowIndex = rowIndex };  
   sheetData.Append(newExcelRow);

   for (int colInx = 0; colInx < numberOfColumns; colInx++)
   {
    cellValue = dr.ItemArray[colInx].ToString();

    if (IsNumericColumn[colInx])
    {
     cellNumericValue = 0;
     if (double.TryParse(cellValue, out cellNumericValue))
     {
      cellValue = cellNumericValue.ToString();
      AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString()
                                                                      , cellValue, newExcelRow);
     }
    }
    else
    {
     AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue
                                                                      , newExcelRow);
    }
   }
  }
 }

 private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)
 {
  Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
  CellValue cellValue = new CellValue();
  cellValue.Text = cellStringValue;
  cell.Append(cellValue);
  excelRow.Append(cell);
 }

 private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
 {
  Cell cell = new Cell() { CellReference = cellReference};
  CellValue cellValue = new CellValue();
  cellValue.Text = cellStringValue;
  cell.Append(cellValue);
  excelRow.Append(cell);
 }

 private static string GetExcelColumnName(int columnIndex)
 {
  if (columnIndex < 26)
   return ((char)('A' + columnIndex)).ToString();

  char firstChar = (char)('A' + (columnIndex / 26) - 1);
  char secondChar = (char)('A' + (columnIndex % 26));

  return string.Format("{0}{1}", firstChar, secondChar);
 }
}

Below is the file generated after exporting the data -

Hope this helps. Comment below in case of clarifications or doubts.