Examples with XlsxWriterHelper com.cubrid.common.ui.cubrid.table.control.XlsxWriterHelper used on opensource projects

Example 1 with XlsxWriterHelper

use of com.cubrid.common.ui.cubrid.table.control.XlsxWriterHelper in project cubrid-manager by CUBRID.

the class Export method exportXlsx.

/**
	 * export all data in Query Editor result table cache as xlsx
	 *
	 * @param monitor IProgressMonitor
	 * @throws IOException if failed
	 */
private void exportXlsx(final IProgressMonitor monitor) throws IOException {
    // FIXME move this logic to core module
    // 1048576: limit xlsx row number except for column row.
    final int rowLimit = ImportFileConstants.XLSX_ROW_LIMIT - 1;
    // 16384: limit xlsx column number.
    final int columnLimit = ImportFileConstants.XLSX_COLUMN_LIMIT;
    final int cellCharacterLimit = ImportFileConstants.XLSX_CELL_CHAR_LIMIT;
    //		//create dateformat
    //		SimpleDateFormat datetimeSdf = new SimpleDateFormat(
    //				"yyyy-MM-dd HH:mm:ss.SSS", Locale.getDefault());
    //		SimpleDateFormat timestampSdf = new SimpleDateFormat(
    //				"yyyy-MM-dd HH:mm:ss", Locale.getDefault());
    //		SimpleDateFormat dateSdf = new SimpleDateFormat("yyyy-MM-dd",
    //				Locale.getDefault());
    //		SimpleDateFormat timeSdf = new SimpleDateFormat("HH:mm:ss",
    //				Locale.getDefault());
    //		Date date = null;
    //create memory workbook
    XlsxWriterHelper xlsxWriterhelper = new XlsxWriterHelper();
    XSSFWorkbook workbook = new XSSFWorkbook();
    //		Calendar cal = Calendar.getInstance();
    //		int datetimeStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(
    //				workbook).get("datetime")).getIndex();
    //		int timestampStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(
    //				workbook).get("timestamp")).getIndex();
    //		int dateStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(
    //				workbook).get("date")).getIndex();
    //		int timeStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(
    //				workbook).get("time")).getIndex();
    Map<String, File> fileMap = new HashMap<String, File>();
    try {
        for (int k = 0; k < resultDataList.size(); k++) {
            List<ColumnInfo> columnList = resultColsList.get(k);
            List<Map<String, String>> dataList = resultDataList.get(k);
            int colCount = columnList.size();
            int itemCount = dataList.size();
            if (colCount > columnLimit) {
                if (!CommonUITool.openConfirmBox(Messages.columnCountOver)) {
                    return;
                }
                colCount = columnLimit;
            }
            XlsxWriterHelper.SpreadsheetWriter sheetWriter = null;
            try {
                sheetWriter = createSheetWriter(workbook, "Sheet " + (k + 1), fileMap);
                //export columns
                exportColumnsForXLSX(sheetWriter, k, columnLimit);
                int sheetNum = 0;
                for (int i = 0, xssfRowNum = 1; i < itemCount; i++) {
                    sheetWriter.insertRow(xssfRowNum);
                    for (int j = 0; j < colCount; j++) {
                        String colType = columnList.get(j).getType();
                        String colIndex = columnList.get(j).getIndex();
                        String cellValue = dataList.get(i).get(colIndex);
                        int cellType = FieldHandlerUtils.getCellType(colType, cellValue);
                        switch(cellType) {
                            case -1:
                                sheetWriter.createCell(j, DataType.NULL_EXPORT_FORMAT);
                                break;
                            case 0:
                                sheetWriter.createCell(j, Long.parseLong(cellValue));
                                break;
                            case 1:
                                sheetWriter.createCell(j, Double.parseDouble(cellValue));
                                break;
                            //								break;
                            case 2:
                            default:
                                String cellStr = cellValue.toString().length() > cellCharacterLimit ? cellValue.toString().substring(0, cellCharacterLimit) : cellValue.toString();
                                sheetWriter.createCell(j, covertXMLString(cellStr));
                                break;
                        }
                    }
                    sheetWriter.endRow();
                    xssfRowNum++;
                    if (((i + 1) % rowLimit) == 0 && (i + 1) < itemCount) {
                        sheetNum++;
                        try {
                            XlsxWriterHelper.writeSheetWriter(sheetWriter);
                        } catch (IOException e) {
                            sheetWriter = null;
                            throw e;
                        }
                        sheetWriter = createSheetWriter(workbook, "Sheet " + (k + 1) + "_" + sheetNum, fileMap);
                        exportColumnsForXLSX(sheetWriter, k, columnLimit);
                        xssfRowNum = 1;
                    }
                    exportedCount++;
                    monitor.subTask(Messages.bind(com.cubrid.common.ui.cubrid.table.Messages.msgExportDataRow, exportedCount));
                }
            } finally {
                try {
                    XlsxWriterHelper.writeSheetWriter(sheetWriter);
                } catch (IOException e) {
                    sheetWriter = null;
                    throw e;
                }
            }
        }
    } finally {
        XlsxWriterHelper.writeWorkbook(workbook, xlsxWriterhelper, fileMap, file);
    }
}

