Showing posts with label Open XML. Show all posts
Showing posts with label Open XML. Show all posts

Monday, 13 June 2016

Copy and Update Excel sheet with Dynamic values using cell ID

//Copy and Update Excel sheet with Dynamic values using cell ID

public string TemplateCreation()
{
string destinationFilePath = string.Format(Convert.ToString(ConfigurationManager.AppSettings[Constants.Configuration.DestinationFilePath]) + @"\{0}.xlsx", DateTime.Now.ToString(CultureInfo.InvariantCulture).Replace(":", string.empty).Replace("/", "_").Replace(" ", "_"));

            string sourceFilePath = Convert.ToString(ConfigurationManager.AppSettings[Constants.Configuration.SourceFilePath]);

            File.Copy(sourceFilePath, destinationFilePath);


            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(destinationFilePath, true))
            {
                WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, Constants.VendorFormConstants.Sheet1);
                if (worksheetPart != null)
                {
UpdateCellData(worksheetPart, Constants.CddSections.GCell, 2, "Value");


                    // Save the worksheet.
                    worksheetPart.Worksheet.Save();
                }
         }
 return destinationFilePath;
        }
 /// <summary>
        /// Update Cell Data
        /// </summary>
        /// <param name="worksheetPart"></param>
        /// <param name="cellColumn"></param>
        /// <param name="cellRow"></param>
        /// <param name="cellValue"></param>
        public void UpdateCellData(WorksheetPart worksheetPart, string cellColumn, uint cellRow, string cellValue)
        {
            Cell cddEntitiesValue = GetCell(worksheetPart.Worksheet, cellColumn, cellRow);
            cddEntitiesValue.CellValue = new CellValue(cellValue);
            cddEntitiesValue.DataType = new EnumValue<CellValues>(CellValues.String);
        }

        /// <summary>
        /// Get Celll Based WorkSheet, Column and Row Index
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="columnName"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
        {
            Row row = GetRow(worksheet, rowIndex);

            if (row == null)
                return null;

            return row.Elements<Cell>().First(c => String.Compare(c.CellReference.Value, columnName + rowIndex, StringComparison.OrdinalIgnoreCase) == 0);
        }

        /// <summary>
        /// Getting Work Sheet Row
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private Row GetRow(Worksheet worksheet, uint rowIndex)
        {
            return worksheet.GetFirstChild<SheetData>().Elements<Row>().First(r => r.RowIndex == rowIndex);
        }

        /// <summary>
        /// Getting worksheet based sheet in Document
        /// </summary>
        /// <param name="document"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
        {
            IEnumerable<Sheet> sheets =
               document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
               Elements<Sheet>().Where(s => s.Name == sheetName);



            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)
                 document.WorkbookPart.GetPartById(relationshipId);
            return worksheetPart;
        }




Monday, 10 February 2014

Update Word Template Dynamically using Open XML

using System.Data;

using System.Text;
using System.IO;
using System.Xml;
using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml;

// use this method in you Page
DataTable dtDoc = new DataTable();
dtDoc =CreateDocDataTable();
SearchAndReplace(Sourctfilepath, DestinationfilePath, dtDoc);


//Creating Data table
public DataTable CreateDocDataTable()
    {
        DataTable dtDoc = new DataTable();
        DataColumn dcKey_Name = new DataColumn("Key_Name");
        DataColumn dcOutputValue = new DataColumn("OutputValue");
        dtDoc.Columns.Add(dcKey_Name);
        dtDoc.Columns.Add(dcOutputValue);

        DataRow drContractRef = dtDoc.NewRow();
        drContractRef["Key_Name"] = "<<ContractRef#>>";

       drContractRef["OutputValue"] = "value" ;

       dtDoc.Rows.Add(drContractRef);
     
        return dtDoc;

}



    public static byte[] StreamFile(string filename)
    {
        FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read);

        // Create a byte array of file stream length
        byte[] FileData = new byte[fs.Length];

        //Read block of bytes from stream into the byte array
        fs.Read(FileData, 0, System.Convert.ToInt32(fs.Length));

        //Close the File Stream
        fs.Close();
        return FileData; //return the byte data
    }

//Save Output file with Memory Stream
    public static void SaveMemoryStream(MemoryStream ms, string FileName)
    {
        FileStream outStream = File.OpenWrite(FileName);
        ms.WriteTo(outStream);
        outStream.Flush();
        outStream.Close();
    }


