• 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

    • A few things I am wondering about for S3: A.  What's Pike doo gonna look like this time around....yea I've watched the trailers & it looks poofed up, but still.... B. When will the unintelligible, physco-babbling Pelia go away (or at least learn how to speak English properly, without that crappy, slurred accent that sounds like she's on crack, Mary Jane, & LSD at the same time) ?  Hopefully Scotty's arrival will mean her departure is near.... C.  When do we get to see more of the stunningly gorgeous No. 1, preferably in regular/civilian clothes that show off her physical attributes better ? D. Is Spock EVER gonna get laid properly, human style ?  I feel certain that Christine could make any Vulcan-human horizontal bomp a thing to remember for a LONG time  E.  Can we PLEEEEEEZE get rid of the hatchet-head/buzz cut hair styles on Ortega & anyone else that has it....  But otherwise, as Pike says:  HIT IT !  
    • RIP Hotlips..... IMHO, her best scenes were the few where she dared to let her REAL feminine side show through from underneath all that crappy, worn-torn soldier facade that she had to keep up with...she instantly lit up every room or situation where she was featured !
    • Helium Converter 3.3.69.0 by Razvan Serea Helium Converter is a free Windows utility for converting audio files between formats such as MP3, FLAC, AAC, WMA, OGG, and WAV. It supports batch conversion, preserves or updates tag information, and offers features like volume normalization. With a simple interface, it's ideal for users who need to convert large music libraries quickly and efficiently while retaining metadata. Helium Converter key features: Supports file formats: MP3, MP4, FLAC, AAC, M4A, WMA, WAV, OGG, OPUS, APE.... Batch conversion for large music libraries Preserves and edits metadata (ID3, Vorbis Comments, etc.) Volume normalization to equalize loudness Album art extraction and embedding Drag-and-drop interface for quick file selection Adjustable encoding parameters (bitrate, sample rate, channels) Uses internal codecs for consistent performance Supports CUE sheets for split track conversion File renaming based on tags during export Unicode support for international file and tag names Logging of conversion processes for troubleshooting Multi-core CPU support for faster conversions Download: Helium Converter 3.3.69.0 | 25.1 MB (Freeware) Links: Helium Converter Home Page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Since it's been quite a while since the last episode aired, would it be fair (or cruel) to refer to Peggy as a MILF, or even a SMILF ?  HAHAHAHAHAHA
    • Their computers are not gonna stop working in October
  • Recent Achievements

    • One Year In
      WaynesWorld earned a badge
      One Year In
    • First Post
      chriskinney317 earned a badge
      First Post
    • Week One Done
      Nullun earned a badge
      Week One Done
    • First Post
      sultangris earned a badge
      First Post
    • Reacting Well
      sultangris earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      172
    2. 2
      ATLien_0
      122
    3. 3
      snowy owl
      121
    4. 4
      Xenon
      116
    5. 5
      +Edouard
      93
  • Tell a friend

    Love Neowin? Tell a friend!