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