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."); } }