• 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

    • The first link on the github page gives me a very, very red page warning me about phishing........
    • Hey Nick! 👋 No worries at all — you're not alone in this, and it's great you're asking for help. Let me walk you through a simple, step-by-step fix using the tool mentioned on that AOMEI Partition Assistant guide — it really does the trick for many people with this exact issue! ✅ Here’s how to restore your USB back to full 256GB on Windows 11: Download & Install AOMEI Partition Assistant Go to the link you shared: AOMEI Partition Assistant and click the Download Freeware button. Insert your USB stick (Sandisk Ultra 256GB) Make sure it's properly connected. Wait for the system to detect it. Launch AOMEI Partition Assistant Once open, you'll see all your connected drives listed. Locate your USB Drive Look for the one that says something like Disk X – Removable and shows only 3.1GB or so. Right-click on your USB Drive’s Partition Then select Delete Partition → confirm the deletion. Now, right-click on the unallocated space (it should now show full unallocated capacity) Choose Create Partition → format it as FAT32 or exFAT (recommended for larger drives) → click OK. Click “Apply” in the top left corner Then hit Proceed to execute the pending operations. Wait a bit... and boom! 🎉 Your USB should now be restored to its full 256GB capacity!
    • When Facebook bought Beat Saber, they promised exactly this would never happen. Facebook can’t not lie.
    • Depends on what you mean by "this data". Nvidia can show you quite a few of those as well in their performance overlay, and I guess they might've assumed that if anyone wants to see more, they'll just use Afterburner as the de facto standard. As for real framerate vs framegen framerate, I don't think they exactly want you to know, given that their marketing has been strongly focused on hiding the real framerate and pretending the generated one is all that matters...
  • Recent Achievements

    • First Post
      Fuzz_c earned a badge
      First Post
    • First Post
      TIGOSS earned a badge
      First Post
    • Week One Done
      slackerzz earned a badge
      Week One Done
    • Week One Done
      vivetool earned a badge
      Week One Done
    • Reacting Well
      pnajbar earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      713
    2. 2
      ATLien_0
      279
    3. 3
      Michael Scrip
      209
    4. 4
      +FloatingFatMan
      201
    5. 5
      Steven P.
      131
  • Tell a friend

    Love Neowin? Tell a friend!