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(); } }
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); } }
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); }