/// <summary>
/// Adds styles to workbook (for reporting).
/// </summary>
public void AddStylesForReport()
{
if (spreadsheetDocument == null)
{
return;
}
if (spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats == null)
{
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats();
}
if (spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats == null)
{
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats();
}
DocumentFormat.OpenXml.Spreadsheet.CellFormat cellFormat;
DocumentFormat.OpenXml.Spreadsheet.NumberingFormat numberingFormat;
DocumentFormat.OpenXml.UInt32Value customFormatId = 164;
// Number (integral)
cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();
cellFormat.NumberFormatId = 3; // #,##0
cellFormat.ApplyNumberFormat = DocumentFormat.OpenXml.BooleanValue.FromBoolean(true);
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat);
numberIntegralFormatStyleIndex = spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count++;
// Number (fractional)
cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();
cellFormat.NumberFormatId = 4; // #,##0.00
cellFormat.ApplyNumberFormat = DocumentFormat.OpenXml.BooleanValue.FromBoolean(true);
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat);
numberFractionalFormatStyleIndex = spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count++;
// Percent (integral)
cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();
cellFormat.NumberFormatId = 9; // 0%
cellFormat.ApplyNumberFormat = DocumentFormat.OpenXml.BooleanValue.FromBoolean(true);
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat);
percentIntegralFormatStyleIndex = spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count++;
// Percent (fractional)
cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();
cellFormat.NumberFormatId = 10; // 0.00%
cellFormat.ApplyNumberFormat = DocumentFormat.OpenXml.BooleanValue.FromBoolean(true);
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat);
percentFractionalFormatStyleIndex = spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count++;
// Date
numberingFormat = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
numberingFormat.NumberFormatId = customFormatId++;
numberingFormat.FormatCode = DocumentFormat.OpenXml.StringValue.FromString("dd.mm.yyyy");
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.AppendChild(numberingFormat);
cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();
cellFormat.NumberFormatId = numberingFormat.NumberFormatId; // 14; // mm-dd-yy
cellFormat.ApplyNumberFormat = DocumentFormat.OpenXml.BooleanValue.FromBoolean(true);
cellFormat.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Alignment {
Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center
});
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat);
dateFormatStyleIndex = spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count++;
// Date and time
numberingFormat = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
numberingFormat.NumberFormatId = customFormatId++;
numberingFormat.FormatCode = DocumentFormat.OpenXml.StringValue.FromString("dd.mm.yyyy hh:mm");
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.AppendChild(numberingFormat);
cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();
cellFormat.NumberFormatId = numberingFormat.NumberFormatId; // 22; // m/d/yy h:mm
cellFormat.ApplyNumberFormat = DocumentFormat.OpenXml.BooleanValue.FromBoolean(true);
cellFormat.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Alignment {
Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center
});
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat);
dateTimeFormatStyleIndex = spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count++;
// Boolean
cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();
cellFormat.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Alignment {
Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center
});
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat);
booleanFormatStyleIndex = spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count++;
}
private void WriteToSheet(Worksheet worksheet, SharedStringTable sharedString, List <Record> records, string action, string sheet_name)
{
switch (action)
{
case "fill":
{
List <DataForFill> data_for_fill = new List <DataForFill>(); // Создаем список структур, в которых будут содержаться только интересующие нас данные
Row task_result_row = worksheet.Descendants <Row>().Where(r => r.RowIndex == 11).FirstOrDefault(); // т.к. начинаем писать в файл с 11 строки
foreach (Cell cell in task_result_row) // Собираем буквенные ссылки на ячейки с заданиями
{
if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.InnerText) && cell.DataType?.Value != CellValues.Error)
{
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
if (int.TryParse(sharedString.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText, out int result_int) ||
double.TryParse(sharedString.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText,
NumberStyles.Any, CultureInfo.InvariantCulture.NumberFormat, out double result_double))
{
columnName_columnIndex.Add(new Tuple <string, string>(sharedString.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText,
Regex.Match(cell.CellReference, "[A-Z]{1,}").Value));
}
}
}
else
{
if (int.TryParse(sharedString.ElementAt(int.Parse(cell.CellValue.InnerText)).InnerText, out int result_int) ||
double.TryParse(cell.CellValue.InnerText, NumberStyles.Any, CultureInfo.InvariantCulture.NumberFormat, out double result_double))
{
columnName_columnIndex.Add(new Tuple <string, string>(cell.CellValue.InnerText,
Regex.Match(cell.CellReference, "[A-Z]{1,}").Value));
}
}
}
} // Собираем буквенные ссылки на ячейки с заданиями
foreach (Record item in records) // Заполняем список data
{
data_for_fill.Add(new DataForFill(item.student_FIO, item.variant, item.task_results));
}
Row sample_row = worksheet.Descendants <Row>().Where(i => i.RowIndex == 25).FirstOrDefault();
foreach (Cell sample_cell in sample_row)
{
if (sample_cell.CellReference == "D25")
{
cell_style_fio = sample_cell.StyleIndex;
}
if (sample_cell.CellReference == "E25")
{
cell_style_variant = sample_cell.StyleIndex;
}
if (sample_cell.CellReference == "F25")
{
cell_style_tasks = sample_cell.StyleIndex;
}
}
foreach (DataForFill data in data_for_fill) // Бежим по нашей структуре и заполняем ячейки
{
// Insert the text into the SharedStringTablePart.
int index_FIO = InsertSharedStringItem(data.student_FIO, sharedString);
int index_variant = InsertSharedStringItem(data.variant, sharedString);
Cell cellFIO = InsertCellInWorksheet("D", (uint)data_for_fill.IndexOf(data) + 25, worksheet);
Cell cellVariant = InsertCellInWorksheet("E", (uint)data_for_fill.IndexOf(data) + 25, worksheet);
// Задаем значения ячеек.
cellFIO.CellValue = new CellValue(index_FIO.ToString());
cellVariant.CellValue = new CellValue(index_variant.ToString());
cellFIO.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.SharedString);
cellVariant.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.SharedString);
cellFIO.StyleIndex = cell_style_fio;
cellVariant.StyleIndex = cell_style_variant;
foreach (Tuple <double, double> temp_task in data.task_results) // Заполяем ячейки результатов
{
foreach (Tuple <string, string> task_ref in columnName_columnIndex)
{
if (temp_task.Item1 == double.Parse(task_ref.Item1, NumberStyles.Any, CultureInfo.InvariantCulture.NumberFormat))
{
int c_row_index = (int)data_for_fill.IndexOf(data) + 25;
//if (c_row_index > 165)
// MessageBox.Show("Watch");
Cell cell_task_result = InsertCellInWorksheet(task_ref.Item2, (uint)data_for_fill.IndexOf(data) + 25, worksheet);
cell_task_result.CellValue = new CellValue(temp_task.Item2.ToString());
cell_task_result.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_task_result.StyleIndex = cell_style_tasks;
}
}
}
// Save the new worksheet.
worksheet.Save();
double count_record = data_for_fill.IndexOf(data);
double progress = (((double)data_for_fill.IndexOf(data) + 1) / (double)(data_for_fill.Count - 1)) * 100;
OnProgress_up((int)progress, sheet_name);
progress = 0;
}
break;
}
case "calculate_and_fill":
{
var region_records = records.GroupBy(r => r.region).ToList();
List <DataSortedByRegion> region_data_to_fill = new List <DataSortedByRegion>(region_records.Count);
foreach (IGrouping <string, Record> record in region_records) // Заполняем список данных, собранных с кадого региона отдельно
{
string region = record.Key;
int schools_count = record.GroupBy(s => s.school_id).ToList().Count;
int clasess_count = record.GroupBy(s => s.class_id).ToList().Count;
int student_count = record.GroupBy(s => s.student_FIO).ToList().Count;
int student_write_count = record.Where(s => s.variant != "N").ToList().Count;
int low_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Низкий", RegexOptions.IgnoreCase)).ToList().Count;
int upper_low_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Пониженный", RegexOptions.IgnoreCase)).ToList().Count;
int base_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Базовый", RegexOptions.IgnoreCase)).ToList().Count;
int upper_base_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Повышенный", RegexOptions.IgnoreCase)).ToList().Count;
int high_lvl_score_count = record.Where(l => Regex.IsMatch(l.understand_lvl, "Высокий", RegexOptions.IgnoreCase)).ToList().Count;
double min_total_score = record.Where(v => v.variant != "N").Min(t => t.total_score);
double max_total_score = record.Where(v => v.variant != "N").Max(t => t.total_score);
double total_score = 0;
double base_lvl_count = 0;
double high_lvl_count = 0;
foreach (var data in record)
{
total_score += data.total_score;
base_lvl_count += data.base_lvl;
high_lvl_count += data.high_lvl;
}
region_data_to_fill.Add(new DataSortedByRegion(region, schools_count, clasess_count, student_count, student_write_count,
low_lvl_score_count, upper_low_lvl_score_count, base_lvl_score_count,
upper_base_lvl_score_count, high_lvl_score_count, (int)Math.Truncate(total_score), (int)Math.Truncate(base_lvl_count),
(int)Math.Truncate(high_lvl_count), min_total_score, max_total_score));
}
foreach (DataSortedByRegion data in region_data_to_fill)
{
int index_region = InsertSharedStringItem(data.region, sharedString);
//int index_schools_count = InsertSharedStringItem(data.schools_count.ToString(), sharedString);
//int index_clasess_count = InsertSharedStringItem(data.clasess_count.ToString(), sharedString);
//int index_student_count = InsertSharedStringItem(data.student_count.ToString(), sharedString);
//int index_student_write_count = InsertSharedStringItem(data.student_write_count.ToString(), sharedString);
//int index_low_lvl_score_count = InsertSharedStringItem(data.low_lvl_score_count.ToString(), sharedString);
//int index_upper_low_lvl_score_count = InsertSharedStringItem(data.upper_low_lvl_score_count.ToString(), sharedString);
//int index_base_lvl_score_count = InsertSharedStringItem(data.base_lvl_score_count.ToString(), sharedString);
//int index_upper_base_lvl_score_count = InsertSharedStringItem(data.upper_base_lvl_score_count.ToString(), sharedString);
//int index_high_lvl_score_count = InsertSharedStringItem(data.high_lvl_score_count.ToString(), sharedString);
//int index_total_score = InsertSharedStringItem(data.total_score.ToString(), sharedString);
//int index_base_lvl_count = InsertSharedStringItem(data.base_lvl_count.ToString(), sharedString);
//int index_high_lvl_count = InsertSharedStringItem(data.high_lvl_count.ToString(), sharedString);
//int min_total_score = InsertSharedStringItem(data.min_total_score.ToString(), sharedString);
//int max_total_score = InsertSharedStringItem(data.max_total_score.ToString(), sharedString);
Cell cell_region = InsertCellInWorksheet("B", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_schools_count = InsertCellInWorksheet("D", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_clasess_count = InsertCellInWorksheet("E", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_student_count = InsertCellInWorksheet("F", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_student_write_count = InsertCellInWorksheet("H", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_low_lvl_score_count = InsertCellInWorksheet("M", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_upper_low_lvl_score_count = InsertCellInWorksheet("Q", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_base_lvl_score_count = InsertCellInWorksheet("U", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_upper_base_lvl_score_count = InsertCellInWorksheet("Y", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_high_lvl_score_count = InsertCellInWorksheet("AC", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_total_score = InsertCellInWorksheet("AG", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_base_lvl_count = InsertCellInWorksheet("AK", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_high_lvl_count = InsertCellInWorksheet("AN", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_min_total_score = InsertCellInWorksheet("AS", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
Cell cell_max_total_score = InsertCellInWorksheet("AU", (uint)region_data_to_fill.IndexOf(data) + 7, worksheet);
cell_region.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.SharedString);
cell_schools_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_clasess_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_student_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_student_write_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_low_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_upper_low_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_base_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_upper_base_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_high_lvl_score_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_total_score.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_base_lvl_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_high_lvl_count.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_min_total_score.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
cell_max_total_score.DataType = new DocumentFormat.OpenXml.EnumValue <CellValues>(CellValues.Number);
// Set the value of cell A1.
cell_region.CellValue = new CellValue(index_region.ToString());
cell_schools_count.CellValue = new CellValue(data.schools_count.ToString());
cell_clasess_count.CellValue = new CellValue(data.clasess_count.ToString());
cell_student_count.CellValue = new CellValue(data.student_count.ToString());
cell_student_write_count.CellValue = new CellValue(data.student_write_count.ToString());
cell_low_lvl_score_count.CellValue = new CellValue(data.low_lvl_score_count.ToString());
cell_upper_low_lvl_score_count.CellValue = new CellValue(data.upper_low_lvl_score_count.ToString());
cell_base_lvl_score_count.CellValue = new CellValue(data.base_lvl_score_count.ToString());
cell_upper_base_lvl_score_count.CellValue = new CellValue(data.upper_base_lvl_score_count.ToString());
cell_high_lvl_score_count.CellValue = new CellValue(data.high_lvl_score_count.ToString());
cell_total_score.CellValue = new CellValue(data.total_score.ToString());
cell_base_lvl_count.CellValue = new CellValue(data.base_lvl_count.ToString());
cell_high_lvl_count.CellValue = new CellValue(data.high_lvl_count.ToString());
cell_min_total_score.CellValue = new CellValue(data.min_total_score.ToString());
cell_max_total_score.CellValue = new CellValue(data.max_total_score.ToString());
// Save the new worksheet.
worksheet.Save();
double progress = (((double)region_data_to_fill.IndexOf(data) + 1) / (double)(region_data_to_fill.Count)) * 100;
OnProgress_up((int)progress, sheet_name);
progress = 0;
}
break;
}
}
}
private static Picture AddPicture(Slide slide, Shape referingShape, string imageFile)
{
Picture picture = new Picture();
string embedId = string.Empty;
DocumentFormat.OpenXml.UInt32Value picId = 10001U;
string name = string.Empty;
if (slide.Elements <Picture>().Count() > 0)
{
picId = ++slide.Elements <Picture>().ToList().Last().NonVisualPictureProperties.NonVisualDrawingProperties.Id;
}
name = "image" + picId.ToString();
embedId = "rId" + (slide.Elements <Picture>().Count() + 915).ToString(); // some value
NonVisualPictureProperties nonVisualPictureProperties = new NonVisualPictureProperties()
{
NonVisualDrawingProperties = new NonVisualDrawingProperties()
{
Name = name, Id = picId, Title = name
},
NonVisualPictureDrawingProperties = new NonVisualPictureDrawingProperties()
{
PictureLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks()
{
NoChangeAspect = true
}
},
ApplicationNonVisualDrawingProperties = new ApplicationNonVisualDrawingProperties()
{
UserDrawn = true
}
};
BlipFill blipFill = new BlipFill()
{
Blip = new DocumentFormat.OpenXml.Drawing.Blip()
{
Embed = embedId
}
};
DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch()
{
FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle()
};
blipFill.Append(stretch);
ShapeProperties shapeProperties = new ShapeProperties()
{
Transform2D = new DocumentFormat.OpenXml.Drawing.Transform2D()
{
//MMS:4
Offset = new DocumentFormat.OpenXml.Drawing.Offset()
{
X = 1565275, Y = 612775
}, // { X = 457200L, Y = 1124000L }
Extents = new DocumentFormat.OpenXml.Drawing.Extents()
{
Cx = 5486400, Cy = 4114800
} //{ Cx = 8229600L, Cy = 5029200L }
}
//<a:off x="1565275" y="612775" />
//<a:ext cx="5486400" cy="4114800" />
};
DocumentFormat.OpenXml.Drawing.PresetGeometry presetGeometry = new DocumentFormat.OpenXml.Drawing.PresetGeometry()
{
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle
};
DocumentFormat.OpenXml.Drawing.AdjustValueList adjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
presetGeometry.Append(adjustValueList);
shapeProperties.Append(presetGeometry);
picture.Append(nonVisualPictureProperties);
picture.Append(blipFill);
picture.Append(shapeProperties);
slide.CommonSlideData.ShapeTree.Append(picture);
// Add Image part
AddImagePart(slide, embedId, imageFile);
slide.Save();
return(picture);
}
