using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;

namespace CRM.Components
{
/// <summary>
/// Excel 的摘要说明


/// </summary>
    public class ToExcel
    {
        public ToExcel()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }


        public static int TabletoExcel(DataTable TB, string[] cols)
        {
            try
            {

                if (TB.Rows.Count == 0)
                {
                    //Response.Write("<script>alert('没有记录!!'</script>");
                    return 0;

                }

                Excel.Application xApp;//程序对象

                Excel.Workbook xBook; //工作簿



                Excel.Worksheet xSheet;//表单

                xApp = new Excel.ApplicationClass();//新建对象

                xApp.Visible = true;

                if (xApp == null)
                {

                    // Response.Write("<script>alert('创建对象出错，可能你没有安装office或你的office版本低'</script>");
                    return 0;

                }
                else
                {


                    // Response.Write("<script>alert('创建对象成功')</script>");
                    // return;
                }

                xBook = xApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

                xSheet = new Excel.Worksheet();

                xSheet = (Excel.Worksheet)xBook.Sheets[1];



                for (int i = 1; i <= cols.Length; i++)
                {

                    Excel.Range rng = (Excel.Range)xSheet.Cells[1, i];
                    rng.Value2 = cols[i - 1].ToString();
                }



                //开始对电子表格操作

                for (int i = 0; i <= TB.Rows.Count - 1; i++)
                {

                    for (int j = 1; j <= cols.Length; j++)
                    {

                        Excel.Range rngbh = (Excel.Range)xSheet.Cells[i + 2, j];

                        rngbh.Value2 = TB.Rows[i][j - 1].ToString();

                    }

                }


                xBook = null;
                xSheet = null;
                //  xApp.Quit();
                xApp = null;
                //Response.Write("<script>alert('导出到Excel文件成功')</script>");
                return 1;
            }
            catch (Exception e2)
            {
                throw e2;

            }
        }


        public static int TabletoExcel(DataTable TB)
        {


            if (TB.Rows.Count == 0)
            {
                //Response.Write("<script>alert('没有记录!!'</script>");
                return 0;

            }

            Excel.Application xApp;//程序对象

            Excel.Workbook xBook; //工作簿



            Excel.Worksheet xSheet;//表单

            xApp = new Excel.ApplicationClass();//新建对象

            xApp.Visible = true;

            if (xApp == null)
            {

                // Response.Write("<script>alert('创建对象出错，可能你没有安装office或你的office版本低'</script>");
                return 0;

            }
            else
            {


                // Response.Write("<script>alert('创建对象成功')</script>");
                // return;
            }

            xBook = xApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

            xSheet = new Excel.Worksheet();

            xSheet = (Excel.Worksheet)xBook.Sheets[1];

            xSheet.get_Range(xSheet.Cells[1, 1], xSheet.Cells[1, 1]).ColumnWidth = 20;

            xSheet.get_Range(xSheet.Cells[1, 2], xSheet.Cells[1, 2]).ColumnWidth = 20;

            xSheet.get_Range(xSheet.Cells[1, 3], xSheet.Cells[1, 3]).ColumnWidth = 20;
            //xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).ColumnWidth = 0; 


            for (int i = 1; i <= TB.Columns.Count; i++)
            {

                Excel.Range rng = (Excel.Range)xSheet.Cells[1, i];
                rng.Value2 = TB.Columns[i - 1].ColumnName;
            }



            //开始对电子表格操作

            for (int i = 0; i <= TB.Rows.Count - 1; i++)
            {

                for (int j = 1; j <= TB.Columns.Count; j++)
                {

                    Excel.Range rngbh = (Excel.Range)xSheet.Cells[i + 2, j];

                    rngbh.Value2 = TB.Rows[i][j - 1].ToString();

                }

            }


            xBook = null;
            xSheet = null;
            //  xApp.Quit();
            xApp = null;
            //Response.Write("<script>alert('导出到Excel文件成功')</script>");
            return 1;


        }


        /// <summary>
        /// 导出Excel
        /// </summary>

        public static void tableToExcel(DataTable tb)
        {



            FileStream file;
            StreamWriter filewrite;
            Random r = new Random();
            string t = r.NextDouble().ToString().Remove(0, 2);

            string path = System.Web.HttpContext.Current.Server.MapPath(".") + @"\tempdate\excel.xls";
            //System.Web.HttpContext.Current.Server.MapPath("tempdate") + "\\查询.xls";

            int i, j;
            //            Response.Write(FilePath);
            file = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);

            filewrite = new StreamWriter(file, System.Text.Encoding.Unicode);

            string strline = "";
            //表列名
            for (i = 1; i <= tb.Columns.Count; i++)
            {

                strline = strline + tb.Columns[i - 1].ColumnName.ToString() + Convert.ToChar(9);
            }

            filewrite.WriteLine(strline);

            //表内容

