asp.net导出excel都有什么方法啊？

mylover88 2014-04-22 09:54:50

asp.net导出excel都有什么方法啊？请贴上代码谢谢

...全文

231 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

时间都去哪了啊 2014-04-22

打赏
举报

本人用代码写的，希望对君，有所帮助


 protected void Button1_Click(object sender, EventArgs e)
        {
            string XX = Label1.Text + Label3.Text;
            Export("application/ms-excel", XX + "月份采购计划.xls");
        }

        public override void VerifyRenderingInServerForm(Control control)
        {

        }

        private void Export(string FileType, string FileName)
        {
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
            Response.ContentType = FileType;
            this.EnableViewState = false;
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            GridView1.RenderControl(hw);
            Response.Write(tw.ToString());
            Response.End();
        }

aellonxie 2014-04-22

打赏
举报

机器安装excel 导入excel 12组件到项目　//实例化一个Excel应用程序对象 Microsoft.Office.Interop.Excel.application myexcel = new Microsoft.Office.Interop.Excel.Application(); //添加工作表 myexcel.Application.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet myworksheet = (Microsoft.Office.Interop.Excel.Worksheet) myexcel.Worksheets["Sheet1"]; //定义一个区域范围 Microsoft.Office.Interop.Excel.Range myrange = myexcel.get_Range(myexcel.Cells[1, 1], myexcel.Cells[3, 3]); //显示这个excel程序 myexcel.Visible = true ; 　　但此时的Excel表格是一个空的表格，没有任何内容，下面就来介绍如何往Excel表格中输入数据。　　（3）.往Excel表格中输入数据：　　在命名空间"Excel"中，还定义了一个类"Cell"，这个类所代表的就是Excel表格中的一个下单元。通过给差"Cell"赋值，从而实现往Excel表格中输入相应的数据，下列代码功能是打开Excel表格，并且往表格输入一些数据。 myexcel.Caption = " 　　花名册"; myworksheet .Cells[1, 1] = "　　花名册"; myworksheet .Cells[2, 1] = "姓名"; （4）. Visual C＃调用Excel表格，　　了解了上面的这些知识，得到完成上述功能的程序代码就显得比较容易了，函数具体如下： Excel.ApplicationClass Mylxls = new Excel.ApplicationClass(); Mylxls.Application.Workbooks.Add(true); myexcel.Caption = " 　　花名册"; myworksheet .Cells[1, 1] = "　　花名册"; myworksheet .Cells[2, 1] = "姓名"; myworksheet .Cells[2, 2] = "性别"; myworksheet .Cells[2, 3] = "出生年月"; //合并单元格(第一行的第一列至第３列) myworksheet .get_Range(myworksheet .Cells[1, 1], myworksheet .Cells[1, 3]).MergeCells = true; //逐行写入数据，dt为DataTable对象,从第三行开始写数据。　　　　　int i=3; foreach(DataRow row in dt.Rows) { myworksheet .Cells[i, 1] = row["姓名"].ToString(); myworksheet .Cells[i, 2] = row["性别"].ToString(); myworksheet .Cells[i, 3] = row["出生年月"].ToString(); i++; } 还可以，用oledb直接写入，那样就不需要导入这么麻烦了 excel也是跟sql一样可以用sql 语句操作的

於黾 2014-04-22

打赏
举报

我知道2种方法,一种是以流的方式直接保存到本地不过只能导出table,修改列名合并单元格什么的都不行了还有一种是BS下挂一个CS的类,先导出到服务器,然后下载

by_封爱版主 2014-04-22

打赏
举报

导出我推荐你用myxls 个人感觉不错..

「已注销」 2014-04-22

打赏
举报

npoi比较好用

dralion 2014-04-22

打赏
举报

推荐两款三方插件：aspose，NPOI，如不懂在咨询我

头像即本人_敲代码很累 2014-04-22

打赏
举报

