The Problem: Microsoft.Office.Interop.Excel and Server Compatibility
While building a tool to automate the import and export of content copies from Excel files, everything functioned smoothly on my local workstation. However, issues surfaced when deploying the solution to a server environment. The application threw exceptions related to the Microsoft.Office.Interop.Excel assembly, which relies on a full Excel installation and desktop interaction — something not feasible or recommended for server environments. To overcome this limitation, I turned to OpenXML SDK, a lightweight, server-friendly alternative that enables reading, writing, and editing Excel files without needing Excel installed. This approach not only resolved the compatibility issues but also improved the efficiency and portability of the tool across environments.
Could not load file or assembly 'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies
So, the reason is implied that it was working on my local IIS since I have MS Office installed but we can’t have such privileges on Server.
The Solution: OpenXML SDK for Server-Friendly Excel File Handling
As an alternate solution to this issue, I opted for OpenXML SDK. The Open XML SDK 2.0 provided by Microsoft simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages.
Getting Started with OpenXML SDK
In order to use the classes, you must:
- Add as a reference the DocumentFormat.OpenXml library from Open XML SDK 2.0, and WindowsBase in your project; adding in Visual Studio is straightforward. Go to your
Solution Explorer> right click onreferencesand then clickManage NuGet Packages. - Search in tab “Online” for
DocumentFormat.OpenXmland install it. - You would also need to Add Reference to “WindowsBase” library.
By using the OpenXML SDK, you can avoid the need for Excel installation, making your tool more efficient, portable, and compatible with server environments.
Following namespaces should be referenced from your code now:
using System.IO; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet;
Create four service classes in your project and add the below code in respective class files:
- SLExcelData
- SLExcelStatus
- SLExcelReader
- SLExcelWriter
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Spreadsheet;
namespace BusinessLayer
{
public class SLExcelData
{
public SLExcelStatus Status { get; set; }
public Columns DataColumns { get; set; }
public List<string> Headers { get; set; }
public List<List<string>> DataRows { get; set; }
public string SheetName { get; set; }
public SLExcelData()
{
Status = new SLExcelStatus();
Headers = new List<string>();
DataRows = new List<List<string>>();
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Businessage { get; set;Space(Message); }
}
}Layer
{
public class SLExcelStatus
{
public string Mess }
public bool Success
{
get { return string.IsNullOrWhite
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;
using System.Web;
namespace BusinessLayer
{
public class SLExcelReader
{
private string GetColumnName(string cellReference)
{
var regex = new Regex("[A-Za-z]+");
var match = regex.Match(cellReference);
return match.Value;
}
private int ConvertColumnNameToNumber(string columnName)
{
var alpha = new Regex("^[A-Z]+$");
if (!alpha.IsMatch(columnName)) throw new ArgumentException();
char[] colLetters = columnName.ToCharArray();
Array.Reverse(colLetters);
var convertedValue = 0;
for (int i = 0; i < colLetters.Length; i++)
{
char letter = colLetters[i];
// ASCII 'A' = 65
int current = i == 0 ? letter - 65 : letter - 64;
convertedValue += current * (int)Math.Pow(26, i);
}
return convertedValue;
}
private IEnumerator<Cell> GetExcelCellEnumerator(Row row)
{
int currentCount = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
string columnName = GetColumnName(cell.CellReference);
int currentColumnIndex = ConvertColumnNameToNumber(columnName);
for (; currentCount < currentColumnIndex; currentCount++)
{
var emptycell = new Cell()
{
DataType = null,
CellValue = new CellValue(string.Empty)
};
yield return emptycell;
}
yield return cell;
currentCount++;
}
}
private string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
{
var cellValue = cell.CellValue;
var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
text = workbookPart.SharedStringTablePart.SharedStringTable
.Elements<SharedStringItem>().ElementAt(
Convert.ToInt32(cell.CellValue.Text)).InnerText;
}
return (text ?? string.Empty).Trim();
}
public SLExcelData ReadExcel(string FilePath)
{
var data = new SLExcelData();
// Open the excel document
WorkbookPart workbookPart; List<Row> rows;
try
{
var document = SpreadsheetDocument.Open(FilePath, false);
workbookPart = document.WorkbookPart;
var sheets = workbookPart.Workbook.Descendants<Sheet>();
var sheet = sheets.First();
data.SheetName = sheet.Name;
var workSheet = ((WorksheetPart)workbookPart
.GetPartById(sheet.Id)).Worksheet;
var columns = workSheet.Descendants<Columns>().FirstOrDefault();
data.DataColumns = columns;
var sheetData = workSheet.Elements<SheetData>().First();
rows = sheetData.Elements<Row>().ToList();
}
catch (Exception e)
{
data.Status.Message = "Unable to open the file";
return data;
}
// Read the header
if (rows.Count > 0)
{
var row = rows[0];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
data.Headers.Add(text);
}
}
// Read the sheet data
if (rows.Count > 1)
{
for (var i = 1; i < rows.Count; i++)
{
var dataRow = new List<string>();
data.DataRows.Add(dataRow);
var row = rows[i];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
dataRow.Add(text);
}
}
}
return data;
}
public SLExcelData ReadExcel(HttpPostedFileBase file)
{
var data = new SLExcelData();
// Check if the file is excel
if (file.ContentLength <= 0)
{
data.Status.Message = "You uploaded an empty file";
return data;
}
if (file.ContentType
!= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
data.Status.Message
= "Please upload a valid excel file of version 2007 and above";
return data;
}
// Open the excel document
WorkbookPart workbookPart; List<Row> rows;
try
{
var document = SpreadsheetDocument.Open(file.InputStream, false);
workbookPart = document.WorkbookPart;
var sheets = workbookPart.Workbook.Descendants<Sheet>();
var sheet = sheets.First();
data.SheetName = sheet.Name;
var workSheet = ((WorksheetPart)workbookPart
.GetPartById(sheet.Id)).Worksheet;
var columns = workSheet.Descendants<Columns>().FirstOrDefault();
data.DataColumns = columns;
var sheetData = workSheet.Elements<SheetData>().First();
rows = sheetData.Elements<Row>().ToList();
}
catch (Exception e)
{
data.Status.Message = "Unable to open the file";
return data;
}
// Read the header
if (rows.Count > 0)
{
var row = rows[0];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
data.Headers.Add(text);
}
}
// Read the sheet data
if (rows.Count > 1)
{
for (var i = 1; i < rows.Count; i++)
{
var dataRow = new List<string>();
data.DataRows.Add(dataRow);
var row = rows[i];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
dataRow.Add(text);
}
}
}
return data;
}
}
}
using System;
using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace BusinessLayer
{
public class SLExcelWriter
{
private string ColumnLetter(int intCol)
{
var intFirstLetter = ((intCol) / 676) + 64;
var intSecondLetter = ((intCol % 676) / 26) + 64;
var intThirdLetter = (intCol % 26) + 65;
var firstLetter = (intFirstLetter > 64)
? (char)intFirstLetter : ' ';
var secondLetter = (intSecondLetter > 64)
? (char)intSecondLetter : ' ';
var thirdLetter = (char)intThirdLetter;
return string.Concat(firstLetter, secondLetter,
thirdLetter).Trim();
}
private Cell CreateTextCell(string header, UInt32 index,
string text)
{
var cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = header + index
};
var istring = new InlineString();
var t = new Text { Text = text };
istring.AppendChild(t);
cell.AppendChild(istring);
return cell;
}
public byte[] GenerateExcel(SLExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument
.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart
.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
// Add header
UInt32 rowIdex = 0;
var row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0)
{
// Add the column configuration if available
if (data.DataColumns != null)
{
var columns = (Columns)data.DataColumns.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart
.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row { RowIndex = ++rowIdex };
sheetData.AppendChild(row);
foreach (var callData in rowData)
{
var cell = CreateTextCell(ColumnLetter(cellIdex++),
rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return stream.ToArray();
}
}
}
Once you are able to include and compile your project with above files, the final step is to consume them to read/write the data. Below are the code snippet to help you on this:
//READING DATA FROM EXCEL AND POPULATING A LIST OF MyRow OBJECT
//MyRow is a dummy object containing properties "Title", "Name", "Description" mapped to columns in excel
var myRows = new List<MyRow>();
var data = (new SLExcelReader()).ReadExcel(excelFileName);
foreach (var row in data.DataRows)
{
MyRow t = new MyRow();
t.Title = Convert.ToString(row[0]);
t.Name = Convert.ToString(row[1]);
t.Description = Convert.ToString(row[2]);
.....
myRows.Add(t);
}
bool ExportToExcel(DataTable dataTable, string excelFilePath, string SheetName)
{
bool status = false;
try
{
int ColumnsCount;
if (dataTable == null || (ColumnsCount = dataTable.Columns.Count) == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!");
}
SLExcelData data = new SLExcelData();
data.SheetName = SheetName;
data.Headers = new List<string>();
for (int i = 0; i < ColumnsCount; i++)
data.Headers.Add(dataTable.Columns[i].ColumnName);
data.DataRows = new List<List<string>>();
int RowsCount = dataTable.Rows.Count;
for (int j = 0; j < RowsCount; j++)
{
List<string> row = new List<string>();
for (int i = 0; i < ColumnsCount; i++)
{
row.Add(Convert.ToString(dataTable.Rows[j][i]));
}
data.DataRows.Add(row);
}
var file = (new SLExcelWriter()).GenerateExcel(data);
File.WriteAllBytes(excelFilePath, file);
status = true;
}
catch (Exception ex)
{
throw new Exception("Error while Export To Excel: " + ex.Message);
}
return status;
}(Visited 3,717 times, 1 visits today)