Perfectly working Excel download program inLiferay
public void adminMethodExcel(ResourceRequest req,ResourceResponse res) throws SystemException, IOException, PortalException
{
XSSFWorkbook workbook = new XSSFWorkbook(); //Blank workbook Excel 2007 OOXML (.xlsx) file format
XSSFSheet sheet = workbook.createSheet("Employee Data"); //Create a blank sheet Excel 2007 OOXML (.xlsx) file format
int numOfUser=UserLocalServiceUtil.getUsersCount(); //getting number of rows or number of users
int rowNum=0; //Number of rows
int cellNum=0; //Number of columns
Row row=sheet.createRow(rowNum); //Zeroth row formation(heading)
Cell cell = row.createCell(cellNum++); //First column creation
cell.setCellValue("uuid_");
cell = row.createCell(cellNum++);
cell.setCellValue("userId");
cell = row.createCell(cellNum++);
cell.setCellValue("companyId");
cell = row.createCell(cellNum++);
cell.setCellValue("createDate");
cell = row.createCell(cellNum++);
cell.setCellValue("modifiedDate");
cell = row.createCell(cellNum++);
cell.setCellValue("defaultUser");
cell = row.createCell(cellNum++);
cell.setCellValue("contactId");
cell = row.createCell(cellNum++);
cell.setCellValue("password_");
cell = row.createCell(cellNum++);
cell.setCellValue("passwordEncrypted");
cell = row.createCell(cellNum++);
cell.setCellValue("passwordReset");
cell = row.createCell(cellNum++);
cell.setCellValue("passwordModifiedDate");
cell = row.createCell(cellNum++);
cell.setCellValue("digest");
cell = row.createCell(cellNum++);
cell.setCellValue("reminderQueryQuestion");
cell = row.createCell(cellNum++);
cell.setCellValue("reminderQueryAnswer");
cell = row.createCell(cellNum++);
cell.setCellValue("graceLoginCount");
cell = row.createCell(cellNum++);
cell.setCellValue("screenName");
cell = row.createCell(cellNum++);
cell.setCellValue("emailAddress");
cell = row.createCell(cellNum++);
cell.setCellValue("facebookId");
cell = row.createCell(cellNum++);
cell.setCellValue("openId");
cell = row.createCell(cellNum++);
cell.setCellValue("portraitId");
cell = row.createCell(cellNum++);
cell.setCellValue("languageId");
cell = row.createCell(cellNum++);
cell.setCellValue("timeZoneId");
cell = row.createCell(cellNum++);
cell.setCellValue("greeting");
cell = row.createCell(cellNum++);
cell.setCellValue("comments");
cell = row.createCell(cellNum++);
cell.setCellValue("firstName");
cell = row.createCell(cellNum++);
cell.setCellValue("middleName");
cell = row.createCell(cellNum++);
cell.setCellValue("lastName");
cell = row.createCell(cellNum++);
cell.setCellValue("jobTitle");
cell = row.createCell(cellNum++);
cell.setCellValue("loginDate");
cell = row.createCell(cellNum++);
cell.setCellValue("loginIP");
cell = row.createCell(cellNum++);
cell.setCellValue("lastLoginDate");
cell = row.createCell(cellNum++);
cell.setCellValue("lastLoginIP");
cell = row.createCell(cellNum++);
cell.setCellValue("lastFailedLoginDate");
cell = row.createCell(cellNum++);
cell.setCellValue("failedLoginAttempts");
cell = row.createCell(cellNum++);
cell.setCellValue("lockout");
cell = row.createCell(cellNum++);
cell.setCellValue("lockoutDate");
cell = row.createCell(cellNum++);
cell.setCellValue("agreedToTermsOfUse");
cell = row.createCell(cellNum++);
cell.setCellValue("emailAddressVerified");
cell = row.createCell(cellNum++);
cell.setCellValue("status");
List<User> totalUsers=UserLocalServiceUtil.getUsers(0,numOfUser); //fetch whole table in our object
Iterator<User> it=totalUsers.iterator();
User eachRow=null;
DateFormat df=null;
String str=null;
java.util.Date jDate=null;
while(it.hasNext())
{
row = sheet.createRow(++rowNum); //if we are trying to creating a new row which already created, e.g. row=0, and then we again try to creating row on row=0 then we will get an error org.apache.xmlbeans.impl.values.xmlvaluedisconnectedexception
cellNum = 0;
eachRow=(User) it.next();
df=new SimpleDateFormat("yyyy-MM-dd");
str=null;
jDate=null;
cell = row.createCell(cellNum++); // we have to create column then adding the value another problem that I faced
cell.setCellValue((eachRow.getUuid()).toString());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getUserId()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getCompanyId()+"");
cell = row.createCell(cellNum++);
jDate=eachRow.getCreateDate();
if(jDate!=null)
{
str=df.format(jDate);
str=df.format(jDate);
}
else
str="NA";
cell.setCellValue(str);
cell = row.createCell(cellNum++);
jDate=eachRow.getModifiedDate();
if(jDate!=null)
{
str=df.format(jDate);
str=df.format(jDate);
}
else
str="NA";
cell.setCellValue(str);
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getDefaultUser()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getContactId()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getPassword());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getPasswordEncrypted()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getPasswordReset()+"");
cell = row.createCell(cellNum++);
jDate=eachRow.getPasswordModifiedDate();
if(jDate!=null)
{
str=df.format(jDate);
str=df.format(jDate);
}
else
str="NA";
cell.setCellValue(str);
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getDigest());
cell = row.createCell(cellNum++);
Iterator itr=(eachRow.getReminderQueryQuestions()).iterator();
Object obj=itr.next();
str=(String)obj;
cell.setCellValue(str);
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getReminderQueryAnswer());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getGraceLoginCount()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getScreenName());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getEmailAddress());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getFacebookId()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getOpenId());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getPortraitId()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getLanguageId());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getTimeZoneId());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getGreeting());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getComments());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getFirstName());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getMiddleName());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getLastName());
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getJobTitle());
cell = row.createCell(cellNum++);
System.out.println("Middle val of row: "+rowNum);
jDate=eachRow.getLoginDate();
if(jDate!=null)
{
str=df.format(jDate);
str=df.format(jDate);
}
else
str="NA";
cell.setCellValue(str);
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getLoginIP());
cell = row.createCell(cellNum++);
jDate=eachRow.getLastLoginDate();
if(jDate!=null)
{
str=df.format(jDate);
str=df.format(jDate);
}
else
str="NA";
cell.setCellValue(str);
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getLastLoginIP());
cell = row.createCell(cellNum++);
jDate=eachRow.getLastFailedLoginDate();
if(jDate!=null)
{
str=df.format(jDate); str=df.format(jDate);
}
else
str="NA";
cell.setCellValue(str);
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getFailedLoginAttempts()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getLockout()+"");
cell = row.createCell(cellNum++);
jDate=eachRow.getLockoutDate();
if(jDate!=null)
{
str=df.format(jDate);
str=df.format(jDate);
}
else
str="NA";
cell.setCellValue(str);
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getAgreedToTermsOfUse()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getEmailAddressVerified()+"");
cell = row.createCell(cellNum++);
cell.setCellValue(eachRow.getStatus()+"");
//cell = row.createCell(cellNum++);
System.out.println("Last val of row: "+rowNum);
}//end of wile
* Blck fonts are comments
* Red fonts are error that I got while executing the code
No comments:
Post a Comment