第一种:C#读取excel数据并返回datatable
1.写一个返回datatable的excel类
public static DataTable ExcelImport(string strFileName) { DataTable dt = new DataTable(); ISheet sheet = null; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { if (strFileName.IndexOf(".xlsx") == -1)//2003 { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); sheet = hssfworkbook.GetSheetAt(0); } else//2007 { XSSFWorkbook xssfworkbook = new XSSFWorkbook(file); sheet = xssfworkbook.GetSheetAt(0); } } System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } return dt; }
2.添加NPOI的引用。可以直接在NuGet包管理器中搜索下载
3.在button的双击事件中传入地址即可获取到excel表中的全部数据。
private void readData_Click(object sender, EventArgs e) { var dt = ExcelImport("ProductData.xlsx"); }
注意事项:
1.报错“未能加载文件或程序集 ICSharpCode.SharpZipLib” 可能是因为下载的NPOI与项目版本不匹配导致,可项目右键“属性-应用程序”修改目标框架即可。
2.excel名称不能使用中文可以使用英文或数字,最好放在bin目录下 ,否则也可能导致生成失败。
-------------------------------------------------------------------------------------------------------------------------
方法二:C#读取excel数据并显示在datagrilview上
1.先来一个读取Excel表内容返回dataset的方法。
public DataSet getData() { //打开文件 OpenFileDialog file = new OpenFileDialog(); file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls"; file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); file.Multiselect = false; if (file.ShowDialog() == DialogResult.Cancel) return null; //判断文件后缀 var path = file.FileName; string fileSuffix = System.IO.Path.GetExtension(path); if (string.IsNullOrEmpty(fileSuffix)) return null; using (DataSet ds = new DataSet()) { //判断Excel文件是2003版本还是2007版本 string connString = ""; if (fileSuffix == ".xls") connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; else connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; //读取文件 string sql_select = " SELECT * FROM [Sheet1$]"; using (OleDbConnection conn = new OleDbConnection(connString)) using (OleDbDataAdapter cmd = new OleDbDataAdapter(sql_select, conn)) { conn.Open(); cmd.Fill(ds); } if (ds == null || ds.Tables.Count <= 0) return null; return ds; } }
2.写入button事件方法
dataGridView1.DataSource = null; //每次打开清空内容 DataTable dt = getData().Tables[0]; dataGridView1.DataSource = dt;
3.效果如图,内容显示在datagridview中