16,550
社区成员
发帖
与我相关
我的任务
分享
Imports System.Windows.Forms
Imports System.IO
Public Class OutputExcel
Dim excelApp As New Microsoft.Office.Interop.Excel.Application
Dim excelBook As Microsoft.Office.Interop.Excel.Workbook = excelApp.Workbooks.Open(System.Windows.Forms.Application.StartupPath & "\Mode.xls")
Dim excelWorksheet As Microsoft.Office.Interop.Excel.Worksheet
Dim SqlObject As New SQL_Conn
Dim SqlTable As DataTable
Dim ExcelSaveFileDia As New SaveFileDialog
Public Sub OutputSalaryView()
excelWorksheet = excelBook.Sheets.Item(1)
Try
SqlTable = SqlObject.SQL_Ada("select * from Salary_In_All")
With excelWorksheet
.Range(.Cells(1, 1), .Cells(1, SqlTable.Columns.Count)).Merge()
.Range(.Cells(2, 1), .Cells(2, SqlTable.Columns.Count)).Merge()
.Range("A1").Value = Now.Month.ToString + " 月工资表"
.Range("A1").Font.Size = 17
.Range("A1").Font.Name = "黑体"
.Range("A2").Value = Now.ToString + " 制"
For i = 0 To SqlTable.Columns.Count - 1
.Cells(3, i + 1) = SqlTable.Columns.Item(i).ColumnName.ToString
Next i
For i = 0 To SqlTable.Rows.Count - 1
For j = 0 To SqlTable.Columns.Count - 1
.Cells(i + 4, j + 1) = SqlTable.Rows(i).Item(j).ToString
Next j
Next i
End With
ExcelSaveFileDia.InitialDirectory = Application.StartupPath
ExcelSaveFileDia.Filter = "Excel表格(*.xls)|*.xls"
ExcelSaveFileDia.OverwritePrompt = True
ExcelSaveFileDia.ShowDialog()
excelBook.SaveAs(ExcelSaveFileDia.FileName)
Catch ex As Exception
Finally
excelBook.Close()
excelApp.Quit()
excelWorksheet = Nothing
excelBook = Nothing
excelApp = Nothing
For Each pro In Process.GetProcesses
If pro.ProcessName = "EXCEL" Then
pro.Kill()
End If
Next
End Try
End Sub
Public Sub OutputInfoView()
excelWorksheet = excelBook.Sheets.Item(2)
Try
SqlTable = SqlObject.SQL_Ada("exec selectinfo ''")
With excelWorksheet
.Range(.Cells(1, 1), .Cells(1, SqlTable.Columns.Count)).Merge()
.Range(.Cells(2, 1), .Cells(2, SqlTable.Columns.Count)).Merge()
.Range("A1").Value = "公司人员信息一览表"
.Range("A1").Font.Size = 17
.Range("A1").Font.Name = "黑体"
.Range("A2").Value = Now.ToString + " 制"
For i = 0 To SqlTable.Columns.Count - 1
.Cells(3, i + 1) = SqlTable.Columns.Item(i).ColumnName.ToString
Next i
For i = 0 To SqlTable.Rows.Count - 1
For j = 0 To SqlTable.Columns.Count - 1
.Cells(i + 4, j + 1) = SqlTable.Rows(i).Item(j).ToString
Next j
Next i
End With
excelWorksheet.Move(excelBook.Sheets.Item(1))
ExcelSaveFileDia.InitialDirectory = Application.StartupPath
ExcelSaveFileDia.Filter = "Excel表格(*.xls)|*.xls"
ExcelSaveFileDia.OverwritePrompt = True
ExcelSaveFileDia.ShowDialog()
excelBook.SaveAs(ExcelSaveFileDia.FileName)
Catch ex As Exception
Finally
excelBook.Close()
excelApp.Quit()
excelWorksheet = Nothing
excelBook = Nothing
excelApp = Nothing
Dim pro As Process
For Each pro In Process.GetProcesses
If pro.ProcessName = "EXCEL" Then
pro.Kill()
End If
Next
End Try
End Sub
Public Sub OutputVacationView()
excelWorksheet = excelBook.Sheets.Item(3)
Try
SqlTable = SqlObject.SQL_Ada("exec selectvacation '',0")
With excelWorksheet
.Range(.Cells(1, 1), .Cells(1, SqlTable.Columns.Count)).Merge()
.Range(.Cells(2, 1), .Cells(2, SqlTable.Columns.Count)).Merge()
.Range("A1").Value = Now.Month.ToString + " 月加班请假情况表"
.Range("A1").Font.Size = 17
.Range("A1").Font.Name = "黑体"
.Range("A2").Value = Now.ToString + " 制"
For i = 0 To SqlTable.Columns.Count - 1
.Cells(3, i + 1) = SqlTable.Columns.Item(i).ColumnName.ToString
Next i
For i = 0 To SqlTable.Rows.Count - 1
For j = 0 To SqlTable.Columns.Count - 1
.Cells(i + 4, j + 1) = SqlTable.Rows(i).Item(j).ToString
Next j
Next i
.Range(.Cells(1, 7), .Cells(SqlTable.Rows.Count + 3, 7)).Delete()
End With
excelWorksheet.Move(excelBook.Sheets.Item(1))
ExcelSaveFileDia.InitialDirectory = Application.StartupPath
ExcelSaveFileDia.Filter = "Excel表格(*.xls)|*.xls"
ExcelSaveFileDia.OverwritePrompt = True
ExcelSaveFileDia.ShowDialog()
excelBook.SaveAs(ExcelSaveFileDia.FileName)
Catch ex As Exception
Finally
excelBook.Close()
excelApp.Quit()
excelWorksheet = Nothing
excelBook = Nothing
excelApp = Nothing
For Each pro In Process.GetProcesses
If pro.ProcessName = "EXCEL" Then
pro.Kill()
End If
Next
End Try
End Sub
End Class