subinto의 개발노트

cfgItemSheet = new Tabulator("#sheet-containerItem", {

    height:"750px", // virtualDom 사용위해 필수

        layout:"fitColumns",

    headerSort:false,

        selectable: 1,

        dataTree:true,

        dataTreeStartExpanded:true,

        rowClick:function(e, row){

        //selected Row 없어지는 것 방지

        var selectedData = cfgItemSheet.getSelectedData();

        if(selectedData == "") row.toggleSelect();

        },

        ajaxConfig: "POST",

        ajaxContentType:"json",

        ajaxResponse:function(url, params, response) {

    if (response.Message === "OK") {

$('#message').css("color", "#FFFFFF");

$('#message').html("* 조회 완료 : 총 " + response.Data.length + "건");

        return bindListToTree(response.Data);

} else {

$('#message').css("color", "#FF3A00");

$('#message').html("* " + response.Message);

return [];

}

        },

        columns:[

            {

            title:"항목관리",

            columns:[

            {

                    title:"번호",

                    field:"sSeq",

                    width:80,

                    minWidth:60,

                    hozAlign:"left"

            },

            {

                title:"상태",

                field:"sStatus",

                width:80,

                minWidth:60,

                hozAlign:"center",

                download:false,

                formatter: "lookup",

                formatterParams: {

                "D":"삭제", "I":"입력", "U":"수정", undefined:""

                }

            },

            {

            title: "항목명",

                    field: "itemNm",

                    hozAlign:"center",

editor:"input",

            cellEdited:function(cell) {

            var rowData = cell.getRow().getData();

            if (rowData.sStatus != "I")

            cellEditedCallback(cell, "U", 0);

            },

                    validator : ["maxLength:100"]

            },

            {

            title: "항목ID",

                    field: "itemId",

                    download: true,

                    hozAlign:"center",

                    visible : false

            },

            {

            title: "상위항목ID",

                    field: "itemTop",

                    hozAlign:"center",

                    download: true,

                    visible : false

            },

            {

            title: "레벨",

                    field: "itemLvl",

                    hozAlign:"center",

                    download: true,

                    visible : false

            },

            {

            title: "정렬순서",

                    field: "sortOrder",

                    hozAlign:"center",

editor:"input",

            cellEdited:function(cell) {

            var rowData = cell.getRow().getData();

            if (rowData.sStatus != "I")

            cellEditedCallback(cell, "U", 0);

            },

                    validator : ["integer", "maxLength:10"]

            },

        {

        title: "사용여부",

                field: "useYn",

                hozAlign:"center",

                editor:"select",

        formatter: "lookup",

        formatterParams: {"Y":"사용", "N":"미사용"},

        editorParams:{

        values:{"Y":"사용", "N":"미사용"}

        },

                    cellEdited:function(cell) {

                    var rowData = cell.getRow().getData();

                    if (rowData.sStatus != "I")

                    cellEditedCallback(cell, "U", 0);

                    }

        }

            ]

            }

        ]

    });

//최상위 항목 등록

function addActionHandlerUp(ev) {

var newSSeq = searchTreeDataLength(cfgItemSheet) + 1;

cfgItemSheet.addRow({ "sStatus":"I", "sDelCheck":0, "sSeq":newSSeq, "itemLvl":"0", "itemId":"", "itemTop":vGrpId, "useYn":"Y"}, true);

//selected Row 체크

var rows = cfgItemSheet.getRows();

rows[0].toggleSelect();

}

//하위 항목 등록

function addActionHandlerDown(ev) {

var sheetData = cfgItemSheet.getData();

var selectedData = cfgItemSheet.getSelectedData()[0];

if(selectedData.sStatus == "I"){

$('#message').html("* 상위레벨을 먼저 저장해야 합니다.");

}

else if(parseInt(selectedData.itemLvl, 10) >= 2){

$('#message').html("* 3레벨까지만 생성이 가능합니다.");

}

else{

//Tree Data Row 추가

sheetData = addSelectedTreeDataRow(cfgItemSheet);

//Tree Data 세팅 및 세팅 후 selected Row 처리

cfgItemSheet.setData(sheetData).then(function(){

if(cfgItemSheet.getDataCount() > 0){

//신규 생성 Tree Data Row Selected

selectedTreeNewRow(cfgItemSheet);

}

});

}

}

