/SpLocalizer.ContentSourceProvider/SpDocumentLibraryFile/ExcelFileLocalizationTask.cs

using System;

using System.Collections.Generic;

using System.Globalization;

using System.IO;

using System.Linq;

using System.Text;

using SpLocalizer.Common;

using Microsoft.SharePoint;

using System.Xml;

using Microsoft.Practices.Unity;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Validation;

using DocumentFormat.OpenXml.Spreadsheet;





namespace SpLocalizer.ContentSourceProviders.SpDocumentLibraryFile

{

    internal class ExcelFileLocalizationTask: AbstractFileLocalizationTask

    {



         /// <summary>

        /// Constructor without parameters should be public for unity injection

        /// </summary>

        public ExcelFileLocalizationTask () { }



        private const string SlideXmlElementName = "Part";



        protected override string GetDocumentXmlToTranslate(SPListItem item)

        {            

            // Open a PresentationDocument for read-only access based on a stream.

            using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(item.File.OpenBinaryStream(), false))

            {

                XmlDocument XmlDoc = new XmlDocument();

                XmlDoc.LoadXml(@"<CustomExcelDocument></CustomExcelDocument>");

                XmlNode Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "strings", "");

                Xnode.InnerXml = excelDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.InnerXml;

                XmlDoc.DocumentElement.AppendChild(Xnode);





                foreach (WorksheetPart part in excelDocument.WorkbookPart.WorksheetParts)

                {

                    if (part.DrawingsPart != null)

                    {

                        foreach (ChartPart partx in part.DrawingsPart.ChartParts)

                        {

                            Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "charts", "");

                            Xnode.InnerXml = partx.ChartSpace.InnerXml;

                            XmlDoc.DocumentElement.AppendChild(Xnode);

                        }

                    }

                   /* if (part.PivotTableParts != null)

                    {

                        foreach (PivotTablePart partx in part.PivotTableParts)

                        {

                            Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "pivottables", "");

                            Xnode.InnerXml = partx.PivotTableDefinition.InnerXml;

                            XmlDoc.DocumentElement.AppendChild(Xnode);

                        }

                    } */

                    if (part.TableDefinitionParts != null)

                    {

                        foreach (TableDefinitionPart partx in part.TableDefinitionParts)

                        {

                            Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "tables", "");

                            Xnode.InnerXml = partx.Table.InnerXml;

                            XmlDoc.DocumentElement.AppendChild(Xnode);

                        }

                    }

                }

                //





                Sheets sheet = excelDocument.WorkbookPart.Workbook.Sheets;



                foreach (Sheet sheett in sheet)

                {

                    OpenXmlPart mypart =  excelDocument.WorkbookPart.GetPartById(sheett.Id);

                    if (Convert.ToString(mypart.Uri).IndexOf("/xl/worksheets") != -1)

                    {

                        WorksheetPart worksheetPart = (WorksheetPart)excelDocument.WorkbookPart.GetPartById(sheett.Id);

                        Worksheet worksheet = worksheetPart.Worksheet;

                        //sheett.InnerXml

                        Xnode = XmlDoc.CreateNode(XmlNodeType.Element, "sheets", "");

                        Xnode.InnerXml = worksheet.InnerXml;

                        XmlDoc.DocumentElement.AppendChild(Xnode);

                    }

                }





                //





                if (XmlDoc.DocumentElement.ChildNodes.Count == 0)

                    return null;

                

                using (StringWriter sw = new StringWriter()){

                using(XmlTextWriter xtw = new XmlTextWriter(sw)){    

                    XmlDoc.WriteContentTo(xtw);

                    xtw.Flush();

                    sw.Flush();

                    return sw.ToString();

                }

                }

            }

            

        }



        internal override void ExportTranslation(SPListItem item)

        {



            if (!Constants.FILE_EXCEL.Equals(this.Format))

            {

                throw new ApplicationException(String.Format("Illegal format {0} for export with {1}. {2} Required",

                    this.Format, typeof(ExcelFileLocalizationTask).Name, Constants.FILE_EXCEL));

            }

                SPFile theFile = item.File;





                // Open a WordprocessingDocument for read-only access based on a stream.

                Stream documentStream = theFile.OpenBinaryStream();

                using (SpreadsheetDocument xlsxDocument = SpreadsheetDocument.Open(documentStream, true))

                {



                    XmlDocument XmlDoc = new XmlDocument();

                    XmlDoc.LoadXml(this.TranslatedText);

                    int i = 0;

                    //









 

                    XmlNode Xnode = XmlDoc.SelectNodes("//*[local-name()='strings']").Item(0);

                    xlsxDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.InnerXml = Xnode.InnerXml;





                    foreach (WorksheetPart part in xlsxDocument.WorkbookPart.WorksheetParts)

                    {

                        if (part.DrawingsPart != null)

                        {

                            i = 0;

                            foreach (ChartPart partx in part.DrawingsPart.ChartParts)

                            {

                                Xnode = XmlDoc.SelectNodes("//*[local-name()='charts']").Item(i);

                                partx.ChartSpace.InnerXml = Xnode.InnerXml;

                                i++;

                            }

                        }

                       /* if (part.PivotTableParts != null)

                        {

                            i = 0;

                            foreach (PivotTablePart partx in part.PivotTableParts)

                            {

                                Xnode = XmlDoc.SelectNodes("//*[local-name()='pivottables']").Item(i);

                                partx.PivotTableDefinition.InnerXml = Xnode.InnerXml;

                                i++;

                            }

                        }*/



                        if (part.TableDefinitionParts != null)

                        {

                            i = 0;

                            foreach (TableDefinitionPart partx in part.TableDefinitionParts)

                            {

                                Xnode = XmlDoc.SelectNodes("//*[local-name()='tables']").Item(i);

                                partx.Table.InnerXml = Xnode.InnerXml;

                                i++;

                            }

                        }

                    }

                    //









                    i = 0;



                     foreach (Sheet sheett in xlsxDocument.WorkbookPart.Workbook.Sheets)

                    {

                                             OpenXmlPart mypart =  xlsxDocument.WorkbookPart.GetPartById(sheett.Id);

                                             if (Convert.ToString(mypart.Uri).IndexOf("/xl/worksheets") != -1)

                                             {

                                                 WorksheetPart worksheetPart = (WorksheetPart)xlsxDocument.WorkbookPart.GetPartById(sheett.Id);

                                                 Worksheet worksheet = worksheetPart.Worksheet;

                                                 Xnode = XmlDoc.SelectNodes("//*[local-name()='sheets']").Item(i);

                                                 worksheet.InnerXml = Xnode.InnerXml;

                                                 i++;

                                             }

                    }

                    



                    //



















                    //



                    xlsxDocument.Close();



                    SafeFile(theFile, documentStream);



                }

            



        }

    }

}