/*
* OpenClinica is distributed under the
* GNU Lesser General Public License (GNU LGPL).
* For details see: http://www.openclinica.org/license
* copyright 2003-2007 Akaza Research
*/
package org.akaza.openclinica.control.managestudy;
import org.akaza.openclinica.bean.admin.AuditBean;
import org.akaza.openclinica.bean.admin.DeletedEventCRFBean;
import org.akaza.openclinica.bean.core.Status;
import org.akaza.openclinica.bean.core.Utils;
import org.akaza.openclinica.bean.managestudy.StudyBean;
import org.akaza.openclinica.bean.managestudy.StudyEventBean;
import org.akaza.openclinica.bean.managestudy.StudyEventDefinitionBean;
import org.akaza.openclinica.bean.managestudy.StudySubjectBean;
import org.akaza.openclinica.bean.submit.CRFVersionBean;
import org.akaza.openclinica.bean.submit.EventCRFBean;
import org.akaza.openclinica.bean.submit.ItemDataBean;
import org.akaza.openclinica.bean.submit.SubjectBean;
import org.akaza.openclinica.control.core.SecureController;
import org.akaza.openclinica.control.form.FormProcessor;
import org.akaza.openclinica.control.submit.SubmitDataServlet;
import org.akaza.openclinica.dao.admin.AuditDAO;
import org.akaza.openclinica.dao.admin.CRFDAO;
import org.akaza.openclinica.dao.managestudy.EventDefinitionCRFDAO;
import org.akaza.openclinica.dao.managestudy.StudyDAO;
import org.akaza.openclinica.dao.managestudy.StudyEventDAO;
import org.akaza.openclinica.dao.managestudy.StudyEventDefinitionDAO;
import org.akaza.openclinica.dao.managestudy.StudySubjectDAO;
import org.akaza.openclinica.dao.submit.CRFVersionDAO;
import org.akaza.openclinica.dao.submit.EventCRFDAO;
import org.akaza.openclinica.dao.submit.ItemDataDAO;
import org.akaza.openclinica.dao.submit.SubjectDAO;
import org.akaza.openclinica.i18n.util.ResourceBundleProvider;
import org.akaza.openclinica.view.Page;
import org.akaza.openclinica.web.InsufficientPermissionException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.Label;
/**
* @author jsampson
* @author akung
*/
@SuppressWarnings("serial")
public class ExportExcelStudySubjectAuditLogServlet extends SecureController {
/**
* Checks whether the user has the right permission to proceed function
*/
@Override
public void mayProceed() throws InsufficientPermissionException {
// if (SubmitDataServlet.mayViewData(ub, currentRole)) {
// return;
// }
// if (ub.isSysAdmin()) {
// return;
// }
// Role r = currentRole.getRole();
// if (r.equals(Role.STUDYDIRECTOR) || r.equals(Role.COORDINATOR)) {
// return;
// }
// addPageMessage(respage.getString("no_have_correct_privilege_current_study") + respage.getString("change_study_contact_sysadmin"));
// throw new InsufficientPermissionException(Page.MENU_SERVLET, resexception.getString("may_not_submit_data"), "1");
if (ub.isSysAdmin()) {
return;
}
if (SubmitDataServlet.mayViewData(ub, currentRole)) {
return;
}
addPageMessage(respage.getString("no_have_correct_privilege_current_study") + " " + respage.getString("change_study_contact_sysadmin"));
throw new InsufficientPermissionException(Page.LIST_STUDY_SUBJECTS, resexception.getString("not_study_director"), "1");
}
@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);
studySubject.setLabel(decodeForHtml(studySubject.getLabel()));
studySubject.setSecondaryLabel(decodeForHtml(studySubject.getSecondaryLabel()));
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());
// Text values will be shown on the page for the corresponding
// 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());
}
if (auditBean.getAuditEventTypeId() == 4) {
auditBean.setOldValue(decodeForHtml(auditBean.getOldValue()));
auditBean.setNewValue(decodeForHtml(auditBean.getNewValue()));
}
}
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.getCrfVersion(),
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
}
}
@Override
protected String getAdminServlet() {
if (ub.isSysAdmin()) {
return SecureController.ADMIN_SERVLET_CODE;
} else {
return "";
}
}
private String dateFormat(Date date) {
if(date == null) {
return "";
}
else {
SimpleDateFormat dteFormat = new SimpleDateFormat(ResourceBundleProvider.getFormatBundle().getString("date_format_string"));
return dteFormat.format(date);
}
}
private String dateTimeFormat(Date date) {
if(date == null) {
return "";
}
else {
SimpleDateFormat dtetmeFormat = new SimpleDateFormat(ResourceBundleProvider.getFormatBundle().getString("date_time_format_string"));
return dtetmeFormat.format(date);
}
}
private void autoSizeColumns(WritableSheet sheet) {
for(int x=0;x<6;x++)
{
CellView cell=sheet.getColumnView(x);
cell.setAutosize(true);
sheet.setColumnView(x, cell);
}
}
}