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