Example 2 with XlsxWriterHelper

use of com.cubrid.common.ui.cubrid.table.control.XlsxWriterHelper in project cubrid-manager by CUBRID.

the class ExportTableDataTask method createSheetWriter.

/**
	 * Create the instance of SpreadsheetWriter and based upon the given
	 * condition writing the header of a sheet
	 *
	 * @param workbook the instance of Workbook
	 * @param xlsxWriterhelper the instance of XlsxWriterHelper
	 * @param sheetNum the number of a sheet
	 * @param fileMap a map includes the temporary file and its name
	 * @param columnTitles the column title
	 * @param xssfRowNum the number of row
	 * @throws IOException the exception
	 * @return the instance of XlsxWriterHelper.SpreadsheetWriter
	 */
private XlsxWriterHelper.SpreadsheetWriter createSheetWriter(XSSFWorkbook workbook, XlsxWriterHelper xlsxWriterhelper, int sheetNum, Map<String, File> fileMap, List<String> columnTitles, int xssfRowNum) throws IOException {
    // FIXME move this logic to core module
    XSSFSheet sheet = workbook.createSheet("sheet" + sheetNum);
    String sheetRef = sheet.getPackagePart().getPartName().getName().substring(1);
    File tmp = File.createTempFile("sheet" + sheetNum, ".xml");
    fileMap.put(sheetRef, tmp);
    String charset = null;
    if (fileCharset == null || fileCharset.trim().length() == 0) {
        charset = "UTF-8";
    } else {
        charset = fileCharset;
    }
    OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(tmp), charset);
    XlsxWriterHelper.SpreadsheetWriter sheetWriter = new XlsxWriterHelper.SpreadsheetWriter(writer);
    sheetWriter.setCharset(charset);
    sheetWriter.beginSheet();
    if (isFirstRowAsColumnName && columnTitles != null) {
        sheetWriter.insertRow(xssfRowNum);
        int styleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(workbook).get("header")).getIndex();
        for (int index = 0; index < columnTitles.size(); index++) {
            sheetWriter.createCell(index, columnTitles.get(index), styleIndex);
        }
        sheetWriter.endRow();
    }
    return sheetWriter;
}

Example 3 with XlsxWriterHelper

use of com.cubrid.common.ui.cubrid.table.control.XlsxWriterHelper in project cubrid-manager by CUBRID.

the class ExportToXlsxHandler method createSheetWriter.

/**
	 * Create the instance of SpreadsheetWriter and based upon the given
	 * condition writing the header of a sheet
	 *
	 * @param workbook the instance of Workbook
	 * @param xlsxWriterhelper the instance of XlsxWriterHelper
	 * @param sheetNum the number of a sheet
	 * @param fileMap a map includes the temporary file and its name
	 * @param columnTitles the column title
	 * @param xssfRowNum the number of row
	 * @throws IOException the exception
	 * @return the instance of XlsxWriterHelper.SpreadsheetWriter
	 */
private XlsxWriterHelper.SpreadsheetWriter createSheetWriter(XSSFWorkbook workbook, XlsxWriterHelper xlsxWriterhelper, int sheetNum, Map<String, File> fileMap, List<String> columnTitles, int xssfRowNum) throws IOException {
    // FIXME move this logic to core module
    XSSFSheet sheet = workbook.createSheet("sheet" + sheetNum);
    String sheetRef = sheet.getPackagePart().getPartName().getName().substring(1);
    File tmp = File.createTempFile("sheet" + sheetNum, ".xml");
    fileMap.put(sheetRef, tmp);
    String charset = null;
    if (StringUtil.isEmpty(exportConfig.getFileCharset())) {
        charset = "UTF-8";
    } else {
        charset = exportConfig.getFileCharset();
    }
    OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(tmp), charset);
    XlsxWriterHelper.SpreadsheetWriter sheetWriter = new XlsxWriterHelper.SpreadsheetWriter(writer);
    sheetWriter.setCharset(charset);
    sheetWriter.beginSheet();
    if (exportConfig.isFirstRowAsColumnName() && columnTitles != null) {
        sheetWriter.insertRow(xssfRowNum);
        int styleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(workbook).get("header")).getIndex();
        for (int index = 0; index < columnTitles.size(); index++) {
            sheetWriter.createCell(index, columnTitles.get(index), styleIndex);
        }
        sheetWriter.endRow();
    }
    return sheetWriter;
}

