public static void SetInlineCell(WorksheetPart worksheetPart, string text, string columnName, uint rowIndex)
        {
            Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);

            cell.CellValue = new CellValue(string.IsNullOrWhiteSpace(text) ? "" : text);
            cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);
        }

        public void AddNewWorksheet()
        {
            WorkbookPart workbookPart = spreadSheet.WorkbookPart;

            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();
            CurrentWorksheetPart = newWorksheetPart;
            //workbookPart.SharedStringTablePart.SharedStringTable.Count = workbookPart.SharedStringTablePart.SharedStringTable.Count + 1;
            //workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount = workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount + 1;
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            string sheetName = "Sheet" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();
        }

        public static void SetCellDateTime(WorksheetPart worksheetPart, DateTime value, string columnName, uint rowIndex)
        {
            Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);

            cell.CellValue = new CellValue(value.ToOADate().ToString("0.000#####"));
            cell.DataType = new EnumValue<CellValues>(CellValues.Number);
        }

        private void UpdateWorksheetContent(WorksheetPart workSheetPart, List<ExcelSheet> templateRows, SharedStringTable sharedStrings, List<ProjectFile> projectFiles, Customer customer, User user)
        {
            IEnumerable<SheetData> sheets = workSheetPart.Worksheet.ChildElements.OfType<SheetData>();

            foreach (var sheetData in sheets)
            {
                sheetData.RemoveAllChildren<Row>();

                List<String> strings = sharedStrings.ChildElements.OfType<SharedStringItem>().Where(shItem => shItem.ChildElements.OfType<Text>().Any()).Select(shItem =>
                    {
                        var firstOrDefault = shItem.ChildElements.OfType<Text>().FirstOrDefault();
                        return firstOrDefault != null ? firstOrDefault.Text : String.Empty;
                    }).ToList();

                //from this point is the content
                int linesToAdd = 0;
                var templateSheet = templateRows.FirstOrDefault(row => row.SheetData.Equals(sheetData));
                if (templateSheet != null)
                {
                    foreach (var projectFile in projectFiles)
                    {
                        GenerateWorksheetDataContent(sheetData, templateSheet.Rows, linesToAdd, new List<TokensProvider> {projectFile, customer, user}, strings);
                        linesToAdd += (templateSheet.Rows.Count + 5);
                    }
                }
            }
        }

        public static void SetCellDate(WorksheetPart worksheetPart, DateTime value, string columnName, DocumentFormat.OpenXml.Spreadsheet.Row row)
        {
            Cell cell = GetCell(worksheetPart.Worksheet, columnName, row);

            cell.CellValue = new CellValue(value.ToOADate().ToString("0"));
            cell.DataType = new EnumValue<CellValues>(CellValues.Number);
        }

        public static void SetCell(WorksheetPart worksheetPart, string text, string columnName, DocumentFormat.OpenXml.Spreadsheet.Row row, out Cell cell)
        {
            cell = GetCell(worksheetPart.Worksheet, columnName, row);

            cell.CellValue = new CellValue(string.IsNullOrWhiteSpace(text) ? "" : text);
            cell.DataType = new EnumValue<CellValues>(CellValues.String);
        }

        /// <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();
        }

 public OpenXLRow(WorkbookPart wbPart, WorksheetPart wsPart, List<string> columns, Row xRow)
 {
     this.WbPart = wbPart;
     this.WsPart = wsPart;
     this.Columns = columns;
     this._cells = GetCells(xRow);
 }

 private Sheet GetCurrentSheet(WorksheetPart worksheetPart)
 {
     var sheetId = workbookPart.GetIdOfPart(worksheetPart);
     var correspondingSheet = sheets.FirstOrDefault(
         s => s.Id.HasValue && s.Id.Value == sheetId);
     return correspondingSheet;
 }

 public string GetValueCell(ref WorkbookPart wbPart, ref WorksheetPart wsPart, string addressName)
 {
     string value = null;
     Cell theCell = wsPart.Worksheet.Descendants<Cell>().
         Where(c => c.CellReference == addressName).FirstOrDefault();
     value = GetCleanValueCell(theCell, ref wbPart);
     return value;
 }

 public void CreateHeader(WorksheetPart worksheetPart)
 {
     if (worksheetPart == null)
     {
         throw new ArgumentNullException("worksheetPart");
     }
     ExcelUtility.SetStringCell(worksheetPart.Worksheet, "B", 2, ServiceOffering.Id.ToString());
     ExcelUtility.SetStringCell(worksheetPart.Worksheet, "C", 2, ServiceOffering.Name);
 }

 public void CreateErrorRows(WorksheetPart worksheetPart)
 {
     for (int rowIndex = 0; rowIndex < ErrorRows.Count; rowIndex++)
     {
         for (int i = 0; i < ErrorRows[rowIndex].RowErrors.Count; i++)
         {
             ExcelUtility.SetStringCell(worksheetPart.Worksheet, CellDictionary[i], (uint)rowIndex + 4, ErrorRows[rowIndex].RowErrors[i]);
         }
     }
 }

        private void AddReportColumns(WorkbookPart workbookPart, WorksheetPart worksheetPart, List<string> columnNames)
        {
            for (int i = 0; i < columnNames.Count; i++)
            {
                string fieldName = columnNames[i];

                WorkbookHelper.AddCellText(
                    workbookPart,
                    worksheetPart,
                    fieldName,
                    (uint)(i + 1), 1U);
            }
        }

        private WorksheetPart wsSheet = null; //  WorkSheetPart

        #endregion Fields

        #region Constructors

        public LogGenerator(string fn)
        {
            ////  get spreadsheet path from constructor
            //path = folder;
            ////  File name is based on date and time
            //DateTime now = DateTime.Now;
            ////  Construct the spreadsheet filename
            //string fn = string.Format("{0}\\report_{1}-{2}-{3}_{4}{5}{6}.xlsx",
            //    path, now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);
            if (File.Exists(fn))
            {
                doc = SpreadsheetDocument.Open(fn, true);
            }
            else
            {
                //
                //  Create the Spreadsheet document
                //
                doc = SpreadsheetDocument.Create(fn, SpreadsheetDocumentType.Workbook);
                //
                //  Add WoorkBookPart to the document
                //
                wbPart = doc.AddWorkbookPart();
                wbPart.Workbook = new Workbook();
                wbPart.Workbook.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                //
                //  Add WorkSheetPart to the WorkBookPart
                //
                wsSheet = wbPart.AddNewPart<WorksheetPart>();
                wsSheet.Worksheet = new Worksheet(new SheetData());
                wsSheet.Worksheet.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                //
                //  Add sheets to the WorkBook
                //
                sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                //
                //  Append new sheet and associate it with the workbook
                //
                sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsSheet), SheetId = 1, Name = wsName };
                sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                sheets.Append(sheet);

                wbPart.Workbook.Save();
                //CreatePackage(fn);

                //if (File.Exists(fn))
                //{
                //    TestXls();
                //}
            }
        }

 private void AddReportLines(WorkbookPart workbookPart, WorksheetPart worksheetPart, Report report, uint startRow)
 {
     for (int iRow = 0; iRow < report.ReportLines.Count; iRow++)
     {
         for (int iCol = 0; iCol < report.ColumnNames.Count; iCol++)
         {
             WorkbookHelper.AddCellText(
                 workbookPart,
                 worksheetPart,
                 report[iRow][iCol].ToString(),
                 (uint)iCol + 1, (uint)iRow + startRow + 1);
         }
     }
 }

        public static void SetCellValue(WorksheetPart worksheetPart, decimal? value, string columnName, DocumentFormat.OpenXml.Spreadsheet.Row row, out Cell cell)
        {
            cell = GetCell(worksheetPart.Worksheet, columnName, row);

            if (value.HasValue)
            {
                cell.CellValue = new CellValue(value.ToString());
                cell.DataType = new EnumValue<CellValues>(CellValues.Number);
            }
            else
            {
                cell.CellValue = new CellValue();
                cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);
            }
        }

        public static void InsertValuesInWorksheet(WorksheetPart worksheetPart, uint rowIdx, List<string> values)
        {
            var worksheet = worksheetPart.Worksheet;
            var sheetData = worksheet.GetFirstChild<SheetData>();

            Row row = new Row();
            values.ForEach(v =>
            {
                Cell cell = new Cell() { DataType = CellValues.InlineString };
                InlineString inlineString = new InlineString();
                inlineString.Append(new Text() { Text = v });
                cell.Append(inlineString);
                row.Append(cell);
            });
            sheetData.Append(row);
        }

        /// <summary>
        /// Creates a spreadsheet document with a chart 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="chartType">Chart type</param>
        /// <param name="categoryColumn">Column to use as category for charting</param>
        /// <param name="columnsToChart">Columns to use as data series</param>
        /// <param name="initialRow">Row index to start copying data</param>
        /// <returns>SpreadsheetDocument</returns>
        //public static void Create(SpreadsheetDocument document, List<string> headerList, string[][] valueTable, ChartType chartType, string categoryColumn, List<string> columnsToChart, int initialRow)
        //{
        //    headerRow = initialRow;

        //    //Creates worksheet with data
        //    WorksheetPart worksheet = WorksheetAccessor.Create(document, headerList, valueTable, headerRow);
        //    //Creates chartsheet with given series and category
        //    string sheetName = GetSheetName(worksheet, document);
        //    ChartsheetPart chartsheet =
        //        ChartsheetAccessor.Create(document,
        //            chartType,
        //            GetValueReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable),
        //            GetHeaderReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable),
        //            GetCategoryReference(sheetName, categoryColumn, headerList, valueTable)
        //        );
        //}

        /// <summary>
        /// Gets the internal name of a worksheet from a document
        /// </summary>
        private static string GetSheetName(WorksheetPart worksheet, SpreadsheetDocument document)
        {
            //Gets the id of worksheet part
            string partId = document.WorkbookPart.GetIdOfPart(worksheet);
            XDocument workbookDocument = document.WorkbookPart.GetXDocument();
            //Gets the name from sheet tag related to worksheet
            string sheetName =
                workbookDocument.Root
                .Element(ns + "sheets")
                .Elements(ns + "sheet")
                .Where(
                    t =>
                        t.Attribute(relationshipsns + "id").Value == partId
                ).First()
                .Attribute("name").Value;
            return sheetName;
        }

        public static void AddCellText(WorkbookPart workbookPart,
                                        WorksheetPart worksheetPart, 
                                        string text, 
                                        uint colIndex, 
                                        uint rowIndex)
        {
            string columnName = GetColumnIdentifier((int)colIndex);

            // Insert the text into the SharedStringTablePart.
            int index = InsertSharedStringItem(workbookPart, text);

            // Insert cell A1 into the new worksheet.
            Cell cell = InsertCellInWorksheet(worksheetPart, columnName, rowIndex);

            // Set the value of cell A1.
            cell.CellValue = new CellValue(index.ToString());
            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
        }

        private void CreateWorkSheet(WorksheetPart worksheetPart, DataGridView dgv)
        {
            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();

            UInt32Value currRowIndex = 1U;
            Row excelRow = new Row();
            foreach(DataGridViewColumn col in dgv.Columns)
            {
                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                CellValue cellValue = new CellValue();
                cellValue.Text = col.HeaderText;
                cell.Append(cellValue);
                excelRow.Append(cell);
            }
            sheetData.Append(excelRow);
            currRowIndex++;
            foreach (DataGridViewRow row in dgv.Rows)
            {
                excelRow = new Row();
                excelRow.RowIndex = currRowIndex++;
                foreach (DataGridViewCell col in row.Cells)
                {
                    Cell cell = new Cell();
                    CellValue cellValue = new CellValue();
                    cell.DataType = CellValues.String;
                    cellValue.Text = col.Value?.ToString();
                    cell.Append(cellValue);
                    excelRow.Append(cell);
                }
                sheetData.Append(excelRow);
            }

            SheetFormatProperties formattingProps = new SheetFormatProperties()
            {
                DefaultRowHeight = 20D,
                DefaultColumnWidth = 20D
            };

            worksheet.Append(formattingProps);
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;
        }