• csharp:asp.net Importing or Exporting Data from Worksheets using aspose cell


    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 = "	";
    
                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();
            }
    

     net4.0

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

      

  • 相关阅读:
    delphi datetimepicker 修改时间无效问题
    C#写入文件
    C# winform中listview排序
    C/C++,python,java,C#月经贴问题
    Redis Sentinel 原理简单介绍
    JVM垃圾收集 知识汇总
    Java HotSpot VM Options
    Spring三级缓存解决循环依赖
    Markdown基础用法
    join示例分析
  • 原文地址:https://www.cnblogs.com/geovindu/p/5728013.html
Copyright © 2020-2023  润新知