public void CreateExcel(DataTable dt, string FileName)
        {
            try
            {
                HttpResponse resp;
                resp = Page.Response;
                resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
                string colHeaders = "", ls_item = "";

                //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; i++)
                {
                    if (i == (cl - 1))//最后一列，加\n
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                    }
                    else
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + ",";
                    }

                }
                resp.Write(colHeaders);
                //向HTTP输出流中写入取得的数据信息 

                int fees = 0;

                //逐行处理数据   
                foreach (DataRow row in myRow)
                {
                    //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据     
                    for (i = 0; i < cl; i++)
                    {
                        if (i == (cl - 1))//最后一列，加\n
                        {
                            ls_item += row[i].ToString().Trim() + "\n";// row[i].ToString() + "\n";
                        }
                        else
                        {
                            ls_item += row[i].ToString().Trim() + ",";
                        }
                    }

                    resp.Write(ls_item);
                    ls_item = "";
                }

                resp.End();
            }
            catch (Exception ex)
            {
                // Response.Write("<script>alert('"+ex .Data +ex.Message +ex.Source +ex.TargetSite+"');history.go(-1);</script>");
                throw;
            }
        }

这个是CSV的-,-

头像即本人_敲代码很累 2014-04-22

打赏
举报

引用 14 楼 mylover88 的回复:

[quote=引用 10 楼 lvxinyi123 的回复:]

/// <summary> 
        /// 将指定的Dataset导出到Excel文件 
        /// </summary> 
        /// <param name="dt"></param> 
        /// <returns></returns> 
        public int Export(string path, System.Data.DataTable dt)
        {
            try
            {
                
                Microsoft.Office.Interop.Excel.Application excelkccx = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook wb = excelkccx.Workbooks.Add(true);
                Range rngA = (Range)excelkccx.Columns["A", Type.Missing];//设置单元格格式
                rngA.NumberFormatLocal = "@";//字符型格式
                //Range rngD = (Range)excelkccx.Columns["D", Type.Missing];//设置单元格格式
                //rngD.NumberFormatLocal = "0.00";
                //Range rngE = (Range)excelkccx.Columns["E", Type.Missing];//设置单元格格式
                //rngE.NumberFormatLocal = "0.00";
                //Range rngF = (Range)excelkccx.Columns["F", Type.Missing];//设置单元格格式
                //rngF.NumberFormatLocal = "0.00";
                //Range rngAll = (Range)excelkccx.Columns["B","C"];
                //rngAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;   //字段水平居中
                //Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)excelkccx.Workbooks[1].Worksheets[1];
                //string name = ws.Name; //获取sheet名称


                //System.Data.DataTable dt = ds.Tables[0];
                int row = 2;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    excelkccx.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        excelkccx.Cells[row, j + 1] = dt.Rows[i][j].ToString();
                    }
                    row++;
                }
                wb.SaveAs(path);
                wb.Close(false, null, null);
                excelkccx.Quit();
                wb = null;
                excelkccx = null;
                return 1;
            }
            catch(Exception ex)
            {
                return 0;
            }
        }

这个方法你直接拿去可以用我一直在用的不谢哈

你的这个如果服务器没有安装office就不能运行吧？[/quote]是的刚忘记提示了不好意思,我同事有导出CSV格式的,不知道满足要求吗?

mylover88 2014-04-22

打赏
举报

引用 10 楼 lvxinyi123 的回复:

/// <summary> 
        /// 将指定的Dataset导出到Excel文件 
        /// </summary> 
        /// <param name="dt"></param> 
        /// <returns></returns> 
        public int Export(string path, System.Data.DataTable dt)
        {
            try
            {
                
                Microsoft.Office.Interop.Excel.Application excelkccx = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook wb = excelkccx.Workbooks.Add(true);
                Range rngA = (Range)excelkccx.Columns["A", Type.Missing];//设置单元格格式
                rngA.NumberFormatLocal = "@";//字符型格式
                //Range rngD = (Range)excelkccx.Columns["D", Type.Missing];//设置单元格格式
                //rngD.NumberFormatLocal = "0.00";
                //Range rngE = (Range)excelkccx.Columns["E", Type.Missing];//设置单元格格式
                //rngE.NumberFormatLocal = "0.00";
                //Range rngF = (Range)excelkccx.Columns["F", Type.Missing];//设置单元格格式
                //rngF.NumberFormatLocal = "0.00";
                //Range rngAll = (Range)excelkccx.Columns["B","C"];
                //rngAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;   //字段水平居中
                //Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)excelkccx.Workbooks[1].Worksheets[1];
                //string name = ws.Name; //获取sheet名称


                //System.Data.DataTable dt = ds.Tables[0];
                int row = 2;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    excelkccx.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        excelkccx.Cells[row, j + 1] = dt.Rows[i][j].ToString();
                    }
                    row++;
                }
                wb.SaveAs(path);
                wb.Close(false, null, null);
                excelkccx.Quit();
                wb = null;
                excelkccx = null;
                return 1;
            }
            catch(Exception ex)
            {
                return 0;
            }
        }

