I was working on creating a tool that can help automate importing and exporting content copies from Excel. The solution worked well on my local workstation, but failed to address the need when deployed on Server. Instead, I was getting an exception relating to Microsoft.Office.Interop.Excel assembly.
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.
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.
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 on references and then click Manage NuGet Packages. Then search in tab “Online” for DocumentFormat.OpenXml and install it. You would also need to Add Reference to “WindowsBase” library.
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 BusinessLayer
{
public class SLExcelStatus
{
public string Message { get; set; }
public bool Success
{
get { return string.IsNullOrWhiteSpace(Message); }
}
}
}
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,686 times, 1 visits today)