Export To Excel Using DataTable

Selain menggunakan XML,HTMLWrite dan clientscript,korang boleh juga export data ke excel menggunakan datatable .

Contohnya (using vb.net)

Function ExportToExcel(ByVal dt As DataTable, ByVal startIdx As Integer)
Dim txt As String = ""
Dim dr As DataRow, ary() As Object, i As Integer
Dim iCol As Integer
Dim sb as stringbuilder= new stringbuilder()

If dt.Rows.Count > 0 Then
'Output Column Headers
For iCol = startIdx To dt.Columns.Count - 1
sb.append(dt.Columns(iCol).ToString & vbTab)
Next
sb.append(vbCrLf)

'Output Data
For Each dr In dt.Rows
ary = dr.ItemArray
For i = startIdx To UBound(ary)
sb.append(Trim(ary(i).ToString) & vbTab)
Next
sb.append(vbCrLf)
Next
End If
Return sb.tostring()

End Function

'//remarks:make sure event handler coontrol is outside of updatepanel control
Sub btn_click...
Dim attachment As String = "attachment; filename=Employee.xls"
Dim val As String = ""
Response.ClearContent()
Response.AddHeader("content-disposition", attachment)
Response.ContentType = "application/vnd.ms-excel"
val =ExportToExcel(DtTable, 0) '<--DtTable is your gridview datatable
Response.Write(val)
Response.End()
End sub

Merging GridView Header Cell

Korang boleh merge cell untuk header gridview menggunakan function SetRenderMethodDelegate.
Tengok sample code kat bawah

