public void Main(List <MetrologicalServiseListObjectDTO> aObjects, string excelTitle, string filename)
{
//Создаем новый документ
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename + ".xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
FileVersion fv = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();
// Добавляем в документ набор стилей
wbsp.Stylesheet = OpenXMLCustomMethods.GenerateStyleSheet();
wbsp.Stylesheet.Save();
// Задаем колонки и их ширину
Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <Columns>();
bool needToInsertColumns = false;
if (lstColumns == null)
{
lstColumns = new Columns();
needToInsertColumns = true;
}
lstColumns.Append(new Column()
{
Min = 1, Max = 1, Width = 5, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 2, Max = 2, Width = 23, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 3, Max = 3, Width = 19, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 4, Max = 4, Width = 16, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 5, Max = 6, Width = 8, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 7, Max = 7, Width = 10, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 8, Max = 8, Width = 15, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 9, Max = 9, Width = 12, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 10, Max = 10, Width = 18, CustomWidth = false
});
if (needToInsertColumns)
{
worksheetPart.Worksheet.InsertAt(lstColumns, 0);
}
// Width = 5, Cu
//Width = 28,
//Width = 11,
//Width = 19,
//Width = 6, C
// Width = 14,
// Width = 22,
// Width = 12,
// , Width = 15,
//Создаем лист в книге
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "График"
};
sheets.Append(sheet);
Worksheet worksheet = OpenXMLCustomMethods.GetWorksheet(document, sheet.Name);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//Добавим заголовки в первую строку
uint regular = 5;
uint head = 6;
uint regular_rotate = 4;
Row row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
OpenXMLCustomMethods.InsertCell(row, "График поверки, калибровки и аттестации средств измерений АО «Атомтехэнерго»", CellValues.String, head);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "A1", "J1");
row = new Row()
{
RowIndex = 2
};
sheetData.Append(row);
OpenXMLCustomMethods.InsertCell(row, "№ п.п.", CellValues.String, regular);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "A2", "A3");
OpenXMLCustomMethods.InsertCell(row, "Наименование, тип, заводской №", CellValues.String, regular);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "B2", "B3");
OpenXMLCustomMethods.InsertCell(row, "Метрологические характеристики", CellValues.String, regular);
OpenXMLCustomMethods.InsertCellEmpty(row, 1, regular);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "C2", "D2");
OpenXMLCustomMethods.InsertCell(row, "МПИ, месяцы", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "E2", "E3");
OpenXMLCustomMethods.InsertCell(row, "Вид работ", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "F2", "F3");
OpenXMLCustomMethods.InsertCell(row, "Дата последней работы", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "G2", "G3");
OpenXMLCustomMethods.InsertCell(row, "Место проведения работ", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "H2", "H3");
OpenXMLCustomMethods.InsertCell(row, "Сроки проведения следующей поверки", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "I2", "I3");
OpenXMLCustomMethods.InsertCell(row, "Подразделение", CellValues.String, regular);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "J2", "J3");
row = new Row()
{
RowIndex = 3
};
sheetData.Append(row);
OpenXMLCustomMethods.InsertCellEmpty(row, 2, regular);
OpenXMLCustomMethods.InsertCell(row, "Класс точности, погрешность", CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, "Пределы (диапазоны); измерений", CellValues.String, regular);
OpenXMLCustomMethods.InsertCellEmpty(row, 6, regular);
int index = 0;
for (UInt32Value i = 4; index < aObjects.Count; i++, index++)
{
row = new Row()
{
RowIndex = i
};
sheetData.Append(row);
OpenXMLCustomMethods.InsertCell(row, (index + 1).ToString(), CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].TypeAOTitle + ", " +
aObjects[index].SignTypeTitle + ", " +
aObjects[index].FactoryNumber, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].Accuracy, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].MesuareLimits, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].MetrologicalWorkPeriod.ToString(), CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].WorkTypeTitle, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].MetrologicalWorkDateStr, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].WorkEnterpriseTitle, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].MetrologicalWorkDatePlanStr, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].OwnerDepartmentTitle, CellValues.String, regular);
}
OpenXMLCustomMethods.OrientationLandscape(worksheetPart);
workbookPart.Workbook.Save();
document.Close();
}
}