.NET Reading Multiple Excel Files

I’m trying to write a simple program in work, but my .NET skills are letting me down!


I need to be able to select a folder, read through a few hundred excel files extracting some data (will be in the same place in every excel file) and then just put it all together in a single excel file.


The part I’m stuck how to read through the excel files, I’ve got the folder path, using the selectfolder dialog. but I’m unsure how i could get the program to read through each excel file until it reaches the end of the folder. I’ve had a few experiments with loops but I’m not getting anywhere.

any help would be much appreciated!

edit: simple .vbscript file I used for exactly the same reason (data takeon from a bunch of Excel sheets). Sorry, no .NET (I'm currently at the beginning of reading 'beginning' c#).

edit: you'll end up with results.txt which is a CSV of the data extracted.

Code:

---

'batch process all .xls files in the same directory where
'this script is located, "results.txt" and "errors.txt"
option explicit

call main()

private sub main()
        dim filePath
        dim fileNames
        dim resultsFile, errorsFile
       
        call checkengine()
       
        filepath = trim(replace(wscript.scriptFullName, wscript.scriptName, ""))
        if filepath = "" then
                call wscript.echo("ERROR: Failed to determine path")
                exit sub
        end if
       
        if not getFiles(fileNames, filePath, resultsFile, errorsFile) then exit sub
        call wscript.echo("Found " & ubound(fileNames) + 1 & " file(s) to process")
       
        call processBooks(filePath, fileNames, resultsFile, errorsFile)
       
        call resultsFile.close()
        call errorsFile.close()
       
        set resultsFile = nothing
        set errorsFile  = nothing
       
        call wscript.echo("")
        call wscript.echo("Press RETURN to continue...")
        call WScript.StdIn.Read(1)
end sub

private sub checkengine()
        dim pcengine, wshshell
       
        pcengine = LCase(Mid(WScript.FullName, InstrRev(WScript.FullName,"\")+1))
        If Not pcengine="cscript.exe" Then
        Set WshShell = CreateObject("WScript.Shell")
        WshShell.Run "CSCRIPT.EXE """ & WScript.ScriptFullName & """"
        WScript.Quit
        End If
end sub

private function getFiles(byref fileNames, byval filePath, byref resultsFile, byref errorsFile)
        dim fso, folder, files, file
       
        getFiles = false
       
        set fso    = wscript.createObject("scripting.fileSystemObject")
        set folder = fso.getFolder(filePath)
        set files  = folder.files
       
        for each file in files
                if right(file.name, 4) = ".xls" then
                        if isEmpty(fileNames) then
                                redim fileNames(0)
                                fileNames(0) = file.name
                        else
                                redim preserve fileNames(ubound(fileNames) + 1)
                                fileNames(ubound(fileNames)) = file.name
                        end if
                end if
        next
       
        set resultsFile = fso.openTextFile(filePath & "results.txt", 2, true)
        set errorsFile  = fso.openTextFile(filePath & "errors.txt", 2, true)
       
        getFiles = true
       
        set files  = nothing
        set folder = nothing
        set fso    = nothing
end function

private sub processBooks(byref filePath, byref fileNames, byref resultsFile, byref errorsFile)
        dim excel, excelsheet
        dim i, j
        dim str
       
        set excel = wscript.createObject("Excel.Application")
        excel.DisplayAlerts = 0 ' don't display any messages about documents needing to be converted from  old Excel file formats
       
        str = "Workbook,Worksheet,FX,FX Rate,Season,Article,"
        str = str & "Fabric Cost,"
        str = str & "CM Cost,"
        str = str & "Trimmings,"
        str = str & "Others,"
        str = str & "Total FOB,"
        str = str & "Sea Freight,"
        str = str & "Insurance,"
        str = str & "Inland Freight,"
        str = str & "Fee Agents,"
        str = str & "Unexpected Costs,"
        str = str & "LC Cost/DP Cost,"
        str = str & "Duty EU,"
        str = str & "Hgz Processing Cost,"
        str = str & "Hgz Accessories,"
        str = str & "Press,"
        str = str & "Pick & Pack,Transport to Customer,Other,TK2 Price"
        call resultsFile.writeLine(str)
       
        str = "Workbook,Worksheet,Error"
        call errorsFile.writeLine(str)
       
        for i = 0 to ubound(fileNames)
                call wscript.echo("Processing workbook: " & fileNames(i))
                call excel.workbooks.open(filePath & fileNames(i), false, true) ' open the excel document as read-only open (path, confirmconversions, readonly)
               
                for j = 1 to excel.worksheets.count
                        call wscript.echo("Processing worksheet: " & j & " of " & excel.worksheets.count)
                        set excelsheet = excel.activeWorkbook.worksheets(j)
                       
                        str = fileNames(i) & "," & excelsheet.name & ","
                       
                        if left(trim(excelsheet.range("A2").value), 19) <> "Product Calculation" then
                                call errorsFile.writeLine(str & "Not a Melka product worksheet.")
                        else
                                str = fileNames(i) & "," & excelsheet.name & ","
                                str = str & excelsheet.range("D3").value & ","
                                str = str & excelsheet.range("F3").value & ","
                                str = str & excelsheet.range("A4").value & ","
                                str = str & excelsheet.range("F4").value & ","
                                str = str & excelsheet.range("H15").value & ","
                                str = str & excelsheet.range("H16").value & ","
                                str = str & excelsheet.range("H17").value & ","
                                str = str & excelsheet.range("H18").value & ","
                                str = str & excelsheet.range("H21").value & ","
                                str = str & excelsheet.range("H22").value & ","
                                str = str & excelsheet.range("H23").value & ","
                                str = str & excelsheet.range("H26").value & ","
                                str = str & excelsheet.range("H27").value & ","
                                str = str & excelsheet.range("H28").value & ","
                                str = str & excelsheet.range("H29").value & ","
                                str = str & excelsheet.range("H30").value & ","
                                str = str & excelsheet.range("H31").value & ","
                                str = str & excelsheet.range("H32").value & ","
                                str = str & excelsheet.range("H33").value & ","
                                str = str & excelsheet.range("H37").value & ","
                                str = str & excelsheet.range("H38").value & ","
                                str = str & excelsheet.range("H39").value & ","
                                str = str & excelsheet.range("H40").value
                                call resultsFile.writeLine(str)
                        end if
                       
                        set excelsheet = nothing
                next
               
                call excel.workbooks(1).close
        next
       
       
        call excel.quit()
        set excel = nothing
end sub

---

loops are the right way to do it, at least in part.

Code:

---

foreach( string FileName in System.IO.Directory.GetFiles( dirnamevariable ) )
{
    // program logic goes here - you have a full path
    // to the file, for ever file in turn, so generally do
    // whatever you need. usually you'd setup your
    // output file OUTSIDE the loop, at the top, put
    // a "read file, output correct line to output file"
    // here, then close the output file after the end
    // of the foreach
}

---

Reading the excel file here is the 'fun' part. As i'm slightly suprised Hex didn't mention, its a proprietry format, which means when you want to do something propreitry the fun starts.

There are 3 main options open to you.

1 Automation, you use "COM Automation" to create an invisble excel session, which you open the file in. You can then either save it as CSV or even extract the data via the Excel Object Model. This might sound scary but is actually very easy to do, but requires that Excel be present on the PC. This is the approch used in the VBA above.

2 Use someones Excel file library. There are quite a few commercial ones of these floating about, some are better than others but they do cost a fair bit.

3 Write your own, microsoft specified the excel file format, you can implement your own reader, this of course is not for the faint hearted and would take more than a little while.

There is another way where you can use an OBDC excel driver, but this method is in my experiance bug ridden and very anoying from .Net.

However enless you have a need for this app to be run autoamtically as a system service or something, i'd be VERY tempted to write this in VBA in an excel workbook. This will probably be faster to develope maintian, and because of the 'cpu cost' of .Net run faster too!

Quote:

---

Originally Posted by chadders

edit: simple .vbscript file I used for exactly the same reason (data takeon from a bunch of Excel sheets). Sorry, no .NET (I'm currently at the beginning of reading 'beginning' c#).

edit: you'll end up with results.txt which is a CSV of the data extracted.

Code:

---

-Snip-

---

---

Thank you! this was perfect for what i wanted.

cheers for the input guys

No worries - I've been repeat lurking since I got Hexus help with my PC build, glad I was FINALLY able to help someone ;)