excel的导入导出

  1.进行引用Microsoft.Office.Interop.Excel.dll和office.dll(但是在服务器上不行)

 

    public void GetDataToExcel()
        {
            //创建Excel对象
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

 

            excel.Application.Workbooks.Add(true);

            //设置Excel标题
            excel.Caption = "用户列表";

            //设置Excel列名
            excel.Cells[1, 1] = "ID";
            excel.Cells[1, 2] = "真实姓名";
            excel.Cells[1, 3] = "角色ID";
            excel.Cells[1, 4] = "添加时间";
            excel.Cells[1, 5] = "电话";
            excel.Cells[1, 6] = "添加人";
            excel.Cells[1, 7] = "用户名";
            excel.Cells[1, 8] = "角色";
            //设置Excel字体加粗
            excel.Range[excel.Cells[1, 1], excel.Cells[1, 8]].Font.Bold = true;
            //设置Excel字体颜色
            excel.Range[excel.Cells[1, 1], excel.Cells[1, 8]].Font.ColorIndex = 0;
            //设置Excel边框样式
            excel.Range[excel.Cells[1, 1], excel.Cells[1, 8]].Borders.LineStyle = XlLineStyle.xlContinuous;

            //循环将DataGridView中的数据赋值到Excel中
            System.Data.DataTable dt1 = new BLL.Admin().GetList("");
            int i;
            for (i = 0; i <dt1.Rows.Count; i++)
            {
                excel.Cells[i + 2, 1] = dt1.Rows[i]["id"].ToString();
                excel.Cells[i + 2, 2] = dt1.Rows[i]["adminname"].ToString();
                excel.Cells[i + 2, 3] = dt1.Rows[i]["status"].ToString();
                excel.Cells[i + 2, 4] = dt1.Rows[i]["date"].ToString();
                excel.Cells[i + 2, 5] = dt1.Rows[i]["phone"].ToString();
                excel.Cells[i + 2, 6] = dt1.Rows[i]["addname"].ToString();
                excel.Cells[i + 2, 7] = dt1.Rows[i]["loginname"].ToString();
                excel.Cells[i + 2, 8] = dt1.Rows[i]["statusname"].ToString();
               
            }
            //设置Excel水平对齐方式
            excel.Range[excel.Cells[1, 1], excel.Cells[i + 2, 4]].HorizontalAlignment = XlHAlign.xlHAlignLeft;

            //显示当前窗口
            excel.Visible = true;
        }

       当出现乱码时,加上

             Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

2.这个在服务器上是可以的

 

  protected void output(System.Data.DataTable dt)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("<table cellpadding='0' cellspacing='0' border='1'>");

            sb.Append(@"<tr class='tb_header'>
                                <td style='width:100px; background-color:#67B3DC; text-align:center'>编号</td>
                                <td style='width:100px; background-color:#67B3DC; text-align:center'>内容标题</td>
                            
                                <td style='width:100px; background-color:#67B3DC; text-align:center'>内容文档</td>
                                <td style='width:100px; background-color:#67B3DC; text-align:center'>内容链接</td>
                            
                                <td style='width:100px; background-color:#67B3DC; text-align:center'>添加时间</td>
                              
                    </tr>");
            int id2 = int.Parse(Session["id"].ToString());
            string filesname = new BLL.MauClass().GetidByModel(id2).Classname;

            for (int x = 0; x < dt.Rows.Count; x++)
            {
                sb.Append("<tr>");
                for (int y = 0; y < 1; y++)
                {
                    sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["id"]));
                    sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["name"]));

                    sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["files"]));
                    sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["url"]));

                    sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["date"]));
                }
                sb.Append("</tr>");
            }

            sb.Append("</table>");

            Response.Buffer = true;
            Response.Clear();
            Response.ContentType = "application/msexcel";

            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", filesname));


        
            Response.Write(sb.ToString());
            Response.Flush();
            //Response.End();
            //  HttpContext.C()urrent.ApplicationInstance.CompleteRequest();
            Response.Close();

        }

 

 

