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

时间：2021-03-16 08:32:50
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();

        }

　net4.0

        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>";

        }

　　

相关文章