Send excel file to user in web

Private Sub ExportFile(ByVal strfilePath As String, ByVal strFilename As String)
Try
Response.Clear()
Response.ContentType = “application/vnd.ms-excel”
strfilePath = Server.MapPath(strfilePath)
If Not System.IO.File.Exists(strfilePath) Then
strfilePath = Server.MapPath(“~//Data//ExportToExcel//NoRecord.xls”)
Response.AddHeader(“content-disposition”, (“attachment;filename=” + (“NoRecord” + “.xls”)))
Else
Response.AddHeader(“content-disposition”, (“attachment;filename=” _
+ (strFilename + “.xls”)))
End If

Dim sourcefile As FileStream = New FileStream(strfilePath, FileMode.Open)
Dim filesize As Long
filesize = sourcefile.Length
Dim getcontent() As Byte = New Byte((CType(filesize, Integer)) – 1) {}
sourcefile.Read(getcontent, 0, CType(sourcefile.Length, Integer))
sourcefile.Close()
Response.BinaryWrite(getcontent)
Catch ex As Exception
ex = ex
Finally
Response.End()
End Try

End Sub

Export data from a DataSet into a real Excel 2007 file Open XML SDK

Ever wanted to add an “Export to Excel” function to your ASP.Net, WinForms or WPF application ?

This free C# and VB.Net library lets you export a DataTable or DataSet of data into a “real” Excel 2007 .xlsx file, using one line of code.

DocumentFormat.OpenXml.dll
From the Microsoft Open XML SDK library
WindowsBase.dll
From the Microsoft .Net Framework library

————————————————————————————

Public Class CreateExcelFile
Public Shared Function CreateExcelDocument(Of T)(ByVal list As List(Of T), ByVal xlsxFilePath As String) As Boolean
Dim ds As New DataSet()
ds.Tables.Add(ListToDataTable(list))

Return CreateExcelDocument(ds, xlsxFilePath)
End Function

‘ This function is adapated from: http://www.codeguru.com/forum/showthread.php?t=450171
‘ My thanks to Carl Quirion, for making it “nullable-friendly”.
Public Shared Function ListToDataTable(Of T)(ByVal list As List(Of T)) As DataTable

Dim dt As New DataTable
Dim row As DataRow
For Each info As System.Reflection.PropertyInfo In list.GetType().GetProperties()
dt.Columns.Add(New DataColumn(info.Name, GetNullableType(info.PropertyType)))
Next

For Each tValue As T In list

row = dt.NewRow()
For Each info As System.Reflection.PropertyInfo In list.GetType().GetProperties()

If Not IsNullableType(info.PropertyType) Then
row(info.Name) = info.GetValue(tValue, Nothing)
Else
row(info.Name) = info.GetValue(tValue, Nothing)
End If
Next
dt.Rows.Add(row)
Next
Return dt
End Function

Public Shared Function GetNullableType(ByVal t As Type) As Type

Dim returnType As Type = t

If (t.IsGenericType Or t.GetGenericTypeDefinition() Is GetType(Nullable(Of ))) Then
returnType = Nullable.GetUnderlyingType(t)
End If

Return returnType

End Function

Public Shared Function IsNullableType(ByVal type As Type) As Boolean

Return (type Is GetType(String) Or
type.IsArray Or
(type.IsGenericType And type.GetGenericTypeDefinition() Is GetType(Nullable(Of ))))
End Function

Public Shared Function CreateExcelDocument(ByVal dt As DataTable, ByVal xlsxFilePath As String) As Boolean

Dim ds As New DataSet
ds.Tables.Add(dt)

Return CreateExcelDocument(ds, xlsxFilePath)
End Function

Public Shared Function CreateExcelDocument(ByVal ds As DataSet, ByVal excelFilename As String) As Boolean
Try
Using document As SpreadsheetDocument = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook)

Dim workbook As WorkbookPart = document.AddWorkbookPart

‘ document.AddWorkbookPart()
document.WorkbookPart.Workbook = New DocumentFormat.OpenXml.Spreadsheet.Workbook()