这个方法你直接拿去可以用我一直在用的不谢哈

你的这个如果服务器没有安装office就不能运行吧？

mylover88 2014-04-22

打赏
举报

引用 6 楼 u010703853 的回复:

本人用代码写的，希望对君，有所帮助


 protected void Button1_Click(object sender, EventArgs e)
        {
            string XX = Label1.Text + Label3.Text;
            Export("application/ms-excel", XX + "月份采购计划.xls");
        }

        public override void VerifyRenderingInServerForm(Control control)
        {

        }

        private void Export(string FileType, string FileName)
        {
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
            Response.ContentType = FileType;
            this.EnableViewState = false;
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            GridView1.RenderControl(hw);
            Response.Write(tw.ToString());
            Response.End();
        }

你的代码在360兼容模式下根本导不出来

mylover88 2014-04-22

打赏
举报

[quote=引用 6 楼 u010703853 的回复:] 你的代码在360的兼容模式下下载后没东西，我现在用的就是这种方法

exception92 2014-04-22

打赏
举报

http://blog.csdn.net/duanzi_peng/article/details/17414629

头像即本人_敲代码很累 2014-04-22

打赏
举报

/// <summary> 
        /// 将指定的Dataset导出到Excel文件 
        /// </summary> 
        /// <param name="dt"></param> 
        /// <returns></returns> 
        public int Export(string path, System.Data.DataTable dt)
        {
            try
            {
                
                Microsoft.Office.Interop.Excel.Application excelkccx = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel._Workbook wb = excelkccx.Workbooks.Add(true);
                Range rngA = (Range)excelkccx.Columns["A", Type.Missing];//设置单元格格式
                rngA.NumberFormatLocal = "@";//字符型格式
                //Range rngD = (Range)excelkccx.Columns["D", Type.Missing];//设置单元格格式
                //rngD.NumberFormatLocal = "0.00";
                //Range rngE = (Range)excelkccx.Columns["E", Type.Missing];//设置单元格格式
                //rngE.NumberFormatLocal = "0.00";
                //Range rngF = (Range)excelkccx.Columns["F", Type.Missing];//设置单元格格式
                //rngF.NumberFormatLocal = "0.00";
                //Range rngAll = (Range)excelkccx.Columns["B","C"];
                //rngAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;   //字段水平居中
                //Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)excelkccx.Workbooks[1].Worksheets[1];
                //string name = ws.Name; //获取sheet名称


                //System.Data.DataTable dt = ds.Tables[0];
                int row = 2;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    excelkccx.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        excelkccx.Cells[row, j + 1] = dt.Rows[i][j].ToString();
                    }
                    row++;
                }
                wb.SaveAs(path);
                wb.Close(false, null, null);
                excelkccx.Quit();
                wb = null;
                excelkccx = null;
                return 1;
            }
            catch(Exception ex)
            {
                return 0;
            }
        }

这个方法你直接拿去可以用我一直在用的不谢哈

志起计算机编程 2014-04-22

打赏
举报

引用 8 楼 u012999424 的回复:

 string strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=t_alibaba_data.xlsx;Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = "SELECT * FROM [t_alibaba_data$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);

上面从t_alibaba_data.xlsx文件中读取数据到Dataset类型的ds中

你说导出excel意思是导出数据吧，那按照上面的方面可以把ds的数据存到数据库中。

志起计算机编程 2014-04-22

打赏
举报

 string strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=t_alibaba_data.xlsx;Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = "SELECT * FROM [t_alibaba_data$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);

上面从t_alibaba_data.xlsx文件中读取数据到Dataset类型的ds中

wubing1111 2014-04-22

打赏
举报

using Aspose.Cells; Workbook workbook = new Workbook(); Worksheet sheet = (Worksheet)workbook.Worksheets[0]; sheet.Cells["A1"].PutValue("ＴＥＳＴ１"); sheet.Cells["B1"].PutValue("日期"); String filename = string.Format("{0}{1}.xls", "saleranklist", Convert.ToDateTime(DateTime.Now).ToString("yyyyMMdd")); //文件默认命名方式，可以自定义 Response.ContentType = "application/ms-excel;charset=utf-8"; Response.AddHeader("content-disposition", "attachment; filename=" + filename); System.IO.MemoryStream memStream = workbook.SaveToStream(); Response.BinaryWrite(memStream.ToArray()); Response.End();