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();
}
}
}
