>using System; >using System.Data; >using System.Net.Mime; >using Microsoft.Office.Interop.Excel; >using System.Text; >using System.Reflection; > >namespace ConsoleApplication3 >{ > public class ExcelWrapper > { > public DataSet GetExcel(string fileName) > { > // > // Include a reference to Microsoft.Office.Interop.Excel > // ExcelWrapper wrap = new ExcelWrapper(); > // DataSet ds = wrap.GetExcel(@"c:\development\rda.xls"); > // > // I just ran this against an old spreadsheet an it worked fine. > // > > Application oXL; > Workbook oWB; > Worksheet oSheet; > Range oRng; > try > { > // creat a Application object > oXL = new ApplicationClass(); > // get WorkBook object > oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, > Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, > Missing.Value, Missing.Value); > > // get WorkSheet object > oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1]; > System.Data.DataTable dt = new System.Data.DataTable("dtExcel"); > DataSet ds = new DataSet(); > ds.Tables.Add(dt); > DataRow dr; > > StringBuilder sb = new StringBuilder(); > int jValue = oSheet.UsedRange.Cells.Columns.Count; > int iValue = oSheet.UsedRange.Cells.Rows.Count; > // get data columns > for (int j = 1; j <= jValue; j++) > { > dt.Columns.Add("column" + j, System.Type.GetType("System.String")); > } > > //string colString = sb.ToString().Trim(); > //string[] colArray = colString.Split(':'); > > // get data in cell > for (int i = 1; i <= iValue; i++) > { > dr = ds.Tables["dtExcel"].NewRow(); > for (int j = 1; j <= jValue; j++) > { > oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j]; > string strValue = oRng.Text.ToString(); > dr["column" + j] = strValue; > } > ds.Tables["dtExcel"].Rows.Add(dr); > } > return ds; > } > catch (Exception ex) > { > throw; > } > } > } >} >