sql数据导出Excel

方法1：

    //sql语句

  SELECT cu.CustomerCode  as '客户编号'
 ,CustomerName as '客户名称',cu.Address as '联系地址',cu.Telephone as '电话'
 ,cu.TelAreaCode as '电话区号' ,cu.Fax as '传真号',cu.FaxAreaCode as '传真区号'
 ,cu.FaxExtNo as '传真',CityID as '所属城市',WebSiteUrl as '公司网站',
 StatusName as '客户状态',SourceName as '客户来源',TypeName as '客户类型',LevelName as '客户级别',IndustryName as '客户所属行业'
 FROM T_Customer as cu
 inner join T_AddressBook as d on d.CustomerCode =cu.CustomerCode
 inner join dbo.T_Customer_Source as cus on cus.SourceId=cu.SourceId
 inner join dbo.T_Customer_Status as sta on sta.StatusId=cu.StatusId
 inner join  dbo.T_Customer_Type as typ on typ.TypeId=cu.TypeId
 inner join dbo.T_Customer_Level as lev on lev.LevelId=cu.LevelId
 inner join  dbo.T_Customer_Industry as ind on ind.IndustryId=cu.IndustryId

 

    //导出按钮事件
        protected void Button1_Click(object sender, EventArgs e)
        {
            Export();
        }

        //导出方法
        private void Export()
        {

            string sql = "select * from T_Role";
            DataSet ds = DbHelperSQL.Query(sql);
            DataTable dt = ds.Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                string time = DateTime.Now.ToString("yyyyMMddHHmmss");
                this.ExportExcel(dt, time + "_统计客户数据");
            }
        }

        /// <summary>
        /// 导出Excel数据
        /// </summary>
        /// <param name="data"></param>
        /// <param name="fileName"></param>
        protected void ExportExcel(System.Data.DataTable data, string fileName)
        {
            if (data != null && data.Rows.Count > 0)
            {
                Response.ContentType = "application/vnd.ms-excel";
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.Charset = "Utf-8";
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", System.Text.Encoding.UTF8));

                System.Text.StringBuilder sbHtml = new System.Text.StringBuilder();
                sbHtml.AppendLine("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">");
                sbHtml.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");

                //写出列名
                sbHtml.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">");
                foreach (System.Data.DataColumn column in data.Columns)
                {
                    sbHtml.AppendLine("<td>" + column.ColumnName + "</td>");
                }
                sbHtml.AppendLine("</tr>");

                //写数据
                bool isWarn = false;
                foreach (System.Data.DataRow row in data.Rows)
                {
                    isWarn = false;
                    sbHtml.Append("<tr>");
                    foreach (System.Data.DataColumn column in data.Columns)
                    {
                        if ("状态".Equals(column.ColumnName) && !"正常".Equals(row[column].ToString()))
                        {
                            isWarn = true;
                            break;
                        }
                    }

                    foreach (System.Data.DataColumn column in data.Columns)
                    {
                        if (isWarn)
                        {
                            sbHtml.Append("<td style='background-color:Tomato;color:Navy;font-weight:bold;'>");
                        }
                        else
                        {
                            sbHtml.Append("<td>");
                        }
                        sbHtml.Append(row[column].ToString() + "</td>");
                    }
                    sbHtml.AppendLine("</tr>");
                }
                sbHtml.AppendLine("</table>");
                Response.Write(sbHtml.ToString());
                Response.End();
            }
        }

 

 

