使用Open XML sdk创建超链接并在excel表中填充它 [英] Create hyperlink and populating it in excel sheet using Open XML sdk

问题描述

我正在尝试使用Open Office xml sdk填充Excel工作表中的超链接。

Excel工作表说明：

excel表包含3个表 -

•表1

•表2

•表3

表1和表3已包含一些数据和超链接。现在我想在excel表的表2中填充超链接和其他数据。



一旦我运行代码然后尝试打开excel表，我收到以下错误：



我们发现'Sheet.xlsx'中的某些内容存在问题。您是否希望我们尽可能多地恢复？如果您信任此工作簿的来源，请单击是。

单击是后，出现另一个错误：

Excel可以通过修复或删除不可读的内容来打开文件。

关闭此错误消息后，工作表数据变空。



然后我在excel表中进行了更改。

从表1和表3中删除了超链接，然后再次尝试在excel表的表2中填充数据和超链接。并且代码可以工作。



请建议我如何在excel表的表2中填充超链接，而不删除表1和表3中的超链接。



我使用以下代码：



//表单数据和超链接的方法

private bool PopulateHyperlink（SpreadsheetDocument myWorkbook）

{

#region变量

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;

行行;

DocumentFormat.OpenXml.UInt32Value sheetId;

SheetData sheetData;

var toolOffering = string.Empty;

List< tool> toolList = null;

超链接超链接1 =新的超链接（）;

//从web.config获取这些值

int ToolsColumn = 2;

int ToolsRow = 17;

int ToolsColumnValue = 2;

#endregion



尝试

{

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）

{

//从工具列表中删除重复记录

toolList = Project.Tools.GroupBy（p => p.Name）.Select（g => g.First（））。ToList（）;



//循环浏览项目工具

foreach（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;

//增加行以添加下一个工具信息。

ToolsRow ++;

i ++;

}



PageMargins pageMargins = worksheetPart.Worksheet.Descendants< pagemargins>（）。First（）;

worksheetPart.Worksheet.InsertBefore< ;超链接>（hyperlinks1，pageMargins）;

worksheetPart.Worksheet.Save（）;

}

success = true;

}

catch（exception ex）

{

抛出新的异常（填充信息时出现异常）。 + ex.Message）;

}



返回成功;

}



//创建超链接的方法

private Cell CreateHyperLink（SpreadsheetDocument myWorkbook，WorksheetPart worksheetPart，string url，string columnname，Cell cellName，int i，Hyperlinks hyperlinks，int行，字符串名称）

{

超链接超链接=新的超链接（）;

hyperlink.Reference = columnname + row;

hyperlink.Id =UNIQUE+ i;

hyperlinks.Append（超链接）;

worksheetPart.AddHyperlinkRelationship（new System.Uri（url，System。 UriKind.Absolute），true，hyperlink.Id）;



//在SheetData中创建一个元素

//获取SharedStringTablePart。如果它不存在，创建一个新的。

SharedStringTablePart shareStringPart;

if（myWorkbook.WorkbookPart.GetPartsOfType< sharedstringtablepart>（）。Count（）> 0）

{

shareStringPart = myWorkbook.WorkbookPart.GetPartsOfType< sharedstringtablepart>（）。First（）;

}

else

{

shareStringPart = myWorkbook.WorkbookPart.AddNewPart< sharedstringtablepart>（）;

}

//将文本插入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;

}



私有Cell GetCellInWorksheet（字符串columnName，uint rowIndex，WorksheetPart worksheetPart）

{

工作表workheet = worksheetPart.Worksheet;

SheetData sheetData = worksheet.GetFirstChild< sheetdata>（）;

string cellReference = columnName + rowIndex;

/ /如果工作表不包含具有指定行索引的行，请插入一行。

行行;

if（sheetData.Elements< row>（）。其中​​（ r => r.RowI ndex == rowIndex）.Count（）！= 0）

{

row = sheetData.Elements< row>（）。其中​​（r => r.RowIndex == rowIndex）.First（）;

}

else

{

row = new Row（）{RowIndex = rowIndex};

sheetData.Append（row）;

}

//如果没有包含指定的列名，插入一个。

if（row.Elements< cell>（）。Where（c => c.CellReference.Value == columnName + rowIndex）.Count（）> 0）

{

返回row.Elements< cell>（）。其中​​（c => c.CellReference.Value == cellReference）.First（）;

}

else

{

//根据CellReference，单元格必须按顺序排列。确定插入新单元格的位置。

Cell refCell = null;

foreach（行中的单元格单元格< cell>（））

{

if（string.Compare（cell.CellReference.Value，cellReference，true）> 0）

{

refCell = cell ;

休息;

}

}

Cell newCell = new Cell（）{CellReference = cellReference};

row.InsertBefore（newCell，refCell）;

worksheet.Save（）;

返回newCell;

}

}



私有字符串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）））;

}



返回retVal.ToString（）;

}

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

推荐答案

这篇关于使用Open XML sdk创建超链接并在excel表中填充它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！