Create hyperlink and populating it in excel sheet using Open XML sdk

See more:

C#

OpenXML

I am trying to populate hyperlink in excel sheet using Open Office xml sdk.
Description of Excel sheet:
The excel sheet contains 3 tables –
• Table 1
• Table 2
• Table 3
Table 1 and Table 3 already contains some data along with hyperlinks. Now I want to populate hyperlink and other data in table 2 of excel sheet.

Once i run the code and then tried to open excel sheet, i am getting following error:

We found a problem with some content in 'Sheet.xlsx'. Do you want us to try to recover as much as we can?If you trust the source of this workbook, click Yes.
After click on Yes, getting another error:
Excel was able to open the file by repairing or removing the unreadable content.
And after closing this error message, sheet data gets empty.

Then I made changes in excel sheet.
Removed hyperlink from Table 1 and Table 3 and then again tried to populate data and hyperlink in table 2 of excel sheet. And the code works.

Please suggest how I can populate hyperlinks in table 2 of excel sheet without removing hyperlinks from table 1 and table 3.

I am using following code:

//Method to sheet data and hyperlink
private bool PopulateHyperlink(SpreadsheetDocument myWorkbook)
{
#region Variables
bool success = false;
Cell toolcelloffering = null;
Cell toolcellvendor = null;
Cell toolcellname = null;
Cell toolcellfunction = null;
Sheet sheet = null;
WorksheetPart worksheetPart = null;
string columnname = string.Empty;
string toolUrl = string.Empty;
int i = 1;
Row row;
DocumentFormat.OpenXml.UInt32Value sheetId;
SheetData sheetData;
var toolOffering = string.Empty;
List<tool> toolList = null;
Hyperlinks hyperlinks1 = new Hyperlinks();
//Get these values from web.config
int ToolsColumn=2;
int ToolsRow=17;
int ToolsColumnValue=2;
#endregion

try
{
sheet = myWorkbook.WorkbookPart.Workbook.GetFirstChild<sheets>().Elements<sheet>().First(s => s.Name.Value.Equals("SheetName", StringComparison.OrdinalIgnoreCase));
worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(sheet.Id.Value);
sheetData = worksheetPart.Worksheet.GetFirstChild<sheetdata>();

if (Project.Tools != null)
{
//removing duplicate records from Tools list
toolList = Project.Tools.GroupBy(p => p.Name).Select(g => g.First()).ToList();

//Loop through the Tools of the project
foreach (Tool tool in toolList)
{
toolUrl = tool.URL;

columnname = GetColumnName(ToolsColumn);
toolcellname = GetCellInWorksheet(columnname, Convert.ToUInt32(17), worksheetPart);
toolcellname.CellValue = new CellValue(tool.Name.Replace("amp;", string.Empty));
toolcellname.DataType = new EnumValue<cellvalues>(CellValues.String);
toolcellname.StyleIndex = 130;

if (!string.IsNullOrEmpty(toolUrl))
{
toolcellname = CreateHyperLink(myWorkbook, worksheetPart, toolUrl, columnname, toolcellname, i, hyperlinks1, ToolsRow, tool.Name);
columnname = string.Empty;
}
ToolsColumn++;

ToolsColumn = ToolsColumnValue;
//Increment the row to add the next tool information.
ToolsRow++;
i++;
}

PageMargins pageMargins = worksheetPart.Worksheet.Descendants<pagemargins>().First();
worksheetPart.Worksheet.InsertBefore<hyperlinks>(hyperlinks1, pageMargins);
worksheetPart.Worksheet.Save();
}
success = true;
}
catch (Exception ex)
{
throw new Exception("Exception while populating the information. " + ex.Message);
}

return success;
}

//Method to create hyperlink
private Cell CreateHyperLink(SpreadsheetDocument myWorkbook, WorksheetPart worksheetPart, string url, string columnname, Cell cellName, int i, Hyperlinks hyperlinks, int row, string name)
{
Hyperlink hyperlink = new Hyperlink();
hyperlink.Reference = columnname + row;
hyperlink.Id = "UNIQUE" + i;
hyperlinks.Append(hyperlink);
worksheetPart.AddHyperlinkRelationship(new System.Uri(url, System.UriKind.Absolute), true, hyperlink.Id);

// Create an element in SheetData
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (myWorkbook.WorkbookPart.GetPartsOfType<sharedstringtablepart>().Count() > 0)
{
shareStringPart = myWorkbook.WorkbookPart.GetPartsOfType<sharedstringtablepart>().First();
}
else
{
shareStringPart = myWorkbook.WorkbookPart.AddNewPart<sharedstringtablepart>();
}
// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(name.Replace("amp;", string.Empty), shareStringPart);
cellName = GetCellInWorksheet(columnname, Convert.ToUInt32(row), worksheetPart);
cellName.CellValue = new CellValue(index.ToString());
cellName.DataType = new EnumValue<cellvalues>(CellValues.SharedString);
cellName.StyleIndex = 134;
return cellName;
}

private Cell GetCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<sheetdata>();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (sheetData.Elements<row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements<cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}

private string GetColumnName(int columnNumber)
{
StringBuilder retVal = new StringBuilder();
int x = 0;

for (int n = (int)(Math.Log(25 * (columnNumber + 1)) / Math.Log(26)) - 1; n >= 0; n--)
{
x = (int)((Math.Pow(26, (n + 1)) - 1) / 25 - 1);
if (columnNumber > x)
retVal.Append(System.Convert.ToChar((int)(((columnNumber - x - 1) / Math.Pow(26, n)) % 26 + 65)));
}

return retVal.ToString();
}