1.首先配置web.config 文件
2.编写个公用函数封装字符串调用，分Excel读和写两种不同的字符串调用连接
返回Excel的数据源连接字符串[写]：GetExcelOutPutConString(filepath);
返回Excel的数据源连接字符串[读]：GetExcelInPutConString(filepath);
其主要区别在于IMEX参数，在我们采用OleDB方式进行Excel读写的时候一定要注意IMEX 和HDR参数的区别，详细如下
用OLEDB进行Excel文件数据的读取，并返回DataSet数据集。其中有几点需要注意的：
1.连接字符串中参数IMEX 的值：
当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
当IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
但我们需奥特别注意：当IMEX=2 时，将EXCEL数据导出的时候如果同一列数据中既有文字，又有数字！读取时一列中要么文字丢失只剩下数字，要么数字丢失，只剩下文字
例如第C列有3个值，2个为数值型 123，1个为字符型 ABC，当导入时，
页面不报错了，但库里只显示数值型的123，而字符型的ABC则呈现为空值。当IMEX=1时，无上述情况发生，库里可正确呈现 123 和 ABC.
2.参数HDR的值：
HDR=Yes，这代表第一行是标题，不做为数据使用，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES
3.参数Excel 8.0
对于Excel 97用啥，我没有实现过；对于Excel 2003版本用Excel 8.0，对于Excel 2007及以上版本都用Excel 12.0
3.读ExcelExcel的某个Sheet数据到导入DataTable中
#region 返回Excel的数据源连接字符串[导入模式]
/// <summary>
/// 返回Excel的数据源连接字符串
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static string GetExcelInPutConString(string filePath)
{
string conn = string.Empty;
if (filePath.Contains(".xlsx"))
{
conn = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";";
}
else
{
conn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;;HDR=YES;IMEX=1\"";
}
return conn;
}
#endregion
#region 返回Excel的数据源连接字符串[导出模式]
/// <summary>
/// 返回Excel的数据源连接字符串[导出模式]
/// </summary>
/// <param name="filePath">导出文件名</param>
/// <returns></returns>
public static string GetExcelOutputConString(string filePath)
{
string conn = string.Empty;
if (filePath.Contains(".xlsx"))
{
conn = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0\";";
}
else
{
conn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;;HDR=YES;IMEX=0\"";
}
return conn;
}
#endregion
/// <summary>
/// Excel数据到导入DataTable中
/// </summary>
/// <param name="filepath">文件路径</param>
/// <returns></returns>
protected DataTable CallExcel(string filepath)
{
string conn = GetExcelInPutConString (filepath);
OleDbConnection con = new OleDbConnection(conn);
con.Open();
DataTable table = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string tableName = table.Rows[0][2].ToString().Trim();
string sql = "Select * From [" + tableName + "]";//选择第一个数据SHEET
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
adapter.Fill(ds);
dt = ds.Tables[0];
con.Close();
con.Dispose();
return dt;
}
public static void ExportDataTableToExcel(DataTable dt, string fileName, bool showNum)
{
string tempFile = string.Empty;
try
{
string souceFileName = System.Web.HttpContext.Current.Server.MapPath("~/ExcelTemplate/RestockingTemp.xls");
string destFileName = System.Web.HttpContext.Current.Server.MapPath("~/TempExcel/" + Guid.NewGuid().ToString() + ".xls");
tempFile = destFileName;
File.Copy(souceFileName, destFileName);
string strConn = GetExcelConString(destFileName);
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
int i = 1;
foreach (DataRow dr in dt.Rows)
{
try
{
cmd.CommandText = "INSERT INTO [Sheet1$] VALUES('" +
dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() +
"','" + dr[3].ToString() + "')";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
cmd.CommandText = "INSERT INTO [Sheet1$] VALUES('" +
dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() +
"','" + dr[3].ToString() +"')";
cmd.ExecuteNonQuery();
OpLogService.MakeLogError(ex.Message, "物料号：" + dr[2].ToString(), "工位补货量维护 WEB.PAGE");
}
i++;
}
conn.Close();
FileStream fs = new FileStream(destFileName, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
byte[] content = new byte[fs.Length];
fs.Read(content, 0, content.Length);
fs.Close();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "GB2312";
//HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + fileName + ".xls");
HttpContext.Current.Response.AddHeader("Content-Length", content.Length.ToString());
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Filter.Close();
HttpContext.Current.Response.OutputStream.Write(content, 0, content.Length);
HttpContext.Current.Response.OutputStream.Flush();
HttpContext.Current.Response.End();
DeleteFile(destFileName);
}
catch (System.Data.OleDb.OleDbException ex)
{
OpLogService.MakeLogError(ex.Message, "Excel", "WEB.PAGE");
}
}
#region
/// <summary>
/// 导出数据 added by ldb on 20110826 导出Excel无乱码
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="outFileName">导出文件名</param>
/// <param name="header">表头键值对<字段,类型></param>
public static void ExportDataTableToExcel(DataTable dt, string outFileName, System.Collections.ArrayList header)
{
string tempFile = string.Empty;
try
{
//string souceFileName = System.Web.HttpContext.Current.Server.MapPath("~/ExcelTemplate/" + templateName + ".xls");
string destFileName = System.Web.HttpContext.Current.Server.MapPath("~/TempExcel/" + Guid.NewGuid().ToString() + ".xls");
tempFile = destFileName;
//FileStream fs = File.Create(destFileName, 10000, FileOptions.Asynchronous);
//fs.Close();
int columnNum = dt.Columns.Count;
string strConn = GetExcelOutputConString(destFileName);
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
try
{
StringBuilder createStr = new StringBuilder();
createStr.Append(@" CREATE TABLE [Sheet1](");
foreach (object key in header.ToArray())
{
KeyValuePair<string, string> kvp = (KeyValuePair<string, string>)key;
createStr.Append("[" + kvp.Key + "] " + kvp.Value + ",");
}
/*for (int j = 0; j < columnNum; j++)
{
createStr += "["+dt.Columns[j].ColumnName + "] VARCHAR,";
}*/
createStr = createStr.Remove(createStr.Length - 1, 1);
createStr.Append(")");
cmd.CommandText = createStr.ToString();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
OpLogService.MakeLogError(ex.Message, "创建Excel失败!", "ExportDataTableToExcel");
return;
}
int i = 1;
foreach (DataRow dr in dt.Rows)
{
try
{
//字段项
StringBuilder filedString = new StringBuilder();
filedString.Append("(");
//value项
StringBuilder valueString = new StringBuilder();
valueString.Append("(");
for (int k = 0; k < header.Count; k++)
{
KeyValuePair<string, string> kvp = (KeyValuePair<string, string>)header[k];
filedString.Append("[" + kvp.Key + "]");
//若为字符串则添加‘’
if (kvp.Value == "NVARCHAR")
{
valueString.Append("'");
valueString.Append(dr[k].ToString());
valueString.Append("'");
}
else//若为数字
{
valueString.Append(string.IsNullOrEmpty(dr[k].ToString()) ? "NULL" : dr[k].ToString());
}
if (k != header.Count - 1)
{
valueString.Append(",");
filedString.Append(",");
}
}
filedString.Append(")");
valueString.Append(")");
cmd.CommandText = "INSERT INTO [Sheet1$]" + filedString + " VALUES " + valueString.ToString();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
OpLogService.MakeLogError(ex.Message, "第一列：" + dr[0].ToString());
}
i++;
}
conn.Close();
FileStream fs = new FileStream(destFileName, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
byte[] content = new byte[fs.Length];
fs.Read(content, 0, content.Length);
fs.Close();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "GB2312";
//HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + outFileName + ".xls");
HttpContext.Current.Response.AddHeader("Content-Length", content.Length.ToString());
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Filter.Close();
HttpContext.Current.Response.OutputStream.Write(content, 0, content.Length);
HttpContext.Current.Response.OutputStream.Flush();
HttpContext.Current.Response.End();
DeleteFile(destFileName);
}
catch (System.Data.OleDb.OleDbException ex)
{
//log error
}
}
#endregion
