Examples with ExcelDataDVO com.kyj.fx.voeditor.visual.excels.base.ExcelDataDVO used on opensource projects

Example 1 with ExcelDataDVO

use of com.kyj.fx.voeditor.visual.excels.base.ExcelDataDVO in project Gargoyle by callakrsos.

the class AbstractExcelModel method work.

/**
	 * 2014. 11. 4. KYJ
	 * 
	 * @param
	 * @return
	 * @throws Exception
	 * @처리내용 : 입력된 엑셀파일로부터 ExcelSVO객체를 생성하여 반환한다.
	 */
public ExcelSVO work() throws Exception {
    // 결과반환용 SVO
    ExcelSVO excelSVO = new ExcelSVO();
    // 컬럼부
    /* 시작 엑셀관련 메타정보 처리객체 */
    FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
    DecimalFormat df = new DecimalFormat();
    /* 끝 엑셀관련 메타정보 처리객체 */
    for (int sheetIndex = 0; sheetIndex < excel.getNumberOfSheets(); sheetIndex++) {
        Sheet sheetAt = excel.getSheetAt(sheetIndex);
        String sheetName = sheetAt.getSheetName();
        List<ExcelColDVO> columnDVOList = new ArrayList<ExcelColDVO>();
        excelSVO.setColDvoList(sheetName, columnDVOList);
        int maxColumIndex = 0;
        // 시작 데이터부 처리
        List<ExcelDataDVO> arrayList = new ArrayList<ExcelDataDVO>();
        // 컬럼부에 정의되어야하는데 없음. 데이터부에는 존재할경우 컬럼부를 추가하기 위한 플래그
        boolean existsOutOfColumn = false;
        // while (rowIterator.hasNext())
        for (int row = 0; row < sheetAt.getLastRowNum(); row++) {
            Row next = sheetAt.getRow(row);
            if (next != null) {
                short lastCellNum = next.getLastCellNum();
                for (int col = 0; col < lastCellNum; col++) {
                    Cell cell = next.getCell(col);
                    if (cell != null) {
                        // 엑셀 셀
                        // Cell cell = cellIterator.next();
                        CellStyle cellStyle = cell.getCellStyle();
                        Color fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
                        String backColorRgb = "";
                        if (fillBackgroundColorColor instanceof HSSFColor) {
                            HSSFColor backColor = (HSSFColor) fillBackgroundColorColor;
                            backColorRgb = backColor.getHexString();
                        } else if (fillBackgroundColorColor instanceof XSSFColor) {
                            XSSFColor backColor = (XSSFColor) fillBackgroundColorColor;
                            backColorRgb = backColor.getARGBHex();
                        }
                        int cellType = cell.getCellType();
                        String stringCellValue = "";
                        switch(cellType) {
                            case Cell.CELL_TYPE_FORMULA:
                                if (!(cell.toString() == "")) {
                                    if (evaluator.evaluateFormulaCell(cell) == 0) {
                                        double fddata = cell.getNumericCellValue();
                                        stringCellValue = String.valueOf(fddata);
                                    } else if (evaluator.evaluateFormulaCell(cell) == 1) {
                                        stringCellValue = cell.getStringCellValue();
                                    } else if (evaluator.evaluateFormulaCell(cell) == 4) {
                                        boolean fbdata = cell.getBooleanCellValue();
                                        stringCellValue = String.valueOf(fbdata);
                                    }
                                    break;
                                }
                                stringCellValue = cell.getCellFormula();
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                /* N/A */
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                // double numericCellValue =
                                // cell.getNumericCellValue();
                                String dateFormatString = cellStyle.getDataFormatString();
                                short dataFormat = cellStyle.getDataFormat();
                                boolean internalDateFormat = HSSFDateUtil.isInternalDateFormat(dataFormat);
                                double numericCellValue = cell.getNumericCellValue();
                                boolean validExcelDate = HSSFDateUtil.isValidExcelDate(numericCellValue);
                                boolean cellDateFormatted = HSSFDateUtil.isCellDateFormatted(cell);
                                boolean cellInternalDateFormatted = HSSFDateUtil.isCellInternalDateFormatted(cell);
                                LOGGER.debug("sheet : [ " + sheetName + " ] dateFormatString : [ " + dateFormatString + " ] dataFormat : [ " + dataFormat + " ] isInternalDateFormat : [ " + internalDateFormat + " ] validExcelDate : [ " + validExcelDate + " ] cellDateFormatted : [ " + cellDateFormatted + " ]  cellInternalDateFormatted : [" + cellInternalDateFormatted + " ] numericCellValue : [ " + numericCellValue + " ] rowIndex : [ " + row + " ] columnIndex : [ " + col + " ]");
                                if (cellDateFormatted || cellInternalDateFormatted || (!"GENERAL".equals(dateFormatString.toUpperCase()))) {
                                    Date date = cell.getDateCellValue();
                                    LOGGER.debug("dateFmt : %s", dateFormatString);
                                    stringCellValue = new CellDateFormatter(dateFormatString).format(date);
                                } else {
                                    double ddata = cell.getNumericCellValue();
                                    stringCellValue = df.format(ddata);
                                }
                                break;
                            case Cell.CELL_TYPE_STRING:
                                stringCellValue = cell.getStringCellValue();
                                break;
                            default:
                                /* N/A */
                                break;
                        }
                        // 시작 컬럼부 처리
                        if (row == 0) {
                            short alignment = cellStyle.getAlignment();
                            int columnWidth = sheetAt.getColumnWidth(col);
                            columnDVOList.add(new ExcelColDVO(col, stringCellValue, columnWidth, alignment));
                        }
                        // 끝 컬럼부 처리
                        ExcelDataDVO excelDataDVO = new ExcelDataDVO(row, col, stringCellValue, backColorRgb);
                        arrayList.add(excelDataDVO);
                    } else {
                        ExcelDataDVO excelDataDVO = new ExcelDataDVO(row, col, "");
                        arrayList.add(excelDataDVO);
                    }
                }
            // end for
            } else {
                ExcelDataDVO excelDataDVO = new ExcelDataDVO(row, 0, "");
                arrayList.add(excelDataDVO);
            }
        }
        // end for
        // 끝 데이터부 처리
        excelSVO.addSheetExcelDVO(sheetName, arrayList);
    }
    return excelSVO;
}

