Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>Using the OpenXML SDK directly instead of going through Excel's automation model is much more complex and error-prone. Therefore I recommend using a library for this; espcially if your task gets more complex (e.g. <a href="http://excelpackage.codeplex.com/" rel="nofollow">http://excelpackage.codeplex.com/</a>). <strong>Edit</strong>: An example on doing something similar using ExcelPackage can be found here: <a href="http://excelpackage.codeplex.com/wikipage?title=Using%20a%20template%20to%20create%20an%20Excel%20spreadsheet" rel="nofollow">http://excelpackage.codeplex.com/wikipage?title=Using%20a%20template%20to%20create%20an%20Excel%20spreadsheet</a> Although I have no idea on what performance to expect compared to using the raw SDK, I would guess that ExcelPackage is using the SDK internally anyway and therefore it might produce some overhead. Probably only a measurement for you concrete scenario can provide a definite answer here.</p> <p>If you want to stick to the SDK though here is an example for inserting strings into an Excel workbook:</p> <pre><code>string filePath = "workbook.xlsx"; string sheetName = "Sheet1"; uint startRow = 9; string columnName = "C"; string[] data = new string[] { "A", "B", "C" }; using (var spreadsheetDocument = SpreadsheetDocument.Open(filePath, true)) { // Find the Id of the worksheet in question var sheet = spreadsheetDocument.WorkbookPart.Workbook .Sheets.Elements&lt;Sheet&gt;() .Where(s =&gt; s.Name == sheetName).First(); var sheetReferenceId = sheet.Id; // Map the Id to the worksheet part WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheetReferenceId); var sheetData = worksheetPart.Worksheet.GetFirstChild&lt;SheetData&gt;(); // Inset the data at the given location for (uint i = 0; i &lt; data.Length; i++) { uint rowNumber = startRow + i; // Find the XML entry for row i var row = sheetData.Elements&lt;Row&gt;().Where(r =&gt; r.RowIndex == rowNumber).FirstOrDefault(); if (row == null) { // Row does not exist yet, create it row = new Row(); row.RowIndex = rowNumber; // Insert the row at its correct sequential position Row rowAfter = null; foreach (Row otherRow in sheetData.Elements&lt;Row&gt;()) { if (otherRow.RowIndex &gt; row.RowIndex) { rowAfter = otherRow; break; } } if (rowAfter == null) // New row is the last row in the sheet sheetData.Append(row); else sheetData.InsertBefore(row, rowAfter); } // CellReferences in OpenXML are "normal" Excel cell references, e.g. D15 string cellReference = columnName + rowNumber.ToString(); // Find cell in row var cell = row.Elements&lt;Cell&gt;() .Where(c =&gt; c.CellReference == cellReference) .FirstOrDefault(); if (cell == null) { // Cell does not exist yet, create it cell = new Cell(); cell.CellReference = new StringValue(cellReference); // The cell must be in the correct position (e.g. column B after A) // Note: AA must be after Z, so a normal string compare is not sufficient Cell cellAfter = null; foreach (Cell otherCell in row.Elements&lt;Cell&gt;()) { // This is ugly, but somehow the row number must be stripped from the // cell reference for comparison string otherCellColumn = otherCell.CellReference.Value; otherCellColumn = otherCellColumn.Remove(otherCellColumn.Length - rowNumber.ToString().Length); // Now compare first to length and then alphabetically if (otherCellColumn.Length &gt; columnName.Length || string.Compare(otherCellColumn, columnName, true) &gt; 0) { cellAfter = otherCell; break; } } if (cellAfter == null) // New cell is last cell in row row.Append(cell); else row.InsertBefore(cell, cellAfter); } // Note: This is the most simple approach. // Normally Excel itself will store the string as a SharedString, // which is more difficult to implement. The only drawback of using // this approach though, is that the cell might have been the only // reference to its shared string value, which is not deleted from the // list here. cell.DataType = CellValues.String; cell.CellValue = new CellValue(data[i]); } } </code></pre> <p>Please note that this example is not perfect as it does not consider complex scenarios at all (e.g. styling, print margin, merged cells...). For production use you might want to extract certain functionality into a method (for example inserting a row or cell at the correct position) or even into a class all by itself (e.g. the part about comparing cell references in correct order).</p> <p><strong>Edit</strong>: The performance of using the SDK instead of going through the Automation Model is MUCH better (this is probably the second huge advantage of the SDK, the first being that you do not neeed to have Excel installed). If you still see performance bottlenecks here are some ideas for improvement:</p> <ol> <li>Make sure that the cells are already created in the template document (e.g. insert a 0 into each of the using Excel). This saves you the effort to create new cells. </li> <li>Make sure that none of the cells already exists in the template and then create all of the cells in sequence (saving you the effort to find the correct insert position at least for the row). </li> <li>Manipulate the XML directly, which is more difficult and requires even more knowledge of the inner workings of OpenXML. This is probably acceptable for rather uniform documents.</li> </ol>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload