Rahmat Faisal

Thursday, August 19, 2010 3:10 PM

Export Data Set To Excel Fastly

Public Sub ExportToExcel(ByVal dataSet As DataSet, ByVal outputPath As String)
        ' Create the Excel Application object
        Dim excelApp As New ApplicationClass()

        ' Create a new Excel Workbook
        Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing)

        Dim sheetIndex As Integer = 0
        Dim col, row As Integer
        Dim excelSheet As Worksheet

        ' Copy each DataTable as a new Sheet
        For Each dt As System.Data.DataTable In dataSet.Tables

            sheetIndex += 1

            ' Copy the DataTable to an object array
            Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object

            ' Copy the column names to the first row of the object array
            For col = 0 To dt.Columns.Count - 1
                rawData(0, col) = dt.Columns(col).ColumnName
            Next

            ' Copy the values to the object array
            For col = 0 To dt.Columns.Count - 1
                For row = 0 To dt.Rows.Count - 1
                    rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
                Next
            Next

            ' Calculate the final column letter
            Dim finalColLetter As String = String.Empty
            Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
            Dim colCharsetLen As Integer = colCharset.Length

            If dt.Columns.Count > colCharsetLen Then
                finalColLetter = colCharset.Substring( _
                 (dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
            End If

            finalColLetter += colCharset.Substring( _
              (dt.Columns.Count - 1) Mod colCharsetLen, 1)

            ' Create a new Sheet
            excelSheet = CType( _
                excelWorkbook.Sheets.Add(excelWorkbook.Sheets(sheetIndex), _
                Type.Missing, 1, XlSheetType.xlWorksheet), Worksheet)

            excelSheet.Name = dt.TableName

            ' Fast data export to Excel
            Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
            excelSheet.Range(excelRange, Type.Missing).Value2 = rawData

            ' Mark the first row as BOLD
            CType(excelSheet.Rows(1, Type.Missing), Range).Font.Bold = True

            excelSheet = Nothing
        Next

        ' Save and Close the Workbook
        excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, _
         Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
         Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

        excelWorkbook.Close(True, Type.Missing, Type.Missing)

        excelWorkbook = Nothing

        ' Release the Application object
        excelApp.Quit()
        excelApp = Nothing

        ' Collect the unreferenced objects
        GC.Collect()
        GC.WaitForPendingFinalizers()

    End Sub

Share this post:

|

Posted by Rahmat.Faisal | 1 comment(s)

Filed under: DataGrid, Excel Object, WEB Application, VB.NET rdlc report

Tuesday, July 03, 2007 7:04 AM

Modal popup dialog window with multiple parameters (using array)

Open a New Web project in Visual Studio
Create two New WebForm pages named ParentWebForm and ChildWebForm
Open the HTML surface for the ParentWebForm
Locate the yellow line
Delete all code EXCEPT the yellow line

Insert the following HTML below the existing yellow line

Collapse

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
    <HEAD>
        <title>Parent Webform</title>
        <script language="javascript">
function OpenChild() 
{
    var ParmA = retvalA.value;
    var ParmB = retvalB.value;
    var ParmC = retvalC.value;
    var MyArgs = new Array(ParmA, ParmB, ParmC);
    var WinSettings = "center:yes;resizable:no;dialogHeight:300px"
    //ALTER BELOW LINE - supply correct URL for Child Form
    var MyArgs = window.showModalDialog(
   "http://localhost/ModalWin/ChildWebForm.aspx", MyArgs, WinSettings);
    if (MyArgs == null)
    {
        window.alert(
          "Nothing returned from child. No changes made to input boxes")
    }
    else
    {
        retvalA.value=MyArgs[0].toString();
        retvalB.value=MyArgs[1].toString();
        retvalC.value=MyArgs[2].toString();
    }
}
        </script>
    </HEAD>
    <body>
        <P><INPUT id="retvalA" type="text" value="AAA"></P>
        <P><INPUT id="retvalB" type="text" value="BBB"></P>
        <P><INPUT id="retvalC" type="text" value="CCC"></P>
        <P><BUTTON onclick="OpenChild()" type="button">
                Open Child Window</BUTTON>
        </P>
    </body>
</HTML>

In the above code, locate the line saying //ALTER BELOW LINE
Supply the correct URL for your ChildWebform
Open the HTML surface for the ChildWebForm
Locate the yellow line
Delete all code EXCEPT the yellow line

Insert the following HTML below the existing yellow line

Collapse

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
    <HEAD>
        <TITLE>Child Webform</TITLE>
        <script language="javascript">
function Done() {
    var ParmA = tbParamA.value;
    var ParmB = tbParamB.value;
    var ParmC = tbParamC.value;
    var MyArgs = new Array(ParmA, ParmB, ParmC);
    window.returnValue = MyArgs;
    window.close();
}
function doInit() {
    var ParmA = "Aparm";
    var ParmB = "Bparm";
    var ParmC = "Cparm";
    var MyArgs = new Array(ParmA, ParmB, ParmC);
    MyArgs =  window.dialogArguments;
    tbParamA.value = MyArgs[0].toString();
    tbParamB.value = MyArgs[1].toString();
    tbParamC.value = MyArgs[2].toString();
}
        </script>
    </HEAD>
    <BODY onload="doInit()">
        <P>Param A:<INPUT id="tbParamA" TYPE="text"></P>
        <P>Param B:<INPUT ID="tbParamB" TYPE="text"></P>
        <P>Param C:<INPUT ID="tbParamC" TYPE="text"></P>
        <BUTTON onclick="Done()" type="button">OK</BUTTON>
    </BODY>
</HTML>

Set the project StartUp page to be the Parent Webform
Run the project.

Note

To make the child modal to the entire desktop, you'll need add code to the child which uses <body onblur="this.focus">.

Source :

Share this post:

|

Posted by Rahmat.Faisal | with no comments

Filed under: WEB Application

Monday, September 18, 2006 8:54 AM

Image di Crystal Report

Private Sub CetakData()

Dim cn As New SqlClient.SqlConnection("Integrated Security=False;Data Source=srv;Initial Catalog=tbl;User ID=user;Password=pwd;")

Dim strsql As String

Sub CetakData()

Dim cn As New SqlClient.SqlConnection("Integrated Security=False;Data Source=srv;Initial Catalog=tbl;User ID=user;Password=pwd;")

Dim strsql As String

Dim cn As New SqlClient.SqlConnection("Integrated Security=False;Data Source=srv;Initial Catalog=tbl;User ID=user;Password=pwd;")

Dim strsql As String

Dim ds As New dsLineSheet

Dim getImage As New clsGetImageData

cn.Open()

strsql = "Select ItemID,ItemCode,ItemSize,Status,LastStatusDate,SalesGroup,SalesGroupDate,Market,Collection,CollectionDesc,ShortDesc,RetailPrice,WSPrice,comp_name from vwLineSheet where comp_name = host_name()"

Dim ds As New dsLineSheet

Dim getImage As New clsGetImageData

cn.Open()

strsql = "Select ItemID,ItemCode,ItemSize,Status,LastStatusDate,SalesGroup,SalesGroupDate,Market,Collection,CollectionDesc,ShortDesc,RetailPrice,WSPrice,comp_name from vwLineSheet where comp_name = host_name()"

Dim getImage As New clsGetImageData

cn.Open()

strsql = "Select ItemID,ItemCode,ItemSize,Status,LastStatusDate,SalesGroup,SalesGroupDate,Market,Collection,CollectionDesc,ShortDesc,RetailPrice,WSPrice,comp_name from vwLineSheet where comp_name = host_name()"

"Select ItemID,ItemCode,ItemSize,Status,LastStatusDate,SalesGroup,SalesGroupDate,Market,Collection,CollectionDesc,ShortDesc,RetailPrice,WSPrice,comp_name from vwLineSheet where comp_name = host_name()"

Dim dr As SqlClient.SqlDataReader

Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(strsql, cn)

Dim drw As DataRow

Dim i As Integer = 0

dr = cmd.ExecuteReader

ds.Tables("vwLineSheet").Clear()

Do

Dim dr As SqlClient.SqlDataReader

Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(strsql, cn)

Dim drw As DataRow

Dim i As Integer = 0

dr = cmd.ExecuteReader

ds.Tables("vwLineSheet").Clear()

Do

Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(strsql, cn)

Dim drw As DataRow

Dim i As Integer = 0

dr = cmd.ExecuteReader

ds.Tables("vwLineSheet").Clear()

Do

Dim drw As DataRow

Dim i As Integer = 0

dr = cmd.ExecuteReader

ds.Tables("vwLineSheet").Clear()

Do

Dim i As Integer = 0

dr = cmd.ExecuteReader

ds.Tables("vwLineSheet").Clear()

Do

"vwLineSheet").Clear()

Do

Do Until dr.Read = False

drw = ds.Tables("vwLineSheet").NewRow()

For i = 0 To dr.FieldCount - 1

drw(i) = dr(i)

For i = 0 To dr.FieldCount - 1

drw(i) = dr(i)

Try

drw(dr.FieldCount) = getImage.GetImageData("Images\" & ds.Tables("vwLineSheet").Rows(0)("ItemCode") & ".jpg")

Catch ex As Exception

drw(dr.FieldCount) = getImage.GetImageData("\Images\na.jpg")

End Try

"Images\" & ds.Tables("vwLineSheet").Rows(0)("ItemCode") & ".jpg")

Catch ex As Exception

drw(dr.FieldCount) = getImage.GetImageData("\Images\na.jpg")

End Try

Catch ex As Exception

drw(dr.FieldCount) = getImage.GetImageData("\Images\na.jpg")

End Try

"\Images\na.jpg")

End Try

ds.Tables("vwLineSheet").Rows.Add(drw)

Loop

"vwLineSheet").Rows.Add(drw)

