Examples with WritableWorkbook jxl.write.WritableWorkbook used on opensource projects

Example 1 with WritableWorkbook

use of jxl.write.WritableWorkbook in project OpenClinica by OpenClinica.

the class ExportExcelStudySubjectAuditLogServlet method processRequest.

@Override
public void processRequest() throws Exception {
    StudySubjectDAO subdao = new StudySubjectDAO(sm.getDataSource());
    SubjectDAO sdao = new SubjectDAO(sm.getDataSource());
    AuditDAO adao = new AuditDAO(sm.getDataSource());
    StudyEventDAO sedao = new StudyEventDAO(sm.getDataSource());
    StudyEventDefinitionDAO seddao = new StudyEventDefinitionDAO(sm.getDataSource());
    EventDefinitionCRFDAO edcdao = new EventDefinitionCRFDAO(sm.getDataSource());
    EventCRFDAO ecdao = new EventCRFDAO(sm.getDataSource());
    StudyDAO studydao = new StudyDAO(sm.getDataSource());
    CRFDAO cdao = new CRFDAO(sm.getDataSource());
    CRFVersionDAO cvdao = new CRFVersionDAO(sm.getDataSource());
    StudySubjectBean studySubject = null;
    SubjectBean subject = null;
    ArrayList events = null;
    ArrayList studySubjectAudits = new ArrayList();
    ArrayList eventCRFAudits = new ArrayList();
    ArrayList studyEventAudits = new ArrayList();
    ArrayList allDeletedEventCRFs = new ArrayList();
    ArrayList allEventCRFs = new ArrayList();
    ArrayList allEventCRFItems = new ArrayList();
    String attachedFilePath = Utils.getAttachedFilePath(currentStudy);
    FormProcessor fp = new FormProcessor(request);
    int studySubId = fp.getInt("id", true);
    if (studySubId == 0) {
        addPageMessage(respage.getString("please_choose_a_subject_to_view"));
        forwardPage(Page.LIST_STUDY_SUBJECTS);
    } else {
        studySubject = (StudySubjectBean) subdao.findByPK(studySubId);
        StudyBean study = (StudyBean) studydao.findByPK(studySubject.getStudyId());
        // Check if this StudySubject would be accessed from the Current Study
        if (studySubject.getStudyId() != currentStudy.getId()) {
            if (currentStudy.getParentStudyId() > 0) {
                addPageMessage(respage.getString("no_have_correct_privilege_current_study") + " " + respage.getString("change_active_study_or_contact"));
                forwardPage(Page.MENU_SERVLET);
                return;
            } else {
                // The SubjectStudy is not belong to currentstudy and current study is not a site.
                Collection sites = studydao.findOlnySiteIdsByStudy(currentStudy);
                if (!sites.contains(study.getId())) {
                    addPageMessage(respage.getString("no_have_correct_privilege_current_study") + " " + respage.getString("change_active_study_or_contact"));
                    forwardPage(Page.MENU_SERVLET);
                    return;
                }
            }
        }
        subject = (SubjectBean) sdao.findByPK(studySubject.getSubjectId());
        /* Show both study subject and subject audit events together */
        // Study subject value changed
        Collection studySubjectAuditEvents = adao.findStudySubjectAuditEvents(studySubject.getId());
        // integer values.
        for (Iterator iterator = studySubjectAuditEvents.iterator(); iterator.hasNext(); ) {
            AuditBean auditBean = (AuditBean) iterator.next();
            if (auditBean.getAuditEventTypeId() == 3) {
                auditBean.setOldValue(Status.get(Integer.parseInt(auditBean.getOldValue())).getName());
                auditBean.setNewValue(Status.get(Integer.parseInt(auditBean.getNewValue())).getName());
            }
        }
        studySubjectAudits.addAll(studySubjectAuditEvents);
        // Global subject value changed
        studySubjectAudits.addAll(adao.findSubjectAuditEvents(subject.getId()));
        studySubjectAudits.addAll(adao.findStudySubjectGroupAssignmentAuditEvents(studySubject.getId()));
        // Get the list of events
        events = sedao.findAllByStudySubject(studySubject);
        for (int i = 0; i < events.size(); i++) {
            // Link study event definitions
            StudyEventBean studyEvent = (StudyEventBean) events.get(i);
            studyEvent.setStudyEventDefinition((StudyEventDefinitionBean) seddao.findByPK(studyEvent.getStudyEventDefinitionId()));
            // Link event CRFs
            studyEvent.setEventCRFs(ecdao.findAllByStudyEvent(studyEvent));
            // Find deleted Event CRFs
            List deletedEventCRFs = adao.findDeletedEventCRFsFromAuditEvent(studyEvent.getId());
            allDeletedEventCRFs.addAll(deletedEventCRFs);
            List eventCRFs = (List) adao.findAllEventCRFAuditEvents(studyEvent.getId());
            allEventCRFs.addAll(eventCRFs);
            List eventCRFItems = (List) adao.findAllEventCRFAuditEventsWithItemDataType(studyEvent.getId());
            allEventCRFItems.addAll(eventCRFItems);
            logger.info("deletedEventCRFs size[" + deletedEventCRFs.size() + "]");
            logger.info("allEventCRFItems size[" + allEventCRFItems.size() + "]");
        }
        for (int i = 0; i < events.size(); i++) {
            StudyEventBean studyEvent = (StudyEventBean) events.get(i);
            studyEventAudits.addAll(adao.findStudyEventAuditEvents(studyEvent.getId()));
            ArrayList eventCRFs = studyEvent.getEventCRFs();
            for (int j = 0; j < eventCRFs.size(); j++) {
                // Link CRF and CRF Versions
                EventCRFBean eventCRF = (EventCRFBean) eventCRFs.get(j);
                eventCRF.setCrfVersion((CRFVersionBean) cvdao.findByPK(eventCRF.getCRFVersionId()));
                eventCRF.setCrf(cdao.findByVersionId(eventCRF.getCRFVersionId()));
                // Get the event crf audits
                eventCRFAudits.addAll(adao.findEventCRFAuditEventsWithItemDataType(eventCRF.getId()));
                logger.info("eventCRFAudits size [" + eventCRFAudits.size() + "] eventCRF id [" + eventCRF.getId() + "]");
            }
        }
        ItemDataDAO itemDataDao = new ItemDataDAO(sm.getDataSource());
        for (Object o : eventCRFAudits) {
            AuditBean ab = (AuditBean) o;
            if (ab.getAuditTable().equalsIgnoreCase("item_data")) {
                ItemDataBean idBean = (ItemDataBean) itemDataDao.findByPK(ab.getEntityId());
                ab.setOrdinal(idBean.getOrdinal());
            }
        }
    }
    try {
        WritableFont headerFormat = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLUE2);
        WritableCellFormat cellFormat = new WritableCellFormat();
        cellFormat.setFont(headerFormat);
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=export.xls");
        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setLocale(new Locale("en", "EN"));
        WritableWorkbook workbook = Workbook.createWorkbook(response.getOutputStream(), wbSettings);
        int row = 0;
        // Subject Information
        workbook.createSheet("Subject Information", 0);
        WritableSheet excelSheet = workbook.getSheet(0);
        // Subject Summary
        String[] excelRow = new String[] { "study_subject_ID", "secondary_subject_ID", "date_of_birth", "person_ID", "created_by", "status" };
        for (int i = 0; i < excelRow.length; i++) {
            Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
            excelSheet.addCell(label);
        }
        row++;
        excelRow = new String[] { studySubject.getLabel(), studySubject.getSecondaryLabel(), dateFormat(subject.getDateOfBirth()), subject.getUniqueIdentifier(), studySubject.getOwner().getName(), studySubject.getStatus().getName() };
        for (int i = 0; i < excelRow.length; i++) {
            Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
            excelSheet.addCell(label);
        }
        row++;
        row++;
        // Subject Audit Events
        excelRow = new String[] { "audit_event", "date_time_of_server", "user", "value_type", "old", "new" };
        for (int i = 0; i < excelRow.length; i++) {
            Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
            excelSheet.addCell(label);
        }
        row++;
        for (int j = 0; j < studySubjectAudits.size(); j++) {
            AuditBean audit = (AuditBean) studySubjectAudits.get(j);
            excelRow = new String[] { audit.getAuditEventTypeName(), dateTimeFormat(audit.getAuditDate()), audit.getUserName(), audit.getEntityName(), audit.getOldValue(), audit.getNewValue() };
            for (int i = 0; i < excelRow.length; i++) {
                Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                excelSheet.addCell(label);
            }
            row++;
        }
        row++;
        // Study Events
        excelRow = new String[] { "study_events", "location", "date", "occurrence_number" };
        for (int i = 0; i < excelRow.length; i++) {
            Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
            excelSheet.addCell(label);
        }
        row++;
        for (int j = 0; j < events.size(); j++) {
            StudyEventBean event = (StudyEventBean) events.get(j);
            if (event.getStartTimeFlag()) {
                excelRow = new String[] { event.getStudyEventDefinition().getName(), event.getLocation(), dateTimeFormat(event.getDateStarted()), Integer.toString(event.getSampleOrdinal()) };
            } else {
                excelRow = new String[] { event.getStudyEventDefinition().getName(), event.getLocation(), dateFormat(event.getDateStarted()), Integer.toString(event.getSampleOrdinal()) };
            }
            for (int i = 0; i < excelRow.length; i++) {
                Label label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                excelSheet.addCell(label);
            }
            row++;
        }
        autoSizeColumns(excelSheet);
        int sheet = 0;
        // Study Event Summary Looper
        for (int eventCount = 0; eventCount < events.size(); eventCount++) {
            row = 0;
            sheet++;
            StudyEventBean event = (StudyEventBean) events.get(eventCount);
            workbook.createSheet(event.getStudyEventDefinition().getName().replace("/", ".") + "_" + event.getSampleOrdinal(), sheet);
            excelSheet = workbook.getSheet(sheet);
            Label label = null;
            // Header
            label = new Label(0, row, ResourceBundleProvider.getResWord("name"), cellFormat);
            excelSheet.addCell(label);
            label = new Label(1, row, event.getStudyEventDefinition().getName(), cellFormat);
            excelSheet.addCell(label);
            row++;
            label = new Label(0, row, "Location");
            excelSheet.addCell(label);
            label = new Label(1, row, event.getLocation());
            excelSheet.addCell(label);
            row++;
            label = new Label(0, row, "Start Date");
            excelSheet.addCell(label);
            if (event.getStartTimeFlag()) {
                label = new Label(1, row, dateTimeFormat(event.getDateStarted()));
            } else {
                label = new Label(1, row, dateFormat(event.getDateStarted()));
            }
            excelSheet.addCell(label);
            row++;
            label = new Label(0, row, "Status");
            excelSheet.addCell(label);
            label = new Label(1, row, event.getSubjectEventStatus().getName());
            excelSheet.addCell(label);
            row++;
            label = new Label(0, row, ResourceBundleProvider.getResWord("occurrence_number"));
            excelSheet.addCell(label);
            label = new Label(1, row, Integer.toString(event.getSampleOrdinal()));
            excelSheet.addCell(label);
            row++;
            row++;
            // End Header
            // Audit for Deleted Event CRFs
            excelRow = new String[] { "name", "version", "deleted_by", "delete_date" };
            for (int i = 0; i < excelRow.length; i++) {
                label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                excelSheet.addCell(label);
            }
            row++;
            for (int j = 0; j < allDeletedEventCRFs.size(); j++) {
                DeletedEventCRFBean deletedEventCRF = (DeletedEventCRFBean) allDeletedEventCRFs.get(j);
                if (deletedEventCRF.getStudyEventId() == event.getId()) {
                    excelRow = new String[] { deletedEventCRF.getCrfName(), deletedEventCRF.getFormLayout(), deletedEventCRF.getDeletedBy(), dateFormat(deletedEventCRF.getDeletedDate()) };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                }
            }
            row++;
            row++;
            // Audit Events for Study Event
            excelRow = new String[] { "audit_event", "date_time_of_server", "user", "value_type", "old", "new" };
            for (int i = 0; i < excelRow.length; i++) {
                label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                excelSheet.addCell(label);
            }
            row++;
            for (int j = 0; j < studyEventAudits.size(); j++) {
                AuditBean studyEvent = (AuditBean) studyEventAudits.get(j);
                if (studyEvent.getEntityId() == event.getId()) {
                    String getOld = studyEvent.getOldValue();
                    String oldValue = "";
                    if (getOld.equals("0"))
                        oldValue = "invalid";
                    else if (getOld.equals("1"))
                        oldValue = "scheduled";
                    else if (getOld.equals("2"))
                        oldValue = "not_scheduled";
                    else if (getOld.equals("3"))
                        oldValue = "data_entry_started";
                    else if (getOld.equals("4"))
                        oldValue = "completed";
                    else if (getOld.equals("5"))
                        oldValue = "stopped";
                    else if (getOld.equals("6"))
                        oldValue = "skipped";
                    else if (getOld.equals("7"))
                        oldValue = "locked";
                    else if (getOld.equals("8"))
                        oldValue = "signed";
                    else
                        oldValue = studyEvent.getOldValue();
                    String getNew = studyEvent.getNewValue();
                    String newValue = "";
                    if (getNew.equals("0"))
                        newValue = "invalid";
                    else if (getNew.equals("1"))
                        newValue = "scheduled";
                    else if (getNew.equals("2"))
                        newValue = "not_scheduled";
                    else if (getNew.equals("3"))
                        newValue = "data_entry_started";
                    else if (getNew.equals("4"))
                        newValue = "completed";
                    else if (getNew.equals("5"))
                        newValue = "removed";
                    else if (getNew.equals("6"))
                        newValue = "skipped";
                    else if (getNew.equals("7"))
                        newValue = "locked";
                    else if (getNew.equals("8"))
                        newValue = "signed";
                    else if (getNew.equals("9"))
                        newValue = "forzen";
                    else
                        newValue = studyEvent.getNewValue();
                    excelRow = new String[] { studyEvent.getAuditEventTypeName(), dateTimeFormat(studyEvent.getAuditDate()), studyEvent.getUserName(), studyEvent.getEntityName() + "(" + studyEvent.getOrdinal() + ")", oldValue, newValue };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                }
            }
            row++;
            row++;
            // Event CRFs Audit Events
            for (int j = 0; j < allEventCRFs.size(); j++) {
                AuditBean auditBean = (AuditBean) allEventCRFs.get(j);
                if (auditBean.getStudyEventId() == event.getId()) {
                    // Audit Events for Study Event
                    excelRow = new String[] { "name", "version", "date_interviewed", "interviewer_name", "owner" };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                    excelRow = new String[] { auditBean.getCrfName(), auditBean.getCrfVersionName(), dateFormat(auditBean.getDateInterviewed()), auditBean.getInterviewerName(), auditBean.getUserName() };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                    row++;
                    excelRow = new String[] { "audit_event", "date_time_of_server", "user", "value_type", "old", "new" };
                    for (int i = 0; i < excelRow.length; i++) {
                        label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                        excelSheet.addCell(label);
                    }
                    row++;
                    row++;
                    for (int k = 0; k < allEventCRFItems.size(); k++) {
                        row--;
                        AuditBean eventCrfAudit = (AuditBean) allEventCRFItems.get(k);
                        if (eventCrfAudit.getStudyEventId() == event.getId() && eventCrfAudit.getEventCrfVersionId() == auditBean.getEventCrfVersionId()) {
                            String oldValue = "";
                            String newValue = "";
                            if (eventCrfAudit.getAuditEventTypeId() == 12 || eventCrfAudit.getEntityName().equals("Status")) {
                                String getOld = eventCrfAudit.getOldValue();
                                if (getOld.equals("0"))
                                    oldValue = "invalid";
                                else if (getOld.equals("1"))
                                    oldValue = "available";
                                else if (getOld.equals("2"))
                                    oldValue = "unavailable";
                                else if (getOld.equals("3"))
                                    oldValue = "private";
                                else if (getOld.equals("4"))
                                    oldValue = "pending";
                                else if (getOld.equals("5"))
                                    oldValue = "removed";
                                else if (getOld.equals("6"))
                                    oldValue = "locked";
                                else if (getOld.equals("7"))
                                    oldValue = "auto-removed";
                                else {
                                    oldValue = getOld;
                                }
                            } else if (eventCrfAudit.getAuditEventTypeId() == 32) {
                                String getOld = eventCrfAudit.getOldValue();
                                if (getOld.equals("0"))
                                    oldValue = "FALSE";
                                else if (getOld.equals("1"))
                                    oldValue = "TRUE";
                                else {
                                    oldValue = getOld;
                                }
                            } else {
                                oldValue = eventCrfAudit.getOldValue();
                            }
                            if (eventCrfAudit.getAuditEventTypeId() == 12 || eventCrfAudit.getEntityName().equals("Status")) {
                                String getNew = eventCrfAudit.getNewValue();
                                if (getNew.equals("0"))
                                    newValue = "invalid";
                                else if (getNew.equals("1"))
                                    newValue = "available";
                                else if (getNew.equals("2"))
                                    newValue = "unavailable";
                                else if (getNew.equals("3"))
                                    newValue = "private";
                                else if (getNew.equals("4"))
                                    newValue = "pending";
                                else if (getNew.equals("5"))
                                    newValue = "removed";
                                else if (getNew.equals("6"))
                                    newValue = "locked";
                                else if (getNew.equals("7"))
                                    newValue = "auto-removed";
                                else {
                                    newValue = getNew;
                                }
                            } else if (eventCrfAudit.getAuditEventTypeId() == 32) {
                                String getNew = eventCrfAudit.getNewValue();
                                if (getNew.equals("0"))
                                    newValue = "FALSE";
                                else if (getNew.equals("1"))
                                    newValue = "TRUE";
                                else {
                                    newValue = getNew;
                                }
                            } else {
                                newValue = eventCrfAudit.getNewValue();
                            }
                            String ordinal = "";
                            if (eventCrfAudit.getOrdinal() != 0) {
                                ordinal = "(" + eventCrfAudit.getOrdinal() + ")";
                            } else if (eventCrfAudit.getOrdinal() == 0 && eventCrfAudit.getItemDataRepeatKey() != 0) {
                                ordinal = "(" + eventCrfAudit.getItemDataRepeatKey() + ")";
                            }
                            excelRow = new String[] { eventCrfAudit.getAuditEventTypeName(), dateTimeFormat(eventCrfAudit.getAuditDate()), eventCrfAudit.getUserName(), eventCrfAudit.getEntityName() + ordinal, oldValue, newValue };
                            for (int i = 0; i < excelRow.length; i++) {
                                label = new Label(i, row, ResourceBundleProvider.getResWord(excelRow[i]), cellFormat);
                                excelSheet.addCell(label);
                            }
                            row++;
                            row++;
                        }
                        row++;
                    }
                    row++;
                }
                autoSizeColumns(excelSheet);
            }
        }
        workbook.write();
        workbook.close();
        session.setAttribute("subject", null);
        session.setAttribute("study", null);
        session.setAttribute("studySub", null);
        session.setAttribute("studyEventAudits", null);
        session.setAttribute("studySubjectAudits", null);
        session.setAttribute("events", null);
        session.setAttribute("eventCRFAudits", null);
        session.setAttribute("allDeletedEventCRFs", null);
    } catch (Exception e) {
        throw e;
    } finally {
    // proposed move session attributes here
    }
}

