Struggling to find ways for reading data from excel sheet and doing some calculation.

Question

Hi,

Please I need help to find the best way to approach the following problem.

In our company we are daily receiving one or more excel sheet files. Each file contains an average of 5000 to 25000 phone number stored from top to bottom with no sorting.

We have to do some calculation manually against these files. The total of the entire phone numbers, which phone numbers are duplicated and the total number of times of the duplication for each one.

It's hard for everybody to do the calculation manually and it consumes a lot of time.

What about reading the data directly and programmatically from excel sheet and then saving them into access or dataset and then doing the calculation?

What about LINQ? Moving the data to an array and then doing the calculation using LINQ.

Thanks.

Answer 1

Hi Sami !

I have here some sample code where I read from a EXCEL file and update my ERP-system - it's NOT the best way of ternimating the read, but I think you can get some ideas for your specific project.

1	private void StartOpdatering()
2	{
3	ExcelObj = new Microsoft.Office.Interop.Excel.Application();
4	// See if the Excel Application Object was successfully constructed
5	if (ExcelObj == null)
6	{
7	MessageBox.Show("EXCEL kunne ikke startes !");
8	System.Windows.Forms.Application.Exit();
9	}
10	ExcelObj.Visible = true;
11	Microsoft.Office.Interop.Excel.Workbook KSOWorkbook = ExcelObj.Workbooks.Open(@"F:\ProduktGrp med tilhørende produkter.xls",
12	0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 0, true);
13	Microsoft.Office.Interop.Excel.Sheets sheets = KSOWorkbook.Worksheets;
14	Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("GULE produkter");
15
16	for (int i = 3; i <= 60000; i++)
17	{
18	string ProdNr = worksheet.get_Range("B" + i.ToString(), "B" + i.ToString()).Value2.ToString();
19	if (ProdNr.Trim() != "SLUT")
20	{
21	worksheet.get_Range("A" + i.ToString(), "A" + i.ToString()).Select();
22	string grp = worksheet.get_Range("A" + i.ToString(), "A" + i.ToString()).Value2.ToString();
23	FindProduktOgOpdater(ProdNr, grp);
24	}
25	else
26	{
27	ExcelObj.Quit();
28	MessageBox.Show("Slut");
29	this.Close();
30	break;
31	}
32	}
33	}
34

and here is the "FindProductOfOpdater" method, but I don't think you need it :

1	private void FindProduktOgOpdater(string P, string G)
2	{
3	OpdaterC5DataContext C5db = new OpdaterC5DataContext();
4	KSO_view_ProdukterMedGruppe prod = C5db.KSO_view_ProdukterMedGruppes.Single(p => p.ITEMNUMBER == P);
5	prod.GROUP_ = G;
6	C5db.SubmitChanges();
7	}
8

Answer 2

You could use Jet or ACE OLEDB provider to read Excel files. Here is sample how to read data using Jet

http://support.microsoft.com/kb/306572

You could also use Office Interop as in above example, but it would require Excel application installed on that PC and works much slower than Jet.
You could try to use .NET reader for Excel from my web site that works as .NET provider but with read-only capabilities

 

---

Val Mazur (MVP) http://www.xporttools.net