一:数据导出EXCEL
1.要有个数据集,也便输出到Excel中:
DataSet ds;
int custId;
try
{
custId = Convert.ToInt32(txtCustId.Text);
}
catch
{
custId = -1;
}
vmc = new VIPManagerClass(connection());
vmc.get_CustBaseinfo(custId, this.textName.Text.Trim(), this.ddlCusttype.SelectedValue, "", this.ddlCustLevel.SelectedValue, "", "", out ds);
if (ds.Tables[0].Rows[0][0].ToString() == "0")
{
this.gvVip.DataSource = ds.Tables[1];
this.gvVip.DataBind();
}
string endExcel = "";
try
{
endExcel =
"<tr><td>用户ID</td><td>用户名</td><td>客户类型</td><td>证件类型</td><td>证件号码</td><td>证件发 放 机构地址</td><td>客户等级</td><td>性别</td><td>出生日期</td><td>省份</td><td>城市</td><td>联 系地址</td><td>传真号码</td><td>电话号码</td><td>移动电话 </td><td>Emial</td><td>QQ</td><td>MSN</td><td>公司名称</td><td>注册时间</td><td>收费开始日期 </td><td>收费结束日期</td><td>VIP开始日期</td><td>VIP结束日期</td><td>备注</td></tr>";
foreach (DataRow dr in ds.Tables[1].Rows)
{
endExcel = endExcel + "<tr><td>" + dr["Custid"].ToString() + " </td><td>" + dr["Custname"].ToString() + "</td><td>"+dr["Custtypename"].ToString()+"</td>" + "<td>" + dr["Idtypename"].ToString() + "</td>" + " <td>" + dr["Idno"].ToString() + "</td>" + "<td>" + dr["Idaddr"].ToString() + "</td>" + " <td>" + dr["CustLevelname"].ToString() + "</td>" + " <td>" + dr["sexname"].ToString() + "</td>" + " <td>" + dr["Birthday"].ToString() + "</td>" + " <td>" + dr["ProvinceName"].ToString() + "</td>" + "<td>" + dr["CityName"].ToString() + "</td>" + "<td>" + dr["Addr"].ToString() + "</td>" + " <td>" + dr["Faxno"].ToString() + "</td>" + "<td>" + dr["Telno"].ToString() + "</td>" + " <td>" + dr["Mobileno"].ToString() + "</td>" + "<td>" + dr["Email"].ToString() + "</td>" + " <td>" + dr["Qq"].ToString() + "</td>" + "<td>" + dr["Msn"].ToString() + "</td>" + "<td>" + dr["Company"].ToString() + "</td>" + "<td>" + dr["Regeditdatetime"].ToString() + "</td>" + "<td>" + dr["FeeStartdate"].ToString() + "</td>" + "<td>" + dr["FeeEnddate"].ToString() + "</td>" + "</td>" + "<td>" + dr["VipStartdate"].ToString() + "</td>" + "<td>" + dr["VipEnddate"].ToString() + "</td>" + "<td>" + dr["Remark"].ToString() +"<td></tr>";
}
tryToExcel(this.gvVip, "UserDetai.xls", "<div><table>" + endExcel + "</table></div>");
}
catch (Exception e1)
{
alert(e1.Message, "alert");
}
//写入到Excel中的方法
public static void tryToExcel(System.Web.UI.Control ctl, string file_name, string tryexcel)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "gb2312";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + file_name);
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
HttpContext.Current.Response.ContentType = "application/ms-excel";
ctl.Page.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("zh-CN", true);
System.IO.StringWriter tw = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
ctl.RenderControl(hw);
Console.WriteLine(tw.ToString());
HttpContext.Current.Response.Write(tryexcel);
HttpContext.Current.Response.End();
}
3.最后加上
public override void VerifyRenderingInServerForm(Control control)
{
}
和EnableEventValidation="false"
二:Excel导入到数据库中
vmc = new VIPManagerClass(connection());
DataSet ds1;
vmc.delete_AllCustVisitinfo(out ds1);
if (ds1.Tables[0].Rows[0][0].ToString() != "0")
{
return;
}
string fileType = string.Empty;
fileType = FileUpload1.FileName.Substring(FileUpload1.FileName.IndexOf(".") + 1);
if (fileType.Trim().ToLower() != "xls")
{
alert("你导入的文件不是Excel文件!", "alert");
return;
}
string fileName = Server.MapPath(FileUpload1.FileName);
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1);
DataTable dt = objDataset1.Tables[0];
int count = 0;//用来记录出错的条数
DataSet ds;
try
{
foreach (DataRow myDrv in dt.Rows)
{
count++;
vmc.insert_CustVisitinfo(myDrv[0].ToString(),"", "","","","", Convert.ToString(myDrv[1]), "",Convert.ToString(myDrv[2]),Convert.ToString(myDrv[3]),Convert.ToString(myDrv[4]), Convert.ToString(myDrv[5]), Convert.ToString(myDrv[6]), Convert.ToString(myDrv[7]), Convert.ToString(myDrv[8]), Convert.ToString(myDrv[9]), Convert.ToString(myDrv[10]), Convert.ToString(myDrv[11]), Convert.ToString(myDrv[12]), Convert.ToString(myDrv[13]), Convert.ToString(myDrv[14]),Convert.ToString(myDrv[15]), out ds);
}
}
catch
{
alert("第" + count.ToString() + "条数据导入出错", "alert");
objConn.Close();//关闭EXCEL的连接
}
alert("数据导入成功!", "alert");