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