/// <summary>
/// 在当前工作簿中创建一个可以在各Sheet中共用的存放字符串的容器对象
/// </summary>
/// <param name="workbookPart">当前的工作簿对象</param>
/// <returns>返回创建后的共享容器对象</returns>
public static SharedStringTablePart CreateSharedPart(this WorkbookPart workbookPart)
{
SharedStringTablePart shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (shareStringPart == null)
shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
return shareStringPart;
}
// Given the main workbook part, and a text value, insert the text into the shared
// string table. Create the table if necessary. If the value already exists, return
// its index. If it doesn't exist, insert it and return its new index.
public static int InsertSharedStringItem(this WorkbookPart wbPart, string value)
{
// Insert a value into the shared string table, creating the table if necessary.
// Insert the string if it's not already there.
// Return the index of the string.
int index = 0;
bool found = false;
var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something's wrong.
// Just return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (stringTablePart == null)
{
// Create it.
stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
}
SharedStringTable stringTable;
if (stringTablePart != null && stringTablePart.SharedStringTable != null)
{
stringTable = stringTablePart.SharedStringTable;
}
else
{
stringTable = new SharedStringTable();
stringTablePart.SharedStringTable = new SharedStringTable();
}
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == value)
{
found = true;
break;
}
index += 1;
}
if (!found)
{
if (stringTablePart.SharedStringTable.Count == null)
{
stringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new Text(value)));
stringTablePart.SharedStringTable.Save();
}
else
{
stringTable.AppendChild(new SharedStringItem(new Text(value)));
// stringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new Text(value)));
stringTable.Save();
}
}
return index;
}
public static WorksheetPart AddWorksheet(this WorkbookPart workbookPart, string name, Worksheet sheet)
{
sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
var result = workbookPart.AddNewPart<WorksheetPart>();
result.Worksheet = sheet;
sheet.Save();
workbookPart.Workbook.Sheets.Append(
new Sheet
{
Name = name,
SheetId = (uint)workbookPart.Workbook.Sheets.Count() + 1,
Id = workbookPart.GetIdOfPart(result)
});
return result;
}
/// <summary>
/// 往当前工作簿中插入一个Sheet工作区
/// </summary>
/// <param name="workbookPart">当前的工作簿对象</param>
/// <param name="sheetName">指定新插入工作区的名称</param>
/// <returns>返回一个新的工作区节点对象WorksheetPart</returns>
public static WorksheetPart InsertSheet(this WorkbookPart workbookPart, string sheetName = null)
{
//创建一个新的WorkssheetPart
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
//取得Sheet集合
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>() ?? workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
//得到Sheet的唯一序号
IEnumerable<Sheet> sheetList = sheets.Elements<Sheet>();
uint sheetId = sheetList.Count() > 0 ? sheetList.Select(s => s.SheetId.Value).Max() + 1 : 1;
string sheetTempName = "Sheet" + sheetId;
//如果有设置Sheet名称，并且不与当前的文档中其它的工作区重名，则使用设置的名称
if (!General.IsNullable(sheetName) && sheetList.Where(s => s.Name.Equals(sheetName)).Count() <= 0)
sheetTempName = sheetName;
//创建Sheet实例并将它与sheets关联
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetTempName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
return newWorksheetPart;
}