Example 4 with XlsxWriterHelper

use of com.cubrid.common.ui.cubrid.table.control.XlsxWriterHelper in project cubrid-manager by CUBRID.

the class ExportTableDataTask method exportXlsx.

/**
	 *
	 * Export data as XLSX file format
	 *
	 * @param rs CUBRIDResultSetProxy
	 * @param monitor IProgressMonitor
	 * @throws IOException The exception
	 * @throws SQLException The exception
	 */
private void exportXlsx(CUBRIDResultSetProxy rs, final IProgressMonitor monitor) throws IOException, SQLException {
    // FIXME move this logic to core module
    // 1048576: limit xlsx row number.
    int rowLimit = ImportFileConstants.XLSX_ROW_LIMIT;
    // 16384: limit xlsx column number.
    int columnLimit = ImportFileConstants.XLSX_COLUMN_LIMIT;
    int cellCharacterLimit = ImportFileConstants.XLSX_CELL_CHAR_LIMIT;
    CUBRIDResultSetMetaDataProxy rsmt = (CUBRIDResultSetMetaDataProxy) rs.getMetaData();
    int colCount = rsmt.getColumnCount();
    if (colCount >= columnLimit) {
        Display.getDefault().syncExec(new Runnable() {

            public void run() {
                if (!CommonUITool.openConfirmBox(Messages.exportColumnCountOverWarnInfo)) {
                    isExit = true;
                }
            }
        });
        if (isExit) {
            return;
        }
        colCount = columnLimit;
    }
    List<String> columnTitles = new ArrayList<String>();
    for (int j = 1; j <= colCount; j++) {
        String columnName = rsmt.getColumnName(j);
        String columnType = rsmt.getColumnTypeName(j);
        int precision = rsmt.getPrecision(j);
        columnType = columnType == null ? "" : columnType;
        setIsHasBigValue(columnType, precision);
        //the data length > XLS column character limit
        if (precision > cellCharacterLimit) {
            final String confirmMSG = Messages.bind(Messages.exportCharacterCountExceedWarnInfo, columnName);
            Display.getDefault().syncExec(new Runnable() {

                public void run() {
                    if (!CommonUITool.openConfirmBox(confirmMSG)) {
                        isExit = true;
                    }
                }
            });
            if (isExit) {
                return;
            }
        }
        if (columnName.length() > cellCharacterLimit) {
            columnName = columnName.substring(0, cellCharacterLimit);
        }
        columnTitles.add(columnName);
    }
    XlsxWriterHelper xlsxWriterhelper = new XlsxWriterHelper();
    //create memory workbook
    XSSFWorkbook workbook = new XSSFWorkbook();
    Calendar cal = Calendar.getInstance();
    int datetimeStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(workbook).get("datetime")).getIndex();
    int timestampStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(workbook).get("timestamp")).getIndex();
    int dateStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(workbook).get("date")).getIndex();
    int timeStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(workbook).get("time")).getIndex();
    int sheetNum = 1;
    int xssfRowNum = 0;
    Map<String, File> fileMap = new HashMap<String, File>();
    XlsxWriterHelper.SpreadsheetWriter sheetWriter = null;
    try {
        sheetWriter = createSheetWriter(workbook, xlsxWriterhelper, sheetNum, fileMap, columnTitles, xssfRowNum);
        if (isFirstRowAsColumnName && columnTitles != null) {
            xssfRowNum++;
        }
        while (rs.next()) {
            sheetWriter.insertRow(xssfRowNum);
            for (int k = 1; k <= colCount; k++) {
                String colType = rsmt.getColumnTypeName(k);
                colType = FieldHandlerUtils.amendDataTypeByResult(rs, k, colType);
                int precision = rsmt.getPrecision(k);
                setIsHasBigValue(colType, precision);
                Object cellValue = FieldHandlerUtils.getRsValueForExport(colType, rs, k, nullValue);
                if (cellValue instanceof Long) {
                    sheetWriter.createCell(k - 1, ((Long) cellValue).longValue());
                } else if (cellValue instanceof Double) {
                    sheetWriter.createCell(k - 1, ((Double) cellValue).doubleValue());
                } else if (cellValue instanceof Date) {
                    cal.setTime((Date) cellValue);
                    if (DataType.DATATYPE_DATETIME.equals(colType)) {
                        sheetWriter.createCell(k - 1, cal, datetimeStyleIndex);
                    } else if (DataType.DATATYPE_DATE.equals(colType)) {
                        sheetWriter.createCell(k - 1, cal, dateStyleIndex);
                    } else if (DataType.DATATYPE_TIME.equals(colType)) {
                        sheetWriter.createCell(k - 1, cal, timeStyleIndex);
                    } else {
                        sheetWriter.createCell(k - 1, cal, timestampStyleIndex);
                    }
                } else {
                    String cellStr = cellValue.toString().length() > cellCharacterLimit ? cellValue.toString().substring(0, cellCharacterLimit) : cellValue.toString();
                    sheetWriter.createCell(k - 1, Export.covertXMLString(cellStr));
                }
            }
            sheetWriter.endRow();
            xssfRowNum++;
            if (((exportedCount + 1) % rowLimit) == 0) {
                sheetNum++;
                xssfRowNum -= rowLimit;
                try {
                    XlsxWriterHelper.writeSheetWriter(sheetWriter);
                } catch (IOException e) {
                    sheetWriter = null;
                    throw e;
                }
                sheetWriter = createSheetWriter(workbook, xlsxWriterhelper, sheetNum, fileMap, columnTitles, xssfRowNum);
            }
            exportedCount++;
            monitor.worked(10);
            monitor.subTask(Messages.bind(Messages.msgExportDataRow, exportedCount));
        }
    } finally {
        try {
            XlsxWriterHelper.writeSheetWriter(sheetWriter);
        } catch (IOException e) {
            sheetWriter = null;
            throw e;
        } finally {
            XlsxWriterHelper.writeWorkbook(workbook, xlsxWriterhelper, fileMap, file);
        }
    }
}

