• 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.
  • Popular Now

  • Posts

    • Apple is making a scary move: a private company overchallenging a legit government and legislation. I am not a fan of democracy, but I am more scared of corporatism. The EU must take a strong stance now: Apple should comply with the law or face consequences, and it must do so immediately.
    • Intel LGA1700 14th Gen i5-14600K and 12th Gen 12600K are selling for great prices by Sayan Sen Intel is back again with another great CPU sale. We reported on the 14600K deal recently, which is now available for an even better price of just $195, and this includes a free AIO liquid cooler (purchase link under the specs list below). Aside from the 14th Gen i5, the 12th Gen 12600K is also available for just $125 (purchase link under the specs list below). Both chips are compatible with LGA1700 socket. First up, we have the Intel 14600K. The chip performs about as well as an AMD Ryzen 7600(X) in gaming and beats it in productivity. Since this is a socket LGA1700 SKU, buyers are advised to pair it up with a decent motherboard that has good VRM power delivery and cooling in place (ideally a Z790 chipset motherboard), plus you will need a good quality air cooler (and a complementary good case with excellent airflow) or a 240/280 mm AIO liquid cooler. The free MSI AIO cooler that comes with the bundle should suffice even for some moderate overclocking. Yes, overclocking is possible on both the 14600K and 12600K, as they are both K SKUs and thus are unlocked chips. The technical specs of the 14600K are given below: Core Count: 14 (6 Performance Cores + 8 Efficiency Cores) Thread Count: 20 Base Clock Frequency: 3.5 GHz (P-core), 2.6 GHz (E-core) Boost Clock Frequency: Up to 5.3 GHz Cache: 24 MB Cache Stock Memory Support: DDR4 (3200 MT/s) and DDR5 (5600 MT/s) Socket: LGA 1700 Base TDP: 125 W Integrated Graphics: Intel UHD Graphics 770 PCIe Support: PCIe Gen 5 and Gen 4 (16 lanes) Process Technology: Intel 7 (10 nm) Maximum Temperature: 100° C Get the 14600K at the link below: Intel Core i5-14600K BX8071514600K (additional $5 off w/ promo code SSET237, limited offer) + MSI MAG Coreliquid A13 240mm Liquid Cooler + Free Intel Spring Bundle (Civilization VII & Dying Light: The Beast): $194.99 (Sold and Shipped by Newegg US first-party seller) Up next we have the Core i5-12600K which tends to slot right in between Ryzen's 5000 series and 7000 series in gaming performance, typically trading blows with the 5800X3D. In productivity, it can get even with Ryzen's 7600(X) or a 5900X. The technical specs of the 12600K are given below: Core Count: 10 (6 Performance Cores + 4 Efficiency Cores) Thread Count: 16 Base Clock Frequency: 3.7 GHz (P-core), 2.8 GHz (E-core) Boost Clock Frequency: Up to 4.9 GHz Cache: 20 MB Cache Stock Memory Support: DDR4 (3200 MT/s) and DDR5 (4800 MT/s) Socket: LGA 1700 Base TDP: 125 W Integrated Graphics: Intel UHD Graphics 770 PCIe Support: PCIe Gen 5 and Gen 4 (16 lanes) Process Technology: Intel 7 (10 nm) Maximum Temperature: 100° C Get the 12600K at the link below: Core i5-12600K Processor Box, BX8071512600K: $125.33 (Amazon US) This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • Yes, this should have the Sponsored tag on it methinks.
    • Is there a conversion kit available so we can make it not look like something from a low-polygon video game from the 1990s?
    • "The company's core argument is that the changes mandated by the EU's DMA, which came into full effect in March 2024, introduce serious security and privacy risks for users. Apple claims that allowing sideloading and alternative app stores effectively opens the door for malware, fraud, scams, and other harmful content." I've heard this anti-sideloading argument a lot, but only from people who never wanted to sideload and will never do (so no risks for them), and from Apple themselves.
  • Recent Achievements

    • One Month Later
      EdwardFranciscoVilla earned a badge
      One Month Later
    • One Month Later
      MoyaM earned a badge
      One Month Later
    • One Month Later
      qology earned a badge
      One Month Later
    • One Year In
      Frinco90 earned a badge
      One Year In
    • Apprentice
      Frinco90 went up a rank
      Apprentice
  • Popular Contributors

    1. 1
      +primortal
      453
    2. 2
      +FloatingFatMan
      247
    3. 3
      snowy owl
      240
    4. 4
      ATLien_0
      196
    5. 5
      Xenon
      142
  • Tell a friend

    Love Neowin? Tell a friend!