Right, I managed to figure this out, after a lot of experimentation. It turns out that excel reserves styles 0 and 1 for normal cells and “Gray125” pattern fill respectively. Most of the above code can be removed, as we only need a CellFormat really. Working code: Console.WriteLine(“Creating document”); using (var spreadsheet = SpreadsheetDocument.Create(“output.xlsx”, SpreadsheetDocumentType.Workbook)) … Read more
With a worksheet object called worksheet, worksheet.Dimension.Start.Row and worksheet.Dimension.End.Row should give you the information you need. worksheet.Dimension.Address will give you a string containing the worksheet dimensions in the traditional Excel range format (e.g. ‘A1:I5’ for rows 1-5, columns 1-9). There is a documentation file available. In many cases it might be just as quick to … Read more
This makes sense since Excel will not store a value for a cell that is null. If you open your file using the Open XML SDK 2.0 Productivity Tool and traverse the XML down to the cell level you will see that only the cells that have data are going to be in that file. … Read more
If you use a MemoryStream you can save the changes to a new file like this: byte[] byteArray = File.ReadAllBytes(“c:\\data\\hello.docx”); using (MemoryStream stream = new MemoryStream()) { stream.Write(byteArray, 0, (int)byteArray.Length); using (WordprocessingDocument wordDoc = WordprocessingDocument.Open(stream, true)) { // Do work here } // Save the file with the new name File.WriteAllBytes(“C:\\data\\newFileName.docx”, stream.ToArray()); }
All strings in an Excel worksheet are stored in a array like structure called the SharedStringTable. The goal of this table is to centralize all strings in an index based array and then if that string is used multiple times in the document to just reference the index in this array. That being said, the … Read more
To insert newlines, you have to add a Break instance to the Run. Example: run.AppendChild(new Text(“Hello”)); run.AppendChild(new Break()); run.AppendChild(new Text(“world”)); The XML produced will be something like: <w:r> <w:t>Hello</w:t> <w:br/> <w:t>world</w:t> </w:r>
The answer is going to depend slightly upon if the application is running on a server or if it is running on the client machine. If you are running on a server then you are going to want to use one of the XML based office generation formats as there are know issues when using … Read more
Try this Color colFromHex = System.Drawing.ColorTranslator.FromHtml(“#B7DEE8”); ws.Cells[“A1:B1”].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells[“A1:B1”].Style.Fill.BackgroundColor.SetColor(colFromHex);