#region 导出Excel /// <summary> /// 导出Excel /// </summary> /// <param name="page">请求的页面this</param> /// <param name="dataTable">导出的数据源</param> /// <param name="fileName">保存文件名称</param> /// <returns>布尔值</returns> public bool ExportExcel(Page page, DataTable dataTable, string fileName) { try { HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.numberformat:@"; page.EnableViewState = false; HttpContext.Current.Response.Charset = "UTF-8"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//设置输出流为简体中文 HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); //输出列名 for (int i = 0; i < dataTable.Columns.Count; i++) HttpContext.Current.Response.Write(dataTable.Columns[i].ColumnName + "\t"); HttpContext.Current.Response.Write("\r\n"); //输出数据 for (int i = 0; i < dataTable.Rows.Count; i++) { for (int j = 0; j < dataTable.Columns.Count; j++) { HttpContext.Current.Response.Write(dataTable.Rows[i][j].ToString() + "\t"); } HttpContext.Current.Response.Write("\r\n"); } //输出当前缓存内容 //HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); return true; } catch { return false; } } #endregion
#region 导出Excel 自定义格式 /// <summary> /// 导出Excel /// 1.文本:vnd.ms-excel.numberformat:@ /// 2.日期:vnd.ms-excel.numberformat:yyyy/mm/dd /// 3.数字:vnd.ms-excel.numberformat:#,##0.00 /// 4.货币:vnd.ms-excel.numberformat:¥#,##0.00 /// 5.百分比:vnd.ms-excel.numberformat: #0.00% /// </summary> /// <param name="fileName"></param> /// <param name="dt"></param> /// <returns></returns> public bool Export(string fileName, DataTable dt) { try { HttpResponse resp; resp = System.Web.HttpContext.Current.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); resp.AppendHeader("Content-Type", "application/ms-excel"); StringBuilder colHeaders = new StringBuilder(); StringBuilder ls_item = new StringBuilder(); DataRow[] myRow = dt.Select(); int cl = dt.Columns.Count; colHeaders.Append(" <html><head> \n "); colHeaders.Append(" <meta http-equiv='Content-Type' content='text/html; charset=gb2312' /> \n "); colHeaders.Append(" </head> \n "); colHeaders.Append(" <body> \n "); colHeaders.Append(" <table border='1'> "); colHeaders.Append(" <tr> "); //输出列名 for (int i = 0; i < dt.Columns.Count; i++) colHeaders.Append("<td style='background-color:#CCCCCC'>" + dt.Columns[i].ColumnName + "</td>"); colHeaders.Append("</tr> "); resp.Write(colHeaders.ToString()); foreach (DataRow row in myRow) { ls_item.Append("<tr>"); for (int i = 0; i < cl; i++) { if (i == (cl - 1)) { ls_item.Append("<td style='vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>" + "\n"); } else { ls_item.Append("<td style= 'vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>"); } } ls_item.Append("</tr>"); } ls_item.Append(" </table> \n "); ls_item.Append(" </body> \n "); ls_item.Append(" </html>"); resp.Write(ls_item.ToString()); resp.End(); return true; } catch { return false; } } #endregion
#region 导入Excel public string ImportExcel(string[] list, string filePath) { string isXls = System.IO.Path.GetExtension(filePath).ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (isXls != ".xls") return "请选择Excel文件导入!"; DataSet ds = ExecleDataSet(filePath);//调用自定义方法 DataRow[] dr = ds.Tables[0].Select();//定义一个DataRow数组 int rowsnum = ds.Tables[0].Rows.Count; if (ds.Tables[0].Rows.Count == 0) return "Excel无数据!"; return ""; } //OleDB连接读取Excel中数据 public DataSet ExecleDataSet(string filePath) { string OleDbConnection = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(OleDbConnection); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn); odda.Fill(ds); conn.Close(); return ds; } #endregion