// Adds child parts and generates content of the specified part.
        private void CreateParts(SpreadsheetDocument document)
        {
            ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
            GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);

            WorkbookPart workbookPart1 = document.AddWorkbookPart();
            GenerateWorkbookPart1Content(workbookPart1);

            WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId3");
            GenerateWorkbookStylesPart1Content(workbookStylesPart1);

            ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId2");
            GenerateThemePart1Content(themePart1);

            WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetPart1Content(worksheetPart1);

            DrawingsPart drawingsPart1 = worksheetPart1.AddNewPart<DrawingsPart>("rId2");
            GenerateDrawingsPart1Content(drawingsPart1);

            ImagePart imagePart1 = drawingsPart1.AddNewPart<ImagePart>("image/jpeg", "rId1");
            GenerateImagePart1Content(imagePart1);

            SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = worksheetPart1.AddNewPart<SpreadsheetPrinterSettingsPart>("rId1");
            GenerateSpreadsheetPrinterSettingsPart1Content(spreadsheetPrinterSettingsPart1);

            CalculationChainPart calculationChainPart1 = workbookPart1.AddNewPart<CalculationChainPart>("rId5");
            GenerateCalculationChainPart1Content(calculationChainPart1);

            SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId4");
            GenerateSharedStringTablePart1Content(sharedStringTablePart1);

            SetPackageProperties(document);
        }

 public static Cell XLGetCell(SpreadsheetDocument document, string sheetName, string columnName, uint rowIndex)
 {
     Sheet theSheet = XLGetSheet(document, sheetName);
     // Throw an exception if there is no sheet.
     if (theSheet == null) throw new ArgumentException("sheetName");
     return XLGetCell(document, theSheet, columnName, rowIndex);
 }

 /// <summary>
 /// XDocument containing Xml content of the styles part
 /// </summary>
 public static XDocument GetStylesDocument(SpreadsheetDocument document)
 {
     if (document.WorkbookPart.WorkbookStylesPart != null)
         return document.WorkbookPart.WorkbookStylesPart.GetXDocument();
     else
         return null;
 }

        private void CreateParts(SpreadsheetDocument document)
        {
            WorkbookPart workbookPart1 = document.AddWorkbookPart();
            GenerateWorkbookPart1Content(workbookPart1);

            // Create sharedstring
            SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>("rId7");
            GenerateSharedStringTablePart1Content(sharedStringTablePart1);

            // Create Sheet2
            WorksheetPart worksheetPart2 = workbookPart1.AddNewPart<WorksheetPart>("rId2");
            GenerateWorksheetPart2Content(worksheetPart2);

            // Create Sheet1
            WorksheetPart worksheetPart3 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetPart3Content(worksheetPart3);

            // Add PivotTablePart to Sheet1
            PivotTablePart pivotTablePart1 = worksheetPart3.AddNewPart<PivotTablePart>("rId1");
            GeneratePivotTablePart1Content(pivotTablePart1);

            // Add PivotTableCacheDefinitionPart to PivotTablePart
            PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = pivotTablePart1.AddNewPart<PivotTableCacheDefinitionPart>("rId1");
            GeneratePivotTableCacheDefinitionPart1Content(pivotTableCacheDefinitionPart1);

            // Add PivotTableCacheDefinitionPart to PivotTableCacheDefinitionPart
            PivotTableCacheRecordsPart pivotTableCacheRecordsPart1 = pivotTableCacheDefinitionPart1.AddNewPart<PivotTableCacheRecordsPart>("rId1");
            GeneratePivotTableCacheRecordsPart1Content(pivotTableCacheRecordsPart1);

            WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId6");
            GenerateWorkbookStylesPart1Content(workbookStylesPart1);

            workbookPart1.AddPart(pivotTableCacheDefinitionPart1, "rId4");
        }

 public static string XLGetCellValue(SpreadsheetDocument document, string sheetName, string columnName, uint rowIndex)
 {
     Cell cell = XLGetCell(document, sheetName, columnName, rowIndex);
     string value = null;
     if (cell != null) value = XLInterpretedCellValue(document, cell);
     return value;
 }

        /// <summary>
        /// Creates the content of the shet (columns, rows, cells)
        /// </summary>
        /// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
        /// <param name="worksheetPart">The worksheetpart for this item</param>
        private void InsertTextIntoCells(SpreadsheetDocument spreadsheetDocument, WorksheetPart worksheetPart)
        {
            // Get the SharedStringTablePart. If it does not exist, create a new one.
            SharedStringTablePart shareStringPart;
            if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Any())
            {
                shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
            }

            #region Excel headers

            string header1 = "ResourceTypeID";
            int index1 = _sharedResources.InsertSharedStringItem(header1, shareStringPart);
            Cell headerCell1 = _sharedResources.InsertCellInWorksheet("A", 1, worksheetPart);
            headerCell1.CellValue = new CellValue(index1.ToString(CultureInfo.InvariantCulture));
            headerCell1.DataType = new EnumValue<CellValues>(CellValues.SharedString);

            string header2 = "ResourceType";
            int index2 = _sharedResources.InsertSharedStringItem(header2, shareStringPart);
            Cell headerCell2 = _sharedResources.InsertCellInWorksheet("B", 1, worksheetPart);
            headerCell2.CellValue = new CellValue(index2.ToString(CultureInfo.InvariantCulture));
            headerCell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);

            #endregion

            #region insert the items from the temporary list into the ktUIOrder excel sheet

            int columnCount = 1;
            uint rowCount = 2;

            foreach (ktResourceType resourceType in _resourceType.ktResourceTypeList)
            {
                    if (columnCount >= 2)
                    {
                        columnCount = 1;
                    }

                    string text1 = resourceType.ResourceTypeID;
                    Cell cell1 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
                    cell1.CellValue = new CellValue(text1);
                    cell1.DataType = CellValues.Number;
                    columnCount++;

                    string text2 = resourceType.ResourceType;
                    Cell cell2 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
                    cell2.CellValue = new CellValue(text2);
                    cell2.DataType = CellValues.String;
                    columnCount++;

                    rowCount++;
            }

            #endregion

            worksheetPart.Worksheet.Save();
        }

 private ExcelStreamReader(string fileName)
 {
     this.fileName = fileName;
     this.doc = SpreadsheetDocument.Open(fileName, false);
     this.workbookPart = doc.WorkbookPart;
     this.sheets = GetSheets();
 }

        // Adds child parts and generates content of the specified part.
        private void CreateParts(SpreadsheetDocument document)
        {
            ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
            GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);

            WorkbookPart workbookPart1 = document.AddWorkbookPart();
            GenerateWorkbookPart1Content(workbookPart1);

            WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId3");
            GenerateWorksheetPart1Content(worksheetPart1);

            WorksheetPart worksheetPart2 = workbookPart1.AddNewPart<WorksheetPart>("rId2");
            GenerateWorksheetPart2Content(worksheetPart2);

            WorksheetPart worksheetPart3 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetPart3Content(worksheetPart3);

            WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId5");
            GenerateWorkbookStylesPart1Content(workbookStylesPart1);

            ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId4");
            GenerateThemePart1Content(themePart1);

            SetPackageProperties(document);
        }

        /// <summary>
        /// Creates a spreadsheet document from a value table
        /// </summary>
        /// <param name="filePath">Path to store the document</param>
        /// <param name="headerList">Contents of first row (header)</param>
        /// <param name="valueTable">Contents of data</param>
        /// <param name="initialRow">Row to start copying data from</param>
        /// <returns></returns>
        public static void Create(SpreadsheetDocument document, List<string> headerList, string[][] valueTable, int initialRow)
        {
            headerRow = initialRow;

            //Creates a worksheet with given data
            WorksheetPart worksheet = WorksheetAccessor.Create(document, headerList, valueTable, headerRow);
        }

        public void Print(SpreadsheetDocument document)
        {
            var extendedFilePropertiesPart = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
            generateExtendedFilePropertiesPartContent(extendedFilePropertiesPart);

            var workbookPart = document.AddWorkbookPart();
            generateWorkbookPartContent(workbookPart);

            var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rId3");
            GenerateWorkbookStylesPartContent(workbookStylesPart);

            var themePart = workbookPart.AddNewPart<ThemePart>("rId2");
            generateThemePartContent(themePart);

            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetPartContent(worksheetPart);

            var spreadsheetPrinterSettingsPart = worksheetPart.AddNewPart<SpreadsheetPrinterSettingsPart>("rId1");
            generateSpreadsheetPrinterSettingsPartContent(spreadsheetPrinterSettingsPart);

            var sharedStringTablePart = workbookPart.AddNewPart<SharedStringTablePart>("rId4");
            GenerateSharedStringTablePartContent(sharedStringTablePart);

            setPackageProperties(document);
        }

 public xlBook ReadToBook(SpreadsheetDocument document)
 {
     using (_docProvider = new DocDictionaries(document, _config.AutoDispose))
     {
         xlBook result = new xlBook();
         for (int i = 0; i < document.WorkbookPart.WorksheetParts.Count(); i++)
         {
             result.AddSheet(_docProvider.sheetNames[i]);
             var sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().SingleOrDefault(s => s.Name == _docProvider.sheetNames[i]);
             var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id.Value);
             foreach (var cell in worksheetPart.Worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
             {
                 try
                 {
                     if (cell == null) { continue; }
                     result[i].Cells.Add(ReadCell(cell));
                 }
                 catch (Exception)
                 {
                     if (_config.ContinueOnRowReadingError) continue; else throw;
                 }
             }
         }
         return result;
     }
 }

 public DocDictionaries(SpreadsheetDocument document, bool AutoDispose)
 {
     _autoDispose = AutoDispose;
     this.document = document;
     sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable.Select(x => x.InnerText).ToArray();
     sheetNames = document.WorkbookPart.Workbook.Sheets.Cast<Sheet>().Select(x => x.Name.Value).ToArray();
     styles = document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Cast<CellFormat>().ToArray();
 }

 /// <summary>
 /// Создает экземпляр класса для работы с текущим файлом
 /// </summary>
 /// <param name="filePath">Путь к документу</param>
 /// <param name="removeAfterDestroy">Удалять ли файл после окончания работы с ним</param>
 public ExcellWorker(string filePath, bool removeAfterDestroy)
 {
     _currentFilePath = filePath;
     _currentDocument = SpreadsheetDocument.Open(filePath, true);
     _currentWorkBookPart = _currentDocument.WorkbookPart;
     _currentSheet = _currentWorkBookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
     RemoveAfterDestroy = removeAfterDestroy;
 }

 public RenderSpreadSheetDocument_OpenXmlSdk(Report report, IStreamGen sg)
     : base(report, sg)
 {
     _ms = new MemoryStream();
     _spreadSheet = SpreadsheetDocument.Create(_ms, SpreadsheetDocumentType.Workbook);
     _openXmlExportHelper = new OpenXmlWriterHelper();
     _workbook = new Workbook();
     _workSheetSettings = new List<WorkSheetSetting>();
 }

        public ExcelDataReader(string path, int sheetIndex = 0, bool firstRowAsHeader = true)
        {
            _document = SpreadsheetDocument.Open(path, false);
            _sharedStrings = GetSharedStrings(_document);

            var worksheetPart = _document.WorkbookPart.GetPartById(GetSheetByIndex(sheetIndex).Id.Value);
            _reader = OpenXmlReader.Create(worksheetPart);
            _headers = firstRowAsHeader ? GetFirstRowAsHeaders() : GetRangeHeaders(worksheetPart);
        }

 protected ExcelTemplate(string fileName)
 {
     mStream = new MemoryStream();
     using (var fileStream = File.OpenRead(fileName))
     {
         fileStream.CopyTo(mStream);
     }
     mSpreadSheet = SpreadsheetDocument.Open(mStream, true);
 }

        private void UpdateParts(SpreadsheetDocument document, Dictionary<WorksheetPart, List<ExcelSheet>> worksheetsWithRows, List<ProjectFile> projectFiles, Customer customer, User user)
        {
            var workSheets = document.WorkbookPart.WorksheetParts;

            foreach (var workSheet in workSheets)
            {
                UpdateWorksheetContent(workSheet, worksheetsWithRows[workSheet], document.WorkbookPart.SharedStringTablePart.SharedStringTable, projectFiles, customer, user);
            }
        }

 public static List<Worksheet> GetAllWorksheets(SpreadsheetDocument spreadsheet)
 {
     var worksheets = new List<Worksheet>();
     var worksheetParts = ExcelSpreadsheet.GetAllWorksheetParts(spreadsheet);
     foreach (WorksheetPart itemWorksheetPart in worksheetParts)
     {
         worksheets.Add(itemWorksheetPart.Worksheet);
     }
     return worksheets;
 }

 public ExcelHelper Create(string ExcelFile)
 {
     System.IO.FileInfo fi = new System.IO.FileInfo(ExcelFile);
     if (!fi.Directory.Exists)
         fi.Directory.Create();
     _doc = SpreadsheetDocument.Create(ExcelFile, SpreadsheetDocumentType.Workbook);
     _wbPart = _doc.AddWorkbookPart();
     _wbPart.Workbook = new Workbook();
     _doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
     return _instance;
 }

 private CellFormat GetCellFormat(SpreadsheetDocument doc, Cell cell)
 {
     var index = (int) cell.StyleIndex.Value;
     var cellFormat = doc.WorkbookPart
                         .WorkbookStylesPart
                         .Stylesheet
                         .CellFormats
                         .Elements<CellFormat>()
                         .ElementAt(index);
     return cellFormat;
 }