代碼示例中的標準方法是迭代 sharedstringtable 搜索以獲取要添加的值。添加; 如果未找到，如果字串存在，則使用現有。但是，在幾百個值之后，這種搜索會顯著減慢。
我使用 LINQ 查詢來搜索字串，它看起來更快。請參閱代碼中注釋掉的行。但是，我無法確定找到的專案的位置。有誰知道一種識別共享字串表中專案位置的方法。
public static int InsertSharedStringItem(string text, InfoReg.OpenXML.Spreadsheet mySpreadsheet)
{
// If the part does not contain a SharedStringTable, create one.
if (mySpreadsheet.sharedstringtable == null)
{
mySpreadsheet.shareStringPart.SharedStringTable = new SharedStringTable();
}
//SharedStringItem item = mySpreadsheet.sharedstringtable.Elements<SharedStringItem>()
// .Where(t => t.InnerText == text)
// .FirstOrDefault();
//if(item != null)
//{
// return item's location in the shared string table;
//}
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
int i = 0;
foreach (SharedStringItem item1 in mySpreadsheet.sharedstringtable.Elements<SharedStringItem>())
{
if (item1.InnerText == text)
{
return i;
}
i ;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
mySpreadsheet.sharedstringtable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
return i;
}
使用以下方法添加 sharedstringtable 值：
index = InfoReg.OpenXML.Spreadsheet.InsertSharedStringItem(string.IsNullOrEmpty(datarow.AssignedTo) ? string.Empty : datarow.AssignedTo, myspreadsheet);
// Insert cell into the new worksheet.
cell = InfoReg.OpenXML.Spreadsheet.InsertCellInWorksheet(InfoReg.OpenXML.Spreadsheet.GetExcelColumnName(colAddr ), rowAddr, myspreadsheet.worksheetPart.Worksheet);
// Set the value of cell.
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
為了限制問題，我對匹配概率較低的單元格使用非共享字串方法。我使用這段代碼：
Cell cell = InfoReg.OpenXML.Spreadsheet.InsertCellInWorksheet(InfoReg.OpenXML.Spreadsheet.GetExcelColumnName(colAddr ), rowAddr, myspreadsheet.worksheetPart.Worksheet);
// Set the value of cell.
cell.CellValue = new CellValue(datarow.ForeName);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
一些文章建議使用 Dictionary<string, int> 和共享字串表的副本。我沒有看到 Dictionary 的性能有任何顯著改善。
uj5u.com熱心網友回復：
使用 Dictionary 的想法應該會給您帶來更好的性能，但是如果您每次要插入元素時都重新創建字典，您將不會節省任何時間，實際上只會花費更長的時間。
我看到它的方式是將 SharedStringTable 包裝在一個包裝器中，該包裝器快取并跟蹤元素，并確保包裝器是您訪問共享字串表的唯一方式。
public class SharedStringWrapper
{
private readonly SharedStringTable _table;
private readonly Lazy<Dictionary<string, (int index, OpenXmlElement element)>> _lazyDict;
private int _maxElement;
public SharedStringWrapper(SharedStringTable table)
{
_table = table;
_maxElement = _table.Elements().Count();
//lazy initialize, this could take a while, no reason to do it until we need it.
//in this case we ignore the casing of the text
_lazyDict = new Lazy<Dictionary<string, (int index, OpenXmlElement element)>>(
_table.Select((element, index) => (element, index)).ToDictionary(k => k.element.InnerText, v => (v.index, itm: v.element)));
}
/// <summary>
/// Inserts text into the shared string table and returns the index of the inserted text.
/// If the text already exists, it returns the index of the existing element
/// </summary>
/// <param name="text">text to insert</param>
/// <returns>index of the element in the shared strings table</returns>
public int InsertTextElement(string text)
{
//this is where you get the huge time saving - first time it will take a while, subsequently it'll be way faster
if (_lazyDict.Value.TryGetValue(text, out var value))
return value.index;
//append child and increment the count.
_table.AppendChild(new SharedStringItem(new Text(text)));
_maxElement ;
return _maxElement;
}
}
uj5u.com熱心網友回復：
在 jAnderson 的答案中使用 Lazy(Dictionary.. .
下面的代碼對記錄在https://docs.microsoft.com/en-us/office/open-xml/working-with-the-shared-string-table的標準迭代方法提供了顯著的性能改進。
修改后的包裝類使用字典來初始鏡像共享字串表，并且隨著新條目添加到表中，它們也添加到字典中。因此，可以搜索字典以查找現有條目，而不是遍歷共享字串表。
我們的一些資料不完整。經常遇到空值??和空字串。因此，添加了空測驗和從 SharedStringTable 回傳空字串條目的快速路徑。
雖然這是一個巨大的改進，但 SharedStringTable 的內部管理可以使用一些二進制索引或類似的更具可擴展性的解決方案。
修改后的包裝類。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace MyOpenXML
{
public class SharedStringWrapper
{
private readonly SharedStringTable _table;
private readonly Dictionary<string, int> _dict;
private int _maxElement;
private int _emptyStringElement;
public SharedStringWrapper(SharedStringTable table)
{
_table = table; // Gets the table as is in the spreadsheet
_maxElement = _table.Elements().Count(); // Count of elements at the start
_dict = new Dictionary<string, int>(); // Shadow copy of the shared string table
int i = 0;
foreach (SharedStringItem item in _table.Elements<SharedStringItem>()) // _dict is given its initial copy
{
_dict.Add(item.InnerText, i );
}
_emptyStringElement = InsertTextElement(string.Empty); // Used for fast location of empty string value
}
/// <summary>
/// Inserts text into the shared string table and returns the index of the inserted text.
/// If the text already exists, it returns the index of the existing element.
/// Also inserts a copy in _dict so that it is a copy of the shared string table.
/// </summary>
/// <param name="text">text to insert</param>
/// <returns>index of the element in the shared strings table</returns>
public int InsertTextElement(string text)
{
if (string.IsNullOrEmpty(text)) return _emptyStringElement; // Treat null as empty string
int indx = _maxElement; // Value for new entry
try
{
indx = _dict[text]; // If the text is found the index is to be returned
}
catch // An entry has not been found, add it to both the shared string table and the dictionary
{
//append child and increment the count.
_table.AppendChild(new SharedStringItem(new Text(text)));
_dict.Add(text, _maxElement );
}
return indx; // return index.
}
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/479524.html
下一篇：Excel查找2條件