EXCEL Operation Techninque

EXCEL Operation in .NET
This post is regarding how we can read and write EXCEL based on our requirement. I have worked for many applications and each application requirement is different so based on my experience I have written this blog. May be it will save your time.

Below are the three techniques by which we can automate Excel
1. OLE DB Provider
2. COM Component
3. Open XML

Below is comparison for this three





Below are the examples for it
1. OLE DB
private string ExcelConnection()
{
return
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=" + _strExcelFilename + ";" +
@"Extended Properties=" + Convert.ToChar(34).ToString() +
@"Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString();
}
_oleConn = new OleDbConnection(ExcelConnection());
_oleConn.Open();
_oleCmdSelect =new OleDbCommand(
@"SELECT * FROM ["
+ _strSheetName
+ "$" + _strSheetRange
+ "]", _oleConn);

OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = _oleCmdSelect;
DataTable dt = new DataTable(strTableName);
oleAdapter.FillSchema(dt,SchemaType.Source);
oleAdapter.Fill(dt);

2. COM Component by adding reference of Excel component
3. Private Sub TestWorksheetFunction()
4. Dim ws As Excel.Worksheet = _
5. DirectCast(ThisWorkbook.ActiveSheet, Excel.Worksheet)
6. Dim rng As Excel.Range = ws.Range("RandomNumbers")
7. Dim rnd As New System.Random
8.
9. Dim i As Integer
10. For i = 1 To 20
11. ws.Cells(i, 2) = rnd.Next(100)
12. Next i
13. rng.Sort(rng, _
14. Orientation:=Excel.XlSortOrientation.xlSortColumns)
15.
16. With ThisApplication.WorksheetFunction
17. ws.Range("Min").Value = .Min(rng)
18. ws.Range("Max").Value = .Max(rng)
19. ws.Range("Median").Value = .Median(rng)
20. ws.Range("Average").Value = .Average(rng)
21. ws.Range("StDev").Value = .StDev(rng)
22. End With
23. End Sub
24.

3. OPEN XML

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
private void ReadUserInputExcel(string filename)
{

SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(filename, true);
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
}