Example 2 with ExcelDataDVO

use of com.kyj.fx.voeditor.visual.excels.base.ExcelDataDVO in project Gargoyle by callakrsos.

the class ExcelUtilTest method testToXlsxExcelSVO.

/**
	 * Test method for
	 * {@link com.kyj.fx.voeditor.visual.util.ExcelUtil#toXlsxExcelSVO(java.io.File)}
	 * .
	 * 
	 * @throws Exception
	 */
@Test
public final void testToXlsxExcelSVO() throws Exception {
    ExcelSVO xlsxExcelSVO = ExcelUtil.toK(createExcelFile, new BiFunction<File, Workbook, ExcelSVO>() {

        @Override
        public ExcelSVO apply(File file, Workbook xlsx) {
            ExcelSVO svo = new ExcelSVO();
            svo.setFile(file);
            int numberOfSheets = xlsx.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheetAt = xlsx.getSheetAt(i);
                String sheetName = xlsx.getSheetName(i);
                // 헤더부 처리
                {
                    Row columnRow = sheetAt.getRow(2);
                    short lastCellNum = columnRow.getLastCellNum();
                    ArrayList<ExcelColDVO> colList = new ArrayList<>();
                    for (int _cell = 0; _cell < lastCellNum; _cell++) {
                        Cell cell = columnRow.getCell(_cell);
                        String stringCellValue = cell.getStringCellValue();
                        ExcelColDVO excelColDVO = new ExcelColDVO();
                        excelColDVO.setColSeq(_cell);
                        excelColDVO.setColName(stringCellValue);
                        colList.add(excelColDVO);
                    }
                    svo.setColDvoList(sheetName, colList);
                }
                // 데이터부 처리
                for (int _row = 3; _row < sheetAt.getLastRowNum(); _row++) {
                    Row row = sheetAt.getRow(_row);
                    short lastCellNum = row.getLastCellNum();
                    for (int _cell = 0; _cell < lastCellNum; _cell++) {
                        Cell cell = row.getCell(_cell);
                        String value = cell.getStringCellValue();
                        svo.addSheetExcelDVO(sheetName, new ExcelDataDVO(_row, _cell, value));
                    }
                }
            }
            return svo;
        }
    });
    List<TableModelDVO> list = ExcelUtil.toK(createExcelFile, new BiFunction<File, Workbook, List<TableModelDVO>>() {

        @Override
        public List<TableModelDVO> apply(File file, Workbook xlsx) {
            List<TableModelDVO> llist = new ArrayList<>();
            Sheet sheetAt = xlsx.getSheetAt(0);
            // 헤더부 처리
            Row columnRow = sheetAt.getRow(2);
            ArrayList<ExcelColDVO> colList = new ArrayList<>();
            Cell _column = columnRow.getCell(0);
            Cell _type = columnRow.getCell(1);
            Cell _size = columnRow.getCell(2);
            Cell _comment = columnRow.getCell(3);
            // 데이터부 처리
            for (int _row = 3; _row < sheetAt.getLastRowNum(); _row++) {
                Row row = sheetAt.getRow(_row);
                Cell column = row.getCell(0);
                Cell type = row.getCell(1);
                Cell size = row.getCell(2);
                Cell comment = row.getCell(3);
                TableModelDVO modelDVO = new TableModelDVO();
                modelDVO.setName(column.getStringCellValue());
                modelDVO.setDabaseTypeName(type.getStringCellValue());
                modelDVO.setSize(size.getStringCellValue());
                modelDVO.setDesc(comment.getStringCellValue());
                llist.add(modelDVO);
            }
            return llist;
        }
    });
    System.out.println(list);
}

