- 浏览: 54991 次
- 性别:
- 来自: 上海
最新评论
Public Class input_timecard_syoukai Private shainCd As String = String.Empty Private shainNm As String = String.Empty Private koyouJouken As String = String.Empty Private db As ADODB.Connection 'DB接続 system1_db Private rs As ADODB.Recordset 'レコードセット Private Const system1_db As Integer = 1 Dim db_system1 As New SysproCon() Private SQL As String Private dtExcel As DataTable Private dr As DataRow Private xls As Excel.Application Private xBook As Excel.Workbook Private xsheet As Excel.Worksheet Private excel_name As String Private saveFileDialog As New SaveFileDialog '************************************************************************** '*処理名称:Form1_Load '*パラメーター :ByVal sender As System.Object '* ByVal e As System.EventArgs '*処理概要:画面上の項目初期化 '************************************************************************** Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load '画面上の項目初期化 Chk_kikan.Checked = False Chk_ten.Checked = False Tbx_shain_cd_sel.Text = "000165" Chk_kikan.Checked = True Tbx_startDayymd.Text = "2010/01/01" Tbx_overDayymd.Text = "2010/10/10" getTenList() Creat_DataTable() End Sub '************************************************************************** '*処理名称:getTenList '*パラメーター :ByVal sender As System.Object '* ByVal e As System.EventArgs '*処理概要:店リストデータ取得 '************************************************************************** Public Sub getTenList() Dim ten As String = "" Try db = db_system1.DnsIn(system1_db) rs = New ADODB.Recordset() SQL = "" SQL &= "select " SQL &= " ten_cd, " SQL &= " ten_nam " SQL &= " from " SQL &= " tbl_tenpo " SQL &= " where " SQL &= " ten_cd >= 400 " SQL &= " and " SQL &= " close_date is null " SQL &= " order by " SQL &= " ten_cd asc " rs.Open(SQL, db, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic) Me.ten_combox.Items.Clear() If Not rs.EOF Then Do Until rs.EOF Dim item As TextValue = New TextValue() item.Value = Trim(rs.Fields("ten_cd").Value) item.Text = Trim(rs.Fields("ten_cd").Value) & " " & Trim(rs.Fields("ten_nam").Value) ten_combox.Items.Add(item) rs.MoveNext() Loop End If ten_combox.DisplayMember = "Text" ten_combox.ValueMember = "Value" Me.ten_combox.SelectedIndex = -1 Catch ex As Exception MsgBox(ex.Message, MsgBoxStyle.Critical, "エラー") Finally rs.Close() rs = Nothing db.Close() db = Nothing End Try End Sub '************************************************************************** '*処理名称:Creat_DataTable '*パラメーター :ByVal sender As System.Object '* ByVal e As System.EventArgs '*処理概要:ログインする時基準日の月取得 '************************************************************************** Public Sub Creat_DataTable() dtExcel = New DataTable() dtExcel.Columns.Add("hiduke", System.Type.GetType("System.String")) '日付 dtExcel.Columns.Add("NO", System.Type.GetType("System.String")) 'NO dtExcel.Columns.Add("renban", System.Type.GetType("System.String")) '連番 dtExcel.Columns.Add("tenmei", System.Type.GetType("System.String")) '店名 dtExcel.Columns.Add("goWork", System.Type.GetType("System.String")) '出勤 dtExcel.Columns.Add("toutyaku", System.Type.GetType("System.String")) '到着 dtExcel.Columns.Add("outOfWork", System.Type.GetType("System.String")) '退勤 dtExcel.Columns.Add("kyuukei", System.Type.GetType("System.String")) '休憩 dtExcel.Columns.Add("modoru", System.Type.GetType("System.String")) '戻り dtExcel.Columns.Add("kyuukeibunn", System.Type.GetType("System.String")) '休憩(分) dtExcel.Columns.Add("jitudou", System.Type.GetType("System.String")) '実働 dtExcel.Columns.Add("syultukinkitei", System.Type.GetType("System.String")) '出勤規程 dtExcel.Columns.Add("taikinkitei", System.Type.GetType("System.String")) '退勤規程 dtExcel.Columns.Add("idou", System.Type.GetType("System.String")) '移動 dtExcel.Columns.Add("tyoltukou", System.Type.GetType("System.String")) '直行 dtExcel.Columns.Add("tyokuki", System.Type.GetType("System.String")) '直帰 dtExcel.Columns.Add("yuukyuu", System.Type.GetType("System.String")) '有休 dtExcel.Columns.Add("motorenban", System.Type.GetType("System.String")) '元連番 End Sub '************************************************************************** '*処理名称:Chk_kikan_CheckedChanged '*パラメーター :ByVal sender As System.Object '* ByVal e As System.EventArgs '*処理概要:※期間指定チェック処理 '************************************************************************** Private Sub Chk_kikan_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Chk_kikan.CheckedChanged '【期間指定】チェックボックスをチェックした場合: 【開始日】テキストフィールドと【終了日】テキストフィールドは入力できるように設定する If Chk_kikan.Checked Then Me.Tbx_startDayymd.Enabled = True Me.Tbx_overDayymd.Enabled = True '【期間指定】チェックボックスをチェックしない場合:【開始日】テキストフィールドと【終了日】テキストフィールドは入力できないように設定する Else Me.Tbx_startDayymd.Enabled = False Me.Tbx_overDayymd.Enabled = False Me.Tbx_startDayymd.Text = "" Me.Tbx_overDayymd.Text = "" End If End Sub '************************************************************************** '*処理名称:Chk_ten_CheckedChanged '*パラメーター :ByVal sender As System.Object '* ByVal e As System.EventArgs '*処理概要:※店指定チェック処理 '************************************************************************** Private Sub Chk_ten_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Chk_ten.CheckedChanged '【店指定】チェックボックスをチェックした場合: 【店リスト】コンボボックスを選択できるように設定する If Me.Chk_ten.Checked Then Me.ten_combox.Enabled = True '【店指定】チェックボックスをチェックしない場合:【店リスト】コンボボックスを選択できないように設定する Else Me.ten_combox.Enabled = False Me.ten_combox.Text = "" End If End Sub Private Sub Tbx_shain_cd_sel_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Tbx_shain_cd_sel.TextChanged '社員CD指定チェック() If check_shainCd() = False Then Tbx_shain_cd_sel.Clear() Me.Tbx_shain_cd_sel.Focus() MsgBox("社員CDは数字で入力してください。") End If End Sub '************************************************************************** '*処理名称:check_shainCd '*処理概要:社員CD指定チェック:数字制限 '*処理結果:true or false '************************************************************************** Public Function check_shainCd() As Boolean Dim Tbx_shain_cd As String Tbx_shain_cd = Me.Tbx_shain_cd_sel.Text If Tbx_shain_cd.Trim().Length <> 0 Then If IsNumeric(Tbx_shain_cd) = False Then Return False End If End If Return True End Function '************************************************************************** '*処理名称:check_startDayymd '*処理概要:開始日 有効性チェック '*処理結果:true or false '************************************************************************** Public Function check_startDayymd() As Boolean '開始日 有効性チェック If Me.Tbx_startDayymd.Text.Length > 0 Then Dim str_date As String = Me.Tbx_startDayymd.Text If IsDate(str_date) = False Then Return False End If End If Return True End Function '************************************************************************** '*処理名称:check_overDayymd '*処理概要:終了日 有効性チェック '*処理結果:true or false '************************************************************************** Public Function check_overDayymd() As Boolean '終了日 有効性チェック If Me.Tbx_overDayymd.Text.Length > 0 Then Dim str_date As String = Me.Tbx_overDayymd.Text If IsDate(str_date) = False Then Return False End If End If Return True End Function Private Sub Tbx_overDayymd_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Tbx_overDayymd.KeyPress Select Case e.KeyChar Case "0" To "9", "/", ""c e.Handled = False Case Else e.Handled = True End Select End Sub Private Sub Tbx_shain_cd_sel_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) e.Handled = glbKeyCheck(Asc(e.KeyChar.ToString), 2) End Sub Private Sub Btn_search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_search.Click Dim startDayymd As String = String.Empty Dim overDayymd As String = String.Empty Dim ten_cd As String = String.Empty Dim count As Integer = 0 '社員CD指定を取得 If Me.Tbx_shain_cd_sel.Text.Trim.Length = 0 Then MsgBox("社員CD指定を入力してください。") Tbx_shain_cd_sel.Focus() Exit Sub ElseIf Me.Tbx_shain_cd_sel.Text.Trim.Length > 0 Then If check_shainCd() = False Then Me.Tbx_shain_cd_sel.Focus() MsgBox("社員CDは数字で入力してください。") Tbx_shain_cd_sel.Focus() Exit Sub Else shainCd = Me.Tbx_shain_cd_sel.Text.Trim End If End If '期間指定と店指定をチェック If Chk_kikan.Checked = False And Chk_ten.Checked = False Then MsgBox("検索条件を選らんでください") Exit Sub End If '期間指定をチェック If Me.Chk_kikan.Checked Then '開始日 If Me.Tbx_startDayymd.Text.Trim.Length = 0 Then MsgBox("開始日を入力してください") Me.Tbx_startDayymd.Focus() Exit Sub ElseIf check_startDayymd() = False Then MsgBox("開始日の入力内容に誤りがありますす") Me.Tbx_startDayymd.Focus() Exit Sub Else startDayymd = Me.Tbx_startDayymd.Text.Trim End If '終了日 If Me.Tbx_overDayymd.Text.Trim.Length = 0 Then MsgBox("終了日を入力してください") Me.Tbx_overDayymd.Focus() Exit Sub ElseIf check_overDayymd() = False Then MsgBox("終了日の入力内容に誤りがあります") Me.Tbx_overDayymd.Focus() Exit Sub Else overDayymd = Me.Tbx_overDayymd.Text.Trim End If '【開始日】>【終了日】の場合 If CDate(Tbx_startDayymd.Text) > CDate(Tbx_overDayymd.Text) Then MsgBox("日付の入力内容に誤りがあります") Me.Tbx_startDayymd.Focus() Exit Sub End If End If '店指定をチェック If Me.Chk_ten.Checked Then If ten_combox.SelectedIndex = -1 OrElse ten_combox.Text.Length = 0 Then MsgBox("店を指定ください", MsgBoxStyle.Exclamation Or MsgBoxStyle.OkOnly, "店未選択") ten_combox.Focus() Exit Sub Else ten_cd = Me.ten_combox.SelectedItem.Value End If End If Me.timecard_syoukai.Items.Clear() dtExcel.Clear() Try db = db_system1.DnsIn(system1_db) rs = New ADODB.Recordset() SQL = "" SQL &= "select " SQL &= " count(*) as con" SQL &= " from " SQL &= " tbl_timecard_new_rireki " SQL &= " where " SQL &= " tc_emp_no='" + shainCd + "'" If startDayymd <> String.Empty And overDayymd <> String.Empty Then SQL &= " and " SQL &= " to_char(tc_date,'yyyy/mm/dd')<='" + overDayymd + "'" SQL &= " and " SQL &= " to_char(tc_date,'yyyy/mm/dd')>='" + startDayymd + "'" End If If ten_cd <> String.Empty Then SQL &= " and " SQL &= " tc_ten=" + ten_cd End If Try rs.Open(SQL, db, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic) Catch ex As Exception db.Close() db = Nothing count = 0 Exit Sub End Try If Not (rs.BOF And rs.EOF) Then count = rs.Fields("con").Value rs.Close() End If If count = 0 Then MsgBox("社員【" + shainCd + "】のタイムカード履歴は有りません。") Exit Sub Else SQL = "" SQL &= "select " SQL &= " to_char(t1.tc_date,'mm/dd') as tc_date" '打刻日 SQL &= " ,t1.tc_cnt as tc_cnt " '順番号 SQL &= " ,t1.tc_ren_no as tc_ren_no" '打刻連番 SQL &= " ,t1.tc_ten as tc_ten" '打刻店CD SQL &= " ,to_char(t1.tc_time_8,'hh24:mi') as tc_time_8" '出勤時刻 SQL &= " ,to_char(t1.tc_time_5,'hh24:mi') as tc_time_5" '到着時刻 SQL &= " ,to_char(t1.tc_time_7,'hh24:mi') as tc_time_7" '退勤時刻 SQL &= " ,to_char(t1.tc_time_2,'hh24:mi') as tc_time_2" '休憩時刻 SQL &= " ,to_char(t1.tc_time_3,'hh24:mi') as tc_time_3" '戻り時刻 SQL &= " ,to_char(t1.tc_time_3-t1.tc_time_2,'hh24:mi') as restTime" '「戻り時刻-休憩時刻」 休憩(分) SQL &= " ,to_char(t1.tc_time_7-t1.tc_time_8,'hh24:mi') as moveTime" '「退勤時刻-出勤時刻」 SQL &= " ,to_char(t1.tc_time_1,'hh24:mi') as tc_time_1" '規程時刻(出勤) SQL &= " ,to_char(t1.tc_time_4,'hh24:mi') as tc_time_4" '規程時刻(退勤) SQL &= " ,to_char(t1.tc_time_6,'hh24:mi') as tc_time_6" '「移動時刻」 SQL &= " ,t1.tc_choko as tc_choko " '「直行フラグ」 SQL &= " ,t1.tc_choko_tx as tc_choko_tx " '「直行理由」 SQL &= " ,t1.tc_choki as tc_choki" '「直帰フラグ」 SQL &= " ,t1.tc_choki_tx as tc_choki_tx" '「直帰理由」 SQL &= " ,t1.tc_yukyu as tc_yukyu" '有休フラグ SQL &= " ,t1.tc_orginal_ren_no as tc_orginal_ren_no" '「元打刻連番」 SQL &= " ,t2.ten_nam as ten_nam" '店名 SQL &= " from " SQL &= " tbl_timecard_new_rireki as t1" SQL &= " ,tbl_tenpo as t2" SQL &= " where " SQL &= " t1.tc_emp_no='" + shainCd + "'" SQL &= " and " SQL &= " t1.tc_ten=t2.ten_cd" If startDayymd <> String.Empty And overDayymd <> String.Empty Then SQL &= " and " SQL &= " to_char(t1.tc_date,'yyyy/mm/dd')<='" + overDayymd + "'" SQL &= " and " SQL &= " to_char(t1.tc_date,'yyyy/mm/dd')>='" + startDayymd + "'" End If If ten_cd <> String.Empty Then SQL &= " and " SQL &= " t1.tc_ten=" + ten_cd End If SQL &= " order by to_char(t1.tc_date,'mm/dd'), " SQL &= " tc_ten, " SQL &= " tc_orginal_ren_no, " SQL &= " tc_cnt, " SQL &= " tc_time_8, " SQL &= " tc_time_7 " Try rs.Open(SQL, db, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic) Catch ex As Exception db.Close() db = Nothing Exit Sub End Try Dim t1(2) As String Dim t2(2) As String Dim dbrestTime As String = String.Empty Dim dbmoveTime As String = String.Empty Dim tc_choko As String = String.Empty Dim tc_choki As String = String.Empty Dim restTime As Integer = 0 Dim restTime1 As Integer = 0 Dim moveTime As Integer = 0 Dim doTime As Double Dim tc_time_8 As String = String.Empty Dim tc_time_7 As String = String.Empty Dim tc_time_2 As String = String.Empty Dim tc_time_3 As String = String.Empty Dim tc_time_1 As String = String.Empty Dim tc_time_4 As String = String.Empty Dim tc_time_5 As String = String.Empty Dim tc_time_6 As String = String.Empty Dim yuukyuuFlg As String = String.Empty If Not (rs.BOF And rs.EOF) Then Do Until rs.EOF dbrestTime = check_dbnull(rs.Fields("restTime").Value) dbmoveTime = check_dbnull(rs.Fields("moveTime").Value) If dbrestTime.Trim() <> String.Empty Then t1 = dbrestTime.Split(":") End If If dbmoveTime.Trim <> String.Empty Then t2 = dbmoveTime.Split(":") End If Dim k As Integer = 0 If t1 IsNot Nothing Then '休憩(分) restTime1 = CInt(t1(0)) * 60 + CInt(t1(1)) End If If t2 IsNot Nothing Then '実働時間=「退勤時刻-出勤時刻-休憩時間(時間)」 moveTime = CInt(t2(0)) * 60 + CInt(t2(1)) doTime = Convert.ToDouble((moveTime - restTime1) / 60).ToString("0.00") End If dr = dtExcel.NewRow() dr("hiduke") = check_dbnull(rs.Fields("tc_date").Value) '日付 dr("NO") = check_dbnull(rs.Fields("tc_cnt").Value) 'NO dr("renban") = check_dbnull(rs.Fields("tc_ren_no").Value) '連番 dr("tenmei") = check_dbnull(rs.Fields("ten_nam").Value) '店名 tc_time_8 = check_dbnull(rs.Fields("tc_time_8").Value) dr("goWork") = tc_time_8 '出勤 tc_time_5 = check_dbnull(rs.Fields("tc_time_5").Value) dr("toutyaku") = tc_time_5 '到着 tc_time_7 = check_dbnull(rs.Fields("tc_time_7").Value) dr("outOfWork") = tc_time_7 '退勤 tc_time_2 = check_dbnull(rs.Fields("tc_time_2").Value) dr("kyuukei") = tc_time_2 '休憩 tc_time_3 = check_dbnull(rs.Fields("tc_time_3").Value) dr("modoru") = tc_time_3 '戻り If restTime1 > 0 Then dr("kyuukeibunn") = restTime1 '休憩(分) Else dr("kyuukeibunn") = "" '休憩(分) End If If doTime <= 0 Then dr("jitudou") = "" '実働 Else dr("jitudou") = doTime '実働 End If tc_time_1 = check_dbnull(rs.Fields("tc_time_1").Value) dr("syultukinkitei") = tc_time_1 '出勤規程 tc_time_4 = check_dbnull(rs.Fields("tc_time_4").Value) dr("taikinkitei") = tc_time_4 '退勤規程 tc_time_6 = check_dbnull(rs.Fields("tc_time_6").Value) dr("idou") = tc_time_6 '移動 tc_choko = check_dbnull(rs.Fields("tc_choko").Value) If tc_choko.Equals("t") Then dr("tyoltukou") = check_dbnull(rs.Fields("tc_choko_tx").Value) '直行 Else dr("tyoltukou") = "" End If tc_choki = check_dbnull(rs.Fields("tc_choki").Value) If tc_choki.Equals("t") Then dr("tyokuki") = check_dbnull(rs.Fields("tc_choki_tx").Value) '直帰 Else dr("tyokuki") = "" End If yuukyuuFlg = check_dbnull(rs.Fields("tc_yukyu").Value) If yuukyuuFlg <> String.Empty And yuukyuuFlg.Equals("1") Then dr("yuukyuu") = "※" '有休 Else dr("yuukyuu") = "" '有休 End If dr("motorenban") = check_dbnull(rs.Fields("tc_orginal_ren_no").Value) '元連番 dtExcel.Rows.Add(dr) rs.MoveNext() Loop End If rs.Close() rs = Nothing db.Close() db = Nothing End If Dim i As Integer Dim Lvi_item As ListViewItem For i = 0 To dtExcel.Rows.Count - 1 Lvi_item = New ListViewItem() Lvi_item.SubItems.Add(dtExcel.Rows(i)("hiduke").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("NO").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("renban").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("tenmei").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("goWork").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("toutyaku").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("outOfWork").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("kyuukei").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("modoru").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("kyuukeibunn").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("jitudou").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("syultukinkitei").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("taikinkitei").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("idou").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("tyoltukou").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("tyokuki").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("yuukyuu").ToString()) Lvi_item.SubItems.Add(dtExcel.Rows(i)("motorenban").ToString()) Me.timecard_syoukai.Items.AddRange(New ListViewItem() {Lvi_item}) Next Catch ex As Exception MsgBox(ex.Message, MsgBoxStyle.Critical, "エラー") End Try End Sub '************************************************************************** '*処理名称:getTenList '*パラメーター :ByVal sender As System.Object '* ByVal e As System.EventArgs '*処理概要:店リストデータ取得 '************************************************************************** Public Sub getshainInfo() SQL = "" SQL &= "select " SQL &= " emp_name, " SQL &= " koyou_jouken " SQL &= " from " SQL &= " tbl_emp " SQL &= " where " SQL &= " emp_no='" + shainCd + "'" Try db = db_system1.DnsIn(system1_db) rs = New ADODB.Recordset() rs.Open(SQL, db, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic) Catch ex As Exception rs.Close() rs = Nothing db.Close() db = Nothing Exit Sub End Try If Not rs.EOF Then Do Until rs.EOF shainNm = check_dbnull(rs.Fields("emp_name").Value) koyouJouken = check_dbnull(rs.Fields("koyou_jouken").Value) Exit Do Loop End If rs.Close() rs = Nothing db.Close() db = Nothing End Sub Private Sub Btn_print_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_print.Click Dim rCnt As Integer = 0 Dim iRows As Integer = 0 Dim iRowCnt As Integer = 0 rCnt = dtExcel.Rows.Count If rCnt > 0 Then Dim saveFileDialog As New SaveFileDialog '出力ファイル saveFileDialog.RestoreDirectory = True saveFileDialog.Filter = "Microsoft Excel ブック|*.xls" saveFileDialog.InitialDirectory = CurDir() saveFileDialog.FileName = "タイムカード履歴" If saveFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then Dim cnt As Integer Dim ExcelProIdBefore As Integer() = Nothing Dim ExcelProIdAfter As Integer() = Nothing Dim k As Integer = 0 Dim pro As Process For Each pro In Process.GetProcessesByName("EXCEL") ReDim Preserve ExcelProIdBefore(k) ExcelProIdBefore(k) = pro.Id k = k + 1 Next xls = New Excel.Application k = 0 For Each pro In Process.GetProcessesByName("EXCEL") ReDim Preserve ExcelProIdAfter(k) ExcelProIdAfter(k) = pro.Id k = k + 1 Next xBook = CType(xls.Workbooks.Add, Excel.Workbook) getEmpNm(shainCd) Dim ixSheet As Integer Dim ixSheetCnt As Integer = 0 If (rCnt Mod 5) = 0 Then ixSheetCnt = rCnt / 5 Else ixSheetCnt = rCnt / 5 + 1 End If For ixSheet = 0 To ixSheetCnt - 1 xsheet = CType(xBook.Worksheets(1), Excel.Worksheet) cnt = 5 iRows = 0 '列タイトル xsheet.Cells(cnt, 1) = "日付" xsheet.Cells(cnt, 2) = "NO" xsheet.Cells(cnt, 3) = "連番" xsheet.Cells(cnt, 4) = "店名" xsheet.Cells(cnt, 5) = "出勤" xsheet.Cells(cnt, 6) = "到着" xsheet.Cells(cnt, 7) = "退勤" xsheet.Cells(cnt, 8) = "休憩" xsheet.Cells(cnt, 9) = "戻り" xsheet.Cells(cnt, 10) = "休憩(分)" xsheet.Cells(cnt, 11) = "実働" xsheet.Cells(cnt, 12) = "出勤規程" xsheet.Cells(cnt, 13) = "退勤規程" xsheet.Cells(cnt, 14) = "移動" xsheet.Cells(cnt, 15) = "直行" xsheet.Cells(cnt, 16) = "直帰" xsheet.Cells(cnt, 17) = "有休" xsheet.Cells(cnt, 18) = "元連番" xsheet.Cells(1, 1) = "タイムカード履歴" xsheet.Cells(3, 1) = "社員コード" xsheet.Cells.Range("A3", "B3").Merge() xsheet.Cells.Range("C3", "D3").Merge() xsheet.Range("C3", "C3").NumberFormatLocal = "@" xsheet.Cells(3, 3) = shainCd xsheet.Cells.Range("E3", "F3").Merge() xsheet.Cells(3, 5) = "社員名゙" xsheet.Range("E3", "F3").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Cells.Range("G3", "I3").Merge() xsheet.Cells(3, 7) = sEmpNm xsheet.Range("G3", "I3").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft xsheet.Range("N3").Value = "雇用" xsheet.Range("N3").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Range("O3").Value = sKoyouJouken xsheet.Range("O3").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter cnt += 1 Do While iRows < 5 And iRowCnt < rCnt xsheet.Range("A" & cnt, "R" & cnt).NumberFormatLocal = "@" xsheet.Range("B" & cnt, "C" & cnt).NumberFormatLocal = "@" xsheet.Range("J" & cnt, "J" & cnt).NumberFormatLocal = "@" xsheet.Range("R" & cnt, "R" & cnt).NumberFormatLocal = "@" '明細行の表示 xsheet.Cells(cnt, 1) = dtExcel.Rows(iRowCnt)("hiduke") xsheet.Cells(cnt, 2) = dtExcel.Rows(iRowCnt)("NO") xsheet.Cells(cnt, 3) = dtExcel.Rows(iRowCnt)("renban") xsheet.Cells(cnt, 4) = dtExcel.Rows(iRowCnt)("tenmei") xsheet.Cells(cnt, 5) = dtExcel.Rows(iRowCnt)("goWork") xsheet.Cells(cnt, 6) = dtExcel.Rows(iRowCnt)("toutyaku") xsheet.Cells(cnt, 7) = dtExcel.Rows(iRowCnt)("outOfWork") xsheet.Cells(cnt, 8) = dtExcel.Rows(iRowCnt)("kyuukei") xsheet.Cells(cnt, 9) = dtExcel.Rows(iRowCnt)("modoru") xsheet.Cells(cnt, 10) = dtExcel.Rows(iRowCnt)("kyuukeibunn") xsheet.Cells(cnt, 11) = dtExcel.Rows(iRowCnt)("jitudou") xsheet.Cells(cnt, 12) = dtExcel.Rows(iRowCnt)("syultukinkitei") xsheet.Cells(cnt, 13) = dtExcel.Rows(iRowCnt)("taikinkitei") xsheet.Cells(cnt, 14) = dtExcel.Rows(iRowCnt)("idou") xsheet.Cells(cnt, 15) = dtExcel.Rows(iRowCnt)("tyoltukou") xsheet.Cells(cnt, 16) = dtExcel.Rows(iRowCnt)("tyokuki") xsheet.Cells(cnt, 17) = dtExcel.Rows(iRowCnt)("yuukyuu") xsheet.Cells(cnt, 18) = dtExcel.Rows(iRowCnt)("motorenban") '枠線の表示 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlInsideVertical).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeLeft).ColorIndex = 1 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeTop).ColorIndex = 1 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeBottom).ColorIndex = 1 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeRight).ColorIndex = 1 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlInsideVertical).ColorIndex = 1 xsheet.Range("A" & cnt, "J" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Range("A" & cnt, "J" & cnt).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter xsheet.Range("K" & cnt, "K" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight xsheet.Range("L" & cnt, "N" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Range("L" & cnt, "N" & cnt).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter xsheet.Range("O" & cnt, "P" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft xsheet.Range("Q" & cnt, "R" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Range("Q" & cnt, "R" & cnt).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter xsheet.Range("A" & cnt, "R" & cnt).Interior.ColorIndex = 37 cnt += 1 iRows += 1 iRowCnt += 1 Loop xsheet.Range("A5", "R5").Interior.ColorIndex = 37 xsheet.Range("A1", "R1").Interior.ColorIndex = 38 xsheet.Range("A2", "R2").Interior.ColorIndex = 38 xsheet.Range("A3", "R3").Interior.ColorIndex = 38 xsheet.Range("A4", "R4").Interior.ColorIndex = 38 'フォントサイズの変更 xsheet.Cells.Font.Size = 10 xsheet.Range("A1", "D1").Font.Bold = True xsheet.Range("A1", "D1").Font.Size = 16 xsheet.Range("A1", "D1").Font.Underline = False xsheet.Range("A3", "R3").Font.Bold = True xsheet.Range("A3", "R3").Font.Size = 14 xsheet.Range("A3", "R3").Font.Underline = False '列の幅 xsheet.Range("A:A").ColumnWidth = 6.88 xsheet.Range("B:B").ColumnWidth = 5.13 xsheet.Range("C:C").ColumnWidth = 5.13 xsheet.Range("D:D").ColumnWidth = 11.5 xsheet.Range("E:E").ColumnWidth = 5.25 xsheet.Range("F:F").ColumnWidth = 5.25 xsheet.Range("G:G").ColumnWidth = 5.25 xsheet.Range("H:H").ColumnWidth = 5.25 xsheet.Range("I:I").ColumnWidth = 5.25 xsheet.Range("J:J").ColumnWidth = 7.88 xsheet.Range("K:K").ColumnWidth = 5.13 xsheet.Range("L:L").ColumnWidth = 8.5 xsheet.Range("M:M").ColumnWidth = 8.5 xsheet.Range("N:N").ColumnWidth = 5.38 xsheet.Range("O:O").ColumnWidth = 13.75 xsheet.Range("P:P").ColumnWidth = 13.75 xsheet.Range("Q:Q").ColumnWidth = 5.13 xsheet.Range("R:R").ColumnWidth = 7.13 xsheet.Range("A5", "R5").RowHeight = 13.5 'タイトル欄の設定 xsheet.Range("A5", "R5").Font.Bold = True xsheet.Range("A5", "R5").Font.Size = 11 xsheet.Rows("1:1").RowHeight = 20 xsheet.Range("A5", "R5").WrapText = True xsheet.Range("A5", "R5").Interior.ColorIndex = 37 xsheet.Range("A5", "R5").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Range("A5", "R5").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter '枠線の表示 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlInsideVertical).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeLeft).ColorIndex = 1 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeTop).ColorIndex = 1 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeBottom).ColorIndex = 1 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeRight).ColorIndex = 1 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlInsideVertical).ColorIndex = 1 '印刷フォームの設定 xsheet.PageSetup.PrintGridlines = True xsheet.PageSetup.CenterHorizontally = True xsheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4 xsheet.PageSetup.LeftMargin = 30 xsheet.PageSetup.RightMargin = 30 xsheet.Name = "タイムカード履歴(" + (ixSheet + 1).ToString() + ")" xls.ActiveWorkbook.Worksheets.Add(Count:=ixSheet + 1) Next Dim localFilePath, fileNameExt, filePath As String localFilePath = saveFileDialog.FileName.ToString fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf(".") + 1) filePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\")) If Len(Dir(saveFileDialog.FileName)) <> 0 Then Kill(saveFileDialog.FileName) End If xBook.SaveAs(saveFileDialog.FileName) xsheet = Nothing xBook.Close() xBook = Nothing xls.Quit() xls = Nothing Dim blhas As Boolean = False If ExcelProIdBefore Is Nothing Then For Each pro In Process.GetProcessesByName("EXCEL") pro.Kill() Next Else For int As Integer = 0 To ExcelProIdAfter.Length - 1 blhas = False For p As Integer = 0 To ExcelProIdBefore.Length - 1 If ExcelProIdAfter(int) = ExcelProIdBefore(p) Then blhas = True End If Next If blhas = False Then For Each pro In Process.GetProcessesByName("EXCEL") If pro.Id = ExcelProIdAfter(int) Then pro.Kill() End If Next End If Next End If MsgBox("Excelファイル出力完了。", MsgBoxStyle.Information, "确認") End If Else MsgBox("出力データがない", MsgBoxStyle.Exclamation Or MsgBoxStyle.OkOnly, "確認") End If End Sub Private Sub Btn_over_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_over.Click Me.Close() End Sub Private Sub Tbx_startDayymd_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Tbx_startDayymd.KeyPress Select Case e.KeyChar Case "0" To "9", "/", ""c e.Handled = False Case Else e.Handled = True End Select End Sub End Class
[color=green]'注意sql取时刻是 to_char(t1.tc_time_8,'hh24:mi')而不是'hh24:mm'查看附件 snap2
原先写的代码,参考snap6,snap7,snap9不能实现excel分页,时间是取出来以后在进行处理
画面出来结果参照snap4
源代码打印出excel不能分页,代码参照
If dtExcel.Rows.Count > 0 Then If dtExcel.Rows.Count > 0 Then Dim saveFileDialog As New SaveFileDialog '出力ファイル saveFileDialog.RestoreDirectory = True saveFileDialog.Filter = "Microsoft Excel ブック|*.xls" 'saveFileDialog.InitialDirectory = "c:\" saveFileDialog.InitialDirectory = CurDir() saveFileDialog.FileName = "タイムカード履歴" If saveFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then Dim cnt As Integer '2010/02/08 start add ************************************ Dim ExcelProIdBefore As Integer() = Nothing Dim ExcelProIdAfter As Integer() = Nothing Dim k As Integer = 0 Dim pro As Process For Each pro In Process.GetProcessesByName("EXCEL") ReDim Preserve ExcelProIdBefore(k) ExcelProIdBefore(k) = pro.Id k = k + 1 Next xls = New Excel.Application k = 0 For Each pro In Process.GetProcessesByName("EXCEL") ReDim Preserve ExcelProIdAfter(k) ExcelProIdAfter(k) = pro.Id k = k + 1 Next '2010/02/08 start end ************************************ xBook = CType(xls.Workbooks.Add, Excel.Workbook) xsheet = CType(xBook.Worksheets(1), Excel.Worksheet) cnt = 5 '列タイトル xsheet.Cells(cnt, 1) = "日付" xsheet.Cells(cnt, 2) = "NO" xsheet.Cells(cnt, 3) = "連番" xsheet.Cells(cnt, 4) = "店名" xsheet.Cells(cnt, 5) = "出勤" xsheet.Cells(cnt, 6) = "到着" xsheet.Cells(cnt, 7) = "退勤" xsheet.Cells(cnt, 8) = "休憩" xsheet.Cells(cnt, 9) = "戻り" xsheet.Cells(cnt, 10) = "休憩(分)" xsheet.Cells(cnt, 11) = "実働" xsheet.Cells(cnt, 12) = "出勤規程" xsheet.Cells(cnt, 13) = "退勤規程" xsheet.Cells(cnt, 14) = "移動" xsheet.Cells(cnt, 15) = "直行" xsheet.Cells(cnt, 16) = "直帰" xsheet.Cells(cnt, 17) = "有休" xsheet.Cells(cnt, 18) = "元連番" xsheet.Cells(1, 1) = "タイムカード履歴" xsheet.Cells.Range("A1", "D1").Merge() xsheet.Range("A1", "R1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Cells(3, 1) = "社員コード" xsheet.Cells.Range("A3", "B3").Merge() xsheet.Cells(3, 2) = shainCd xsheet.Cells.Range("C3", "D3").Merge() xsheet.Cells(3, 3) = "社員名゙" xsheet.Cells.Range("E3", "F3").Merge() xsheet.Range("N3").Value = "雇用" xsheet.Range("A3", "R3").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter cnt += 1 Dim i As Integer For i = 0 To dtExcel.Rows.Count - 1 '明細行の表示 '明細行の表示 xsheet.Cells(cnt, 1) = dtExcel.Rows(i)("hiduke") xsheet.Cells(cnt, 2) = dtExcel.Rows(i)("NO") xsheet.Cells(cnt, 3) = dtExcel.Rows(i)("renban") xsheet.Cells(cnt, 4) = dtExcel.Rows(i)("tenmei") xsheet.Cells(cnt, 5) = dtExcel.Rows(i)("goWork") xsheet.Cells(cnt, 6) = dtExcel.Rows(i)("toutyaku") xsheet.Cells(cnt, 7) = dtExcel.Rows(i)("outOfWork") xsheet.Cells(cnt, 8) = dtExcel.Rows(i)("kyuukei") xsheet.Cells(cnt, 9) = dtExcel.Rows(i)("modoru") xsheet.Cells(cnt, 10) = dtExcel.Rows(i)("kyuukeibunn") xsheet.Cells(cnt, 11) = dtExcel.Rows(i)("jitudou") xsheet.Cells(cnt, 12) = dtExcel.Rows(i)("syultukinkitei") xsheet.Cells(cnt, 13) = dtExcel.Rows(i)("taikinkitei") xsheet.Cells(cnt, 14) = dtExcel.Rows(i)("idou") xsheet.Cells(cnt, 15) = dtExcel.Rows(i)("tyoltukou") xsheet.Cells(cnt, 16) = dtExcel.Rows(i)("tyokuki") xsheet.Cells(cnt, 17) = dtExcel.Rows(i)("yuukyuu") xsheet.Cells(cnt, 18) = dtExcel.Rows(i)("motorenban") '枠線の表示 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlInsideVertical).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeLeft).ColorIndex = 1 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeTop).ColorIndex = 1 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeBottom).ColorIndex = 1 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlEdgeRight).ColorIndex = 1 xsheet.Range("A" & cnt, "R" & cnt).Borders(Excel.XlBordersIndex.xlInsideVertical).ColorIndex = 1 xsheet.Range("A" & cnt, "J" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Range("A" & cnt, "J" & cnt).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter xsheet.Range("K" & cnt, "K" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight xsheet.Range("L" & cnt, "N" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Range("L" & cnt, "N" & cnt).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter xsheet.Range("O" & cnt, "P" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft xsheet.Range("Q" & cnt, "R" & cnt).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Range("Q" & cnt, "R" & cnt).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter xsheet.Range("B" & cnt, "C" & cnt).NumberFormatLocal = "@" xsheet.Range("J" & cnt, "J" & cnt).NumberFormatLocal = "@" xsheet.Range("R" & cnt, "R" & cnt).NumberFormatLocal = "@" xsheet.Range("A" & cnt, "R" & cnt).Interior.ColorIndex = 37 cnt = cnt + 1 Next xsheet.Range("A5", "R5").Interior.ColorIndex = 37 xsheet.Range("A1", "R1").Interior.ColorIndex = 38 xsheet.Range("A2", "R2").Interior.ColorIndex = 38 xsheet.Range("A3", "R3").Interior.ColorIndex = 38 xsheet.Range("A4", "R4").Interior.ColorIndex = 38 'フォントサイズの変更 xsheet.Cells.Font.Size = 10 xsheet.Range("A1", "D1").Font.Bold = True xsheet.Range("A1", "D1").Font.Size = 16 xsheet.Range("A1", "D1").Font.Underline = False xsheet.Range("A3", "R3").Font.Bold = True xsheet.Range("A3", "R3").Font.Size = 12 xsheet.Range("A3", "R3").Font.Underline = False xsheet.Range("A4", "R4").Font.Bold = True xsheet.Range("A4", "R4").Font.Size = 11 '列の幅 xsheet.Range("A:A").ColumnWidth = 8 xsheet.Range("B:B").ColumnWidth = 4 xsheet.Range("C:C").ColumnWidth = 4 xsheet.Range("D:D").ColumnWidth = 12 xsheet.Range("E:E").ColumnWidth = 8 xsheet.Range("F:F").ColumnWidth = 8 xsheet.Range("G:G").ColumnWidth = 8 xsheet.Range("H:H").ColumnWidth = 8 xsheet.Range("I:I").ColumnWidth = 8 xsheet.Range("J:J").ColumnWidth = 10 xsheet.Range("K:K").ColumnWidth = 6 xsheet.Range("L:L").ColumnWidth = 8 xsheet.Range("M:M").ColumnWidth = 8 xsheet.Range("N:N").ColumnWidth = 8 xsheet.Range("O:O").ColumnWidth = 16 xsheet.Range("P:P").ColumnWidth = 16 xsheet.Range("Q:Q").ColumnWidth = 4 xsheet.Range("R:R").ColumnWidth = 10 'タイトル欄の設定 xsheet.Range("A5", "R5").Font.Bold = True xsheet.Rows("1:1").RowHeight = 20 xsheet.Range("A5", "R5").WrapText = True xsheet.Range("A5", "R5").Interior.ColorIndex = 37 xsheet.Range("A5", "R5").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter xsheet.Range("A5", "R5").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter '枠線の表示 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlInsideVertical).Weight = Excel.XlBorderWeight.xlThin xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeLeft).ColorIndex = 1 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeTop).ColorIndex = 1 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeBottom).ColorIndex = 1 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlEdgeRight).ColorIndex = 1 xsheet.Range("A5", "R5").Borders(Excel.XlBordersIndex.xlInsideVertical).ColorIndex = 1 '印刷フォームの設定 xsheet.PageSetup.PrintGridlines = True xsheet.PageSetup.CenterHorizontally = True xsheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4 xsheet.PageSetup.LeftMargin = 30 xsheet.PageSetup.RightMargin = 30 Dim localFilePath, fileNameExt, filePath As String localFilePath = saveFileDialog.FileName.ToString fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf(".") + 1) filePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\")) xsheet.Name = "タイムカード履歴" '2010/02/08 start add ************************************ If Len(Dir(saveFileDialog.FileName)) <> 0 Then Kill(saveFileDialog.FileName) End If '2010/02/08 start end ************************************ xsheet.SaveAs(saveFileDialog.FileName) xsheet = Nothing xBook.Close() xBook = Nothing xls.Quit() xls = Nothing '2010/02/08 start add ************************************ Dim blhas As Boolean = False If ExcelProIdBefore Is Nothing Then For Each pro In Process.GetProcessesByName("EXCEL") pro.Kill() Next Else For int As Integer = 0 To ExcelProIdAfter.Length - 1 blhas = False For p As Integer = 0 To ExcelProIdBefore.Length - 1 If ExcelProIdAfter(int) = ExcelProIdBefore(p) Then blhas = True End If Next If blhas = False Then For Each pro In Process.GetProcessesByName("EXCEL") If pro.Id = ExcelProIdAfter(int) Then pro.Kill() End If Next End If Next End If '2010/02/08 start end ************************************ MsgBox("Excelファイル出力完了。", MsgBoxStyle.Information, "确認") End If Else MsgBox("出力データがない", MsgBoxStyle.Exclamation Or MsgBoxStyle.OkOnly, "確認") End If End If
相关推荐
vb.net将如何将excel中的数据导入到数据库
vb.net开发Exce格式总结 l vb.net开发Excel vb.net开发Excel格式总结
vb.net 操作excel实例,包括简单的treeview,datagrid实例
利用vb.net将Excel表导入到sql数据库中
vb.net 读取excel
一个VB.NET连接和操作Sql Server的类 一个VB.NET连接和操作Sql Server的类
有关于vb.net对于Excel的使用与存储,文件在 生成文件目录路径下的存储,Excel的多个sheet生成与存储 ,Excel的显示。
vb.net做的图片存储到SQL数据库 vb.net做的图片存储到SQL数据库 vb.net做的图片存储到SQL数据库
vb.net操作excel文件,要求将数据显示在Excel表中,并且要设好数据格式。
这是我最近做的一个小程序,比较简单就是用VB.NET创建一个Excel表
利用VB.NET实现的Excel文件导入Sqlsever 2000中
使用vb.net语言编写的excel文件数据读取和保存,可以实现读取数据的显示,希望大家可以多学习交流下。
1.陈恩点VB.NET 视频 VB2010基础入门 2.陈恩点VB.NET 视频 VB2010基本输入输出 3.陈恩点VB.NET 视频 VB...19.陈恩点VB.NET 视频ACCESS与EXCEL互导 20.陈恩点VB.NET 视频SQL简单学籍管理 21.陈恩点VB.NET 视频多线程操作
vb.net 与excel 的结合,可以直接建立excel
vb.net链接SQL的类,实现查询,增改删,有无查询等返回datatable
Vb.net 用户登录界面与后台sql数据库链接代码Vb.net 用户登录界面与后台sql数据库链接代码
详细的介绍了防止sql注入攻击,内附asp.net 源码和vb.net源码。是初学者不错的选择。希望能够帮助到你。
VSTO开发Excel工具箱源码(VB.NET版),VB.NET连接SQL serve数据库,远程数据库,异步委托、VB.net画甘特图等等源码,有注释等等,超值