• 0

[ASP.NET 3.5] Uploading an Excel file & reading it.


Question

Hello,

I'm trying to upload an Excel file, and read the information found in the file in order to save them to a database. What's the best of doing this? I've never done anything like it before. The fields in the Excel file mirrors the fields in the database I have.

thanks.

16 answers to this question

Recommended Posts

  • 0

Hi Ali,

Depending on the version of excel, there are a couple of things you can do. Let's define an example class.

public class Person {
  public string Name { get; set; }
  public int Age { get; set; }
}

And our spreadsheet:

post-92970-1262079371.png

We'll use these in the examples:

ADO.NET

You can use ADO.NET to read from the spreadsheet. What you can do, is read from that worksheet in a Sql like manner:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"";

List<Person> people = new List<Person>();
using (OleDbConnection conn = new OleDbConnection(connectionString)) {
  conn.Open();

  using (OleDbCommand command = new OleDbCommand("SELECT Name, Age FROM [People$]", conn)) {
	using (OleDbDataReader reader = command.ExecuteReader()) {
	  while (reader.Read()) {
		string name = reader["Name"].ToString();
		int age = int.Parse(reader["Age"].ToString());

		people.Add(new Person() 
				   {
					  Name = name,
					  Age = age
				   });
	  }
	}
  }
}

Microsoft Excel Object Library

Office 2003/2007 includes some pre-built Primary Interop Assemblies (PIAs) bundled with the installation, so you need Office installed on the server for this to work.

Add a COM reference to the project, find "Microsoft Excel Object Library", and you can do the following:

