using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
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.IO;
using Aspose.Cells;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Runtime;
using System.Text;


namespace asposecelldemo
{

    /// <summary>
    /// 
    /// </summary>
    public partial class _Default : System.Web.UI.Page
    {


        DataTable getData()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("name", typeof(string));
            dt.Rows.Add(1, "geovindu");
            dt.Rows.Add(2, "geov");
            dt.Rows.Add(3, "塗斯博");
            dt.Rows.Add(4, "趙雅芝");
            dt.Rows.Add(5, " なわち日本語");
            dt.Rows.Add(6, "처리한다");
            dt.Rows.Add(7, "涂聚文");
            dt.Rows.Add(8, "塗聚文");
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.GridView1.DataSource = getData();
                this.GridView1.DataBind();

            }
        }
        /// <summary>
        /// http://www.aspose.com/docs/display/cellsnet/Importing+Data+to+Worksheets
        /// </summary>
        /// <param name="table"></param>
        private void ExporttoExcelExcel(DataTable table, string fileName)
        {
            //Instantiate a new Workbook
            Workbook book = new Workbook();
            //Clear all the worksheets
            book.Worksheets.Clear();
            //Add a new Sheet "Data";
            Worksheet worksheet = book.Worksheets.Add("Data");
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            worksheet.Cells.ImportDataTable(table, true, "A1");
            context.Response.Buffer = true;  
            context.Response.ContentType = "application/ms-excel";
            context.Response.Charset = "utf-8";
            context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            context.Response.BinaryWrite(book.SaveToStream().ToArray());
            context.Response.Flush();
            context.Response.End();
                     
        }      

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fileName"></param>
        protected void ExportToExcel(DataTable dataTable, string fileName)
        {
            
            HttpContext context = HttpContext.Current;
            StringBuilder sb = new StringBuilder();

            //foreach (DataColumn column in dataTable.Columns)
            //{
            //    context.Response.Write(column.ColumnName + ",");
            //}
            //context.Response.Write(Environment.NewLine);
 
            //foreach (DataRow row in dataTable.Rows)
            //{
            //    for (int i = 0; i < dataTable.Columns.Count; i++)
            //    {
            //        context.Response.Write(row[i].ToString() + ",");
            //    }
            //    context.Response.Write(Environment.NewLine);
            //} 此法亚洲语言用会出现乱码
            foreach (DataColumn column in dataTable.Columns)
            {
                sb.Append(column.ColumnName + ",");
            }
            sb.Append(Environment.NewLine);

            foreach (DataRow row in dataTable.Rows)
            {
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    sb.Append(row[i].ToString() + ",");
                }
                sb.Append(Environment.NewLine);
            }


            StringWriter sw = new StringWriter(sb);
            sw.Close();
            context.Response.Clear();
            context.Response.Buffer = true;
            context.Response.Charset = "utf-8";
            context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
            context.Response.HeaderEncoding = System.Text.Encoding.UTF8;
            context.Response.ContentType = "text/csv";  
            //context.Response.ContentType = "application/ms-excel";
            context.Response.BinaryWrite(new byte[] { 0xEF, 0xBB, 0xBF });
            context.Response.Write(sw);
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName,System.Text.Encoding.UTF8).Replace("+", "%20")+ ".csv");//亂碼
            context.Response.Flush();  
            context.Response.End();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ExportToExcel(getData(), "塗聚文" + DateTime.Now.ToString("yyyyMMddHHmmssfff"));
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button2_Click(object sender, EventArgs e)
        {
            ExporttoExcelExcel(getData(),"geovindu"+DateTime.Now.ToString("yyyyMMddHHmmssfff"));
        }

      
    }

}

        /// <summary>
        /// http://www.aspose.com/docs/display/cellsnet/Importing+Data+to+Worksheets
        /// </summary>
        /// <param name="table"></param>
        private void ExporttoExcelExcel(DataTable table, string fileName,int type)
        {
            //Instantiate a new Workbook
            Workbook book = new Workbook();
           // book.Save("",SaveFormat.Xlsx);
            //Clear all the worksheets
            book.Worksheets.Clear();
            //Add a new Sheet "geovindu";
            Worksheet worksheet = book.Worksheets.Add("geovindu");
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            worksheet.Cells.ImportDataTable(table, true, "A1");
           
            context.Response.Buffer = true;
            if (type == 1)
            {
                context.Response.ContentType = "application/ms-excel";  //2003
                context.Response.Charset = "utf-8";
                context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
                context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
                context.Response.BinaryWrite(book.SaveToStream().ToArray());
                context.Response.Flush();
                context.Response.End();
            }
            
            if (type == 2)
            {
                //1.//不可以 ,擴展名出現問題
                //context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  // 2007
                //context.Response.Charset = "utf-8";
                //context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
                //context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
                ////book.Save("", SaveFormat.Xlsx);
                //context.Response.BinaryWrite(book.SaveToStream().ToArray());             

                ////Save with default format, send the file to user so that he may open the file in
                ////some application or save it to some location
                //// book.Save(this.Response, "importeddata.xlsx", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsx));      
                //context.Response.Flush();
                //context.Response.End();

                //2.
                string sb = DataTabletoHmtl(table);
               // string sb = toHTML_Table(table);
                byte[] array = Encoding.UTF8.GetBytes(sb.ToString());
                MemoryStream ms = new MemoryStream(array);
                LoadOptions lo = new LoadOptions(LoadFormat.Html);
                book = new Workbook(ms, lo);
                worksheet = book.Worksheets.Add("geovindu");
                book.Save(Response, fileName + ".xlsx", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsx));
            }
               

            
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string DataTabletoHmtl(DataTable dt)
        {
            if (dt.Rows.Count == 0)
                return "";

            string tab = "\t";

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("<html>");
            sb.AppendLine(tab + "<body>");//不帶HTML頭,會顯示格式問題.塗聚文註
            sb.AppendLine(tab + tab + "<table>");

            // headers.
            sb.Append(tab + tab + tab + "<tr>");

            foreach (DataColumn dc in dt.Columns)
            {
                sb.AppendFormat("<td>{0}</td>", dc.ColumnName);
            }

            sb.AppendLine("</tr>");

            // data rows
            foreach (DataRow dr in dt.Rows)
            {
                sb.Append(tab + tab + tab + "<tr>");

                foreach (DataColumn dc in dt.Columns)
                {
                    string cellValue = dr[dc] != null ? dr[dc].ToString() : "";
                    sb.AppendFormat("<td>{0}</td>", cellValue);
                }

                sb.AppendLine("</tr>");
            }

            sb.AppendLine(tab + tab + "</table>");
            sb.AppendLine(tab + "</body>");
            sb.AppendLine("</html>");
            return sb.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string toHTML_Table(DataTable dt)
        {
            if (dt.Rows.Count == 0)
                return "";

            StringBuilder builder = new StringBuilder();
            builder.Append("<html>");
            builder.Append("<head>");
            builder.Append("<title>");
            builder.Append("Page-");
            builder.Append(Guid.NewGuid().ToString());
            builder.Append("</title>");
            builder.Append("</head>");
            builder.Append("<body>");
            builder.Append("<table border='1px' cellpadding='5' cellspacing='0' ");//有樣式,會提示有格式問題. 塗聚文註
            builder.Append("style='border: solid 1px Silver; font-size: x-small;'>");
            builder.Append("<tr align='left' valign='top'>");
            foreach (DataColumn c in dt.Columns)
            {
                builder.Append("<td align='left' valign='top'><b>");
                builder.Append(c.ColumnName);
                builder.Append("</b></td>");
            }
            builder.Append("</tr>");
            foreach (DataRow r in dt.Rows)
            {
                builder.Append("<tr align='left' valign='top'>");
                foreach (DataColumn c in dt.Columns)
                {
                    builder.Append("<td align='left' valign='top'>");
                    builder.Append(r[c.ColumnName]);
                    builder.Append("</td>");
                }
                builder.Append("</tr>");
            }
            builder.Append("</table>");
            builder.Append("</body>");
            builder.Append("</html>");

            return builder.ToString();
        }

        public string ConvertDataTableToHTMLTableInOneLine(DataTable dt)
        {
            //Convert DataTable To HTML Table in one line
            return "<table>\n<tr>" + string.Join("", dt.Columns.Cast<DataColumn>().Select(dc => "<td>" + dc.ColumnName + "</td>")) + "</tr>\n" +
            "<tr>" + string.Join("</tr>\n<tr>", dt.AsEnumerable().Select(row => "<td>" + string.Join("</td><td>", row.ItemArray) + "</td>").ToArray()) + "</tr>\n<\table>";

        }

  /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filename"></param>
        /// <param name="listHeader"></param>
        /// <returns></returns>
        public static string ExportExcel(DataTable dt, string filename, List<string> listHeader)
        {
            try
            {
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿
                Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表
                Aspose.Cells.Cells cells = sheet.Cells;//单元格
                Aspose.Cells.Style style;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cells[0, j].PutValue(listHeader[j]);

                    style = cells[0, j].GetStyle();
                    style.BackgroundColor = System.Drawing.Color.Blue;
                    style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
                    style.Pattern = BackgroundType.Solid;
                    cells[0, j].SetStyle(style);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cells[i + 1, j].PutValue(dt.Rows[i][j].ToString());
                    }
                }

                sheet.AutoFitColumns();
                cells.SetRowHeight(0, 30);
                workbook.Save(filename);

                return "";
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }

        }
        /// <summary>
        /// geovindu 涂聚文
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="response"></param>
        //private static void Export<T>(IEnumerable<T> data, HttpResponse response, string filename)
        //{
        //    Workbook workbook = new Workbook();
        //    Worksheet sheet = (Worksheet)workbook.Worksheets[0];

        //    PropertyInfo[] ps = typeof(T).GetProperties();
        //    var colIndex = "A";

        //    foreach (var p in ps)
        //    {

        //        sheet.Cells[colIndex + 1].PutValue(p.Name);
        //        int i = 2;
        //        foreach (var d in data)
        //        {
        //            sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
        //            i++;
        //        }

        //        colIndex = ((char)(colIndex[0] + 1)).ToString();
        //    }

        //    response.Clear();
        //    response.Buffer = true;
        //    response.Charset = "utf-8";
        //    response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
        //    response.ContentEncoding = System.Text.Encoding.UTF8;
        //    response.ContentType = "application/ms-excel";
        //    response.BinaryWrite(workbook.SaveToStream().ToArray());
        //    response.End();
        //}
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="context"></param>
        /// <param name="file"></param>
        public void ProcessRequest(DataTable dt, HttpContext context,string file)
        {


            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

                // Header
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName);
                }
                // Content
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        sheet.Cells[i + 1, j].PutValue(dt.Rows[i][j].ToString());
                    }
                }

                context.Response.Clear();
                context.Response.Buffer = true;
                context.Response.Charset = "utf-8";
                //context.Response.ContentType = "text/plain";
                context.Response.AddHeader("Content-Disposition",
                                           "attachment;filename=" + context.Server.UrlEncode(file)); //"数据.xlsx"
                context.Response.ContentEncoding = System.Text.Encoding.UTF8;
                context.Response.ContentType = "application/ms-excel";
                //    context.Response.BinaryWrite(workbook.SaveToStream().ToArray());
                //    context.Response.End();

                workbook.Save(context.Response.OutputStream, SaveFormat.Xlsx);
            
        }