|
|
|
สอบถาม Code VB.net ที่ใช้ Export ข้อมูลจาก SQL เป็นไฟล์ Excel แบบ 1 ไฟล์ Excel มีหลาย WorkSheet ค่ะ |
|
|
|
|
|
|
|
โดยปกติผมทำดึงแค่ Sheet เดียวหลาย ๆ Sheet ไม่เคย ตามนี้เลย
Code (VB.NET)
Private Sub excelHexa()
If MessageBox.Show("ต้องการออกรายงานข้อมูล หรือไม่", "ยืนยันการออกรายงาน", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.Yes Then
Dim excel8 As New Excel.Application
Dim cu As System.Threading.Thread
cu = System.Threading.Thread.CurrentThread
cu.CurrentUICulture = New CultureInfo("en-US")
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
excelapp.Visible = True
excelbooks = excelapp.Workbooks.Add
excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
Try
With excelsheets
Dim A As Integer
For A = 1 To 1
.Range("A1:J10").Font.Name = "Tahoma"
.Range("A" & A.ToString & ":J" & A.ToString).Merge()
With .Range("A1:J1")
.Font.Bold = True
.Font.Underline = True
.Value = "บริษัท .... รวมทุนจำกัด จำกัด"
.Font.Size = 18
.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
End With
Next
Dim H As Integer
For H = 2 To 2
.Range("A" & H.ToString & ":J" & H.ToString).Merge()
.Range("A" & H.ToString & ":J" & H.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
With .Range("A2:J2")
.Font.Bold = True
.Value = "ใบแสดงรายงานเกี่ยวกับรายการ SCAN FINGER (สแกนนิ้วมือ) แบบรายวัน / รายบุคคล"
.Font.Size = 14
End With
Next
Dim G As Integer
For G = 3 To 3
.Range("A" & G.ToString & ":J" & G.ToString).Merge()
.Range("A" & G.ToString & ":J" & G.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
With .Range("A3:J3")
' Dim Buttoms As String = ""
.Value = " พิมพ์รายงาน ณ วันที่ " & Today.ToShortDateString()
.Font.Size = 10
End With
Next
.Range("A4:K35").Font.Name = "Tahoma"
.Columns().ColumnWidth = 15
.Range("A4").Value = "ลำดับ"
.Range("A4").Font.Size = 10
.Range("A4").Font.Color = RGB(255, 255, 255)
.Range("A4").Interior.Color = RGB(255, 69, 28)
.Range("B4").Value = "รหัสพนักงาน"
.Range("B4").Font.Size = 10
.Range("B4").Font.Color = RGB(255, 255, 255)
.Range("B4").Interior.Color = RGB(255, 69, 28)
.Range("C4").Value = "วัน"
.Range("C4").Font.Size = 10
.Range("C4").Font.Color = RGB(255, 255, 255)
.Range("C4").Interior.Color = RGB(255, 69, 28)
.Range("D4").Value = "เข้างาน"
.Range("D4").Font.Size = 10
.Range("D4").Font.Color = RGB(255, 255, 255)
.Range("D4").Interior.Color = RGB(255, 69, 28)
.Range("E4").Value = "ออกงาน"
.Range("E4").Font.Size = 10
.Range("E4").Font.Color = RGB(255, 255, 255)
.Range("E4").Interior.Color = RGB(255, 69, 28)
.Range("F4").Value = "เวลาเข้างาน"
.Range("F4").Font.Size = 10
.Range("F4").Font.Color = RGB(255, 255, 255)
.Range("F4").Interior.Color = RGB(255, 69, 28)
.Range("G4").Value = "เวลาออกงาน"
.Range("G4").Font.Size = 10
.Range("G4").Font.Color = RGB(255, 255, 255)
.Range("G4").Interior.Color = RGB(255, 69, 28)
.Range("H4").Value = "หมายเหตุ"
.Range("H4").Font.Size = 10
.Range("H4").Font.Color = RGB(255, 255, 255)
.Range("H4").Interior.Color = RGB(255, 69, 28)
.Range("I4").Value = "คำนำหน้านาม"
.Range("I4").Font.Size = 10
.Range("I4").Font.Color = RGB(255, 255, 255)
.Range("I4").Interior.Color = RGB(255, 69, 28)
.Range("J4").Value = "ชื่อพนักงาน"
.Range("J4").Font.Size = 10
.Range("J4").Font.Color = RGB(255, 255, 255)
.Range("J4").Interior.Color = RGB(255, 69, 28)
.Range("K4").Value = "นามสกุล"
.Range("K4").Font.Size = 10
.Range("K4").Font.Color = RGB(255, 255, 255)
.Range("K4").Interior.Color = RGB(255, 69, 28)
'--------------------------------------------------------------------------------------
Dim CountDate(DateDiff(DateInterval.Day, dtpTimeIn.Value, dtpTimeout.Value)) As Date
Dim n As Integer, i As Integer = 0, j As Integer = 4, v As Byte = 0
For n = 0 To DateDiff(DateInterval.Day, dtpTimeIn.Value, dtpTimeout.Value)
CountDate(n) = DateAdd(DateInterval.Day, n, dtpTimeIn.Value)
v = 0
'On Error Resume Next
'For j = 5 To (5 + (DataGridView2.RowCount - 1))
For i = 0 To DataGridView2.Rows.Count - 1
If CountDate(n).Date.ToString("dd-MM-yyyy") = CDate(DataGridView2.Rows(i).Cells("TimeIn").Value).ToString("dd-MM-yyyy") Then
v = 1
j += 1
.Range("A" & j.ToString()).Value = DataGridView2.Rows(i).Cells("ID").Value.ToString
.Range("A" & j.ToString()).Font.Size = 10
.Range("A" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("B" & j.ToString()).Value = DataGridView2.Rows(i).Cells("ID_Emp").Value.ToString
.Range("B" & j.ToString()).Font.Size = 10
.Range("B" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("C" & j.ToString()).Value = CDate(DataGridView2.Rows(i).Cells("TimeIn").Value).ToString("dddd")
.Range("C" & j.ToString()).Font.Size = 10
.Range("C" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("D" & j.ToString()).Value = DataGridView2.Rows(i).Cells("TimeIn").Value.ToString
.Range("D" & j.ToString()).Font.Size = 10
.Range("D" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("E" & j.ToString()).Value = DataGridView2.Rows(i).Cells("TimeOut").Value.ToString
.Range("E" & j.ToString()).Font.Size = 10
.Range("E" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("F" & j.ToString()).Value = DataGridView2.Rows(i).Cells("timeworkIn").Value.ToString
.Range("F" & j.ToString()).Font.Size = 10
.Range("F" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("G" & j.ToString()).Value = DataGridView2.Rows(i).Cells("timeworkOut").Value.ToString
.Range("G" & j.ToString()).Font.Size = 10
.Range("G" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("H" & j.ToString()).Value = DataGridView2.Rows(i).Cells("comment").Value.ToString
.Range("H" & j.ToString()).Font.Size = 10
.Range("H" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("I" & j.ToString()).Value = DataGridView2.Rows(i).Cells("Antacedent").Value.ToString
.Range("I" & j.ToString()).Font.Size = 10
.Range("I" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("J" & j.ToString()).Value = DataGridView2.Rows(i).Cells("Name").Value.ToString
.Range("J" & j.ToString()).Font.Size = 10
.Range("J" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("K" & j.ToString()).Value = DataGridView2.Rows(i).Cells("Surname").Value.ToString
.Range("K" & j.ToString()).Font.Size = 10
.Range("K" & j.ToString()).Font.Color = RGB(34, 54, 34)
End If
Next
If v = 0 Then
j += 1
.Range("C" & j.ToString()).Value = CountDate(n).ToString("dddd")
.Range("C" & j.ToString()).Font.Size = 10
.Range("C" & j.ToString()).Font.Color = RGB(34, 54, 34)
.Range("D" & j.ToString()).Value = FormatDateTime(CountDate(n), DateFormat.ShortDate) & " 00:00" 'CountDate(n).Date.ToString("dd/MM/yyyy") & " 00:00"
.Range("D" & j.ToString()).Font.Size = 10
.Range("D" & j.ToString()).Font.Color = RGB(34, 54, 34)
End If
Next
End With
'-------------------------------------------------
Dim proc As System.Diagnostics.Process
'proc = System.Diagnostics.Process.GetProcessById(CInt(Me.TextBox1.Text)) ' อันนี้กรณีคุณต้องการระบุ Process ID
proc = System.Diagnostics.Process.GetProcessesByName("EXCEL", My.Computer.Name)(0) ' อันนี้กรณีคุณต้องการทำงานกับ Process แรกสุด ที่จริงควรสร้าง array มาทำงาน แล้วสร้างลูปเพื่อหา process ที่ต้องการ
If Not IsNothing(proc) Then
If MsgBox("EXPORT ข้อมูล " & proc.ProcessName & " เรียบร้อยแล้ว", MsgBoxStyle.OkOnly) = MsgBoxResult.Ok Then
' proc.Kill()
proc.Dispose()
Application.Restart()
End If
End If
'-------------------------------------------------
Catch ex As Exception
' MsgBox(ex.Message)
End Try
End If
'Con_CallData.Close()
End Sub
รายงานก็ออกมาประมานนี้แหละครับ
|
|
|
|
|
Date :
2013-12-24 16:31:42 |
By :
angelrings0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
EPPlus
http://epplus.codeplex.com/
|
|
|
|
|
Date :
2013-12-24 17:21:27 |
By :
fonfire |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Load balance : Server 05
|