using Excel; = Microsoft.Office.Interop.Excel;

Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open("Book1.xlsType.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Excel.Worksheet worksheet = workbook.Worksheets[1];
List<Person> person = new List<Person>();

bool hasValue = true;
int i = 2; // Start at second row.
while (hasValue) {
  if (worksheet.Cells[i, 1].ToString() == "") {
	hasValue = false;
	continue;
  }

  string name = worksheet.Cells[i, 1].ToString();
  int age = int.Parse(worksheet.Cells[i, 2].ToString());

  people.Add(new Person() 
			 {
				Name = name,
				Age = age
			 });
}

workbook.Close(Excel.XlSaveAction.xlDoNotSaveChanges, Type.Missing, Type.Missing);
workbook = null;
app = null;

The Type.Missing bits are a necessary evil I believe, they represent parameters that are optional, but of course C# doesn't support optional parameters. Not sure if you can pass null in.

ExcelPackage

In Office Excel 2007, the format of the file switched to OOXML. There is a Codeplex project called ExcelPackage which buils on the existing System.IO.Packaging namespace to support reading the newer format files. You need to visit http://excelpackage.codeplex.com/ and download the library.

using (ExcelPackage package = new ExcelPackage("Book1.xlsx")) {
  ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
  List<Person> person = new List<Person>();

  bool hasValue = true;
  int i = 2; // Start at second row.
  while (hasValue) {
	if (worksheet.Cell(i, 1).ToString() == "") {
	  hasValue = false;
	  continue;
	}

	string name = worksheet.Cell(i, 1).Value.ToString();
	int age = int.Parse(worksheet.Cell(i, 2).Value.ToString());

	people.Add(new Person() 
			   {
				  Name = name,
				  Age = age
			   });
	}
}

So there are many options you can do, depending on your file and environment setup. Let me know if that helps.

  • 0

If you want to access Shape and Picture objects, you'll need to go the interop route, as the ADO.NET won't have any concept of images.

Here is something I found on a quick Bing: http://joymonscode.blogspot.com/2009/01/re...excel-file.html

  • 0

*bump*

Reading from the excel sheet is not working properly ;(. I'm always getting: System.__ComObject.

Here's the code I have:

		Excel.ApplicationClass app = new Excel.ApplicationClass();
		Excel.Workbook book = app.Workbooks.Open(@"C:\Inetpub\Wwwroot\MyVeryOwnWebsite\test.xlspe.Missing, Type.Missing, Type.Missing,
									Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
									Type.Missing, Type.Missing, Type.Missing);

		Excel.Worksheet sheet = book.Worksheets[1] as Excel.Worksheet;

		for (int i = 2; i < 4; i++)
		{
			this.lbl.Text += sheet.Cells[i, 7].ToString();
			this.lbl.Text += "<br />";
		}

		book.Close(Excel.XlSaveAction.xlDoNotSaveChanges, Type.Missing, Type.Missing);
		book = null;
		app = null;

What should I do in order to read the content of that cell? Also, if the cell is a DropDownList how do I read it?

Thanks.

  • 0

The interop system will resolve types as System.__ComObject, and may have a variety of properties, you need to cast the particular cell to an Range instance, and access its Value2 property:

		public static string GetCell(Excel.Worksheet worksheet, int row, int column)
		{
			Excel.Range range = worksheet.Cells[row, column] as Excel.Range;
			if (range == null || range.Value2 == null)
			{
				return string.Empty;
			}

			return range.Value2.ToString();
		}

Called such like:

string text = GetCell(sheet, 1, 1);

Remember Sheet coordinates are 1-based, not 0-based like arrays in .NET, so if you call Cells[i, j] on a Worksheet instance, you can't use sheet.Cells[0, 0].

Hope that helps.

Edited by Antaris
  • 0

Ummm, something is wrong. The application is spawning Excel.exe processes like crazy. It looks like it's not closing the connection or something?

		book.Close(Excel.XlSaveAction.xlDoNotSaveChanges, Type.Missing, Type.Missing);
		book = null;
		app = null;

Is there anything else I could do? After 4 runs, there were 4 Excel.exe processes in the TaskManager and I had to terminate them manually.

  • 0

Ah, we've not been releasing our COM objects!:

public static void ReleaseObject(object instance)
{
	try {
		System.Runtime.InteropServices.Marshal.ReleaseComObject(instance);
	} catch { } finally {
		instance = null;
	}
}

The interop doesn't dispose of these COM resources for you, so we need to do it manually:

object missing = Type.Missing;

Excel.Application app = new Excel.ApplicationClass();
Excel.Workbook workbook = app.Workbooks.Open(@"C:\Book1.xlsssing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Excel.Worksheet worksheet = workbook.Sheets[1] as Excel.Worksheet;

ReleaseObject(worksheet);

workbook.Close(false, missing, missing);
ReleaseObject(workbook);

app.Quit();
ReleaseObject(app);

Make sure you call the Quit(); method or the office server component won't quit, and the process will remain.

Hope that helps.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Showing people how to self host their own media is harmful, according to YouTube by David Uzondu YouTube has taken down a video from tech creator Jeff Geerling that demonstrated how to use LibreELEC, a lightweight operating system for turning devices into media centers, on a Raspberry Pi 5 for 4K video playback. The video, titled "I replaced my Apple TV—with a Raspberry Pi", originally published in May 2024, was removed in June 2025 under YouTube's "Harmful or dangerous content" policy. According to the violation notice, YouTube claimed the video showed "how to get unauthorized or free access to audio or audiovisual content, software, subscription services, or games that usually require payment." Image via Jeff Geerling Geerling strongly refuted YouTube's claims. He stated clearly, "I purposefully avoid demonstrating any of the tools that are popularly used to circumvent purchasing movie, TV, and other media content." He also emphasized that his own Network Attached Storage, or NAS, contains only legally acquired content. This isn't Geerling's first run-in with YouTube over self-hosted media tools. Last October, his tutorial titled "Better than Disney+: Jellyfin on my NAS" was hit with a similar strike for showing how to set up Jellyfin, an open source media server for organizing and streaming personal media. That strike was quickly overturned after an appeal. But this time, YouTube rejected his appeal, even though the LibreELEC video had been live for over a year, had racked up over half a million views, and contained no promotion of anything illegal. This whole thing feels a lot like what happened with youtube-dl. It's a simple command-line tool for downloading videos, used by tons of people for perfectly legal reasons like saving public domain content or backing up their own uploads. But that didn't stop the RIAA from hitting it with a DMCA takedown on GitHub, calling it a piracy tool. The community pushed back hard, and eventually it was brought back, thanks in part to support from groups like the Electronic Frontier Foundation who pointed out that not everything that can be misused is automatically bad. Side note, the youtube-dl project appears to be unmaintained (the last release was in 2021), if you're looking for an alternative, consider its very popular fork, yt-dlp. After the appeal was rejected, YouTube required Geerling to complete "policy training" to avoid a more serious, permanent strike on his channel. He eventually gave in and took the training. Anyways, if you're interested, he has uploaded the removed LibreELEC video to Internet Archive for anyone to watch.
    • Thanks to Herr Musk being a total poison pill, they can't even give those pieces of scrap away.  They can't even ship them to the UK/EU because they're completely illegal over here.  
    • Intel vs AMD? Microsoft seemingly has a clear recommendation for Windows 11 Pro PC upgrade by Sayan Sen Microsoft and its partners are now quite actively and regularly promoting the upgrade to Windows 11. Asus, for example, recently published blog posts about the "mandatory Windows 11 upgrade" that is coming as the Windows 11 end of support date nears. Microsoft itself, from time to time, urges users to upgrade to its newest OS. Back in February 2024, Microsoft released an advert highlighting the best things about Windows 11 over Windows 10. Later, in June in the same year, the tech giant busted "myths and misconceptions" surrounding a Windows 11 upgrade. And towards the end of 2024, in December, Microsoft put up a blog post outlining the gaming features a user enjoys on 11 if they were to upgrade from Windows 10. While technically there is nothing wrong with a company promoting its own product, sometimes these campaigns make little sense and they fall flat. For example, in January earlier this year, Microsoft shared a blog post headlined "Free Upgrade to Windows 11 (For a Limited Time Only)" which did not make sense as it offered little information about it being a "free upgrade," and it was rightfully, later taken down. The company is back again with a new commercial about Windows 11. This time it is aimed mainly at IT professionals and enterprises as the advert talks about upgrading to Windows 11 Pro from Windows 10. This landed a few days after Microsoft released a new backup tool for organizations for such a purpose. What is interesting is that the company is promoting Intel's vPro processors and there is no mention of AMD's Ryzen PRO parts. The commercial is posted on the Windows official YouTube channel and has been titled "Right side of risk | Windows 11 Pro and Intel". The video description says, "Windows 10 support ends October 14. Stay on the right side of risk—upgrade now to the power of Windows 11 Pro PCs with Intel vPro®." AMD does have a support article about the subject headlined "Support Your Customers’ Move to Windows 11, With AMD Ryzen™ PRO Processors" and you can find it here. This is not the first time Microsoft has promoted Intel CPUs over AMD ones. Back in 2021, the company also put up a full page explaining how users should "look for the Intel EVO badge" on a new device before making a purchase decision because such PCs are "verified wonderful" which was a bit of an odd language. Like the limited upgrade time article, the page above was taken down after we reported on it (can be viewed via the archive) and replaced with something else. The new commercial was published about a couple of days ago, and it is possible that Microsoft may have a dedicated AMD advert too in the pipeline scheduled for a later release, and that would only be fair if both companies get a similar treatment.
    • Don’t blame web developers for the downfall of Firefox. 😂
  • Recent Achievements

    • Week One Done
      luxoxfurniture earned a badge
      Week One Done
    • First Post
      Uranus_enjoyer earned a badge
      First Post
    • Week One Done
      Uranus_enjoyer earned a badge
      Week One Done
    • Week One Done
      jfam earned a badge
      Week One Done
    • First Post
      survivor303 earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      435
    2. 2
      +FloatingFatMan
      238
    3. 3
      snowy owl
      213
    4. 4
      ATLien_0
      211
    5. 5
      Xenon
      155
  • Tell a friend

    Love Neowin? Tell a friend!