Mecit Atmaca

/// <summary>
/// Displays data (if exist) as an Excel spreadsheet on user browser.
/// </summary>
/// <param name="bytes">The bytes to be the content of the excel spreadsheet</param>
private void DisplayNewExcelFile(byte[] bytes)
{
    if (bytes == null || bytes.Length == 0)
    {
        litMsg.Text = "No data found.";
        return;
    }

    Response.ClearHeaders();
    Response.ContentType = "application/vnd.ms-excel";
    Page.EnableViewState = false;
    Response.AddHeader("content-disposition", "attachment;filename=PersonAddress_Modified.xlsx");
    Response.BinaryWrite(bytes);

    try
    {
        Response.End();
        //this causes a System.Threading.ThreadAbortException, which ends the current request.
    }
    catch { }
}

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ImportPersonAddressXml](
        @XmlDoc xml
)
AS 

BEGIN

SET NOCOUNT ON
DECLARE @CurrentDt datetime; 
SET @CurrentDt = getDate();

BEGIN TRANSACTION

INSERT INTO Person.Address(AddressLine1, AddressLine2, City, StateProvinceID,
 PostalCode, rowguid, ModifiedDate)    
    SELECT 
        P.e.value('@AddressLine1','nvarchar(60)'),
        P.e.value('@AddressLine2','nvarchar(60)'),
        P.e.value('@City','nvarchar(30)'),
        P.e.value('@StateProvinceID','int'),
        P.e.value('@PostalCode','nvarchar(15)'),
        P.e.value('@RowGuid','uniqueIdentifier'),
        @CurrentDt
        FROM   @XmlDoc.nodes('/Root/Data') P(e)

COMMIT TRANSACTION

SELECT * FROM Person.Address WHERE ModifiedDate = @CurrentDt

END

/// <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;
}

/// <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;
}