Search in sources :

Example 1 with ExcelColumnExpression

use of com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression in project Gargoyle by callakrsos.

the class FxExcelUtil method createExcel.

/**
	 *  엑셀파일을 생성한다.
	 * @작성자 : KYJ
	 * @작성일 : 2016. 9. 6.
	 *  
	 * @param exportExcelFile 
	 *   	export하려는 파일 확장자는 .xlsx 사용 권고
	 *   
	 * @param dataSource 
	 *      데이터셋.  개발자 혼동(순서)을 피하기 위해 LinkedMap(순서유지)을 강제적으로 사용
	 *      값은 Map<Sheet명, Map<컬럼메타,List<값>> 순 매핑이되어있다.
	 * 
	 * @param handler
	 *     데이터 컨버터, 데이터내에 특화가 필요한 처리가 필요한경우 구현하여 사용하도록한다.
	 *     
	 * @throws Exception
	 */
public static void createExcel(File exportExcelFile, LinkedHashMap<String, LinkedHashMap<ExcelColumnExpression, List<Object>>> dataSource, IExcelDataSetHandler<Sheet, LinkedHashMap<ExcelColumnExpression, List<Object>>> handler, Map<String, Map<String, String>> metadata, boolean overwrite) throws Exception {
    //파일이 이미 존재하는 상황에서 overrite하려는 경우 에러.
    if (exportExcelFile.exists() && !overwrite) {
        throw new GargoyleException(String.format("output File : {%s} already exists.!", exportExcelFile.getName()));
    }
    Workbook createNewWorkBookXlsx = createNewWorkBookXlsx();
    //시트를 의미하는 iterator.
    Iterator<String> iterator = dataSource.keySet().iterator();
    int sheetIndex = 0;
    while (iterator.hasNext()) {
        String sheetName = iterator.next();
        Sheet createSheet = createNewWorkBookXlsx.createSheet(sheetName);
        Map<String, String> meta = metadata.get(sheetName);
        if (meta == null || meta.isEmpty())
            continue;
        //각 헤더컬럼의 개수(높이)
        String columnMaxHeight = meta.get($$META_COLUMN_MAX_HEIGHT$$);
        int maxColumnRowSize = ValueUtil.decode(columnMaxHeight, val -> Integer.parseInt(val), () -> 0);
        LinkedHashMap<ExcelColumnExpression, List<Object>> linkedHashMap = dataSource.get(sheetName);
        createHeaders(handler, createSheet, linkedHashMap, maxColumnRowSize);
        //컬럼 + 데이터리스트로 이루어진 데이터셋
        LinkedHashMap<ExcelColumnExpression, List<Object>> dataSet = dataSource.get(sheetName);
        drawBody(handler, createSheet, maxColumnRowSize, dataSet);
        //(short) (createSheet.getRow(maxColumnRowSize + 1).getLastCellNum() - 1);
        short lastColumnNum = (short) dataSet.size();
        IExcelScreenHandler screenHandler = handler.getExcelScreenHandler();
        if (handler.isApplyAutoFit()) {
            for (int i = 0; i < lastColumnNum; i++) {
                createSheet.autoSizeColumn(i);
            }
        }
        if (screenHandler != null) {
            //사용자 정의 sheet 처리를 지원한다.
            screenHandler.customSheetHandle(sheetIndex, createSheet);
            sheetIndex++;
        }
        //로고 이미지 처리.
        Utils.createDefaultLogo(createSheet);
        createSheet.setAutoFilter(new CellRangeAddress((maxColumnRowSize), (maxColumnRowSize), START_COLUMN_INDEX, lastColumnNum));
        createSheet.createFreezePane(0, (maxColumnRowSize + 1));
    }
    //사용자 정의 workbook 처리를 지원함.
    IExcelScreenHandler excelScreenHandler = handler.getExcelScreenHandler();
    if (excelScreenHandler != null)
        excelScreenHandler.customWorkbookHandle(createNewWorkBookXlsx);
    //파일 write처리.
    try (FileOutputStream fileOutputStream = new FileOutputStream(exportExcelFile)) {
        createNewWorkBookXlsx.write(fileOutputStream);
    }
}
Also used : IExcelScreenHandler(com.kyj.fx.voeditor.visual.framework.excel.IExcelScreenHandler) GargoyleException(com.kyj.fx.voeditor.visual.exceptions.GargoyleException) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Workbook(org.apache.poi.ss.usermodel.Workbook) ExcelColumnExpression(com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression) FileOutputStream(java.io.FileOutputStream) ArrayList(java.util.ArrayList) List(java.util.List) ObservableList(javafx.collections.ObservableList) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) Sheet(org.apache.poi.ss.usermodel.Sheet)