Example 3 with ExcelDataDVO

use of com.kyj.fx.voeditor.visual.excels.base.ExcelDataDVO in project Gargoyle by callakrsos.

the class ExcelTest method simpleWrite.

@Test
public void simpleWrite() throws Exception {
    ExcelSVO svo = new ExcelSVO();
    svo.addSheetExcelDVO("sampleExcel1", new ExcelDataDVO(0, 0, "0:0 data"));
    String excelFileName = "c:\\Users\\kyj\\desktop\\sampleFile.xlsx";
    Stream<String> lines = Files.lines(Paths.get("c:", "G-MES2.0", "gmes20CodeTemplete.xml"));
    lines.forEach(System.out::println);
    ExcelUtil.createExcel(excelFileName, svo, false);
    File file = new File(excelFileName);
    Assert.assertEquals(file.exists(), true);
    file.delete();
}

Example 4 with ExcelDataDVO

use of com.kyj.fx.voeditor.visual.excels.base.ExcelDataDVO in project Gargoyle by callakrsos.

the class ExcelUtil method createExcel.

/**
	 * 2015. 11. 22. KYJ
	 *
	 * @처리내용 : 엑셀파일을 생성한다.
	 * @param excelFileName
	 * @param svo
	 * @param appendExtension
	 *            확장자를 붙일지 여부
	 * @throws Exception
	 */
