public void AppendErrorRows(string sheetName, IWorksheetWriter worksheetWriter)
{
if (worksheetWriter == null)
{
throw new ArgumentNullException("worksheetWriter");
}
if (FileContentStream == null)
{
throw new InvalidOperationException(string.Format("Cannot {0} prior to initializing from a template.", MethodBase.GetCurrentMethod().Name));
}
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(FileContentStream, true))
{
//Access the main Workbook part, which contains data
WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
Sheet ss = ExcelUtility.FindSheet(sheetName, workbookPart);
if (ss == null)
{
throw new InvalidOperationException("Cannot find sheet named '" + sheetName + "' in workbook.");
}
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
if (worksheetPart != null)
{
worksheetWriter.CreateErrorRows(worksheetPart);
worksheetPart.Worksheet.Save();
}
}
}
public static DataTable ExtractExcelSheetValues(Stream excelStream, string sheetName)
{
DataTable dt = new DataTable();
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(excelStream, true))
{
//Access the main Workbook part, which contains data
WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
Sheet ss = ExcelUtility.FindSheet(sheetName, workbookPart);
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
if (worksheetPart != null)
{
Row lastRow = worksheetPart.Worksheet.Descendants <Row>().LastOrDefault();
Row headerRow = worksheetPart.Worksheet.Descendants <Row>().Skip(1).First();
if (headerRow != null)
{
foreach (Cell c in headerRow.ChildElements)
{
string value = GetValue(c, stringTablePart);
dt.Columns.Add(value);
}
}
if (lastRow != null)
{
for (int i = 1; i <= lastRow.RowIndex; i++)
{
DataRow dr = dt.NewRow();
bool empty = true;
Row row = worksheetPart.Worksheet.Descendants <Row>().Where(r => i == r.RowIndex).FirstOrDefault();
int j = 0;
if (row != null)
{
foreach (Cell c in row.ChildElements)
{
//Get cell value
string value = GetValue(c, stringTablePart);
if (!string.IsNullOrEmpty(value) && value != " ")
{
empty = false;
}
var col = ConvertColumnLettering(c.CellReference.Value[0]);
if (col >= 0)
{
dr[col] = value;
}
j++;
if (j == dt.Columns.Count)
{
break;
}
}
if (empty)
{
break;
}
dt.Rows.Add(dr);
}
}
}
}
}
return(dt);
}