Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>Use OpenXMLSDK - a free download that needs to be installed on the server.</p> <pre><code> [...] using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; public class OffBookAssetLibraryEventReceiver : SPItemEventReceiver { public override void ItemUpdated(SPItemEventProperties properties) { // This if statement is to work around the sharepoint issue of this event firing twice. if (properties.AfterProperties["vti_sourcecontrolcheckedoutby"] == null &amp;&amp; properties.BeforeProperties["vti_sourcecontrolcheckedoutby"] != null) { byte[] workSheetByteArray = properties.ListItem.File.OpenBinary(); Stream stream = new MemoryStream(workSheetByteArray); Package spreadsheetPackage = Package.Open(stream, FileMode.Open, FileAccess.ReadWrite); SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(spreadsheetPackage); SharedStringTablePart shareStringTablePart = spreadsheetDocument.WorkbookPart.SharedStringTablePart; Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets; try { foreach (Sheet sheet in sheets) { var worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id.Value); IEnumerable&lt;Row&gt; rows = worksheetPart.Worksheet.GetFirstChild&lt;SheetData&gt;().Elements&lt;Row&gt;(); if (rows.Count() &gt; 0) { int rowNumber = 0; foreach (Row row in rows) { IEnumerable&lt;Cell&gt; cells = row.Elements&lt;Cell&gt;(); Cell title = null; Cell description = null; title = cells.ToArray()[0]; description = cells.ToArray()[1]; // This is the code used to extract cells from excel that are NOT inline (Inline cells are decimal and dates - although dates are stored as int) int index = int.Parse(title.CellValue.Text); string titleString = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements&lt;SharedStringItem&gt;().ElementAt(index).InnerText; index = int.Parse(description.CellValue.Text); string descriptionString = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements&lt;SharedStringItem&gt;().ElementAt(index).InnerText; //Insert into your sharepoint list here! } } } } } } } </code></pre> <p>I recommend putting this code into an event receiver on the document library (as seen above). </p>
 

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