protected void Page_Load(object sender, EventArgs e)
{
string savepath = @"E:\govweb\fzb\全市领取浙江省行政执法证的行政执法人员名册.xls";
string SheetName = "全市领取《浙江省行政执法证》的行政执法人员名册";
DataTable dtExcel = ExcelToDataTable(savepath, SheetName);
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
InsertDataToAccess(dtExcel.Rows[i][0].ToString(), dtExcel.Rows[i][1].ToString(), dtExcel.Rows[i][2].ToString(), dtExcel.Rows[i][3].ToString(), dtExcel.Rows[i][4].ToString(), dtExcel.Rows[i][5].ToString(), dtExcel.Rows[i][6].ToString(), dtExcel.Rows[i][7].ToString(), dtExcel.Rows[i][8].ToString(), dtExcel.Rows[i][9].ToString(), dtExcel.Rows[i][10].ToString(), dtExcel.Rows[i][11].ToString());
}
} public DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelFileName + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet();
try
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds);
conn.Close();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
return ds.Tables[strSheetName];
}错误提示:for (int i = 0; i < dtExcel.Rows.Count; i++) 未将对象引用设置到对象的实例。很早以前学过一点,,现在又要重新拿起来都不懂了。。求教
首先excel的格式必须为标准的行列对应格式/// <summary>
/// 获取导入的excel数据
/// </summary>
/// <param name="excelPath">excel路径</param>
/// <returns>如果excel包含多個worksheet將返回多張表</returns>
public DataSet GetImportExcelData(string excelPath){
using(System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';",excelPath))){
try{
conn.Open();
DataTable SchemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
string tableName = string.Empty;
DataSet ds = new DataSet();
foreach(DataRow row in SchemaTable.Rows){
tableName = string.Format("select * from [{0}] ", row[2].ToString());
using(System.Data.OleDb.OleDbDataAdapter dapter = new System.Data.OleDb.OleDbDataAdapter(tableName,conn)){
System.Data.DataTable dt = new System.Data.DataTable();
dapter.Fill(dt);
ds.Tables.Add(dt);
}
}
return ds;
}
catch(Exception ex){throw ex;}
}
}
SheetName是正确的。。
我是复制粘贴过来了。。
...
adapter.Fill(ds);
...
return ds.Tables[strSheetName];
改为DataTable dt = new DataTable();
...
adapter.Fill(dt);
...
return dt;
using(OleDbConnection OleConn = new OleDbConnection(strConn))
{
OleConn.Open();
String sql = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet ds= new DataSet();
OleDaExcel.Fill(ds);
OleConn.Close();
}if(dtExcel !=null &&dtExcel .Rows.Count>0){}
return ds;