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