Java uses POI to generate an excel table with a linked drop-down box.

Java uses POI to generate an excel table with a linked drop-down box.

JA learning record for an ava primary school student.

Import java. io. file; import java. io. fileNotFoundException; import java. io. fileOutputStream; import java. io. IOException; import java. util. arrayList; import java. util. arrays; import java. util. list; import org. apache. poi. hssf. usermodel. DVConstraint; import org. apache. poi. hssf. usermodel. HSSFCell; import org. apache. poi. hssf. usermodel. HSSFCellStyle; import org. apache. poi. hssf. usermodel. HSSFDataFormat; import org. ap Ache. poi. hssf. usermodel. HSSFDataValidation; import org. apache. poi. hssf. usermodel. HSSFFont; import org. apache. poi. hssf. usermodel. HSSFRow; import org. apache. poi. hssf. usermodel. HSSFSheet; import org. apache. poi. hssf. usermodel. HSSFWorkbook; import org. apache. poi. hssf. util. HSSFColor; import org. apache. poi. ss. usermodel. dataValidation; import org. apache. poi. ss. usermodel. name; import org. apache. poi. ss. util. cellRan GeAddressList; public class ExcelLinkage {// Style private HSSFCellStyle cellStyle; // initialize the province data private List <String> province = new ArrayList <String> (Arrays. asList ("Hunan", "Guangdong"); // initialize the data (Hunan City) private List <String> hnCity = new ArrayList <String> (Arrays. asList ("Changsha City", "Shaoyang City"); // initialization data (Guangdong City) private List <String> gdCity = new ArrayList <String> (Arrays. asList ("Shenzhen City", "Guangzhou City"); public void setDataCellStyles (H SSFWorkbook workbook, HSSFSheet sheet) {cellStyle = workbook. createCellStyle (); // set the border cellStyle. setBorderBottom (HSSFCellStyle. BORDER_THIN); cellStyle. setBorderLeft (HSSFCellStyle. BORDER_THIN); cellStyle. setBorderRight (HSSFCellStyle. BORDER_THIN); cellStyle. setBorderTop (HSSFCellStyle. BORDER_THIN); // sets the background color cellStyle. setFillForegroundColor (HSSFColor. LIGHT_GREEN.index); cellStyle. setFillPattern (HSSF CellStyle. SOLID_FOREGROUND); // sets the center cellStyle. setAlignment (HSSFCellStyle. ALIGN_LEFT); // set the font HSSFFont font = workbook. createFont (); font. setFontName (""); font. setFontHeightInPoints (short) 11); // set the cellStyle font size. setFont (font); // select the font format to be used. // set the cell format to text format (you can also set it to another format here, Baidu) HSSFDataFormat format = workbook. createDataFormat (); cellStyle. setDataFormat (format. getFormat ("@");}/*** create a data domain (Pull linked data) ** @ param workbook * @ param hideSheetName * data domain name */private void creatHideSheet (HSSFWorkbook workbook, String hideSheetName) {// create a data domain HSSFSheet sheet = workbook. createSheet (hideSheetName); // used to record the row int rowRecord = 0; // get the row (starting from 0 subscript) HSSFRow provinceRow = sheet. createRow (rowRecord); // create province data this. creatRow (provinceRow, province); // Insert the corresponding city information rowRecord ++ according to the province; for (int I = 0; I <province. Size (); I ++) {List <String> list = new ArrayList <String> (); // I am writing dead, in practice, we should directly obtain a better if (province. get (I ). toString (). equals ("Hunan") {// place the province name in the first column of the inserted city. list is used in name management. add (0, province. get (I ). toString (); list. addAll (hnCity);} else {list. add (0, province. get (I ). toString (); list. addAll (gdCity);} // get the row HSSFRow Cityrow = sheet. createRow (rowRecord); // create province data this. creatRow (Cityrow, list); ro WRecord ++ }}/ *** create a column of Data ** @ param currentRow * @ param textList */public void creatRow (HSSFRow currentRow, List <String> text) {if (text! = Null) {int I = 0; for (String cellValue: text) {// note that the column starts from the (1) subscript HSSFCell userNameLableCell = currentRow. createCell (I ++); userNameLableCell. setCellValue (cellValue) ;}}/ *** name management ** @ param workbook * @ param hideSheetName * sheet Name of the data field */private void creatExcelNameList (HSSFWorkbook workbook, String hideSheetName) {Name name; name = workbook. createName (); // set the province name. setNameName ("province" ); Name. setRefersToFormula (hideSheetName + "! $ A $1: $ "+ this. getcellColumnFlag (province. size () + "$1"); // set the saved city for (int I = 0; I <province. size (); I ++) {List <String> num = new ArrayList <String> (); if (province. get (I ). toString (). equals ("Hunan") {name = workbook. createName (); num. add (0, province. get (I ). toString (); num. addAll (hnCity); name. setNameName (province. get (I ). toString (); name. setRefersToFormula (hideSheetName + "! $ B $ "+ (I + 2) +": $ "+ this. getcellColumnFlag (num. size () + "$" + (I + 2);} else {name = workbook. createName (); num. add (0, province. get (I ). toString (); num. addAll (gdCity); name. setNameName (province. get (I ). toString (); name. setRefersToFormula (hideSheetName + "! $ B $ "+ (I + 2) +": $ "+ this. getcellColumnFlag (num. size () + "$" + (I + 2) ;}}// determine the cell location based on the data value (for example, 28-AB) private String getcellColumnFlag (int num) {String columFiled = ""; int chuNum = 0; int yuNum = 0; if (num> = 1 & num <= 26) {columFiled = this. doHandle (num);} else {chuNum = num/26; yuNum = num % 26; columFiled + = this. doHandle (chuNum); columFiled + = this. doHandle (yuNum);} return col UmFiled;} private String doHandle (final int num) {String [] charArr = {"A", "B", "C", "D", "E ", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O ", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y ", "Z"}; return charArr [num-1]. toString ();}/*** set a data verification using the defined data source method ** @ param formulaString * @ param naturalRowIndex * @ param naturalColumnIndex * @ return */public DataValidation getDataValidationB YFormula (String formulaString, int naturalRowIndex, int naturalColumnIndex) {// load the content of the drop-down list DVConstraint constraint = DVConstraint. Loads (formulaString. // The four parameters are: Start row, end row, start column, and end column int firstRow = naturalRowIndex; int lastRow = naturalRowIndex; int firstCol = naturalColumnIndex-1; int lastCol = naturalColumnIndex-1; CellRangeAddressList regions = new CellRangeAddressList (firstRow, lastRow, firstCol, lastCol); // Data Validity object DataValidation validation = new partition (regions, straint ); return rows;}/*** create a column of Data ** @ param hssfSheet */public void creatAppRow (HSSFSheet hssfSheet, int sheet) {// obtain the row HSSFRow hssfRow = hssfSheet. createRow (naturalRowIndex); HSSFCell province = hssfRow. createCell (0); province. setCellValue (""); province. setCellStyle (cellStyle); HSSFCell City = hssfRow. createCell (1); City. setCellValue (""); City. setCellStyle (cellStyle); // The verified object DataValidation data_validation_list1 = this. getDataValidationByFormula ("province", naturalRowIndex, 1); DataValidation data_validation_list2 = this. getDataValidationByFormula ("INDIRECT ($ A" + (naturalRowIndex + 1) + ")", naturalRowIndex, 2); // Add verification data hssfSheet to the worksheet. addValidationData (data_validation_list1); hssfSheet. addValidationData (data_validation_list2);} public void Export () {try {File file = new File ("F:/excel.xls"); FileOutputStream outputStream = new FileOutputStream (file ); // create an excel HSSFWorkbook workbook = new HSSFWorkbook (); // set the sheet name HSSFSheet excelSheet = workbook. createSheet ("excel"); // sets the style this. setDataCellStyles (workbook, excelSheet); // create a hidden page and hide the dataset this. creatHideSheet (workbook, "shutDataSource"); // set the name dataset this. creatExcelNameList (workbook, "shutDataSource"); // create a row of data for (int I = 0; I <50; I ++) {this. creatAppRow (excelSheet, I);} workbook. write (outputStream); outputStream. close ();} catch (FileNotFoundException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace () ;}} public static void main (String [] args) {ExcelLinkage linkage = new ExcelLinkage (); linkage. export ();}}