‘ My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
document.WorkbookPart.Workbook.Append(New BookViews(New WorkbookView()))

‘ If we don’t add a “WorkbookStylesPart”, OLEDB will refuse to connect to this .xlsx file !
Dim workbookStylesPart As WorkbookStylesPart = document.WorkbookPart.AddNewPart(Of WorkbookStylesPart)(“rIdStyles”)

Dim stylesheet As New Stylesheet
workbookStylesPart.Stylesheet = stylesheet
workbookStylesPart.Stylesheet.Save()

‘ Dim sp As WorkbookStylesPart = document.WorkbookPart.AddNewPart(Of WorkbookStylesPart)()

CreateParts(ds, document)

End Using
‘Trace.WriteLine(“Successfully created: ” + excelFilename)
Return True

Catch ex As Exception
‘ Trace.WriteLine(“Failed, exception thrown: ” + ex.Message)
Return False
End Try

End Function

Private Shared Sub CreateParts(ByVal ds As DataSet, ByVal spreadsheet As SpreadsheetDocument)

‘ Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
Dim worksheetNumber As UInt64 = 1
For Each dt As DataTable In ds.Tables
‘ For each worksheet you want to create
Dim workSheetID As String = “rId” + worksheetNumber.ToString()
Dim worksheetName As String = dt.TableName

Dim newWorksheetPart As WorksheetPart = spreadsheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
newWorksheetPart.Worksheet = New DocumentFormat.OpenXml.Spreadsheet.Worksheet()

‘ If you want to define the Column Widths, you need to do this *before* appending the SheetData
http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk/thread/1d93eca8-2949-4d12-8dd9-15cc24128b10/

‘ If you want to calculate the column width, it’s not easy. Have a read of this article:
http://polymathprogrammer.com/2010/01/11/custom-column-widths-in-excel-open-xml/

Dim columnWidthSize As Int32 = 20 ‘ Replace the following line with your desired Column Width for column # col
Dim columns As New Columns

For colInx As Integer = 0 To dt.Columns.Count
Dim column As Column = CustomColumnWidth(colInx, columnWidthSize)
columns.Append(column)
Next
newWorksheetPart.Worksheet.Append(columns)

‘ create sheet data
newWorksheetPart.Worksheet.AppendChild(New DocumentFormat.OpenXml.Spreadsheet.SheetData())

‘ save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart)
newWorksheetPart.Worksheet.Save()

‘ create the worksheet to workbook relation
If (worksheetNumber = 1) Then
spreadsheet.WorkbookPart.Workbook.AppendChild(New DocumentFormat.OpenXml.Spreadsheet.Sheets())
End If

Dim sheet As DocumentFormat.OpenXml.Spreadsheet.Sheet = New DocumentFormat.OpenXml.Spreadsheet.Sheet
sheet.Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart)
sheet.SheetId = worksheetNumber
sheet.Name = dt.TableName
‘ Sheets.Append(sheet)

spreadsheet.WorkbookPart.Workbook.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.Sheets).Append(sheet)
‘ AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
Next
End Sub

Private Shared Sub WriteDataTableToExcelWorksheet(ByVal dt As DataTable, ByVal worksheetPart As WorksheetPart)

Dim worksheet As Worksheet = worksheetPart.Worksheet
Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()

Dim cellValue As String = “”

‘ Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.

‘ We’ll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
‘ cells of data, we’ll know if to write Text values or Numeric cell values.
Dim numberOfColumns As Integer = dt.Columns.Count
Dim IsNumericColumn(numberOfColumns) As Boolean

Dim excelColumnNames([numberOfColumns]) As String

For n As Integer = 0 To numberOfColumns
excelColumnNames(numberOfColumns) = GetExcelColumnName(n)
Next n


‘ Create the Header row in our Excel Worksheet

Dim rowIndex As UInt32 = 1

Dim headerRow As Row = New Row
headerRow.RowIndex = rowIndex ‘ add a row at the top of spreadsheet
sheetData.Append(headerRow)

