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);
}
}
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;
}
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;
}
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);
}
}
}
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);
}
}
Aggregations