读取excel文件.doc

Imports System.IOImports System.Windows.FormsImports Microsoft.Office.InteropPublic Class Form1Public Structure TermDim kouben As String Dim nm As String Dim pri As String Dim kei As String Dim isStatic As String staticDim ti As String Dim setumei As String hEnd StructurePrivate lst As ArrayListPrivate lstMethod As ArrayListPrivate lstClass As New ArrayListPrivate strPath As String = String.EmptyPrivate strDir As String = String.EmptyPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Clicklst = New ArrayListFolderBrowserDialog1.Description = Select a FolderFolderBrowserDialog1.RootFolder = Environment.SpecialFolder.MyComputerFolderBrowserDialog1.ShowNewFolderButton = FalseIf FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK ThenMe.Label1.Text = FolderBrowserDialog1.SelectedPathGetAllFile(FolderBrowserDialog1.SelectedPath, lst, *.xls)End IfEnd SubPrivate Sub GetAllFile(ByVal path As String, ByRef lst As ArrayList, ByVal str As String)Dim strDir As String() = System.IO.Directory.GetDirectories(path)Dim strFile As String() = System.IO.Directory.GetFiles(path, str)Dim i As IntegerIf strFile.Length 0 ThenFor i = 0 To strFile.Length - 1lst.Add(strFile(i)NextEnd IfIf strDir.Length 0 ThenFor i = 0 To strDir.Length - 1GetAllFile(strDir(i), lst, str)NextEnd IfEnd SubPrivate Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.ClickFolderBrowserDialog1.Description = Select a FolderFolderBrowserDialog1.RootFolder = Environment.SpecialFolder.MyComputerFolderBrowserDialog1.ShowNewFolderButton = FalseIf FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK ThenMe.Label2.Text = FolderBrowserDialog1.SelectedPathEnd IfEnd SubPrivate Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.ClickDim xlAppExcelFile As Excel.ApplicationDim xlBook As Excel.WorkbookDim xlSheet As Excel.WorksheetlstMethod = New ArrayListFor i As Integer = 0 To lst.Count - 1instancexlAppExcelFile = New Excel.ApplicationxlBook = xlAppExcelFile.Workbooks.Open(lst(i)ExcelxlAppExcelFile.Visible = FalsexlAppExcelFile.DisplayAlerts = FalsexlAppExcelFile.ShowWindowsInTaskbar = FalseDim iniSheets As Integer = xlBook.Sheets.CountFor index As Integer = 1 To iniSheetsDim lstMeisai As New ArrayListxlSheet = xlBook.Sheets(index)If s.Equals(xlSheet.Name.Trim) _Or .Equals(xlSheet.Name.Trim) ThenContinue ForEnd IfIf .Equals(xlSheet.Cells(5, B).Value() ThenIf Not lstMethod.Contains(lst(i) ThenlstMethod.Add(lst(i)End IfContinue ForEnd IflstMeisai.Add(/*)lstMeisai.Add( * 2yATM)lstMeisai.Add( *)lstMeisai.Add( * SUBSYSTEM NAME : S)lstMeisai.Add( * SERVICE NAME : dC)lstMeisai.Add( * FUNCTION : + xlSheet.Cells(8, C).Value()lstMeisai.Add( * FILE NAME : + xlSheet.Cells(6, C).Value()lstMeisai.Add( *)lstMeisai.Add( * s)lstMeisai.Add( * 2010/12/21 + xlSheet.Cells(4, H).Value() + SWT )lstMeisai.Add(*/)lstMeisai.Add(package + xlSheet.Cells(7, C).Value() & ;)lstMeisai.Add()lstMeisai.Add(/*)lstMeisai.Add( * + xlSheet.Cells(8, C).Value() + .)Dim blnSousaku As Boolean = FalseFor rowIndex As Integer = 1 To xlSheet.UsedRange.Rows.CountIf .Equals(xlSheet.Cells(rowIndex, B).Value() ThenIf blnSousaku = False ThenblnSousaku = TrueElseIf Not (xlSheet.Cells(rowIndex + 2, B).Value() Is Nothing _OrElse .Equals(xlSheet.Cells(rowIndex + 2, B).Value() _Or .Equals(xlSheet.Cells(rowIndex + 2, B).Value() ThenlstMeisai.Add( * C)lstMeisai.Add( * )End IfFor j As Integer = rowIndex + 2 To xlSheet.UsedRange.Rows.CountIf Not (xlSheet.Cells(j, B).Value() Is Nothing _OrElse .Equals(xlSheet.Cells(j, B).Value() _Or .Equals(xlSheet.Cells(j, B).Value() ThenlstMeisai.Add( * + xlSheet.Cells(j, H).Value()ElseExit ForEnd IfNextIf Not (xlSheet.Cells(rowIndex + 2, B).Value() Is Nothing _OrElse .Equals(xlSheet.Cells(rowIndex + 2, B).Value() _Or .Equals(xlSheet.Cells(rowIndex + 2, B).Value() ThenlstMeisai.Add( * )lstMeisai.Add( * )lstMeisai.Add( * )End IfExit ForEnd IfEnd IfNextlstMeisai.Add( * version $Revision$(2010/08/09)lstMeisai.Add(*/)Dim str1 As String = String.EmptyIf xlSheet.Cells(11, C).Value() Is Nothing _OrElse (.Equals(xlSheet.Cells(11, C).Value().ToString.Trim) Or _-.Equals(xlSheet.Cells(11, C).Value().ToString.Trim) Or _.Equals(xlSheet.Cells(11, C).Value().ToString.Trim) Or _.Equals(xlSheet.Cells(11, C).Value().ToString.Trim) Thenstr1 += Elsestr1 += extends + xlSheet.Cells(11, C).Value().ToString.TrimEnd IfIf xlSheet.Cells(12, C).Value() Is Nothing _OrElse (.Equals(xlSheet.Cells(12, C).Value().ToString.Trim) Or _-.Equals(xlSheet.Cells(12, C).Value().ToString.Trim) Or _.Equals(xlSheet.Cells(12, C).Value().ToString.Trim) Or _.Equals(xlSheet.Cells(12, C).Value().ToString.Trim) Thenstr1 += Elsestr1 += implements + xlSheet.Cells(12, C).Value().ToString.TrimEnd IflstMeisai.Add(xlSheet.Cells(9, C).Value() + class + xlSheet.Cells(5, C).Value() + str1 + )Dim termList As New List(Of Term)For rowIndex As Integer = 1 To xlSheet.UsedRange.Rows.CountIf .Equals(xlSheet.Cells(rowIndex, B).Value() ThenFor j As Integer = rowIndex + 2 To xlSheet.UsedRange.Rows.CountIf Not (xlSheet.Cells(j, B).Value() Is Nothing _OrElse .Equals(xlSheet.Cells(j, B).Value() ThenDim term1 As Termterm1.kouben = xlSheet.Cells(j, B).Value()term1.nm = xlSheet.Cells(j, C).Value()If LenC(term1.nm) term1.nm.Length ThenMessageBox.Show(lst(i) + + xlSheet.Name + + j.ToString + - + term1.nm)Exit SubEnd Ifterm1.pri = xlSheet.Cells(j, D).Value()term1.kei = xlSheet.Cells(j, E).Value()If .Equals(xlSheet.Cells(j, F).Value() Thenterm1.isStatic = static Elseterm1.isStatic = End Ifterm1.ti = xlSheet.Cells(j, G).Value()term1.setumei = xlSheet.Cells(j, H).Value()termList.Add(term1)ElseExit ForEnd IfNextEnd IfNextFor k As Integer = 0 To termList.Count - 1lstMeisai.Add()lstMeisai.Add( /*)lstMeisai.Add( * + termList(k).setumei + .)lstMeisai.Add( */)lstMeisai.Add( + termList(k).pri + + termList(k).isStatic + termList(k).kei + + termList(k).nm + = + termList(k).ti + ;)NextFor k As Integer = 0 To termList.Count - 1lstMeisai.Add()lstMeisai.Add( /*)lstMeisai.Add( * + termList(k).setumei + .)lstMeisai.Add( * return + termList(k).nm + + termList(k).setumei)lstMeisai.Add( */)If boolean.Equals(termList(k).kei.ToLower) ThenlstMeisai.Add( public + termList(k).kei + + is + termList(k).nm.Substring(0, 1).ToUpper + termList(k).nm.Substring(1) + () )ElselstMeisai.Add( public + termList(k).kei + + get + termList(k).nm.Substring(0, 1).ToUpper + termList(k).nm.Substring(1) + () )End IflstMeisai.Add( return + termList(k).nm + ;)lstMeisai.Add( )lstMeisai.Add()lstMeisai.Add( /*)lstMeisai.Add( * + termList(k).setumei + O.)lstMeisai.Add( * param + termList(k).nm + + termList(k).kei + + termList(k).setumei)lstMeisai.Add( */)lstMeisai.Add( public void + set + termList(k).nm.Substring(0, 1).ToUpper + termList(k).nm.Substring(1) + ( + termList(k).kei + + termList(k).nm + ) )lstMeisai.Add( this. + termList(k).nm + = + termList(k).nm + ;)lstMeisai.Add( )NextstrPath = xlSheet.Cells(7, C).Value().ToString.Replace(., /) + / + xlSheet.Cells(6, C).Value()strDir = xlSheet.Cells(7, C).Value().ToString.Replace(., /)CreateJavaFile(lstMeisai)NextNextMessageBox.Show(OK)xlSheet = NothingxlBook.Close()xlBook = NothingxlAppExcelFile.Quit()xlAppExcelFile = NothingEnd SubPrivate Sub CreateJavaFile(ByVal lst As ArrayList)Dim path As String = Me.Label2.Text + + strPathDim dir As String = Me.Label2.Text + + strDirIf System.IO.Directory.Exists(dir) = False ThenSystem.IO.Directory.CreateDirectory(dir)End IfIf Not File.Exists(path) ThenFile.Create(path).Close()End IfIf Not lstClass.Contains(path) ThenlstClass.Add(path)End IfDim fs As New FileStream(path, FileMode.Create)Dim fw As New StreamWriter(fs)For i As Integer = 0 To lst.Count - 1fw.WriteLine(lst(i)Nextfw.Close()fs.Close()End SubPrivate Function LenC(ByVal str As String) As IntegerDim n As IntegerDim StrLen As IntegerFor n = 1 To Len(str)If AscW(Mid(str, n, 1) 256 ThenStrLen = StrLen + 2ElseStrLen = StrLen + 1End IfNextReturn StrLenEnd FunctionPrivate Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.ClickDim xlAppExcelFile As Excel.ApplicationDim xlBook As Excel.WorkbookDim xlSheet As Excel.WorksheetFor i As Integer = 0 To lstMethod.Count - 1instancexlAppExcelFile = New Excel.ApplicationxlBook = xlAppExcelFile.Workbooks.Open(lstMethod(i)ExcelxlAppExcelFile.Visible = FalsexlAppExcelFile.DisplayAlerts = FalsexlAppExcelFile.ShowWindowsInTaskbar = FalseDim iniSheets As Integer = xlBook.Sheets.CountFor index As Integer = 1 To iniSheetsxlSheet = xlBook.Sheets(index)If LenC(xlSheet.Cells(5, C).Value() xlSheet.Cells(5, C).Value().Length ThenMessageBox.Show(lstMethod(i) + + xlSheet.Name + + xlSheet.Cells(5, C).Value()Exit SubEnd IfstrPath = Me.Label2.Text + + xlSheet.Cells(8, C).Value().ToString.Replace(., /) + / + xlSheet.Cells(7, C).Value()strDir = Me.Label2.Text + + xlSheet.Cells(8, C).Value().ToString.Replace(., /)If Not lstClass.Contains(strPath) ThenMessageBox.Show(lstMethod(i) + + xlSheet.Name + )Exit SubEnd IfDim fs As New FileStream(strPath, FileMode.Append)Dim fw As New StreamWriter(fs)Dim lstYinsu As New ArrayListDim lstReturn As New ArrayListDim lstCaozuo As New ArrayListDim lstExp As New ArrayListfw.WriteLine()fw.WriteLine( /*)For rowIndex As Integer = 1 To xlSheet.UsedRange.Rows.CountIf .Equals(xlSheet.Cells(rowIndex, B).Value() Thenfw.WriteLine( * + xlSheet.Cells(rowIndex + 1, B).Value() + .)End IfIf .Equals(xlSheet.Cells(rowIndex, B).Value() ThenFor j As Integer = rowIndex + 2 To xlSheet.UsedRange.Rows.CountIf Not (xlSheet.Cells(j, C).Value() Is Nothing _OrElse .Equals(xlSheet.Cells(j, C).Value() ThenIf (xlSheet.Cells(j, E).Value() Is Nothing _OrElse .Equals(xlSheet.Cells(j, E).Value() ThenMessageBox.Show(lstMethod(i) + + xlSheet.Name + h)Exit SubElselstYinsu.Add(xlSheet.Cells(j, C).Value() + + xlSheet.Cells(j, D).Value() + + xlSheet.Cells(j, E).Value().ToString)fw.WriteLine( * param + xlSheet.Cells(j, C).Value() + + xlSheet.Cells(j, D).Value() + + xlSheet.Cells(j, E).Value().ToString)End IfElseExit ForEnd IfNextEnd IfIf .Equals(xlSheet.Cells(rowIndex, B).Value() ThenFor j As Integer = rowIndex + 2 To xlSheet.UsedRange.Rows.CountIf Not (xlSheet.Cells(j, C).Value() Is Nothing _OrElse .Equals(xlSheet.Cells(j, C).Value() _OrElse VOID.Equals(xlSheet.Cells(j, C).Value().ToString.Trim.ToUpper) ThenIf (xlSheet.Cells(j, E).Value() Is Nothing _OrElse .Equals(xlSheet.Cells(j, E).Value() ThenMessageBox.Show(lstMethod(i) + + xlSheet.Name + h)Exit SubElselstReturn.Add(xlSheet.Cells(j, C).Value() + + xlSheet.Cells(j, E).Value()fw.WriteLine( * return + xlSheet.Cells(j, C).Value() + + xlSheet.Cells(j, E).Value()End IfElseExit ForEnd IfNextEnd IfIf .Equals(xlSheet.Cells(rowIndex, B).Value() ThenFor j As Integer = rowIndex + 2 To xlSheet.UsedRange.Rows.CountIf Not (.Equals(xlSheet.Cells(j, B).Value() ThenIf xlSheet.Cells(j, B).Value() IsNot Nothing ThenlstCaozuo.Add(xlSheet.Cells(j, B).Value()End IfElseExit ForEnd IfNextEnd IfIf .Equals(xlSheet.Cells(rowIndex, B).Value() ThenFor j As Integer = rowIndex + 2 To xlSheet.UsedRange.Rows.CountIf Not (xlSheet.Cells(j, B).Value() Is Nothing _OrElse .Equals(xlSheet.Cells(j, B).Value() _OrElse .Equals(xlSheet.Cells(j, B).Value() ThenlstExp.Add(xlSheet.Cells(j, C).Value() + + xlSheet.Cells(j, E).Value()fw.WriteLine( * exception + xlSheet.Cells(j, C).Value()ElseExit ForEnd IfNextEnd IfNextfw.WriteLine( */)Dim str1 As String = voidDim str2 As String = If lstReturn.Count 0 Thenstr1 = lstReturn(0).ToString.Split()(0)End IfIf lstYinsu.Count 0 Thenstr2 = lstYinsu(0).ToString.Split()(1) + + lstYinsu(0).ToString.Split()(0)End IfIf lstYinsu.Count 1 ThenFor n As Integer = 1 To lstYinsu.Count - 1str2 += , + lstYinsu(n).ToString.Split()(1) + + lstYinsu(n).ToString.Split()(0)NextEnd Iffw.WriteLine( + xlSheet.Cells(10, C).Value() + + str1 + + xlSheet.Cells(5, C).Value() + ( + str2 + ) )fw.WriteLine()Dim int131 As Integer = 0If lstCaozuo(0).ToString.Trim.Contains(g) _Or lstCaozuo(0).ToString.Trim.Contains(g) ThenFor m As Integer = 1 To lstCaozuo.Count - 1If lstCaozuo(m).ToString.Trim.Contains() Thenint131 = mExit ForEnd IfNextEnd IfIf int131 0 ThenFor j As Integer = 0 To int131 - 1If lstCaozu