主要功能是写在一个按钮里面的,导出Excel文件的方法。
用了自己写的一类来操作数据库
注意几点:
1、在格式化日期类型的时候不得不用笨办法foreach每个row来设置。。。。
2、public override void VerifyRenderingInServerForm,据说是BUG,必须添加,但是注释掉一样的能用这个,所以说是没用的,哈哈
- #region 导出Excel文件
- // 导出Excel文件
- protected void Button6_Click(object sender, EventArgs e)
- {
- GridView myGridView = new GridView();
- SelectData mySelectData = new SelectData();
- DataTable myDataTable = new DataTable();
- string strSQLOrder;
- string strWhere, strSortExpression, strSortDirection;
- strSortExpression = ViewState["GridView1SortExpression"].ToString();
- strSortDirection = ViewState["GridView1Order"].ToString();
- strWhere = ViewState["GridView1Where"].ToString();
- try
- {
- // 如果有查询条件
- if (strWhere.Length > 0)
- strSQLOrder = @"SELECT CM_ID, Name, MobileTelephone, Phone, Birthday, Sex, Age, Profession, Expenditure, AddDateTime, MonthIncome, Taste FROM tabCustomerInfo " +
- @" where DeletedTag = False and " + strWhere + @" ORDER BY " + strSortExpression + " " + strSortDirection;
- else
- strSQLOrder = @"SELECT CM_ID, Name, MobileTelephone, Phone, Birthday, Sex, Age, Profession, Expenditure, AddDateTime, MonthIncome, Taste FROM tabCustomerInfo" +
- @" where DeletedTag = False ORDER BY " + strSortExpression + " " + strSortDirection;
- // 用GetTable方法获取数据
- myDataTable = mySelectData.GetTable(strSQLOrder);
- myDataTable.Columns["CM_ID"].ColumnName = "编号";
- myDataTable.Columns["Name"].ColumnName = "姓名";
- myDataTable.Columns["MobileTelephone"].ColumnName = "移动电话";
- myDataTable.Columns["Phone"].ColumnName = "电话";
- myDataTable.Columns["Birthday"].ColumnName = "出生日期";
- myDataTable.Columns["Sex"].ColumnName = "性别";
- myDataTable.Columns["Age"].ColumnName = "年龄";
- myDataTable.Columns["Profession"].ColumnName = "职业";
- myDataTable.Columns["Expenditure"].ColumnName = "消费金额";
- myDataTable.Columns["AddDateTime"].ColumnName = "编辑时间";
- myDataTable.Columns["MonthIncome"].ColumnName = "月收入";
- myDataTable.Columns["Taste"].ColumnName = "爱好";
- myGridView.DataSource = myDataTable;
- // 绑定数据
- myGridView.DataBind();
- foreach (GridViewRow myGVR in myGridView.Rows)
- {
- myGVR.Cells[4].Text = Convert.ToDateTime((myGVR.Cells[4].Text)).ToString("yyyy-MM-dd");
- myGVR.Cells[9].Text = Convert.ToDateTime((myGVR.Cells[9].Text)).ToString("yyyy-MM-dd");
- }
- }
- catch (System.Exception ex)
- {
- tips.Text = "查询数据库时发生错误!错误信息:" + ex.Message + "<br>可能是因为数据库连接出现问题!请联系系统管理员!<br>>";
- return;
- }
- Response.Clear();
- Response.Buffer = true;
- Response.Charset = "GB2312";
- Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("客户资料.xls", System.Text.Encoding.UTF8));
- // 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
- Response.ContentEncoding = System.Text.Encoding.UTF8;
- Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
- System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
- System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
- myGridView.RenderControl(oHtmlTextWriter);
- Response.Output.Write(oStringWriter.ToString());
- Response.Flush();
- Response.End();
- }
- // 导出Excel文件必须要下面这个,据说是vs2005的BUG,日
- public override void VerifyRenderingInServerForm(Control control)
- {
- }
- #endregion
在这个页的ASPX文件里面的<%@ Page,必须加上EnableEventValidation="false"
我看网上说要在Web.Config加东西,但是我没加也能成功。。。
在最后用Response导出成Excel的那一段代码是抄网上的