openxml

How to create Excel file using OpenXML without creating a local file?

You could use the overload of SpreadsheetDocument.Create that takes a Stream and pass it a MemoryStream: MemoryStream memoryStream = new MemoryStream(); SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook); //add the excel contents… //reset the position to the start of the stream memoryStream.Seek(0, SeekOrigin.Begin); return new FileStreamResult(memoryStream, “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”); Note that as per this StackOverflow question you don’t need … Read more

How do I read data from a spreadsheet using the OpenXML Format SDK?

The other answer seemed more like a meta-answer. I have been struggling with this since using LINQ does work with separated document parts. The following code includes a wrapper function to get the value from a Cell, resolving any possible string lookups. public void ExcelDocTest() { Debug.WriteLine(“Running through sheet.”); int rowsComplete = 0; using (SpreadsheetDocument … Read more

Replace image in word doc using OpenXML

Although the documentation for OpenXML isn’t great, there is an excellent tool that you can use to see how existing Word documents are built. If you install the OpenXml SDK it comes with the DocumentReflector.exe tool under the Open XML Format SDK\V2.0\tools directory. Images in Word documents consist of the image data and an ID … Read more

How to convert HSSFWorkbook to XSSFWorkbook using Apache POI?

this code has been adapted from what I found here on coderanch forum public final class ExcelDocumentConverter { public static XSSFWorkbook convertWorkbookHSSFToXSSF(HSSFWorkbook source) { XSSFWorkbook retVal = new XSSFWorkbook(); for (int i = 0; i < source.getNumberOfSheets(); i++) { XSSFSheet xssfSheet = retVal.createSheet(); HSSFSheet hssfsheet = source.getSheetAt(i); copySheets(hssfsheet, xssfSheet); } return retVal; } public static … Read more

OpenXml and Date format in Excel cell

This blog helped me: http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/ My problem was that I wanted to add NumberingFormats to the stylesheet rather than adding a new stylesheet altogether. If you want to to that, use Stylesheet.InsertAt<NumberingFormats>(new NumberingFormats(), 0); rather than Stylesheet.AppendChild<NumberingFormats>(new NumberingFormats(), 0); surprise, order counts..

Add HTML String to OpenXML (*.docx) Document

I can reproduce the error “… there is a problem with the content” by using an incomplete HTML document as the content of the alternative format import part. For example if you use the following HTML snippet <h1>HELLO</h1> MS Word is unable to open the document. The code below shows how to add an AlternativeFormatImportPart … Read more

Cell styles in OpenXML spreadsheet (SpreadsheetML)

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