Example 5 with XlsxWriterHelper

use of com.cubrid.common.ui.cubrid.table.control.XlsxWriterHelper in project cubrid-manager by CUBRID.

the class ExportConnectionUtil method writeToXlsx.

/**
	 * Write to xlsx
	 *
	 * @throws IOException
	 */
private void writeToXlsx() throws IOException {
    // FIXME split logic and ui
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet(Messages.sheetNameConnections);
    String sheetRef = sheet.getPackagePart().getPartName().getName().substring(1);
    File tmp = File.createTempFile("Connections", ".xml");
    Map<String, File> fileMap = new HashMap<String, File>();
    fileMap.put(sheetRef, tmp);
    OutputStreamWriter writer = null;
    XlsxWriterHelper xlsxWriterhelper = null;
    XlsxWriterHelper.SpreadsheetWriter sheetWriter = null;
    try {
        writer = new OutputStreamWriter(new FileOutputStream(tmp), "UTF-8");
        xlsxWriterhelper = new XlsxWriterHelper();
        sheetWriter = new XlsxWriterHelper.SpreadsheetWriter(writer);
        sheetWriter.setCharset("UTF-8");
        sheetWriter.beginSheet();
        /* Write the header */
        int styleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(workbook).get("header")).getIndex();
        sheetWriter.insertRow(0);
        sheetWriter.createCell(0, Messages.nameColumn, styleIndex);
        sheetWriter.createCell(1, Messages.iPColumn, styleIndex);
        sheetWriter.createCell(2, Messages.portColumn, styleIndex);
        sheetWriter.createCell(3, Messages.userColumn, styleIndex);
        sheetWriter.createCell(4, Messages.commentColumn, styleIndex);
        sheetWriter.createCell(5, Messages.javaUrlColumn, styleIndex);
        sheetWriter.createCell(6, Messages.phpUrlColumn, styleIndex);
        sheetWriter.endRow();
        /* Write the data */
        int rowIndex = 1;
        for (CubridDatabase database : input) {
            if (database == null) {
                continue;
            }
            DatabaseInfo dbInfo = database.getDatabaseInfo();
            if (dbInfo == null) {
                continue;
            }
            sheetWriter.insertRow(rowIndex++);
            /* name */
            sheetWriter.createCell(0, dbInfo.getDbName());
            /* ip */
            sheetWriter.createCell(1, dbInfo.getBrokerIP());
            /* port */
            sheetWriter.createCell(2, dbInfo.getBrokerPort());
            /* user */
            sheetWriter.createCell(3, getDbUser(dbInfo));
            /* comment */
            String comment = "";
            DatabaseEditorConfig editorConfig = QueryOptions.getEditorConfig(database, managerMode);
            if (editorConfig != null && editorConfig.getDatabaseComment() != null) {
                comment = editorConfig.getDatabaseComment();
            }
            sheetWriter.createCell(4, comment);
            /* javaUrl */
            String url = NodeUtil.getJavaConnectionUrl(dbInfo);
            sheetWriter.createCell(5, url);
            /* phpUrl */
            String phpUrl = NodeUtil.getPHPConnectionUrl(dbInfo);
            sheetWriter.createCell(6, phpUrl);
            sheetWriter.endRow();
        }
        XlsxWriterHelper.writeSheetWriter(sheetWriter);
    } finally {
        XlsxWriterHelper.writeWorkbook(workbook, xlsxWriterhelper, fileMap, file);
    }
}