什么都不说，直接贴.cs中的代码
//错误信息
public static string errormessage = "";
public List<usertable> lstut = new List<usertable>();
protected void Page_Load(object sender, EventArgs e)
{
test_dbEntities db = new test_dbEntities();
lstut = db.usertable.ToList();
}
//导入
protected void btnin_Click(object sender, EventArgs e)
{
//上传文件在项目中的文件夹名称
string excelpath = "/fileupload/";
//上传文件
string filepath = uploadexcel(excelpath);
//读取指定文件的数据
DataSet ds = ReadExcel(filepath);
#region 定义变量值
//全局验证，错误不保存信息
bool checkall = true;
//错误行
string errorall = "";
//错误数量
int counterror = 0;
//第几条数据
int countall = 0;
//成功导入数据的数量
int successnumber = 0;
#endregion
#region 定义要导入的数据表的字段
string User = "";
string Pass = "";
#endregion
test_dbEntities db = new test_dbEntities();
foreach (DataRow dr in ds.Tables[0].Rows)
{
countall++;
User = dr["用户名"].ToString();
Pass = dr["密码"].ToString();
if (checkall)
{
string sql = "insert into usertable ([User],Pass) values('"+User+"','"+Pass+"')";
int i = db.ExecuteStoreCommand(sql);
if (i > 0)
{
successnumber += i;
}
else
{
if (errorall == "")
{
errorall += countall;
}
else
{
errorall += "、"+countall;
}
counterror++;
}
}
}
if (counterror == 0)
{
Response.Write(successnumber + "条数据全部导入成功,没有出现错误。");
lstut = db.usertable.ToList();
}
else
{
Response.Write("成功导入" + successnumber + "条数据\\n错误条数："+counterror+",错误行：" + errorall + "\\n请记录错误的信息修改，将错误值修改后重新导入");
}
}
/// <summary>
/// 上传文件到指定目录
/// <param name="excelpath">项目中的文件名称</param>
/// </summary>
/// <returns></returns>
public string uploadexcel(string excelpath)
{
string filepath = string.Empty;
//文档是否存在
if (filein.HasFile)
{
//ToLower转化为小写
string fileExrensio = System.IO.Path.GetExtension(filein.FileName).ToLower();
//上传的项目绝对目录
string UploadURL = Server.MapPath("~" + excelpath);
string UploadURL1 = excelpath;
//判断文件类型
if (fileExrensio == ".xls" || fileExrensio == ".xlsx")
{
try
{
//判断文件夹是否已经存在
if (!System.IO.Directory.Exists(UploadURL))
{
//创建文件夹
System.IO.Directory.CreateDirectory(UploadURL);
}
//取当前时间作为上传文件的文件名
string filename = DateTime.Now.ToString("yyyyMMddHHmm");
//给文件加上后缀名
filename += fileExrensio;
//将fileupload中的文件上传到指定目录
filein.PostedFile.SaveAs(UploadURL + filename);
//完整的上传后的文件的绝对路径
filepath = UploadURL + filename;
//判断上传的文件路径是否存在
if (filepath != "")
{
return filepath;
}
else
{
return "上传文件路径不存在！";
}
}
catch(Exception ex)
{
return "导入失败："+ex.Message;
}
}
else
{
return "需要上传格式为xls和xlsx";
}
}
else
{
return "请选择上传文件";
}
}
/// <summary>
/// 读取Excel数据到DataSet
/// </summary>
/// <param name="strFileName">带路径名称</param>
/// <returns></returns>
private static DataSet ReadExcel(string filepath)
{
DataSet ds;
OleDbConnection con;
//string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;Data Source='{0}';Extended Properties='Excel 8.0; HDR=yes; IMEX=1';";
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties='Excel 8.0; HDR=yes; IMEX=1';";
//备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
// "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。
// "strConn = "Provider=Microsoft.Jet.OLEDB.4.0(Excel2007版及以下);Provider=Microsoft.ACE.OLEDB.12.0（）。
strConnection = string.Format(strConnection, filepath);
con = new OleDbConnection(strConnection);
try
{
con.Open();
DataTable schema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//下面取得第一个表名
string strTableName = schema.Rows[0]["TABLE_NAME"].ToString();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + strTableName + "]", con);
ds = new DataSet();
da.Fill(ds);
da.Dispose();
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnout_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("用户名", System.Type.GetType("System.String"));
dt.Columns.Add("密码", System.Type.GetType("System.String"));
int i = -1;
foreach (var item in lstut)
{
i++;
dt.Rows.Add();
int ss = dt.Rows.Count;
dt.Rows[i]["用户名"] = item.User;
dt.Rows[i]["密码"] = item.Pass;
}
DataTable3Excel(dt, "用户信息");
}
/// <summary>
/// 导出Excel文件，并自定义文件名
/// </summary>
/// <param name="dtData">数据源</param>
/// <param name="FileName">导出文件名称</param>
/// <param name="numberformat">需要处理科学计数法的列的下标</param>
public static void DataTable3Excel(DataTable dtData, String FileName,string numberformat)
{
GridView dgExport = null;
HttpContext curContext = HttpContext.Current;
StringWriter strWriter = null;
HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
curContext.Response.Clear();
HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
curContext.Response.AddHeader("content-disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
curContext.Response.ContentType = "application/ms-excel";
curContext.Response.Charset = "GB2312";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
strWriter = new StringWriter();
htmlWriter = new HtmlTextWriter(strWriter);
dgExport = new GridView();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
//判断是否存在需要处理科学计数法的列，存在则进行相对应的列的处理，处理为“以文本形式存储的数字”
if(numberformat!="")
{
string[] arraystr = numberformat.Split(',');
foreach (GridViewRow dg in dgExport.Rows)
{
for (int i = 0; i < arraystr.Length; i++)
{
dg.Cells[Convert.ToInt32(arraystr[i])].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
}
}
}
dgExport.RenderControl(htmlWriter);
curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
curContext.Response.End();
}
}
C# 导出excel 各种方法总结https://www.cnblogs.com/Brambling/p/6854731.html
