• 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

    • Amazon lays off more staff across Goodreads and Kindle divisions by Hamid Ganji Dozens of Amazon employees working on the retailer's book divisions have been laid off. As reported by Reuters, Amazon confirmed that it's cutting jobs across the Goodreads review site and Kindle units, which impacts fewer than 100 workers. Amazon says the recent layoffs across Goodreads and Kindle divisions are meant to improve efficiency and streamline operations. The giant retailer has constantly reduced staff across various divisions over the past few years. According to CEO Andy Jassy, reducing headcounts helps the company to eliminate bureaucracy. "As part of our ongoing work to make our teams and programs operate more efficiently and to better align with our business roadmap, we've made the difficult decision to eliminate a small number of roles within the Books organization," an Amazon spokesperson said. Layoffs recently impacted employees in Amazon's Wondery podcast division, devices and services units, communications, and in-store staff. However, Amazon's Q1 results show the retailer has added about 4,000 jobs compared to Q4 2024. After the Covid pandemic settled down, many companies began laying off thousands of staff they hired during the pandemic to respond to growing demands. The layoff trend among tech firms still exists today, and AI has amplified it. The latest data shows that in 2025, about 62,832 tech employees were laid off across 141 tech companies. Also, 152,922 tech employees across 551 companies were laid off in 2024. More layoffs are expected to occur due to declining economic growth, tariffs, and the expansion of AI across companies. Amazon is also gearing up to double down in AI investments and robotics. The company has recently announced the forming of a new agentic AI team to develop an agentic AI framework for use in robotics. Also, a new report by The Information indicates that Amazon has begun testing humanoid robots for package delivery.
    • Major Privacy 0.98.1.1 Beta by Razvan Serea MajorPrivacy is a cutting-edge privacy and security tool for Windows, offering unparalleled control over process behavior, file access, and network communication. It is a continuation of the PrivateWin10 project. By leveraging advanced kernel-level protections, MajorPrivacy creates a secure environment where user data and system integrity are fully safeguarded. Unlike traditional tools, MajorPrivacy introduces innovative protection methods that ensure mounted encrypted volumes are only accessible by authorized applications, making it the first and only encryption solution of its kind. MajorPrivacy – Ultimate Privacy & Security for Windows key features Process Protection – Isolate processes to block interference from unauthorized apps, even with admin privileges. Software Restriction – Block unwanted apps and DLLs to ensure only trusted software runs. Revolutionary Encrypted Volumes Secure Storage – Create encrypted disk images for sensitive data. Exclusive Access – Unlike traditional tools, only authorized apps can access mounted volumes—blocking all unauthorized processes. File & Folder Protection – Lock down sensitive files and prevent unauthorized access or modifications. Advanced Network Firewall – Control which apps can send or receive data online. DNS Monitoring & Filtering – Track domain access and block unwanted sites (Pi-hole compatible filtering coming soon). Tweak Engine – Disable telemetry, cloud integration, and invasive Windows features for better privacy. Why MajorPrivacy? Kernel-Level Security – Protects at the deepest system level. Unmatched Encryption Protection – Keeps mounted volumes safe from all unauthorized access. Full System Control – Block, isolate, or restrict processes as needed. Enhanced Privacy – Stops Windows & apps from collecting unnecessary data. Perfect for privacy-conscious users, IT pros, and anyone who wants total system control. Major Privacy 0.98.1.1 Beta changelog: The 0.98.1 release of MajorPrivacy introduces significant enhancements and a number of critical fixes aimed at improving usability, localization, and system integration. A major new feature is the introduction of full translation support, allowing the application interface and tweaks to be localized into multiple languages. Initial translations include AI-assisted German and Polish versions, a community-contributed Turkish translation, and Simplified Chinese. Users interested in contributing translations or adding new languages are encouraged to participate via the forum. This version also improves compatibility and deployment by bundling the Microsoft Visual C++ Redistributable with the installer, which is required for the ImDisk user interface. Several important bugs have been resolved. The installer now correctly removes the driver during uninstallation. Tweak definitions have been cleaned up for better consistency. A number of networking issues were addressed, including failures related to network shares and incorrect handling of mapped drive letters. It is now required to use full UNC paths for defining rules involving shared resources. Additionally, configuration persistence issues on system shutdown have been fixed, as well as problems affecting protected folder visibility and rule precedence involving enclave conditions. Finally, the underlying driver code has been refactored, laying the groundwork for better maintainability and future enhancements. MajorPrivacy-v0.98.1.1.exe (0.98.1a) hotfix for #71 Download: Major Privacy 0.98.1.1 Beta | 47.4 MB (Open Source) View: MajorPrivacy Home Page | Github Project page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • OpenAI responds to The New York Times' ChatGPT data demands by Pradeep Viswanathan The New York Times has sued OpenAI for the unauthorized use of its news articles to train large language models. As part of the ongoing lawsuit, the NYT recently asked the court to require OpenAI to retain all ChatGPT user content indefinitely. The NYT's argument is that they may find something in the data that supports their case. Brad Lightcap, COO of OpenAI, wrote the following regarding the NYT's sweeping demand: OpenAI has already filed a motion asking the Magistrate Judge to reconsider the preservation order, since indefinite retention of user data breaches industry norms and its own policies. Additionally, OpenAI has also appealed this order with the District Court Judge. Until OpenAI wins its appeal, it will be complying with the court order. The content defined by the court order will be stored separately in a secure system and will be accessed or used only for meeting legal obligations. Only a small, audited OpenAI legal and security team will be able to access this data as necessary to comply with our legal obligations. As of early 2025, ChatGPT has over 400 million weekly active users, and this data retention order will affect a significant number of them. OpenAI confirmed that ChatGPT Free, Plus, Pro, and Teams subscription users, and developers who use the OpenAI API (without a Zero Data Retention agreement) will be affected by this order. ChatGPT Enterprise, ChatGPT Edu, and API customers who are using Zero Data Retention endpoints will not be affected by this court change.
  • Recent Achievements

    • 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
    • Week One Done
      CHUNWEI earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      428
    2. 2
      +FloatingFatMan
      239
    3. 3
      snowy owl
      211
    4. 4
      ATLien_0
      211
    5. 5
      Xenon
      157
  • Tell a friend

    Love Neowin? Tell a friend!