如何使用开放XML在Excel中格式化单元格
我正在使用OpenXML来操作Excel文件。
我将Excel文件作为SharePoint中的内存流发送，并对其进行编辑：即插入新的工作表并将一些数据从SharePoint添加到新插入的workssheeet中。 然后将内存流发送到浏览器，以便在客户端办公室程序中打开。
我可以插入新工作表并将数据添加到其他资源的单元格中，但是我在格式化单元格时遇到问题。 就像使它们变粗，改变字体和颜色等一样。我尝试了这里所描述的方法，但是它会弄乱我的工作簿中的所有其他表单，因为样式会被覆盖。
https://blogs.msdn.microsoft.com/chrisquon/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0/
如何添加我自己的样式，并将其应用于我的单元格而不覆盖其他工作表的样式。 这是我的代码：
private static void BuildWorkSheet(WorksheetPart worksheetPart, SharedStringTablePart shareStringPart)
{
// Insert the text into the SharedStringTablePart.
var docTypeIndex = InsertSharedStringItem("Dokumenttype", shareStringPart);
var titleIndexindex = InsertSharedStringItem("TITTEL ", shareStringPart);
var docIdIndex = InsertSharedStringItem("Dokument-ID:", shareStringPart);
var confidencialityIndex = InsertSharedStringItem("Konfidensialitet:", shareStringPart);
// Insert cell A1 into the new worksheet.
var docTypeCell = InsertCellInWorksheet("A", 3, worksheetPart);
var titleCell = InsertCellInWorksheet("A", 4, worksheetPart);
var docIdCell = InsertCellInWorksheet("A", 6, worksheetPart);
var confidencialityCell = InsertCellInWorksheet("A", 7, worksheetPart);
// Set the value of cells
titleCell.CellValue = new CellValue(titleIndexindex.ToString());
titleCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
docTypeCell.CellValue = new CellValue(docTypeIndex.ToString());
docTypeCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
docIdCell.CellValue = new CellValue(docIdIndex.ToString());
docIdCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
confidencialityCell.CellValue = new CellValue(confidencialityIndex.ToString());
confidencialityCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
// Given a WorkbookPart, inserts a new worksheet.
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
// Add a new worksheet part to the workbook.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
//sheets.Append(sheet);
var firstChild = sheets.FirstChild;
sheets.InsertBefore(sheet, firstChild);
workbookPart.Workbook.Save();
return newWorksheetPart;
}
// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
{
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}