function saveActionHandler(ev) {

ev.preventDefault();

// 입력/수정/삭제 할 행

let rows = searchTreeData(cfgItemSheet, "sStatus", ["D", "I", "U"]);

//변경된 Row의 데이터를 JSON 형태로 받음.

//var jsonString = cfgItemSheet.GetSaveJson();

if(rows.length < 1) {

$('#message').html("* 저장할 데이터가 없습니다!");

return;

}

// 신호장치명, 장치코드, 정렬순서 공백 체크

let itemNmRows = searchTreeData(cfgItemSheet, "itemNm", [undefined, ""]);

if (itemNmRows.length > 0) {

alert("항목명을 확인해주십시오!");

return;

}

let sortOrderRows = searchTreeData(cfgItemSheet, "sortOrder", [undefined, ""]);

if (sortOrderRows.length > 0) {

alert("정렬순서를 확인해주십시오!");

return;

}

if(confirm("변경하신 항목관리 정보를 저장하시겠습니까?")) {

let obj = {

schGrpId : vGrpId,

data: rows

};

cfgItemSheet.setData("/cfgItemAction.do", obj, "POST").then(function(){

if(cfgItemSheet.getDataCount() > 0){

var rows = cfgItemSheet.getRows();

rows[0].toggleSelect();

}

});

}

}

//list데이터 구조를 Tree데이터 구조로 변환하여 리턴

function bindListToTree(listData){

var treeData = new Array();

var itemLvl1Cnt = 0;

var itemLvl2Cnt = 0;

if(listData != null && listData.length > 0){

for(var i = 0 ; i < listData.length ; i++){

var map = listData[i];

//1레벨 세팅

if(map.itemLvl == 0) {

treeData[itemLvl1Cnt++] = map;

itemLvl2Cnt = 0;

}

//2레벨 세팅

else if(map.itemLvl == 1) {

var lvl1Map = treeData[itemLvl1Cnt - 1];

var lvl1SubList = lvl1Map._children;

if(lvl1SubList == undefined) {

lvl1SubList = new Array();

}

lvl1SubList[lvl1SubList.length] = map;

lvl1Map._children = lvl1SubList;

treeData[itemLvl1Cnt - 1] =  lvl1Map;

itemLvl2Cnt++;

}

//3레벨 세팅

else if(map.itemLvl == 2) {

var lvl1Map = treeData[itemLvl1Cnt - 1];

var lvl1SubList = lvl1Map._children;

var lvl2Map = lvl1SubList[itemLvl2Cnt - 1];

var lvl2SubList = lvl2Map._children;

if(lvl2SubList == undefined) {

lvl2SubList = new Array();

}

lvl2SubList[lvl2SubList.length] = map;

lvl2Map._children = lvl2SubList;

lvl1SubList[itemLvl2Cnt - 1] = lvl2Map;

lvl1Map._children = lvl1SubList;

treeData[itemLvl1Cnt - 1] = lvl1Map;

}

}

}

return treeData;

}

//Tree데이터 구조를 list데이터 구조로 변환하여 리턴

function bindTreeToList(treeData){

var listData = new Array();

var listCnt = 0;

if(treeData != null && treeData.length > 0){

for(var i = 0 ; i < treeData.length ; i++){

var lvl1Map = treeData[i];

listData[listCnt++] = lvl1Map;

//1레벨 하위항목

if(lvl1Map._children != undefined) {

for(var j = 0 ; j < lvl1Map._children.length ; j++){

var lvl2Map = lvl1Map._children[j];

listData[listCnt++] = lvl2Map;

//2레벨 하위항목

if(lvl2Map._children != undefined) {

for(var k = 0 ; k < lvl2Map._children.length ; k++){

var lvl3Map = lvl2Map._children[k];

listData[listCnt++] = lvl3Map;

}

}

}

}

}

}

return listData;

}

//Tree데이터에서 조건에 맞는 데이터를 찾아 list데이터 구조로 변환하여 리턴

function searchTreeData(vSheet, vField, vParamArr){

var treeData = vSheet.getData();

var listData = bindTreeToList(treeData);

var searchData = new Array();

var searchCnt = 0;

if(listData != null && listData.length > 0){

for(var i = 0 ; i < listData.length ; i++){

var map = listData[i];

for(var j = 0 ; j < vParamArr.length ; j++){

if(map[vField] == vParamArr[j]){

searchData[searchCnt++] = map;

}

}

}

}

return searchData;

}

//Tree데이터의 총 length를 리턴

function searchTreeDataLength(vSheet){

var treeData = vSheet.getData();

var listData = bindTreeToList(treeData);

return listData.length;

}

//선택된 Tree Data Row 추가