For colInx As Integer = 0 To numberOfColumns – 1
Dim col As DataColumn = dt.Columns(colInx)
AppendTextCell(excelColumnNames(colInx) + “1”, col.ColumnName, headerRow)
IsNumericColumn(colInx) = (col.DataType.FullName = “System.Decimal”) Or (col.DataType.FullName = “System.Int32”)
Next


‘ Now, step through each row of data in our DataTable…

Dim cellNumericValue As Double = 0

For Each dr As DataRow In dt.Rows
‘ …create a new row, and append a set of this row’s data to it.
rowIndex = rowIndex + 1
Dim newExcelRow As New Row
newExcelRow.RowIndex = rowIndex ‘ add a row at the top of spreadsheet
sheetData.Append(newExcelRow)

For colInx As Integer = 0 To numberOfColumns – 1
cellValue = dr.ItemArray(colInx).ToString()

‘ Create cell with data
If (IsNumericColumn(colInx)) Then
‘ For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
‘ If this numeric value is NULL, then don’t write anything to the Excel file.
cellNumericValue = 0
If (Double.TryParse(cellValue, cellNumericValue)) Then
cellValue = cellNumericValue.ToString()
AppendNumericCell(excelColumnNames(colInx) + rowIndex.ToString(), cellValue, newExcelRow)
End If
Else
‘ For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames(colInx) + rowIndex.ToString(), cellValue, newExcelRow)
End If
Next

Next

End Sub

Private Shared Function CustomColumnWidth(ByVal columnIndex As Integer, ByVal columnWidth As Double) As Column
‘ This creates a Column variable for a zero-based column-index (eg 0 = Excel Column A), with a particular column width.
Dim column As New Column
column.Min = columnIndex + 1
column.Max = columnIndex + 1
column.Width = columnWidth
column.CustomWidth = True
Return column
End Function

Public Shared Sub AppendTextCell(ByVal cellReference As String, ByVal cellStringValue As String, ByVal excelRow As Row)
‘/ Add a new Excel Cell to our Row
Dim cell As New Cell
cell.CellReference = cellReference
cell.DataType = CellValues.String

Dim cellValue As New CellValue
cellValue.Text = cellStringValue

cell.Append(cellValue)

excelRow.Append(cell)
End Sub

Public Shared Sub AppendNumericCell(ByVal cellReference As String, ByVal cellStringValue As String, ByVal excelRow As Row)
‘/ Add a new Excel Cell to our Row
Dim cell As New Cell
cell.CellReference = cellReference
cell.DataType = CellValues.Number

Dim cellValue As New CellValue
cellValue.Text = cellStringValue

cell.Append(cellValue)

excelRow.Append(cell)
End Sub

Public Shared Function GetExcelColumnName(ByVal columnIndex As Integer) As String
If (columnIndex < 26) Then
Return Chr(Asc("A") + columnIndex)
End If

Dim firstChar As Char,
secondChar As Char

firstChar = Chr(Asc("A") + (columnIndex \ 26) – 1)
secondChar = Chr(Asc("A") + (columnIndex Mod 26))

Return firstChar + secondChar
End Function

End Class
————————————————————————————

http://mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

Dividend share KLSE 2017

