How to format cells in Excel using open XML
I am using OpenXML to manipulate Excel files.
I am sending the Excel files as memory stream from SharePoint, and editing them: ie inserting a new worksheet and adding some data from SharePoint to that newly inserted workssheeet. The memorystream is then sent to the browser so it opens in the client office program.
I can insert a new sheet and add data to the cells from other resources, but I am having problems formatting the cells. Like making them bold, changing font and color etc. I have tried the approach as descbribed here, but it messes up all the other sheets in my workbook because the style is then overwritten.
https://blogs.msdn.microsoft.com/chrisquon/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0/
How can I add my own style, and apply them to my cells without overwriting the style of the other sheets. This is my code:
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;
}