locked
Excel Template RRS feed

  • Question

  • User-714036019 posted

    Hi,

     


    Anyone could give me some assistance about Excel Template. I am writing a program to use the Gridview display the XML file, then export to a EXCEL TEMPLATE. Is that any URL or Demo Example or code to follow?

     

     

     

    Sunday, November 23, 2008 5:42 PM

Answers

  • User-309129747 posted
    i figure it on windows app, but there is a keyword you can find in google : excel.application in asp.net here code : Dim oXL As Excel.Application = CreateObject("Excel.Application")//just copy paste 'Dim oBook As Excel.Workbook = oXL.Workbooks.Open(Filename) Dim oBook As Excel.Workbook = oXL.Workbooks.Open(AppPath + ReportPath + "\RCMR.xlt")//name of template(RCMR.XLT) Dim oSheet As Excel.Worksheet = oBook.Worksheets("RCMR")//the worksheet name oXL.Visible = True Dim dvTblCATT As DataView Dim dtRCMR As New DataTable Dim SQlQueryx As String = "select field1,field2, ... etc from table " Dim SQLCommandx As New OleDbCommand(SQlQueryx, conn) Dim tempDA As New OleDbDataAdapter(SQLCommandx) tempDA.Fill(dsTblCATT) dtRCMR = dsTblCATT.tables(0) Dim i As Integer = 5 dvTblCATT = dtRCMR.DefaultView For Each DR As DataRowView In dvTblCATT oSheet.Cells(i, 1) = DR("Risk Number")// per cell (ex: A1) oSheet.Cells(i, 2) = DR("Risk Tier Level 1")// per cell (ex: A2) oSheet.Cells(i, 3) = DR("Product") oSheet.Cells(i, 4) = DR("Key Process") oSheet.Cells(i, 5) = DR("Risk Description") oSheet.Cells(i, 6) = DR("IHRR") oSheet.Range("F" & i).Interior.ColorIndex = GetColorCell(DR("IHRR")) oSheet.Cells(i, 7) = DR("Control") oSheet.Cells(i, 8) = DR("RDRR1") oSheet.Range("H" & i).Interior.ColorIndex = GetColorCell(DR("RDRR1")) oSheet.Cells(i, 9) = DR("Key Control") oSheet.Cells(i, 10) = DR("Control Type 1") oSheet.Cells(i, 11) = DR("Control Type 2") oSheet.Cells(i, 12) = DR("Control Frequency") oSheet.Cells(i, 13) = DR("Internal Control Objective") oSheet.Cells(i, 14) = DR("COSO Component") oSheet.Cells(i, 15) = DR("COSO Attribut") oSheet.Cells(i, 16) = DR("Assessment Type") oSheet.Cells(i, 17) = DR("COBIT Component") oSheet.Cells(i, 18) = DR("COBIT Attribut") oSheet.Cells(i, 19) = DR("Control Objective") i += 1 Dim rng As Excel.Range = oSheet.Cells(i, 1) Dim row As Excel.Range = rng.EntireRow If i <> dvTblCATT.Count + 5 Then row.Insert(Excel.XlInsertShiftDirection.xlShiftDown, False) Else row.Delete(Excel.XlInsertShiftDirection.xlShiftDown) End If 'oSheet.Rows(i, 1).Insert(Excel.XlInsertShiftDirection.xlShiftDown, False) Next oSheet.Cells(2, 17) = GetServerDateTime().ToString("dd MMMM yyyy") 'tanggal print oSheet.Cells(3, 17) = Me.JobGroupCode 'jobgroup 'Add By Elfan Add New Info oSheet.Cells(3, 2) = Me.txtNamaUnitKerja.Text 'Unit Kerja Dim NewDocName As String = AppPath & "\temp\" & ReplaceInvalidChar(Me.JobGroupCode.ToString()) & "_" & "RCMR" Dim Count As Integer = 0 While File.Exists(NewDocName & ".xls") NewDocName = AppPath & "\temp\" & ReplaceInvalidChar(Me.JobGroupCode.ToString()) & "_" & "RCMR" & Count Count += 1 End While oBook.SaveAs(NewDocName & ".xls") 'oXL.Quit() conn.Close()
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 24, 2008 12:55 AM