java apache poi excel sheet validation example ( 엑셀 시트 유효성 검사 추가 )

다음 [그림1] 과 같이 다운로드용 엑셀서식에 입력 유효성 검사가 필요해서 가이드를 참고하여 작성해 봤다.

그림 1

몇해 전에 해 봤던 것 같은데, 기억이 나면 이상한 일이 된 나이가 되어버렸다.

마지막에 소스 다운로드가 있다.

https://poi.apache.org/components/spreadsheet/quick-guide.html

Busy Developers' Guide to HSSF and XSSF Features

일련번호, 사번, 성명, 출근상황의 셀이 존재하고, 출근상황을 제외하고 제목부분 포함 수정 불가하며, 출근상황만 콤보박스로 수정 가능하도록 했다.

엉성하지만 동작하면 된다.

apache poi는 3.9를 사용했다.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

@RequestMapping(value = "/excel", method = {RequestMethod.POST})
public void surb01001Excel(@RequestParam Map<String, Object> paramMap, HttpServletRequest request, HttpServletResponse response) throws IOException, ParseException {

    List<Map<String,Object>> dataList = (List<Map<String,Object>>) paramMap.get("dataList");
    /*if (logger.isDebugEnabled()) {
        logger.debug("dataList -----> {}", dataList);
    }*/

    String[] headers = {"일련번호","사번","성명","출근상황"};
    XSSFWorkbook wb = new XSSFWorkbook();

    XSSFSheet sheet = (XSSFSheet) wb.createSheet("Sheet1");

    sheet.setColumnWidth(0,2000);
    sheet.setColumnWidth(1,3000);
    sheet.setColumnWidth(2,3000);
    sheet.setColumnWidth(3,2500);

    XSSFDataValidationHelper dataValidationHelper = new XSSFDataValidationHelper(sheet);

    XSSFDataValidationConstraint headerDataValidationConstraint  = (XSSFDataValidationConstraint)dataValidationHelper.createExplicitListConstraint(new String[]{""});
    CellRangeAddressList headerCellRangeAddressList = new CellRangeAddressList(0, 0, 0, 3);

    XSSFDataValidation headerDataValidation = (XSSFDataValidation)dataValidationHelper.createValidation(headerDataValidationConstraint, headerCellRangeAddressList);
    headerDataValidation.setSuppressDropDownArrow(false);
    headerDataValidation.createErrorBox("경고","수정할 수 없습니다.");
    headerDataValidation.setShowErrorBox(true);
    headerDataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
    headerDataValidation.setEmptyCellAllowed(false);

    XSSFDataValidationConstraint bodyDataValidationConstraint  = (XSSFDataValidationConstraint)dataValidationHelper.createExplicitListConstraint(new String[]{""});
    CellRangeAddressList bodyCellRangeAddressList = new CellRangeAddressList(1, dataList.size(), 0, 2);

    XSSFDataValidation bodyDataValidation = (XSSFDataValidation)dataValidationHelper.createValidation(bodyDataValidationConstraint, bodyCellRangeAddressList);
    bodyDataValidation.setSuppressDropDownArrow(false);
    bodyDataValidation.createErrorBox("경고","수정할 수 없습니다.");
    bodyDataValidation.setShowErrorBox(true);
    bodyDataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
    bodyDataValidation.setEmptyCellAllowed(false);

    XSSFDataValidationConstraint dataValidationConstraint  = (XSSFDataValidationConstraint)dataValidationHelper.createExplicitListConstraint(new String[]{"출근","결근","지각","반차","연차"});
    CellRangeAddressList absentCellRangeAddressList = new CellRangeAddressList(1, dataList.size(), 3, 3);
    XSSFDataValidation absentDataValidation = (XSSFDataValidation)dataValidationHelper.createValidation(dataValidationConstraint, absentCellRangeAddressList);
    absentDataValidation.setSuppressDropDownArrow(true);
    absentDataValidation.createErrorBox("경고","입력값이 올바르지 않습니다.");
    absentDataValidation.setShowErrorBox(true);
    absentDataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
    absentDataValidation.setEmptyCellAllowed(false);

    sheet.addValidationData(headerDataValidation);
    sheet.addValidationData(bodyDataValidation);
    sheet.addValidationData(absentDataValidation);

    Row row = null;
    Cell cell = null;
    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setBorderTop(CellStyle.BORDER_THIN);
    headerStyle.setBorderRight(CellStyle.BORDER_THIN);
    headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
    headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    headerStyle.setLocked(true);

    Font headerFont = wb.createFont();
    headerFont.setFontName("맑은 고딕");
    headerFont.setFontHeight((short) 200);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    headerStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    CellStyle bodyStyle = wb.createCellStyle();
    bodyStyle.setBorderTop(CellStyle.BORDER_THIN);
    bodyStyle.setBorderRight(CellStyle.BORDER_THIN);
    bodyStyle.setBorderBottom(CellStyle.BORDER_THIN);
    bodyStyle.setBorderLeft(CellStyle.BORDER_THIN);
    bodyStyle.setAlignment(CellStyle.ALIGN_CENTER);
    bodyStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    row = sheet.createRow(0);
    row.setHeight((short) 400);

    for (int i = 0; i < headers.length; i++) {
        cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(headers[i]);
    }

    for (int i = 0; i < dataList.size(); i++) {
        Map<String, Object> map = dataList.get(i);
        row = sheet.createRow(i + 1);
        cell = row.createCell(0);
        cell.setCellStyle(bodyStyle);
        cell.setCellValue(map.get("ORD_SEQ").toString());
        cell = row.createCell(1);
        cell.setCellStyle(bodyStyle);
        cell.setCellValue(map.get("EMP_NO").toString());
        cell = row.createCell(2);
        cell.setCellStyle(bodyStyle);
        cell.setCellValue(map.get("EMP_NAME").toString());
        cell = row.createCell(3);
        cell.setCellStyle(bodyStyle);
        cell.setCellValue("출근");
    }

    String fileName = "출근상황_서식";
    String strAgent = request.getHeader("User-Agent");
    String userCharset = request.getCharacterEncoding();
    if (strAgent.indexOf("MSIE") > -1 || strAgent.indexOf("Trident/") > -1 || strAgent.indexOf("Edge/") > -1) {
        // Microsoft Internet Explorer & Edge인 경우
        if (userCharset.equalsIgnoreCase("UTF-8")) {
            fileName = URLEncoder.encode(fileName, userCharset);
        } else {
            fileName = new String(fileName.getBytes(userCharset), StandardCharsets.ISO_8859_1);
        }
    } else {
        // IE 를 제외한 브라우저
        fileName = new String(fileName.getBytes(), StandardCharsets.ISO_8859_1);
    }

    String headerKey = "Content-Disposition";
    String headerValue = String.format("attachment; filename=\"%s\"", fileName+".xlsx");
    response.setHeader(headerKey, headerValue);
    response.setContentType("application/octet-stream");

    wb.write(response.getOutputStream());
    //wb.dispose();

}

exceldown_source.txt

0.01MB

셀합치기는 다음처럼하면 된다. 모든 셀을 모두 그린 후에...

sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, 2));
sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 3, 10));