            for (i = 1; i <= tb.Rows.Count; i++)
            {
                strline = "";
                for (j = 1; j <= tb.Columns.Count; j++)
                {
                    strline = strline + tb.Rows[i - 1][j - 1].ToString() + Convert.ToChar(9);


                }
                filewrite.WriteLine(strline);
            }

            filewrite.Close();
            file.Close();


            string l_strHtml = "<script language='JavaScript'>";
            l_strHtml += " window.open('./tempdate/excel.xls','newwindow','height=800,width=1024,scrollbars=yes,resizable=yes,location=yes, status=yes,menubar=yes,toolbar=yes,titlebar=yes')";
            l_strHtml += "</script>";
            HttpContext.Current.Response.Write(l_strHtml);




            // window.showModalDialog('Activityinfo.aspx?activityID=1','newwindow','height=400,width=400,help=0;status=0;menubar=0;')

            // HttpContext.Current.Response.Redirect("查询.xls");
        }



        public static void tableToExcel(DataTable tb, string listname)
        {



            FileStream file;
            StreamWriter filewrite;
            Random r = new Random();
            string t = r.NextDouble().ToString().Remove(0, 2);

            string path = System.Web.HttpContext.Current.Server.MapPath(".") + @"\tempdate\excel.xls";
            int i, j;
            //            Response.Write(FilePath);
            file = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);

            filewrite = new StreamWriter(file, System.Text.Encoding.Unicode);

            string strline = "";

            filewrite.WriteLine(listname);

            //表内容

            for (i = 1; i <= tb.Rows.Count; i++)
            {
                strline = "";
                for (j = 1; j <= tb.Columns.Count; j++)
                {
                    strline = strline + tb.Rows[i - 1][j - 1].ToString() + Convert.ToChar(9);


                }
                filewrite.WriteLine(strline);
            }

            filewrite.Close();
            file.Close();


            string l_strHtml = "<script language='JavaScript'>";
            l_strHtml += " window.open('./tempdate/excel.xls','newwindow','height=800,width=1024,scrollbars=yes,resizable=yes,location=yes, status=yes,menubar=yes,toolbar=yes,titlebar=yes')";
            l_strHtml += "</script>";
            HttpContext.Current.Response.Write(l_strHtml);

            // window.showModalDialog('Activityinfo.aspx?activityID=1','newwindow','height=400,width=400,help=0;status=0;menubar=0;')

            // HttpContext.Current.Response.Redirect("查询.xls");
        }

        //listname 和 cols 个数　要相等 ，顺序一一对应
        public static void tableToExcel(DataTable tb, string[] listname, string[] cols)
        {


            if ((tb == null) || (tb.Rows.Count == 0))
            {
                return;
            }


            FileStream file;
            StreamWriter filewrite;
            Random r = new Random();
            string t = r.NextDouble().ToString().Remove(0, 2);

            string path = System.Web.HttpContext.Current.Server.MapPath(".") + @"\tempdate\excel.xls";

            int i, j;

            //            Response.Write(FilePath);
            file = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);

            filewrite = new StreamWriter(file, System.Text.Encoding.Unicode);

            string strline = "";
            for (i = 1; i <= listname.Length; i++)
            {
                strline = strline + listname[i - 1].ToString() + Convert.ToChar(9);

            }


            filewrite.WriteLine(strline);

            //表内容

            for (i = 1; i <= tb.Rows.Count; i++)
            {
                strline = "";



                //strline = strline + tb.Rows[i - 1][cols[0].ToString()].ToString() + Convert.ToChar(9);

                //strline = strline + tb.Rows[i - 1][cols[1].ToString()].ToString() + Convert.ToChar(9);


                for (j = 1; j <= cols.Length; j++)
                {
                    strline = strline + tb.Rows[i - 1][cols[j - 1].ToString()].ToString() + Convert.ToChar(9);


                }
                filewrite.WriteLine(strline);
            }

            filewrite.Close();
            file.Close();


            string l_strHtml = "<script language='JavaScript'>";
            l_strHtml += " window.open('./tempdate/excel.xls','newwindow','height=800,width=1024,scrollbars=yes,resizable=yes,location=yes, status=yes,menubar=yes,toolbar=yes,titlebar=yes')";
            l_strHtml += "</script>";
            HttpContext.Current.Response.Write(l_strHtml);


            // window.showModalDialog('Activityinfo.aspx?activityID=1','newwindow','height=400,width=400,help=0;status=0;menubar=0;')

            // HttpContext.Current.Response.Redirect("查询.xls");
        }



        public static void SendDataToExcel(DataTable dt)
        {
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            int colindex = 0;
            foreach (DataColumn col in dt.Columns)
            {
                colindex++;
                excel.Cells[1, colindex] = col.ColumnName;
            }
            int rowindex = 1;
            foreach (DataRow row in dt.Rows)
            {
                rowindex++;
                colindex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colindex++;
                    excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();
                }
            }
            excel.Visible = true;
        }
    }
}