public static MemoryStream CalcSpreadsheetDocument(MemoryStream ms, bool DeleteFormula, string OutFile) { bool pg = (progressBar != null); SpreadsheetDocument RecultDoc = SpreadsheetDocument.Open(ms, true); if (DeleteFormula) { RecultDoc.WorkbookPart.DeletePart(RecultDoc.WorkbookPart.CalculationChainPart); } foreach (Sheet curSheet in RecultDoc.WorkbookPart.Workbook.Sheets) { WorksheetPart RecultWorkSheetPart = SpreadsheetReader.GetWorksheetPartByName(RecultDoc, curSheet.Name.Value); IEnumerable <Row> ListRowInRange = RecultWorkSheetPart.Worksheet.Elements <SheetData>().First().Elements <Row>().Where(r => r.Elements <Cell>().Where(c => c.CellFormula != null).Count() > 0); if (pg) { progressBar.Maximum = ListRowInRange.Count(); } int value = 0; foreach (Row Row in ListRowInRange) { IEnumerable <Cell> fCell = Row.Elements <Cell>().Where(c => c.CellFormula != null).Where(c => c.CellFormula.Text.Trim() != ""); foreach (Cell c in fCell) { var dd = calcFormule(c, RecultWorkSheetPart, RecultDoc, DeleteFormula); } if (pg) { progressBar.Value = ++value; } } } RecultDoc.Close(); if (OutFile != null) { if (OutFile.Length > 0) { SpreadsheetWriter.StreamToFile(OutFile, ms); } } return(ms); }
//Создание отчета public MemoryStream GenerateReport(DataSet dataSet, string Temleyt, string OutFile) { MemoryStream ms = new MemoryStream(); ms = SpreadsheetReader.Copy(Temleyt); SpreadsheetDocument RecultDoc = SpreadsheetDocument.Open(ms, true); MemoryStream tp = new MemoryStream(); tp = SpreadsheetReader.Copy(Temleyt); SpreadsheetDocument TemleytDoc = SpreadsheetDocument.Open(tp, true); foreach (Sheet curSheet in TemleytDoc.WorkbookPart.Workbook.Sheets) { UInt32 Offset = 0; // Получаем имена областей DefinedNames принадлежащих к текущему Sheet List <DefinedName> dn = RecultDoc.WorkbookPart.Workbook.Elements <DefinedNames>().First().Elements <DefinedName>() .Where(d => d.Text.IndexOf("#REF!") < 0 && curSheet.Name.Value == d.Text.Substring(0, d.Text.IndexOf("!")).TrimEnd('\'').TrimStart('\'')).ToList(); foreach (DefinedName definedName in dn) { DataTable dataTable = GetTableByName(dataSet, definedName.Name); if (dataTable != null) { Offset = SetValueFromDataTable(dataTable, RecultDoc, TemleytDoc, Offset); } } } SetValueWithoutRange(dataSet, RecultDoc); ClearSpreadsheetDocument(RecultDoc); TemleytDoc.Close(); RecultDoc.Close(); if (OutFile != null) { if (OutFile.Length > 0) { SpreadsheetWriter.StreamToFile(OutFile, ms); } } return(ms); }