本人不才，不会用ODBC4EXCEL这种高级技术，以下纯粹手工代码，写的比较乱，好在有些注释，将就看吧。建立一个VB.NET控制台应用程序，然后C&P以下代码到Module1.vb中Imports Microsoft.Office.Interop.Excel '在项目属性的引用中添加此引用，注意选择较高的版本。如果你一个都没有请安装Office正式版Imports System.TextImports System.IOModule Module1
Sub Main()
Dim app = New Application
Dim wb = app.Workbooks.Open("d:\1.xls") '要处理的文件在这里，请自行改名
Dim sh As Worksheet = wb.Sheets(1) '必须放在第一张工作表里
Dim table = New List(Of List(Of String)) 'excel的每个单元格的文本放在这张表里
For Each row As Range In sh.Rows
Dim firstCell As Range = row.Cells(1)
If firstCell.Text = String.Empty Then '从第一行一直处理到空行为止，第一行没有标题，直接放记录
Exit For
End If
Dim lsRow = New List(Of String)
For index As Integer = 1 To 4
Dim cell As Range = row.Cells(index)
lsRow.Add(cell.Text)
Next
table.Add(lsRow)
Next
Dim sb = New StringBuilder '此sb非彼sb，此sb乃StringBuilder
For Each row In table
Dim workNo = String.Format("{0:D4}", Integer.Parse(row(0)))
Dim eventDate = Date.Parse(row(1)).ToString("yyyy/MM/dd")
Dim eventTimeStart = Date.Parse(row(2).Substring(0, 8)).ToString("hh:mm:ss")
Dim eventTimeEnd = Date.Parse(row(3).Substring(0, 8)).ToString("hh:mm:ss")
If Not _isPass(row(2)) Then '一切听LZ的。。。
sb.AppendLine(workNo + ",0," + eventDate + "," + eventTimeStart)
End If
If Not _isPass(row(3)) Then '为了一切LZ。。。
sb.AppendLine(workNo + ",1," + eventDate + "," + eventTimeEnd)
End If
Next
File.WriteAllText("d:\1.txt", sb.ToString) '绕了半天终于出来了，放在这里，自己改名
wb.Close()
app.Quit() '如果你有300G的内存请无视他，不然还是加上吧
End Sub
Private Function _isPass(ByVal context As String) As Boolean
Return context.Contains("旷工") OrElse context.Contains("休息") OrElse context.Contains("未刷卡")
End FunctionEnd Module
|
