摘要:frmLabExcel.vb
use vb.net to load excel file.
Then fill data,
Then save as new Excel file.
Dot Mode

frmLabExcel.vb
Imports ...System.IO
Imports HISLIB
Imports System.Collections.Generic
Public Class frmLabExcel

Private Sub frmLabExcel_Load() Sub frmLabExcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
Me.lblDemo.Text = "The file format should be" & vbNewLine & _
"Column A - Lab No. " & vbNewLine & _
"Column B - Admission Date. " & vbNewLine & _
"Column C - Discharge Date." & vbNewLine & _
"Column D - Visit No. " & vbNewLine & _
"Row 1 - Column Heading"
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Sub

"Small Function"#Region "Small Function"

Private Function sFileOutput() Function sFileOutput(ByVal psFilePath As String) As String
' add "_out" into file path.
' eg input : C:\aaa.doc
' output : C:\aaa_out.doc
sFileOutput = BLANK
Try
Dim liSplitPoint As Integer = psFilePath.IndexOf(".")
sFileOutput = psFilePath.Substring(0, liSplitPoint) & "_Out" & psFilePath.Substring(liSplitPoint)
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Function

Private Function sFormatData() Function sFormatData(ByVal psData As String) As String
sFormatData = BLANK
Try
If psData <> BLANK Then
If psData.Length = 7 Then
sFormatData = "0" & psData
End If
End If
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Function
#End Region

"Select File"#Region "Select File"

Private Sub cmdSelectFile_Click() Sub cmdSelectFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelectFile.Click
Try
Me.lblInputFile.Text = BLANK
Me.lblOutputFile.Text = BLANK
If (OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK) Then
Me.lblInputFile.Text = OpenFileDialog1.FileName
If MsgBoxYesNo("Do you want to Fill Data into this Excel file?") = MsgBoxResult.Yes Then
Call BuildFile()
Else
Me.lblInputFile.Text = BLANK
End If
End If
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Sub

Private Sub BuildFile() Sub BuildFile()
Try
Dim lsIDList As ArrayList
Dim mDictionary As Dictionary(Of String, String)
Dim liListCol As Integer = 1
Dim liStartRow As Integer = 2
Dim lsFilename As String = Me.lblInputFile.Text
If lsFilename = BLANK Then Exit Sub
lsIDList = sGetIDListFromExcel(lsFilename, liListCol, liStartRow)
mDictionary = sGetData(lsIDList)
Call BuildExcelFile(lsFilename, mDictionary, liListCol, liStartRow)
' Kill Excel.exe
GC.Collect()
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Sub
#End Region

"Excel"#Region "Excel" 
Private Function sGetIDListFromExcel() Function sGetIDListFromExcel(ByVal psFilePath As String, ByVal piListCol As Integer, ByVal piStartRow As Integer) As ArrayList
' get list from an Excel file
sGetIDListFromExcel = New ArrayList
Try
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim ExcelWorkSheet As Excel.Worksheet
ExcelApp = New Excel.Application
ExcelWorkbook = ExcelApp.Workbooks.Open(psFilePath)
ExcelWorkSheet = ExcelWorkbook.Sheets.Item(1)
ExcelWorkSheet.Activate()
Dim lsData As String
While Not (ExcelWorkSheet.Cells(piStartRow, piListCol).value Is Nothing)
lsData = sFormatData(ExcelWorkSheet.Cells(piStartRow, piListCol).value.ToString)
sGetIDListFromExcel.Add(lsData)
piStartRow = piStartRow + 1
End While
ExcelWorkbook.Close()
ExcelApp.Application.Quit()
ExcelWorkSheet = Nothing
ExcelWorkbook = Nothing
ExcelApp = Nothing
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Function