//Search and Replace data in Word document
 public static void SearchAndReplace(string strSourcePath, string outPutFilepath, DataTable dtKey_Vlaues)
    {

        //SPFile objSpFile = myWeb.GetFile(strSourcePath);
        //byte[] fileByteDoc = objSpFile.OpenBinary();


        byte[] fileByteDoc = StreamFile(strSourcePath);
        MemoryStream streamSource = new MemoryStream();
        streamSource.Write(fileByteDoc, 0, (int)fileByteDoc.Length);

        using (var mainDoc = WordprocessingDocument.Open(streamSource, false))


        using (MemoryStream outPutMemStream = new MemoryStream())
        {
            using (var resultDoc = WordprocessingDocument.Create(outPutMemStream,
              WordprocessingDocumentType.Document))
            {
                // copy parts from source document to new document
                foreach (var part in mainDoc.Parts)
                    resultDoc.AddPart(part.OpenXmlPart, part.RelationshipId);
                // perform replacements in resultDoc.MainDocumentPart
                // ...
            }
            using (WordprocessingDocument wordDoc = WordprocessingDocument.Open(outPutMemStream, true))
            {
                string docText = null;
                using (StreamReader sr = new StreamReader(wordDoc.MainDocumentPart.GetStream()))
                {
                    docText = sr.ReadToEnd();
                }
                XmlDocument xmlDoc;
                xmlDoc = GetXmlDocument(wordDoc.MainDocumentPart.DocumentSettingsPart);
                XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmlDoc.NameTable);
                nsmgr.AddNamespace("w",
                    "http://schemas.openxmlformats.org/wordprocessingml/2006/main");
                XmlNodeList trackedRevisions =
                    xmlDoc.SelectNodes("descendant::w:trackRevisions", nsmgr);
                if (trackedRevisions.Count > 0)
                    throw new SearchAndReplaceException(
                        "Revision tracking is turned on for document.");

                xmlDoc = GetXmlDocument(wordDoc.MainDocumentPart);
                foreach (DataRow theRow in dtKey_Vlaues.Rows)
                {
                    SearchAndReplaceInXmlDocument(xmlDoc, theRow["Key_Name"].ToString(), strReturnDate(theRow["OutputValue"].ToString()), true);
                }
                PutXmlDocument(wordDoc.MainDocumentPart, xmlDoc);

                int headerpartscount = wordDoc.MainDocumentPart.HeaderParts.Count();
                foreach (var headerpart in wordDoc.MainDocumentPart.HeaderParts)
                {

                    xmlDoc = GetXmlDocument(headerpart);

                    foreach (DataRow theRow in dtKey_Vlaues.Rows)
                    {
                        SearchAndReplaceInXmlDocument(xmlDoc, theRow["Key_Name"].ToString(), strReturnDate(theRow["OutputValue"].ToString()), true);
                    }
                    PutXmlDocument(headerpart, xmlDoc);
                }


                //myWeb.Files.Add(outPutFilepath, outPutMemStream, true);

                SaveMemoryStream(outPutMemStream, outPutFilepath);

                //outPutMemStream.Close();  //closed in the above function                      
                wordDoc.Close();
                wordDoc.Dispose();
            }
        }


    }


    private static string strReturnDate(string strDate)
    {
        string strReturn = string.Empty;
        try
        {

            return strReturn = string.Format("{0:dd-MMM-yyyy}", Convert.ToDateTime(strDate));
        }
        catch (Exception ex)
        {
            return strDate;
        }
    }

//Creating base class method for exceptions
    public class SearchAndReplaceException : Exception
    {
        public SearchAndReplaceException(string message) : base(message) { }
    }

//Fetching data in XML Format on Word document
    public static XmlDocument GetXmlDocument(OpenXmlPart part)
    {
        XmlDocument xmlDoc = new XmlDocument();
        using (Stream partStream = part.GetStream())
        using (XmlReader partXmlReader = XmlReader.Create(partStream))
            xmlDoc.Load(partXmlReader);
        return xmlDoc;
    }

//Search and Replace Data in XML Format
private static void SearchAndReplaceInXmlDocument(XmlDocument xmlDocument, string search, string replace, bool matchCase)
    {

        XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmlDocument.NameTable);
        nsmgr.AddNamespace("w",
            "http://schemas.openxmlformats.org/wordprocessingml/2006/main");
        var paragraphs = xmlDocument.SelectNodes("descendant::w:p", nsmgr);
        foreach (var paragraph in paragraphs)
            SearchAndReplaceInParagraph((XmlElement)paragraph, search, replace, matchCase);
    }