Example 2 with WritableWorkbook

use of jxl.write.WritableWorkbook in project cubrid-manager by CUBRID.

the class RunSQLProcessManager method createExcel.

/**
	 * create excel workbook
	 * @param excelFile
	 * @return
	 */
public WritableWorkbook createExcel(File excelFile) {
    WritableWorkbook wwb = null;
    try {
        WorkbookSettings workbookSettings = new WorkbookSettings();
        workbookSettings.setEncoding(charset);
        wwb = Workbook.createWorkbook(excelFile, workbookSettings);
    } catch (Exception e) {
        LOGGER.error("create excel error", e);
    }
    return wwb;
}

Example 3 with WritableWorkbook

use of jxl.write.WritableWorkbook in project cubrid-manager by CUBRID.

the class ExportDashboardDialog method exportTableList.

/**
	 * export dashboard table list
	 * @return boolean sucess
	 */
public boolean exportTableList() throws Exception {
    WritableWorkbook wwb = null;
    WritableSheet ws = null;
    String fileName = saveExcelPath.getText() + saveExcelName.getText() + ".xls";
    try {
        WritableCellFormat normalCellStyle = getNormalCell();
        WorkbookSettings workbookSettings = new WorkbookSettings();
        workbookSettings.setEncoding(fileCharsetCombo.getText());
        wwb = Workbook.createWorkbook(new File(fileName), workbookSettings);
        for (int i = 0; i < exportTableList.size(); i++) {
            ws = wwb.createSheet(sheetNames[i], i);
            Table table = exportTableList.get(i);
            int rowIndex = 0;
            //title
            for (int j = 0; j < table.getColumnCount(); j++) {
                String cellString = table.getColumn(j).getText();
                ws.addCell(new jxl.write.Label(j, rowIndex, cellString, normalCellStyle));
                ws.setColumnView(j, 30);
            }
            rowIndex++;
            //row
            for (int j = 0; j < table.getItemCount(); j++) {
                TableItem tableItem = table.getItem(j);
                for (int k = 0; k < table.getColumnCount(); k++) {
                    String cellString = tableItem.getText(k);
                    ws.addCell(new jxl.write.Label(k, rowIndex, cellString, normalCellStyle));
                }
                rowIndex++;
            }
        }
        wwb.write();
        return true;
    } catch (Exception e) {
        LOGGER.error(e.getMessage());
        throw e;
    } finally {
        if (wwb != null) {
            try {
                wwb.close();
            } catch (Exception ex) {
                LOGGER.error("close excel stream error", ex);
            }
        }
    }
}

Example 4 with WritableWorkbook

use of jxl.write.WritableWorkbook 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);
    }
}

Example 5 with WritableWorkbook

use of jxl.write.WritableWorkbook in project cubrid-manager by CUBRID.

the class ExportToXlsHandler method createWorkbook.

private WritableWorkbook createWorkbook(String filePath, int workbookNum) throws IOException {
    // FIXME move this logic to core module
    File file = new File(getFixFileName(filePath, workbookNum));
    WritableWorkbook workbook = Workbook.createWorkbook(file);
    if (StringUtil.isEmpty(exportConfig.getFileCharset())) {
        workbook = Workbook.createWorkbook(file);
    } else {
        WorkbookSettings workbookSettings = new WorkbookSettings();
        workbookSettings.setEncoding(exportConfig.getFileCharset());
        workbook = Workbook.createWorkbook(file, workbookSettings);
    }
    return workbook;
}