Beispiel #1
0
        public byte[] GenerateExcel(SLExcelData data)
        {
            var stream   = new MemoryStream();
            var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);

            var workbookpart = document.AddWorkbookPart();

            workbookpart.Workbook = new Workbook();
            var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
            var sheetData     = new SheetData();

            worksheetPart.Worksheet = new Worksheet(sheetData);

            var sheets = document.WorkbookPart.Workbook.
                         AppendChild <Sheets>(new Sheets());

            var sheet = new Sheet()
            {
                Id      = document.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name    = data.SheetName ?? "Sheet 1"
            };

            sheets.AppendChild(sheet);

            // Add header
            UInt32 rowIdex = 0;
            var    row     = new Row {
                RowIndex = ++rowIdex
            };

            sheetData.AppendChild(row);
            var cellIdex = 0;

            foreach (var header in data.Headers)
            {
                row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
            }
            if (data.Headers.Count > 0)
            {
                // Add the column configuration if available
                if (data.ColumnConfigurations != null)
                {
                    var columns = (Columns)data.ColumnConfigurations.Clone();
                    worksheetPart.Worksheet
                    .InsertAfter(columns, worksheetPart.Worksheet.SheetFormatProperties);
                }
            }

            // Add sheet data
            foreach (var rowData in data.DataRows)
            {
                cellIdex = 0;
                row      = new Row {
                    RowIndex = ++rowIdex
                };
                sheetData.AppendChild(row);
                foreach (var callData in rowData)
                {
                    var cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, callData ?? string.Empty);
                    row.AppendChild(cell);
                }
            }

            workbookpart.Workbook.Save();
            document.Close();

            return(stream.ToArray());
        }
Beispiel #2
0
        public SLExcelData ReadExcel(FileInfo file)
        {
            var data = new SLExcelData();


            var stream = new FileStream(file.FullName, FileMode.Open, FileAccess.Read);

            // Open the excel document
            WorkbookPart workbookPart; List <Row> rows;

            try
            {
                var document = SpreadsheetDocument.Open(stream, false);
                workbookPart = document.WorkbookPart;

                var sheets = workbookPart.Workbook.Descendants <Sheet>();
                var sheet  = sheets.First();
                data.SheetName = sheet.Name;

                var workSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
                var columns   = workSheet.Descendants <Columns>().FirstOrDefault();
                data.ColumnConfigurations = columns;

                var sheetData = workSheet.Elements <SheetData>().First();
                rows = sheetData.Elements <Row>().ToList();
            }
            catch (Exception e)
            {
                data.Status.Message = "Unable to open the file";
                return(data);
            }

            // Read the header
            if (rows.Count > 0)
            {
                var row            = rows[0];
                var cellEnumerator = GetExcelCellEnumerator(row);
                while (cellEnumerator.MoveNext())
                {
                    var cell = cellEnumerator.Current;
                    var text = ReadExcelCell(cell, workbookPart).Trim();
                    data.Headers.Add(text);
                }
            }

            // Read the sheet data
            if (rows.Count > 1)
            {
                for (var i = 1; i < rows.Count; i++)
                {
                    var dataRow = new List <string>();
                    data.DataRows.Add(dataRow);
                    var row            = rows[i];
                    var cellEnumerator = GetExcelCellEnumerator(row);
                    while (cellEnumerator.MoveNext())
                    {
                        var cell = cellEnumerator.Current;
                        var text = ReadExcelCell(cell, workbookPart).Trim();
                        dataRow.Add(text);
                    }
                }
            }

            return(data);
        }