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