/// <summary> /// .xls导入数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnDaoRu_Click(object sender, EventArgs e) { // Flag("DelPurview&0", "EnterLibPos"); if (string.IsNullOrEmpty(FileUpload1.FileName)) { JScriptUtil.AlertAndRedirect("请选择导入文件!", "index.aspx"); } string sFileName = FileUpload1.FileName; //文件的名字 if (sFileName.Substring(sFileName.Length - 4, 4).ToLower()!=".xls") { JScriptUtil.AlertAndRedirect("格式不支持!", "index.aspx"); return; } //文件保存的路径 string sFilePath = HttpContext.Current.Server.MapPath("~/") + "excel\" + sFileName; FileUpload1.PostedFile.SaveAs(sFilePath); if (!File.Exists(sFilePath)) { JScriptUtil.AlertAndRedirect("文件上传不成功!", "index.aspx"); } string sErrorMsg = string.Empty; //错误信息 //从Excel获取到的DataSet数据 DataSet dsBookExcel = ExcelUtil.GetDataSetByPath(sFilePath, ref sErrorMsg); //此时出错了 if (sErrorMsg.Length > 0) { JScriptUtil.AlertAndRedirect(sErrorMsg, "index.aspx"); } foreach (DataTable dtItem in dsBookExcel.Tables) { if (!dtItem.Columns.Contains("第三空间ID") || dtItem.Rows.Count == 0) { continue; } foreach (DataRow drItem in dtItem.Rows) { InsertData(drItem); } } bind_data(); JScriptUtil.AlertAndRedirect("导入完成", "index.aspx"); }
/// <summary> /// 通过路径获取DataSet /// </summary> /// <param name="strFileName"> 文件路径 </param> /// <param name="strErrorMsg"> 错误信息 </param> public static DataSet GetDataSetByPath(string strFileName, ref string strErrorMsg) { OleDbConnection OleConn = null; DataSet dsResult = new DataSet(); // DataSet结果集 try { string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties='Excel 12.0;HDR=False;IMEX=1'"; OleConn = new OleDbConnection(strConn); OleConn.Open();
//Excel中表的数据 DataTable dtSheet = OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//获取工作簿
string sql = string.Empty; // 查询语句
foreach (DataRow item in dtSheet.Rows) { if (item[2].ToString().Contains("FilterDatabase")) continue; // 过滤掉重复的数据 sql = string.Format("SELECT * FROM [{0}]", item[2].ToString()); OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); DataTable dtItem = new DataTable(); OleDaExcel.Fill(dtItem); dtItem.TableName = item[2].ToString(); dsResult.Tables.Add(dtItem); OleDaExcel.Dispose(); dtItem.Dispose(); }
} catch (Exception err) { strErrorMsg = string.Format("数据绑定Excel失败!失败原因：{0}", err.Message); return null; } finally { OleConn.Close(); } return dsResult; }
/// <summary> /// 导出数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnDaochu_Click(object sender, EventArgs e) { string sql = "select a.*,b.Name,b.Address,b.Tel,b.OpenTime,b.AreaId from dbo.bz_EnterpriseLibaryPos as a left join dbo.bz_EnterpriseLibrary as b on a.EnterpriseLibaryId=b.EnterpriseLibraryId "; //得到需要导入Excel的DataTable DataTable dt = Common.DBUtility.SqlHelper.ExecuteDataTable(CommandType.Text, sql, null); ; CreateExcel(dt, "zuobiao.xls", this.Page); } /// <summary> /// 导出xls /// </summary> /// <param name="dt">数据源</param> /// <param name="fileName">文件名（坐标.xls）</param> /// <param name="page"></param> public static void CreateExcel(DataTable dt, string fileName, Page page) { HttpResponse resp; resp = page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); // HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=Excel.xls"); string colHeaders = "", ls_item = ""; ////定义表对象与行对象，同时用DataSet对其值进行初始化 //DataTable dt = ds.Tables[0]; DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的 int i = 0; int cl = dt.Columns.Count; //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 for (i = 0; i < cl-1; i++) { if (i == (cl - 2))//最后一列，加n,不是AreaId列 { // colHeaders += dt.Columns[i].Caption.ToString() + " "; colHeaders += "开放时间" + " "; } else { // colHeaders += dt.Columns[i].Caption.ToString() + " "; switch (dt.Columns[i].Caption.ToString()) { case "EnterpriseLibaryPosId": colHeaders += "序号" + " "; break; case "EnterpriseLibaryId": colHeaders += "第三空间ID" + " ";break ; case "Position": colHeaders += "坐标" + " "; break; case "Name": colHeaders += "名称" + " "; break; case "Address": colHeaders += "地址" + " "; break; case "Tel": colHeaders += "电话" + " "; break; default: return; } } } resp.Write(colHeaders); //向HTTP输出流中写入取得的数据信息 //逐行处理数据 foreach (DataRow row in myRow) { //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 for (i = 0; i < cl-1; i++) { if (i == (cl - 2))//最后一列，加n { ls_item += row[i].ToString() + " "; } else { if (i == 4) { string addr = AreaBLL.GetArea(Convert.ToInt32(row[7])) + row[i].ToString(); ls_item += addr + " "; } else { ls_item += row[i].ToString() + " "; } } } resp.Write(ls_item); ls_item = ""; } dt.Clear(); resp.End(); }