VB.net 中读些excel问题,请看看代码帮忙解决。
我下边的代码,其实主要完成的是 读取excel模板,当然模板中的表格等等都是已经画好的,它的位置是第一个sheet 名字为sheet0,读取模板后,拷贝sheet0到sheet1,sheet2等sheet上,当然根据翻页情况来定,数据库里的数据也只是往sheet1,sheet2等上填写,sheet0上是空的模板,但是我想写完数据再把sheet0删除,不知道可不可以直接删除? 我在'***************************************** 注释中两行代码试图删除,但是实现不了,当然还有种方法就是把所有对象释放后,再打开这个文件删除,这是可以实现的,但是不知道有没有一种方法,可不可以在对象没有释放前就可以删除,省得再去打开文件?忘高手赐教!!!!
Private Sub WriteExcel()
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheetBat As Excel.Worksheet
Try
Dim dlg As SaveFileDialog = New SaveFileDialog()
Dim strPathMoudle As String
Dim strPathExcel As String
Dim intCount, i, j, k, m As Integer
Dim strDlyCode As String = ""
Dim strShipSeq As String = ""
strPathMoudle = GetFilePath("JS\Report\JS004L01.xls")
If Not (File.Exists(strPathMoudle)) Then
MsgBox("NO Excel Module")
Return
End If
dlg.DefaultExt = "xls"
dlg.DefaultExt = "JS004L01.xls"
dlg.Filter = "Excel(*.xls)|*.xls"
dlg.FileName = "JS004L01.xls"
If (dlg.ShowDialog() <> System.Windows.Forms.DialogResult.OK) Then
Return
End If
strPathExcel = dlg.FileName
If strPathExcel = "" Then
MsgBox("Excel File Name Do not Null ")
Return
End If
xlBook = xlApp.Workbooks.Open(strPathMoudle)
xlSheet = xlBook.Worksheets(1)
'xlSheetBat = xlBook.Worksheets(1)
intCount = dsPrint.Tables(0).Rows.Count
j = 1
For i = 0 To intCount - 1
'change page when dlycode or shipseq change
If (Not strDlyCode.Equals(dsPrint.Tables(0).Rows(i).Item("SO_DLYCODE"))) Or (Not strShipSeq.Equals(dsPrint.Tables(0).Rows(i).Item("SH_SHIPSEQ"))) Then
k = 0
xlBook.Worksheets(1).Copy(After:=xlBook.Worksheets(j))
xlSheet = xlBook.Worksheets(j + 1)
xlSheet.Name = "sheet" + j.ToString
'write excel head
With xlSheet
.Cells(21, 44).value = dsPrint.Tables(0).Rows(i).Item("SH_SHIPSEQ")
.Cells(28, 9).value = dsPrint.Tables(0).Rows(i).Item("SO_DLYCODE") + " " + dsPrint.Tables(0).Rows(i).Item("CUS_NAMEE")
.Cells(28, 44).value = dsPrint.Tables(0).Rows(i).Item("SH_SHIPDATE")
.Cells(31, 9).value = dsPrint.Tables(0).Rows(i).Item("CUS_ADDE1")
.Cells(34, 1).value = dsPrint.Tables(0).Rows(i).Item("CUS_ADDE2")
.Cells(37, 1).value = dsPrint.Tables(0).Rows(i).Item("CUS_ADDE3")
End With
j = j + 1
End If
strDlyCode = dsPrint.Tables(0).Rows(i).Item("SO_DLYCODE")
strShipSeq = dsPrint.Tables(0).Rows(i).Item("SH_SHIPSEQ")
m = 45 + k
'write excel detail
With xlSheet
'if line is 75 ,then insert a line
If m >= 75 Then
'copy line(m) and insert
.Application.ActiveSheet.Rows(m).Copy()
.Application.ActiveSheet.Rows(m).insert()
End If
.Cells(m, 1).value = k.ToString
.Cells(m, 6).value = dsPrint.Tables(0).Rows(i).Item("SO_ITMCODE")
.Cells(m, 15).value = dsPrint.Tables(0).Rows(i).Item("ITM_NAME1")
.Cells(m, 32).value = dsPrint.Tables(0).Rows(i).Item("SH_SHIPQTY")
.Cells(m, 38).value = dsPrint.Tables(0).Rows(i).Item("ITM_PRODUNT")
.Cells(m, 43).value = dsPrint.Tables(0).Rows(i).Item("SO_CUSORD")
End With
k = k + 1
Next
'*****************************************
'delete sheet0
'xlSheet = xlBook.Worksheets(1)
'xlSheet.Delete()
'*****************************************
'save Excel
xlBook.SaveAs(strPathExcel)
xlSheet = Nothing
xlSheetBat = Nothing
xlBook.Close()
xlBook = Nothing
xlApp = Nothing
'open excel file
System.Diagnostics.Process.Start(strPathExcel)
Finally
'xlApp.Visible = False
'xlSheet = Nothing
'xlSheetBat = Nothing
'xlBook.Close()
'xlBook = Nothing
'xlApp = Nothing
End Try
End Sub --------------------编程问答-------------------- 难道就没有高手吗? --------------------编程问答-------------------- Reading and Writing Excel using OLEDB
http://www.codeproject.com/office/excel_using_oledb.asp
补充:.NET技术 , VB.NET