Need Help--Create a new excel sheet based on a condition ???? RSS

• • Reply
  

  ryan_r

  Member

  110 Points

  134 Posts

  Need Help--Create a new excel sheet based on a condition ????

  Jul 07, 2008 08:03 AM|LINK

   Hi I have a code that can create an excel workbook in vb.net language, anyway here's the code:

   

  Private Sub PrintExcel()
          'contents
  
  
          Dim DtSalesPersonFrom As String = Trim(Window.txtSalesPersonFrom.value)
          Dim DtSalesPersonTo As String = Trim(Window.txtSalesPersonTo.value)
          Dim dtmOrdDateFrom As DateTime = CDate(Window.txtDateParamFrom.value)
          Dim dtmOrdDateTo As DateTime = CDate(Window.txtDateParamTo.value)
          Dim exc_tempitem As String
          Dim exc_itemarray As New ArrayList
  
  
          Dim c As Integer = 7
          Dim d As Integer = 7
          Dim f As Integer = 7
  
  
          Dim t As Integer = 7
          Dim w As Integer = 7
          Dim q As Integer = 7
  
  
          Try
  
  
              m_itmOrder = m_ObjRptTest.GetItemOrder(DtSalesPersonFrom, DtSalesPersonTo, dtmOrdDateFrom, dtmOrdDateTo)
              For h As Integer = 0 To m_itmOrder.Count - 1
                  m_ObjItemDataList = CType(m_itmOrder(h), un_csItemOrderData)
  
  
                  exc_tempitem = m_ObjItemDataList.sz_ProductIDun
                  exc_itemarray.Add(exc_tempitem)
              Next
  
  
              m_dtOrder = m_ObjRptTest.GetCustOrder(DtSalesPersonFrom, DtSalesPersonTo, dtmOrdDateFrom, dtmOrdDateTo)
  
  
              Dim e As Integer
  
  
              For e = 0 To m_dtOrder.Count - 1
                  m_ObjCustDataList = CType(m_dtOrder(e), un_csCustOrderData)
  
  
                  oSheet.Cells(c, 1) = m_ObjCustDataList.sz_CustIDun
                  oSheet.Cells(d, 2) = m_ObjCustDataList.sz_CustNameun
                  oSheet.Cells(f, 3) = m_ObjCustDataList.sz_OrderIDun
  
  
                  c += 1
                  d += 1
                  f += 1
  
  
                  m_itmdet = m_ObjRptTest.GetItemDetailsOrder(m_ObjCustDataList.sz_OrderIDun, DtSalesPersonFrom, DtSalesPersonTo, dtmOrdDateFrom, dtmOrdDateTo)
  
  
                  Dim s As Integer
  
  
                  For s = 0 To m_itmdet.Count - 1
                      m_ObjItemDetDataList = CType(m_itmdet(s), un_csItemOrderDetailsData)
  
  
                      Dim cellitem As Integer = 0
                      Dim cellqty As Integer = 0
                      Dim tempcell As Integer = 0
  
  
                      'If e = 0 Then
                      '    w = w
                      '    q = q
  
  
  
  
                      '    tempcell = exc_itemarray.BinarySearch(m_ObjItemDetDataList.sz_ProductIDDetun)
                      '    cellitem = (4 + tempcell) + tempcell
                      '    cellqty = (5 + tempcell) + tempcell
  
  
                      '    oSheet.Cells(w, cellitem) = m_ObjItemDetDataList.sz_ProductQtyDetun
                      '    oSheet.Cells(q, cellqty) = m_ObjItemDetDataList.sz_ProductPriceDetun
  
  
                      'ElseIf e > 0 Then
  
  
                          tempcell = exc_itemarray.BinarySearch(m_ObjItemDetDataList.sz_ProductIDDetun)
                          cellitem = (4 + tempcell) + tempcell
                          cellqty = (5 + tempcell) + tempcell
  
  
                          oSheet.Cells(w, cellitem) = m_ObjItemDetDataList.sz_ProductQtyDetun
                          oSheet.Cells(q, cellqty) = m_ObjItemDetDataList.sz_ProductPriceDetun
  
  
                          w = w
                          q = q
  
  
                      'End If
                  Next
                  w += 1
                  q += 1
              Next

  I want to make a condition later on like, if the date changes it will create a new sheet based on the looped data. So, what I want to know is do you guys know the syntax for creating a new sheet in vb.net language ??? please. Thanks guys.

  FYI: I use Visual Studio .NET 2003

   

  Best Regards 

  --Please put "Mark as Answer" if I help you solve your problem, Thanks.
  
  "Vanity is my favorite sins" -- John Milton/Satan ( Al Pacino )