Use aspose. cell to dynamically export multi-header EXCEL, aspose. cellexcel

Source: Internet
Author: User
Tags id3

Use aspose. cell to dynamically export multi-header EXCEL, aspose. cellexcel

:

 

Foreground call:

Using System; using System. collections. generic; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; using System. data; using ExportCells; namespace WebApplication1 {public partial class _ Default: System. web. UI. page {protected void Page_Load (object sender, EventArgs e) ************* * ******** // set the column List <ExportCells. asposeHelper. jqxTableColumns> columns = new List <ExportCells. asposeHelper. jqxTableColumns> (); columns. add (new ExportCells. asposeHelper. jqxTableColumns () {text = "id"}); columns. add (new ExportCells. asposeHelper. jqxTableColumns () {text = "name", columngroup = "namesex"}); columns. add (new ExportCells. asposeHelper. jqxTableColumns () {text = "sex", columngroup = "namesex"}); columns. add (new ExportCells. asposeHelper. jqxTableColumns () {text = "id2"}); columns. add (new ExportCells. asposeHelper. jqxTableColumns () {text = "cat", columngroup = "Animal"}); columns. add (new ExportCells. asposeHelper. jqxTableColumns () {text = "dog", columngroup = "Animal"}); columns. add (new ExportCells. asposeHelper. jqxTableColumns () {text = "rabbit", columngroup = "Animal"}); columns. add (new ExportCells. asposeHelper. jqxTableColumns () {text = "id3"}); // sets the group List <ExportCells. asposeHelper. jqxTableColumnsGroup> group = new List <ExportCells. asposeHelper. jqxTableColumnsGroup> (); group. add (new ExportCells. asposeHelper. jqxTableColumnsGroup () {name = "Animal", text = "Animal"}); group. add (new ExportCells. asposeHelper. jqxTableColumnsGroup () {name = "namesex", text = "name gender"}); // sets the data DataTable dt = new DataTable (); dt. columns. add ("id"); dt. columns. add ("name"); dt. columns. add ("sex"); dt. columns. add ("id2"); dt. columns. add ("cat"); dt. columns. add ("dog"); dt. columns. add ("rabbit"); dt. columns. add ("id3"); var dr = dt. newRow (); dr [0] = 0; dr [1] = 1; dr [2] = 2; dr [3] = 3; dr [4] = 4; dr [5] = 5; dr [6] = 6; dr [7] = 7; dt. rows. add (dr); var dr2 = dt. newRow (); dr2 [0] = 10; dr2 [1] = 11; dr2 [2] = 12; dr2 [3] = 13; dr2 [4] = 14; dr2 [5] = 15; dr2 [6] = 16; dr2 [7] = 17; dt. rows. add (dr2); AsposeHelper. saveColumnsHierarchy ("1.xls", columns, group, dt );}}}

  

 

ASPOSE encapsulation class

Using System; using System. collections. generic; using System. linq; using System. text; using Aspose. cells; using System. data; using System. drawing; using System. web; namespace ExportCells {// <summary> /// ** Description: Aspose /// ** Creation Time: // ** modification time: -// ** modifier: sunkaixuan // ** usage instructions: /// </summary> public class AsposeHelper {// <summary> // export the EXCEL file and dynamically generate a multi-level header. /// </summary> /// <param name = "columns"> Column </param> /// <param name = "group"> group </param> /// <param name = "dt"> dataTable </param> // /<param name = "path"> Save path </param> public static void SaveColumnsHierarchy (List <JqxTableColumns> columns, list <JqxTableColumnsGroup> group, DataTable dt, string path) {Workbook workbook = new Workbook (); // workbook Worksheet sheet = Workbook. worksheets [0]; // worksheet Cells = sheet. cells; // cell for (int I = 0; I <= dt. Rows. count + 1; I ++) {sheet. cells. setRowHeight (I, 30) ;}list <AsposeCellInfo> acList = new List <AsposeCellInfo> (); List <string> acColumngroupHistoryList = new List <string> (); int currentX = 0; foreach (var it in columns) {AsposeCellInfo ac = new AsposeCellInfo (); ac. y = 0; if (it. columngroup = null) {ac. text = it. text; ac. x = currentX; ac. xCount = 1; acList. add (ac); currentX ++; ac. yCount = 2;} Else if (! AcColumngroupHistoryList. contains (it. columngroup) // prevents repeated {var sameCount = columns. where (itit => itit. columngroup = it. columngroup ). count (); ac. text = group. first (itit => itit. name = it. columngroup ). text; ac. x = currentX; ac. xCount = sameCount; acList. add (ac); currentX = currentX + sameCount; acColumngroupHistoryList. add (it. columngroup); ac. yCount = 1; ac. groupName = it. columngroup;} else {// none Logic }}// header foreach (var it in acList) {cells. merge (it. y, it. x, it. yCount, it. xCount); // merge cells [it. y, it. x]. putValue (it. text); // enter the content of cells [it. y, it. x]. setStyle (_ thStyle); if (! String. isNullOrEmpty (it. groupName) {var cols = columns. where (itit => itit. columngroup = it. groupName ). toList (); foreach (var itit in cols) {var colsIndex = cols. indexOf (itit); cells [it. y + 1, it. x + colsIndex]. putValue (itit. text); // enter the content of cells [it. y + 1, it. x + colsIndex]. setStyle (_ thStyle) ;}}// table if (dt! = Null & dt. rows. count> 0) {var rowList = dt. asEnumerable (). toList (); foreach (var it in rowList) {int dtIndex = rowList. indexOf (it); var dtColumns = dt. columns. cast <DataColumn> (). toList (); foreach (var itit in dtColumns) {var dtColumnsIndex = dtColumns. indexOf (itit); cells [2 + dtIndex, dtColumnsIndex]. putValue (it [dtColumnsIndex]); cells [2 + dtIndex, dtColumnsIndex]. setStyle (_ tdStyle) ;}}} w Orkbook. save (path );} /// <summary> /// export an EXCEL file and dynamically generate a multi-level header. /// </summary> /// <param name = "columns"> column </param>/ // <param name = "group"> group </param> /// <param name = "dt"> dataTable </param> /// <param name = "path "> Save path </param> public static void SaveColumnsHierarchy (string fileName, list <JqxTableColumns> columns, List <JqxTableColumnsGroup> group, DataTable dt) {Workbook workbook = new Workbook (); // Workbook Wo Rksheet sheet = workbook. worksheets [0]; // worksheet Cells = sheet. cells; // cell for (int I = 0; I <= dt. rows. count + 1; I ++) {sheet. cells. setRowHeight (I, 30) ;}list <AsposeCellInfo> acList = new List <AsposeCellInfo> (); List <string> acColumngroupHistoryList = new List <string> (); int currentX = 0; foreach (var it in columns) {AsposeCellInfo ac = new AsposeCellInfo (); ac. y = 0; if (it. columngroup = Null) {ac. text = it. text; ac. x = currentX; ac. xCount = 1; acList. add (ac); currentX ++; ac. yCount = 2;} else if (! AcColumngroupHistoryList. contains (it. columngroup) // prevents repeated {var sameCount = columns. where (itit => itit. columngroup = it. columngroup ). count (); ac. text = group. first (itit => itit. name = it. columngroup ). text; ac. x = currentX; ac. xCount = sameCount; acList. add (ac); currentX = currentX + sameCount; acColumngroupHistoryList. add (it. columngroup); ac. yCount = 1; ac. groupName = it. columngroup;} else {// none Logic }}// header foreach (var it in acList) {cells. merge (it. y, it. x, it. yCount, it. xCount); // merge cells [it. y, it. x]. putValue (it. text); // enter the content of cells [it. y, it. x]. setStyle (_ thStyle); if (! String. isNullOrEmpty (it. groupName) {var cols = columns. where (itit => itit. columngroup = it. groupName ). toList (); foreach (var itit in cols) {var colsIndex = cols. indexOf (itit); cells [it. y + 1, it. x + colsIndex]. putValue (itit. text); // enter the content of cells [it. y + 1, it. x + colsIndex]. setStyle (_ thStyle) ;}}// table if (dt! = Null & dt. rows. count> 0) {var rowList = dt. asEnumerable (). toList (); foreach (var it in rowList) {int dtIndex = rowList. indexOf (it); var dtColumns = dt. columns. cast <DataColumn> (). toList (); foreach (var itit in dtColumns) {var dtColumnsIndex = dtColumns. indexOf (itit); cells [2 + dtIndex, dtColumnsIndex]. putValue (it [dtColumnsIndex]); cells [2 + dtIndex, dtColumnsIndex]. setStyle (_ tdStyle) ;}}var response = HttpContext. current. response; response. clear (); response. buffer = true; response. charset = "UTF-8"; response. appendHeader ("Content-Disposition", "attachment; filename =" + fileName); response. contentEncoding = System. text. encoding. UTF8; response. contentType = "application/ms-excel"; response. binaryWrite (workbook. saveToStream (). toArray (); response. end () ;}private static Style _ thStyle {get {Style s = new Style (); s. font. isBold = true; s. font. name = ""; s. font. color = Color. black; s. horizontalAlignment = TextAlignmentType. center; // return s;} private static Style _ tdStyle {get {Style s = new Style (); return s ;}} public class JqxTableColumns {public string field {get; set;} public string cellsAlign {get; set;} public string align {get; set;} public string text {get; set ;} public string columngroup {get; set;} public class JqxTableColumnsGroup {public string text {get; set;} public string align {get; set;} public string name {get; set ;}} public class AsposeCellInfo {public string text {get; set ;}public int x {get; set ;}public int xCount {get; set ;} public int y {get; set;} public int yCount {get; set;} public string groupName {get; set ;}}}}

  

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.