/// <summary>
/// 导出Excel
/// </summary>
/// 创建时间：2012/07/01
//private void fExpert(DataTable dt)
private void fExpert(IList<Farmer> comlist)
{
Response.Buffer = false;
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("标题" + ".csv"));
Response.Write("编号,人员姓名,人口,备住,乡镇Id,村庄Id\n");
//foreach (DataRow dr in dt.Rows)
foreach (Farmer bi in comlist)
{
//Response.Write(dr["ID"].ToString() + "," + dr["NT_Num"].ToString() + "," + dr["F_Id"].ToString() + "," + dr["T_Id"].ToString() + "," + dr ["V_Id"].ToString() + "\n");
Response.Write(bi.Id + "," + bi.FName + "," + bi.Population+ "," + bi.FRemarks+ "," + bi.T_Id + ","+bi.V_Id+"\n");
}
Response.End();
}
///导入的方法
FileUpload FileUp = e.Item.FindControl("FileUp") as FileUpload;//这是获取前台上传的控件
//是否上传文件
if (FileUp.HasFile)
{
//获取上传文件名
string extension = Path.GetExtension(FileUp.PostedFile.FileName);
//判断上传文件类型
switch (extension.ToLower())
{
case ".xls":
case ".xlsx":
AddExcel(FileUp, extension.ToLower());
Page.ClientScript.RegisterStartupScript(this.GetType(), "aaa", "<script>alert('上传成功！！！')</script>");
break;
default:
MessageBox.Show("文件格式不正确");
return;
//break;
}
}
/// <summary>
/// 这是添加excel的方法
/// </summary>
/// <param name="type"></param>
private void AddExcel(FileUpload FileUp,string type)
{
ISession session = CreateSession(Server.MapPath("http://www.cnblogs.com/JBNTBH_dy.cfg.xml"));
//生成保存上传文件名称
string strFileNewName = DateTime.Now.ToString("yyyyMMddhhmmss") + type;
//将上传文件保存至指定文件夹
FileUp.SaveAs(Server.MapPath("FileExcel") + "\\" + strFileNewName);
//获取刚刚上传文件路径
string filepath = Server.MapPath("FileExcel/" + strFileNewName).Replace("\\", "\\\\");
Farmer Data = new Farmer();//实例化Model类
DataSet ds = ExcelDataSource(filepath, "Sheet1");
if (ds.Tables.Count == 0)
{
MessageBox.Show("导入模版格式不合法，请核实！");
return;
}
DataRow[] dr = ds.Tables[0].Select();
DAL.JBNTBH_dy.BaseInfo dal = new DAL.JBNTBH_dy.BaseInfo(Server.MapPath("http://www.cnblogs.com/JBNTBH_dy.cfg.xml"));
DAL.JBNTBH_dy.farmer farmer = new DAL.JBNTBH_dy.farmer(Server.MapPath("http://www.cnblogs.com/JBNTBH_dy.cfg.xml"));
string xmid = "";
for (int i = 0; i < dr.Length; i++)
{
xmid = "jsyd" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
try
{
Data.FName = dr[i]["户主 姓名"].ToString();
if (!string.IsNullOrEmpty(dr[i]["人口"].ToString().Trim()))
{
Data.Population = int.Parse(dr[i]["人口"].ToString().Trim());
}
//切记，excle里面的格式一定是表头，正文从第二行开始，，dr[i]["所在地块编号"].引号里面的一定要是和表头一样
//表格实例
| 编号 | 户主 姓名 | 人口 | 所在地块编号 | 面积 | 东至 | 西至 | 南至 | 北至 | 备注 |
| 1 | 公翠平 | 1 | J3709111110010002 | 0.54 | 裴广太 | 南苏耕地 | 后营耕地 | 生产路 |
//Data.T_Id = (int)dr[i]["外镇ID"];// DataConvert.TxtGetDate(dr[i]["受让日期"].ToString());
Data.DiNumber = dr[i]["所在地块编号"].ToString();
Data.East = dr[i]["东至"].ToString();
Data.West = dr[i]["西至"].ToString();
Data.South = dr[i]["南至"].ToString();
Data.North = dr[i]["北至"].ToString();
// Data.V_Id = (int)dr[i]["村庄ID"];
Data.FRemarks = dr[i]["备注"].ToString();
farmer.Add(Data);//这是用Nhibernate框架里面的方法（ public void Add(Model.JBNTBH_dy.Farmer Model){ control.AddEntity(Model);}）
//session.Save(Data);
}
catch (Exception)
{
MessageBox.Show("数据导入失败，excel中第" + i + 2 + "条数据附近内容不合法，请核实后重新上传");
return;
}
//session.Flush();
//}
//ts.Complete();
}
}
/// <summary>
/// Excel导入数据方法
/// </summary>
/// <param name="filepath">文件路径</param>
/// <param name="sheetname">标签名称</param>
/// <returns>返回一个DataSet</returns>
public static DataSet ExcelDataSource(string filepath, string sheetname)
{
string strConn = "";
string[] str = filepath.Split('.');
if (str[str.Length - 1] == "xls")
{
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES'";
}
else if (str[str.Length - 1] == "xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";
}
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
DataSet ds = new DataSet();
try
{
oada.Fill(ds);
}
catch (Exception)
{
}
return ds;
}
简单的导出
#region 导出
protected void Button2_Click(object sender, EventArgs e)
{
Export("application/ms-excel", "设备维修记录表_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");//调用导出方法
}
private void Export(string FileType, string FileName)
{
//清除分页
GVList.AllowPaging = false;
//重新查询
query();
foreach (GridViewRow grv in this.GVList.Rows)
{
Label LabelUpdate = (Label)grv.FindControl("LabelUpdate");
LabelUpdate.Visible = false;
this.GVList.Columns[9].Visible = false;
this.GVList.Columns[10].Visible = false;
ImageButton ibtn = (ImageButton)grv.FindControl("imgDelete");
ibtn.Visible = false;
}
Response.Clear();
Response.Buffer = true;
//设定输出的字符集
Response.Charset = "GB2312";
//解决导出到Excel2007乱码问题
Response.Write("<meta http-equiv=Content-Type content=text/html;charset=GB2312>");
//假定导出的文件名为盘点结果表.xls
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
//DisableControls(GVList);
// PrepareGridViewForExport(GVList);
//解决导出到Excel2007乱码问题
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//设置导出文件的格式
Response.ContentType = FileType;
//关闭ViewState
this.EnableViewState = false;
StringWriter stringWriter = new StringWriter();
HtmlTextWriter textWriter = new HtmlTextWriter(stringWriter);
GVList.RenderControl(textWriter);
//把HTML写回浏览器
Response.Write("<span style='font-size:18px; font-weight:bold; color:Black'><center>设备维护记录表</center></span>");
Response.Write(stringWriter.ToString());
Response.Flush();
Response.End();
GVList.AllowPaging = true;//恢复分页
//为GridView重新绑定数据源
query();
}