public static void createExcel(String excelFileName, ExcelSVO svo, boolean appendExtension) throws Exception {
    Workbook createNewWorkBookXlsx = createNewWorkBookXlsx();
    Iterator<String> iterator = svo.iterator();
    while (iterator.hasNext()) {
        String sheetName = iterator.next();
        List<ExcelColDVO> colDvoList = svo.getColDvoList(sheetName);
        Sheet createSheet = createNewWorkBookXlsx.createSheet(sheetName);
        List<ExcelDataDVO> sheetExcelDVOList = svo.getSheetExcelDVO(sheetName);
        if (colDvoList != null) {
            for (ExcelColDVO dvo : colDvoList) {
                /* 자동 사이즈 조절 */
                Cell createCell = createCell(createSheet, dvo.getColName(), 0, dvo.getColSeq());
                applyColor(createNewWorkBookXlsx, dvo, createCell);
            }
        }
        if (sheetExcelDVOList != null) {
            for (ExcelDataDVO dvo : sheetExcelDVOList) {
                Cell createCell = createCell(createSheet, dvo.getData(), dvo.getRow() + 1, dvo.getCol());
                applyColor(createNewWorkBookXlsx, dvo, createCell);
            }
        }
    }
    String extension = "";
    if (appendExtension)
        extension = "xlsx";
    createNewWorkBookXlsx.write(getFileOutputStream(excelFileName, extension));
}

Example 5 with ExcelDataDVO

use of com.kyj.fx.voeditor.visual.excels.base.ExcelDataDVO in project Gargoyle by callakrsos.

the class VoWizardUtil method createExcelFile.

/**
	 * 엑셀파일생성
	 *
	 * @param pathDir
	 * @param _fileName
	 * @param models
	 * @return 생성된 엑셀파일에 대한 파일 객체
	 * @throws GargoyleFileAlreadyExistException
	 */
public static File createExcelFile(File pathDir, String _fileName, List<TableModelDVO> models, boolean isOverWrite) throws GargoyleFileAlreadyExistException {
    File saveFile = null;
    if (!pathDir.exists() || !pathDir.isDirectory())
        return saveFile;
    String fileName = _fileName;
    String createFilePathName = pathDir.getAbsolutePath() + File.separator + fileName;
    if (!_fileName.endsWith(".xlsx")) {
        createFilePathName.concat(".xlsx");
    }
    saveFile = new File(createFilePathName);
    if (saveFile.exists() && !isOverWrite) {
        throw new GargoyleFileAlreadyExistException("already exists. file");
    }
    // 로우번호 채번링
    AtomicInteger atomicInteger = new AtomicInteger(2);
    List<ExcelDataDVO> collect = models.stream().map(vo -> {
        List<ExcelDataDVO> list = new ArrayList<ExcelDataDVO>();
        String columnName = vo.getDatabaseColumnName();
        String dataType = vo.getType();
        String dataSize = vo.getSize();
        String desc = vo.getDesc();
        int row = atomicInteger.getAndIncrement();
        list.add(new ExcelDataDVO(row, 0, ValueUtil.toCamelCase(ValueUtil.decode(columnName, columnName, vo.getName()).toString())));
        list.add(new ExcelDataDVO(row, 1, dataType));
        list.add(new ExcelDataDVO(row, 2, dataSize));
        list.add(new ExcelDataDVO(row, 3, desc));
        return list;
    }).collect(() -> {
        /* 컬럼헤더처리 */
        ArrayList<ExcelDataDVO> arrayList = new ArrayList<ExcelDataDVO>();
        arrayList.add(new ExcelDataDVO(1, 0, VoWizardUtil.COLUMN_NAME, /* "컬럼명" */
        Color.GREEN));
        arrayList.add(new ExcelDataDVO(1, 1, VoWizardUtil.TYPE, /* "데이터타입" */
        Color.GREEN));
        arrayList.add(new ExcelDataDVO(1, 2, VoWizardUtil.DATA_LENGTH, /* "데이터사이즈" */
        Color.GREEN));
        arrayList.add(new ExcelDataDVO(1, 3, VoWizardUtil.COMMENTS, /* "설명" */
        Color.GREEN));
        return arrayList;
    }, (t, u) -> t.addAll(u), (t, u) -> t.addAll(u));
    ExcelSVO svo = new ExcelSVO();
    svo.addSheetExcelDVO(SHEET_NAME, collect);
    try {
        ExcelUtil.createExcel(saveFile.getAbsolutePath(), svo, false);
    } catch (Exception e1) {
        ValueUtil.toString(e1);
        saveFile = null;
    }
    return saveFile;
}