[June topic: high dividend series] – you recommend, I analyze!
WeShareWeTrade decided to launch a high dividend series in June after the team launched a small series every month!
We will share a high dividend company every day in June and analyze whether these companies are suitable for long-term dividends to raise your passive income! That is, we will share and analyze 30 companies!
Will we share the thirty companies? We will analyze The Edge Malaysia finishing the 20 high dividend companies, the other 10 by you call the shots! You only need to comment comment recommend high dividend company name, our team will screen 10 and make an analysis in June.
A perfect portfolio must have at least one or two stable to no friends of the high-yield stocks to run together. So be sure to high-interest stocks this good Kang head to share with you like to buy shares to buy friends know, or really no friends. Haha, hope readers are much support! Share to share!
This is a 30-day homework share, you can expect the team in the next 30 days will be very busy, if asked at least thirty share should not be too much right: P hehe
The Edge Malaysia (# 1162) finishing the top 20 high dividend companies (dividends, net profit and positive cash flow over the past 10 years)

1. STAR (6084)

2. FPI (9172)

3. UCHITEC (7100)

4. MAGNUM (3859)

5. APOLLO (6432)

6. CSCENIC (7202)

7. BAT (4162)

8. TWRREIT (5111)

9. BJTOTO (1562)

10. FIMACOR (3107)

11. PERSTIM (5436)

12. HEIM (3255)

13. TASEK (4448)

14. HUPSENG (5024)

15. CYL (7157)

16. KFIMA (6491)

17. DLADY (3026)

18. LUXCHEM (5143)

19. MAGNI (7087)

20. WELLCAL (7231)

Dividend share KLSE 2017

【六月专题:高股息系列】- 你推荐,我分析!
继团队推出每月小股系列后,WeShareWeTrade决定在六月份推出高股息系列!
我们将会在六月的每一天都分享一家高股息的公司,并分析这些公司是否适合买来长期收股息,以提高你的passive income!也就是说,我们将会分享并分析30家公司!
我们会分享那三十家公司呢?我们将会分析The Edge Malaysia 整理的20大高股息公司,另外10家就由你们做主!你只需要留言comment推荐高股息公司的名字,我们团队就会筛选10家并在六月做出分析。
一个完美的投资组合中必定要有至少一两支稳到没有朋友的高息股来磨合一下。所以一定要把高息股这个好康头分享给你喜欢买股收息的朋友们知道,不然就真的没朋友了。哈哈,希望读者们多多支持!分享分享!
这是一个连续三十天的功课分享,可以预见团队在未来的三十天都会很忙碌,如果要求至少三十个分享应该不会很过分对吧 😛 hehe
The Edge Malaysia (#1162期) 整理的20家最棒的高股息公司(过去10年都有派股息,净利和正面现金流)

1. STAR (6084)

2. FPI (9172)

3. UCHITEC (7100)

4. MAGNUM (3859)

5. APOLLO (6432)

6. CSCENIC (7202)

7. BAT (4162)

8. TWRREIT (5111)

9. BJTOTO (1562)

10. FIMACOR (3107)

11. PERSTIM (5436)

12. HEIM (3255)

13. TASEK (4448)

14. HUPSENG (5024)

15. CYL (7157)

16. KFIMA (6491)

17. DLADY (3026)

18. LUXCHEM (5143)

19. MAGNI (7087)

20. WELLCAL (7231)
剩下的10家,由你们来推荐,我们来分析!

21. ?

22. ?

23. ?

24. ?

25. ?

26. ?

27. ?

28. ?

29. ?

30. ?
#你推荐,我分析

#高股息人人爱

#投资组合一定要有高股息的公司

MicrosoftAjax.js: Cannot read property ‘_notified’ of null

The problem is that the browser detection code in MicrosoftAjax.js does not detect WebKit (gasp!). Just include this javascript in your site and that should take care of it.

Sys.Browser.WebKit = {};
if (navigator.userAgent.indexOf(‘WebKit/’) > -1) {
Sys.Browser.agent = Sys.Browser.WebKit;
Sys.Browser.version = parseFloat(navigator.userAgent.match(/WebKit\/(\d+(\.\d+)?)/)[1]);
Sys.Browser.name = ‘WebKit’;
}

https://forums.asp.net/t/1348484.aspx?error+in+MicrosoftAjax+js+Cannot+read+property+_notified+of+null

Dynamic Web Service JSON return

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Web.Script.Serialization
Imports System.Runtime.Serialization.Json
Imports System.IO

_
_
_
Public Class WS
Inherits System.Web.Services.WebService
Private strConn As String

_
_
Public Function GetSummaryDynamic(ByVal startrowindex As String, ByVal maximumrows As String, ByVal searchstring As String, ByVal sortby As String) As String

Dim dtResults As New DataTable
Dim ms As New MemoryStream()

Try
Using sqlConn As IDbConnection = CreateConnection(ConnStr(0))
Using sqlCmd As IDbCommand = sqlConn.CreateCommand
sqlCmd.CommandText = “SPP_SUMMARY_ENQ”
sqlCmd.CommandType = CommandType.StoredProcedure
clsCONN.addInputParam(sqlCmd, “startrowindex”, startrowindex, clsCONN.dtType.dbString)
clsCONN.addInputParam(sqlCmd, “maximumrows”, maximumrows, clsCONN.dtType.dbString)
clsCONN.addInputParam(sqlCmd, “searchstring”, searchstring, clsCONN.dtType.dbString)
clsCONN.addInputParam(sqlCmd, “sortby”, sortby, clsCONN.dtType.dbString)
Using sqlReader As IDataReader = sqlCmd.ExecuteReader
dtResults.Load(sqlReader)
End Using
End Using
End Using

Dim strJson As String = DataTableToJSONWithJavaScriptSerializer(dtResults)
Return strJson
Catch ex As Exception
Throw (New ExceptionMsg(GenerateErrNumber(), ex))
Finally
ms.Close()
End Try
End Function

Public Function DataTableToJSONWithJavaScriptSerializer(ByVal table As DataTable) As String
Dim jsSerializer As JavaScriptSerializer = New JavaScriptSerializer
Dim parentRow As List(Of Dictionary(Of String, Object)) = New List(Of Dictionary(Of String, Object))
Dim childRow As Dictionary(Of String, Object)
For Each row As DataRow In table.Rows
childRow = New Dictionary(Of String, Object)
For Each col As DataColumn In table.Columns
childRow.Add(col.ColumnName, row(col))
Next
parentRow.Add(childRow)
Next
Return jsSerializer.Serialize(parentRow)
End Function
#End Region

http://www.c-sharpcorner.com/UploadFile/9bff34/3-ways-to-convert-datatable-to-json-string-in-Asp-Net-C-Sharp/

Get huge email list from Active Directory

DirectoryEntry entry = new DirectoryEntry(“Domain”, “UserName”, “Password”, AuthenticationTypes.None);
DirectorySearcher dSearch = new DirectorySearcher(entry);
dSearch.SearchScope = System.DirectoryServices.SearchScope.Subtree;
dSearch.ReferralChasing = ReferralChasingOption.All;
dSearch.PageSize = 5000;
dSearch.Filter = “(&(objectClass=user))”;
foreach (SearchResult sResultSet in dSearch.FindAll())
{
if (sResultSet.Properties[“mail”].Count > 0)
strClientEmails += sResultSet.Properties[“mail”][0].ToString() + “,”;
}

WA Affiliate teach wordpress

https://my.wealthyaffiliate.com/kyle

Kyle, CEO & Co-founder of Wealthy Affiliate here! It’s so wonderful to meet you and I look forward to working with you in the coming days and helping you achieve success!

You are receiving this email because you have joined Wealthy Affiliate as a Starter Member, which I think you are going to find to be a really awesome place. By lesson 4 of our “Get Started Here” training you are going to have your very own “niche” website up and running.

Carson and I want to personally welcome you to Wealthy Affiliate and I want to give you a brief overview of what you can expect and how you should get rolling within the members area.

Your First Task…

The first thing you should do is acquaint yourself with the “Get Started Here” course within the main menu. It is the BIG GREEEN button.

This course is going to walk you through how Wealthy Affiliate works and also get you up and running with your very own business (which includes your very own website).

This will be your home base and will give you step-by-step instructions on what to do. Each lesson you will be given an associated “video” walk-through that is going to SHOW you the process.

I want you to promise me as you are going through the training and you ever feel stuck or you don’t quite understand something….

Ask for HELP!

We have many communication platforms that you have access to including live chat, classroom discussions, training, and blogs that you have exclusive access to within the first 7 days of your Starter membership.

There is no reason to get frustrated as you have access to a community at Wealthy Affiliate that has many experts, many millionaire marketers, and many selfless folks that would be more than happy to help you out. Simply ask your questions as they come to mind or as you feel stuck. Someone will be there to help, including Carson and myself.

In the coming days our hope is that you will have the best experience possible…and it is our goal to facilitate this. We will be sending you some helpful emails to help you get up and running and familiarize yourself with Wealthy Affiliate ,Internet marketing, and how to get started and build success online.

We care about every single member within the Wealthy Affiliate community and I think this is going to be quite evident as you immerse yourself.

Wealthy Affiliate, The Full Walk-through

Today I want to start things off for you so that you are not left with a feeling of being overwhelmed. I have created a “video walk-through” that explains exactly what Wealthy Affiliate is.

==> Watch Kyle Walk You Through the WA System

Take a few minutes to watch this, it will make your life within the community much easier and reveal how powerful Wealthy Affiliate is going to be to your online success!

Let’s Get Rolling…

When you become a member of WA, you are automatically enrolled in the Getting Started training which is included with your Starter Membership. This is our flagship business building course that walks you through the step-by-step process of building a successful business online that earns revenue.

By the time you complete Lesson 10 of this Course, you will have developed many key web assets that will lead you to your ultimate success. You can access this course by clicking the “Get Started Here” button inside of WA, or directly through the following link.

==> Access the Getting Started training HERE!

The Getting Started (Level 1) course is going to lead to the following accomplishments:

Setting up your account profile at Wealthy Affiliate
Interaction with fellow expert marketers and aspiring marketers via live chat
Understanding the process of earning money online
Choosing your own niche from scratch
Creating your very OWN niche website
Setting your website for massive success in search engines
Activating website extensions that add many new website features
Understanding how to create, find, and install one of over 20,000 plugins on your website
Create your very first page of content on your niche website
Create relationships with others here at WA and developed your own following
Receive timely help/support when you needed it
Understand the process of finding low competition, high traffic keywords
Create a list of a bunch of awesome keywords
Create valuable content within your niche that is getting RANKED in Google
Learn new skills that will lead you to long term SUCCESS

If a particular “lesson” takes you several days, that is OK!! If you are able to accomplish several lessons in a day, that is awesome too!. Work at your own pace through this training. The most important thing is that you take action on the tasks.

Action equals success!

Without the action, no success is possible. A good deal of the actual learning process will also take place during the action phase. Over the years the #1 reason I have seen people succeed online is because they take consistent action.

Make sure you not only read through and watch the video training, that you take action on the tasks within each lesson. This is very important and will be critical to your success.

One Last Thing…

What better opportunity to say hello and introduce yourself than on Day 1.

Don’t be shy, don’t be a stranger…

You are going to find that the community at Wealthy Affiliate is full of a lot of great people, people that you can learn from, people that are willing to help, and people that have likely had your same questions at one point along the way.

We are a community that truly cares about fellow members success and you will see that being quite evident in the coming days.

Oh ya, I almost forgot. To access your membership area at any time you will use the following details. You can use your membership from any computer, or device with an Internet connection, including laptops, tablets, or your smartphone:

In the coming days I am going to be emailing you some really powerful videos, tips, and will be inviting you to some “popular” discussions that will be highly beneficial to you and your success. 🙂

Again, welcome to Wealthy Affiliate! We look forward to a long lasting and highly successful relationship with you !

Cheers,

Kill All Active Connections To A Database

To kill all the connections to your database you can use the ALTER DATABASE command. Sometimes you want to restore the database from a backup but can’t because people are connected. Here is one way which will kick off all the users immediately

TSQLLINE NUMBER OFF | HIDE | SELECT ALL
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

–do you stuff here

ALTER DATABASE YourDatabase SET MULTI_USER

The code below will wait 60 seconds

TSQLLINE NUMBER OFF | HIDE | SELECT ALL
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS

–do you stuff here

ALTER DATABASE YourDatabase SET MULTI_USER
The reason this is better than looping over sysprocesses or sys.dm_exec_sessions is that nobody will connect while that looping piece of code runs

http://wiki.lessthandot.com/index.php/Kill_All_Active_Connections_To_A_Database