public ExlsSheetData ReadSpreadSheetDoc(Stream stream, int sheetIndex, int startRowIndex) { ExlsSheetData result = new ExlsSheetData(); using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; var theSheet = workbookPart.Workbook.Sheets.ElementAt(sheetIndex) as Sheet; var workSheetPart = workbookPart.GetPartById(theSheet.Id) as WorksheetPart; string cellValue = string.Empty; uint rowIndex = 1; var lstDataRows = new List <SheetRowItem>(); var rows = workSheetPart.Worksheet.Descendants <Row>(); if (rows.Count() >= startRowIndex) { foreach (Row r in rows.Skip(startRowIndex - 1)) { var lstDataItems = new List <SheetCellItem>(); uint colIndex = 0; foreach (Cell theCell in r.Elements <Cell>()) { if (theCell.InnerText.Length > 0) { cellValue = GetCellValue(workbookPart, theCell); } else { cellValue = string.Empty; } var realIndex = CellReferenceToIndex(theCell); if (colIndex < realIndex) { while (colIndex < realIndex) { lstDataItems.Add(new SheetCellItem { ColIndex = ++colIndex, Data = string.Empty, DataType = DataTypes.String }); } } //empty cell was skpipped lstDataItems.Add(new SheetCellItem { ColIndex = ++colIndex, Data = cellValue, DataType = DataTypes.String }); } lstDataRows.Add(new SheetRowItem(lstDataItems, rowIndex)); rowIndex++; } } result.SheetRows = lstDataRows; } return(result); }
/// <summary> /// 生成多个sheet的Excel文件 /// </summary> /// <param name="dataItems"></param> /// <param name="shNames">sheet name</param> /// <param name="filename">生成的excel文件名(包含路径)</param> /// <param name="doMerge">true: 存在合并单元格/false: 不存在合并单元格</param> /// <param name="customFormat">true: 有自定义样式/false: 没有自定义样式</param> public void GenerateXlsxFile(List <BaseSheetData> exlsSheetData, string[] shNames, string filename, bool doMerge = false, bool customFormat = false) { using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = spreadsheetDoc.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.AppendChild(new Sheets()); SharedStringTablePart tbpart = null; var stylePart = workbookPart.AddNewPart <WorkbookStylesPart>(); List <SheetCellItem> allCells = new List <SheetCellItem>(); for (uint i = 0; i < exlsSheetData.Count; i++) { ExlsSheetData xlsxSheetData = exlsSheetData[(int)i] as ExlsSheetData; if (xlsxSheetData != null) { allCells.AddRange(xlsxSheetData.AllCells); } } if (allCells.Any()) { if (customFormat) { stylePart.Stylesheet = GenerateStylesheet(allCells); } else { stylePart.Stylesheet = DefaultStylesheet(); //GenerateDefaultStylesheet(allCells); } stylePart.Stylesheet.Save(); } if (allCells.Any(x => x.DataType == DataTypes.SharedString)) { tbpart = workbookPart.AddNewPart <SharedStringTablePart>(); } for (uint i = 0; i < exlsSheetData.Count; i++) { WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); ExlsSheetData xlsxSheetData = exlsSheetData[(int)i] as ExlsSheetData; if (xlsxSheetData != null) { worksheetPart.Worksheet = new Worksheet(); if (customFormat) { var cols = GenerateColumns(xlsxSheetData.AllCells); if (cols != null) { worksheetPart.Worksheet.Append(cols); } } else { foreach (var c in xlsxSheetData.AllCells) { c.FormatIndex = c.RowIndex.Equals(1) ? 1u : 2u; } } worksheetPart.Worksheet.Append(CreateSheetData(xlsxSheetData.SheetRows, tbpart)); if (doMerge) { DoMerge(xlsxSheetData.AllCells, worksheetPart.Worksheet); } } UInt32Value id = UInt32Value.FromUInt32(i + 1); Sheet sheet = new Sheet { Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = id, Name = shNames[i] }; sheets.AppendChild(sheet); } workbookPart.Workbook.Save(); } }