function addSelectedTreeDataRow(vSheet){

var sheetData = vSheet.getData();

var selectedData = vSheet.getSelectedData()[0];

var itemLvl = parseInt(selectedData.itemLvl, 10) + 1;

var itemTop = selectedData.itemTop;

//Tree Data Row 추가

for(var i = 0 ; i < sheetData.length ; i++){

var lvl1Map = sheetData[i];

var newSSeq = searchTreeDataLength(vSheet) + 1;

//1레벨 항목 확인

if(lvl1Map.itemId == selectedData.itemId){

var newItemTop = "";

var newItemLvl = 0;

if(lvl1Map._children != undefined){

newItemTop = lvl1Map.itemId;

newItemLvl = parseInt(lvl1Map.itemLvl, 10) + 1;

}

else{

lvl1Map._children = new Array();

newItemTop = lvl1Map.itemId;

newItemLvl = parseInt(lvl1Map.itemLvl, 10) + 1;

}

lvl1Map._children[lvl1Map._children.length] = {"sStatus":"I", "sDelCheck":0, "sSeq":newSSeq, "itemLvl":newItemLvl, "itemId":"", "itemTop":newItemTop, "useYn":"Y"};

}

else if(lvl1Map._children != undefined) {

for(var j = 0 ; j < lvl1Map._children.length ; j++){

var lvl2Map = lvl1Map._children[j];

//2레벨 항목 확인

if(lvl2Map.itemId == selectedData.itemId){

var newItemTop = "";

var newItemLvl = 0;

if(lvl2Map._children != undefined){

newItemTop = lvl2Map.itemId;

newItemLvl = parseInt(lvl2Map.itemLvl, 10) + 1;

}

else{

lvl2Map._children = new Array();

newItemTop = lvl2Map.itemId;

newItemLvl = parseInt(lvl2Map.itemLvl, 10) + 1;

}

lvl2Map._children[lvl2Map._children.length] = {"sStatus":"I", "sDelCheck":0, "sSeq":newSSeq, "itemLvl":newItemLvl, "itemId":"", "itemTop":newItemTop, "useYn":"Y"};

}

}

}

}

return sheetData;

}

//신규 생성 Tree Data Row Selected

function selectedTreeNewRow(vSheet){

var treeDataLength = searchTreeDataLength(vSheet);

var rows = vSheet.getRows();

for(var i = 0 ; i < rows.length ; i++){

var row1 = rows[i];

//1레벨 selected

if(parseInt(row1.getData().sSeq, 10) == treeDataLength){

row1.select();

}

else if(row1.getTreeChildren().length != undefined){

for(var j = 0 ; j < row1.getTreeChildren().length ; j++){

var row2 = row1.getTreeChildren()[j];

//2레벨 selected

if(parseInt(row2.getData().sSeq, 10) == treeDataLength){

row2.select();

}

else if(row2.getTreeChildren().length != undefined){

for(var k = 0 ; k < row2.getTreeChildren().length ; k++){

var row3 = row2.getTreeChildren()[k];

//3레벨 selected

if(parseInt(row3.getData().sSeq, 10) == treeDataLength){

row3.select();

}

}

}

}

}

}

}

<!-- MultipartResolver 설정 -->

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

<property name="maxUploadSize" value="100000000" />

<property name="maxInMemorySize" value="100000000" />

</bean>

package egovframework.cmn.cmn;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

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.xssf.usermodel.XSSFWorkbook;

import org.springframework.web.multipart.commons.CommonsMultipartFile;

public class ExcelUtil {

/**

* 엑셀 파일 읽기

* @param file

* @param sheetNum

* @param strartRowNum

* @param startCelNum

* @return List<HashMap<Integer, String>>

* @throws Exception

*/

public List<HashMap<Integer, String>> excelReadSetValue(CommonsMultipartFile file, int sheetNum, int strartRowNum, int startCelNum) throws Exception {

List<HashMap<Integer, String>> resultList = new ArrayList<>();

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");

//xls, xlsx 구분

Workbook workbook = null;

if(file.getOriginalFilename().toUpperCase().endsWith("XLSX")) {

workbook = new XSSFWorkbook(file.getInputStream());

}

else {

workbook = new HSSFWorkbook(file.getInputStream());

}

//Sheet 수 확인

int sheetCnt = workbook.getNumberOfSheets();

int listNum = 0;

try {

if (sheetCnt > 0) {

//첫번째 Sheet 선택

Sheet sheet = workbook.getSheetAt(sheetNum);

//Sheet의 Row와 Cell 수 확인

int rows = sheet.getPhysicalNumberOfRows();

int cells = sheet.getRow(0).getPhysicalNumberOfCells();

HashMap<Integer, String> valueMap = null;

//Header Row 빼고 시작(0에서 시작)

for(int r = strartRowNum ; r < rows; r++) {

//String device_id = "";

valueMap = new HashMap<Integer, String>();

//한 줄씩 읽고 데이터 저장

Row row = sheet.getRow(r);

if (row != null) {

//Cell 기본값 빼고 시작(0에서 시작)

for(int c = startCelNum ; c < cells ; c++) {

Cell cell = row.getCell(c);

if (cell != null) {

String value = "";

switch(cell.getCellType()) {

case Cell.CELL_TYPE_BLANK :

value = "";

break;

case Cell.CELL_TYPE_BOOLEAN :

value = "" + cell.getBooleanCellValue();

break;

case Cell.CELL_TYPE_ERROR :

value = "" + cell.getErrorCellValue();

break;

case Cell.CELL_TYPE_FORMULA :

value = cell.getCellFormula();

break;

case Cell.CELL_TYPE_NUMERIC :

if(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat())) {

value = sdf.format(cell.getDateCellValue());

}

else {

cell.setCellType(Cell.CELL_TYPE_STRING ); 

value = cell.getStringCellValue(); 

}

break;

case Cell.CELL_TYPE_STRING :

value = cell.getStringCellValue();

break;

}

//공백과 트림 제거

value = value.trim().replaceAll(" ", "");

valueMap.put(c, value);

}

}//end col for

resultList.add(listNum++, valueMap);

}//end if

}//end row for

}

} catch(Exception e) {

e.getStackTrace();

}

return resultList;

}

}

