您好,登錄后才能下訂單哦!
本篇文章為大家展示了如何在Java中利用POI生成一個帶聯(lián)動下拉框的excel表格,內(nèi)容簡明扼要并且容易理解,絕對能使你眼前一亮,通過這篇文章的詳細介紹希望你能有所收獲。
具體代碼如下所示:
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.apache.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.CellRangeAddressList; public class ExcelLinkage { // 樣式 private HSSFCellStyle cellStyle; // 初始化省份數(shù)據(jù) private List<String> province = new ArrayList<String>(Arrays.asList("湖南", "廣東")); // 初始化數(shù)據(jù)(湖南的市區(qū)) private List<String> hnCity = new ArrayList<String>(Arrays.asList("長沙市", "邵陽市")); // 初始化數(shù)據(jù)(廣東市區(qū)) private List<String> gdCity = new ArrayList<String>(Arrays.asList("深圳市", "廣州市")); public void setDataCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) { cellStyle = workbook.createCellStyle(); // 設(shè)置邊框 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 設(shè)置背景色 cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 設(shè)置居中 cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 設(shè)置字體 HSSFFont font = workbook.createFont(); font.setFontName("宋體"); font.setFontHeightInPoints((short) 11); // 設(shè)置字體大小 cellStyle.setFont(font);// 選擇需要用到的字體格式 // 設(shè)置單元格格式為文本格式(這里還可以設(shè)置成其他格式,可以自行百度) HSSFDataFormat format = workbook.createDataFormat(); cellStyle.setDataFormat(format.getFormat("@")); } /** * 創(chuàng)建數(shù)據(jù)域(下拉聯(lián)動的數(shù)據(jù)) * * @param workbook * @param hideSheetName * 數(shù)據(jù)域名稱 */ private void creatHideSheet(HSSFWorkbook workbook, String hideSheetName) { // 創(chuàng)建數(shù)據(jù)域 HSSFSheet sheet = workbook.createSheet(hideSheetName); // 用于記錄行 int rowRecord = 0; // 獲取行(從0下標開始) HSSFRow provinceRow = sheet.createRow(rowRecord); // 創(chuàng)建省份數(shù)據(jù) this.creatRow(provinceRow, province); // 根據(jù)省份插入對應(yīng)的市信息 rowRecord++; for (int i = 0; i < province.size(); i++) { List<String> list = new ArrayList<String>(); // 我這里是寫死的 , 實際中應(yīng)該從數(shù)據(jù)庫直接獲取更好 if (province.get(i).toString().equals("湖南")) { // 將省份名稱放在插入市的第一列, 這個在后面的名稱管理中需要用到 list.add(0, province.get(i).toString()); list.addAll(hnCity); } else { list.add(0, province.get(i).toString()); list.addAll(gdCity); } //獲取行 HSSFRow Cityrow = sheet.createRow(rowRecord); // 創(chuàng)建省份數(shù)據(jù) this.creatRow(Cityrow, list); rowRecord++; } } /** * 創(chuàng)建一列數(shù)據(jù) * * @param currentRow * @param textList */ public void creatRow(HSSFRow currentRow, List<String> text) { if (text != null) { int i = 0; for (String cellValue : text) { // 注意列是從(1)下標開始 HSSFCell userNameLableCell = currentRow.createCell(i++); userNameLableCell.setCellValue(cellValue); } } } /** * 名稱管理 * * @param workbook * @param hideSheetName * 數(shù)據(jù)域的sheet名 */ private void creatExcelNameList(HSSFWorkbook workbook, String hideSheetName) { Name name; name = workbook.createName(); // 設(shè)置省名稱 name.setNameName("province"); name.setRefersToFormula(hideSheetName + "!$A$1:$" + this.getcellColumnFlag(province.size())+ "$1"); // 設(shè)置省下面的市 for (int i = 0; i < province.size(); i++) { List<String> num = new ArrayList<String>(); if (province.get(i).toString().equals("湖南")) { 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)); } } } // 根據(jù)數(shù)據(jù)值確定單元格位置(比如: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 columFiled; } 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(); } /** * 使用已定義的數(shù)據(jù)源方式設(shè)置一個數(shù)據(jù)驗證 * * @param formulaString * @param naturalRowIndex * @param naturalColumnIndex * @return */ public DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex) { // 加載下拉列表內(nèi)容 DVConstraint constraint = DVConstraint .createFormulaListConstraint(formulaString); // 設(shè)置數(shù)據(jù)有效性加載在哪個單元格上。 // 四個參數(shù)分別是:起始行、終止行、起始列、終止列 int firstRow = naturalRowIndex; int lastRow = naturalRowIndex; int firstCol = naturalColumnIndex - 1; int lastCol = naturalColumnIndex - 1; CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); // 數(shù)據(jù)有效性對象 DataValidation data_validation_list = new HSSFDataValidation(regions, constraint); return data_validation_list; } /** * 創(chuàng)建一列數(shù)據(jù) * * @param hssfSheet */ public void creatAppRow(HSSFSheet hssfSheet, int naturalRowIndex) { // 獲取行 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); // 得到驗證對象 DataValidation data_validation_list1 = this.getDataValidationByFormula( "province", naturalRowIndex, 1); DataValidation data_validation_list2 = this .getDataValidationByFormula("INDIRECT($A" + (naturalRowIndex + 1) + ")", naturalRowIndex, 2); // 工作表添加驗證數(shù)據(jù) hssfSheet.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); // 創(chuàng)建excel HSSFWorkbook workbook = new HSSFWorkbook(); // 設(shè)置sheet 名稱 HSSFSheet excelSheet = workbook.createSheet("excel"); // 設(shè)置樣式 this.setDataCellStyles(workbook, excelSheet); // 創(chuàng)建一個隱藏頁和隱藏數(shù)據(jù)集 this.creatHideSheet(workbook, "shutDataSource"); // 設(shè)置名稱數(shù)據(jù)集 this.creatExcelNameList(workbook, "shutDataSource"); // 創(chuàng)建一行數(shù)據(jù) 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(); } }
上述內(nèi)容就是如何在Java中利用POI生成一個帶聯(lián)動下拉框的excel表格,你們學(xué)到知識或技能了嗎?如果還想學(xué)到更多技能或者豐富自己的知識儲備,歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。