public List <List <string> > SheetRange(string sheetName, string firstCellName, string lastCellName, bool IgnoreEmptyRows = true) { uint firstRowNum = ColumnComparer.GetRowIndex(firstCellName); uint lastRowNum = ColumnComparer.GetRowIndex(lastCellName); string firstColumn = ColumnComparer.GetColumnName(firstCellName); string lastColumn = ColumnComparer.GetColumnName(lastCellName); return(SheetRange(sheetName, firstRowNum, lastRowNum, firstColumn, lastColumn, IgnoreEmptyRows)); }
public List <List <string> > Sheet(string sheetName, bool IgnoreEmptyRows) { GetSheetPart(sheetName, out SharedStringTablePart stringTable, out WorksheetPart wsPart); W worksheet = wsPart.Worksheet; IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Elements <Row>(); uint firstRowNum = rows.Select(r => r.RowIndex).Min(); uint lastRowNum = rows.Select(r => r.RowIndex).Max(); var columns = rows.SelectMany(r => r.Elements <Cell>()).Distinct().OrderBy(c => ColumnComparer.GetColumnName(c.CellReference), new ColumnComparer()).Select(c => ColumnComparer.GetColumnName(c.CellReference)); string firstColumn = columns.First(); string lastColumn = columns.Last(); return(SheetRange(sheetName, firstRowNum, lastRowNum, firstColumn, lastColumn, IgnoreEmptyRows, stringTable, wsPart)); }
public void DumpSheetRange <T>(T output, string filename, string sheetName, string firstCellName, string lastCellName) where T : TextWriter { // Create instance of OpenSettings OpenSettings openSettings = new OpenSettings { // Add the MarkupCompatibilityProcessSettings MarkupCompatibilityProcessSettings = new MarkupCompatibilityProcessSettings( MarkupCompatibilityProcessMode.ProcessAllParts, FileFormatVersions.Office2013) }; // Open the document with OpenSettings using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(filename, false, openSettings)) { var wbPart = excelDocument.WorkbookPart; Sheets sheets = wbPart.Workbook.Sheets; foreach (E sheetElem in sheets) { foreach (A attr in sheetElem.GetAttributes()) { output.Write("{0}: {1}\t", attr.LocalName, attr.Value); } output.WriteLine(); } S sheet = wbPart.Workbook.Descendants <S>().Where(s => s.Name == sheetName).First(); // Throw an exception if there is no sheet. if (sheet == null) { throw new ArgumentException("sheetName is invalid for this workbook"); } // Retrieve a reference to the worksheet part. WorksheetPart wsPart = (WorksheetPart)wbPart.GetPartById(sheet.Id); W worksheet = wsPart.Worksheet; var stringTable = wbPart.GetPartsOfType <SharedStringTablePart>() .FirstOrDefault(); uint firstRowNum = ColumnComparer.GetRowIndex(firstCellName); uint lastRowNum = ColumnComparer.GetRowIndex(lastCellName); string firstColumn = ColumnComparer.GetColumnName(firstCellName); string lastColumn = ColumnComparer.GetColumnName(lastCellName); string headers = firstColumn; while (ColumnComparer.CompareColumn(headers, lastColumn) <= 0) { output.Write("{0}\t", headers); headers = ColumnComparer.NextColumn(headers); } output.WriteLine(); //// Iterate through the cells within the range and do whatever. foreach (int row in Enumerable.Range((int)firstRowNum, (int)lastRowNum)) { string col = firstColumn; while (ColumnComparer.IsColumnInRange(firstColumn, lastColumn, col)) { var cellRef = String.Format("{0}{1}", col, row); var cell = wsPart.Worksheet.Descendants <Cell>().Where(c => c.CellReference == cellRef).FirstOrDefault(); output.Write("{0}\t", DecodedCell(cell, stringTable)); col = ColumnComparer.NextColumn(col); } output.WriteLine(); } } }