ASP.NET

Re: Is is possible to import an excel file with hyperlinks?

16-Jul-23 21:45

Without seeing your code it is impossible to guess what may be the problem.

samflex17-Jul-23 5:26

Re: Is is possible to import an excel file with hyperlinks?

17-Jul-23 5:26

There is no code sir.

I am just asking about importing excel with hyperlinks to sql server or asp.net applications.

Richard MacCutchan17-Jul-23 5:29

Re: Is is possible to import an excel file with hyperlinks?

17-Jul-23 5:29

Well you need to explain exactly how you are doing it and what actually happens when you do. We cannot be expected to guess what is going on.

samflex18-Jul-23 3:43

Re: Is is possible to import an excel file with hyperlinks?

18-Jul-23 3:43

LOL,

The only way I have done import from excel to SQL Server is to use the SQL Server import utility.

It always works. If you have imported files before to sql server, then nothing to guess there.

The only issue this time around is that when I imported the file, the hyperlinks on the values for one of the columns was removed.

Normally, this is not an issue for anyone who has encountered this type of problem.

All I have asked for is whether anyone has had similar issue and if yes, how did they resolved it.

Nothing really complicated about my question.

If I Have a code and I am having problem making it work, I post the code and ask for help which I have done many times here.

Richard MacCutchan18-Jul-23 4:40

Re: Is is possible to import an excel file with hyperlinks?

18-Jul-23 4:40

samflex wrote:
The only way I have done import from excel to SQL Server is to use the SQL Server import utility.

Which you omitted to mention in your original question, leaving us in the dark as to how you were doing it.

samflex wrote:
nothing to guess there.

Actually everything to guess.

samflex wrote:
Nothing really complicated about my question.

That's a matter of opinion.

Did you check Import data from Excel to SQL Server or Azure SQL Database - SQL Server | Microsoft Learn[^] ?

RedDk18-Jul-23 7:19

RedDk

18-Jul-23 7:19

Sounds like someone who knows what the BCP Utility is because they use it regularly in their procedural code should be able to tell you why using various commandline options does what they're supposed to do ... or doesn't do what they're supposed to do.

I think the direct object of that sentence is "using" ... so yeah, does/doesn't ...

modified 18-Jul-23 14:25pm.

Re: Is is possible to import an excel file with hyperlinks?

Andre Oosthuizen16-Jul-23 23:59

Andre Oosthuizen

16-Jul-23 23:59

With no language, no code and very little information, the following might help, only as a shot in the dark -
SSIS Extract links from Excel cells to load into SQL [^]

Re: Is is possible to import an excel file with hyperlinks?

Richard Andrew x6424-Jul-23 3:19

Richard Andrew x64

24-Jul-23 3:19

Hyperlinks are a feature of Excel. They are not a data type.

You can't store an Excel hyperlink in a database column. You can store the target address of a hyperlink, but not a hyperlink.

Is it the target address of the hyperlink that you are looking to store?

And what does show up in the SQL table when you import the file?

The difficult we do right away...
...the impossible takes slightly longer.

Re: Is is possible to import an excel file with hyperlinks?

samflex24-Jul-23 6:34

Ali Al Omairi(Abu AlHassan)

24-Jul-23 6:34

Hi,

I suppose that when you say target address of a hyperlink, you meant the location you are taking when you click on a hyperlink?

Here is what we are trying to accomplish.

We have directory on our server where several documents are stored.

Then the excel file contains hyperlinks to those documents.

For instance, one of the columns, called Director for example, the Director is hyperlinked Director.

On the excel file, it is clearly hyperlinked and when clicked, takes you to the Files directory and Director file.

There are over 600 of these records on excel.

What we are trying to do is import the contents of the excel file to the database then use our .net app to display these records on our site so users can visit the site search for any title info, find it, cick the hyperlink to take him/her to the file on the server.

So, after importing the file to the database, all you see now is the text Director. The hyperlink is stripped away.

Illustrating ad emails

Ali Al Omairi(Abu AlHassan)10-Jul-23 2:35

10-Jul-23 2:35

Hi guys
I am writing a tool for meeting management in my organization so I use ews managed api to call the exchange server
What I need to do is showing the ad user emails just like when you press to button in the outlook
Anyone can help me with this

Help people,so poeple can help you.

Advise on how to reuse code behind in asp.net VB pages

Member 875830229-Jun-23 16:25

Member 8758302

29-Jun-23 16:25

Hallo

I have about 100 asp.net forms. I have a master page and many pages that refer to the master. The .aspx pages differ, but the .aspx.vb codebehind of all is essentially identical. The .aspx_vb has some parameter declarations and constants at the top, but the remainder of the .aspx_vb is identical generalised code - handling events like page_init, page_load etc.

Obviously this is inefficient to maintain.

Can someone offer advice of what techniques I should investigate to be able to centralise the common code into one code base and maintain it once.

I have written extensions to existing controls previously - for example I have written extensions that inherit GridView and DropDownList. I also use User Controls extensively.

Can someone point me to examples or articles to assist me solve this problem?

thanks, Grant

Re: Advise on how to reuse code behind in asp.net VB pages

Andre Oosthuizen3-Jul-23 0:49

Andre Oosthuizen

3-Jul-23 0:49

You can use Classes that are essential in object-oriented programming. These will have functions that you can call to in any of your pages. I found numerous tutorials by searching writing and using classes in aspx [^]

