reading Excel files in C sharp the easy and free way.
reading excel files should be easy right. I just felt really frustrated after reading code blocks online
you'll learn how to read from a single worksheet multiple worksheets and a password-protected file
let's create a new project in Visual Studio.
It'll be the unit test framework project and we'll call the project Excel tests
next up let's install the new get packaged Excel mapper. Rather than using the
Microsoft Interop Excel NuGet package which is of a verbose package and
complicated this one is much easier and more concise to work with. Great let's
read our first worksheet it's a list of names and we will create a new person
c-sharp class and match each of the properties or rather column headers within Excel
to c-sharp last that being first name last name date and email let's create
our first unit test we'll call it I can read hundred rows from excel sheet
because the excel sheet contains and add rows so that's what we will be expecting
to write that will write a search our equal hundred and compare that to a
variable count now let's make some magic happen using our Excel Napper library
very simple we need to pass the phone in and we'll call them AC the method of
fetch and fetch it to our person object my phone ends in
temp directory it's called list of people don't Excel basics and that's it
we've got a list of people let's test this so will the sign count and count
the number of objects we getting back good practices to fail the unit test the
first time by change the expected number two and written one and closing the
existing worksheet yeah yep that's failed let's change it to 100 run it
again and we've passed great let's read from a second worksheet we will just
simply copy the existing code which is the great one line of code and pass one
additional argument name that being the sheet name let's run it boss done that's
it finally it's read from a parser
protected file I'll admit this is somewhat of a key because Excel method
does not support reading and encrypted file sanera
so we will need to yes install the infamous excel dot into our office
package I know stick with me here let's first create a
simple using statement here so we can more easily refer to the excel on top
object okay now for the hack we will open the encrypted workbook then we will
save it as a new workbook with no password and finally you will read it
to open it will use their Microsoft Excel into up library Excel application
I hate this library can you tell w file workbooks mmm open positive file name
password password that we know that we've got and finally we'll save it as a
new one and importantly pass the new file name I'll call it underscore no
password well I forgot something there password needs to be no and importantly
workbook dot close otherwise it's locked and then we can only read that new
unprotected file we know that nice and easy the farm I get this right and that
is it it's run it
failed as expected let's change the 200 run it again we're good