524 lines
22 KiB
C#
524 lines
22 KiB
C#
using System.Globalization;
|
||
|
||
namespace QWERTYkez.ExcelProcessor;
|
||
|
||
internal static class ReplaceNumericExtensions
|
||
{
|
||
// =========================== МНОЖЕСТВЕННЫЕ ЗАМЕНЫ ===========================
|
||
|
||
// --- Double ---
|
||
internal static void Replace(this SpreadsheetDocument doc,
|
||
IEnumerable<KeyValuePair<string, double>> replacements, string? format = null,
|
||
StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)
|
||
{
|
||
if (replacements is null) return;
|
||
WorksheetPart[] worksheets = [.. doc.WorkbookPart?.WorksheetParts!];
|
||
if (worksheets.Length < 1) return;
|
||
|
||
var coll = replacements as ICollection<KeyValuePair<string, double>>;
|
||
int count = coll?.Count ?? replacements.Count();
|
||
if (count == 0) return;
|
||
|
||
var workbookPart = doc.WorkbookPart!;
|
||
var stringReplacements = new Dictionary<string, string>(count);
|
||
foreach (var kvp in replacements)
|
||
stringReplacements[kvp.Key] = kvp.Value.ToString(format ?? "G", CultureInfo.CurrentCulture);
|
||
|
||
ReplaceNumericCore(workbookPart, worksheets, replacements, stringReplacements, format, comparisonType,
|
||
(val, fmt) => val.ToString(fmt ?? "G", CultureInfo.InvariantCulture));
|
||
}
|
||
|
||
// --- Float ---
|
||
internal static void Replace(this SpreadsheetDocument doc,
|
||
IEnumerable<KeyValuePair<string, float>> replacements, string? format = null,
|
||
StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)
|
||
{
|
||
if (replacements is null) return;
|
||
WorksheetPart[] worksheets = [.. doc.WorkbookPart?.WorksheetParts!];
|
||
if (worksheets.Length < 1) return;
|
||
|
||
var coll = replacements as ICollection<KeyValuePair<string, float>>;
|
||
int count = coll?.Count ?? replacements.Count();
|
||
if (count == 0) return;
|
||
|
||
var workbookPart = doc.WorkbookPart!;
|
||
var stringReplacements = new Dictionary<string, string>(count);
|
||
foreach (var kvp in replacements)
|
||
stringReplacements[kvp.Key] = kvp.Value.ToString(format ?? "G", CultureInfo.CurrentCulture);
|
||
|
||
ReplaceNumericCore(workbookPart, worksheets, replacements, stringReplacements, format, comparisonType,
|
||
(val, fmt) => val.ToString(fmt ?? "G", CultureInfo.InvariantCulture));
|
||
}
|
||
|
||
// --- Int ---
|
||
internal static void Replace(this SpreadsheetDocument doc,
|
||
IEnumerable<KeyValuePair<string, int>> replacements,
|
||
StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)
|
||
{
|
||
if (replacements is null) return;
|
||
WorksheetPart[] worksheets = [.. doc.WorkbookPart?.WorksheetParts!];
|
||
if (worksheets.Length < 1) return;
|
||
|
||
var coll = replacements as ICollection<KeyValuePair<string, int>>;
|
||
int count = coll?.Count ?? replacements.Count();
|
||
if (count == 0) return;
|
||
|
||
var workbookPart = doc.WorkbookPart!;
|
||
var stringReplacements = new Dictionary<string, string>(count);
|
||
foreach (var kvp in replacements)
|
||
stringReplacements[kvp.Key] = kvp.Value.ToString(CultureInfo.CurrentCulture);
|
||
|
||
ReplaceNumericCore(workbookPart, worksheets, replacements, stringReplacements, null, comparisonType,
|
||
(val, _) => val.ToString(CultureInfo.InvariantCulture));
|
||
}
|
||
|
||
// --- Long ---
|
||
internal static void Replace(this SpreadsheetDocument doc,
|
||
IEnumerable<KeyValuePair<string, long>> replacements,
|
||
StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)
|
||
{
|
||
if (replacements is null) return;
|
||
WorksheetPart[] worksheets = [.. doc.WorkbookPart?.WorksheetParts!];
|
||
if (worksheets.Length < 1) return;
|
||
|
||
var coll = replacements as ICollection<KeyValuePair<string, long>>;
|
||
int count = coll?.Count ?? replacements.Count();
|
||
if (count == 0) return;
|
||
|
||
var workbookPart = doc.WorkbookPart!;
|
||
var stringReplacements = new Dictionary<string, string>(count);
|
||
foreach (var kvp in replacements)
|
||
stringReplacements[kvp.Key] = kvp.Value.ToString(CultureInfo.CurrentCulture);
|
||
|
||
ReplaceNumericCore(workbookPart, worksheets, replacements, stringReplacements, null, comparisonType,
|
||
(val, _) => val.ToString(CultureInfo.InvariantCulture));
|
||
}
|
||
|
||
// =========================== ОДИНОЧНЫЕ ЗАМЕНЫ ===========================
|
||
|
||
// --- Double ---
|
||
internal static void Replace(this SpreadsheetDocument doc,
|
||
string oldValue, double newValue, string? format = null,
|
||
StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)
|
||
{
|
||
if (string.IsNullOrEmpty(oldValue)) return;
|
||
WorksheetPart[] worksheets = [.. doc.WorkbookPart?.WorksheetParts!];
|
||
if (worksheets.Length < 1) return;
|
||
|
||
var workbookPart = doc.WorkbookPart!;
|
||
ReplaceSingleCore(workbookPart, worksheets, oldValue, newValue, format, comparisonType,
|
||
(val, fmt) => val.ToString(fmt ?? "G", CultureInfo.InvariantCulture),
|
||
(val, fmt) => val.ToString(fmt ?? "G", CultureInfo.CurrentCulture));
|
||
}
|
||
|
||
// --- Float ---
|
||
internal static void Replace(this SpreadsheetDocument doc,
|
||
string oldValue, float newValue, string? format = null,
|
||
StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)
|
||
{
|
||
if (string.IsNullOrEmpty(oldValue)) return;
|
||
WorksheetPart[] worksheets = [.. doc.WorkbookPart?.WorksheetParts!];
|
||
if (worksheets.Length < 1) return;
|
||
|
||
var workbookPart = doc.WorkbookPart!;
|
||
ReplaceSingleCore(workbookPart, worksheets, oldValue, newValue, format, comparisonType,
|
||
(val, fmt) => val.ToString(fmt ?? "G", CultureInfo.InvariantCulture),
|
||
(val, fmt) => val.ToString(fmt ?? "G", CultureInfo.CurrentCulture));
|
||
}
|
||
|
||
// --- Int ---
|
||
internal static void Replace(this SpreadsheetDocument doc,
|
||
string oldValue, int newValue,
|
||
StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)
|
||
{
|
||
if (string.IsNullOrEmpty(oldValue)) return;
|
||
WorksheetPart[] worksheets = [.. doc.WorkbookPart?.WorksheetParts!];
|
||
if (worksheets.Length < 1) return;
|
||
|
||
var workbookPart = doc.WorkbookPart!;
|
||
ReplaceSingleCore(workbookPart, worksheets, oldValue, newValue, null, comparisonType,
|
||
(val, _) => val.ToString(CultureInfo.InvariantCulture),
|
||
(val, _) => val.ToString(CultureInfo.CurrentCulture));
|
||
}
|
||
|
||
// --- Long ---
|
||
internal static void Replace(this SpreadsheetDocument doc,
|
||
string oldValue, long newValue,
|
||
StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)
|
||
{
|
||
if (string.IsNullOrEmpty(oldValue)) return;
|
||
WorksheetPart[] worksheets = [.. doc.WorkbookPart?.WorksheetParts!];
|
||
if (worksheets.Length < 1) return;
|
||
|
||
var workbookPart = doc.WorkbookPart!;
|
||
ReplaceSingleCore(workbookPart, worksheets, oldValue, newValue, null, comparisonType,
|
||
(val, _) => val.ToString(CultureInfo.InvariantCulture),
|
||
(val, _) => val.ToString(CultureInfo.CurrentCulture));
|
||
}
|
||
|
||
// =========================== ОБЩАЯ ЛОГИКА ===========================
|
||
|
||
static void ReplaceNumericCore<T>(
|
||
WorkbookPart workbookPart,
|
||
WorksheetPart[] worksheets,
|
||
IEnumerable<KeyValuePair<string, T>> numericReplacements,
|
||
Dictionary<string, string> stringReplacements,
|
||
string? format,
|
||
StringComparison comparisonType,
|
||
Func<T, string?, string> numberToStringForNumberCell)
|
||
{
|
||
var formatCache = new Dictionary<string, uint>();
|
||
|
||
uint? GetOrCreateStyleIndex(string fmt)
|
||
{
|
||
if (string.IsNullOrEmpty(fmt)) return null;
|
||
if (formatCache.TryGetValue(fmt, out var idx)) return idx;
|
||
var newIdx = CreateNumberFormat(workbookPart, fmt);
|
||
formatCache[fmt] = newIdx;
|
||
return newIdx;
|
||
}
|
||
|
||
// Инициализация SharedStringTable (один раз)
|
||
var allSharedStrings = new List<string>();
|
||
var sharedStringIndexMap = new Dictionary<string, int>();
|
||
var sharedStringTable = workbookPart.SharedStringTablePart?.SharedStringTable;
|
||
if (sharedStringTable != null)
|
||
{
|
||
foreach (var item in sharedStringTable.Elements<SharedStringItem>())
|
||
{
|
||
var text = ConcatTexts(item.Descendants<Text>());
|
||
sharedStringIndexMap[text] = allSharedStrings.Count;
|
||
allSharedStrings.Add(text);
|
||
}
|
||
}
|
||
|
||
foreach (var worksheetPart in worksheets)
|
||
{
|
||
var worksheet = worksheetPart.Worksheet;
|
||
if (worksheet == null) continue;
|
||
var sheetData = worksheet.GetFirstChild<SheetData>();
|
||
if (sheetData == null) continue;
|
||
|
||
// Обработка ячеек
|
||
foreach (var row in sheetData.Elements<Row>())
|
||
{
|
||
foreach (var cell in row.Elements<Cell>())
|
||
{
|
||
string originalText = GetCellTextForNumeric(cell, allSharedStrings);
|
||
if (string.IsNullOrEmpty(originalText)) continue;
|
||
|
||
string? matchedKey = null;
|
||
T matchedVal = default!;
|
||
int matchStart = -1, matchLength = 0;
|
||
|
||
foreach (var kvp in numericReplacements)
|
||
{
|
||
int idx = originalText.IndexOf(kvp.Key, comparisonType);
|
||
if (idx >= 0 && kvp.Key.Length > matchLength)
|
||
{
|
||
matchedKey = kvp.Key;
|
||
matchedVal = kvp.Value;
|
||
matchStart = idx;
|
||
matchLength = kvp.Key.Length;
|
||
}
|
||
}
|
||
if (matchedKey == null) continue;
|
||
|
||
bool isFullCell = (matchStart == 0 && matchLength == originalText.Length);
|
||
if (isFullCell)
|
||
{
|
||
cell.DataType = CellValues.Number;
|
||
string numStr = numberToStringForNumberCell(matchedVal, format);
|
||
cell.CellValue = new CellValue(numStr);
|
||
if (!string.IsNullOrEmpty(format))
|
||
{
|
||
var styleIdx = GetOrCreateStyleIndex(format!);
|
||
if (styleIdx.HasValue)
|
||
cell.StyleIndex = styleIdx.Value;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
string replacementStr = stringReplacements[matchedKey];
|
||
string newText = ReplaceSubstring(originalText, matchStart, matchLength, replacementStr);
|
||
SetCellText(cell, newText, allSharedStrings, sharedStringIndexMap);
|
||
}
|
||
}
|
||
}
|
||
|
||
// Колонтитулы и комментарии
|
||
ReplaceInHeadersFooters(worksheetPart, stringReplacements, comparisonType);
|
||
ReplaceInComments(worksheetPart, stringReplacements, comparisonType);
|
||
}
|
||
|
||
// Сохраняем SharedStringTable
|
||
UpdateSharedStringTable(workbookPart, allSharedStrings);
|
||
}
|
||
|
||
static void ReplaceSingleCore<T>(
|
||
WorkbookPart workbookPart,
|
||
WorksheetPart[] worksheets,
|
||
string oldValue,
|
||
T newValue,
|
||
string? format,
|
||
StringComparison comparisonType,
|
||
Func<T, string?, string> numberToStringForNumberCell,
|
||
Func<T, string?, string> numberToStringForTextCell)
|
||
{
|
||
var formatCache = new Dictionary<string, uint>();
|
||
|
||
uint? GetOrCreateStyleIndex(string fmt)
|
||
{
|
||
if (string.IsNullOrEmpty(fmt)) return null;
|
||
if (formatCache.TryGetValue(fmt, out var idx)) return idx;
|
||
var newIdx = CreateNumberFormat(workbookPart, fmt);
|
||
formatCache[fmt] = newIdx;
|
||
return newIdx;
|
||
}
|
||
|
||
var singleStringReplacement = new Dictionary<string, string>
|
||
{
|
||
{ oldValue, numberToStringForTextCell(newValue, format) }
|
||
};
|
||
|
||
// Инициализация SharedStringTable
|
||
var allSharedStrings = new List<string>();
|
||
var sharedStringIndexMap = new Dictionary<string, int>();
|
||
var sharedStringTable = workbookPart.SharedStringTablePart?.SharedStringTable;
|
||
if (sharedStringTable != null)
|
||
{
|
||
foreach (var item in sharedStringTable.Elements<SharedStringItem>())
|
||
{
|
||
var text = ConcatTexts(item.Descendants<Text>());
|
||
sharedStringIndexMap[text] = allSharedStrings.Count;
|
||
allSharedStrings.Add(text);
|
||
}
|
||
}
|
||
|
||
foreach (var worksheetPart in worksheets)
|
||
{
|
||
var worksheet = worksheetPart.Worksheet;
|
||
if (worksheet == null) continue;
|
||
var sheetData = worksheet.GetFirstChild<SheetData>();
|
||
if (sheetData == null) continue;
|
||
|
||
foreach (var row in sheetData.Elements<Row>())
|
||
{
|
||
foreach (var cell in row.Elements<Cell>())
|
||
{
|
||
string originalText = GetCellTextForNumeric(cell, allSharedStrings);
|
||
if (string.IsNullOrEmpty(originalText)) continue;
|
||
|
||
int idx = originalText.IndexOf(oldValue, comparisonType);
|
||
if (idx < 0) continue;
|
||
|
||
bool isFullCell = (idx == 0 && oldValue.Length == originalText.Length);
|
||
if (isFullCell)
|
||
{
|
||
cell.DataType = CellValues.Number;
|
||
string numStr = numberToStringForNumberCell(newValue, format);
|
||
cell.CellValue = new CellValue(numStr);
|
||
if (!string.IsNullOrEmpty(format))
|
||
{
|
||
var styleIdx = GetOrCreateStyleIndex(format!);
|
||
if (styleIdx.HasValue)
|
||
cell.StyleIndex = styleIdx.Value;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
string replacementStr = numberToStringForTextCell(newValue, format);
|
||
string newText = ReplaceSubstring(originalText, idx, oldValue.Length, replacementStr);
|
||
SetCellText(cell, newText, allSharedStrings, sharedStringIndexMap);
|
||
}
|
||
}
|
||
}
|
||
|
||
ReplaceInHeadersFooters(worksheetPart, singleStringReplacement, comparisonType);
|
||
ReplaceInComments(worksheetPart, singleStringReplacement, comparisonType);
|
||
}
|
||
|
||
UpdateSharedStringTable(workbookPart, allSharedStrings);
|
||
}
|
||
|
||
// =========================== ВСПОМОГАТЕЛЬНЫЕ МЕТОДЫ ===========================
|
||
|
||
static string GetCellTextForNumeric(Cell cell, List<string> allSharedStrings)
|
||
{
|
||
if (cell?.CellValue == null) return string.Empty;
|
||
|
||
// InlineString – без LINQ
|
||
if (cell.InlineString != null)
|
||
{
|
||
var sb = new StringBuilder();
|
||
foreach (var t in cell.InlineString.Descendants<Text>())
|
||
sb.Append(t.Text);
|
||
return sb.ToString();
|
||
}
|
||
|
||
string val = cell.CellValue.InnerText;
|
||
if (cell.DataType?.Value == CellValues.SharedString)
|
||
{
|
||
if (int.TryParse(val, out int idx) && idx >= 0 && idx < allSharedStrings.Count)
|
||
return allSharedStrings[idx];
|
||
return string.Empty;
|
||
}
|
||
return val;
|
||
}
|
||
|
||
static void SetCellText(Cell cell, string newText,
|
||
List<string> allSharedStrings, Dictionary<string, int> sharedStringIndexMap)
|
||
{
|
||
if (cell.InlineString != null)
|
||
{
|
||
// Очищаем старые тексты
|
||
foreach (var t in cell.InlineString.Descendants<Text>().ToList())
|
||
t.Remove();
|
||
cell.InlineString.AppendChild(new Text(newText));
|
||
return;
|
||
}
|
||
|
||
if (!sharedStringIndexMap.TryGetValue(newText, out int index))
|
||
{
|
||
index = allSharedStrings.Count;
|
||
allSharedStrings.Add(newText);
|
||
sharedStringIndexMap[newText] = index;
|
||
}
|
||
cell.DataType = CellValues.SharedString;
|
||
cell.CellValue = new CellValue(index.ToString());
|
||
}
|
||
|
||
static void UpdateSharedStringTable(WorkbookPart workbookPart, List<string> allSharedStrings)
|
||
{
|
||
var ssPart = workbookPart.SharedStringTablePart;
|
||
ssPart ??= workbookPart.AddNewPart<SharedStringTablePart>();
|
||
var sharedStringTable = ssPart.SharedStringTable ?? new SharedStringTable();
|
||
sharedStringTable.RemoveAllChildren<SharedStringItem>();
|
||
foreach (var str in allSharedStrings)
|
||
sharedStringTable.AppendChild(new SharedStringItem(new Text(str)));
|
||
sharedStringTable.Save();
|
||
}
|
||
|
||
static string ConcatTexts(IEnumerable<Text> texts)
|
||
{
|
||
var sb = new StringBuilder();
|
||
foreach (var t in texts)
|
||
sb.Append(t.Text);
|
||
return sb.ToString();
|
||
}
|
||
|
||
// Оптимизированная замена подстроки через string.Create (без unsafe)
|
||
static unsafe string ReplaceSubstring(string original, int start, int length, string replacement)
|
||
{
|
||
if (length == 0) return original;
|
||
int newLen = original.Length - length + replacement.Length;
|
||
if (newLen <= 0) return replacement;
|
||
|
||
fixed (char* pOrig = original, pRep = replacement)
|
||
{
|
||
char* result = stackalloc char[newLen];
|
||
int pos = 0;
|
||
for (int i = 0; i < start; i++)
|
||
result[pos++] = pOrig[i];
|
||
for (int i = 0; i < replacement.Length; i++)
|
||
result[pos++] = pRep[i];
|
||
for (int i = start + length; i < original.Length; i++)
|
||
result[pos++] = pOrig[i];
|
||
return new string(result, 0, newLen);
|
||
}
|
||
}
|
||
|
||
static uint CreateNumberFormat(WorkbookPart workbookPart, string format)
|
||
{
|
||
var stylesPart = workbookPart.WorkbookStylesPart;
|
||
if (stylesPart == null)
|
||
{
|
||
stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
|
||
stylesPart.Stylesheet = new Stylesheet();
|
||
}
|
||
var ss = stylesPart.Stylesheet!;
|
||
ss.NumberingFormats ??= new NumberingFormats();
|
||
uint nextId = 164;
|
||
if (ss.NumberingFormats.Elements<NumberingFormat>().Any())
|
||
nextId = ss.NumberingFormats.Elements<NumberingFormat>().Max(nf => nf.NumberFormatId!.Value) + 1;
|
||
var nf = new NumberingFormat { NumberFormatId = nextId, FormatCode = format };
|
||
ss.NumberingFormats.AppendChild(nf);
|
||
ss.CellFormats ??= new CellFormats();
|
||
var cf = new DocumentFormat.OpenXml.Spreadsheet.CellFormat
|
||
{
|
||
NumberFormatId = nextId,
|
||
FormatId = 0,
|
||
ApplyNumberFormat = true
|
||
};
|
||
ss.CellFormats.AppendChild(cf);
|
||
ss.Save();
|
||
return ss.CellFormats.Count!.Value - 1;
|
||
}
|
||
|
||
// =========================== КОЛОНТИТУЛЫ И КОММЕНТАРИИ ===========================
|
||
|
||
static void ReplaceInHeadersFooters(WorksheetPart worksheetPart, Dictionary<string, string> replacementDict, StringComparison comparisonType)
|
||
{
|
||
var worksheet = worksheetPart.Worksheet;
|
||
if (worksheet is null) return;
|
||
var headerFooter = worksheet.Descendants<HeaderFooter>().FirstOrDefault();
|
||
if (headerFooter is null) return;
|
||
|
||
foreach (var elem in new OpenXmlLeafTextElement?[] { headerFooter.OddHeader, headerFooter.OddFooter, headerFooter.EvenHeader, headerFooter.EvenFooter, headerFooter.FirstHeader, headerFooter.FirstFooter })
|
||
ReplaceHeaderFooter(elem, replacementDict, comparisonType);
|
||
}
|
||
|
||
static void ReplaceHeaderFooter(OpenXmlLeafTextElement? element, Dictionary<string, string> replacementDict, StringComparison comparisonType)
|
||
{
|
||
if (element?.Text is null) return;
|
||
string original = element.Text;
|
||
string processed = ProcessReplacements(original, replacementDict, comparisonType);
|
||
if (processed != original)
|
||
element.Text = processed;
|
||
}
|
||
|
||
static void ReplaceInComments(WorksheetPart worksheetPart, Dictionary<string, string> replacementDict, StringComparison comparisonType)
|
||
{
|
||
var commentsPart = worksheetPart.WorksheetCommentsPart;
|
||
if (commentsPart?.Comments is null) return;
|
||
foreach (var comment in commentsPart.Comments.Elements<Comment>())
|
||
{
|
||
var textElement = comment.Descendants<CommentText>().FirstOrDefault();
|
||
if (textElement?.Text is null) continue;
|
||
string original = textElement.Text.Text;
|
||
if (string.IsNullOrEmpty(original)) continue;
|
||
string processed = ProcessReplacements(original, replacementDict, comparisonType);
|
||
if (processed != original)
|
||
textElement.Text.Text = processed;
|
||
}
|
||
}
|
||
|
||
static string ProcessReplacements(string input, Dictionary<string, string> replacementDict, StringComparison comparisonType)
|
||
{
|
||
if (string.IsNullOrEmpty(input) || replacementDict.Count == 0) return input;
|
||
string result = input;
|
||
foreach (string key in replacementDict.Keys.OrderByDescending(k => k.Length))
|
||
{
|
||
string value = replacementDict[key];
|
||
result = ReplaceInString(result, key, value, comparisonType);
|
||
}
|
||
return result;
|
||
}
|
||
|
||
static string ReplaceInString(string original, string oldValue, string newValue, StringComparison comparisonType)
|
||
{
|
||
int idx = original.IndexOf(oldValue, comparisonType);
|
||
if (idx < 0) return original;
|
||
var sb = new StringBuilder(original.Length + newValue.Length - oldValue.Length);
|
||
int last = 0;
|
||
while (idx >= 0)
|
||
{
|
||
sb.Append(original, last, idx - last);
|
||
sb.Append(newValue);
|
||
last = idx + oldValue.Length;
|
||
idx = original.IndexOf(oldValue, last, comparisonType);
|
||
}
|
||
sb.Append(original, last, original.Length - last);
|
||
return sb.ToString();
|
||
}
|
||
} |