Server-side Office Integration -4: Serializing and De-serializing Excel spreadsheet by using ExcelPackage
In the previous blog (Office Integration -3), we serialized the excel spreadsheet into a stream object then open a Package on it. Now, according to our scenario, it is time to do some validation. Since I prefer doing validation on the objects rather than the Package directly, first we de-serialize the stream to the PersonAddress typed objects. Again according to plan; the validation checks:
- if column numbers matches
- if required data exist.
Yes, I agree the validation would be complex in the real world application, however here we focus on Excel package rather Validation. Once the validation passes for all the records, we then serialize the PersonAddress objects back into an XML document. At the end, we will push the XML doc into database stored proc, which will execute importing and returning the records with updated fields (AddressID and ModifiedDate).
/// <summary>
/// Transforms the Excel Worksheet into XML document.
/// </summary>
private void TranslateXlToXmlDocument()
{
int rowCurrent = 1; //excel row index
bool flgContinue = true;
XmlElement root = _xmlDoc.CreateElement(_rootNodeName); //creates root element
_rowCount = Convert.ToInt32(_xlWS.WorksheetXml.SelectSingleNode("//d:sheetData/d:row[last()]",
_xlWS.NameSpaceManager).Attributes.GetNamedItem("r").Value);
//For each data row in the excel worksheet, generate a PersonAddress
//Then validate the object against to business rules
//If fails, then keep running validation check for the rest and omit creating xml document
//If succeeds, convert the objet into xml element having attributes for each object property then append it.
while (++rowCurrent <= _rowCount)
{
//create PersonAddress object from the xml document
PersonAddress personAddress = TranslateXlRowToEntity(rowCurrent);//ws index starts with 1 and plus header
ValidateEntity(personAddress, rowCurrent);
if (flgContinue && _errors.Count > 0)
flgContinue = false;
if (flgContinue)
{
XmlNode node = ImportUtility.ConvertToXml<PersonAddress>(personAddress, _xmlDoc, _childNodeName);
if (node != null)
root.AppendChild(node);
}
}
if (flgContinue)
{
_xmlDoc.AppendChild(root);
//#if DEBUG
#region testing Xml
string strPath = System.Web.Hosting.HostingEnvironment.MapPath("~") + @"\App_Data";
if (!Directory.Exists(strPath))
Directory.CreateDirectory(strPath);
string path = Path.Combine(strPath, "ExcelToXml.xml");
if (File.Exists(path))
File.Delete(path);
_xmlDoc.Save(path);
#endregion
//#endif
}
}
|
/// <summary>
/// Returns the XML Node generated from the object with T type
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t">the Object</param>
/// <param name="doc">The XML Document</param>
/// <param name="nodeName">The Name for the Xml Node </param>
/// <returns></returns>
public static XmlNode ConvertToXml<T>(T t, XmlDocument doc, string nodeName)
{
XmlNode node = doc.CreateElement(nodeName == null ? t.GetType().Name : nodeName);
PropertyInfo[] properties = t.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
XmlAttribute attr = doc.CreateAttribute(property.Name);
attr.Value = (property.GetValue(t, null) != null) ? property.GetValue(t, null).ToString() : "";
node.Attributes.Append(attr);
}
return node;
}
|