Hello,
I am extracting data from database and adding them to Excel sheet, my data as below:
ID, Name, Description, CV Link
and already extracted the CV's to be in physical folder, and I wrote in the excel sheet the data even link for CV in physical folder.
My problem how can I make the this Link clickable link hyperlink.
BR,
A.Ragab
What I have tried:
public void WriteEmployeesMetadata(List<Employee> employees)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(spreadsheetDocumentPath, true))
{
double maxRowsPerSheet = Constants.maxRowsPerSheet;
double numberOfSheets = Math.Ceiling(employees.Count / maxRowsPerSheet);
int maxRowsPerSheetParam = int.Parse(maxRowsPerSheet.ToString());
List<Employee> containerEmployeeList = new List<List<Employee>>();
for (int x = 0; x < numberOfSheets; x++)
{
List<Employee> newEmployee = employees.Skip(x * maxRowsPerSheetParam).Take(maxRowsPerSheetParam).ToList();
containerEmployeeList.Add(newEmployee);
}
var workbookPart = document.WorkbookPart;
var workbook = workbookPart.Workbook = new Workbook();
var sheets = workbook.AppendChild<Sheets>(new Sheets());
for (int i = 0; i < containerEmployeeList.Count; i++)
{
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = (UInt32)i + 1, Name = "Employee " + (i + 1) };
sheets.Append(sheet);
using (var writer = OpenXmlWriter.Create(worksheetPart))
{
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
writer.WriteStartElement(new Row());
int headerIndex = 1;
Extensions.InsertHeaderCellsInRow(writer, 1);
writer.WriteEndElement();
for (int j = 0; j < containerEmployeeList[i].Count(); j++)
{
writer.WriteStartElement(new Row());
Extensions.InsertEmployeeCellsInRow(writer, containerEmployeeList[i][j], j + headerIndex + 1);
writer.WriteEndElement();
Console.WriteLine(j + 1 + " written with name " + containerEmployeeList[i][j].Number + " from " + containerEmployeeList[i].Count);
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
}
}
}
}
public static void InsertEmployeeCellsInRow(OpenXmlWriter writer, Employee Employee, int index)
{
string iDCellReference = Constants.IDColumnName + index;
InsertCellInWorksheet(writer, iDCellReference, Employee.ID.ToString());
string nameCellReference = Constants.NameColumnName + index;
InsertCellInWorksheet(writer, nameCellReference, Employee.Name.ToString());
string descriptionCellReference = Constants.descriptionColumnName + index;
InsertCellInWorksheet(writer, descriptionCellReference, Employee.Description.ToString());
if (!string.IsNullOrEmpty(Employee.PhysicalFileLink))
{
string physicalFileLinkCellReference = Constants.PhysicalFileLinkColumnName + index;
InsertCellInWorksheet(writer, physicalFileLinkCellReference, Employee.PhysicalFileLink.ToString());
}
}
public static void InsertCellInWorksheet(OpenXmlWriter writer, string linkTypeCellReference, string cellValue, List<OpenXmlAttribute> attributes = null)
{
if (attributes == null)
{
attributes = new List<OpenXmlAttribute>();
}
attributes.Add(new OpenXmlAttribute("t", null, "inlineStr"));
writer.WriteStartElement(new Cell() { CellReference = linkTypeCellReference }, attributes);
writer.WriteElement(new InlineString(new Text(cellValue)));
writer.WriteEndElement();
}