private static void FormatSheet(HrsStatistic stats, ExcelWorksheet worksheet, Dictionary <HealthRecord, int> additionalRows)
{
// область значений
var rangeColStart = patientCols + 1;
var rangeColEnd = patientCols + stats.Icds.Count + stats.Words.Count + stats.WordsWithMeasure.Count;
var rangeRowStart = headerRows + 1;
var rangeRowEnd = headerRows + stats.HealthRecords.Count + additionalRows.Values.Sum();
// format headers
worksheet.Cells[1, 1, headerRows, rangeColEnd].Style.Font.Bold = true;
worksheet.Cells[1, patientCols, rangeRowEnd, patientCols].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[1, 1, 1, rangeColEnd].AutoFilter = true;
worksheet.Cells[1, 1, rangeRowEnd, rangeColEnd].AutoFitColumns();
worksheet.View.FreezePanes(rangeRowStart, rangeColStart);
// hr id column
worksheet.Cells[rangeRowStart, 4, rangeRowEnd, 4].Style.Numberformat.Format = "dd/mm/yy h:mm";
worksheet.Column(4).Width = hrIdColWidth;
// format page
worksheet.HeaderFooter.OddHeader.CenteredText = "Эскпорт";
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Стр. {0} / {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
worksheet.HeaderFooter.OddFooter.LeftAlignedText = DateTime.Now.Date.ToShortDateString();
//worksheet.PrinterSettings.RepeatRows = worksheet.Cells[1, 1, headerRows, rangeColEnd];
//worksheet.PrinterSettings.RepeatColumns = worksheet.Cells[1, 1, rangeRowEnd, patientCols];
}
public void ExportToXlsx(HrsStatistic stats)
{
var result = new FileDialogService().ShowSaveFileDialog(null,
FileType.Xlsx.ToEnumerable(),
FileType.Xlsx,
string.Format("{0} {1:yyyy.MM.dd HH.mm}", defName, DateTime.Now));
if (result.IsValid)
{
var newFile = FileHelper.CreateNewFile(result.FileName);
ExcelPackage package = MakeExcelPackage(stats);
SaveTo(package, newFile);
}
}
private static void SetValues(HrsStatistic stats, ExcelWorksheet worksheet, Dictionary <HealthRecord, int> additionalRows)
{
int cIndex = 1, rIndex = 1; // индексация с 1
foreach (var hr in stats.HealthRecords)
{
cIndex = 1;
foreach (var icd in stats.Icds)
{
if (stats.GridValues[hr][icd].Bool.HasValue)
{
worksheet.Cells[rIndex, cIndex].Value = stats.GridValues[hr][icd].Bool;
}
cIndex++;
}
foreach (var word in stats.Words)
{
int additionalRow = 0;
if (stats.GridValues[hr][word].Bool.HasValue)
{
worksheet.Cells[rIndex, cIndex].Value = stats.GridValues[hr][word].Bool;
}
else
{
foreach (var measure in stats.GridValues[hr][word].Measures)
{
// При повторе слова с числом в одной записи — только это число в дополнительной строке.
worksheet.Cells[rIndex + additionalRow, cIndex].Value = measure.Value;
worksheet.Cells[rIndex + additionalRow, cIndex + 1].Value = measure.Uom != null ? measure.Uom.Abbr : null;
additionalRow++;
}
}
// есть измерение со словом - доп. колонка
if (HasMeasuresFor(stats.GridValues, word))
{
cIndex++;
}
cIndex++;
additionalRows[hr] = Math.Max(additionalRows.GetValueOrDefault(hr), additionalRow - 1);
}
rIndex = rIndex + 1 + additionalRows[hr];
}
}
private static ExcelPackage MakeExcelPackage(HrsStatistic stats)
{
ExcelPackage package = new ExcelPackage();
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Матрица");
// доп. строки на запись
var additionalRows = new Dictionary <HealthRecord, int>();
SetValues(stats, worksheet, additionalRows);
SetHeaders(stats, worksheet, additionalRows);
FormatSheet(stats, worksheet, additionalRows);
// set some document properties
package.Workbook.Properties.Title = "Diagnosis Эскпорт";
return(package);
}
private static void SetHeaders(HrsStatistic stats, ExcelWorksheet worksheet, Dictionary <HealthRecord, int> additionalRows)
{
// заголовки строк
worksheet.InsertColumn(1, patientCols);
var rIndex = 1;
foreach (var hr in stats.HealthRecords)
{
var pat = hr.GetPatient();
worksheet.Cells[rIndex, 1].Value = pat.FullNameOrCreatedAt;
worksheet.Cells[rIndex, 2].Value = pat.IsMale;
worksheet.Cells[rIndex, 3].Value = pat.Age;
worksheet.Cells[rIndex, 4].Value = hr.CreatedAt;
// доп. строки
rIndex += 1 + additionalRows[hr];
}
// заголовки колонок на заголовок строки
worksheet.InsertRow(1, headerRows);
worksheet.Cells[1, 1].Value = "Имя";
worksheet.Cells[1, 2].Value = "Пол";
worksheet.Cells[1, 3].Value = "Возраст";
worksheet.Cells[1, 4].Value = "Дата создания записи";
var cIndex = patientCols + 1;
// заголовки колонок со значениями
foreach (var icd in stats.Icds)
{
worksheet.Cells[1, cIndex++].Value = icd.Code;
}
foreach (var word in stats.Words)
{
worksheet.Cells[1, cIndex++].Value = word.Title;
// есть измерение со словом - доп. колонка
if (HasMeasuresFor(stats.GridValues, word))
{
worksheet.Cells[1, cIndex++].Value = uomHeader;
}
}
}
