2014年5月6日 星期二

VBnet 將資料填進EXCEL,WORD中


WORD:
Imports Microsoft.Office.Interop

Dim wod_app As New Word.Application    '這邊要注意建立app時要用new
Dim wod_doc As Word.Document
Dim wod_tab As Word.Table

  wod_doc = wod_app.Documents.Open("C:\NEWS管理.doc", , True)
  wod_tab = wod_doc.Tables.Item(1)

  contSqlconn(1)    '個人的連接資料庫函式
  cmd.CommandText = "select * from hn_news where (流水號='" + 流水號.Text + "')"
  dr = cmd.ExecuteReader
  dr.Read()

    wod_tab.Cell(1, 1).Range.Text = dr("狀態旗標").ToString
    wod_tab.Cell(1, 2).Range.Text = dr("流水號").ToString
    wod_tab.Cell(1, 4).Range.Text = dr("標題").ToString
    wod_tab.Cell(2, 2).Range.Text = dr("內容").ToString
    wod_tab.Cell(4, 2).Range.Text = dr("回覆").ToString

  dr.Close()
  contSqlconn(-1)

  wod_app.Visible = True '預覽列印
  'wod_app.PrintOut(Copies:=1) '直接列印
  'wod_app.Quit(SaveChanges:=False) '關閉開啟的WORD

  wod_tab = Nothing

  wod_doc.Close()
  wod_doc = Nothing

  wod_app.Quit()
  wod_app = Nothing
  
  GC.Collect()


EXCEL:
Imports Microsoft.Office.Interop

  Dim exl_ap As New Excel.Application
  Dim exl_wkb As Excel.Workbook
  Dim exl_wks As Excel.Worksheet
  Dim i As Integer = 1

  exl_wkb = exl_app.Workbooks.Open("C:\驗購表.xls", , True) '這個TRUE是Readonly
  exl_wks = exl_wkb.Worksheets("sheet1") '或者 exl_wks = exl_wkb.Worksheets(1)

  contSqlconn(1)
  cmd.CommandText = "sql語法省略" + _
  dr = cmd.ExecuteReader
    With exl_wks
      .Name = "藥品驗購表"
      .Range("A" & i).Value = "藥品中文名"
      .Range("B" & i).Value = "藥品索引"
      .Range("C" & i).Value = "藥品簡稱"
      .Range("D" & i).Value = "驗收日期"
      .Range("E" & i).Value = "藥廠名稱"
      .Range("F" & i).Value = "藥廠代號"
      .Range("A1:F1").Interior.ColorIndex = 6
      .Cells().ColumnWidth = 7

    End With
  While dr.Read
    i += 1
    With exl_wks
      .Range("A" & i).Value = dr(0).ToString
      .Range("B" & i).Value = dr(1).ToString
      .Range("C" & i).Value = dr(2).ToString
      .Range("D" & i).Value = dr(3).ToString
      .Range("E" & i).Value = dr(4).ToString
      .Range("F" & i).Value = dr(5).ToString
    End With
  End While

  exl_app.SaveWorkspace()
  exl_app.Visible = True

  exl_wks = Nothing

  exl_wkb.Close()
  exl_wkb = Nothing

  exl_app.Quit()
  exl_app = Nothing

  contSqlconn(-1)
  GC.Collect()

沒有留言:

張貼留言