Example 2 with ExcelColumnExpression

use of com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression in project Gargoyle by callakrsos.

the class FxExcelUtil method drawHeader.

/**
	 * 헤더부분을 실제 엑셀에 처리하기 위한 코드.
	 * @작성자 : KYJ
	 * @작성일 : 2016. 9. 7. 
	 * @param sheetConverter
	 * @param createSheet
	 * @param columnExpr
	 * @param startRow
	 * @throws Exception
	 */
private static void drawHeader(IExcelDataSetHandler<Sheet, LinkedHashMap<ExcelColumnExpression, List<Object>>> sheetConverter, Sheet createSheet, ExcelColumnExpression columnExpr, final int maxLevel) throws Exception {
    Cell createCell = sheetConverter.createCell(createSheet, columnExpr.getDisplayText(), (/*startRow +*/
    columnExpr.getLevel()), START_COLUMN_INDEX + columnExpr.getIndex());
    List<ExcelColumnExpression> childrens = columnExpr.getChildrens();
    if (childrens != null && !childrens.isEmpty()) {
        int horizontalSize = childrens.size();
        for (ExcelColumnExpression subColumnExpr : childrens) {
            if (subColumnExpr.isVisible()) {
                drawHeader(sheetConverter, createSheet, subColumnExpr, maxLevel);
            }
        }
        /* 셀병합. Horizontal Merge  부모 레벨.*/
        {
            int level = columnExpr.getParent() == null ? HEADER_ROW_INDEX : columnExpr.getParent().getLevel();
            int index = columnExpr.getIndex() + START_COLUMN_INDEX;
            CellRangeAddress cellMerge = cellMerge(createSheet, level, level, index, (index + horizontalSize - 1));
            border(cellMerge, createSheet);
            headerStyle(createCell);
        }
    } else {
        /*셀병합. Vertical Merge*/
        //case1 자식레벨은 없고 , 본인 레벨이 max 레벨이 낮은경우
        int level = columnExpr.getLevel();
        int index = columnExpr.getIndex() + START_COLUMN_INDEX;
        if (level < maxLevel) {
            CellRangeAddress cellMerge = cellMerge(createSheet, level, maxLevel, index, index);
            //				style(createCell, DEFAULT_HEADER_STYLE);
            border(cellMerge, createSheet);
        }
        /* 현재 레벨.*/
        {
            headerStyle(createCell, style -> {
                style.setBorderTop(CellStyle.BORDER_THIN);
                style.setBorderLeft(CellStyle.BORDER_THIN);
                style.setBorderRight(CellStyle.BORDER_THIN);
                style.setBorderBottom(CellStyle.BORDER_THIN);
            });
            //컬럼 width 지정.
            width(createSheet, columnExpr, index);
        }
    }
}
Also used : ClientAnchor(org.apache.poi.ss.usermodel.ClientAnchor) Drawing(org.apache.poi.ss.usermodel.Drawing) ByteArrayOutputStream(java.io.ByteArrayOutputStream) URL(java.net.URL) ITableColumnForExcel(com.kyj.fx.voeditor.visual.framework.excel.ITableColumnForExcel) LoggerFactory(org.slf4j.LoggerFactory) HashMap(java.util.HashMap) ArrayList(java.util.ArrayList) XSSFFont(org.apache.poi.xssf.usermodel.XSSFFont) TableColumn(javafx.scene.control.TableColumn) LinkedHashMap(java.util.LinkedHashMap) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Map(java.util.Map) Cell(org.apache.poi.ss.usermodel.Cell) Picture(org.apache.poi.ss.usermodel.Picture) XSSFColor(org.apache.poi.xssf.usermodel.XSSFColor) TableView(javafx.scene.control.TableView) GargoyleException(com.kyj.fx.voeditor.visual.exceptions.GargoyleException) IExcelScreenHandler(com.kyj.fx.voeditor.visual.framework.excel.IExcelScreenHandler) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) Sheet(org.apache.poi.ss.usermodel.Sheet) Logger(org.slf4j.Logger) Iterator(java.util.Iterator) Predicate(java.util.function.Predicate) DefautExcelDataSetHandler(com.kyj.fx.voeditor.visual.framework.excel.DefautExcelDataSetHandler) AnchorType(org.apache.poi.ss.usermodel.ClientAnchor.AnchorType) FileOutputStream(java.io.FileOutputStream) Set(java.util.Set) IOException(java.io.IOException) IExcelDataSetHandler(com.kyj.fx.voeditor.visual.framework.excel.IExcelDataSetHandler) XSSFCellStyle(org.apache.poi.xssf.usermodel.XSSFCellStyle) File(java.io.File) Consumer(java.util.function.Consumer) List(java.util.List) Workbook(org.apache.poi.ss.usermodel.Workbook) ExcelColumnExpression(com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression) CreationHelper(org.apache.poi.ss.usermodel.CreationHelper) ObservableList(javafx.collections.ObservableList) CellStyle(org.apache.poi.ss.usermodel.CellStyle) RegionUtil(org.apache.poi.ss.util.RegionUtil) InputStream(java.io.InputStream) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) Cell(org.apache.poi.ss.usermodel.Cell) ExcelColumnExpression(com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression)

