New Topic/Question
Reply
i dont know how to repeat set of cells in excel through vb.net. Am just fed up with searching for it. Am a student n am currently working on my final year poject n am new to vb.net. My project is for a clearing n forwarding agency. i need to prepare a bill for the duty to be paid. in a particular invoice, there may be single item r multiple item. for each item i've to calculate some values n display it on excel. i've made my excel book to be a template with all the things i should display. n the calculated values'll be displayed in corresponding cells which'll be retrived from database. if its single item its all good n i can prepare the bill easily but for multiple items i've to repeat certain cells from the template. i dunno hw to do it. am attaching my excel file with tis.i want to repeat the cells A24 to J35 'N' number of times with the cells in between filled with respective data from database. kindly help plz...
Dim xla As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim r As Integer
Dim cl As Integer
Dim Obj As Object
Dim rng As Excel.Range
xla = New Excel.ApplicationClass
xlwb = xla.Workbooks.Open("D:\Manju\Reports\BE\Be.xlsx")
'xla.Workbooks.Close ("D:\Manju\Reports\BE\Be.xlsx")
xla.Visible = False
xlws = xlwb.Worksheets("Sheet1")
rng = xlws.UsedRange
rng.Font.Name = "Times New Roman"
rng.Font.Size = 8
cmd = New SqlCommand("select * from import where sajob='" + Import.Jbtxt.Text + "'", con)
dr = cmd.ExecuteReader
If dr.Read Then
xlws.Range("E1").Value = dr(11)
xlws.Range("B2").Value = dr(25)
xlws.Range("C3").Value = dr(6)
xlws.Range("F8").Value = dr(35)
xlws.Range("B3").Value = dr(5)
xlws.Range("B4").Value = dr(12)
xlws.Range("A5").Value = dr(15)
xlws.Range("A6").Value = dr(16)
xlws.Range("B9").Value = dr(34)
xlws.Range("F9").Value = dr(36)
End If
dr.Close()
If Import.Mdcmbx.Text = "SEA" Then
cmd = New SqlCommand("select * from imp_sea where JbNo='" + Import.Jbtxt.Text + "'", con)
dr = cmd.ExecuteReader
If dr.Read Then
xlws.Range("B8").Value = dr(3)
xlws.Range("C8").Value = dr(4)
xlws.Range("B10").Value = dr(5)
xlws.Range("F10").Value = dr(7)
xlws.Range("B11").Value = dr(6)
xlws.Range("F11").Value = dr(8)
xlws.Range("B12").Value = dr(9)
xlws.Range("F12").Value = dr(11)
xlws.Range("B13").Value = dr(14)
End If
dr.Close()
ElseIf Import.Mdcmbx.Text = "AIR" Then
cmd = New SqlCommand("select * from imp_air where JobNo='" + Import.Jbtxt.Text + "'", con)
dr = cmd.ExecuteReader
If dr.Read Then
xlws.Range("B8").Value = dr(3) & " " & dr(4)
xlws.Range("B10").Value = dr(5)
xlws.Range("F10").Value = dr(7)
xlws.Range("B11").Value = dr(6)
xlws.Range("F11").Value = dr(8)
xlws.Range("B12").Value = dr(9)
xlws.Range("F12").Value = dr(11)
xlws.Range("B13").Value = dr(14)
End If
dr.Close()
End If
cmd = New SqlCommand("SELECT * FROM imp_inv where jobno='" + Import.Jbtxt.Text + "'", con)
dr = cmd.ExecuteReader
If dr.Read Then
xlws.Range("B15").Value = dr(2)
xlws.Range("C15").Value = dr(3)
xlws.Range("F15").Value = dr(25)
xlws.Range("F16").Value = dr(26)
xlws.Range("F17").Value = dr(27)
xlws.Range("B16").Value = dr(16)
xlws.Range("C16").Value = dr(10)
xlws.Range("E16").Value = dr(13)
xlws.Range("B17").Value = dr(32)
xlws.Range("C17").Value = dr(33)
xlws.Range("B18").Value = dr(35)
xlws.Range("C18").Value = dr(36)
xlws.Range("B21").Value = dr(44)
xlws.Range("E21").Value = dr(45)
xlws.Range("A28").Value = dr(0)
End If
dr.Close()
cmd = New SqlCommand("select * from SVB where invno='" + Invtxt.Text + "' and jbno='" + Invoice.JbNotxt.Text + "'", con)
dr = cmd.ExecuteReader
If dr.Read Then
xlws.Range("C19").Value = dr(6)
xlws.Range("C20").Value = dr(8)
End If
dr.Close()
If Invoice.Hsstxt.Text = "Y" Then
cmd = New SqlCommand("select * from inv_hss where invno='" + Invtxt.Text + "' and jbno='" + Invoice.JbNotxt.Text + "'", con)
dr = cmd.ExecuteReader
If dr.Read Then
xlws.Range("F20").Value = dr(2)
xlws.Range("H20").Value = dr(3)
End If
dr.Close()
End If
cmd = New SqlCommand("select * from Imp_invdesc where InvNo='" + Invtxt.Text + "'", con)
dr = cmd.ExecuteReader
If dr.Read Then
xlws.Range("B28").Value = dr(12)
xlws.Range("C28").Value = dr(11)
xlws.Range("A29").Value = dr(19)
xlws.Range("C29").Value = dr(21)
xlws.Range("D29").Value = dr(13)
xlws.Range("A30").Value = dr(20)
xlws.Range("D30").Value = dr(14)
End If
dr.Close()
'cmd = New SqlCommand("select * from cal where jbno='" + Invoice.JbNotxt.Text + "' and invno='" + Invtxt.Text + "'", con)
'dr = cmd.ExecuteReader
'If dr.Read Then
'BCD(amt)
xlws.Range("I29").Value = bdv
'ass(Val)
xlws.Range("C30").Value = asvl
'cvd(amt)
xlws.Range("I30").Value = cvd
'edu cess 2%
xlws.Range("I31").Value = ec
'edu cess 1%
xlws.Range("I32").Value = ec1
'cust edu cess 2%
xlws.Range("I33").Value = cec
'cust edu cess 1% '
xlws.Range("I34").Value = cec1
'addl dty '
xlws.Range("I35").Value = ad
'total dty 4% '
xlws.Range("E35").Value = td
'1 inc ass val '
xlws.Range("B37").Value = asvl
'bcd+cvd+edu 2 1+cedu 2 1+addl+ '
xlws.Range("I37").Value = asvl / 0.04
'ass(Val) '
xlws.Range("C38").Value = asvl
'total(dty) '
xlws.Range("I38").Value = td
'bcd(amt) '
xlws.Range("C39").Value = bdv
'cvd(amt) '
xlws.Range("C41").Value = cvd
'edu cess cvd '
xlws.Range("C44").Value = ec + ec1
'cust edu cess '
xlws.Range("I44").Value = cec + cec1
'addl(dty) '
xlws.Range("I45").Value = ad
'schedule cess cvd '
xlws.Range("C46").Value = cec
'schedule cust edu cess '
xlws.Range("I46").Value = cec1
'duty(payable) '
xlws.Range("I48").Value = td
'End If
'dr.Close()
cmd = New SqlCommand("select * from cess where invno='" + Invtxt.Text + "' and jbno='" + Invoice.JbNotxt.Text + "'", con)
dr = cmd.ExecuteReader
If dr.Read Then
xlws.Range("C42").Value = dr(2)
End If
dr.Close()
cmd = New SqlCommand("select * from ENM where invno='" + Invtxt.Text + "' and jbno='" + Invoice.JbNotxt.Text + "'", con)
dr = cmd.ExecuteReader
If dr.Read Then
xlws.Range("E29").Value = dr(0)
xlws.Range("I39").Value = dr(11)
xlws.Range("I40").Value = dr(14)
xlws.Range("I41").Value = dr(8)
xlws.Range("C43").Value = dr(10)
xlws.Range("C45").Value = dr(12)
End If
dr.Close()
If xlws.Range("E1").Value = "HOME" Then
xlws.Range("D3").Value = "N/H"
ElseIf xlws.Range("E1").Value = "BOND" Then
xlws.Range("D3").Value = "N/WH"
ElseIf xlws.Range("E1").Value = "EX-BOND" Then
xlws.Range("D3").Value = "N/EX"
End If
For r = 1 To rng.Rows.Count
For cl = 1 To rng.Columns.Count
Obj = CType(rng.Cells(r, cl), Excel.Range)
xla.Visible = True
Next
Next
xlws.SaveAs("D:\Manju\Reports\BE\" + Invoice.JbNotxt.Text + ".xlsx")
Plz help...
MultiQuote
| 