//Open file and Write data in XML Format
    public static void PutXmlDocument(OpenXmlPart part, XmlDocument xmlDoc)
    {
        using (Stream partStream = part.GetStream(FileMode.Create, FileAccess.Write))
        using (XmlWriter partXmlWriter = XmlWriter.Create(partStream))
            xmlDoc.Save(partXmlWriter);
    }

// Search and Replace data in XML Format
static void SearchAndReplaceInParagraph(XmlElement paragraph, string search, string replace, bool matchCase)
    {
        try
        {
            XmlDocument xmlDoc = paragraph.OwnerDocument;
            string wordNamespace =
                "http://schemas.openxmlformats.org/wordprocessingml/2006/main";
            XmlNamespaceManager nsmgr =
                new XmlNamespaceManager(xmlDoc.NameTable);
            nsmgr.AddNamespace("w", wordNamespace);
            XmlNodeList paragraphText = paragraph.SelectNodes("descendant::w:t", nsmgr);
            StringBuilder sb = new StringBuilder();
            foreach (XmlNode text in paragraphText)
                sb.Append(((XmlElement)text).InnerText);
            if (sb.ToString().Contains(search) ||
                (!matchCase && sb.ToString().ToUpper().Contains(search.ToUpper())))
            {
                XmlNodeList runs = paragraph.SelectNodes("child::w:r", nsmgr);
                foreach (XmlElement run in runs)
                {
                    XmlNodeList childElements = run.SelectNodes("child::*", nsmgr);
                    if (childElements.Count > 0)
                    {
                        XmlElement last = (XmlElement)childElements[childElements.Count - 1];
                        for (int c = childElements.Count - 1; c >= 0; --c)
                        {
                            if (childElements[c].Name == "w:rPr")
                                continue;
                            if (childElements[c].Name == "w:t")
                            {
                                string textElementString = childElements[c].InnerText;
                                for (int i = textElementString.Length - 1; i >= 0; --i)
                                {
                                    XmlElement newRun =
                                        xmlDoc.CreateElement("w:r", wordNamespace);
                                    XmlElement runProps =
                                        (XmlElement)run.SelectSingleNode("child::w:rPr", nsmgr);
                                    if (runProps != null)
                                    {
                                        XmlElement newRunProps =
                                            (XmlElement)runProps.CloneNode(true);
                                        newRun.AppendChild(newRunProps);
                                    }
                                    XmlElement newTextElement =
                                        xmlDoc.CreateElement("w:t", wordNamespace);
                                    XmlText newText =
                                        xmlDoc.CreateTextNode(textElementString[i].ToString());
                                    newTextElement.AppendChild(newText);
                                    if (textElementString[i] == ' ')
                                    {
                                        XmlAttribute xmlSpace = xmlDoc.CreateAttribute(
                                            "xml", "space",
                                            "http://www.w3.org/XML/1998/namespace");
                                        xmlSpace.Value = "preserve";
                                        newTextElement.Attributes.Append(xmlSpace);
                                    }
                                    newRun.AppendChild(newTextElement);
                                    paragraph.InsertAfter(newRun, run);
                                }
                            }
                            else
                            {
                                XmlElement newRun = xmlDoc.CreateElement("w:r", wordNamespace);
                                XmlElement runProps =
                                    (XmlElement)run.SelectSingleNode("child::w:rPr", nsmgr);
                                if (runProps != null)
                                {
                                    XmlElement newRunProps =
                                        (XmlElement)runProps.CloneNode(true);
                                    newRun.AppendChild(newRunProps);
                                }
                                XmlElement newChildElement =
                                    (XmlElement)childElements[c].CloneNode(true);
                                newRun.AppendChild(newChildElement);
                                paragraph.InsertAfter(newRun, run);
                            }
                        }
                        paragraph.RemoveChild(run);
                    }
                }

                while (true)
                {
                    bool cont = false;
                    runs = paragraph.SelectNodes("child::w:r", nsmgr);
                    for (int i = 0; i <= runs.Count - search.Length; ++i)
                    {
                        bool match = true;
                        for (int c = 0; c < search.Length; ++c)
                        {
                            XmlElement textElement =
                                (XmlElement)runs[i + c].SelectSingleNode("child::w:t", nsmgr);
                            if (textElement == null)
                            {
                                match = false;
                                break;
                            }
                            if (textElement.InnerText == search[c].ToString())
                                continue;
                            if (!matchCase &&
                                textElement.InnerText.ToUpper() == search[c].ToString().ToUpper())
                                continue;
                            match = false;
                            break;
                        }
                        if (match)
                        {
                            XmlElement runProps =
                                (XmlElement)runs[i].SelectSingleNode("descendant::w:rPr", nsmgr);
                            XmlElement newRun = xmlDoc.CreateElement("w:r", wordNamespace);
                            if (runProps != null)
                            {
                                XmlElement newRunProps = (XmlElement)runProps.CloneNode(true);
                                newRun.AppendChild(newRunProps);
                            }
                            XmlElement newTextElement =
                                xmlDoc.CreateElement("w:t", wordNamespace);
                            XmlText newText = xmlDoc.CreateTextNode(replace);
                            newTextElement.AppendChild(newText);
                            if (replace[0] == ' ' || replace[replace.Length - 1] == ' ')
                            {
                                XmlAttribute xmlSpace = xmlDoc.CreateAttribute("xml", "space",
                                    "http://www.w3.org/XML/1998/namespace");
                                xmlSpace.Value = "preserve";
                                newTextElement.Attributes.Append(xmlSpace);
                            }
                            newRun.AppendChild(newTextElement);
                            paragraph.InsertAfter(newRun, (XmlNode)runs[i]);
                            for (int c = 0; c < search.Length; ++c)
                                paragraph.RemoveChild(runs[i + c]);
                            cont = true;
                            break;
                        }
                    }
                    if (!cont)
                        break;
                }

                // Consolidate adjacent runs that have only text elements, and have the
                // same run properties. This isn't necessary to create a valid document,
                // however, having the split runs is a bit messy.
                XmlNodeList children = paragraph.SelectNodes("child::*", nsmgr);
                List<int> matchId = new List<int>();
                int id = 0;
                for (int c = 0; c < children.Count; ++c)
                {
                    if (c == 0)
                    {
                        matchId.Add(id);
                        continue;
                    }
                    if (children[c].Name == "w:r" &&
                        children[c - 1].Name == "w:r" &&
                        children[c].SelectSingleNode("w:t", nsmgr) != null &&
                        children[c - 1].SelectSingleNode("w:t", nsmgr) != null)
                    {
                        XmlElement runProps =
                            (XmlElement)children[c].SelectSingleNode("w:rPr", nsmgr);
                        XmlElement lastRunProps =
                            (XmlElement)children[c - 1].SelectSingleNode("w:rPr", nsmgr);
                        if ((runProps == null && lastRunProps != null) ||
                            (runProps != null && lastRunProps == null))
                        {
                            matchId.Add(++id);
                            continue;
                        }
                        if (runProps != null && runProps.InnerXml != lastRunProps.InnerXml)
                        {
                            matchId.Add(++id);
                            continue;
                        }
                        matchId.Add(id);
                        continue;
                    }
                    matchId.Add(++id);
                }

                for (int i = 0; i <= id; ++i)
                {
                    var x1 = matchId.IndexOf(i);
                    var x2 = matchId.LastIndexOf(i);
                    if (x1 == x2)
                        continue;
                    StringBuilder sb2 = new StringBuilder();
                    for (int z = x1; z <= x2; ++z)
                        sb2.Append(((XmlElement)children[z]
                            .SelectSingleNode("w:t", nsmgr)).InnerText);
                    XmlElement newRun = xmlDoc.CreateElement("w:r", wordNamespace);
                    XmlElement runProps =
                        (XmlElement)children[x1].SelectSingleNode("child::w:rPr", nsmgr);
                    if (runProps != null)
                    {
                        XmlElement newRunProps = (XmlElement)runProps.CloneNode(true);
                        newRun.AppendChild(newRunProps);
                    }
                    XmlElement newTextElement = xmlDoc.CreateElement("w:t", wordNamespace);
                    XmlText newText = xmlDoc.CreateTextNode(sb2.ToString());
                    newTextElement.AppendChild(newText);
                    if (sb2[0] == ' ' || sb2[sb2.Length - 1] == ' ')
                    {
                        XmlAttribute xmlSpace = xmlDoc.CreateAttribute(
                            "xml", "space", "http://www.w3.org/XML/1998/namespace");
                        xmlSpace.Value = "preserve";
                        newTextElement.Attributes.Append(xmlSpace);
                    }
                    newRun.AppendChild(newTextElement);
                    paragraph.InsertAfter(newRun, children[x2]);
                    for (int z = x1; z <= x2; ++z)
                        paragraph.RemoveChild(children[z]);
                }

                var txbxParagraphs = paragraph.SelectNodes("descendant::w:p", nsmgr);
                foreach (XmlElement p in txbxParagraphs)
                    SearchAndReplaceInParagraph((XmlElement)p, search, replace, matchCase);
            }
        }
        catch (Exception ex)
        {

        }
    }

