private SheetData GenerateSheetDataCells(ExcelData excelData)
{
SheetData sheetData = new SheetData();
var allDataRows = excelData.AllRows;
int fromRowID = 1;
char fromColumnID = 'A';
int rowID = fromRowID;
foreach (var dataRow in allDataRows)
{
Row row = new Row()
{
RowIndex = (uint)rowID
};
char columnID = fromColumnID;
foreach (var cellData in dataRow)
{
string cellAddress = columnID + rowID.ToString();
Cell cell = SetCell(cellData.CellValue, GetCellType(cellData.CellDataType), cellAddress);
row.Append(cell);
columnID++;
}
sheetData.Append(row);
rowID++;
}
return(sheetData);
}
public ExcelData Read(Stream excelStream)
{
ExcelData excelData = new ExcelData();
if (excelStream != null)
{
using (var excelDoc = SpreadsheetDocument.Open(excelStream, false))
{
var rows = excelDoc.WorkbookPart.WorksheetParts.First()
.Worksheet.GetFirstChild <SheetData>().Descendants <Row>();
var cells = excelDoc.WorkbookPart.WorksheetParts.First()
.Worksheet.GetFirstChild <SheetData>().Descendants <Cell>();
int rowsCount = rows.Count();
int cellsCount = cells.Count();
if (rowsCount > 0 && cellsCount > 0)
{
int rowSize = 'A' + (cellsCount / rowsCount);
int fromRowID = 1;
char fromColumnID = 'A';
for (int rowID = fromRowID; rowID <= rowsCount; rowID++)
{
ExcelRowData rowData = new ExcelRowData();
for (char columnID = fromColumnID; columnID < rowSize; columnID++)
{
string cellAddress = columnID + rowID.ToString();
ExcelCellData cellData = GetExcelCellData(excelDoc, cellAddress);
if (cellData != null)
{
rowData.DataRow.Add(cellData);
}
}
if (rowID == fromRowID)
{
excelData.HeadingRow = rowData;
}
else
{
excelData.DataRows.Add(rowData);
}
}
}
else
{
throw new UtilsException("Couldn't read the Excel file.");
}
}
}
else
{
throw new UtilsException("No data was provided for reading.");
}
return(excelData);
}
public byte[] Write(ExcelData excelData)
{
if (excelData != null)
{
using (var templateStream = new MemoryStream())
{
using (var excelDoc = SpreadsheetDocument.Create(templateStream, SpreadsheetDocumentType.Workbook, true))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = excelDoc.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
//Insert generated cells
SheetData sheetData = GenerateSheetDataCells(excelData);
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add minimal Stylesheet to format DateTime cells
var stylesPart = excelDoc.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = SetStyleSheet();
// Add Sheets to the Workbook.
Sheets sheets = excelDoc.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = excelDoc.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Schedules"
};
sheets.Append(sheet);
}
templateStream.Position = 0;
var result = templateStream.ToArray();
templateStream.Flush();
return(result);
}
}
else
{
throw new UtilsException("No data was provided.");
}
}