摘要:frmLabExcel.vb
use vb.net to load excel file.
Then fill data,
Then save as new Excel file.
Dot Mode
frmLabExcel.vbImports ...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