Ni code yang aku dah convert ke vb.net (code asal c# boleh dirujuk di http://marss.co.ua/MergingGridViewHeaderColumns.aspx)


'//yang ni takperlu ubah ape2
'===========================

<Serializable()> _
Private Class MergedColumnsInfo
' indexes of merged columns
Public MergedColumns As New List(Of Integer)()
' key-value pairs: key = first column index, value = number of merged columns
Public StartColumns As New Hashtable()
' key-value pairs: key = first column index, value = common title of merged columns
Public Titles As New Hashtable()

'parameters: merged columns's indexes, common title of merged columns
Public Sub AddMergedColumns(ByVal columnsIndexes As Integer(), ByVal title As String)
MergedColumns.AddRange(columnsIndexes)
StartColumns.Add(columnsIndexes(0), columnsIndexes.Length)
Titles.Add(columnsIndexes(0), title)
End Sub
End Class

'property for storing of information about merged columns
Private ReadOnly Property info() As MergedColumnsInfo
Get
If ViewState("info") Is Nothing Then
ViewState("info") = New MergedColumnsInfo()
End If

Try
Return DirectCast(ViewState("info"), MergedColumnsInfo)
Catch ex As Exception
Return Nothing
End Try

End Get
End Property

'method for rendering of columns's headers
Private Sub RenderHeader(ByVal output As HtmlTextWriter, ByVal container As Control)
For i As Integer = 0 To container.Controls.Count - 1
Dim cell As TableCell = DirectCast(container.Controls(i), TableCell)
'stretch non merged columns for two rows
If Not info.MergedColumns.Contains(i) Then
cell.Attributes("rowspan") = "2"
cell.RenderControl(output)
'render merged columns's common title
ElseIf info.StartColumns.Contains(i) Then
output.Write(String.Format("{1}", info.StartColumns(i), info.Titles(i)))
End If
Next

'close first row
output.RenderEndTag()
'set attributes for second row
gview1.HeaderStyle.AddAttributesToRender(output)
'start second row
output.RenderBeginTag("tr")

'render second row (only merged columns)
For i As Integer = 0 To info.MergedColumns.Count - 1
Dim cell As TableCell = DirectCast(container.Controls(info.MergedColumns(i)), TableCell)
cell.RenderControl(output)
Next
End Sub

'kat event GridView_RowCreated() 'ni pun tak perlu ubah aper2
=========================
If e.Row.RowType = DataControlRowType.Header Then
e.Row.SetRenderMethodDelegate(AddressOf RenderHeader)
End If


'kat page_load event 'sini aje kalau nak set cell mane nak merge
================
If Not IsPostBack Then
info.AddMergedColumns(New Integer() {3, 4, 5}, "any text column baru") 'ni akan merge cell 3,4,5

'kalau nak merge lagi,just put same statement here

End If


note:so korang tak perlu pening2 buat kat itemTemplateField.
ko rang boleh copy sourcecode dari sumber asalnya dan test tengok hasilnya.Thanks to the coder!
Lagi satu,kalau tengah debug,kalau korang ubah any property value kat boundfield,korang kena run balik.so best practise,jgn ubah waktu application tgh running.

Event Not Fired When Click Event Handler

mungkin berlaku masalah bila event handler korang tak fire event bila guna ajax.
caranya,try letak Trigger control kat page korang dan Set kan ControlId Property kepada eventhandler id.
contohnya

<Triggers>
<asp:PostBackTrigger ControlID="btnSave" />
</Triggers>
...
<asp:button id="btnSave" runat="server" />

Error When Using Response Object Withing UpdatePanel Object

korang akan dapat error at run time bila cuba untuk guna response.xxx object bilamana event handler yang fire event(contohnya asp:button) berada dalam updatePanel tag.

at the code
...
sub mybtn_onclick(xxx,xxx) handles...
response.write("bla bla") 'ni akan sebabkan error run time
end sub

T-SQL Using Pivot To Matrix Data

CREATE TABLE #temp123
(
Country varchar(15),
Variable varchar(20),
VaribleValue INT
)

INSERT INTO #temp123 VALUES ('North America','Sales','2000000')
INSERT INTO #temp123 VALUES ('North America','Expenses','1250000')
INSERT INTO #temp123 VALUES ('North America','Taxes','250000')
INSERT INTO #temp123 VALUES ('North America','Profit','500000')

INSERT INTO #temp123 VALUES ('Europe','Sales','2500000')
INSERT INTO #temp123 VALUES ('Europe','Expenses','1250000')
INSERT INTO #temp123 VALUES ('Europe','Taxes','500000')
INSERT INTO #temp123 VALUES ('Europe','Profit','750000')

INSERT INTO #temp123 VALUES ('South America','Sales','500000')
INSERT INTO #temp123 VALUES ('South America','Expenses','250000')

INSERT INTO #temp123 VALUES ('Asia','Sales','800000')
INSERT INTO #temp123 VALUES ('Asia','Expenses','350000')
INSERT INTO #temp123 VALUES ('Asia','Taxes','100000')

/** Show original table **/
SELECT * FROM #temp123

/** Create crosstab using PIVOT **/
SELECT *
FROM #temp123
PIVOT
(
SUM(VaribleValue)
FOR [Variable]
IN ([Sales],[Expenses],[Taxes],[Profit])
)
AS p

DROP TABLE #temp123

--TO ALLOW USING PIVOT COMMAND
--EXEC dbo.sp_dbcmptlevel @dbname=N'PromiseLive', @new_cmptlevel=90

T-SQL Row_Number With Group By Clause

kalau nak kuarkan row number dan row number reset bila new group

1. kalau nak row number desc order

-------------------------
SELECT * FROM

(
SELECT row_number() OVER (PARTITION BY XColumn
ORDER BY COUNT(XColumn)) as RowNumber,XColumn,YColumn,ZColumn
from XYZTable
GROUP BY XColumn,YColumn,ZColumn
)a
ORDER BY XColumn,RowNumber desc

2. kalau nak asc order
-------------------------------------------------
SELECT row_number() OVER (PARTITION BY XColumn
ORDER BY COUNT(XColumn)) as RowNumber,XColumn,YColumn,ZColumn
from XYZTable
GROUP BY XColumn,YColumn,ZColumn


NOTE : berguna untuk kira total count per group,running total etc