Results 1 to 6 of 6

Thread: .NET Reading Multiple Excel Files

  1. #1
    Registered+
    Join Date
    Aug 2005
    Location
    Liverpool/Brum Uni
    Posts
    18
    Thanks
    0
    Thanked
    0 times in 0 posts

    .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!

  2. #2
    Senior Member
    Join Date
    May 2007
    Location
    Cheshire
    Posts
    329
    Thanks
    16
    Thanked
    41 times in 25 posts
    • chadders's system
      • CPU:
      • Sony Vaio VGN-AW11Z/B
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • Samsung 226BW + Belinea 17" lcd
      • Internet:
      • Be Pro - 14000/1200 (down/up kps)

    Re: .NET Reading Multiple Excel Files

    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

  3. #3
    Comfortably Numb directhex's Avatar
    Join Date
    Jul 2003
    Location
    /dev/urandom
    Posts
    17,074
    Thanks
    228
    Thanked
    1,027 times in 678 posts
    • directhex's system
      • Motherboard:
      • Asus ROG Strix B550-I Gaming
      • CPU:
      • Ryzen 5900x
      • Memory:
      • 64GB G.Skill Trident Z RGB
      • Storage:
      • 2TB Seagate Firecuda 520
      • Graphics card(s):
      • EVGA GeForce RTX 3080 XC3 Ultra
      • PSU:
      • EVGA SuperNOVA 850W G3
      • Case:
      • NZXT H210i
      • Operating System:
      • Ubuntu 20.04, Windows 10
      • Monitor(s):
      • LG 34GN850
      • Internet:
      • FIOS

    Re: .NET Reading Multiple Excel Files

    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
    }

  4. #4
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: .NET Reading Multiple Excel Files

    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!
    throw new ArgumentException (String, String, Exception)

  5. #5
    Registered+
    Join Date
    Aug 2005
    Location
    Liverpool/Brum Uni
    Posts
    18
    Thanks
    0
    Thanked
    0 times in 0 posts

    Re: .NET Reading Multiple Excel Files

    Quote Originally Posted by chadders View Post
    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

  6. #6
    Senior Member
    Join Date
    May 2007
    Location
    Cheshire
    Posts
    329
    Thanks
    16
    Thanked
    41 times in 25 posts
    • chadders's system
      • CPU:
      • Sony Vaio VGN-AW11Z/B
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • Samsung 226BW + Belinea 17" lcd
      • Internet:
      • Be Pro - 14000/1200 (down/up kps)

    Re: .NET Reading Multiple Excel Files

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. reading 3dmark 05/06 files
    By Mithrandir in forum Help! Quick Relief From Tech Headaches
    Replies: 0
    Last Post: 04-03-2007, 08:51 PM
  2. Replies: 8
    Last Post: 18-07-2006, 02:42 AM
  3. Reading ISO files
    By krazy_olie in forum Help! Quick Relief From Tech Headaches
    Replies: 12
    Last Post: 28-12-2005, 02:10 PM
  4. Quick way to convert multiple MOV files to MPEGs?
    By Matthoulb in forum Software
    Replies: 1
    Last Post: 28-01-2004, 02:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •