Esempio n. 1
0
 private void ParseWorkbookPart(ExecuteArgs param, Dictionary <string, Dictionary <string, DefinedName> > cacheNames, WorkbookPart workbookPart, WorkbookPart newWorkbookPart, List <Excel.Sheet> sheetList)
 {
     using (var stream = workbookPart.GetStream())
     {
         ParseWorkbookPart(param, cacheNames, stream, newWorkbookPart, sheetList);
     }
 }
Esempio n. 2
0
 private void ParseWorksheetPart(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, StringKeyList sharedStrings, WorksheetPart worksheetPart, WorksheetPart newWorksheetPart)
 {
     using (var stream = worksheetPart.GetStream())
     {
         ParseWorksheetPart(param, cacheNames, sharedStrings, stream, newWorksheetPart);
     }
 }
Esempio n. 3
0
 private void ParseTableDefinition(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, TableDefinitionPart tableDefinitionPart, TableDefinitionPart newTableDefinitionPart)
 {
     using (var stream = tableDefinitionPart.GetStream())
     {
         ParseTableDefinition(param, cacheNames, stream, newTableDefinitionPart);
     }
 }
Esempio n. 4
0
        public override string Fill(Stream stream, string fileName, ExecuteArgs param)
        {
            TextDocument doc       = new TextDocument(stream);
            OdtProcessor processor = new OdtProcessor(doc);

            var procedures = new List <string>();
            var fields     = processor.GetFields();
            var elements   = new Dictionary <string, object>(StringComparer.Ordinal);

            foreach (string documentField in fields)
            {
                // adding group
                if (!procedures.Contains(documentField))
                {
                    object rez = ParseString(param, documentField);
                    if (rez == null)
                    {
                        continue;
                    }
                    procedures.Add(documentField);
                    elements.Add(documentField, rez);
                }
                else
                {
                    continue;
                }
            }
            processor.PerformReplace(elements);
            var tempFile = GetTempFileName(fileName);

            doc.Save(tempFile);
            return(tempFile);
        }
