Display excel file content in a Datagridview

Question

private void button1_Click(object sender, EventArgs e) { DataTable test = getDataFromXLS("c:\temp.xls"); if (test != null) dataGridView1.DataSource = test; } private DataTable getDataFromXLS(string strFilePath) { try { string strConnectionString = ""; strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFilePath + "; Jet OLEDB:Engine Type=5;" + "Extended Properties=Excel 8.0;"; OleDbConnection cnCSV = new OleDbConnection(strConnectionString); cnCSV.Open(); OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [Sheet1$]", cnCSV); OleDbDataAdapter daCSV = new OleDbDataAdapter(); daCSV.SelectCommand = cmdSelect; DataTable dtCSV = new DataTable(); daCSV.Fill(dtCSV); cnCSV.Close(); daCSV = null; return dtCSV; } catch (Exception ex) { return null; } finally { } }

I found that source code to bind an excel file to a DataGridView object on a winform application on the Internet. Yet, I would like to know if there are other ways to do this instead of using ADO- or any SQL-related procedures, thanks for any help.

minalg · Accepted Answer · 2011-09-06 07:06:05Z

public void CreateDataTableForExcelData(String FileName) { OleDbConnection ExcelConnection = null; string filePath = Server.MapPath(Request.ApplicationPath + "/UploadedFile/"); DataTable dtNew = new DataTable(); string strExt = ""; strExt = FileName.Substring(FileName.LastIndexOf(".")); if (strExt == ".xls") { ExcelConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + hdnFileName.Value + ";Extended Properties=Excel 8.0;"); } else { if (strExt == ".xlsx") { ExcelConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + hdnFileName.Value + ";Extended Properties=Excel 12.0;"); } } try { ExcelConnection.Open(); DataTable dt = ExcelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + ddlTableName.SelectedValue + @"]", ExcelConnection); OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); DataSet ExcelDataSet = new DataSet(); ExcelAdapter.Fill(dtExcel); ExcelConnection.Close(); } catch (Exception ex) { } finally { } }

pabdulin · Accepted Answer · 2011-09-06 06:47:35Z

You can use Excel COM Interop directly of thru some already existing wrappers to read files using Excel itself. However I find you current approach quite reasonable.

thekip · Accepted Answer · 2011-09-06 06:48:15Z

The code you're showing binds an ordinary DataTable to the excel file. This means you can build your DataTable in any way you want and bind it to the excel file. There's no dependency or whatsoever on ADO or procedures.

Or maybe I just don't understand your question.

KV Prajapati · Accepted Answer · 2011-09-06 06:48:40Z

0

Use Ms-Office InterOp API.

answered Sep 6, 2011 at 6:48

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Comments

0x49D1 · Accepted Answer · 2011-09-06 06:50:07Z

You can use this(dont forget to reference Microsoft.Office.Interop.Excel. There may be some extra code, because i took it from my tool, but overall idea is to use Excel COM):

public ArrayList ProcessWorkbook(string filePath) { string file = filePath; Excel.Application excel = null; Excel.Workbook wkb = null; ArrayList al = new ArrayList(); try { excel = new Excel.Application(); wkb = ExcelTools.OpenBook(excel, file, false, true, false); Excel.Worksheet sheet = wkb.Sheets["Adresses"] as Excel.Worksheet; Excel.Range range = null; if (sheet != null) range = sheet.get_Range("A1:X6702", Missing.Value); if (range != null) { foreach (Excel.Range r in range) { al.Add(r.Text); } } } catch (Exception ex) { //if you need to handle stuff Console.WriteLine(ex.Message); } finally { if (wkb != null) ExcelTools.ReleaseRCM(wkb); if (excel != null) ExcelTools.ReleaseRCM(excel); } return al; } //---------------- public static class ExcelTools { public static Excel.Workbook OpenBook(Excel.Application excelInstance, string fileName, bool readOnly, bool editable, bool updateLinks) { Excel.Workbook book = excelInstance.Workbooks.Open( fileName, updateLinks, readOnly, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return book; } public static void ReleaseRCM(object o) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(o); } catch { } finally { o = null; } } }

Collectives™ on Stack Overflow

Display excel file content in a Datagridview

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Related