将 href 添加到 Excel Export C#
本文关键字：Export Excel href 添加 | 更新日期: 2023-09-27 18:32:40
我需要解决此问题的帮助，我正在将我的数据表导出到一个工作正常的 excel 文件中。 问题是我正在尝试使其中一列成为指向文档的超链接，但是当它开始构建Excel文件时，我收到此错误。提前感谢您的帮助。
"相对 URI 不支持此操作。"
using OfficeOpenXml;
using OfficeOpenXml.Table;
public void ExportExcelFile(System.Data.DataTable excelData, string excelSheetName)
{
using (var package = new ExcelPackage())
{
ExcelWorksheet ws = package.Workbook.Worksheets.Add(excelSheetName);
int rowCount = 1;
foreach (DataRow rw in excelData.Rows)
{
//excelData.Columns.RemoveAt(0);
rowCount += 1;
for (int i = 1; i < excelData.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
ws.Row(1).Style.Font.Bold = true;
ws.Cells[1, i].Value = excelData.Columns[i - 1].ColumnName;
}
ws.Column(i).AutoFit();
//THIS IS THTE PROBLEM
string hyperlink = "<a href='~/Documents/DownloadFile.aspx?id=" + rw["DocPk"].ToString() + "'>" + rw["Drawing Id"].ToString() + "</a>";
ws.Cells[i, 7].Hyperlink = new Uri(hyperlink, UriKind.RelativeOrAbsolute);
ws.Cells[rowCount, i].Value = rw[i - 1].ToString();
}
}
MemoryStream Result = new MemoryStream();
package.SaveAs(Result);
Response.ClearContent();
Response.OutputStream.Write(Result.GetBuffer(), 0, Result.GetBuffer().Length);
Response.OutputStream.Flush();
Response.OutputStream.Close();
byte[] byteArray = Result.ToArray();
Response.AddHeader("Content-Disposition", "attachment; filename=" + excelSheetName + ".xlsx");
Response.AddHeader("Content-Length", byteArray.Length.ToString());
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(byteArray);
Response.End();
}
}
Excel 根本不
支持相对 URL，因为 Excel 文档没有要相对的 URL。 您需要完全限定所有网址：
string hyperlink = String.Format("http://{0}/Documents/DownloadFile.aspx?id={1}",
Page.Request.Url.Host,
rw["DocPk"].ToString());
ws.Cells[rowCount, i].Hyperlink = new Uri(hyperlink, UriKind.Absolute);
ws.Cells[rowCount, i].Value = rw["Drawing Id"];
请注意完全限定的 URL，包括 http:// 和UriKind.Absolute。
这是固定和有效的解决方案，感谢迈克帮助我指出正确的方向
public void ExportExcelFile(System.Data.DataTable excelData, string excelSheetName)
{
using (var package = new ExcelPackage())
{
ExcelWorksheet ws = package.Workbook.Worksheets.Add(excelSheetName);
HtmlAnchor lnkOpen = new HtmlAnchor();
int rowCount = 1;
foreach (DataRow rw in excelData.Rows)
{
rowCount += 1;
for (int i = 1; i < excelData.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
ws.Row(1).Style.Font.Bold = true;
ws.Cells[1, i].Value = excelData.Columns[i - 1].ColumnName;
}
ws.Column(i).AutoFit();
if (excelData.Columns[i - 1].ColumnName.ToString() == "Drawing Id" )
{
//var hyperlink = String.Format("Http://" + Page.Request.Url.Host + "/Documents/DownloadFile.aspx?id={0}", rw["DocPk"].ToString());
var hyperlink = String.Format(ConfigurationManager.AppSettings["DocumentManagerURL"] + "Documents/DownloadFile.aspx?id={0}", rw["DocPk"].ToString());
ws.Cells[rowCount, i].Hyperlink = new Uri(hyperlink, UriKind.Absolute);
ws.Cells[rowCount, i].Style.Font.UnderLine = true;
ws.Cells[rowCount, i].Style.Font.Color.SetColor(System.Drawing.Color.Blue);
ws.Cells[rowCount, i].Value = rw["Drawing Id"];
}
else{
ws.Cells[rowCount, i].Value = rw[i - 1].ToString();
}
}
//excelData.Columns.RemoveAt(7);
}
MemoryStream Result = new MemoryStream();
package.SaveAs(Result);
Response.ClearContent();
Response.OutputStream.Write(Result.GetBuffer(), 0, Result.GetBuffer().Length);
Response.OutputStream.Flush();
Response.OutputStream.Close();
byte[] byteArray = Result.ToArray();
Response.AddHeader("Content-Disposition", "attachment; filename=" + excelSheetName + ".xlsx");
Response.AddHeader("Content-Length", byteArray.Length.ToString());
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(byteArray);
Response.End();
}
}
终于能够解决我的问题了..多谢 使用 Excelv 作为新的 ExcelPackage（）
Dim ws As ExcelWorksheet = Excelv.Workbook.Worksheets.Add(fileName)
Dim rowCount As Integer = 1
For Each rw As DataRow In dddd.Rows
'excelData.Columns.RemoveAt(0);
rowCount += 1
For i As Integer = 1 To dddd.Columns.Count
' Add the header the first time through
If rowCount = 2 Then
'ws.Row(1).Style.Fill.BackgroundColor.SetColor(Color.Azure)
ws.Row(1).Style.Font.Bold = True
'ws.Row(1).Style.Fill.BackgroundColor.SetColor(Color.AliceBlue)
' ws.Row(1).Style.Color = "#FFFFFF"
If dddd.Columns(i - 1).ColumnName = ("ID") Then
'ws.Cells(1, i).Value = dddd.Columns(i - 1).ColumnName
ElseIf dddd.Columns(i - 1).ColumnName = ("Plant_Description") Then
ws.Cells(1, i).Value = "Plant"
ElseIf dddd.Columns(i - 1).ColumnName = ("Report_No") Then
ws.Cells(1, i).Value = "Scrap Number"
ElseIf dddd.Columns(i - 1).ColumnName = ("Req_Emp_Name") Then
ws.Cells(1, i).Value = "Originator"
ElseIf dddd.Columns(i - 1).ColumnName = ("ScrapItem_Desc") Then
ws.Cells(1, i).Value = "Item"
ElseIf dddd.Columns(i - 1).ColumnName = ("Info_ID") Then
ws.Cells(1, i).Value = "NO"
ElseIf dddd.Columns(i - 1).ColumnName = ("Itemdesc") Then
ws.Cells(1, i).Value = "ItemDesc"
ElseIf dddd.Columns(i - 1).ColumnName = ("Part_No") Then
ws.Cells(1, i).Value = "Part No"
ElseIf dddd.Columns(i - 1).ColumnName = ("quantity") Then
ws.Cells(1, i).Value = "Quantity"
ElseIf dddd.Columns(i - 1).ColumnName = ("STD_Cost") Then
ws.Cells(1, i).Value = "UnitPrice"
ElseIf dddd.Columns(i - 1).ColumnName = ("Total_Cost") Then
ws.Cells(1, i).Value = "UnitTotal"
ElseIf dddd.Columns(i - 1).ColumnName = ("Close_DateTime") Then
ws.Cells(1, i).Value = "Close Date"
ElseIf dddd.Columns(i - 1).ColumnName = ("Doc_Create_Date") Then
ws.Cells(1, i).Value = "Submit Date"
Else
ws.Cells(1, i).Value = dddd.Columns(i - 1).ColumnName
End If
End If
ws.Column(i).AutoFit()
'ws.Column("ID").Hidden = True
'THIS IS THTE PROBLEM
'Dim hyperlink As String = "<a href='~/Documents/DownloadFile.aspx?id=" + rw("ID").ToString() + "'>" + rw("ID").ToString() + "</a>"
'ws.Cells(i, 7).Hyperlink = New Uri(hyperlink, UriKind.RelativeOrAbsolute)
'ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
'Dim hyperlink As String = [String].Format("http://{0}/Documents/DownloadFile.aspx?id={1}", Page.Request.Url.Host, rw("ID").ToString())
'ws.Cells(rowCount, i).Hyperlink = New Uri(hyperlink, UriKind.Absolute)
'ws.Cells(rowCount, i).Value = rw("Drawing Id")
If dddd.Columns(i - 1).ColumnName.ToString() = "Month" Then
'var hyperlink = String.Format("Http://" + Page.Request.Url.Host + "/Documents/DownloadFile.aspx?id={0}", rw["DocPk"].ToString());
Dim hyperlink = [String].Format("your url" + "{0}", rw("ID").ToString())
ws.Cells(rowCount, i).Hyperlink = New Uri(hyperlink, UriKind.RelativeOrAbsolute)
ws.Cells(rowCount, i).Style.Font.UnderLine = True
ws.Cells(rowCount, i).Style.Font.Color.SetColor(System.Drawing.Color.Blue)
ws.Cells(rowCount, i).Value = rw("Month")
Else
If dddd.Columns(i - 1).ColumnName = ("ID") Then
ElseIf dddd.Columns(i - 1).ColumnName = ("Plant_Description") Then
ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
ElseIf dddd.Columns(i - 1).ColumnName = ("Report_No") Then
ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
ElseIf dddd.Columns(i - 1).ColumnName = ("Req_Emp_Name") Then
ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
ElseIf dddd.Columns(i - 1).ColumnName = ("ScrapItem_Desc") Then
ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
ElseIf dddd.Columns(i - 1).ColumnName = ("Info_ID") Then
ws.Cells(rowCount, i).Value = Convert.ToInt32(rw(i - 1).ToString())
ElseIf dddd.Columns(i - 1).ColumnName = ("Itemdesc") Then
ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
ElseIf dddd.Columns(i - 1).ColumnName = ("Part_No") Then
ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
ElseIf dddd.Columns(i - 1).ColumnName = ("quantity") Then
ws.Cells(rowCount, i).Value = Convert.ToDecimal(rw(i - 1).ToString())
ElseIf dddd.Columns(i - 1).ColumnName = ("STD_Cost") Then
ws.Cells(rowCount, i).Value = Convert.ToDecimal(rw(i - 1).ToString())
ElseIf dddd.Columns(i - 1).ColumnName = ("Total_Cost") Then
ws.Cells(rowCount, i).Value = Convert.ToDecimal(rw(i - 1).ToString())
ElseIf dddd.Columns(i - 1).ColumnName = ("Close_DateTime") Then
ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
ElseIf dddd.Columns(i - 1).ColumnName = ("Doc_Create_Date") Then
ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
Else
ws.Cells(1, i).Value = dddd.Columns(i - 1).ColumnName
End If
'ws.Cells(rowCount, i).Value = rw(i - 1).ToString()
End If
Next
Next
Dim Result As New MemoryStream()
Excelv.SaveAs(Result)
Response.ClearContent()
Response.OutputStream.Write(Result.GetBuffer(), 0, Result.GetBuffer().Length)
Response.OutputStream.Flush()
Response.OutputStream.Close()
Dim byteArray As Byte() = Result.ToArray()
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx")
Response.AddHeader("Content-Length", byteArray.Length.ToString())
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.BinaryWrite(byteArray)
Response.[End]()
End Using