// Given a WorkbookPart, inserts a new worksheet.
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new sheet.
            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();

            return newWorksheetPart;
        }

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

        public static string GetCellValue(WorkbookPart wbPart, Cell theCell)
        {
            string value = theCell.InnerText;
            if (theCell.DataType != null)
            {
                switch (theCell.DataType.Value)
                {
                    case CellValues.SharedString:
                        var stringTable = wbPart.
                          GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                        if (stringTable != null)
                        {
                            value = stringTable.SharedStringTable.
                              ElementAt(int.Parse(value)).InnerText;
                        }
                        break;

                    case CellValues.Boolean:
                        switch (value)
                        {
                            case "0":
                                value = "FALSE";
                                break;
                            default:
                                value = "TRUE";
                                break;
                        }
                        break;
                }
            }
            return value;
        }

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

 public static Sheet FindSheet(string sheetName, WorkbookPart workbookPart)
 {
     if (string.IsNullOrEmpty(sheetName))
     {
         return workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
     }
     return workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();
 }

 /// <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 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 static SharedStringItem GetSharedStringValue(WorkbookPart workbookPart, Cell cell, ref int id)
 {
     if (int.TryParse(cell.InnerText, out id))
     {
         var text = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
         return text;
     }
     return null;
 }

 public static WorksheetPart GetWorksheetFromSheetName(WorkbookPart workbookPart, string sheetName)
 {
     var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
     if (sheet == null)
     {
         return null;
     }
     return workbookPart.GetPartById(sheet.Id) as WorksheetPart;
 }

        static bool AddCellUnit(int columnIndex, ref int exportColumnIndex, SheetConfig Sheetcfg, Cell cell, WorkbookPart workbookPart, List<string> stylesList, List<CellUnit> cellUnitList)
        {
            if (columnIndex >= Sheetcfg.ColumnConfigData.Count)
                goto Error;

            var columnConfig = Sheetcfg.ColumnConfigData[columnIndex];
            if (!columnConfig.Export)
                return true;

            var dataType = columnConfig.FieldType;
            string cellValue = cell == null ? "" : GetCellValue(cell, workbookPart, stylesList);
            CellUnit cellUnit = null;
            switch (dataType)
            {
                case DesignerDataType.Int:
                    {
                        int intValue = 0;
                        if (!int.TryParse(cellValue, out intValue) && !string.IsNullOrEmpty(cellValue))
                            goto Error;
                        cellUnit = new CellUnitIntValue(exportColumnIndex, intValue);
                    }
                    break;
                case DesignerDataType.Float:
                    {
                        float floatValue = 0.0f;
                        if (!float.TryParse(cellValue, out floatValue) && !string.IsNullOrEmpty(cellValue))
                            goto Error;
                        cellUnit = new CellUnitFloatValue(exportColumnIndex, floatValue);
                    }
                    break;
                case DesignerDataType.String:
                    {
                        cellUnit = new CellUnitStringValue(exportColumnIndex, cellValue);
                    }
                    break;
                case DesignerDataType.Bool:
                    {
                        decimal decimalValue = 0;
                        cellUnit = new CellUnitBoolValue(exportColumnIndex, decimal.TryParse(cellValue, out decimalValue) && decimalValue != 0);
                    }
                    break;
            }

            if (cellUnit.IsNeedWrite())
                cellUnitList.Add(cellUnit);
            ++exportColumnIndex;

            return true;

            Error:
            string excelFileName = "";
            if (!dicOutputFileName.TryGetValue(Sheetcfg.Name, out excelFileName))
                excelFileName = "";
            Console.Error.WriteLine(string.Format(Resources.ExcelConfigFileInValid, excelFileName, Sheetcfg.Name));
            return false;
        }

 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;
 }

        protected string GetCellValue(WorkbookPart wbPart, Cell theCell)
        {
            if (theCell == null)
            {
                return null;
            }

            var value = theCell.InnerText;
            if (theCell.DataType == null)
            {
                return value;
            }

            switch (theCell.DataType.Value)
            {
                case (CellValues.SharedString):
                {
                    var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                    if (stringTable != null)
                    {
                        value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                    }
                    break;
                }
                case (CellValues.Boolean):
                {
                    switch (value)
                    {
                        case "0":
                        {
                            value = "FALSE";
                            break;
                        }
                        default:
                        {
                            value = "TRUE";
                            break;
                        }
                    }
                    break;
                }
                case (CellValues.Date):
                {
                    if (!string.IsNullOrEmpty(value))
                    {
                        value = DateTime.FromOADate(Convert.ToDouble(value)).ToString(CultureInfo.CurrentCulture);
                    }
                    break;
                }
            }

            return value;
        }

        // Generates content of workbookPart1. 
        private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
            sheets1.Append(sheet1);

            workbook1.Append(sheets1);
            workbookPart1.Workbook = workbook1;
        }

        public List<RPCFan> RetrieveFirstObject(WorkbookPart wbPart, Sheet theSheet)
        {
            string abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            WorksheetPart wsPart =
                (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
            Cell theCell = wsPart.Worksheet.Descendants<Cell>().
              Where(c => c != null).FirstOrDefault();
            string first = FindCellInnerValue(wbPart, theCell);
            var letra = first.Substring(0, 1);
            int numeros = int.Parse(first.Substring(1));
            int i = abc.IndexOf(letra);
            List<RPCFan> retorno = new List<RPCFan>();
            int countFans = 0;
            int numeroscopy = numeros;
            while (theCell != null)
            {
                string newRef = abc[i].ToString() + (numeroscopy++ + 1).ToString();
                theCell = wsPart.Worksheet.Descendants<Cell>().
              Where(c => c.CellReference == newRef).FirstOrDefault();
                countFans++;
            }
            for (int ii = 0; ii < (countFans - 1); ii++)
            {
                string newRef;
                i = abc.IndexOf(letra);
                theCell = new Cell();
                var fan = new RPCFan();
                List<string> data = new List<string>();
                while (theCell != null)
                {
                    newRef = abc[i++].ToString() + (numeros + 1).ToString();
                    theCell = wsPart.Worksheet.Descendants<Cell>().
                  Where(c => c.CellReference == newRef).FirstOrDefault();
                    var value = FindCellInnerValue(wbPart, theCell, false);
                    data.Add(value);
                    //i++;
                }
                fan.YaEntro = false;
                fan.LastCell = abc[i-2].ToString() + (numeros + 1).ToString();
                fan.ID = data[0];
                fan.Nombre = data[1];
                fan.CardNum = data[2];
                fan.DNI = data[3];
                fan.Ingreso = data[4];
                if (data[5]!="0")
                    fan.YaEntro = true;

                retorno.Add(fan);
                numeros++;
            }

            return retorno;
        }

		private string ReadExcelCell(Cell cell, WorkbookPart workbookPart)
		{
			var cellValue = cell.CellValue;
			var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
			if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
			{
				text = workbookPart.SharedStringTablePart.SharedStringTable
					.Elements<SharedStringItem>().ElementAt(
						Convert.ToInt32(cell.CellValue.Text)).InnerText;
			}

			return (text ?? string.Empty).Trim();
		}

        private static Dictionary<int, string> GetHeaders(SheetData sheetData, WorkbookPart workbookPart)
        {
            var headers = new Dictionary<int, string>();

            var headerCells = sheetData.Elements<Row>().First().Elements<Cell>().ToList();

            for (var i = 0; i < headerCells.Count; i++)
            {
                var cellValue = ExcelFileInceptor.GetCellValueAsString(workbookPart, headerCells[i]);
                headers.Add(i, cellValue);
            }
            return headers;
        }

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

 /// <summary>
 /// 根据WorkbookPart获取所有SheetName
 /// </summary>
 /// <param name="workBookPart"></param>
 /// <returns>SheetName集合</returns>
 private List<string> GetSheetNames(WorkbookPart workBookPart)
 {
     List<string> sheetNames = new List<string>();
     Sheets sheets = workBookPart.Workbook.Sheets;
     foreach (Sheet sheet in sheets)
     {
         string sheetName = sheet.Name;
         if (!string.IsNullOrEmpty(sheetName))
         {
             sheetNames.Add(sheetName);
         }
     }
     return sheetNames;
 }

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