方法2：

    /// <summary>
    /// 导出发货单点击事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        LinkButton lkbtn = (LinkButton)sender;
        string InvoceID = lkbtn.CommandArgument;
        ExportToExcel(InvoceID);
    }

 

    /// <summary>
    /// 导出客户数据到 Excel
    ///
    /// </summary>
    public void ExportToExcel(string InvoceID)
    {
        System.Data.DataTable dt = null;

        string strSQL = @"SELECT TI.ID
                                ,TI.Invoice_Code
                                ,TOR.OrderCode
                                ,TOR.CustomerCode
                                ,TOR.CallTime
                                ,TOR.DeliveryAddress
                                ,TOR.PayType
                                ,TOR.ProductCount
                                ,TOR.TotalAmount
                                ,TOR.IsReceived
                                ,TOR.TransportWay
                                ,TC.CustomerName
                                ,TCT.TypeName AS CustomerType
                                ,TP.ProvinceName AS Area
                                ,TC.Phone
                                ,TC.Post
                            FROM T_Invoice TI
                      LEFT JOIN  T_Order TOR
                              ON TOR.OrderCode=TI.Order_Code
                      LEFT JOIN  T_Customer TC
                              ON TC.CustomerCode=TOR.CustomerCode
                      LEFT JOIN T_Customer_Type TCT
                              ON TCT.TypeId=TC.CustomerType
                      LEFT JOIN  T_Province TP
                              ON TP.ProvinceID=TC.Area
                           WHERE TI.ID='{0}'";
        strSQL = string.Format(strSQL, InvoceID);
        dt = ClsDbAccess.getDataSet(strSQL).Tables[0];
        if (dt != null && dt.Rows.Count > 0)
        {
            System.Data.DataRow r = dt.Rows[0];
            string fileURL = Server.MapPath("~/temp/客户发货单"+DateTime.Now.ToString ("yyyyMMddHHmmss")+".xls");

            //实例化操作Excel的对象
            ApplicationClass xlsApp = new ApplicationClass();

            if (xlsApp == null)
            {
                //对此实例进行验证，如果为null则表示运行此代码的机器可能未安装Excel
                Response.Write("未安装Excel!");
            }

            string templeFilePath = Server.MapPath("~/Template/客户发货单_Template.xls");

            Workbook workbook = xlsApp.Workbooks.Open(templeFilePath, Type.Missing, Type.Missing, Type.Missing,
                                                      Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                      Type.Missing, Type.Missing, Type.Missing, Type.Missing);

 

            string InvoceCode = r["Invoice_Code"].ToString();

            Worksheet worksheetCopy = workbook.Sheets[1] as Worksheet; //第一个sheet页
            string sheetName = InvoceCode + DateTime.Now.ToString("客户订购单") + "";

            worksheetCopy.Name = sheetName;  //这里修改sheet名称

            worksheetCopy.Cells[5, 1] = "NO:" + r["Invoice_Code"].ToString();
            worksheetCopy.Cells[8, 5] = r["CustomerName"].ToString();
            worksheetCopy.Cells[8, 10] = r["OrderCode"].ToString();
            worksheetCopy.Cells[9, 5] = r["Area"].ToString();
            worksheetCopy.Cells[9, 10] = r["Phone"].ToString();
            worksheetCopy.Cells[10, 5] = r["DeliveryAddress"].ToString();
            worksheetCopy.Cells[10, 10] = r["Post"].ToString();
            worksheetCopy.Cells[11, 10] ="'"+ r["CustomerCode"].ToString();

            worksheetCopy.Cells[25, 9] = "'" + r["ProductCount"].ToString();
            worksheetCopy.Cells[25, 12] = "'" + r["TotalAmount"].ToString();

            List<Excel.CheckBox> boxes = new List<Excel.CheckBox>();

            for (int i = 1; i <= 15; i++)
            {
                boxes.Add(worksheetCopy.CheckBoxes(i) as Excel.CheckBox);
            }

 

            Excel.CheckBox cbx = null;
            for (int i = 0; i < boxes.Count; i++)
            {
                cbx = boxes[i];
                if (cbx.Caption.Trim() == r["CustomerType"].ToString().Trim() || cbx.Caption.Trim() == r["IsReceived"].ToString().Trim () || cbx.Caption.Trim () == r["TransportWay"].ToString().Trim ())
                {
                    cbx.Value = true;
                }

            }

            strSQL = @"
                          select b.CASE_NUMBER as '箱号'
                                ,b.PRODUCT_PRODUCTDATE as '生产日期'
                                ,b.PRODUCT_CODE as '产品货号' 
                                ,b.PRODUCT_NAME as '产品名称'
                                ,b.PRODUCT_MODEL as '规格'
                                ,a.Product_Num as '数量'
                                ,b.PRODUCT_PRICE as '单价'
                                ,a.ZheKou as '折扣'
                                ,a.Amount as '金额'
                                from T_Order_Product a
                            left join T_Product b
                                on b.PRODUCT_CODE=a.Product_Code
                            where Order_Code='{0}' ";
            strSQL = string.Format(strSQL, r["OrderCode"].ToString());
            System.Data.DataTable _dt = ClsDbAccess.getDataSet(strSQL).Tables[0];

            int rowIndex = 15;
            foreach (System.Data.DataRow row in _dt.Rows)
            {
                worksheetCopy.Cells[rowIndex, 2] ="'"+ row["箱号"].ToString();
                worksheetCopy.Cells[rowIndex, 3] = "'" + row["生产日期"].ToString();
                worksheetCopy.Cells[rowIndex, 4] = "'" + row["产品货号"].ToString();
                worksheetCopy.Cells[rowIndex, 6] = "'" + row["产品名称"].ToString();
                worksheetCopy.Cells[rowIndex, 8] = row["规格"].ToString();
                worksheetCopy.Cells[rowIndex, 9] = "'"+row["数量"].ToString();
                worksheetCopy.Cells[rowIndex, 10] = "'"+row["单价"].ToString();
                worksheetCopy.Cells[rowIndex, 11] = "'" + row["折扣"].ToString();
                worksheetCopy.Cells[rowIndex, 12] = "'" + row["金额"].ToString();
                rowIndex++;
            }

           
           

           
            //另存为
            string filepath = fileURL;
            xlsApp.DisplayAlerts = true;
            xlsApp.AlertBeforeOverwriting = true;
            workbook.Saved = true;
            workbook.SaveCopyAs(filepath);

            //释放excel资源           
            workbook.Close(true, Type.Missing, Type.Missing);
            workbook = null;
            xlsApp.Quit();
            xlsApp = null;
            DownLoadFile(fileURL);
        }

      
    }

    /// <summary>
    /// 下载文件
    /// </summary>
    /// <param name="fileURL"></param>
    private void DownLoadFile(string fileURL)
    {
        System.IO.FileInfo fileInfo = new System.IO.FileInfo(fileURL);
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(fileInfo.Name.ToString()));
        Response.AddHeader("content-length", fileInfo.Length.ToString());
        Response.ContentType = "application/octet-stream";
        Response.ContentEncoding = System.Text.Encoding.Default;
        Response.WriteFile(fileURL);
    }