你的位置:首页 > ASP.net教程

[ASP.net教程]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.open

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