3.导入excel数据库

 

  public DataSet ExcelDs(string FilenamePath, string Table)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + FilenamePath + ";Extended Properties = 'Excel 8.0;HDR=YES;IMEX=1'";
            OleDbConnection Odbconn = new OleDbConnection(strConn);
            OleDbDataAdapter Odda = new OleDbDataAdapter("select * from [Sheet1$]", Odbconn);
            DataSet Ds = new DataSet();
            Odda.Fill(Ds, Table);
            return Ds;
        }
 

        protected void subbtn_Click(object sender, EventArgs e)
        {
            //Excel 实例
            string IsXls = System.IO.Path.GetExtension(FileUpload2.FileName).ToString().ToLower();
            if (FileUpload2.HasFile == false)
            {
                base.Response.Write("<script> alert( '请您先选择后缀名为.xls或.xlsx的Excel文件 ') </script> ");

            }
            else if (IsXls != ".xls" && IsXls != ".xlsx")
            {
                base.Response.Write("<script> alert( '请选择后缀名为.xls或.xlsx的Excel文件 ') </script> ");

            }
            else
            {
                string NewFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random().Next(99, 9999) + IsXls;//新文件名
                string NewPath = Server.MapPath("../../Excel/") + NewFileName;//服务器保存路径
                FileUpload2.SaveAs(NewPath);
                DataSet Ds = ExcelDs(NewPath, NewFileName);
                DataRow[] Dr = Ds.Tables[0].Select();
                int RowsNum = Ds.Tables[0].Rows.Count;
                if (RowsNum.Equals(0))
                {
                    base.Response.Write("<script> alert( '该excel为空表,请重新上传! ') </script> ");
                }
                else
                {
                    for (int i = 0; i < Dr.Length-1; i++)//遍历Excel中的考核范围并添加到数据库
                    {
                        Model.Material m = new Model.Material();

                        m.Name = Dr[i]["产品名称"].ToString();
                        m.Supplier = Dr[i]["供应方"].ToString();
                        m.Norms= Dr[i]["规格"].ToString();
                        m.Units = Dr[i]["单位"].ToString();
                       
                        if (Dr[i]["采购时间"].ToString() != "")
                        {
                            m.Date = Convert.ToDateTime(Dr[i]["采购时间"].ToString());
                        }
                        else
                        {
                            m.Date = DateTime.Parse(DateTime.Now.ToString("yy-MM-dd"));
                        }
                        m.Pname = Dr[i]["项目名称"].ToString();
                        m.Remarks = Dr[i]["备注"].ToString();
                        if (Dr[i]["投标价"].ToString() != "")
                        {
                            //m.Bprice = Convert.ToDouble(Dr[i]["投标价"]);
                            m.Bprice = Double.Parse(Dr[i]["投标价"].ToString());
                        }
                        else
                        {
                            m.Bprice = 0;
                        }
                        if (Dr[i]["信息价"].ToString() != "")
                        {

                        m.Iprice = Convert.ToDouble(Dr[i]["信息价"]);
                        }
                        else
                        {
                            m.Iprice = 0;
                        }
                        if (Dr[i]["采购指导价"].ToString() != "")
                        {
                        m.Puprice = Convert.ToDouble(Dr[i]["采购指导价"]);
                        }
                        else
                        {
                            m.Puprice = 0;

                        }
                        m.Classid  = int.Parse(ddl.SelectedValue.Split(',')[0]);
                        m.Classname = ddl.SelectedItem.Text;
              
                m.Parentid  = new BLL.MaClass().GetidByModel(int.Parse(ddl.SelectedValue.Split(',')[0])).Parentid;
                m.Parentname = new BLL.MaClass().GetidByModel(int.Parse(ddl.SelectedValue.Split(',')[0])).Parentname;
                        m.Username = "管理员";

                    
                       
                        //添加方法
                        if (new BLL.Materials().Add(m) <= 0)
                        {
                            base.Response.Write("<script> alert( '添加失败! ') </script> ");
                            return;
                        }
                    }
                }
            }
        }

 

 

posted @ 2013-09-17 16:52  李翠华  阅读(233)  评论(0编辑  收藏  举报