用excel 导入导出数据

 /// <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() + "\n";
                    colHeaders += "开放时间" + "\n";
                }
else
                {
// colHeaders += dt.Columns[i].Caption.ToString() + "\t";
switch (dt.Columns[i].Caption.ToString())
                    {
case "EnterpriseLibaryPosId": colHeaders += "序号" + "\t"; break;
case "EnterpriseLibaryId": colHeaders += "第三空间ID" + "\t";break ;
case "Position": colHeaders += "坐标" + "\t"; break;
case "Name": colHeaders += "名称" + "\t"; break;
case "Address": colHeaders += "地址" + "\t"; break;
case "Tel": colHeaders += "电话" + "\t"; 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() + "\n";
                    }
else
                    {
if (i == 4)
                        {
string addr = AreaBLL.GetArea(Convert.ToInt32(row[7])) + row[i].ToString();
                            ls_item += addr + "\t";
                        }
else
                        {
                            ls_item += row[i].ToString() + "\t";
                        }
                    }

                }
                resp.Write(ls_item);
                ls_item = "";
            }
            dt.Clear();
            resp.End();
        }

秒客网

用excel 导入导出数据

相关文章