Thursday, 30 January 2014

Fetching Data From Excel sheet using Open XML

Fetching Data From Excel sheet using Open XML

using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;

Create dataSet and Fetching Data From Excel using ReadData Method

DataSet ds = new DataSet("Details");
DataTable dt = ReadData("FileTotalPath", "SheetName");
dt.TableName = "TableName ";
ds.Tables.Add(dt);


public DataTable ReadData(string xlsxFilePath, string sheetName)
        {
            DataTable dt = new DataTable();
            try
            {

                using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(xlsxFilePath, false))
                {
                    WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                    IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                    string relationshipId = sheets.First().Id.Value;
                    WorksheetPart worksheetPart = null;
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();
                        worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
                    }
                    else
                    {
                        worksheetPart = (WorksheetPart)workbookPart.GetPartById(relationshipId);
                    }
                    //WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                    Worksheet workSheet = worksheetPart.Worksheet;
                    SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                    IEnumerable<Row> rows = sheetData.Descendants<Row>();




                    foreach (Cell cell in rows.ElementAt(0))
                    {
                        dt.Columns.Add(GetCellValue(spreadSheetDocument, cell).Trim());
                    }
                    foreach (Row row in rows) //this will also include your header row...
                    {
                        DataRow tempRow = dt.NewRow();
                        int columnIndex = 0;
                        foreach (Cell cell in row.Descendants<Cell>())
                        {
                            // Gets the column index of the cell with data
                            int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
                            cellColumnIndex--; //zero based index
                            if (columnIndex < cellColumnIndex)
                            {
                                do
                                {
                                    tempRow[columnIndex] = ""; //Insert blank data here;
                                    columnIndex++;
                                }
                                while (columnIndex < cellColumnIndex);
                            }
                            tempRow[columnIndex] = GetCellValue(spreadSheetDocument, cell);

                            columnIndex++;
                        }
                        dt.Rows.Add(tempRow);
                    }
                }
                dt.Rows.RemoveAt(0); //...so i'm taking it out here.


            }
            catch (Exception ex)
            {
            }
            return dt;

        }


        /// <summary>
        /// Given a cell name, parses the specified cell to get the column name.
        /// </summary>
        /// <param name="cellReference">Address of the cell (ie. B2)</param>
        /// <returns>Column Name (ie. B)</returns>

        public static string GetColumnName(string cellReference)
        {
            // Create a regular expression to match the column name portion of the cell name.
            Regex regex = new Regex("[A-Za-z]+");
            Match match = regex.Match(cellReference);
            return match.Value;
        }

        /// <summary>
        /// Given just the column name (no row index), it will return the zero based column index.
        /// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ). 
        /// A length of three can be implemented when needed.
        /// </summary>
        /// <param name="columnName">Column Name (ie. A or AB)</param>
        /// <returns>Zero based index if the conversion was successful; otherwise null</returns>

        public static int? GetColumnIndexFromName(string columnName)
        {

            //return columnIndex;
            string name = columnName;
            int number = 0;
            int pow = 1;
            for (int i = name.Length - 1; i >= 0; i--)
            {
                number += (name[i] - 'A' + 1) * pow;
                pow *= 26;
            }
            return number;
        }
        public static string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
            if (cell.CellValue == null)
            {
                return "";
            }
            string value = cell.CellValue.InnerXml;
            if (cell.DataType != null && cell.DataType == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            //else if (cell.DataType != null && cell.DataType == CellValues.Date)
            //{

            //    return Convert.ToString(Convert.ToDateTime(stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText));
            //}
            else
            {
                return value;
            }
        }

Note: Add Windows Base Reference also.
Download DocumentFormat.OpenXML.dll file