Poi realizes Excel to pull the linkage

/** * * @File Name:CreateExcelMoBusiness.java * @Create on:2011-02-12 11:48:453 * @Author: zhys513 * @Chan Gelist *---------------------------------------------------* Date Editor changereasons * */I
Mport Java.io.FileOutputStream;

Import Java.util.HashMap;
Import Org.apache.poi.hssf.usermodel.DVConstraint;
Import org.apache.poi.hssf.usermodel.HSSFDataValidation;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import Org.apache.poi.ss.usermodel.Cell;
Import Org.apache.poi.ss.usermodel.CellStyle;
Import org.apache.poi.ss.usermodel.DataValidation;
Import Org.apache.poi.ss.usermodel.Font;
Import org.apache.poi.ss.usermodel.IndexedColors;
Import Org.apache.poi.ss.usermodel.Name;
Import Org.apache.poi.ss.usermodel.Row;
Import Org.apache.poi.ss.usermodel.Sheet;
Import Org.apache.poi.ss.usermodel.Workbook;
 
 
 

Import org.apache.poi.ss.util.CellRangeAddressList; public class Createexcelmobusiness {private static String Excel_hide_sheet_name = "Excelhidesheetname";   
	private static String Hide_sheet_name_sex = "Sexlist";  

	private static String hide_sheet_name_province = "Provincelist";
    Private HashMap map = new HashMap ();   
	Set the contents of the Drop-down list private static string[] Sexlist = {"Male", "female"};     
	private static string[] Provincelist = {"Zhejiang", "Shandong", "Jiangxi", "Jiangsu", "Sichuan"};   
	private static string[] Zjprovincelist = {"Zhejiang", "Hangzhou", "Ningbo", "Wenzhou"};   
	private static string[] Sdprovincelist = {"Shandong", "Jinan", "Qingdao", "Yantai"};   
	private static string[] Jxprovincelist = {"Jiangxi", "Nanchang", "Xinyu", "Yingtan", "Fuzhou"};  
	private static string[] Jsprovincelist = {"Jiangsu", "Nanjing", "Suzhou", "Wuxi"}; 
	
	private static string[] Scprovincelist = {"Sichuan", "Chengdu", "Mianyang", "Zigong"};   
		public static void Main (string[] args) {//use case Workbook WB = new Hssfworkbook ();
		Createexcelmo (WB);   
		Createxcelhidepage (WB);  
        Setdatavalidation (WB);
		FileOutputStream fileout;
	        try {fileout = new FileOutputStream ("D://excel_template.xls");   
	        Wb.write (fileout); fileout.clOSE ();
		catch (Exception e) {e.printstacktrace ();   
	        } public static void Createexcelmo (Workbook wb) {Sheet Sheet = wb.createsheet ("User classification Add Batch guide"); Create a row and put some cells in it.   
	        Rows are 0 based.   
	        Row row = Sheet.createrow (0);   
	        Cell cell = Row.createcell (0);   
	        Cell.setcellvalue ("mobile number");   
	        Cell.setcellstyle (WB) (Gettitlestyle);   
	        Cell = Row.createcell (1);   
	        Cell.setcellvalue ("parent class");   
	        Cell.setcellstyle (WB) (Gettitlestyle);   
	        Cell = Row.createcell (2);   
	        Cell.setcellvalue ("subordinate subclass");   
	        Cell.setcellstyle (WB) (Gettitlestyle);   
	Cell = Row.createcell (3); /** * Set the horizontal header cell style for the template file * @param WB * @return/private static CellStyle Gettitl   
        Estyle (Workbook wb) {CellStyle style = Wb.createcellstyle ();   
        Alignment setting Style.setalignment (Cellstyle.align_center); Border color and Width setPlacing Style.setborderbottom (Cellstyle.border_thin);   
        Style.setbottombordercolor (IndexedColors.BLACK.getIndex ());   
        Style.setborderleft (Cellstyle.border_thin);   
        Style.setleftbordercolor (IndexedColors.BLACK.getIndex ());   
        Style.setborderright (Cellstyle.border_thin);   
        Style.setrightbordercolor (IndexedColors.BLACK.getIndex ());   
        Style.setbordertop (Cellstyle.border_thin);   
        Style.settopbordercolor (IndexedColors.BLACK.getIndex ());   
        Style.setfillbackgroundcolor (IndexedColors.GREY_25_PERCENT.getIndex ());   
        Set Background color Style.setfillforegroundcolor (IndexedColors.GREY_25_PERCENT.getIndex ());   
        Style.setfillpattern (Cellstyle.solid_foreground);   
        Font font = Wb.createfont () in bold character setting;   
        Font.setboldweight (Font.boldweight_bold);   
        Style.setfont (font);   
    return style; /** * Sets the horizontal header cell style for the template file * @param WB * @return 
     */public static void Createxcelhidepage (Workbook workbook) {Sheet Hideinfosheet = workbook.creates Heet (excel_hide_sheet_name)//Hide some information//in the hidden page settings Select information//The first line set the gender information row Sexrow = Hideinfosheet.   
        CreateRow (0);   
        Creatrow (Sexrow, sexlist);   
        The second line sets the province Name list row Provincenamerow = Hideinfosheet.createrow (1);   
        Creatrow (Provincenamerow, provincelist);   
        The following line sets the city Name list row Citynamerow = Hideinfosheet.createrow (2);   
        
        Creatrow (Citynamerow, zjprovincelist);   
        Citynamerow = Hideinfosheet.createrow (3);   
           
        Creatrow (Citynamerow, sdprovincelist);   
        Citynamerow = Hideinfosheet.createrow (4);   
           
        Creatrow (Citynamerow, jxprovincelist);   
        Citynamerow = Hideinfosheet.createrow (5);   
           
        Creatrow (Citynamerow, jsprovincelist);   
        Citynamerow = Hideinfosheet.createrow (6); Creatrow (CitynamErow, scprovincelist);   
        Name Management//first line set up gender information createxcelnamelist (workbook, Hide_sheet_name_sex, 1, sexlist.length, false);   
        The second line sets the province Name list createxcelnamelist (Workbook, Hide_sheet_name_province, 2, Provincelist.length, false);   
        After dynamic size set the province corresponds to the city list createxcelnamelist (workbook, Provincelist[0], 3, zjprovincelist.length, true);   
        Createxcelnamelist (Workbook, Provincelist[1], 4, sdprovincelist.length, true);   
        Createxcelnamelist (Workbook, Provincelist[2], 5, jxprovincelist.length, true);   
        Createxcelnamelist (Workbook, Provincelist[3], 6, jsprovincelist.length, true);   
        Createxcelnamelist (Workbook, Provincelist[4], 7, scprovincelist.length, true);   
    Sets the hidden page flag Workbook.setsheethidden (Workbook.getsheetindex (Excel_hide_sheet_name), true); /** * Create a name * @param workbook * * private static void Createxcelnamelist (workbook Workbook, String namecode,int order,int Size,boolean cascadeflag) {name name;   
        Name = Workbook.createname ();   
        Name.setnamename (Namecode); Name.setreferstoformula (excel_hide_sheet_name+ "!")   
    +createxcelnamelist (Order,size,cascadeflag)); /** * Name data row and column calculation expression * @param workbook */private static String createxcelnamelist (i   
        NT Order,int Size,boolean cascadeflag) {char start = ' A ';   
            if (cascadeflag) {start = ' B ';   
                if (size<=25) {char end = (char) (start+size-1);   
            Return "$" +start+ "$" +order+ ": $" +end+ "$" +order;   
                }else{char endprefix = ' A ';   
                Char endsuffix = ' A '; if ((size-25)/26==0| |  size==51) {//26-51, including boundary (only two alphanumeric calculations) if ((size-25)%26==0) {//boundary value Endsuffix = (char)   
                    (' A ' +25);   
               }else{         Endsuffix = (char) (' A ' + (size-25)%26-1); }}ELSE{//51 above if ((size-25)%26==0) {Endsuffix = (char   
                        ) (' A ' +25);   
                    Endprefix = (char) (Endprefix + (size-25)/26-1);   
                        }else{endsuffix = (char) (' A ' + (size-25)%26-1);   
                    Endprefix = (char) (Endprefix + (size-25)/26);   
            } return "$" +start+ "$" +order+ ": $" +endprefix+endsuffix+ "$" +order;   
                }}else{if (size<=26) {char end = (char) (start+size-1);   
            Return "$" +start+ "$" +order+ ": $" +end+ "$" +order;   
                }else{char endprefix = ' A ';   
                Char endsuffix = ' A ';   
                    if (size%26==0) {endsuffix = (char) (' A ' +25); if (Size>52&& size/26>0) {endprefix = (char) (Endprefix + size/26-2);   
                    }}else{Endsuffix = (char) (' A ' +size%26-1);   
                    if (size>52&&size/26>0) {endprefix = (char) (Endprefix + size/26-1);   
            } return "$" +start+ "$" +order+ ": $" +endprefix+endsuffix+ "$" +order;  
     /** * Create a column of data * @param currentrow * @param textlist * * private static void Creatrow (Row currentrow,string[] textlist) {if (textlist!=null&&textlist.le   
            ngth>0) {int i = 0;   
                for (String cellvalue:textlist) {Cell Usernamelablecell = Currentrow.createcell (i++);   
            Usernamelablecell.setcellvalue (Cellvalue); }}/** * Add data validation option * @param Sheet */public static void Setdatavalidation (Workbook wb) {int sheetindex = Wb.getnumberofsheets   
        ();   
                if (sheetindex>0) {for (int i=0;i<sheetindex;i++) {Sheet Sheet = Wb.getsheetat (i); if (!
                    Excel_hide_sheet_name.equals (Sheet.getsheetname ())) {datavalidation data_validation_list = null; Province option Add validation data for (int a=2;a<3002;a++) {data_validation_list = Get   
	                    Datavalidationbyformula (hide_sheet_name_province,a,2);   
	                    Sheet.addvalidationdata (data_validation_list);   
	                    City option Add validation data Data_validation_list = Getdatavalidationbyformula ("INDIRECT (B" +a+ ")", a,3);   
	                    Sheet.addvalidationdata (data_validation_list);   
	Gender add validation Data Data_validation_list = Getdatavalidationbyformula (hide_sheet_name_sex,a,1);                    Sheet.addvalidationdata (data_validation_list); /** * Using the defined data source to set a data validation * @param formulas Tring * @param naturalrowindex * @param naturalcolumnindex * @return * * private static D Atavalidation Getdatavalidationbyformula (String formulastring,int naturalrowindex,int naturalColumnIndex) {//load    
        Drop-down list content Dvconstraint constraint = Dvconstraint.createformulalistconstraint (formulastring);     
        Sets the cell on which the data validation is loaded.   
        The four parameters are: Starting row, terminating row, starting column, terminating column int firstrow = naturalRowIndex-1;   
        int lastrow = naturalRowIndex-1;   
        int firstcol = naturalColumnIndex-1;   
        int lastcol = naturalColumnIndex-1;     
        Cellrangeaddresslist regions=new cellrangeaddresslist (Firstrow,lastrow,firstcol,lastcol); Data validation Object DataValidation data_validation_list = new HssfdatAvalidation (Regions,constraint); Set the input message Data_validation_list.createpromptbox ("Drop-down selection Prompt", "Select the appropriate value using the Drop-down.")   
        "); Set input error message Data_validation_list.createerrorbox ("Select Error Prompt", "you entered the value is not in the alternative list, please drop down to select the appropriate value.")   
        ");   
    return data_validation_list;   
        private static datavalidation getdatavalidationbydate (int naturalrowindex,int naturalcolumnindex) { Load drop-down list content dvconstraint constraint = Dvconstraint.createdateconstraint (DVConstraint.OperatorType.BETWEEN, "1    
        900-01-01 "," 5000-01-01 "," yyyy-mm-dd ");     
        Sets the cell on which the data validation is loaded.   
        The four parameters are: Starting row, terminating row, starting column, terminating column int firstrow = naturalRowIndex-1;   
        int lastrow = naturalRowIndex-1;   
        int firstcol = naturalColumnIndex-1;   
        int lastcol = naturalColumnIndex-1;     
        Cellrangeaddresslist regions=new cellrangeaddresslist (Firstrow,lastrow,firstcol,lastcol); Data validation Object DataValidation Data_validation_list = new hssfdatavalidation (regions,constraint); Set the input information prompt data_validation_list.createpromptbox (date format hint, "Please enter the date value in the ' YYYY-MM-DD ' format.)   
        "); Set input error message Data_validation_list.createerrorbox ("Date format error hint", "The date format you entered does not conform to the ' YYYY-MM-DD ' format specification, please re-enter.")   
        ");   
    return data_validation_list;
 }    
}