//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