public override void addDataRows(SheetData sheetData, GroupReport report) { // бежим по строкам foreach (Instance instance in report.Instances) { var row = new Row(); Instance.Property property; foreach (TemplateField field in report.Template.Fields) { if (field.IsVisible) { property = instance.OwnProperties.First(o => o.Attribute.ID.ToString() == field.Attribute.ID.ToString()); var cell = new Cell(); var cellValue = new CellValue(); ApplyStyle(property, field, ref cell, ref cellValue); cell.Append(cellValue); row.AppendChild(cell); } } sheetData.AppendChild(row); } }
public void Create(Stream output, GroupReport report) { ExcelTemplate template; if (report.Template is ExcelTemplate) { template = report.Template as ExcelTemplate; } else { template = new ExcelTemplate(report.Template); } sheetName = template.Sheet; var stream = new MemoryStream(); #if Viktor CreateFromEmptyTemplate(stream, report); #else if (report.Template.Content.Count() > 0) { CreateFromTemplateWithBody(stream, report); } else { CreateFromEmptyTemplate(stream, report); } #endif var array = stream.ToArray(); output.Write(array, 0, array.Length); }
private void GenWorksheetPart(WorksheetPart worksheetPart, GroupReport report) { if (worksheetPart == null) { throw new ArgumentNullException("worksheetPart", string.Format("Параметр {0} имеет значение null.", "worksheetPart")); } if (report == null) { throw new ArgumentNullException("template", string.Format("Параметр {0} имеет значение null.", "template")); } Worksheet workSheet = new Worksheet(); SheetData sheetData = new SheetData(); //записать в Excel названия полей Row headerRow = new Row(); addHeaderRow(headerRow, report); sheetData.AppendChild(headerRow); //записать в Excel строки с данными addDataRows(sheetData, report); workSheet.Append(sheetData); worksheetPart.Worksheet = workSheet; }
public ActionResult ReportGroup([FromBody] ReportCommand model) { if (_context.Users.Find(model.SenderId) == null) { return(NotFound("Could not find sender user with this id")); } if (_context.Groups.Find(model.ReportObjectId) == null) { return(NotFound("Could not find group with this id")); } var groupReport = new GroupReport(); groupReport.Reason = model.Reason; groupReport.Explanation = model.Explanation; groupReport.DateTime = DateTime.Now.AddHours(2); groupReport.GroupId = model.ReportObjectId; groupReport.UserSenderId = model.SenderId; groupReport.Approved = false; try { _context.GroupReports.Add(groupReport); _context.SaveChanges(); return(Ok()); } catch (Exception) { return(BadRequest("An error occured while trying to add group report.")); } }
protected override void addHeaderRow(Row headerRow, GroupReport report) { Fields = new List <Reporting.Attribute>(report.Template.Fields.OrderBy(o => o.Order).Select(x => x.Attribute)); //Добавили Атрибуты из Template //Формируем основной sheet или же sheet's with Constraint's var b = this.sheetName == report.Template.Name; for (int i = 0; i < Fields.Count; i++) { headerRow.AppendChild(BuildHeaderCell(Fields[i].Name)); } }
public ActionResult Index() { List <GroupReport> report = new List <GroupReport>(); try { List <int> fromCities = (from plan in db.Plans join fromCt in db.Cities on plan.CityFromId equals fromCt.Id select fromCt.Id).Distinct().ToList(); foreach (var city in fromCities) { GroupReport reportRow = new GroupReport(); string cityName = db.Cities.Where(c => c.Id == city).First().CityName; reportRow.DepartureCity = cityName; var plans = from plan in db.Plans where plan.CityFromId == city select plan; int totalActualTransportations = 0; int totalPlannedTransportations = 0; List <SubReport> subReport = new List <SubReport>(); foreach (var pln in plans) { SubReport subRow = new SubReport(); string arrivalCity = db.Cities.Where(c => c.Id == pln.CityToId).First().CityName; subRow.ArrivalCity = arrivalCity; int actualTransportations = (from tr in db.Transportations where tr.PlanId == pln.Id select tr).Count(); subRow.ActualTransportations = actualTransportations; totalActualTransportations += actualTransportations; subRow.PlannedTransportations = pln.PlannedTransportations; totalPlannedTransportations += pln.PlannedTransportations; subReport.Add(subRow); } reportRow.PlannedTransportations = totalPlannedTransportations; reportRow.ActualTransportations = totalActualTransportations; reportRow.Row = subReport; report.Add(reportRow); } } catch (Exception ex) { Logger.Log.Error(ex); } ViewBag.Message = "Group report page."; return(View(report)); }
protected void DownloadButton_Click(object sender, EventArgs e) { if (Frame.TemplateList.SelectedDataKey["code"].ToString() == "InputExcelBased") { using (var stream = new MemoryStream()) { var excelTableBased = new TableBasedTemplateToExcel(); // excelTableBased.template = Storage.Select<Teleform.Reporting.Template>(Frame.TemplateList.SelectedDataKey["objID"]); excelTableBased.Entitys = this.GetSchema().Entities.ToList(); var template = Storage.Select <Teleform.Reporting.Template>(Frame.TemplateList.SelectedDataKey["objID"]); var report = GroupReport.Make(template, new DataTable()); excelTableBased.Create(stream, report); Response.Clear(); Response.ContentType = "text/html"; Response.AddHeader("content-disposition", string.Format("attachment;fileName={0}.xlsx", template.Name)); Response.ContentEncoding = Encoding.UTF8; Response.BinaryWrite(stream.ToArray()); Response.Flush(); Response.End(); } } else { var adapter = new SqlDataAdapter( string.Concat( @"SELECT [A].[fileName], [A].[body], [B].[mime], [B].[extension] FROM [model].[R$Template] [A] JOIN [MimeType] [B] ON [A].[mimeTypeID] = [B].[objID] WHERE [A].[objID] = ", Frame.TemplateList.SelectedValue), Global.ConnectionString); var table = new DataTable(); adapter.Fill(table); if (table.Rows.Count > 0) { var data = table.Rows[0]; Response.Clear(); Response.ContentType = data["mime"].ToString(); Response.AddHeader("content-disposition", string.Concat("attachment;fileName=", data["fileName"], data["extension"])); Response.BinaryWrite((byte[])data["body"]); Response.End(); } } }
private void addHeaderRow(Row headerRow, GroupReport gRreport) { foreach (TemplateField headerField in gRreport.Template.Fields.OrderBy(o => o.Order)) { Cell cell = new Cell() { StyleIndex = (UInt32Value)0U }; cell.DataType = CellValues.String; cell.CellValue = new CellValue(headerField.Name); headerRow.AppendChild(cell); } }
private void addHeaderRow(Row headerRow, GroupReport report) { foreach (var head in HeaderList) { Cell cell = new Cell() { StyleIndex = (UInt32Value)0U }; cell.DataType = CellValues.String; cell.CellValue = new CellValue(head); headerRow.AppendChild(cell); } }
private void CreateFromEmptyTemplate(Stream output, GroupReport report) { using (SpreadsheetDocument document = SpreadsheetDocument.Create(output, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); GenWorkbookPart(sheetName, workbookPart); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1"); GenWorksheetPart(worksheetPart, report); WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId2"); GenWorkbookStylesPart(workbookStylesPart); } }
private void CreateFromTemplateWithBody(Stream output, GroupReport report) { var content = report.Template.Content; output.Write(content, 0, content.Length); SpreadsheetDocument document = SpreadsheetDocument.Open(output, true); AddNewSheet(sheetName, document, output, report); WorkbookStylesPart workbookStylesPart = document.WorkbookPart.WorkbookStylesPart; GenWorkbookStylesPart(workbookStylesPart); document.Close(); }
private void GetGroupReportByConstraint(out GroupReport groupReport, Constraint constraint) { var nameTable = constraint.RefTblName; var fields = Entitys.FirstOrDefault(x => x.SystemName == nameTable) .Attributes.Where(x => x.AppType == AppType.title || x.FPath == "objID") .Select(x => new TemplateField(x)); var tmpTemplate = new Template(constraint.Alias, Entitys.FirstOrDefault(x => x.SystemName == nameTable), "TableBased", null, fields); var dt = Global.GetDataTable(string.Concat("EXEC [report].[getBObjectdata] '", nameTable, "', @flTitle=1, @flHeader=0")); //Делаем для того , что бы медот GroupReport.Make() вернул нам объекты вместе с objID, если этого не сдалать он обрежет колонку objID dt.Columns["objID"].ColumnName = tmpTemplate.Fields.FirstOrDefault(x => x.Attribute.FPath == "objID").Attribute.ID.ToString(); groupReport = GroupReport.Make(tmpTemplate, dt); }
protected override void CreateFromEmptyTemplate(Stream output, GroupReport report) { sheetName = report.Template.Name; using (SpreadsheetDocument document = SpreadsheetDocument.Create(output, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); GenWorkbookPart(sheetName, workbookPart); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId0"); GenWorksheetPart(worksheetPart, report); WorksheetPart worksheetPart2 = workbookPart.AddNewPart <WorksheetPart>("rId1"); GenWorksheetPartAddConnectionData(worksheetPart2, report); WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId15"); GenWorkbookStylesPart(workbookStylesPart); } }
private void CreateFromTemplateWithBody(Stream output, GroupReport report) { var content = report.Template.Content; output.Write(content, 0, content.Length); SpreadsheetDocument document = SpreadsheetDocument.Open(output, true); #if Force_Formula_Recalculations // Необходимо для принудительного пересчёта формул при загрузке. document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; document.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; #endif AddNewSheet(sheetName, document, output, report); WorkbookStylesPart workbookStylesPart = document.WorkbookPart.WorkbookStylesPart; GenWorkbookStylesPart(workbookStylesPart); document.Close(); }
public IList <GroupReport> GetMonthlyReportForSA(DateTime startdate, DateTime enddate, IEnumerable <User> sa_list) { IList <GroupReport> group_report = new List <GroupReport>(); foreach (var user in sa_list) { GroupReport newRecord = new GroupReport(); newRecord.firstName = user.FirstName; newRecord.lastName = user.LastName; newRecord.TGAPM = GetTGAPM(startdate, enddate, user.UserId); newRecord.GAPW = (double)newRecord.TGAPM / (((double)enddate.Day) / (double)7); newRecord.AppointmentPercentage = (double)newRecord.TGAPM / (double)GetTotalGoodAppointments(startdate, enddate); newRecord.TCPM = GetTCPM(startdate, enddate, user.UserId); newRecord.SACR = (double)newRecord.TCPM / (double)newRecord.TGAPM; group_report.Add(newRecord); } return(group_report); }
public override void addDataRows(SheetData sheetData, GroupReport report) { // бежим по строкам foreach (Instance instance in report.Instances) { var row = new Row(); foreach (Instance.Property property in instance.OwnProperties) { var field = report.Template.Fields.FirstOrDefault(x => x.Attribute.FPath == property.Attribute.FPath); var cell = new Cell(); var cellValue = new CellValue(); ApplyStyle(property, field, ref cell, ref cellValue); cell.Append(cellValue); row.AppendChild(cell); } sheetData.AppendChild(row); } }
//самый главный метод, с него все начинается public void Create(Stream output, GroupReport report) { ExcelTemplate template; if (report.Template is ExcelTemplate) { template = report.Template as ExcelTemplate; } else { template = new ExcelTemplate(report.Template); } sheetName = template.Sheet; var stream = new MemoryStream(); CreateFromEmptyTemplate(stream, report); var array = stream.ToArray(); output.Write(array, 0, array.Length); }
public static async Task <string> UploadBlob([ActivityTrigger] GroupReport groupReport, ILogger log) { log.LogInformation("Trying to upload report {reportid} of group {groupid}", groupReport.ReportId, groupReport.GroupId); Stream reportStream; string reportName; try { // Create a Power BI Client object. It will be used to call Power BI APIs. using (PowerBIClient powerBIClient = new PowerBIClient(PowerBIApiUrl, TokenCredentials)) { log.LogInformation($"powerBI client created"); Report report = await powerBIClient.Reports.GetReportAsync(groupReport.GroupId, groupReport.ReportId); reportName = DateTime.Now.ToString("yyyyMMdd_HH") + "h/" + groupReport.GroupName + "/" + report.Name + ".pbix"; reportStream = await powerBIClient.Reports.ExportReportAsync(groupReport.GroupId, groupReport.ReportId); } // Retrieve destination blob reference CloudBlockBlob pbixBlob = BlobStorageHelper.GetBlob( BlobStorageCS , BlobStorageContainerName , reportName); log.LogInformation("Blob reference retrieved for report {reportname}", reportName); await pbixBlob.UploadFromStreamAsync(reportStream); } catch (Exception e) { log.LogError(new EventId(666), e, "Error while trying to upload blob: {message}", e.Message); return(e.Message); } return($"{reportName} successfully created!"); }
public void Create(Stream output, GroupReport report) { if (output == null) { throw new ArgumentNullException("output", string.Format("Параметр {0} имеет значение null.", "output")); } if (report == null) { throw new ArgumentNullException("template", string.Format("Параметр {0} имеет значение null.", "template")); } // Параметры хранимой процедуры. var fullPath = Path.Combine(TemporaryDirectory, Guid.NewGuid().ToString() + this.DefaultExtension); #if f var sheet = (report as ExcelReport).Sheet; #endif var templateID = report.Template.ID; // Создание или дополнение отчета зависит от наличия содержимого в шаблоне. var content = report.Template.Content; if (content != null) { try { File.WriteAllBytes(fullPath, content); } catch (IOException ex) { #warning TODO сообщения по I/O - нужны или нет? throw new InvalidOperationException(String.Format("Возникла общая ошибка ввода-вывода:\n{0}.", ex.Message)); } } #if f // Выполняем хранимую процедуру. GenerateExcelReport(templateID, sheet, fullPath); #endif // Результат - пишем в поток. try { using (Stream fileStream = File.OpenRead(fullPath)) { fileStream.CopyTo(output); } } catch (FileNotFoundException ex) { throw new InvalidOperationException(String.Format("Не найден файл {0}.", fullPath)); } catch (NotSupportedException ex) { throw new InvalidOperationException(String.Format("Поток назначения не поддерживает запись:\n{0}.", ex.Message)); } catch (ObjectDisposedException ex) { throw new InvalidOperationException(String.Format("Поток назначения был преждевременно закрыт:\n{0}.", ex.Message)); } catch (IOException ex) { throw new InvalidOperationException(String.Format("Возникла общая ошибка ввода-вывода:\n{0}.", ex.Message)); } }
public override void addDataRows(SheetData sheetData, GroupReport report) { }
private void addDataRows(SheetData sheetData, GroupReport report) { // бежим по строкам foreach (Instance instance in report.Instances) { var row = new Row(); //Instance.Property property; foreach (Instance.Property property in instance.BaseProperties) // TemplateField field in report.Template.Fields) { var field = report.Template.Fields.FirstOrDefault(x => x.Attribute.FPath == property.Attribute.FPath); //property = instance.BaseProperties.First(o => o.Attribute.ID.ToString() == field.Attribute.ID.ToString()); var cell = new Cell(); var cellValue = new CellValue(); if (field.Format.FormatString == "{0:T}") //"thousand") { if (property.Value != DBNull.Value) { decimal o = Convert.ToDecimal(property.Value); o = o / 1000; var a = o.ToString().Replace(',', '.'); cell.StyleIndex = (UInt32Value)2U; cellValue.Text = a; } else { cellValue.Text = string.Empty; } } else if (field.Attribute.Type.Name.Contains("date")) { switch (field.Format.FormatString) { case "{0:dd MMMMM yyyy года}": // Формат № 1 для дат FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy" cell.StyleIndex = (UInt32Value)1U; cell.DataType = CellValues.Date; cellValue.Text = string.Format("{0:yyyy-MM-dd}", property.Value); break; default: //формат № 3 для дат * 14.03.2015 cell.StyleIndex = (UInt32Value)3U; cell.DataType = CellValues.Date; cellValue.Text = string.Format("{0:yyyy-MM-dd}", property.Value); break; } } else if (field.Attribute.Type.Name.Contains("money")) { switch (field.Format.FormatString) { case "{0:### ### ### ### ### p}.": //формат № 2 для денег FormatCode = "#,##0\"р.\"" cell.StyleIndex = (UInt32Value)7U; cell.DataType = CellValues.Number; cellValue.Text = string.Format(CultureInfo.InvariantCulture, "{0}", property.Value); break; case "{0}": //для общего формата денег cell.StyleIndex = (UInt32Value)8U; //cell.DataType = CellValues.Number; if (property.Value != DBNull.Value) { var z = Convert.ToInt64(property.Value); cellValue.Text = string.Format(CultureInfo.InvariantCulture, "{0}", z); } else { cellValue.Text = string.Empty; } break; default: // Формат № 0 по умолчанию общий cell.StyleIndex = (UInt32Value)0U; cell.DataType = CellValues.String; cellValue.Text = string.Format(field.Format.Provider, field.Format.FormatString, property.Value); break; } } else { //ловим на int что бы потом преобразовать в Excel в число if (property.Value is int) { int number = Convert.ToInt32(property.Value); cell.DataType = CellValues.Number; cell.StyleIndex = (UInt32Value)5U; cellValue.Text = number.ToString(); } else { // Формат № 0 по умолчанию общий cell.StyleIndex = (UInt32Value)0U; cell.DataType = CellValues.String; cellValue.Text = string.Format(field.Format.Provider, field.Format.FormatString, property.Value); } } cell.Append(cellValue); row.AppendChild(cell); } sheetData.AppendChild(row); } }
// public abstract void addDataRows(SheetData sheetData, GroupReport report);
/// <summary> /// Создаёт CSV-отчёт в указанном потоке на основе предоставленных данных. /// </summary> /// <param name="stream">Поток, в котором создаётся CSV-отчёт.</param> /// <param name="report">Данные для подготовки группового отчёта.</param> /// <exception cref="System.ArgumentNullException">Параметр stream или report равен null.</exception> public void Create(Stream stream, GroupReport report) { if (report == null) { throw new ArgumentNullException("report", "Параметр report имеет значение null."); } if (stream == null) { throw new ArgumentNullException("stream", "Параметр stream имеет значение null."); } var writer = new StreamWriter(stream, UTF8Encoding.Default); foreach (var field in report.Template.Fields) { writer.Write(string.Concat(field.Name, ";")); } foreach (var instance in report.Instances) { Instance.Property property; writer.WriteLine(); foreach (TemplateField field in report.Template.Fields) { property = instance.OwnProperties.First(o => o.Attribute.ID.ToString() == field.Attribute.ID.ToString()); var stringValue = property.Value.ToString(); if (string.IsNullOrWhiteSpace(stringValue)) { writer.Write(";"); } else if (property.Value is DateTime || property.Value is DateTimeOffset) { writer.Write("{0:dd.MM.yyyy};", property.Value); } else if (field.Attribute.Type.Name.Contains("money")) { switch (field.Format.FormatString) { case "{0:### ### ### ### ### p}.": writer.Write("{0:### ### ### ### ###};", property.Value); break; case "{0}": writer.Write(string.Concat(stringValue, "р.;")); break; default: writer.Write( string.Concat( string.Format(field.Format.Provider, field.Format.FormatString, property.Value), ";")); break; } } else if (stringValue.Contains(';')) { foreach (var c in "«»\"") { var s = c.ToString(); stringValue = stringValue.Replace(s, string.Concat(s, s)); } writer.Write(string.Concat("\"", stringValue, "\";")); } else { writer.Write(string.Concat(stringValue, ";")); } } } }
// создает рабочий лист Excel и запрлняет ячейкаи с данными из GroupReport private void AddNewSheet(string sheetName, SpreadsheetDocument spreadsheetDocument, Stream stream, GroupReport report) { if (spreadsheetDocument == null) { throw new ArgumentNullException("spreadsheetDocument", string.Format("Параметр {0} имеет значение null.", "spreadsheetDocument")); } if (report == null) { throw new ArgumentNullException("report", string.Format("Параметр {0} имеет значение null.", "report")); } Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets; foreach (Sheet sheet in sheets) { if (sheetName == sheet.Name) { WorksheetPart workSheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id); Worksheet workSheet = workSheetPart.Worksheet; SheetData sheetData = workSheet.GetFirstChild <SheetData>(); Row headerRow = new Row() { RowIndex = 1 }; addHeaderRow(headerRow, report); sheetData.AppendChild(headerRow); //записать в Excel строки с данными addDataRows(sheetData, report); return; } } // добавить новый worksheet. WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorksheetPart>(); SheetData newSheetData = new SheetData(); Worksheet newWorkSheet = new Worksheet(newSheetData); newWorksheetPart.Worksheet = newWorkSheet; newWorksheetPart.Worksheet.Save(); string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart); // создать уникальный ID для worksheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } Sheet newSheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(newSheet); spreadsheetDocument.WorkbookPart.Workbook.Save(); //записать в Excel заголовки полей Row newHeaderRow = new Row(); addHeaderRow(newHeaderRow, report); newSheetData.AppendChild(newHeaderRow); //записать в Excel строки с данными addDataRows(newSheetData, report); }
/// <summary> /// add connection data @data source=stend\sqlexpress_12; and Initial Catalog = PMonitor;" /// </summary> /// <param name="worksheetPart2"></param> /// <param name="report"></param> private void GenWorksheetPartAddConnectionData(WorksheetPart worksheetPart2, GroupReport report) { if (worksheetPart2 == null) { throw new ArgumentNullException("worksheetPart", "Параметр worksheetPart имеет значение null."); } if (report == null) { throw new ArgumentNullException("template", "Параметр template имеет значение null."); } Worksheet workSheet = new Worksheet(); SheetData sheetData = new SheetData(); //записать в Excel названия полей Row headerRow = new Row(); Cell headerCellDataSource = new Cell() { StyleIndex = (UInt32Value)0U }; headerCellDataSource.DataType = CellValues.String; headerCellDataSource.CellValue = new CellValue("Server name"); Cell headerCellInitialCatalog = new Cell() { StyleIndex = (UInt32Value)0U }; headerCellInitialCatalog.DataType = CellValues.String; headerCellInitialCatalog.CellValue = new CellValue("Database name"); headerRow.Append(headerCellDataSource); headerRow.Append(headerCellInitialCatalog); var sql = new System.Data.SqlClient.SqlConnection(Global.ConnectionString); Row row = new Row(); Cell cellDataSource = new Cell() { StyleIndex = (UInt32Value)0U }; cellDataSource.DataType = CellValues.String; cellDataSource.CellValue = new CellValue(sql.DataSource); Cell cellInitialCatalog = new Cell() { StyleIndex = (UInt32Value)0U }; cellInitialCatalog.DataType = CellValues.String; cellInitialCatalog.CellValue = new CellValue(sql.Database); row.Append(cellDataSource); row.Append(cellInitialCatalog); sheetData.AppendChild(headerRow); sheetData.AppendChild(row); //записать в Excel строки с данными addDataRows(sheetData, report); workSheet.Append(sheetData); worksheetPart2.Worksheet = workSheet; }
public override void addDataRows(SheetData sheetData, GroupReport report) { throw new NotImplementedException(); }
private void addDataRows(SheetData sheetData, GroupReport gRreport) { // бежим по строкам foreach (Instance instance in gRreport.Instances) { var row = new Row(); Instance.Property property; foreach (TemplateField field in gRreport.Template.Fields) { property = instance.BaseProperties.First(o => o.Attribute.ID.ToString() == field.Attribute.ID.ToString()); var cell = new Cell(); var cellValue = new CellValue(); if (field.Attribute.Type.Name.Contains("date")) { switch (field.Format.FormatString) { case "{0:dd MMMMM yyyy года}": // Формат № 1 для дат FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy" cell.StyleIndex = (UInt32Value)1U; cell.DataType = CellValues.Date; cellValue.Text = string.Format("{0:yyyy-MM-dd}", property.Value); break; default: //формат № 3 для дат * 14.03.2015 cell.StyleIndex = (UInt32Value)3U; cell.DataType = CellValues.Date; cellValue.Text = string.Format("{0:yyyy-MM-dd}", property.Value); break; } } else if (field.Attribute.Type.Name.Contains("money")) { switch (field.Format.FormatString) { case "{0:### ### ### ### ### p}.": //формат № 2 для денег FormatCode = "#,##0.00\"р.\"" cell.StyleIndex = (UInt32Value)2U; cell.DataType = CellValues.Number; cellValue.Text = string.Format(CultureInfo.InvariantCulture, "{0}", property.Value); break; case "{0}": //формат № 4 для чисел cell.StyleIndex = (UInt32Value)4U; cell.DataType = CellValues.Number; cellValue.Text = string.Format(CultureInfo.InvariantCulture, "{0}", property.Value); break; default: // Формат № 0 по умолчанию общий cell.StyleIndex = (UInt32Value)0U; cell.DataType = CellValues.String; cellValue.Text = string.Format(field.Format.Provider, field.Format.FormatString, property.Value); break; } } else { // Формат № 0 по умолчанию общий cell.StyleIndex = (UInt32Value)0U; cell.DataType = CellValues.String; cellValue.Text = string.Format(field.Format.Provider, field.Format.FormatString, property.Value); } cell.Append(cellValue); row.AppendChild(cell); } sheetData.AppendChild(row); } }
// создает рабочий лист Excel и запрлняет ячейкаи с данными из GroupReport private void AddNewSheet(string sheetName, SpreadsheetDocument spreadsheetDocument, Stream stream, GroupReport gRreport) { if (spreadsheetDocument == null) { throw new ArgumentNullException("spreadsheetDocument", string.Format("Параметр {0} имеет значение null.", "spreadsheetDocument")); } if (gRreport == null) { throw new ArgumentNullException("template", string.Format("Параметр {0} имеет значение null.", "template")); } // добавить новый worksheet. WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorksheetPart>(); SheetData sheetData = new SheetData(); newWorksheetPart.Worksheet = new Worksheet(sheetData); newWorksheetPart.Worksheet.Save(); Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart); // создать уникальный ID для worksheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetN = sheetName; //sheetN = "Лист" + sheetId; // Добавить новый worksheet и связать его с workbook Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetN }; sheets.Append(sheet); spreadsheetDocument.WorkbookPart.Workbook.Save(); //записать в Excel заголовки полей Row headerRow = new Row(); addHeaderRow(headerRow, gRreport); sheetData.AppendChild(headerRow); //записать в Excel строки с данными addDataRows(sheetData, gRreport); }