Loop

Loop While dr.NextResult = True

If Me.rbGroup.Checked = True Then

Me.rptLineSheet_Group1.SetDataSource(ds)

Me.CrystalReportViewer1.ReportSource = rptLineSheet_Group1

Else

Me.rptLineSheet_Group1.SetDataSource(ds)

Me.CrystalReportViewer1.ReportSource = rptLineSheet_Group1

Else

Me.CrystalReportViewer1.ReportSource = rptLineSheet_Group1

Else

Me.rptLineSheet1.SetDataSource(ds)

Me.CrystalReportViewer1.ReportSource = rptLineSheet1

End If

Me.rptLineSheet1.SetDataSource(ds)

Me.CrystalReportViewer1.ReportSource = rptLineSheet1

End If

Me.CrystalReportViewer1.ReportSource = rptLineSheet1

End If

dr.Close()

cmd.Dispose()

cn.Close()

End Sub

Public Class clsGetImageData

Public Function GetImageData(ByVal fileName As String) As Byte()

Dim hasil As Byte()

'Method to load an image from disk and return it as a bytestream

Class clsGetImageData

Public Function GetImageData(ByVal fileName As String) As Byte()

Dim hasil As Byte()

'Method to load an image from disk and return it as a bytestream

Public Function GetImageData(ByVal fileName As String) As Byte()

