c#导入导出代码

#region 导入
public string UploadFile(FileUpload Fupload)
{
//文件上传
string str_ParentFolder;
string filename;

str_ParentFolder = Server.MapPath(@"..\app_data\");
/*判断是否更名*/
filename = "zhuanjia.xls";
try
{
//上传新的对应关系文件
if (Fupload.PostedFile.FileName == "")
return "请选择要上传的数据！";
//创建文件夹
if (!Directory.Exists(str_ParentFolder))
{
Directory.CreateDirectory(str_ParentFolder);
if (!Directory.Exists(str_ParentFolder))
return "创建文件夹失败！";
}
if (Fupload.PostedFile.FileName != "")
{
string extname = Fupload.FileName.Substring(https://www.360docs.net/doc/8713816744.html,stIndexOf(".") + 1).ToUpper();
//判断上传类型
string str_UploadFileType = "xls";

if (str_UploadFileType.IndexOf(extname.ToLower()) == -1)
{
return "";
}
Fupload.PostedFile.SaveAs(str_ParentFolder + filename);
}
string str_sql;
str_sql = "select * from [sheet1$] ";
DataTable dt = ExcelManager.GetXlsDataTable(str_ParentFolder + filename, str_sql);
int j = 0, k = 0;
string str_pwd;
for (int i = 0; i < dt.Rows.Count; i++)
{
str_sql = "select count(*) from t_Expert where LoginName ='" + dt.Rows[i][0].ToString() + "'";
str_pwd = dt.Rows[i][2].ToString();
//如果密码为空，则设置登录名为密码
if (str_pwd == "")
str_pwd = dt.Rows[i][0].ToString();

str_pwd = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str_pwd, "MD5");
if ((int)DBFun.ExecuteScalar(str_sql) == 0)
{
str_sql = string.Format("insert into t_Expert (LoginName,UserName,pwd,szbm,zc,jb,zy) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString(), str_pwd, dt.Rows[i][3].ToString(), dt.Rows[i][4].ToString(), dt.Rows[i][5].ToString(), dt.Rows[i][6].ToString());
j++;
}
else
{
str_sql = string.Format("update t_Expert set UserName='{1}',pwd='{2}',szbm='{3}',zc='{4}',jb='{5}',zy='{6}' where LoginName='{0}'",
dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString(), str_pwd, dt.Rows[i][3].ToString(), dt.Rows[i][4].ToString(), dt.Rows[i][5].ToString(), dt.Rows[i][6].ToString());
k++;
}

if (!DBFun.ExecuteUpdate(str_sql))
{
return "系统错误";
}
}
str_sql = "select count(*) from t_Expert where LoginName is not null";
lbl_result.Visible = true;
lbl_result.Text = "数据库中共有记录 " + DBFun.ExecuteScalar(str_sql).ToString() + " 条，本次新增了 " + j.ToString() + " 条记录，更新了 " + k.ToString() + " 条记录";
Fupload.Dispose();
//if (lbl_type.Text == "2")
//{
// str_sql = " delete from zjry where flag = 2;";
// DBFun.ExecuteUpdate(str_sql);

// str_sql = " insert into zjry (flag,zj_sfzh,cpry_sfzh,fs_sftj) " +
// " select 2,pszj.sfzh,ej_cpry.sfzh,'false' from pszj,ej_cpry where pszj.flag = 2;";
// if (!DBFun.ExecuteUpdate(str_sql))
// {
// return "上传失败";
// }
//}
return "上传成功";
}
catch(IOException e)
{
return e.Message;
}
catch (Exception e)
{
return e.Message;
}
}
#endregion

private DataTable GetXlsDataTable(string filename, string comText)
{
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0;");
try
{
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(comText, con);
DataTable table = new DataTable();
adapter.Fill(table);
return table;
}

catch (Exception ex)
{
string exceptionMessage = ex.Message;
return null;
}
finally
{
con.Close();
}
}



#region 导出到excel
protected void btn_exp2Excel_Click(object sender, EventArgs e)
{
str_sql = ViewState["sql"].ToString();
DataTable dt = DBFun.dataTable(str_sql);
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString() + ".xls");
int i_colnum = dt.Columns.Count;
//定义表对象与行对像，同时用DataSet对其值进行初始化
string str_colHeaders = "防灾科技学院教学研究与教学改革项目立项评审结果\n";
str_colHeaders += "序号\t申请人\t评委1\t评委2\t评委3\t评委4\t评委5\t评委6\t评委7\t总分\t平均分\t排名\t同意立项票数\t备注\n";

for (int i = 0; i < dt.Rows.Count; i++)
{
str_colHeaders += Convert.ToString(i + 1) + "\t";
str_colHeaders += dt.Rows[i]["sqr"].ToString() + "\t"

;
str_colHeaders += dt.Rows[i]["jtpf1"].ToString() + "\t";
str_colHeaders += dt.Rows[i]["jtpf2"].ToString() + "\t";
str_colHeaders += dt.Rows[i]["jtpf3"].ToString() + "\t";
str_colHeaders += dt.Rows[i]["jtpf4"].ToString() + "\t";
str_colHeaders += dt.Rows[i]["jtpf5"].ToString() + "\t";
str_colHeaders += dt.Rows[i]["jtpf6"].ToString() + "\t";
str_colHeaders += dt.Rows[i]["jtpf7"].ToString() + "\t";
str_colHeaders += dt.Rows[i]["jtzf"].ToString() + "\t";
str_colHeaders += dt.Rows[i]["jtpjf"].ToString() + "\t";
str_colHeaders += Convert.ToString(i + 1) + "\t";
str_colHeaders += dt.Rows[i]["jtps"].ToString() + "\n";
}
resp.Write(str_colHeaders);
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
#endregion