.NET Reading Multiple Excel Files

[Shadow_101]

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!

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

'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

[directhex]

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

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
}

[TheAnimus]

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!

[Shadow_101]

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.

-Snip-

Thank you! this was perfect for what i wanted.

cheers for the input guys

[chadders]

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