Example 1 with WritableSheet
use of jxl.write.WritableSheet 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
}
}
Also used :
Locale(java.util.Locale)
StudySubjectDAO(org.akaza.openclinica.dao.managestudy.StudySubjectDAO)
SubjectDAO(org.akaza.openclinica.dao.submit.SubjectDAO)
DeletedEventCRFBean(org.akaza.openclinica.bean.admin.DeletedEventCRFBean)
ArrayList(java.util.ArrayList)
Label(jxl.write.Label)
StudyEventBean(org.akaza.openclinica.bean.managestudy.StudyEventBean)
ItemDataDAO(org.akaza.openclinica.dao.submit.ItemDataDAO)
AuditDAO(org.akaza.openclinica.dao.admin.AuditDAO)
DeletedEventCRFBean(org.akaza.openclinica.bean.admin.DeletedEventCRFBean)
EventCRFBean(org.akaza.openclinica.bean.submit.EventCRFBean)
ItemDataBean(org.akaza.openclinica.bean.submit.ItemDataBean)
StudyEventDAO(org.akaza.openclinica.dao.managestudy.StudyEventDAO)
Iterator(java.util.Iterator)
ArrayList(java.util.ArrayList)
List(java.util.List)
StudyDAO(org.akaza.openclinica.dao.managestudy.StudyDAO)
EventCRFDAO(org.akaza.openclinica.dao.submit.EventCRFDAO)
EventDefinitionCRFDAO(org.akaza.openclinica.dao.managestudy.EventDefinitionCRFDAO)
EventCRFDAO(org.akaza.openclinica.dao.submit.EventCRFDAO)
CRFDAO(org.akaza.openclinica.dao.admin.CRFDAO)
CRFVersionDAO(org.akaza.openclinica.dao.submit.CRFVersionDAO)
FormProcessor(org.akaza.openclinica.control.form.FormProcessor)
WritableFont(jxl.write.WritableFont)
StudyBean(org.akaza.openclinica.bean.managestudy.StudyBean)
WorkbookSettings(jxl.WorkbookSettings)
WritableSheet(jxl.write.WritableSheet)
EventDefinitionCRFDAO(org.akaza.openclinica.dao.managestudy.EventDefinitionCRFDAO)
AuditBean(org.akaza.openclinica.bean.admin.AuditBean)
StudySubjectDAO(org.akaza.openclinica.dao.managestudy.StudySubjectDAO)
WritableCellFormat(jxl.write.WritableCellFormat)
InsufficientPermissionException(org.akaza.openclinica.web.InsufficientPermissionException)
WritableWorkbook(jxl.write.WritableWorkbook)
SubjectBean(org.akaza.openclinica.bean.submit.SubjectBean)
StudySubjectBean(org.akaza.openclinica.bean.managestudy.StudySubjectBean)
StudyEventDefinitionDAO(org.akaza.openclinica.dao.managestudy.StudyEventDefinitionDAO)
StudySubjectBean(org.akaza.openclinica.bean.managestudy.StudySubjectBean)
Collection(java.util.Collection)
Example 2 with WritableSheet
use of jxl.write.WritableSheet in project cubrid-manager by CUBRID.
the class ExportTableDefinitionLayoutType1 method generateTableNamesSheet.
/**
* Generate table name sheet
*
* @param wwb
* @param exportTableNames
* @throws Exception
*/
public void generateTableNamesSheet(WritableWorkbook wwb, List<String> exportTableNames) throws Exception {
WritableSheet ws = wwb.createSheet(Messages.exportTableDefinitionCell1, 0);
// Tables
ws.addCell(new jxl.write.Label(0, 0, Messages.exportTableDefinitionCell2, boldCellStyle));
ws.mergeCells(0, 0, 5, 0);
// Project
ws.addCell(new jxl.write.Label(0, 1, Messages.exportTableDefinitionCell3, boldCellStyle));
ws.addCell(new jxl.write.Label(1, 1, "", normalCellStyle));
// Date
ws.addCell(new jxl.write.Label(2, 1, Messages.exportTableDefinitionCell4, boldCellStyle));
ws.addCell(new jxl.write.Label(3, 1, dateString, normalCellStyle));
// Author
ws.addCell(new jxl.write.Label(4, 1, Messages.exportTableDefinitionCell5, boldCellStyle));
ws.addCell(new jxl.write.Label(5, 1, "", normalCellStyle));
// Table Name
ws.addCell(new jxl.write.Label(0, 2, Messages.exportTableDefinitionCell6, boldCellStyle));
// Table Description
ws.addCell(new jxl.write.Label(1, 2, Messages.exportTableDefinitionCell27, boldCellStyle));
// Description
// ws.addCell(new jxl.write.Label(2, 2, Messages.exportTableDefinitionCell8, boldCellStyle));
// Memo
// ws.addCell(new jxl.write.Label(5, 2, Messages.exportTableDefinitionCell9, boldCellStyle));
ws.mergeCells(1, 2, 5, 2);
//table name data
int rowIndex = 3;
for (String tableName : exportTableNames) {
String tableColumnText = "";
if (getProgressObject().isInstalledMetaTable()) {
SchemaComment tableComment = SchemaCommentHandler.find(getProgressObject().getSchemaCommentMap(), tableName, null);
if (tableComment != null) {
tableColumnText = tableComment.getDescription() == null ? "" : tableComment.getDescription();
}
}
ws.addCell(new jxl.write.Label(0, rowIndex, tableName, normalLeftAlignCellStyle));
ws.addCell(new jxl.write.Label(1, rowIndex, tableColumnText, normalLeftAlignCellStyle));
ws.addCell(new jxl.write.Label(2, rowIndex, "", normalCellStyle));
ws.addCell(new jxl.write.Label(5, rowIndex, "", normalCellStyle));
ws.mergeCells(1, rowIndex, 5, rowIndex);
rowIndex++;
}
ws.setRowView(0, 500);
// column width
ws.setColumnView(0, 25);
ws.setColumnView(1, 28);
ws.setColumnView(2, 15);
ws.setColumnView(3, 18);
ws.setColumnView(4, 15);
ws.setColumnView(5, 20);
}
Also used :
WritableSheet(jxl.write.WritableSheet)
SchemaComment(com.cubrid.common.core.schemacomment.model.SchemaComment)
Constraint(com.cubrid.common.core.common.model.Constraint)
Example 3 with WritableSheet
use of jxl.write.WritableSheet in project cubrid-manager by CUBRID.
the class ExportTableDefinitionLayoutType2 method generateTableDetailSheets.
/**
* generate table name sheet
* @param wwb
* @param conn
* @param exportSchemaInfoList
* @param monitor
* @throws Exception
*/
public void generateTableDetailSheets(WritableWorkbook wwb, Connection conn, List<SchemaInfo> exportSchemaInfoList, IProgressMonitor monitor) throws Exception {
int sheetIndex = 1;
for (SchemaInfo schemaInfo : exportSchemaInfoList) {
String tableName = schemaInfo.getClassname();
monitor.subTask(Messages.bind(Messages.exportTableDefinitionProgressTaskWriteTable, tableName));
List<SchemaInfo> supers = SuperClassUtil.getSuperClasses(getProgressObject().getDatabase().getDatabaseInfo(), schemaInfo);
WritableSheet ws = wwb.createSheet(tableName, sheetIndex++);
int rowIndex = 0;
// Title
ws.addCell(new jxl.write.Label(0, rowIndex, Messages.exportTableDefinitionCell10, boldCellStyle));
ws.mergeCells(0, 0, 7, 0);
rowIndex++;
// System name
ws.addCell(new jxl.write.Label(0, rowIndex, Messages.exportTableDefinitionCell11, boldCellStyle));
ws.addCell(new jxl.write.Label(1, rowIndex, "", normalCellStyle));
// Date
ws.addCell(new jxl.write.Label(2, rowIndex, Messages.exportTableDefinitionCell4, boldCellStyle));
ws.addCell(new jxl.write.Label(3, rowIndex, dateString, normalCellStyle));
// Author
ws.addCell(new jxl.write.Label(5, rowIndex, Messages.exportTableDefinitionCell5, boldCellStyle));
ws.addCell(new jxl.write.Label(7, rowIndex, "", normalCellStyle));
ws.mergeCells(3, 1, 4, 1);
ws.mergeCells(5, 1, 6, 1);
rowIndex++;
String tableColumnText = "";
if (getProgressObject().isInstalledMetaTable()) {
SchemaComment tableComment = SchemaCommentHandler.find(getProgressObject().getSchemaCommentMap(), tableName, null);
if (tableComment != null) {
tableColumnText = tableComment.getDescription() == null ? "" : tableComment.getDescription();
}
}
// Table Name
ws.addCell(new jxl.write.Label(0, rowIndex, Messages.exportTableDefinitionCell6, boldCellStyle));
ws.addCell(new jxl.write.Label(1, rowIndex, tableColumnText, normalLeftAlignCellStyle));
ws.mergeCells(1, 2, 7, 2);
rowIndex++;
// Table ID
ws.addCell(new jxl.write.Label(0, rowIndex, Messages.exportTableDefinitionCell7, boldCellStyle));
ws.addCell(new jxl.write.Label(1, rowIndex, tableName, normalLeftAlignCellStyle));
ws.mergeCells(1, 3, 7, 3);
rowIndex++;
// Column name
ws.addCell(new jxl.write.Label(0, rowIndex, Messages.exportTableDefinitionCell12, boldCellStyle));
// Column ID
ws.addCell(new jxl.write.Label(1, rowIndex, Messages.exportTableDefinitionCell13, boldCellStyle));
// Data type
ws.addCell(new jxl.write.Label(2, rowIndex, Messages.exportTableDefinitionCell14, boldCellStyle));
// Size
ws.addCell(new jxl.write.Label(3, rowIndex, Messages.exportTableDefinitionCell15, boldCellStyle));
// Null
ws.addCell(new jxl.write.Label(4, rowIndex, Messages.exportTableDefinitionCell16, boldCellStyle));
// PK
ws.addCell(new jxl.write.Label(5, rowIndex, Messages.exportTableDefinitionCell17, boldCellStyle));
// FK
ws.addCell(new jxl.write.Label(6, rowIndex, Messages.exportTableDefinitionCell18, boldCellStyle));
// Memo
ws.addCell(new jxl.write.Label(7, rowIndex, Messages.exportTableDefinitionCell19, boldCellStyle));
rowIndex++;
// column info
for (DBAttribute columnAtt : schemaInfo.getAttributes()) {
String attrName = columnAtt.getName();
String columnText = "";
if (getProgressObject().isInstalledMetaTable()) {
SchemaComment columnComment = SchemaCommentHandler.find(getProgressObject().getSchemaCommentMap(), tableName, attrName);
if (columnComment != null) {
columnText = columnComment.getDescription() == null ? "" : columnComment.getDescription();
}
}
ws.addCell(new jxl.write.Label(0, rowIndex, columnText, normalLeftAlignCellStyle));
ws.addCell(new jxl.write.Label(1, rowIndex, attrName, normalLeftAlignCellStyle));
String showType = DataType.getShownType((columnAtt.getType()));
if (showType.indexOf("(") > -1 && showType.endsWith("")) {
showType = showType.substring(0, showType.indexOf("("));
}
ws.addCell(new jxl.write.Label(2, rowIndex, showType, normalLeftAlignCellStyle));
int size = DataType.getSize(columnAtt.getType());
int scale = DataType.getScale(columnAtt.getType());
if (size < 0 && scale < 0) {
ws.addCell(new jxl.write.Label(3, rowIndex, "", normalRightAlignCellStyle));
} else if (scale < 0) {
ws.addCell(new jxl.write.Number(3, rowIndex, size, normalRightAlignCellStyle));
} else {
ws.addCell(new jxl.write.Label(3, rowIndex, Integer.toString(size) + "," + Integer.toString(scale), normalRightAlignCellStyle));
}
//get nullable
boolean isNULL = true;
if (!columnAtt.isClassAttribute()) {
if (columnAtt.getInherit().equals(tableName)) {
Constraint pk = schemaInfo.getPK(supers);
if (null != pk && pk.getAttributes().contains(attrName)) {
isNULL = false;
}
} else {
List<Constraint> pkList = schemaInfo.getInheritPK(supers);
for (Constraint inheritPK : pkList) {
if (inheritPK.getAttributes().contains(attrName)) {
isNULL = false;
}
}
}
}
if (columnAtt.isNotNull()) {
isNULL = false;
}
ws.addCell(new jxl.write.Label(4, rowIndex, isNULL ? "Y" : "", normalCellStyle));
//get pk
boolean isPk = false;
if (!columnAtt.isClassAttribute()) {
if (columnAtt.getInherit().equals(tableName)) {
Constraint pk = schemaInfo.getPK(supers);
if (null != pk && pk.getAttributes().contains(attrName)) {
isPk = true;
}
} else {
List<Constraint> pkList = schemaInfo.getInheritPK(supers);
for (Constraint inheritPK : pkList) {
if (inheritPK.getAttributes().contains(attrName)) {
isPk = true;
}
}
}
}
ws.addCell(new jxl.write.Label(5, rowIndex, isPk ? "Y" : "", normalCellStyle));
//get fk
boolean isFk = false;
for (Constraint fk : schemaInfo.getFKConstraints()) {
for (String columns : fk.getAttributes()) {
if (columns.equals(attrName)) {
isFk = true;
break;
}
}
}
ws.addCell(new jxl.write.Label(6, rowIndex, isFk ? "Y" : "", normalCellStyle));
ws.addCell(new jxl.write.Label(7, rowIndex, "", normalCellStyle));
rowIndex++;
}
// blank
for (int i = 0; i < 8; i++) {
ws.addCell(new jxl.write.Label(i, rowIndex, "", normalCellStyle));
}
rowIndex++;
// index
ws.addCell(new jxl.write.Label(0, rowIndex, Messages.exportTableDefinitionCell20, boldCellStyle));
ws.mergeCells(0, rowIndex, 7, rowIndex);
rowIndex++;
// NO
ws.addCell(new jxl.write.Label(0, rowIndex, Messages.exportTableDefinitionCell21, boldCellStyle));
// Index name
ws.addCell(new jxl.write.Label(1, rowIndex, Messages.exportTableDefinitionCell22, boldCellStyle));
// Column ID
ws.addCell(new jxl.write.Label(3, rowIndex, Messages.exportTableDefinitionCell13, boldCellStyle));
// Order
ws.addCell(new jxl.write.Label(5, rowIndex, Messages.exportTableDefinitionCell23, boldCellStyle));
// Memo
ws.addCell(new jxl.write.Label(6, rowIndex, Messages.exportTableDefinitionCell19, boldCellStyle));
ws.mergeCells(1, rowIndex, 2, rowIndex);
ws.mergeCells(3, rowIndex, 4, rowIndex);
ws.mergeCells(6, rowIndex, 7, rowIndex);
rowIndex++;
List<Constraint> constraints = getProgressObject().getIndexList(schemaInfo);
for (int i = 0; i < constraints.size(); i++) {
Constraint constraint = constraints.get(i);
int columnSize = constraint.getAttributes().size();
// mark current row index
int currentRowIndex = rowIndex;
for (int j = 0; j < columnSize; j++) {
String columnName = constraint.getAttributes().get(j);
ws.addCell(new jxl.write.Number(0, rowIndex, i + 1, normalCellStyle));
ws.addCell(new jxl.write.Label(1, rowIndex, constraint.getName(), normalLeftAlignCellStyle));
ws.addCell(new jxl.write.Label(3, rowIndex, columnName, normalLeftAlignCellStyle));
ws.addCell(new jxl.write.Number(5, rowIndex, j + 1, normalCellStyle));
ws.addCell(new jxl.write.Label(6, rowIndex, "", normalCellStyle));
if (columnSize == 1) {
ws.mergeCells(1, rowIndex, 2, rowIndex);
}
ws.mergeCells(3, rowIndex, 4, rowIndex);
ws.mergeCells(6, rowIndex, 7, rowIndex);
rowIndex++;
}
//if multiple colulmn merge NO/Index Name CELL by vertical logic
if (columnSize > 1) {
ws.mergeCells(0, currentRowIndex, 0, currentRowIndex + columnSize - 1);
ws.mergeCells(1, currentRowIndex, 2, currentRowIndex + columnSize - 1);
}
}
// blank
ws.addCell(new jxl.write.Label(0, rowIndex, "", normalCellStyle));
ws.addCell(new jxl.write.Label(1, rowIndex, "", normalCellStyle));
ws.addCell(new jxl.write.Label(3, rowIndex, "", normalCellStyle));
ws.addCell(new jxl.write.Label(5, rowIndex, "", normalCellStyle));
ws.addCell(new jxl.write.Label(6, rowIndex, "", normalCellStyle));
ws.mergeCells(1, rowIndex, 2, rowIndex);
ws.mergeCells(3, rowIndex, 4, rowIndex);
ws.mergeCells(6, rowIndex, 7, rowIndex);
rowIndex++;
// DDL
ws.addCell(new jxl.write.Label(0, rowIndex, Messages.exportTableDefinitionCell24, boldCellStyle));
ws.mergeCells(0, rowIndex, 7, rowIndex);
rowIndex++;
String ddl = getProgressObject().getDDL(schemaInfo);
ws.addCell(new jxl.write.Label(0, rowIndex, ddl, normalLeftAlignCellStyle));
ws.mergeCells(0, rowIndex, 7, rowIndex);
ws.setRowView(0, 500);
int lineNumbner = ddl.split(StringUtil.NEWLINE).length;
ws.setRowView(rowIndex, lineNumbner * 350);
// column width
ws.setColumnView(0, 18);
ws.setColumnView(1, 20);
ws.setColumnView(2, 13);
ws.setColumnView(3, 13);
ws.setColumnView(4, 11);
ws.setColumnView(5, 11);
ws.setColumnView(6, 11);
ws.setColumnView(7, 20);
monitor.worked(1);
}
}
Also used :
Constraint(com.cubrid.common.core.common.model.Constraint)
WritableSheet(jxl.write.WritableSheet)
Constraint(com.cubrid.common.core.common.model.Constraint)
DBAttribute(com.cubrid.common.core.common.model.DBAttribute)
SchemaComment(com.cubrid.common.core.schemacomment.model.SchemaComment)
SchemaInfo(com.cubrid.common.core.common.model.SchemaInfo)
Example 4 with WritableSheet
use of jxl.write.WritableSheet 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);
}
}
}
}
Also used :
WritableWorkbook(jxl.write.WritableWorkbook)
Table(org.eclipse.swt.widgets.Table)
TableItem(org.eclipse.swt.widgets.TableItem)
WritableSheet(jxl.write.WritableSheet)
WorkbookSettings(jxl.WorkbookSettings)
WritableCellFormat(jxl.write.WritableCellFormat)
File(java.io.File)
WriteException(jxl.write.WriteException)
Example 5 with WritableSheet
use of jxl.write.WritableSheet 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);
}
}
Also used :
CUBRIDPreparedStatementProxy(com.cubrid.jdbc.proxy.driver.CUBRIDPreparedStatementProxy)
ArrayList(java.util.ArrayList)
Label(jxl.write.Label)
Timestamp(java.sql.Timestamp)
CUBRIDResultSetMetaDataProxy(com.cubrid.jdbc.proxy.driver.CUBRIDResultSetMetaDataProxy)
ExportDataFailedOneTableEvent(com.cubrid.common.ui.cubrid.table.event.ExportDataFailedOneTableEvent)
Number(jxl.write.Number)
CUBRIDResultSetProxy(com.cubrid.jdbc.proxy.driver.CUBRIDResultSetProxy)
Connection(java.sql.Connection)
WritableSheet(jxl.write.WritableSheet)
WriteException(jxl.write.WriteException)
SQLException(java.sql.SQLException)
IOException(java.io.IOException)
RowsExceededException(jxl.write.biff.RowsExceededException)
WritableWorkbook(jxl.write.WritableWorkbook)
ExportDataSuccessEvent(com.cubrid.common.ui.cubrid.table.event.ExportDataSuccessEvent)
Aggregations
WritableSheet (jxl.write.WritableSheet)21
WritableCellFormat (jxl.write.WritableCellFormat)10
WritableWorkbook (jxl.write.WritableWorkbook)10
WriteException (jxl.write.WriteException)9
WorkbookSettings (jxl.WorkbookSettings)8
IOException (java.io.IOException)6
Constraint (com.cubrid.common.core.common.model.Constraint)5
Label (jxl.write.Label)5
SchemaComment (com.cubrid.common.core.schemacomment.model.SchemaComment)4
SQLException (java.sql.SQLException)4
Number (jxl.write.Number)4
File (java.io.File)3
InvocationTargetException (java.lang.reflect.InvocationTargetException)3
Timestamp (java.sql.Timestamp)3
ArrayList (java.util.ArrayList)3
WritableFont (jxl.write.WritableFont)3
TableItem (org.eclipse.swt.widgets.TableItem)3
DBAttribute (com.cubrid.common.core.common.model.DBAttribute)2
SchemaInfo (com.cubrid.common.core.common.model.SchemaInfo)2
ExportDataFailedOneTableEvent (com.cubrid.common.ui.cubrid.table.event.ExportDataFailedOneTableEvent)2