Dim hasil As Byte()

'Method to load an image from disk and return it as a bytestream

Dim hasil As Byte()

'Method to load an image from disk and return it as a bytestream

If System.IO.File.Exists(fileName) = False Then

fileName = "\\192.168.1.1\Images\na.jpg"

"\\192.168.1.1\Images\na.jpg"

End If

'HttpContext.Current.Response.Write(fileName)

Dim fs As System.IO.FileStream = _

New System.IO.FileStream(fileName, _

System.IO.FileMode.Open, IO.FileAccess.Read)

Dim br As System.IO.BinaryReader = New System.IO.BinaryReader(fs)

hasil = br.ReadBytes(Convert.ToInt32(br.BaseStream.Length))

fs.Dispose()

fs.Close()

br.Close()

Return hasil

End Function

End Class

Dim fs As System.IO.FileStream = _

New System.IO.FileStream(fileName, _

System.IO.FileMode.Open, IO.FileAccess.Read)

Dim br As System.IO.BinaryReader = New System.IO.BinaryReader(fs)

hasil = br.ReadBytes(Convert.ToInt32(br.BaseStream.Length))

fs.Dispose()

fs.Close()

br.Close()

Return hasil

End Function

End Class

New System.IO.FileStream(fileName, _

System.IO.FileMode.Open, IO.FileAccess.Read)

Dim br As System.IO.BinaryReader = New System.IO.BinaryReader(fs)

hasil = br.ReadBytes(Convert.ToInt32(br.BaseStream.Length))

fs.Dispose()

fs.Close()

br.Close()

Return hasil

End Function

End Class

Dim br As System.IO.BinaryReader = New System.IO.BinaryReader(fs)

hasil = br.ReadBytes(Convert.ToInt32(br.BaseStream.Length))

fs.Dispose()

fs.Close()

br.Close()

Return hasil

End Function

End Class

Return hasil

End Function

End Class

End Function

End Class

Class

Share this post:

|

Posted by Rahmat.Faisal | 2 comment(s)

Filed under: Crystal Reports

Monday, September 18, 2006 8:52 AM

RegularExpressionValidator

<asp:RegularExpressionValidator runat="server" ErrorMessage="Only picture files are allowed!" ValidationExpression="^(([a-zA-Z]:)|(\\{2}\w+)\$?)(\\(\w[\w].*))+(.gif|.GIF|.jpg|.JPG|.jpeg|.JPEG)$" ControlToValidate="fileUpload" Display="None"></asp:RegularExpressionValidator>

Share this post:

|

Posted by Rahmat.Faisal | 2 comment(s)

Filed under: WEB Application

Friday, June 02, 2006 8:24 PM

Defenisi Class

Object Oriented Program(OOP): dalam bahasa bebas berarti menganggap sekumpulan code/program sebagai benda.

Class= Benda

Property=input/ciri2 fisik dari benda

Methode=output/fungsi dari benda.

Share this post:

|

Posted by Rahmat.Faisal | 3 comment(s)

Monday, July 11, 2005 9:15 PM

Run Shell Program

Private Sub Timer1_Timer()
On Error GoTo salah
    MsgBox "Please, close your application..." & vbCrLf & _
            "Klick OK if you have done it....", vbExclamation
    Shell "cmd.exe /c copy sourceFile destinationFile", vbHide
    Timer1.Enabled = False
    MsgBox "Program Updated"
    Unload Me
    Exit Sub
salah:
    MsgBox Err.Description
    Unload Me
End Sub

Add : please see copy command parameter for details.

Share this post:

|

Posted by Rahmat.Faisal | with no comments

Rahmat Faisal

This Blog

Syndication

Search

Tags

Navigation

Archives

Export Data Set To Excel Fastly

Modal popup dialog window with multiple parameters (using array)

Note

Image di Crystal Report

RegularExpressionValidator

Defenisi Class

Run Shell Program