Beispiel #1
0
        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();
            }
        }