/**

* 엑셀 업로드 처리

* @param multiRequest

* @param request

* @return

* @throws Exception

*/

@RequestMapping("cfgUploadAction.do")

public String cfgUploadAction(HttpServletRequest request, RedirectAttributes redirectAttributes) throws Exception {

try {

cfgService.excelUpload(request);

redirectAttributes.addFlashAttribute("Code", 0);

redirectAttributes.addFlashAttribute("Message", egovMessageSource.getMessage("proc.success"));

} catch (Exception ex) {

redirectAttributes.addFlashAttribute("Code", 1);

redirectAttributes.addFlashAttribute("Message", "오류가 발생하였습니다. 엑셀양식을 확인해 주세요.");

}

return "redirect:/mng/fac/cfg/cfgUploadPop.do";

}

/**

* 엑셀 업로드 처리

* @param multiRequest

* @return String

* @throws Exception

*/

@SuppressWarnings("unchecked")

public void excelUpload(HttpServletRequest request) throws Exception{

MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest)request;

//파일 정보

CommonsMultipartFile file = (CommonsMultipartFile)multiRequest.getFile("excelFile");

//엑셀정보

ExcelUtil eu = new ExcelUtil();

int sheetNum = 0; //1번째 시트 읽음 

int strartRowNum = 1; //2번째 줄부터 읽음

int startCelNum = 2; //3번째 줄부터 읽음(지역ID)

List<HashMap<Integer, String>> excelList = eu.excelReadSetValue(file, sheetNum, strartRowNum, startCelNum);

//테이블 Key 정보

DeviceBaseVO deviceBaseVO = null;

//엑셀 Row 수 만큼 For문 조회 

for(Object obj : excelList) {

Map<Integer, String> mp = (Map<Integer, String>)obj;

Set<Integer> keySet = mp.keySet();

Iterator<Integer> iterator = keySet.iterator();

deviceBaseVO = new DeviceBaseVO();

while(iterator.hasNext()) {

int key = iterator.next();

String value = StringUtil.nullConvert(mp.get(key));

switch(key) {

case 2 :

deviceBaseVO.setAreaId(value);

break;

case 4 :

deviceBaseVO.setFacilityId(value);

break;

case 5 :

deviceBaseVO.setDeviceNm(value);

break;

case 6 :

deviceBaseVO.setDeviceId(value);

break;

case 7 :

deviceBaseVO.setInstDt(value);

break;

case 8 :

deviceBaseVO.setUseYn(value);

break;

}

}

if(!"".equals(deviceBaseVO.getAreaId()) && deviceBaseVO.getAreaId() != null) {

cfgMapper.updateCfgInfo(deviceBaseVO);

}

}

}

<form name="popForm" method="post" action="${actionUrl}" enctype="multipart/form-data">

<table>

<caption>엑셀 업로드</caption>

<colgroup>

<col><col>

</colgroup>

<tbody>

<tr>

<th><label for="code2">파일찾기</label></th>

<td><input name="excelFile" id="excelFile" type="file" size="30"></td>

</tr>

</table>

<!-- 버튼 영역 -->

<div class="btn-area">

<a href="#" onclick="doSubmit();" class="btn btn-yellow btn-ok">업로드</a>

<a href="javascript:self.close();" class="btn btn-yellow btn-cancel">창닫기</a>

</div>

</form>