Csharp: asp.net Importing or Exporting Data from Worksheets using aspose cell, csharpimporting

Csharp: asp.net Importing or Exporting Data from Worksheets using aspose cell, csharpimporting

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, "SBO"); dt. rows. add (4, ""); dt. rows. add (5, "Japanese"); dt. rows. add (6, "Zookeeper"); dt. rows. add (7, "tu juwen"); 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 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. importable able (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); //} This method may contain garbled 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 "); // specify encryption context. response. flush (); context. response. end ();} protected void button#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 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; c Ontext. response. clear (); worksheet. cells. importable able (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. // No. The problem is displayed. // 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. app EndHeader ("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 Xl SSaveOptions (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 (Respo Neuron, 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 ("
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>";        }