poi下載模板含下拉框

第一種方法

新建QuestionsImport類import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.usermodel.*;import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.util.List;public class QuestionsImport {/*** @param* @param filePath Excel文件路徑* @param* @param headers Excel列標題(數組)* @param* @param downData 下拉框數據(數組)* @param* @param downRows 下拉列的序號(數組,序號從0開始)* @return void* @throws String filePath, String sheetTitle, List<String> headers, Map<String,String[]> map, String[] fatherOption* @Title: testFunction* @Description: 生成Excel導入模板*/public static void createExcelTemplate(String filePath, String sheetTitle, List<String> headers, List<String[]> downData, List<String> downRows) throws IOException {// 創建一個excel@SuppressWarnings("resource")XSSFWorkbook book = new XSSFWorkbook();CellStyle style = book.createCellStyle();// 創建一個居中格式style.setAlignment(HorizontalAlignment.CENTER);// 創建需要用戶填寫的sheetXSSFSheet sheetPro = book.createSheet(sheetTitle);Row row0 = sheetPro.createRow(0);Cell cell1 = row0.createCell((short) 0);//row0.setHeight((short) 400);for (int i = 0; i < headers.size(); i++) {cell1.setCellValue(headers.get(i));cell1.setCellStyle(style);//設置單元格寬度sheetPro.setColumnWidth(i, 24 * 256);//設置居中樣式sheetPro.setDefaultColumnStyle(i, style);cell1 = row0.createCell((short) i + 1);}//因此也不能在現實頁之前創建，否則無法隱藏。XSSFSheet hideSheet = book.createSheet("hideSheet");//這一行作用是將此sheet隱藏，功能未完成時注釋此行,可以查看隱藏sheet中信息是否正確book.setSheetHidden(book.getSheetIndex(hideSheet), true);GetNewExcelTemplate(filePath, downData, downRows, sheetPro, hideSheet, book);}/*** @param filePath 生成的模板文件路徑* @param downData 下拉框數據集合* @param downRows 設置列的序號集合(從0開始)*/public static void GetNewExcelTemplate(String filePath, List<String[]> downData, List<String> downRows, XSSFSheet sheetPro, XSSFSheet hideSheet, XSSFWorkbook workbook) throws IOException {//修改excle表的數據// 設置下拉框數據if (downData.size() > 0 && downRows.size() > 0) {String[] arr = {"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"};int index = 0;//數據行Row row = null;for (int r = 0; r < downRows.size(); r++) {// 獲取下拉對象String[] dlData = downData.get(r);int rownum = Integer.parseInt(downRows.get(r));// 255以內的下拉if (dlData.length < 5) {// 255以內的下拉,參數分別是：作用的sheet、下拉內容數組、起始行、終止行、起始列、終止列// 超過255個報錯sheetPro.addValidationData(setDataValidation(sheetPro, dlData, 1, 5000, rownum, rownum));} else {// 255以上的下拉，即下拉列表元素很多的情況// 1、設置有效性// String strFormula = "Sheet2!$A$1:$A$5000" ;// //Sheet2第A1到A5000作為下拉列表來源數據// Sheet2第A1到A5000作為下拉列表來源數據String strFormula = "hideSheet!$" + arr[index] + "$1:$" + arr[index] + "$" + (dlData.length + 1);// 設置每列的列寬hideSheet.setColumnWidth(r, 4000);// 設置數據有效性加載在哪個單元格上,參數分別是：從sheet2獲取A1到A5000作為一個下拉的數據、起始行、終止行、起始列、終止列// 下拉列表元素很多的情況sheetPro.addValidationData(SetDataValidation(workbook, hideSheet, ("data" + r), strFormula, 1, 5000, rownum, rownum));// 2、生成sheet2內容for (int j = 0; j < dlData.length; j++) {// 第1個下拉選項，直接創建行、列if (index == 0) {// 創建數據行row = hideSheet.createRow(j);// 設置每列的列寬hideSheet.setColumnWidth(j, 4000);// 設置對應單元格的值row.createCell(0).setCellValue(dlData[j]);} else {// 非第1個下拉選項int rowCount = hideSheet.getLastRowNum();// 前面創建過的行，直接獲取行，創建列if (j <= rowCount) {// 獲取行，創建列// 設置對應單元格的值hideSheet.getRow(j).createCell(index).setCellValue(dlData[j]);} else {// 未創建過的行，直接創建行、創建列// 設置每列的列寬hideSheet.setColumnWidth(j, 4000);// 創建行、創建列// 設置對應單元格的值hideSheet.createRow(j).createCell(index).setCellValue(dlData[j]);}}}index++;}}}//將修改后的文件寫出到D:\\excel目錄下// 寫文件File f = new File(filePath);// 不存在則新增if (!f.getParentFile().exists()) {f.getParentFile().mkdirs();}if (!f.exists()) {f.createNewFile();}FileOutputStream out = new FileOutputStream(f);out.flush();workbook.write(out);//關閉流out.close();}/*** @param workbook 當前工作簿* @param sheet 存放下拉菜單數據的sheet* @param dataName 數據有效性名稱* @param strFormula 下拉菜單取數據范圍* @param firstRow* @param endRow* @param firstCol* @param endCol* @return* @Description: xlsx格式 255以上下拉菜單*/private static XSSFDataValidation SetDataValidation(XSSFWorkbook workbook, XSSFSheet sheet, String dataName, String strFormula, int firstRow, int endRow, int firstCol, int endCol) {XSSFName name = workbook.createName();name.setNameName(dataName);name.setRefersToFormula(strFormula);XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(dataName);CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);validation.setSuppressDropDownArrow(true);return validation;}/*** @Title: setDataValidation @Description:* 下拉列表元素不多的情況(255以內的下拉) @param @param sheet @param @param* textList @param @param firstRow @param @param endRow @param @param* firstCol @param @param endCol @param @return @return* DataValidation @throws*/private static XSSFDataValidation setDataValidation(XSSFSheet sheet, String[] textList, int firstRow, int endRow,int firstCol, int endCol) {XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textList);CellRangeAddressList addressList = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);return validation;}/*** @Title: downFile* @Description:* @param @param url文件url* @param @param fileName 文件名* @param @param response* @return void* @throws*/public static void downFile(String url, String fileName, HttpServletRequest request, HttpServletResponse response) {try {//1.定義ContentType為("multipart/form-data")讓瀏覽器自己解析文件格式response.setContentType("multipart/form-data");//2.中文名轉碼response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xlsx");//獲得文件File file = new File(url);FileInputStream in = new FileInputStream(file);//3.將文件寫入緩沖區OutputStream(out)OutputStream out = new BufferedOutputStream(response.getOutputStream());int b = 0;byte[] buffer = new byte[2048];while ((b=in.read(buffer)) != -1){//4.將緩沖區文件輸出到客戶端(out)out.write(buffer,0,b);}in.close();out.flush();out.close();} catch (IOException e) {e.printStackTrace();}} }接口調用 @GetMapping("/download")public Object download1(HttpServletRequest request, HttpServletResponse response) {// 讀取模板String excelPath = request.getSession().getServletContext().getRealPath("/question/poi/questions123.xls");String fileName = "題庫表"; //模板名稱String[] headers = {"類型", "題目(不能重復,注意中英文)", "圖片", "分值", "正確答案", "選項A", "選項B", "選項C", "選項D"};List<String> headerList = new ArrayList<>();for (int i = 0; i < headers.length; i++) {headerList.add(headers[i]);}//下拉框數據List<String[]> downData = new ArrayList();//數據所在列List<String> downRows = Lists.newArrayList();String[] str = {"單選", "多選", "判斷", "填空", "簡答"};downData.add(str);downRows.add("0");try {//生成待下載excel模板QuestionsImport.createExcelTemplate(excelPath, fileName, headerList, downData, downRows);QuestionsImport.downFile(excelPath, fileName, request, response);//刪除臨時文件File delFile = new File(excelPath);if (delFile.exists()) {delFile.delete();}} catch (Exception e) {System.out.println("批量導入信息異常：" + e.getMessage());}return null;}

第二種方法

另外一種工具類寫法import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.util.CellRangeAddressList;import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.*;public class ExcelTest1 {//0開始private static final int XLS_MAX_ROW = 65535;private static final String MAIN_SHEET_NAME = "題庫";private static final String HIDDEN_SHEET1_NAME = "hidden1";private static final String DEVICE_NAMES = "devices";/*** 創建入庫excel模版* @param filePath* @param headers* @param devices* @return** @throws IOException*/private static File createStoreInExcelTemplate(String filePath, List<String> headers, List<String> devices) throws IOException {FileOutputStream out = null;File file;try {//寫文件file = new File(filePath); // file = File.createTempFile("入庫數據模版", ".xls");out = new FileOutputStream(file);//創建工作薄HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet mainSheet = wb.createSheet(MAIN_SHEET_NAME);HSSFSheet dtHiddenSheet = wb.createSheet(HIDDEN_SHEET1_NAME);//將第二個用于存儲下拉框數據的sheet隱藏wb.setSheetHidden(1, true);initHeaders01(wb, mainSheet, headers);initDevicesAndType02(wb, dtHiddenSheet, devices);initSheetNameMapping04(mainSheet);out.flush();wb.write(out); // file.deleteOnExit();} catch (Exception e) {e.printStackTrace();throw e;} finally {if (out != null) {try {out.close();} catch (IOException e) {e.printStackTrace();}}}return file;}private static void initDevicesAndType02(HSSFWorkbook wb, HSSFSheet dtHiddenSheet, List<String> devices) {writeDevices02_01(wb, dtHiddenSheet, devices);initDevicesNameMapping02_03(wb, dtHiddenSheet.getSheetName(), devices.size());}private static void writeDevices02_01(HSSFWorkbook wb, HSSFSheet dtHiddenSheet, List<String> devices) {for (int i = 0; i < devices.size(); i++) {HSSFRow row = dtHiddenSheet.createRow(i);HSSFCell cell1 = row.createCell(0);cell1.setCellValue(devices.get(i));}}private static void initDevicesNameMapping02_03(HSSFWorkbook workbook, String dtHiddenSheetName, int deviceQuantity) {Name name = workbook.createName();// 設置選項“名稱”name.setNameName(DEVICE_NAMES);name.setRefersToFormula(dtHiddenSheetName + "!$A$1:$A$" + deviceQuantity);}/*** 初始化表頭** @param wb* @param mainSheet* @param headers*/private static void initHeaders01(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {//表頭樣式HSSFCellStyle style = wb.createCellStyle();// 創建一個居中格式style.setAlignment(HorizontalAlignment.CENTER);//字體樣式HSSFFont fontStyle = wb.createFont();fontStyle.setFontName("微軟雅黑");fontStyle.setFontHeightInPoints((short) 12);fontStyle.setBold(true);style.setFont(fontStyle);//生成sheet1內容HSSFRow rowFirst = mainSheet.createRow(0);//第一個sheet的第一行為標題mainSheet.createFreezePane(0, 1, 0, 1); //凍結第一行//寫標題for (int i = 0; i < headers.size(); i++) {HSSFCell cell = rowFirst.createCell(i); //獲取第一行的每個單元格mainSheet.setColumnWidth(i, 4000); //設置每列的列寬cell.setCellStyle(style); //加樣式cell.setCellValue(headers.get(i)); //往單元格里寫數據}}/*** 主sheet中下拉框初始化** @param mainSheet*/private static void initSheetNameMapping04(HSSFSheet mainSheet) {DataValidation deviceValidation = getDataValidationByFormula04_01(DEVICE_NAMES, 0);// 主sheet添加驗證數據mainSheet.addValidationData(deviceValidation);}/*** 生成下拉框及提示** @param formulaString* @param columnIndex* @return*/public static DataValidation getDataValidationByFormula04_01(String formulaString, int columnIndex) {// 加載下拉列表內容DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);// 設置數據有效性加載在哪個單元格上。// 四個參數分別是：起始行、終止行、起始列、終止列CellRangeAddressList regions = new CellRangeAddressList(1, XLS_MAX_ROW, columnIndex, columnIndex);// 數據有效性對象DataValidation dataValidationList = new HSSFDataValidation(regions, constraint);dataValidationList.createErrorBox("Error", "請選擇或輸入有效的選項，或下載最新模版重試！");return dataValidationList;}public static File test() throws IOException {List<String> headers = Arrays.asList("類型", "問題(不能重復,注意中英文)", "圖片", "分值", "正確答案", "選項A", "選項B", "選項C", "選項D");//下拉框List<String> devices = Arrays.asList("單選", "多選", "判斷", "填空", "問答");return ExcelTest1.createStoreInExcelTemplate("D:/test"+getTimeStamp()+".xls", headers,devices);}public static void main(String[] args) throws IOException {ExcelTest1.test();}public static String getTimeStamp(){SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddhhmmssSSS");return sdf.format(new Date());} }

總結

以上是生活随笔為你收集整理的poi下载模板含下拉框的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。