Thirdletter_CutsClean
Reputation: 111
Adding href to Excel Export C#
I need help with this issue, I'm exporting my datatable into an excel file which works fine. the problem is I'm trying to make one of the columns a hyperlink to a document but as it starts to build out the excel file I get this error. Thank you in advance for your help.
'This operation is not supported for a relative 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();
}
}
Upvotes: 2
Views: 6565
Answers (3)
venkat
Reputation: 11
Finally able to fix my issue ..Thanks a lot Using Excelv As New 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
Upvotes: 1
Thirdletter_CutsClean
Reputation: 111
Here is the fixed and working solution, Thanks Mike for helping get me pointed in the right direction
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();
}
}
Upvotes: 1
Mike Christensen
Reputation: 91618
Excel simply doesn't support relative URLs, as an Excel document doesn't have a URL to be relative to. You'll need to fully qualify all your URLs:
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"];
Note the fully qualified URL, complete with http:// as well as the UriKind.Absolute.
Upvotes: 4
Related Questions
- Export DataTable to Excel File
- Adding Hyperlinks Through Code To Excel
- How to add hyperlink to Excel's cells with C#
- Exporting a hyperlink field to Excel not showing
- Exporting DataTable to Excel
- Export DataTable to Excel File in Webpage
- Export DataTable to Excel asp
- Export data from DataTable to Excel file
- Export DataTable to Excel File in asp.net
- Inserting hyperlink in the DataTable C#