use of jxl.write.Number in project cubrid-manager by CUBRID.
the class ExportToXlsHandler method exportByQuerying.
public void exportByQuerying(String tableName) throws IOException, SQLException {
if (StringUtil.isEmpty(tableName)) {
// FIXME move this logic to core module
return;
}
long totalRecord = exportConfig.getTotalCount(tableName);
if (totalRecord == 0) {
return;
}
Connection conn = null;
CUBRIDPreparedStatementProxy pStmt = null;
CUBRIDResultSetProxy rs = null;
WritableWorkbook workbook = null;
int workbookNum = 0;
String whereCondition = exportConfig.getWhereCondition(tableName);
boolean hasNextPage = true;
long beginIndex = 1;
int cellCharacterLimit = ImportFileConstants.XLSX_CELL_CHAR_LIMIT;
// 65536: limit xls row number.
int rowLimit = ImportFileConstants.XLS_ROW_LIMIT;
boolean isInitedColumnTitles = false;
List<String> columnTitles = new ArrayList<String>();
try {
conn = getConnection();
int sheetNum = 0;
int xlsRecordNum = 0;
workbook = createWorkbook(exportConfig.getDataFilePath(tableName), workbookNum++);
WritableSheet sheet = workbook.createSheet("Sheet " + sheetNum, sheetNum);
sheetNum++;
int exportedCount = 0;
String sql = QueryUtil.getSelectSQL(conn, tableName);
isPaginating = isPagination(whereCondition, sql, whereCondition);
while (hasNextPage) {
try {
String executeSQL = null;
if (isPaginating) {
long endIndex = beginIndex + RSPAGESIZE;
executeSQL = getExecuteSQL(sql, beginIndex, endIndex, whereCondition);
executeSQL = dbInfo.wrapShardQuery(executeSQL);
beginIndex = endIndex + 1;
} else {
executeSQL = getExecuteSQL(sql, whereCondition);
executeSQL = dbInfo.wrapShardQuery(sql);
beginIndex = totalRecord + 1;
}
pStmt = getStatement(conn, executeSQL, tableName);
rs = (CUBRIDResultSetProxy) pStmt.executeQuery();
CUBRIDResultSetMetaDataProxy rsmt = (CUBRIDResultSetMetaDataProxy) rs.getMetaData();
int colCount = rsmt.getColumnCount();
if (!isInitedColumnTitles) {
isInitedColumnTitles = true;
columnTitles = getColumnTitleList(rsmt);
if (isExit) {
return;
}
// first line add column name
if (exportConfig.isFirstRowAsColumnName()) {
writeHeader(sheet, columnTitles);
xlsRecordNum++;
}
}
while (rs.next()) {
//Check memory
if (!CommonUITool.isAvailableMemory(REMAINING_MEMORY_SIZE)) {
closeWorkbook(workbook);
workbook = null;
System.gc();
workbook = createWorkbook(exportConfig.getDataFilePath(tableName), workbookNum++);
sheetNum = 0;
sheet = workbook.createSheet("Sheet " + sheetNum, sheetNum);
sheetNum++;
xlsRecordNum = 0;
// first line add column name
if (exportConfig.isFirstRowAsColumnName()) {
writeHeader(sheet, columnTitles);
xlsRecordNum++;
}
}
for (int j = 1; j <= colCount; j++) {
String colType = rsmt.getColumnTypeName(j);
colType = FieldHandlerUtils.amendDataTypeByResult(rs, j, colType);
int precision = rsmt.getPrecision(j);
setIsHasBigValue(colType, precision);
Object cellValue = FieldHandlerUtils.getRsValueForExport(colType, rs, j, exportConfig.getNULLValueTranslation());
// We need judge the CLOB/BLOD data by column type
if (DataType.DATATYPE_BLOB.equals(colType) || DataType.DATATYPE_CLOB.equals(colType)) {
if (DataType.DATATYPE_BLOB.equals(colType)) {
String fileName = exportBlobData(tableName, rs, j);
String dataCellValue = DataType.NULL_EXPORT_FORMAT;
if (StringUtil.isNotEmpty(fileName)) {
dataCellValue = DBAttrTypeFormatter.FILE_URL_PREFIX + tableName + BLOB_FOLDER_POSTFIX + File.separator + fileName;
}
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else {
String fileName = exportClobData(tableName, rs, j);
String dataCellValue = DataType.NULL_EXPORT_FORMAT;
if (StringUtil.isNotEmpty(fileName)) {
dataCellValue = DBAttrTypeFormatter.FILE_URL_PREFIX + tableName + CLOB_FOLDER_POSTFIX + File.separator + fileName;
}
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
}
} else if (cellValue instanceof Long) {
sheet.addCell(new Number(j - 1, xlsRecordNum, (Long) cellValue));
} else if (cellValue instanceof Double) {
sheet.addCell(new Number(j - 1, xlsRecordNum, (Double) cellValue));
} else if (cellValue instanceof Timestamp) {
String dataCellValue = FieldHandlerUtils.formatDateTime((Timestamp) cellValue);
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else if (cellValue instanceof java.sql.Time) {
String dataCellValue = DateUtil.getDatetimeString(((java.sql.Time) cellValue).getTime(), FieldHandlerUtils.FORMAT_TIME);
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else if (cellValue instanceof java.sql.Date) {
String dataCellValue = DateUtil.getDatetimeString(((java.sql.Date) cellValue).getTime(), FieldHandlerUtils.FORMAT_DATE);
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else {
sheet.addCell(new Label(j - 1, xlsRecordNum, cellValue.toString().length() > cellCharacterLimit ? cellValue.toString().substring(0, cellCharacterLimit) : cellValue.toString()));
}
}
xlsRecordNum++;
if ((xlsRecordNum + 1) % rowLimit == 0) {
xlsRecordNum = 0;
sheet = workbook.createSheet("Sheet " + sheetNum, sheetNum);
sheetNum++;
// first line add column name
if (exportConfig.isFirstRowAsColumnName()) {
writeHeader(sheet, columnTitles);
xlsRecordNum++;
}
}
exportedCount++;
if (exportedCount >= COMMIT_LINES) {
exportDataEventHandler.handleEvent(new ExportDataSuccessEvent(tableName, exportedCount));
exportedCount = 0;
}
if (stop) {
break;
}
}
exportDataEventHandler.handleEvent(new ExportDataSuccessEvent(tableName, exportedCount));
exportedCount = 0;
} catch (Exception e) {
LOGGER.error("", e);
exportDataEventHandler.handleEvent(new ExportDataFailedOneTableEvent(tableName));
} finally {
QueryUtil.freeQuery(pStmt, rs);
}
if (hasNextPage(beginIndex, totalRecord)) {
hasNextPage = true;
} else {
hasNextPage = false;
}
System.gc();
}
} catch (Exception e) {
LOGGER.error("", e);
} finally {
QueryUtil.freeQuery(conn);
closeWorkbook(workbook);
}
}
use of jxl.write.Number in project cubrid-manager by CUBRID.
the class ExportToXlsHandler method exportFromCache.
public void exportFromCache(String tableName) throws IOException {
if (StringUtil.isEmpty(tableName)) {
// FIXME move this logic to core module
return;
}
WritableWorkbook workbook = null;
int workbookNum = 0;
int cellCharacterLimit = ImportFileConstants.XLSX_CELL_CHAR_LIMIT;
// 65536: limit xls row number.
int rowLimit = ImportFileConstants.XLS_ROW_LIMIT;
boolean isInitedColumnTitles = false;
List<String> columnTitles = new ArrayList<String>();
try {
int sheetNum = 0;
int xlsRecordNum = 0;
workbook = createWorkbook(exportConfig.getDataFilePath(tableName), workbookNum++);
WritableSheet sheet = workbook.createSheet("Sheet " + sheetNum, sheetNum);
sheetNum++;
int exportedCount = 0;
try {
ResultSetDataCache resultSetDataCache = exportConfig.getResultSetDataCache();
List<ArrayList<Object>> datas = resultSetDataCache.getDatas();
List<ColumnInfo> columnInfos = resultSetDataCache.getColumnInfos();
int colCount = columnInfos.size();
if (!isInitedColumnTitles) {
isInitedColumnTitles = true;
for (ColumnInfo column : columnInfos) {
columnTitles.add(column.getName());
}
if (isExit) {
return;
}
// first line add column name
if (exportConfig.isFirstRowAsColumnName()) {
writeHeader(sheet, columnTitles);
xlsRecordNum++;
}
}
for (ArrayList<Object> rowData : datas) {
//Check memory
if (!CommonUITool.isAvailableMemory(REMAINING_MEMORY_SIZE)) {
closeWorkbook(workbook);
workbook = null;
System.gc();
workbook = createWorkbook(exportConfig.getDataFilePath(tableName), workbookNum++);
sheetNum = 0;
sheet = workbook.createSheet("Sheet " + sheetNum, sheetNum);
sheetNum++;
xlsRecordNum = 0;
// first line add column name
if (exportConfig.isFirstRowAsColumnName()) {
writeHeader(sheet, columnTitles);
xlsRecordNum++;
}
}
for (int j = 1; j <= colCount; j++) {
String colType = columnInfos.get(j - 1).getType();
int precision = columnInfos.get(j - 1).getPrecision();
setIsHasBigValue(colType, precision);
Object cellValue = rowData.get(j - 1);
// We need judge the CLOB/BLOD data by column type
if (DataType.DATATYPE_BLOB.equals(colType) || DataType.DATATYPE_CLOB.equals(colType)) {
if (DataType.DATATYPE_BLOB.equals(colType)) {
String fileName = exportBlobData(tableName, (Blob) cellValue);
String dataCellValue = DataType.NULL_EXPORT_FORMAT;
if (StringUtil.isNotEmpty(fileName)) {
dataCellValue = DBAttrTypeFormatter.FILE_URL_PREFIX + tableName + BLOB_FOLDER_POSTFIX + File.separator + fileName;
}
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else {
String fileName = exportClobData(tableName, (Clob) cellValue);
String dataCellValue = DataType.NULL_EXPORT_FORMAT;
if (StringUtil.isNotEmpty(fileName)) {
dataCellValue = DBAttrTypeFormatter.FILE_URL_PREFIX + tableName + CLOB_FOLDER_POSTFIX + File.separator + fileName;
}
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
}
} else if (cellValue instanceof Long) {
sheet.addCell(new Number(j - 1, xlsRecordNum, (Long) cellValue));
} else if (cellValue instanceof Double) {
sheet.addCell(new Number(j - 1, xlsRecordNum, (Double) cellValue));
} else if (cellValue instanceof Timestamp) {
String dataCellValue = FieldHandlerUtils.formatDateTime((Timestamp) cellValue);
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else if (cellValue instanceof java.sql.Time) {
String dataCellValue = DateUtil.getDatetimeString(((java.sql.Time) cellValue).getTime(), FieldHandlerUtils.FORMAT_TIME);
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else if (cellValue instanceof java.sql.Date) {
String dataCellValue = DateUtil.getDatetimeString(((java.sql.Date) cellValue).getTime(), FieldHandlerUtils.FORMAT_DATE);
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else {
sheet.addCell(new Label(j - 1, xlsRecordNum, cellValue.toString().length() > cellCharacterLimit ? cellValue.toString().substring(0, cellCharacterLimit) : cellValue.toString()));
}
}
xlsRecordNum++;
if ((xlsRecordNum + 1) % rowLimit == 0) {
xlsRecordNum = 0;
sheet = workbook.createSheet("Sheet " + sheetNum, sheetNum);
sheetNum++;
// first line add column name
if (exportConfig.isFirstRowAsColumnName()) {
writeHeader(sheet, columnTitles);
xlsRecordNum++;
}
}
exportedCount++;
if (exportedCount >= COMMIT_LINES) {
exportDataEventHandler.handleEvent(new ExportDataSuccessEvent(tableName, exportedCount));
exportedCount = 0;
}
if (stop) {
break;
}
}
exportDataEventHandler.handleEvent(new ExportDataSuccessEvent(tableName, exportedCount));
exportedCount = 0;
} catch (Exception e) {
LOGGER.error("", e);
exportDataEventHandler.handleEvent(new ExportDataFailedOneTableEvent(tableName));
}
System.gc();
} catch (Exception e) {
LOGGER.error("", e);
} finally {
closeWorkbook(workbook);
}
}
use of jxl.write.Number in project cubrid-manager by CUBRID.
the class ExportTableDataTask method exportXls.
/**
* Export data as XLS file format
*
* @param rs CUBRIDResultSetProxy
* @param monitor IProgressMonitor
*/
private void exportXls(CUBRIDResultSetProxy rs, final IProgressMonitor monitor) {
// FIXME move this logic to core module
WritableWorkbook workbook = null;
try {
int sheetNum = 0;
workbook = Workbook.createWorkbook(file);
if (fileCharset == null || fileCharset.trim().length() == 0) {
workbook = Workbook.createWorkbook(file);
} else {
WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setEncoding(fileCharset);
workbook = Workbook.createWorkbook(file, workbookSettings);
}
WritableSheet sheet = workbook.createSheet("Sheet " + sheetNum, sheetNum);
// 65536: limit xls row number.
int rowLimit = ImportFileConstants.XLS_ROW_LIMIT;
// it set 257. Because Tbl's first column is oid value that doesn't export
// 256: limit xls column number.
int columnLimit = ImportFileConstants.XLS_COLUMN_LIMIT + 1;
int cellCharacterLimit = ImportFileConstants.XLSX_CELL_CHAR_LIMIT;
CUBRIDResultSetMetaDataProxy rsmt = (CUBRIDResultSetMetaDataProxy) rs.getMetaData();
int colCount = rsmt.getColumnCount() + 1;
isExit = false;
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;
}
}
// first line add column name
if (isFirstRowAsColumnName) {
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false);
WritableCellFormat wcf = new WritableCellFormat(wf);
if (columnName.length() > cellCharacterLimit) {
Label label = new Label(j - 1, 0, columnName.substring(0, cellCharacterLimit));
sheet.addCell(label);
label = null;
} else {
Label label = new Label(j - 1, 0, columnName.toString(), wcf);
sheet.addCell(label);
label = null;
}
wcf = null;
wf = null;
}
}
if (colCount > columnLimit) {
Display.getDefault().syncExec(new Runnable() {
public void run() {
if (!CommonUITool.openConfirmBox(Messages.exportColumnCountOverWarnInfo)) {
isExit = true;
}
}
});
if (isExit) {
return;
}
colCount = columnLimit;
}
int xlsRecordNum = 0;
if (isFirstRowAsColumnName) {
xlsRecordNum = 1;
}
while (rs.next()) {
if (!CommonUITool.isAvailableMemory(REMAINING_MEMORY_SIZE)) {
throw new OutOfMemoryError();
}
for (int j = 1; j < colCount; j++) {
String colType = rsmt.getColumnTypeName(j);
colType = FieldHandlerUtils.amendDataTypeByResult(rs, j, colType);
int precision = rsmt.getPrecision(j);
setIsHasBigValue(colType, precision);
Object cellValue = FieldHandlerUtils.getRsValueForExport(colType, rs, j, nullValue);
if (cellValue instanceof Long) {
sheet.addCell(new Number(j - 1, xlsRecordNum, (Long) cellValue));
} else if (cellValue instanceof Double) {
sheet.addCell(new Number(j - 1, xlsRecordNum, (Double) cellValue));
} else if (cellValue instanceof Timestamp) {
String dataCellValue = FieldHandlerUtils.formatDateTime((Timestamp) cellValue);
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else if (cellValue instanceof java.sql.Time) {
String dataCellValue = DateUtil.getDatetimeString(((java.sql.Time) cellValue).getTime(), FieldHandlerUtils.FORMAT_TIME);
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else if (cellValue instanceof java.sql.Date) {
String dataCellValue = DateUtil.getDatetimeString(((java.sql.Date) cellValue).getTime(), FieldHandlerUtils.FORMAT_DATE);
sheet.addCell(new Label(j - 1, xlsRecordNum, dataCellValue));
} else {
sheet.addCell(new Label(j - 1, xlsRecordNum, cellValue.toString().length() > cellCharacterLimit ? cellValue.toString().substring(0, cellCharacterLimit) : cellValue.toString()));
}
}
xlsRecordNum++;
if (((exportedCount + 1) % rowLimit) == 0) {
sheetNum++;
xlsRecordNum -= rowLimit;
sheet = workbook.createSheet("Sheet " + sheetNum, sheetNum);
}
exportedCount++;
monitor.worked(10);
monitor.subTask(Messages.bind(Messages.msgExportDataRow, exportedCount));
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (workbook != null) {
workbook.write();
}
} catch (Exception ignored) {
}
try {
if (workbook != null) {
workbook.close();
}
} catch (Exception ignored) {
}
}
}
use of jxl.write.Number in project dhis2-core by dhis2.
the class GridUtils method toXlsInternal.
private static void toXlsInternal(Grid grid, WritableWorkbook workbook, String sheetName, int sheetNo) throws Exception {
if (grid == null) {
return;
}
int cols = grid.getVisibleHeaders().size();
if (cols > JXL_MAX_COLS) {
log.warn("Grid will be truncated, no of columns is greater than JXL max limit: " + cols + "/" + JXL_MAX_COLS);
}
WritableSheet sheet = workbook.createSheet(sheetName, sheetNo);
int rowNumber = 0;
int columnIndex = 0;
if (StringUtils.isNotEmpty(grid.getTitle())) {
sheet.addCell(new Label(0, rowNumber++, grid.getTitle(), XLS_FORMAT_TTTLE));
rowNumber++;
}
if (StringUtils.isNotEmpty(grid.getSubtitle())) {
sheet.addCell(new Label(0, rowNumber++, grid.getSubtitle(), XLS_FORMAT_TTTLE));
rowNumber++;
}
List<GridHeader> headers = ListUtils.subList(grid.getVisibleHeaders(), 0, JXL_MAX_COLS);
for (GridHeader header : headers) {
sheet.addCell(new Label(columnIndex++, rowNumber, header.getName(), XLS_FORMAT_LABEL));
}
rowNumber++;
for (List<Object> row : grid.getVisibleRows()) {
columnIndex = 0;
List<Object> colums = ListUtils.subList(row, 0, JXL_MAX_COLS);
for (Object column : colums) {
if (column != null && MathUtils.isNumeric(String.valueOf(column))) {
sheet.addCell(new Number(columnIndex++, rowNumber, Double.valueOf(String.valueOf(column)), XLS_FORMAT_TEXT));
} else {
String content = column != null ? String.valueOf(column) : EMPTY;
sheet.addCell(new Label(columnIndex++, rowNumber, content, XLS_FORMAT_TEXT));
}
}
rowNumber++;
}
}
Aggregations