Java code implementation nested hierarchy list, POI export nested hierarchy list

To implement the POI export Excel shape as

--A1 (LV1)

----B1 (LV2)

----B2 (LV2)

------C1 (LV3)

------C2 (LV3)

----B3 (LV2)

--A1 (LV1)

I. Data fields that can be used

ID (primary key), PID (parent account ID), name (name), level (current hierarchy)

Two. Ideas:

Define an empty list of accounts to hold an already sequenced account. Create an iterator to the parent node to isolate all of its child nodes, traverse the child nodes into the list of accounts, and call the iterator again, so that the program can insert a node if it finds that a child node continues to insert a child node. Finally, it returns the well-ordered account tree.

Three. Java code

/*** * <p>description: Get nested account tree based on root node </p> *@paramroot root node, just ItemCode *@returnNested account Tree*/ PublicList<subject>getsubjectlistbyroot (Subject root) {//define an empty list of accountsList<subject> Subjecttree =NewArraylist<subject>(); //querying the root node for specific informationRoot = This. Subjectmanager.searchsubjectbycode (Root.getitemcode ()); //Insert the root node into the nested account tree because the iterated account tree is not a root nodeSubjecttree.add (root); //Call iteratorSubjecttree =subjectiteration (root, Subjecttree); returnSubjecttree;}/*** * <p>description: Nested account Tree iterator </p> *@paramSubject Parent Node *@paramsubjecttree Account Tree *@returnNested account Tree*/ PublicList<subject> subjectiteration (Subject Subject, list<subject>Subjecttree) {    //Look up child nodes based on parent nodelist<subject> subjects = This. subjectmanager.searchlistsubjectbyparent (subject); if(Subjects! =NULL) {        //Traverse child nodes and iterate         for(Subject subjectresult:subjects) {//inserting a node into a nested account treeSubjecttree.add (Subjectresult);        Subjectiteration (Subjectresult,subjecttree); }    }    returnSubjecttree;}

Four. SQL

A section of SQL that is based on the parent node is only required to compare the parent node's ID and the PID of the child node.

Five. POI Export

/*** * <p>description: Account Tree Export </p> *@paramRequest *@paramResponse *@returnExcel File *@throwsException*/ PublicModelandview Downloadexcel (httpservletrequest request, httpservletresponse response)throwsException {//File ConfigurationString FileName =NewString (("Account export file. xls"). GetBytes ("UTF-8"), "Iso-8859-1");    Response.reset (); Response.setheader ("Content-disposition", "attachment;filename=" +fileName); Response.setcontenttype ("Application/vnd.ms-excel;charset=utf-8"); //Create a Poi-workbook fileXssfworkbook Workbook =NewXssfworkbook (); Xssfsheet sheet= Workbook.createsheet ("Account Tree"); //Insert HeaderPOIUTIL.ADDROWSXSSF (sheet, 0,NewString[] {"Description:", "Level1", "Level2", "Level3", "Level4", "Level5", "Level6",            "Level7", "Level8" }); //Adjust the sheet style//RedCellStyle stylered =Workbook.createcellstyle ();    Stylered.setfillpattern (Xssfcellstyle.solid_foreground);    Stylered.setfillforegroundcolor (IndexedColors.RED.index); //GreenCellStyle Stylegreen =Workbook.createcellstyle ();    Stylegreen.setfillpattern (Xssfcellstyle.solid_foreground);    Stylegreen.setfillforegroundcolor (IndexedColors.LIME.index); //GreyCellStyle Stylegrey =Workbook.createcellstyle ();    Stylegrey.setfillpattern (Xssfcellstyle.solid_foreground);    Stylegrey.setfillforegroundcolor (IndexedColors.GREY_25_PERCENT.index); //WhiteCellStyle Stylewhite =Workbook.createcellstyle ();    Stylewhite.setfillpattern (Xssfcellstyle.solid_foreground);    Stylewhite.setfillforegroundcolor (IndexedColors.WHITE.index); //GoldenCellStyle Stylegold =Workbook.createcellstyle ();    Stylegold.setfillpattern (Xssfcellstyle.solid_foreground);    Stylegold.setfillforegroundcolor (IndexedColors.GOLD.index); //YellowCellStyle Styleyellow =Workbook.createcellstyle ();    Styleyellow.setfillpattern (Xssfcellstyle.solid_foreground);    Styleyellow.setfillforegroundcolor (IndexedColors.YELLOW.index); //Bright yellowCellStyle Stylelight =Workbook.createcellstyle ();    Stylelight.setfillpattern (Xssfcellstyle.solid_foreground);    Stylelight.setfillforegroundcolor (IndexedColors.LIGHT_YELLOW.index); //LemonCellStyle Stylelemon =Workbook.createcellstyle ();    Stylelemon.setfillpattern (Xssfcellstyle.solid_foreground);    Stylelemon.setfillforegroundcolor (IndexedColors.LEMON_CHIFFON.index); //Set Header colorXssfrow row = Sheet.getrow (0); Cell Cell= Row.getcell (1);    Cell.setcellstyle (stylered); Cell= Row.getcell (2);    Cell.setcellstyle (Stylegreen); Cell= Row.getcell (3);    Cell.setcellstyle (Stylegrey); Cell= Row.getcell (4);    Cell.setcellstyle (Stylewhite); Cell= Row.getcell (5);    Cell.setcellstyle (Stylegold); Cell= Row.getcell (6);    Cell.setcellstyle (Styleyellow); Cell= Row.getcell (7);    Cell.setcellstyle (Stylelight); Cell= Row.getcell (8);    Cell.setcellstyle (Stylelemon); //query Excel with account treeList<subject> Subjecttree =NewArraylist<subject>(); Subject Root=NewSubject (); Root.setitemcode ("1001"); Subjecttree=getsubjectlistbyroot (root); //Inserting Data    intindex = 0;  for(Subject subject:subjecttree) {index= index + 1; Row DataRow=Sheet.createrow (index); String LV=Subject.getlevel (); if(Lv.equals ("1") {Cell Datacell= Datarow.createcell (0);            Datacell.setcellvalue (Subject.getitemname ());        Datacell.setcellstyle (stylered); }        Else if(Lv.equals ("2") {Cell Datacell= Datarow.createcell (0);            Datacell.setcellvalue (Subject.getitemname ());        Datacell.setcellstyle (Stylegreen); }        Else if(Lv.equals ("3") {Cell Datacell= Datarow.createcell (0);            Datacell.setcellvalue (Subject.getitemname ());        Datacell.setcellstyle (Stylegrey); }        Else if(Lv.equals ("4") {Cell Datacell= Datarow.createcell (0);            Datacell.setcellvalue (Subject.getitemname ());        Datacell.setcellstyle (Stylewhite); }        Else if(Lv.equals ("5") {Cell Datacell= Datarow.createcell (0);            Datacell.setcellvalue (Subject.getitemname ());        Datacell.setcellstyle (Stylegold); }        Else if(Lv.equals ("6") {Cell Datacell= Datarow.createcell (0);            Datacell.setcellvalue (Subject.getitemname ());        Datacell.setcellstyle (Styleyellow); }        Else if(Lv.equals ("7") {Cell Datacell= Datarow.createcell (0);            Datacell.setcellvalue (Subject.getitemname ());        Datacell.setcellstyle (Stylelight); }        Else if(Lv.equals ("8") {Cell Datacell= Datarow.createcell (0);            Datacell.setcellvalue (Subject.getitemname ());        Datacell.setcellstyle (Stylelemon); }} sheet.setcolumnwidth (0, 9000); //Create an output stream, generate a fileOutputStream out =NewBufferedoutputstream (Response.getoutputstream ());    Workbook.write (out);    Out.flush ();    Out.close (); return NULL;}

Java code implementation nested hierarchy list, POI export nested hierarchy list