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);
}
}
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);
}
}
private void ParseTableDefinition(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, TableDefinitionPart tableDefinitionPart, TableDefinitionPart newTableDefinitionPart)
{
using (var stream = tableDefinitionPart.GetStream())
{
ParseTableDefinition(param, cacheNames, stream, newTableDefinitionPart);
}
}
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);
}
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);
}
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);
}
}
public TaskExecutor GetExecutor(DBItem document, DBTransaction transaction, bool autoCommit = false)
{
var param = new ExecuteArgs(document)
{
Transaction = transaction,
AutoCommit = autoCommit
};
return(GetExecutor(CreateObject(param), param));
}
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);
}
}
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, 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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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();
}
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);
}
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);
}
public object Execute(ExecuteArgs param)
{
return(ExecuteObject(CreateObject(param), param));
}
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);
}
public abstract string Fill(Stream stream, string fileName, ExecuteArgs args);
public abstract string Parse(Stream stream, string fileName, ExecuteArgs param);
public static string Execute(DBProcedure proc, ExecuteArgs param)
{
return(Execute(new MemoryStream(proc.Data), proc.DataName, param));
}
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);
}
public override string Parse(Stream stream, string fileName, ExecuteArgs param)
{
return(ParseDirectly(stream, fileName, param));
}
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 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);
}
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();
}
}