Re: Advise on how to reuse code behind in asp.net VB pages

Member 87583024-Jul-23 3:04

Member 8758302

4-Jul-23 3:04

Thanks Andre

Re: Advise on how to reuse code behind in asp.net VB pages

Andre Oosthuizen4-Jul-23 3:40

Andre Oosthuizen

4-Jul-23 3:40

You're welcome.

Re: Advise on how to reuse code behind in asp.net VB pages

Gerry Schmitz3-Jul-23 4:54

Gerry Schmitz

3-Jul-23 4:54

The "easiest" way (IMO) to get some reuse is to identify methods that can be made static; with ease; i.e. don't have dependencies that can't be easily made into "parms".

Those can be put in a static class / dll and shared that way. I have a lot of "builders" and "adapters" in that category.

"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I

Re: Advise on how to reuse code behind in asp.net VB pages

Member 87583024-Jul-23 3:07

Member 8758302

4-Jul-23 3:07

Thanks Gerry. Helpful to know that there is no silver bullet. Based on this advice I'm isolating some functions out, and also building a class that inherits from the base.

(SOLVED) StringBuilder error

samflex23-Jun-23 4:43

23-Jun-23 4:43

Hi all,

We are currently having problem with data exported to excel.

When we review the exported file, some show values with exponential format.

To try to work around that, I have the code below with stringbuilder to fix the exponential format issue.

Public Sub GetExcel(ByVal dt As DataTable)
    Dim fileName As String = "file" & DateTime.Now.ToString("MMddyyyy") & ".xls"
    Response.AddHeader("content-disposition", "attachment;filename=" & fileName)
    Response.ContentType = "application/vnd.ms-excel"
    Dim stringWriter As StringWriter = New StringWriter()
    Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWriter)
    Dim dtExportExcel As DataGrid = New DataGrid()
    dtExportExcel.DataSource = dt
    dtExportExcel.DataBind()
    dtExportExcel.RenderControl(htmlWrite)
    Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()
    sb.Append("<html xmlns:v=""urn:schemas-microsoft-com:vml"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:xlExcel8"" xmlns=""http://www.w3.org/TR/REC-html40""> <head><style> table { mso-number-format:'0'; } </style></head> <body></html>")
    sb.Append(stringWriter & "</body></html>")
    Response.Write(sb.ToString())
    Response.[End]()
End Sub

When I run the code, I get an error on this line:

sb.Append(stringWriter & "</body></html>")

The error says, Operator '&' is s not defined for types 'StringWriter' and 'String'

Any ideas what this means?

Thanks in advance

modified 5-Jul-23 21:43pm.

Richard MacCutchan23-Jun-23 5:33

23-Jun-23 5:33

It means you cannot add a simple string to a StringWriter object, as it makes no sense. Furthermore you cannot append a StringWriter object to a StringBuilder. You can only append strings (or the string values of objects) to a StringBuilder. See StringWriter Constructor (System.IO) | Microsoft Learn[^] for the correct way to combine the two.

samflex23-Jun-23 5:47

23-Jun-23 5:47

Ok, thank you very much for your response.

So, this would have been the correct way?

sb.Append("</body></html>")

Richard MacCutchan23-Jun-23 5:53

23-Jun-23 5:53

Yes, You can only add strings, or "things" that can be expressed as strings.

samflex23-Jun-23 5:57

23-Jun-23 5:57

Thank you. I saw a similar code but written in C# that has exact same code and users say it worked for them.

Thank you for your help. I will try this and hopefully, it works.

Richard MacCutchan23-Jun-23 6:04

23-Jun-23 6:04

Sorry about the other message. But looking at your code again I do not see why the StringWriter, or the HtmlTextWriter, are there, as apart from the following two lines:

Dim stringWriter As StringWriter = New StringWriter()
Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWriter)

You never refer to either object (other than in the line with the error).

samflex23-Jun-23 6:51

23-Jun-23 6:51

Right, you are correct.

I have actually removed them from there but added them some place else like:

dtExportExcel.RenderControl(htmlWrite)
	Response.Write(stringWriter.ToString())

Richard Deeming3-Jul-23 4:40

Richard Deeming

3-Jul-23 4:40

As a simple fix:

VB.NET

Dim sb As New System.Text.StringBuilder()
sb.Append("<html xmlns:v=""urn:schemas-microsoft-com:vml"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:xlExcel8"" xmlns=""http://www.w3.org/TR/REC-html40""> <head><style> table { mso-number-format:'0'; } </style></head> <body>")

Dim stringWriter As New StringWriter(sb)
Dim htmlWrite As New HtmlTextWriter(stringWriter)
Dim dtExportExcel As New DataGrid()
dtExportExcel.DataSource = dt
dtExportExcel.DataBind()
dtExportExcel.RenderControl(htmlWrite)
    
sb.Append("</body></html>")
Response.Write(sb.ToString())
Response.[End]()

However, note that you are not "exporting an Excel file"; you are rendering HTML content, but lying to the browser and claiming that it's an Excel file. Excel will display a warning message, and then do its best to import that HTML into a new spreadsheet, but you'll get extremely limited control over the results.

If you actually want to export an Excel file, with precise control over the formatting and layout, then use a library designed to do that - for example, ClosedXML[^] or the Open XML SDK[^].

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

samflex5-Jul-23 15:43