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

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