Server-side Office Integration -3: Use of Excel Package for generating & updating Excel from the server side
So What is ExcelPackage really?
In short, it is to create and/or modify Excel spreadsheets from the server. To me, it is an open source API that includes classes and wrappers around .NET 3.0 System.IO.Packaging class library according to new Open XML standards. It is called Package because it contains multiple data objects, Worksheets, Worksheet, Row, Cell, Column, Styles, etc... A good example would be Zip file. It is aimed to be a complete solution, however it is still in progress, applying styles, rows cloning, etc waiting for heroes... -Hey it is open source... Thanks Dr. John Tunnicliffe, for his generous contribution.
How does it work?
If you look at the System.IO.Packaging namespace, you will see there are different ways of opening a Package. And I am particularly talking about one of them -opening a Package from a stream object:
Steps:
- Dump the excel data into a FileStream typed object,
- Open a Package object on the stream object,
- Construct an Excel worksheet in the package at given index.
- Now, we can start all kinds of CRUD (Create, Read, Update, Delete) operations on the worksheet for its rows, columns, cells, styles, properties. Easy right:)
/// <summary>
/// Imports Excel spreadsheet into Database
/// </summary>
/// <param name="results">The list of errors if any</param>
/// <returns>Array of bytes of after excel saved</returns>
public byte[] ImportExcelData(out List<string> results)
{
DataTable dt;
_xmlDoc = new XmlDocument();
_errors = new List<string>();
_bytes = File.ReadAllBytes(_fileName);
if (_bytes == null || _bytes.Length == 0)
throw new Exception("No data found in the file. ");
FileInfo fi = new FileInfo(_fileName);
//writes data into filestream object
using (FileStream fs = fi.OpenWrite())
{
fs.Write(_bytes, 0, _bytes.Length);
fs.Flush();
}
//create ExcelPackage with fileinfo object created above
using (_xlPackage = new ExcelPackage(fi))
{
//initialize the excel worksheet object from the excel package at given index.
_xlWS = _xlPackage.Workbook.Worksheets[_sheetIndex];
if (_xlWS == null || !_xlWS.WorksheetXml.HasChildNodes)
throw new Exception("No worksheet found at given index.");
//transforms ExcelWorksheet context to Xml document.
TranslateXlToXmlDocument();
if (_errors.Count == 0)
{
//inserts XML document into database table
dt = InsertXmlData();
//syncs the excel worksheet with database data
UpdateXlWS(dt);
//accepts changes made to excel worksheet --> excel package.
_xlPackage.Save();
}
}
fi.Refresh();
byte[] updatedData = (_errors.Count == 0) ? File.ReadAllBytes(fi.FullName) : null;
results = _errors;
return updatedData;
} |