Private Function sGetData() Function sGetData(ByVal psExcelList As ArrayList) As Dictionary(Of String, String)
' get HKID's Diagnosis List from Database
sGetData = New Dictionary(Of String, String)
Try
Dim lsAns As String = BLANK
For Each lsItem As String In psExcelList
Dim lsSQLString As String = BLANK
Dim ClsSQLLoadHelper As New ClassSQLLoadHelper
With ClsSQLLoadHelper
.TableName = "Lab_Report_pdf a"
.SQLExtraSelect += ", Patient_Visit b"
.SQLExtraCriteria += " a.Visit_No = b.Visit_No"
.SQLExtraCriteria += " and a.Lab_No = " & VarSQLString(lsItem)
.AddRequiredField("distinct a.Lab_No")
.AddRequiredField("b.PV_Visit_DT")
.AddRequiredField("b.PV_Discharge_DT")
.AddRequiredField("b.Visit_No")
lsSQLString = .sGenerateSqlSelect
End With
Dim ClsGridHelper As New ClassGridHelper
With ClsGridHelper
.SetConnection(lsSQLString)
Try
While .bReadStart
.fAddGridRow()
lsAns += FormatDate(.oLoadSQLField("PV_Visit_DT")) & _
"," & FormatDate(.oLoadSQLField("PV_Discharge_DT")) & _
"," & .oLoadSQLField("Visit_No")
End While
sGetData.Add(lsItem, lsAns)
lsAns = BLANK
Finally
.bReadEnd()
End Try
End With
Next
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Function

Private Sub BuildExcelFile() Sub BuildExcelFile(ByVal psFilePath As String, ByVal pDictionary As Dictionary(Of String, String), ByVal piListCol As Integer, ByVal piStartRow As Integer)
' Add patient's diagnosis into the specific column and row in excel file
Try
Dim ExcelApp As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim ExcelWorkSheet As Excel.Worksheet
ExcelApp = New Excel.Application
ExcelWorkbook = ExcelApp.Workbooks.Open(psFilePath)
ExcelWorkSheet = ExcelWorkbook.Sheets.Item(1)
ExcelWorkSheet.Activate()
Dim lsString As String()
While Not (ExcelWorkSheet.Cells(piStartRow, piListCol).value Is Nothing)
Dim lsTempString As String = BLANK
lsTempString = pDictionary.Item(sFormatData(ExcelWorkSheet.Cells(piStartRow, 1).value))
If lsTempString <> BLANK Then
lsString = lsTempString.Split(",")
ExcelWorkSheet.Cells(piStartRow, 2).value = lsString(0).ToString
ExcelWorkSheet.Cells(piStartRow, 3).value = lsString(1).ToString
ExcelWorkSheet.Cells(piStartRow, 4).value = lsString(2).ToString
End If
piStartRow = piStartRow + 1
End While
ExcelWorkSheet.Rows.AutoFit()
ExcelWorkSheet.Columns.AutoFit()
' Save file
Dim lsOutputFile As String = sFileOutput(psFilePath)
Try
If File.Exists(lsOutputFile) Then
File.Delete(lsOutputFile)
End If
ExcelWorkbook.SaveAs(lsOutputFile)
Me.lblOutputFile.Text = lsOutputFile
Catch ex As Exception
Call ErrHandler(ex.Message, APP_NAME)
Me.lblInputFile.Text = BLANK
End Try
ExcelWorkbook.Close(False)
ExcelApp.Application.Quit()
ExcelWorkSheet = Nothing
ExcelWorkbook = Nothing
ExcelApp = Nothing
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Sub
#End Region

"Open File / Directory Path"#Region "Open File / Directory Path"

Private Sub cmdOpenFolder_Click() Sub cmdOpenFolder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOpenFolder.Click
Try
Dim lsPath As String = BLANK
lsPath = Me.lblOutputFile.Text
Call fOpenPath(lsPath, True)
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Sub

Private Sub cmdOpenFile_Click() Sub cmdOpenFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOpenFile.Click
Try
Dim lsPath As String = BLANK
lsPath = Me.lblOutputFile.Text
Call fOpenPath(lsPath, False)
Catch Err As Exception
Call ErrHandler(Err.Message, APP_NAME)
End Try
End Sub
' can put into HISLIB when it use in other system later. 
Private Sub fOpenPath() Sub fOpenPath(ByVal psFilePath As String, ByVal pbOpenFileDirectory As Boolean)
If psFilePath = BLANK Then
MsgBoxOkOnly("There is no output Excel file.")
Exit Sub
End If
If pbOpenFileDirectory = True Then
psFilePath = Path.GetDirectoryName(psFilePath)
End If
Process.Start(psFilePath)
End Sub
#End Region
End Class

