Monday 13 June 2016

Copy and Update Excel sheet with Dynamic values using cell ID

//Copy and Update Excel sheet with Dynamic values using cell ID

public string TemplateCreation()
{
string destinationFilePath = string.Format(Convert.ToString(ConfigurationManager.AppSettings[Constants.Configuration.DestinationFilePath]) + @"\{0}.xlsx", DateTime.Now.ToString(CultureInfo.InvariantCulture).Replace(":", string.empty).Replace("/", "_").Replace(" ", "_"));

            string sourceFilePath = Convert.ToString(ConfigurationManager.AppSettings[Constants.Configuration.SourceFilePath]);

            File.Copy(sourceFilePath, destinationFilePath);


            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(destinationFilePath, true))
            {
                WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, Constants.VendorFormConstants.Sheet1);
                if (worksheetPart != null)
                {
UpdateCellData(worksheetPart, Constants.CddSections.GCell, 2, "Value");


                    // Save the worksheet.
                    worksheetPart.Worksheet.Save();
                }
         }
 return destinationFilePath;
        }
 /// <summary>
        /// Update Cell Data
        /// </summary>
        /// <param name="worksheetPart"></param>
        /// <param name="cellColumn"></param>
        /// <param name="cellRow"></param>
        /// <param name="cellValue"></param>
        public void UpdateCellData(WorksheetPart worksheetPart, string cellColumn, uint cellRow, string cellValue)
        {
            Cell cddEntitiesValue = GetCell(worksheetPart.Worksheet, cellColumn, cellRow);
            cddEntitiesValue.CellValue = new CellValue(cellValue);
            cddEntitiesValue.DataType = new EnumValue<CellValues>(CellValues.String);
        }

        /// <summary>
        /// Get Celll Based WorkSheet, Column and Row Index
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="columnName"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
        {
            Row row = GetRow(worksheet, rowIndex);

            if (row == null)
                return null;

            return row.Elements<Cell>().First(c => String.Compare(c.CellReference.Value, columnName + rowIndex, StringComparison.OrdinalIgnoreCase) == 0);
        }

        /// <summary>
        /// Getting Work Sheet Row
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private Row GetRow(Worksheet worksheet, uint rowIndex)
        {
            return worksheet.GetFirstChild<SheetData>().Elements<Row>().First(r => r.RowIndex == rowIndex);
        }

        /// <summary>
        /// Getting worksheet based sheet in Document
        /// </summary>
        /// <param name="document"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
        {
            IEnumerable<Sheet> sheets =
               document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
               Elements<Sheet>().Where(s => s.Name == sheetName);



            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)
                 document.WorkbookPart.GetPartById(relationshipId);
            return worksheetPart;
        }




No comments:

Post a Comment