Example 3 with ExcelColumnExpression

use of com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression in project Gargoyle by callakrsos.

the class FxExcelUtil method getDataSource.

/**
	 * UI 및 TableView로부터 Excel데이터생성을 위한 체계화된 데이터 
	 * LinkedHashMap객체를 리턴.
	 * @작성자 : KYJ
	 * @작성일 : 2016. 9. 7. 
	 * @param screen
	 * @param table
	 * @param allColumnsList 
	 *    계층형 테이블컬럼들을 일렬로 찾아낸 리스트
	 * @return
	 */
@SuppressWarnings({ "rawtypes" })
static /**
	 * UI 및 TableView로부터 Excel데이터생성을 위한 체계화된 데이터 
	 * @작성자 : KYJ
	 * @작성일 : 2016. 9. 19. 
	 * @param mapper
	 * @param table
	 * @param allColumnsList
	 * @return
	 */
LinkedHashMap<ExcelColumnExpression, List<Object>> getDataSource(IExcelScreenHandler mapper, TableView table, ArrayList<ExcelColumnExpression> allColumnsList) {
    LinkedHashMap<ExcelColumnExpression, List<Object>> dataSet = new LinkedHashMap<>();
    //ExcelColumnExpression :: 계층형 테이블컬럼들을 일렬로 찾아낸 리스트
    //		List<ExcelColumnExpression> allColumnsList = new ArrayList<ExcelColumnExpression>();
    @SuppressWarnings("unchecked") ObservableList<TableColumn> items = table.getItems();
    int size = items.size();
    //특화 헤더를 매핑.
    for (ExcelColumnExpression c : allColumnsList) {
        //			if (!c.isVisible())
        //				continue;
        String columnHeaderMapper = mapper.columnHeaderMapper(table, c.getTableColumn());
        c.setDisplayText(columnHeaderMapper);
        List<Object> values = new ArrayList<Object>();
        int columnIndex = c.getIndex();
        for (int rowIndex = 0; rowIndex < size; rowIndex++) {
            Object userValue = mapper.valueMapper(table, c.getTableColumn(), columnIndex, rowIndex);
            values.add(userValue);
        }
        dataSet.put(c, values);
    }
    return dataSet;
}
Also used : ArrayList(java.util.ArrayList) ArrayList(java.util.ArrayList) List(java.util.List) ObservableList(javafx.collections.ObservableList) TableColumn(javafx.scene.control.TableColumn) ExcelColumnExpression(com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression) LinkedHashMap(java.util.LinkedHashMap)

Example 4 with ExcelColumnExpression

use of com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression in project Gargoyle by callakrsos.

the class FxExcelUtil method createExcel.

/**
	 * @작성자 : KYJ
	 * @작성일 : 2016. 9. 7. 
	 * @param screen
	 * @param exportExcelFile
	 * @param tableViewList
	 * @param overrite
	 * @throws Exception
	 */
