ASP.NET读写Excel文件(转自博客园某博主)

最新推荐文章于 2021-09-22 16:23:17 发布

王尼瑪

最新推荐文章于 2021-09-22 16:23:17 发布

阅读量950

点赞数

文章标签： excel asp.net properties string null table

本文链接：https://blog.csdn.net/wayne20018891/article/details/6240281

版权

1.读取Excel文件的数据连接字符串。读取.xls格式文件的Excel文件，可设置连接字符串为：Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/MyExcel.xls;Extended Properties=Excel 8.0;。如果要读取.xlsx和.xls格式文件的Excel文件，则需要将连接字符设置为："Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:/MyExcel.xls;Extended Properties=Excel 12.0。

2.读取Excel表格Sheet的名称。

public static string[] GetExcelSheetNames(string filePath) { string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=/"Excel 12.0;HDR=YES/""; OleDbConnection con = new OleDbConnection(conString); con.Open(); DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); con.Close(); if (dt == null) { return null; } string[] excelSheetNames = new string[dt.Rows.Count]; int i = 0; foreach (DataRow dr in dt.Rows) { excelSheetNames[i++] = dr["TABLE_NAME"].ToString(); } return excelSheetNames; }

3.读取Excel文件

public static DataTable ReadExcel(string filePath, string conStr) { string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=/"Excel 12.0;HDR=YES/""; OleDbConnection con = new OleDbConnection(conString); OleDbDataAdapter oda = new OleDbDataAdapter(conStr, con); DataTable dt = new DataTable(); con.Open(); oda.Fill(dt); con.Close(); return dt; }

4.将数据写入Excel文件。

public static void WriteExcel(string filePath, DataTable dt) { if (File.Exists(filePath)) { File.Delete(filePath); } else { string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=/"Excel 12.0;HDR=YES/""; OleDbConnection con = new OleDbConnection(conString); string createSql = "create table Sheet1 ("; foreach (DataColumn dc in dt.Columns) { createSql += dc.ColumnName + " varchar,"; } createSql = createSql.Substring(0, createSql.Length - 1) + ")"; OleDbCommand cmd = new OleDbCommand(createSql, con); con.Open(); cmd.ExecuteNonQuery(); foreach (DataRow dr in dt.Rows) { string insertSql = "insert into Sheet1 values("; foreach (DataColumn dc in dt.Columns) { insertSql += "'" + dr[dc].ToString() + "',"; } insertSql = insertSql.Substring(0, insertSql.Length - 1) + ")"; cmd = new OleDbCommand(insertSql, con); cmd.ExecuteNonQuery(); } con.Close(); } }