frmLabExcel.Designer.vb
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class frmLabExcel
Inherits System.Windows.Forms.Form
'Form overrides dispose to clean up the component list.
<System.Diagnostics.DebuggerNonUserCode()> _ 
Protected Overrides Sub Dispose() Overrides Sub Dispose(ByVal disposing As Boolean)
Try
If disposing AndAlso components IsNot Nothing Then
components.Dispose()
End If
Finally
MyBase.Dispose(disposing)
End Try
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> _ 
Private Sub InitializeComponent() Sub InitializeComponent()
Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(frmLabExcel))
Me.OpenFileDialog1 = New System.Windows.Forms.OpenFileDialog
Me.cmdSelectFile = New System.Windows.Forms.Button
Me.lblInputFile = New System.Windows.Forms.Label
Me.lblOutputFile = New System.Windows.Forms.Label
Me.Label3 = New System.Windows.Forms.Label
Me.cmdOpenFolder = New System.Windows.Forms.Button
Me.cmdOpenFile = New System.Windows.Forms.Button
Me.picDetail = New System.Windows.Forms.PictureBox
Me.lblDemo = New System.Windows.Forms.Label
Me.GroupBox3 = New System.Windows.Forms.GroupBox
Me.GroupBox2 = New System.Windows.Forms.GroupBox
CType(Me.picDetail, System.ComponentModel.ISupportInitialize).BeginInit()
Me.GroupBox3.SuspendLayout()
Me.GroupBox2.SuspendLayout()
Me.SuspendLayout()
'
'OpenFileDialog1
'
Me.OpenFileDialog1.Filter = "Excel Files|*.xls"
'
'cmdSelectFile
'
Me.cmdSelectFile.BackColor = System.Drawing.Color.Teal
Me.cmdSelectFile.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!)
Me.cmdSelectFile.ForeColor = System.Drawing.Color.White
Me.cmdSelectFile.Location = New System.Drawing.Point(8, 24)
Me.cmdSelectFile.Name = "cmdSelectFile"
Me.cmdSelectFile.Size = New System.Drawing.Size(120, 40)
Me.cmdSelectFile.TabIndex = 84
Me.cmdSelectFile.Text = "Select File"
Me.cmdSelectFile.UseVisualStyleBackColor = False
'
'lblInputFile
'
Me.lblInputFile.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
Me.lblInputFile.Location = New System.Drawing.Point(136, 32)
Me.lblInputFile.Name = "lblInputFile"
Me.lblInputFile.Size = New System.Drawing.Size(600, 24)
Me.lblInputFile.TabIndex = 85
Me.lblInputFile.TextAlign = System.Drawing.ContentAlignment.MiddleLeft
'
'lblOutputFile
'
Me.lblOutputFile.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
Me.lblOutputFile.Location = New System.Drawing.Point(136, 24)
Me.lblOutputFile.Name = "lblOutputFile"
Me.lblOutputFile.Size = New System.Drawing.Size(600, 24)
Me.lblOutputFile.TabIndex = 88
Me.lblOutputFile.TextAlign = System.Drawing.ContentAlignment.MiddleLeft
'
'Label3
'
Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!, System.Drawing.FontStyle.Bold)
Me.Label3.Location = New System.Drawing.Point(8, 24)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(120, 24)
Me.Label3.TabIndex = 89
Me.Label3.Text = "Excel File :"
Me.Label3.TextAlign = System.Drawing.ContentAlignment.MiddleLeft
'
'cmdOpenFolder
'
Me.cmdOpenFolder.BackColor = System.Drawing.Color.Teal
Me.cmdOpenFolder.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!)
Me.cmdOpenFolder.ForeColor = System.Drawing.Color.White
Me.cmdOpenFolder.Location = New System.Drawing.Point(496, 56)
Me.cmdOpenFolder.Name = "cmdOpenFolder"
Me.cmdOpenFolder.Size = New System.Drawing.Size(120, 40)
Me.cmdOpenFolder.TabIndex = 91
Me.cmdOpenFolder.Text = "Open Folder"
Me.cmdOpenFolder.UseVisualStyleBackColor = False
'
'cmdOpenFile
'
Me.cmdOpenFile.BackColor = System.Drawing.Color.Teal
Me.cmdOpenFile.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!)
Me.cmdOpenFile.ForeColor = System.Drawing.Color.White
Me.cmdOpenFile.Location = New System.Drawing.Point(616, 56)
Me.cmdOpenFile.Name = "cmdOpenFile"
Me.cmdOpenFile.Size = New System.Drawing.Size(120, 40)
Me.cmdOpenFile.TabIndex = 92
Me.cmdOpenFile.Text = "Open File"
Me.cmdOpenFile.UseVisualStyleBackColor = False
'
'picDetail
'
Me.picDetail.BackColor = System.Drawing.Color.DarkSlateGray
Me.picDetail.Font = New System.Drawing.Font("Microsoft Sans Serif", 7.8!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.picDetail.Image = CType(resources.GetObject("picDetail.Image"), System.Drawing.Image)
Me.picDetail.Location = New System.Drawing.Point(368, 72)
Me.picDetail.Name = "picDetail"
Me.picDetail.Size = New System.Drawing.Size(370, 323)
Me.picDetail.SizeMode = System.Windows.Forms.PictureBoxSizeMode.AutoSize
Me.picDetail.TabIndex = 93
Me.picDetail.TabStop = False
Me.picDetail.Tag = ""
'
'lblDemo
'
Me.lblDemo.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!)
Me.lblDemo.Location = New System.Drawing.Point(8, 80)
Me.lblDemo.Name = "lblDemo"
Me.lblDemo.Size = New System.Drawing.Size(360, 312)
Me.lblDemo.TabIndex = 94
Me.lblDemo.Text = "The File Structure should follow the Example."
'
'GroupBox3
'
Me.GroupBox3.Controls.Add(Me.cmdOpenFolder)
Me.GroupBox3.Controls.Add(Me.cmdOpenFile)
Me.GroupBox3.Controls.Add(Me.lblOutputFile)
Me.GroupBox3.Controls.Add(Me.Label3)
Me.GroupBox3.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!, System.Drawing.FontStyle.Bold)
Me.GroupBox3.Location = New System.Drawing.Point(8, 424)
Me.GroupBox3.Name = "GroupBox3"
Me.GroupBox3.Size = New System.Drawing.Size(744, 104)
Me.GroupBox3.TabIndex = 101
Me.GroupBox3.TabStop = False
Me.GroupBox3.Text = "Output"
'
'GroupBox2
'
Me.GroupBox2.Controls.Add(Me.picDetail)
Me.GroupBox2.Controls.Add(Me.lblDemo)
Me.GroupBox2.Controls.Add(Me.lblInputFile)
Me.GroupBox2.Controls.Add(Me.cmdSelectFile)
Me.GroupBox2.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!, System.Drawing.FontStyle.Bold)
Me.GroupBox2.Location = New System.Drawing.Point(8, 8)
Me.GroupBox2.Name = "GroupBox2"
Me.GroupBox2.Size = New System.Drawing.Size(744, 408)
Me.GroupBox2.TabIndex = 100
Me.GroupBox2.TabStop = False
Me.GroupBox2.Text = "Input"
'
'frmLabExcel
'
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.None
Me.BackColor = System.Drawing.Color.LightSteelBlue
Me.ClientSize = New System.Drawing.Size(755, 533)
Me.Controls.Add(Me.GroupBox2)
Me.Controls.Add(Me.GroupBox3)
Me.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!)
Me.Name = "frmLabExcel"
Me.Text = "Lab Excel"
Me.WindowState = System.Windows.Forms.FormWindowState.Maximized
CType(Me.picDetail, System.ComponentModel.ISupportInitialize).EndInit()
Me.GroupBox3.ResumeLayout(False)
Me.GroupBox2.ResumeLayout(False)
Me.GroupBox2.PerformLayout()
Me.ResumeLayout(False)
End Sub
Friend WithEvents OpenFileDialog1 As System.Windows.Forms.OpenFileDialog
Friend WithEvents cmdSelectFile As System.Windows.Forms.Button
Friend WithEvents lblInputFile As System.Windows.Forms.Label
Friend WithEvents lblOutputFile As System.Windows.Forms.Label
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents cmdOpenFolder As System.Windows.Forms.Button
Friend WithEvents cmdOpenFile As System.Windows.Forms.Button
Friend WithEvents picDetail As System.Windows.Forms.PictureBox
Friend WithEvents lblDemo As System.Windows.Forms.Label
Friend WithEvents GroupBox2 As System.Windows.Forms.GroupBox
Friend WithEvents GroupBox3 As System.Windows.Forms.GroupBox
End Class
參考
www.dotblogs.com.tw/yc421206/archive/2009/07/16/9553.aspx
------------------
熱愛生命 喜愛新奇 有趣的事物
過去 是無法改變
將來 卻能夠創造
希望使大家生活更便利
世界更美好
a guy who loves IT and life