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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160

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

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153

/// <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

?

1 2 3 4 5 6 7

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

　　

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123

/// <summary>       ///       /// </summary>       /// <param name="dt"></param>       /// <param name="filename"></param>       /// <param name="listHeader"></param>       /// <returns></returns>       public static string ExportExcel(DataTable dt, string filename, List<string> listHeader)       {           try           {               Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //工作簿               Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; //工作表               Aspose.Cells.Cells cells = sheet.Cells;//单元格               Aspose.Cells.Style style;               for (int j = 0; j < dt.Columns.Count; j++)               {                   cells[0, j].PutValue(listHeader[j]);                     style = cells[0, j].GetStyle();                   style.BackgroundColor = System.Drawing.Color.Blue;                   style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);                   style.Pattern = BackgroundType.Solid;                   cells[0, j].SetStyle(style);               }               for (int i = 0; i < dt.Rows.Count; i++)               {                   for (int j = 0; j < dt.Columns.Count; j++)                   {                       cells[i + 1, j].PutValue(dt.Rows[i][j].ToString());                   }               }                 sheet.AutoFitColumns();               cells.SetRowHeight(0, 30);               workbook.Save(filename);                 return "";           }           catch (Exception ex)           {               return ex.ToString();           }         }       /// <summary>       /// geovindu 涂聚文       /// </summary>       /// <typeparam name="T"></typeparam>       /// <param name="data"></param>       /// <param name="response"></param>       //private static void Export<T>(IEnumerable<T> data, HttpResponse response, string filename)       //{       //    Workbook workbook = new Workbook();       //    Worksheet sheet = (Worksheet)workbook.Worksheets[0];         //    PropertyInfo[] ps = typeof(T).GetProperties();       //    var colIndex = "A";         //    foreach (var p in ps)       //    {         //        sheet.Cells[colIndex + 1].PutValue(p.Name);       //        int i = 2;       //        foreach (var d in data)       //        {       //            sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));       //            i++;       //        }         //        colIndex = ((char)(colIndex[0] + 1)).ToString();       //    }         //    response.Clear();       //    response.Buffer = true;       //    response.Charset = "utf-8";       //    response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");       //    response.ContentEncoding = System.Text.Encoding.UTF8;       //    response.ContentType = "application/ms-excel";       //    response.BinaryWrite(workbook.SaveToStream().ToArray());       //    response.End();       //}       /// <summary>       ///       /// </summary>       /// <param name="dt"></param>       /// <param name="context"></param>       /// <param name="file"></param>       public void ProcessRequest(DataTable dt, HttpContext context,string file)       {               Workbook workbook = new Workbook();           Worksheet sheet = workbook.Worksheets[0];                 // Header               for (int i = 0; i < dt.Columns.Count; i++)               {                   sheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName);               }               // Content               for (int i = 0; i < dt.Rows.Count; i++)               {                   for (int j = 0; j < dt.Columns.Count; j++)                   {                       sheet.Cells[i + 1, j].PutValue(dt.Rows[i][j].ToString());                   }               }                 context.Response.Clear();               context.Response.Buffer = true;               context.Response.Charset = "utf-8";               //context.Response.ContentType = "text/plain";               context.Response.AddHeader("Content-Disposition",                                          "attachment;filename=" + context.Server.UrlEncode(file)); //"数据.xlsx"               context.Response.ContentEncoding = System.Text.Encoding.UTF8;               context.Response.ContentType = "application/ms-excel";               //    context.Response.BinaryWrite(workbook.SaveToStream().ToArray());               //    context.Response.End();                 workbook.Save(context.Response.OutputStream, SaveFormat.Xlsx);                   }