@SuppressWarnings({ "rawtypes", "unchecked" })
public static void createExcel(IExcelScreenHandler screen, File exportExcelFile, List<TableView> tableViewList, boolean overrite) throws Exception {
    IExcelScreenHandler screenHandler = screen;
    /*
		 * Key : Sheet
		 * Value - Key :  ExcelColumnExpression
		 * Value - Value :  Object
		 */
    LinkedHashMap<String, LinkedHashMap<ExcelColumnExpression, List<Object>>> dataSet = new LinkedHashMap<>();
    Map<String, Map<String, String>> metadata = new HashMap<>();
    int sheetIndex = 0;
    for (TableView table : tableViewList) {
        Predicate<TableView<?>> useTableViewForExcel = screenHandler.useTableViewForExcel();
        if (useTableViewForExcel != null) {
            if (!useTableViewForExcel.test(table)) {
                continue;
            }
        }
        //Sheet.
        String sheetName = screenHandler.toSheetName(table);
        if (sheetName == null) {
            sheetName = String.format(DEFAULT_SHEET_NAME_FORMAT, sheetIndex++);
        }
        ObservableList<TableColumn> columns = table.getColumns();
        //계층형 테이블컬럼의 모든 값을 찾아냄.
        ArrayList<ExcelColumnExpression> allColumnsList = new ArrayList<ExcelColumnExpression>();
        int maxLevel = getMaxLevel(columns, /*ExcelColumnExpression :: 계층형 테이블컬럼들을 일렬로 찾아낸 리스트 */
        allColumnsList, screenHandler.useTableColumnForExcel());
        dataSet.put(sheetName, getDataSource(screen, table, allColumnsList));
        HashMap<String, String> meta = new HashMap<String, String>();
        meta.put($$META_COLUMN_MAX_HEIGHT$$, String.valueOf(maxLevel));
        metadata.put(sheetName, meta);
    }
    if (dataSet.isEmpty())
        dataSet.put("empty", new LinkedHashMap<>());
    createExcel(screenHandler, exportExcelFile, dataSet, metadata, overrite);
}
Also used : HashMap(java.util.HashMap) LinkedHashMap(java.util.LinkedHashMap) IExcelScreenHandler(com.kyj.fx.voeditor.visual.framework.excel.IExcelScreenHandler) ArrayList(java.util.ArrayList) TableColumn(javafx.scene.control.TableColumn) ExcelColumnExpression(com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression) LinkedHashMap(java.util.LinkedHashMap) HashMap(java.util.HashMap) LinkedHashMap(java.util.LinkedHashMap) Map(java.util.Map) TableView(javafx.scene.control.TableView)

Example 5 with ExcelColumnExpression

use of com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression in project Gargoyle by callakrsos.

the class FxExcelUtil method createHeaders.

/**
	 * @param sheetConverter 
	 * @작성자 : KYJ
	 * @작성일 : 2016. 9. 6. 
	 * @param createSheet
	 * @param headers
	 * @param maxHeaderLength 
	 * @throws Exception
	 */
private static void createHeaders(IExcelDataSetHandler<Sheet, LinkedHashMap<ExcelColumnExpression, List<Object>>> sheetConverter, Sheet createSheet, LinkedHashMap<ExcelColumnExpression, List<Object>> linkedHashMap, final int maxColumnRowSize) throws Exception {
    Set<ExcelColumnExpression> keySet = linkedHashMap.keySet();
    Iterator<ExcelColumnExpression> iterator = keySet.iterator();
    while (iterator.hasNext()) {
        ExcelColumnExpression next = iterator.next();
        //			sheetConverter.createCell(createSheet, next.displayText, HEADER_ROW_INDEX, next.index);
        drawHeader(sheetConverter, createSheet, next, maxColumnRowSize);
    }
}
Also used : ExcelColumnExpression(com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression)

Aggregations

ExcelColumnExpression (com.kyj.fx.voeditor.visual.framework.excel.ExcelColumnExpression)6 ArrayList (java.util.ArrayList)4 TableColumn (javafx.scene.control.TableColumn)4 IExcelScreenHandler (com.kyj.fx.voeditor.visual.framework.excel.IExcelScreenHandler)3 LinkedHashMap (java.util.LinkedHashMap)3 List (java.util.List)3 ObservableList (javafx.collections.ObservableList)3 GargoyleException (com.kyj.fx.voeditor.visual.exceptions.GargoyleException)2 FileOutputStream (java.io.FileOutputStream)2 HashMap (java.util.HashMap)2 Map (java.util.Map)2 TableView (javafx.scene.control.TableView)2 Sheet (org.apache.poi.ss.usermodel.Sheet)2 Workbook (org.apache.poi.ss.usermodel.Workbook)2 CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)2 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)2 DefautExcelDataSetHandler (com.kyj.fx.voeditor.visual.framework.excel.DefautExcelDataSetHandler)1 IExcelDataSetHandler (com.kyj.fx.voeditor.visual.framework.excel.IExcelDataSetHandler)1 ITableColumnForExcel (com.kyj.fx.voeditor.visual.framework.excel.ITableColumnForExcel)1 ByteArrayOutputStream (java.io.ByteArrayOutputStream)1