public static string ReadCell(Excel.Cell cell, StringKeyList buffer = null)
{
string value = cell.CellValue?.InnerText ?? string.Empty;
if (cell.DataType != null)
{
if (cell.DataType.Value == Excel.CellValues.SharedString)
{
// shared strings table.
if (int.TryParse(value, out int val))
{
//if (strings.SharedStringTable.ChildElements.Count > val)
if (buffer != null)
{
value = buffer[val]?.Key;
}
}
}
else if (cell.DataType.Value == Excel.CellValues.Boolean)
{
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
}
}
return(value);
}
public Excel.Cell GetCell(object value, int c, int r, uint styleIndex, StringKeyList sharedStrings)
{
Excel.Cell cell = new Excel.Cell()
{
CellReference = Helper.IntToChar(c) + (r).ToString(),
StyleIndex = styleIndex,
};
WriteCell(cell, value, sharedStrings);
return(cell);
}
public static StringKeyList ReadStringTable(SharedStringTablePart sharedStringTablePart)
{
var dict = new StringKeyList();
using (var reader = OpenXmlReader.Create(sharedStringTablePart))
{
while (reader.Read())
{
if (reader.ElementType == typeof(Excel.SharedStringItem))
{
dict.Add(new StringKey((Excel.SharedStringItem)reader.LoadCurrentElement()));
}
}
}
return(dict);
}
public static void WriteCell(Excel.Cell cell, object value, StringKeyList sharedStrings)
{
if (value != null)
{
Type type = value.GetType();
if (type == typeof(int) || type == typeof(uint) || type == typeof(long) || type == typeof(ulong) || type == typeof(short) || type == typeof(ushort))
{
cell.DataType = Excel.CellValues.Number;
cell.CellValue = new Excel.CellValue(value.ToString());
}
else if (value.GetType() == typeof(decimal) || value.GetType() == typeof(float) || value.GetType() == typeof(double))
{
cell.DataType = Excel.CellValues.Number;
cell.CellValue = new Excel.CellValue(((decimal)value).ToString(".00", CultureInfo.InvariantCulture.NumberFormat));
}
else
{
if (cell.CellFormula != null)
{
cell.DataType = Excel.CellValues.String;
cell.CellValue = new Excel.CellValue(value.ToString());
}
else
{
var stringValue = value.ToString();
if (!sharedStrings.TryGetIndex(stringValue, out var index))
{
index = sharedStrings.Add(new StringKey(stringValue));
}
cell.DataType = Excel.CellValues.SharedString;
cell.CellValue = new Excel.CellValue(index.ToString());
}
//cell.DataType = Excel.CellValues.String;
//cell.CellValue = new Excel.CellValue(value.ToString().Replace(" ", string.Empty));
}
}
}
public List <Excel.Cell> FindParsedCells(StringKeyList stringTable, Excel.SheetData sd)
{
List <Excel.Cell> results = new List <Excel.Cell>();
foreach (OpenXmlElement element in sd)
{
if (element is Excel.Row row)
{
foreach (OpenXmlElement celement in element)
{
if (celement is Excel.Cell cell)
{
string val = ReadCell(cell, stringTable);
if (val.IndexOf('#') >= 0)
{
results.Add(cell);
}
}
}
}
}
return(results);
}
public Excel.Cell GetCell(OpenXmlCompositeElement row, object value, int c, int r, uint styleIndex, StringKeyList sharedStrings)
{
string reference = Helper.IntToChar(c) + r.ToString();
Excel.Cell cell = null;
if (row != null)
{
foreach (var rowCell in row.Elements <Excel.Cell>())
{
var cref = CellReference.Parse(rowCell.CellReference.Value);
if (cref.Col == c)
{
cell = rowCell;
break;
}
else if (cref.Col > c)
{
cell = new Excel.Cell()
{
CellReference = reference, StyleIndex = styleIndex
};
row.InsertBefore <Excel.Cell>(cell, rowCell);
break;
}
}
}
if (cell == null || cell.CellReference.Value != reference)
{
cell = new Excel.Cell()
{
CellReference = reference, StyleIndex = styleIndex
};
if (row != null)
{
row.AppendChild <Excel.Cell>(cell);
}
}
WriteCell(cell, value, sharedStrings);
return(cell);
}
public static void WriteStringTable(SharedStringTablePart sharedStringTablePart, StringKeyList items)
{
using (var writer = XmlWriter.Create(sharedStringTablePart.GetStream(FileMode.Create)
, new XmlWriterSettings {
Encoding = Encoding.UTF8, CloseOutput = true
}))
{
writer.WriteStartDocument();
WriteStartElement(writer, new Excel.SharedStringTable {
Count = (uint)items.Count
});
foreach (var item in items)
{
WriteElement(writer, item.Value);
}
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);
}
}
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 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);
}