Esempio n. 5
0
        public Dictionary <string, object> ExecuteDBProcedure(IDbCommand command, ExecuteArgs param)
        {
            var transaction = param.Transaction ?? new DBTransaction(Schema.Connection);

            try
            {
                transaction.AddCommand(command);
                //UpdateCommand(command, parameters);
                transaction.ExecuteQuery(command);
                foreach (IDataParameter par in command.Parameters)
                {
                    if (par.Direction == ParameterDirection.InputOutput || par.Direction == ParameterDirection.Output)
                    {
                        param.Parameters[par.ParameterName] = par.Value;
                    }
                }
                if (param.Transaction == null)
                {
                    transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                Helper.OnException(ex);
                transaction.Rollback();
            }
            finally
            {
                if (param.Transaction == null)
                {
                    transaction.Dispose();
                }
            }
            return(param.Parameters);
        }
Esempio n. 6
0
 private void ParseTableDefinition(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, TableDefinitionPart tableDefinitionPart)
 {
     using (var temp = new MemoryStream())
     {
         using (var stream = tableDefinitionPart.GetStream())
             stream.CopyTo(temp);
         temp.Position = 0;
         ParseTableDefinition(param, cacheNames, temp, tableDefinitionPart);
     }
 }
Esempio n. 7
0
        public TaskExecutor GetExecutor(DBItem document, DBTransaction transaction, bool autoCommit = false)
        {
            var param = new ExecuteArgs(document)
            {
                Transaction = transaction,
                AutoCommit  = autoCommit
            };

            return(GetExecutor(CreateObject(param), param));
        }
Esempio n. 8
0
 private void ParseWorkbookPart(ExecuteArgs param, Dictionary <string, Dictionary <string, DefinedName> > cacheNames, WorkbookPart workbookPart, List <Excel.Sheet> sheetList)
 {
     using (var buffer = new MemoryStream())
     {
         using (var stream = workbookPart.GetStream())
             stream.CopyTo(buffer);
         buffer.Position = 0;
         ParseWorkbookPart(param, cacheNames, buffer, workbookPart, sheetList);
     }
 }
Esempio n. 9
0
        private void ParseTableDefinition(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, Stream tableDefinitionPart, TableDefinitionPart newTableDefinitionPart)
        {
            using (var reader = OpenXmlReader.Create(tableDefinitionPart))
                using (var writer = XmlWriter.Create(newTableDefinitionPart.GetStream(),
                                                     new XmlWriterSettings {
                    Encoding = Encoding.UTF8, CloseOutput = true
                }))
                {
                    writer.WriteStartDocument(true);
                    while (reader.Read())
                    {
                        if (reader.ElementType == typeof(Excel.Table))
                        {
                            var table = (Excel.Table)reader.LoadCurrentElement();

                            var code = param.ParseCode(table.Name);
                            if (code != null)
                            {
                                var reference = CellRange.Parse(table.Reference.Value);
                                var defName   = new DefinedName
                                {
                                    Name       = table.Name,
                                    Range      = reference,
                                    Code       = code,
                                    CacheValue = param.GetValue(code)
                                };
                                if (defName.CacheValue is QResult result && result.Values.Count > 0)
                                {
                                    var index = reference.Start.Row + result.Values.Count;
                                    if (index > reference.End.Row)
                                    {
                                        defName.NewRange = new CellRange(reference.Start, new CellReference(reference.End.Col, index));
                                        table.Reference  = defName.NewRange.ToString();
                                        //table.TotalsRowCount = (uint)newrange.Rows;
                                    }
                                    defName.Table = table;
                                    cacheNames[defName.Range.Start.ToString()] = defName;
                                }
                            }
                            WriteElement(writer, table);
                        }
                        else if (reader.IsStartElement)
                        {
                            WriteStartElement(writer, reader);
                        }
                        else if (reader.IsEndElement)
                        {
                            writer.WriteEndElement();
                        }
                    }
                    writer.WriteEndDocument();
                    writer.Flush();
                }
        }
Esempio n. 10
0
        private void ParseWorksheetPart(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, StringKeyList sharedStrings, WorksheetPart worksheetPart)
        {
            var tempName = Path.GetTempFileName();

            using (var temp = new FileStream(tempName, FileMode.Create, FileAccess.ReadWrite))
            {
                using (var stream = worksheetPart.GetStream())
                    stream.CopyTo(temp);
                temp.Position = 0;
                ParseWorksheetPart(param, cacheNames, sharedStrings, temp, worksheetPart);
            }
            File.Delete(tempName);
        }
Esempio n. 11
0
        public string ParseDirectly(Stream stream, string fileName, ExecuteArgs param)
        {
            var cacheNames = GetCacheNames(param);

            var sharedStrings = (StringKeyList)null;

            using (var document = SpreadsheetDocument.Open(stream, true))
            {
                var workbookPart = document.WorkbookPart;
                var sheetList    = new List <Excel.Sheet>();
                sharedStrings = ReadStringTable(workbookPart.SharedStringTablePart);

                //if (workbookPart.CalculationChainPart != null)
                //{
                //    workbookPart.DeletePart(workbookPart.CalculationChainPart);
                //}

                ParseWorkbookPart(param, cacheNames, workbookPart, sheetList);

                foreach (var part in workbookPart.Parts)
                {
                    if (part.OpenXmlPart is WorksheetPart worksheetPart)
                    {
                        var sheet = sheetList.FirstOrDefault(p => p.Id == part.RelationshipId);
                        if (cacheNames.TryGetValue(sheet.Name.Value, out var sheetNames))
                        {
                            //if (sheet.State != null
                            //    && (sheet.State.Value == Excel.SheetStateValues.Hidden
                            //    || sheet.State.Value == Excel.SheetStateValues.VeryHidden))
                            //    continue;

                            foreach (var sheetPart in worksheetPart.Parts)
                            {
                                if (sheetPart.OpenXmlPart is TableDefinitionPart tableDefinitionPart)
                                {
                                    ParseTableDefinition(param, sheetNames, tableDefinitionPart);
                                }
                            }
                            ParseWorksheetPart(param, sheetNames, sharedStrings, worksheetPart);
                        }
                    }
                }

                WriteStringTable(workbookPart.SharedStringTablePart, sharedStrings);
                document.Save();
                //var validator = new DocumentFormat.OpenXml.Validation.OpenXmlValidator();
                //var errors = validator.Validate(document);
            }
            return(((FileStream)stream).Name);
        }
Esempio n. 12
0
        public static string Execute(Stream stream, string fileName, ExecuteArgs param)
        {
            var ext = Path.GetExtension(fileName);

            if (cache.TryGetValue(ext, out var parser))
            {
                return(parser.Parse(stream, fileName, param));
            }
            else
            {
                return(stream is FileStream fileStream ? fileStream.Name : null);
            }
            //throw new NotSupportedException(ext);
        }
Esempio n. 13
0
 public override string Parse(Stream stream, string fileName, ExecuteArgs param)
 {
     stream.Position = 0;
     using (var wd = WordprocessingDocument.Open(stream, true))
     {
         ParseDocxPart(wd.MainDocumentPart.Document, param);
         foreach (var header in wd.MainDocumentPart.HeaderParts)
         {
             ParseDocxPart(header.Header, param);
         }
         stream.Flush();
     }
     return(stream is FileStream fileStream ? fileStream.Name : null);
 }
Esempio n. 14
0
        public object ExecuteObject(object obj, ExecuteArgs param)
        {
            Helper.Logs.Add(new StateInfo("Procedure", this.Name, param.ToString(), StatusType.Information));

            //if (FlowEnvir.Config.LogProcedure)
            //DocumentLog.LogUser(FlowEnvir.Personal.User, DocumentLogType.Execute, this.ToString(), this, param.Document);

            if (ProcedureType == ProcedureTypes.Assembly || ProcedureType == ProcedureTypes.Source)
            {
                if (obj is IDocument documented)
                {
                    documented.Document = param.Document;
                }
                if (obj is IExecutable executed)
                {
                    obj = executed.Execute(param)?.GetAwaiter().GetResult();
                }
            }
            else if (ProcedureType == ProcedureTypes.StoredFunction)
            {
                if (param.Transaction != null)
                {
                    obj = ExecuteDBFunction((IDbCommand)obj, param.Transaction);
                }
                else
                {
                    obj = ExecuteDBFunction((IDbCommand)obj);
                }
            }
            else if (ProcedureType == ProcedureTypes.StoredProcedure)
            {
                obj = ExecuteDBProcedure((IDbCommand)obj, param);
            }
            else if (ProcedureType == ProcedureTypes.Query)
            {
                var buf = (QResult)null;
                if (param.Transaction != null)
                {
                    buf = ExecuteQueryResult((IDbCommand)obj, param.Transaction);
                }
                else
                {
                    buf = ExecuteQueryResult((IDbCommand)obj);
                }
                obj = (buf != null && buf.Columns.Count == 1 && buf.Values.Count == 1) ? buf.Values[0][0] : buf;
            }

            return(obj);
        }
Esempio n. 15
0
        public TaskExecutor GetExecutor(object obj, ExecuteArgs param)
        {
            var task = new TaskExecutor
            {
                Name   = $"{this.Name} on {param.Document} #{param.Document?.PrimaryId}",
                Tag    = param.Document,
                Object = this,
                Action = () =>
                {
                    object result = null;
                    try
                    {
                        if (param.AutoCommit && param.Transaction == null)
                        {
                            param.Transaction = new DBTransaction(Schema.Connection);
                        }
                        result = this.ExecuteObject(obj, param);
                        if (param.AutoCommit)
                        {
                            param.Transaction?.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        param.Transaction?.Rollback();
                        result = ex;
                    }
                    finally
                    {
                        if (param.AutoCommit)
                        {
                            param.Transaction?.Dispose();
                        }
                    }

                    return(result);
                }
            };

            return(task);
        }
Esempio n. 16
0
 public object ReplaceExcelString(ExecuteArgs param, string value)
 {
     if (value.IndexOf('#') >= 0)
     {
         var mc = excelRegex.Matches(value);
         foreach (Match m in mc)
         {
             object rz = ParseString(param, m.Value.Trim('#', '<', '>'));
             if (rz is QResult)
             {
                 return(rz);
             }
             else if (rz != null)
             {
                 value = value.Replace(m.Value, rz.ToString());
             }
         }
         return(value);
     }
     return(null);
 }
Esempio n. 17
0
        public void ParseDocxPart(OpenXmlPartRootElement doc, ExecuteArgs param)
        {
            var list = new List <Word.SdtElement>();

            Find <Word.SdtElement>(doc, list);
            foreach (var item in list)
            {
                OpenXmlElement stdContent = FindChildByName(item, "sdtContent");
                var            element    = stdContent.FirstChild;
                var            prop       = item.Descendants <Word.SdtProperties>().FirstOrDefault();
                var            temp       = prop.Descendants <Word.TemporarySdt>().FirstOrDefault();
                var            tag        = prop.Descendants <Word.Tag>().FirstOrDefault();
                if (tag == null)
                {
                    tag = stdContent.Descendants <Word.Tag>().FirstOrDefault();
                }

                if (tag != null)
                {
                    object val = ParseString(param, tag.Val.ToString());
                    if (val != null)
                    {
                        if (temp != null)
                        {
                            element.Remove();
                            item.Parent.ReplaceChild(element, item);
                        }
                        if (val is QResult)
                        {
                            FillTable(item, (QResult)val);
                        }
                        else
                        {
                            ReplaceString(element, val.ToString());
                        }
                    }
                }
            }
            doc.Save();
        }
Esempio n. 18
0
        public object CreateObject(ExecuteArgs arg = null)
        {
            object temp = null;

            if (ProcedureType == ProcedureTypes.Assembly || ProcedureType == ProcedureTypes.Source)
            {
                temp = EmitInvoker.CreateObject(GetObjectType(), true);
            }
            else if (ProcedureType == ProcedureTypes.Table)
            {
                temp = DBService.Schems.ParseTable(Source);
            }
            else if (ProcedureType == ProcedureTypes.Constant)
            {
                temp = Source;
            }
            else
            {
                temp = BuildCommand(arg.Parameters);
            }
            return(temp);
        }
Esempio n. 19
0
        private static Dictionary <string, Dictionary <string, DefinedName> > GetCacheNames(ExecuteArgs param)
        {
            var cacheNames = new Dictionary <string, Dictionary <string, DefinedName> >(StringComparer.OrdinalIgnoreCase);

            foreach (var item in param.Codes.Where(p => p.Attribute.Category == "General" || p.Attribute.Category == param.ProcedureCategory))
            {
                var split = item.Attribute.Code.Split('!');
                if (split.Length == 2)
                {
                    var sheet = split[0].Trim('\'');
                    if (!cacheNames.TryGetValue(sheet, out var names))
                    {
                        cacheNames[sheet] = names = new Dictionary <string, DefinedName>();
                    }
                    var defName = new DefinedName
                    {
                        Name      = item.Attribute.Code,
                        Sheet     = sheet,
                        Reference = split[1],
                        Code      = item
                    };
                    names[defName.Range.Start.ToString()] = defName;
                }
            }
            return(cacheNames);
        }
Esempio n. 20
0
 public object Execute(ExecuteArgs param)
 {
     return(ExecuteObject(CreateObject(param), param));
 }
Esempio n. 21
0
        public override string Parse(Stream stream, string fileName, ExecuteArgs param)
        {
            // bool flag = false;
            stream.Position = 0;
            using (var xl = SpreadsheetDocument.Open(stream, true))
            {
                //IEnumerable<DocumentFormat.OpenXml.Packaging.SharedStringTablePart> sp = xl.WorkbookPart.GetPartsOfType<DocumentFormat.OpenXml.Packaging.SharedStringTablePart>();
                foreach (WorksheetPart part in xl.WorkbookPart.WorksheetParts)
                {
                    var             sharedStrings = ReadStringTable(xl.WorkbookPart.SharedStringTablePart);
                    Excel.Worksheet worksheet     = part.Worksheet;
                    Excel.SheetData sd            = worksheet.GetFirstChild <Excel.SheetData>();
                    var             results       = FindParsedCells(sharedStrings, sd);
                    foreach (Excel.Cell cell in results)
                    {
                        string          val = ReadCell(cell, sharedStrings);
                        Regex           re  = new Regex("#.[^#]*#", RegexOptions.IgnoreCase);
                        MatchCollection mc  = re.Matches(val);
                        foreach (Match m in mc)
                        {
                            object res = ParseString(param, m.Value.Trim("#<>".ToCharArray()));
                            if (res != null)
                            {
                                //flag = true;
                                Excel.Row newRow = null;
                                if (res is QResult query)
                                {
                                    var sref  = CellReference.Parse(cell.CellReference.Value);
                                    int count = 0;
                                    foreach (object[] dataRow in query.Values)
                                    {
                                        count++;
                                        int col = sref.Col;
                                        newRow = GetRow(sd, sref.Row, newRow == null, cell.Parent as Excel.Row);
                                        foreach (object kvp in dataRow)
                                        {
                                            Excel.Cell ncell = GetCell(newRow, kvp, col, sref.Row, 0, sharedStrings);
                                            if (ncell.Parent == null)
                                            {
                                                newRow.Append(ncell);
                                            }
                                            col++;
                                        }
                                        sref.Row++;
                                    }
                                    if (newRow != null)
                                    {
                                        uint rcount = newRow.RowIndex.Value;
                                        foreach (var item in newRow.ElementsAfter())
                                        {
                                            if (item is Excel.Row)
                                            {
                                                rcount++;
                                                ((Excel.Row)item).RowIndex = rcount;
                                                foreach (var itemCell in item.ChildElements)
                                                {
                                                    if (itemCell is Excel.Cell)
                                                    {
                                                        var reference = CellReference.Parse(((Excel.Cell)itemCell).CellReference);
                                                        reference.Row = (int)rcount;
                                                        ((Excel.Cell)itemCell).CellReference = reference.ToString();
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    val = val.Replace(m.Value, res.ToString());
                                    WriteCell(cell, val, sharedStrings);
                                }
                            }
                        }
                    }
                    WriteStringTable(xl.WorkbookPart.SharedStringTablePart, sharedStrings);
                }
            }
            stream.Flush();

            return(stream is FileStream fileStream ? fileStream.Name : null);
        }
Esempio n. 22
0
 public abstract string Fill(Stream stream, string fileName, ExecuteArgs args);
Esempio n. 23
0
 public abstract string Parse(Stream stream, string fileName, ExecuteArgs param);
Esempio n. 24
0
 public static string Execute(DBProcedure proc, ExecuteArgs param)
 {
     return(Execute(new MemoryStream(proc.Data), proc.DataName, param));
 }
Esempio n. 25
0
        public object ParseString(ExecuteArgs parameters, string code)
        {
            var    temp = code.Split(new char[] { ':' });
            object val  = null;

            string procedureCode = code;
            string param         = null;
            string localize      = null;

            if (temp.Length > 0)
            {
                string type = temp[0].Trim();
                if (type.Equals("c", StringComparison.OrdinalIgnoreCase))
                {
                    string[] vsplit = temp[1].Split(new char[] { ' ' });
                    string   column = vsplit[0].Trim();
                    val = parameters.Document[column];
                    if (temp.Length > 2)
                    {
                        param = temp[2].Trim();
                    }
                    if (temp.Length > 3)
                    {
                        localize = temp[3];
                    }
                }
                else if (type.Equals("p", StringComparison.OrdinalIgnoreCase))
                {
                    procedureCode = temp[1].Trim();
                }
                else if (parameters.Parameters.TryGetValue(type, out val))
                {
                    if (temp.Length > 1)
                    {
                        param = temp[1].Trim();
                    }
                    if (temp.Length > 2)
                    {
                        localize = temp[2];
                    }
                }
                else if (code == "list")
                {
                    val = parameters.Result;
                }
            }
            if (param != null && param.Length > 0)
            {
                CultureInfo culture = CultureInfo.InvariantCulture;
                if (localize != null)
                {
                    culture = CultureInfo.GetCultureInfo(localize);
                }
                val = Helper.TextDisplayFormat(val, param, culture);
            }

            if (val == null)
            {
                var codeAttribute = parameters.ParseCode(procedureCode);
                if (codeAttribute != null)
                {
                    val = parameters.GetValue(codeAttribute);
                }
                else
                {
                    var procedure = DBService.Schems.ParseProcedure(procedureCode, parameters.ProcedureCategory);
                    if (procedure != null)
                    {
                        try { val = procedure.Execute(parameters); }
                        catch (Exception ex) { val = ex.Message; }
                    }
                }
            }

            return(val);
        }
Esempio n. 26
0
 public override string Parse(Stream stream, string fileName, ExecuteArgs param)
 {
     return(ParseDirectly(stream, fileName, param));
 }
Esempio n. 27
0
        private void ParseWorksheetPart(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, StringKeyList sharedStrings, Stream worksheetPart, WorksheetPart newWorksheetPart)
        {
            var inserts = new List <CellRange>();

            using (var reader = OpenXmlReader.Create(worksheetPart))
                using (var writer = XmlWriter.Create(newWorksheetPart.GetStream(FileMode.Create)
                                                     , new XmlWriterSettings {
                    Encoding = Encoding.UTF8, CloseOutput = true
                }))
                {
                    int ind, dif = 0;
                    writer.WriteStartDocument(true);

                    while (reader.Read())
                    {
                        //remove protection
                        //if (reader.ElementType == typeof(Excel.SheetProtection))
                        //{
                        //    reader.LoadCurrentElement();
                        //    continue;
                        //}
                        if (reader.ElementType == typeof(Excel.Row))
                        {
                            var row      = (Excel.Row)reader.LoadCurrentElement();
                            var rowIndex = (int)row.RowIndex.Value;
                            ind = rowIndex + dif;
                            UpdateRowIndex(row, ind);
                            QResult query = null;
                            foreach (Excel.Cell ocell in row.Descendants <Excel.Cell>())
                            {
                                object rz = null;
                                if (cacheNames.TryGetValue(ocell.CellReference.Value, out var defName))
                                {
                                    rz = defName.CacheValue ?? param.GetValue(defName.Code);
                                }
                                else
                                {
                                    string value = ReadCell(ocell, sharedStrings);
                                    rz = ReplaceExcelString(param, value);
                                }

                                if (rz != null)
                                {
                                    query = rz as QResult;
                                    if (query != null)
                                    {
                                        var       sref     = CellReference.Parse(ocell.CellReference);
                                        var       insert   = new CellRange(sref, sref);
                                        Excel.Row tableRow = null;
                                        foreach (object[] dataRow in query.Values)
                                        {
                                            if (tableRow == null)
                                            {
                                                tableRow = row;
                                            }
                                            else if (defName != null && defName.Range.End.Row > sref.Row)
                                            {
                                                if (reader.Read() && reader.ElementType == typeof(Excel.Row))
                                                {
                                                    tableRow = (Excel.Row)reader.LoadCurrentElement();
                                                    UpdateRowIndex(tableRow, (int)tableRow.RowIndex.Value + dif);
                                                    insert.Start.Row++;
                                                    insert.End.Row++;
                                                }
                                                else
                                                {
                                                }
                                            }
                                            else
                                            {
                                                tableRow = CloneRow(tableRow, sref.Row);// GetRow(sd, srow, excelRow == null, cell.Parent as Excel.Row);
                                                insert.End.Row++;
                                            }

                                            int col = sref.Col;
                                            foreach (object itemValue in dataRow)
                                            {
                                                GetCell(tableRow, itemValue, col, sref.Row, 0, sharedStrings);
                                                col++;
                                            }
                                            sref.Row++;
                                            WriteElement(writer, tableRow);
                                        }

                                        if (insert.Rows > 0)
                                        {
                                            inserts.Add(insert);
                                            dif += insert.Rows;
                                        }
                                        break;
                                    }
                                    else
                                    {
                                        WriteCell(ocell, rz, sharedStrings);
                                    }
                                }
                            }
                            if (query == null)
                            {
                                WriteElement(writer, row);
                            }
                        }
                        else if (reader.ElementType == typeof(Excel.MergeCell))
                        {
                            var merge = reader.LoadCurrentElement() as Excel.MergeCell;
                            var range = CellRange.Parse(merge.Reference);

                            foreach (var insert in inserts)
                            {
                                if (insert.Start.Row < range.Start.Row)
                                {
                                    range.Start.Row += insert.Rows;
                                    range.End.Row   += insert.Rows;
                                }
                            }
                            merge.Reference = range.ToString();
                            WriteElement(writer, merge);
                        }
                        else if (reader.ElementType == typeof(Excel.DataValidation))
                        {
                            var validation = (Excel.DataValidation)reader.LoadCurrentElement();
                            if (validation.SequenceOfReferences.HasValue)
                            {
                                var newlist = new List <StringValue>();
                                foreach (var item in validation.SequenceOfReferences.Items)
                                {
                                    var range = CellRange.Parse(item);
                                    foreach (var insert in inserts)
                                    {
                                        if (insert.Start.Row <= range.End.Row && insert.End.Row > range.End.Row)
                                        {
                                            range.End.Row = insert.End.Row;
                                            newlist.Add(range.ToString());
                                            continue;
                                        }
                                    }
                                    newlist.Add(item);
                                }
                                validation.SequenceOfReferences = new ListValue <StringValue>(newlist);
                            }
                            WriteElement(writer, validation);
                        }
                        else if (reader.ElementType == typeof(Excel.OddFooter) ||
                                 reader.ElementType == typeof(Excel.EvenFooter) ||
                                 reader.ElementType == typeof(Excel.FirstFooter))
                        {
                            var footer = reader.LoadCurrentElement() as OpenXmlLeafTextElement;
                            var str    = ReplaceExcelString(param, footer.Text) as string;
                            if (str != null)
                            {
                                footer.Text = str;
                            }
                            WriteElement(writer, footer);
                        }
                        else if (reader.IsStartElement)
                        {
                            WriteStartElement(writer, reader);
                        }
                        else if (reader.IsEndElement)
                        {
                            writer.WriteEndElement();
                        }
                    }
                    writer.WriteEndDocument();
                    writer.Flush();
                    writer.Close();
                    //newWorksheetPart.FeedData(temp);
                }
        }
Esempio n. 28
0
        public string ParseReplace(Stream stream, string fileName, ExecuteArgs param)
        {
            string newFileName  = GetTempFileName(fileName);
            var    cacheNames   = GetCacheNames(param);
            var    stringTables = (StringKeyList)null;

            using (var document = SpreadsheetDocument.Open(stream, false))
                using (var newDocument = SpreadsheetDocument.Create(newFileName, document.DocumentType))
                {
                    foreach (var docPart in document.Parts)
                    {
                        if (docPart.OpenXmlPart is ExtendedFilePropertiesPart extendedFilePropertiesPart)
                        {
                            newDocument.AddPart(extendedFilePropertiesPart, docPart.RelationshipId);
                        }
                        else if (docPart.OpenXmlPart is CoreFilePropertiesPart coreFilePropertiesPart)
                        {
                            newDocument.AddPart(coreFilePropertiesPart, docPart.RelationshipId);
                        }
                        else if (docPart.OpenXmlPart is CustomFilePropertiesPart customFilePropertiesPart)
                        {
                            newDocument.AddPart(customFilePropertiesPart, docPart.RelationshipId);
                        }
                        else if (docPart.OpenXmlPart is WorkbookPart workbookPart)
                        {
                            var newWorkbookPart = newDocument.AddPart(workbookPart, docPart.RelationshipId);
                            //var newWorkbookPart = newDocument.AddWorkbookPart();
                            //newDocument.ChangeIdOfPart(newWorkbookPart, docPart.RelationshipId);

                            var sheetList = new List <Excel.Sheet>();
                            stringTables = ReadStringTable(workbookPart.SharedStringTablePart);
                            ParseWorkbookPart(param, cacheNames, workbookPart, newWorkbookPart, sheetList);

                            foreach (var part in workbookPart.Parts)
                            {
                                if (part.OpenXmlPart is SharedStringTablePart sharedStringTablePart)
                                {
                                    //newWorkbookPart.AddPart(sharedStringTablePart, part.RelationshipId);
                                }
                                else if (part.OpenXmlPart is WorkbookStylesPart workbookStylesPart)
                                {
                                    //newWorkbookPart.AddPart(workbookStylesPart, part.RelationshipId);
                                }
                                else if (part.OpenXmlPart is ThemePart themePart)
                                {
                                    //newWorkbookPart.AddPart(themePart, part.RelationshipId);
                                }
                                else if (part.OpenXmlPart is ExternalWorkbookPart externalWorkbookPart)
                                {
                                    //newWorkbookPart.AddPart(externalWorkbookPart, part.RelationshipId);
                                }
                                else if (part.OpenXmlPart is CalculationChainPart calculationChainPart)
                                {
                                    //newWorkbookPart.AddPart(calculationChainPart);//, part.RelationshipId
                                }
                                else if (part.OpenXmlPart is WorksheetPart worksheetPart)
                                {
                                    var sheet = sheetList.FirstOrDefault(p => p.Id == part.RelationshipId);
                                    //var newWorksheetPart = newWorkbookPart.AddNewPart<WorksheetPart>(sheet.Id);

                                    if (!cacheNames.TryGetValue(sheet.Name.Value, out var sheetNames))
                                    {
                                        cacheNames[sheet.Name.Value] = sheetNames = new Dictionary <string, DefinedName>();
                                    }
                                    var newWorksheetPart = (WorksheetPart)newWorkbookPart.GetPartById(sheet.Id);
                                    foreach (var sheetPart in worksheetPart.Parts)
                                    {
                                        if (sheetPart.OpenXmlPart is TableDefinitionPart tableDefinitionPart)
                                        {
                                            //var newTableDefinitionPart = newWorksheetPart.AddNewPart<TableDefinitionPart>(sheetPart.RelationshipId);
                                            var newTableDefinitionPart = (TableDefinitionPart)newWorksheetPart.GetPartById(sheetPart.RelationshipId);
                                            ParseTableDefinition(param, sheetNames, tableDefinitionPart, newTableDefinitionPart);
                                        }
                                        else if (sheetPart.OpenXmlPart is DrawingsPart drawingsPart)
                                        {
                                            //newWorksheetPart.AddPart(drawingsPart, sheetPart.RelationshipId);
                                        }
                                        else if (sheetPart.OpenXmlPart is SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart)
                                        {
                                            //newWorksheetPart.AddPart(spreadsheetPrinterSettingsPart, sheetPart.RelationshipId);
                                        }
                                        else if (sheetPart.OpenXmlPart is ControlPropertiesPart controlPropertiesPart)
                                        {
                                            //newWorksheetPart.AddPart(controlPropertiesPart, sheetPart.RelationshipId);
                                        }
                                        else if (sheetPart.OpenXmlPart is PivotTablePart pivotTablePart)
                                        {
                                            //newWorksheetPart.AddPart(pivotTablePart, sheetPart.RelationshipId);
                                        }
                                        else if (sheetPart.OpenXmlPart is QueryTablePart queryTablePart)
                                        {
                                            //newWorksheetPart.AddPart(queryTablePart, sheetPart.RelationshipId);
                                        }
                                        else if (sheetPart.OpenXmlPart is TimeLinePart timeLinePart)
                                        {
                                            //newWorksheetPart.AddPart(timeLinePart, sheetPart.RelationshipId);
                                        }
                                        else if (sheetPart.OpenXmlPart is WorksheetCommentsPart worksheetCommentsPart)
                                        {
                                            //newWorksheetPart.AddPart(worksheetCommentsPart, sheetPart.RelationshipId);
                                        }
                                        else if (sheetPart.OpenXmlPart is VmlDrawingPart vmlDrawingPart)
                                        {
                                            //newWorksheetPart.AddPart(vmlDrawingPart, sheetPart.RelationshipId);
                                        }
                                        else
                                        {
                                        }
                                    }
                                    ParseWorksheetPart(param, sheetNames, stringTables, worksheetPart, newWorksheetPart);
                                }
                                else
                                {
                                }
                            }
                        }
                        else
                        {
                        }
                    }
                    newDocument.Save();
                }
            return(newFileName);
        }
Esempio n. 29
0
 private void ParseWorkbookPart(ExecuteArgs param, Dictionary <string, Dictionary <string, DefinedName> > cacheNames, Stream workbookPart, WorkbookPart newWorkbookPart, List <Excel.Sheet> sheetList)
 {
     using (var reader = OpenXmlReader.Create(workbookPart))
         using (var writer = XmlWriter.Create(newWorkbookPart.GetStream(),
                                              new XmlWriterSettings {
             Encoding = Encoding.UTF8, CloseOutput = true
         }))
         {
             writer.WriteStartDocument(true);
             while (reader.Read())
             {
                 //if (reader.ElementType == typeof(AlternateContent))
                 //{
                 //    var alternate = (AlternateContent)reader.LoadCurrentElement();
                 //    continue;
                 //}
                 if (reader.ElementType == typeof(Excel.DefinedName))
                 {
                     var name = (Excel.DefinedName)reader.LoadCurrentElement();
                     if (!string.IsNullOrEmpty(name.InnerText))
                     {
                         var split = name.InnerText.Split('!');
                         if (split.Length == 2)
                         {
                             var sheet = split[0].Trim('\'');
                             var code  = param.ParseCode(name.Name);
                             if (code != null)
                             {
                                 if (!cacheNames.TryGetValue(sheet, out var names))
                                 {
                                     cacheNames[sheet] = names = new Dictionary <string, DefinedName>();
                                 }
                                 var defName = new DefinedName
                                 {
                                     Name      = name.Name,
                                     Sheet     = sheet,
                                     Reference = split[1],
                                     Code      = code
                                 };
                                 names.Add(defName.Range.Start.ToString(), defName);
                             }
                         }
                     }
                     WriteElement(writer, name);
                 }
                 else if (reader.ElementType == typeof(Excel.Sheet))
                 {
                     var sheet = (Excel.Sheet)reader.LoadCurrentElement();
                     sheetList.Add(sheet);
                     WriteElement(writer, sheet);
                 }
                 else if (reader.IsStartElement)
                 {
                     WriteStartElement(writer, reader);
                 }
                 else if (reader.IsEndElement)
                 {
                     writer.WriteEndElement();
                 }
             }
             writer.WriteEndDocument();
             writer.Flush();
         }
 }