怎么用.net去操作excel
我看网上说先引用excel的com组件，我在类库里引用了一下
。但是人家说还要Microsoft.Office.Interop.Excel这个，我没有，我在项目里引用了一下，结果变成了
我有2个问题。
1为什么我在类库里没有Microsoft.Office.Interop.Excel
2这两种引用有什么区别，为什么同样是操作excel，有的文章using Excel这样，有的文章using Microsoft.Office.Interop.Excel;这样 excel .net 引用
[解决办法]
用组件，搜NPOI，轻松很多
[解决办法]
using Excel的应该是先导入了Microsoft.Office.Interop吧
再说了 甭管别人怎么写的 自己琢磨琢磨呗
public static bool UpdateData(string Filepath)
{
DataSet ds = new DataSet();
OleDbConnection con;
OleDbDataAdapter da;
try
{
string strExtName = Filepath.Substring(Filepath.Length - 4).ToLower();
string connStr = "";
switch (strExtName)
{
case ".xls":
{
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + Filepath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
break;
}
case "xlsx":
{
connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + Filepath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
}
default:
{
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + Filepath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
break;
}
}
if (GetExcelTableNames(Filepath, connStr).Count <= 0)
{
throw new Exception("提示：上传的文件没数据！");
}
ArrayList sqllist = new ArrayList();
for (int i = 0; i < tableNames.Count; i++)
{
sqllist.Add("SELECT * FROM [" + tableNames[i] + "] ");
}
con = new OleDbConnection(connStr);
con.Open();
for (int i = 0; i < tableNames.Count; i++)
{
da = new OleDbDataAdapter(sqllist[i].ToString(), con);
da.Fill(ds, "mytable");
}
}
catch (Exception ex)
{
throw ex;
}
using (SqlBulkCopy sqlBC = new SqlBulkCopy(connstring, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlBC.BatchSize = 100;
sqlBC.NotifyAfter = 1;
sqlBC.DestinationTableName = "tbBlackCust";
//一下根据实际修改
sqlBC.ColumnMappings.Add("城市", "城市");
sqlBC.ColumnMappings.Add("公司名", "公司名");
sqlBC.ColumnMappings.Add("访问员姓名", "访问员姓名");
sqlBC.ColumnMappings.Add("访问员编号", "访问员编号");
sqlBC.ColumnMappings.Add("性别", "性别");
sqlBC.ColumnMappings.Add("身份证号", "身份证号");
sqlBC.ColumnMappings.Add("地址", "地址");
sqlBC.ColumnMappings.Add("宅电", "宅电");
sqlBC.ColumnMappings.Add("电话", "电话");
sqlBC.ColumnMappings.Add("原因", "原因");
sqlBC.ColumnMappings.Add("日期", "日期");
sqlBC.WriteToServer(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
conn.Close();
return true;
}
public static ArrayList GetExcelTableNames(string excelFileName, string strExtension)
{
tableNames.Clear();
try
{
if (File.Exists(excelFileName))
{
using (OleDbConnection conn = new OleDbConnection(strExtension))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
//sheet的命名规则决定过滤规则
if (dt.Rows[i]["TABLE_NAME"].ToString().Contains("$") && !dt.Rows[i]["TABLE_NAME"].ToString().Replace("'", "").EndsWith("$"))
{
continue;
}
if (GetSheetRows(dt.Rows[i]["TABLE_NAME"].ToString().Trim(), conn) <= 0)
{
continue;
}
tableNames.Add(dt.Rows[i]["TABLE_NAME"].ToString().Trim());
}
conn.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return tableNames;
}
private static int GetSheetRows(string SheetName, OleDbConnection con)
{
string sql = "SELECT count(*) FROM [" + SheetName + "] ";
return (int)new OleDbCommand(sql, con).ExecuteScalar();
}
