using CatalogUnawareMediaModel for the export xlsx files.
DefaultExportToFileService.java
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;
import javax.annotation.Resource;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import de.hybris.platform.b2b.model.B2BCustomerModel;
import de.hybris.platform.b2bcommercefacades.company.data.B2BUnitData;
import de.hybris.platform.catalog.model.CatalogUnawareMediaModel;
import de.hybris.platform.servicelayer.config.ConfigurationService;
import de.hybris.platform.servicelayer.media.MediaService;
import de.hybris.platform.servicelayer.model.ModelService;
import de.hybris.platform.servicelayer.session.SessionService;
public class DefaultExportToFileService implements ExportToFileService {
private static final String YYYY_MMDD_HHMMSS_XLSX = "yyyyMMddHHmmss'.xlsx'";
private static final String ALL_USER_EXPORT = "allUserExport";
private static final Logger LOG = Logger.getLogger(DefaultSSTExportToFileService.class);
private static final String USER_SHEET_NAME = "Users";
private static final int START_OF_DATA_ROWS = 5;
public static final String FORMAT_DATE_TO_STRING = "dd/MM/yy hh:mm:ss ";//Mon Aug 03 10:46:02 PDT 2020
public static final String FORMAT1_DATE_TO_STRING = "MM-dd-yyyy hh:mm:ss ";//Mon Aug 03 10:46:02 PDT 2020
public static final String US_PACIFIC_TIME_ZONE = "America/Los_Angeles";
private static final String EXPORT_ALL_USERS_SHEET_TEMP_LOCATION = "export.all.users.sheet.temp.location";
private static final String OPEN_XML_SHEET = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
@Resource(name = "sessionService")
private SessionService sessionService;
@Resource(name="b2bCommerceUserService")
@Resource(name = "configurationService")
private ConfigurationService configurationService;
@Resource(name = "modelService")
private ModelService modelService;
@Resource(name = "mediaService")
MediaService mediaService;
/*
Service exports:
1. Header
2. User
3. First Name
4. Last Name
5. Title
6. Email
7. Phone
8. Branch
9. Disabled
10. Last Login
*/
@Override
public ByteArrayOutputStream exportUsersToExcel() throws IOException, InvalidFormatException
{
B2BCustomerModel currentUser= (B2BCustomerModel) sessionService.getAttribute("user");
List<ExportUser> users =b2bCommerceUserService.findExportUsersByCustorg(custOrg.getUid());
try (final OPCPackage opcPackage = readTemplate()) {
final XSSFWorkbook wb = new XSSFWorkbook(opcPackage);
final XSSFSheet userSheet = wb.getSheet(USER_SHEET_NAME);
exportHeader(currentUser, userSheet);
exportUsers(users, userSheet,currentUser);
try (final ByteArrayOutputStream outStream = new ByteArrayOutputStream()) {
wb.write(outStream);
return outStream;
}
}
}
public ByteArrayOutputStream createAndPrepareExcelSheet() throws IOException, InvalidFormatException
{
B2BCustomerModel currentUser= (B2BCustomerModel) sessionService.getAttribute("user");
List<ExportUser> users =b2bCommerceUserService.findExportAllUsersByCustorg();
try (final OPCPackage opcPackage = readTemplate()) {
final XSSFWorkbook wb = new XSSFWorkbook(opcPackage);
final XSSFSheet userSheet = wb.getSheet(USER_SHEET_NAME);
exportHeader(currentUser, userSheet);
exportUsers(users, userSheet,currentUser);
try (final ByteArrayOutputStream outStream = new ByteArrayOutputStream()) {
wb.write(outStream);
return outStream;
}
}
}
/**
* ExportAllUsersToExcel Sheet
*/
@Override
public String exportAllUsersToExcel() {
String downloadURL = "";
File attachedFile = null;
try {
attachedFile = generateXLSSheetFile();
if (attachedFile != null) {
final CatalogUnawareMediaModel xlsMedia = modelService.create(CatalogUnawareMediaModel.class);
xlsMedia.setCode(attachedFile.getName());
modelService.save(xlsMedia);
mediaService.setStreamForMedia(xlsMedia, FileUtils.openInputStream(attachedFile),attachedFile.getName(), OPEN_XML_SHEET);
modelService.refresh(xlsMedia);
LOG.debug("CatalogUnawareMedia Code::::" + xlsMedia.getCode());
if (StringUtils.isNotEmpty(xlsMedia.getDownloadURL())) {
downloadURL = xlsMedia.getDownloadURL();
}
}
}
catch (IOException | InvalidFormatException exportException) {
LOG.error("Could not export all users", exportException);
}
finally {
if (attachedFile != null) {
LOG.debug("Going to delete the xls sheet file " + attachedFile.getPath());
FileUtils.deleteQuietly(attachedFile);
}
}
LOG.debug("Export all users sheet downloadURL" + downloadURL);
return downloadURL;
}
/**
* GenerateXLSSheetFile
* @return
* @throws IOException
* @throws InvalidFormatException
*/
private File generateXLSSheetFile() throws IOException,InvalidFormatException {
LOG.debug("***GenerateXLSSheetFile Start***");
String fileName = ALL_USER_EXPORT + new SimpleDateFormat(YYYY_MMDD_HHMMSS_XLSX).format(new Date());
String directory = configurationService.getConfiguration().getString(EXPORT_ALL_USERS_SHEET_TEMP_LOCATION);
File directoryFile = new File(directory);
if (!directoryFile.exists()) {
// if the directory doesn't exist, create it
if (directoryFile.mkdirs()) {
LOG.debug("Temporary directory " + directory + "created");
}
}
File xlsSheetFile = new File(directory + File.separator + fileName);
FileOutputStream fos = null;
ByteArrayOutputStream baos = null;
try {
fos = new FileOutputStream(xlsSheetFile);
baos = createAndPrepareExcelSheet();
baos.writeTo(fos);
}finally {
baos.close();
fos.close();
}
LOG.debug("***GenerateXLSSheetFile End***");
return xlsSheetFile;
}
private void exportUsers(List<ExportUser> users,final XSSFSheet userSheet,B2BCustomerModel currentUser) {
if (CollectionUtils.isNotEmpty(users)) {
int currentIndex = 0;
for (final ExportUser user : users) {
int startOfDataRows = START_OF_DATA_ROWS;
startOfDataRows += currentIndex;
XSSFRow row = userSheet.getRow(startOfDataRows);
if(user != null && row != null) {
row.getCell(0).setCellValue(populateField(user.getUid()));
row.getCell(1).setCellValue(populateField(user.getFirstName()));
row.getCell(2).setCellValue(populateField(user.getLastName()));
row.getCell(3).setCellValue(populateField(user.getJobTitle()));
row.getCell(4).setCellValue(populateField(user.getEmail()));
row.getCell(5).setCellValue(populateField(StringUtils.isNotBlank(user.getPhone()) ? formatPhone(user.getPhone()) : ""));
String disabled=Boolean.TRUE.equals(user.isActive()) ? "N" : "Y";
row.getCell(6).setCellValue(populateField(user.getBranchCodes()));
row.getCell(7).setCellValue(populateField(user.getAccounts()));
row.getCell(8).setCellValue(disabled);
final String date =null!=user.getLastLogin() ? formatDateToString(user.getLastLogin(), FORMAT1_DATE_TO_STRING,US_PACIFIC_TIME_ZONE) : "";
row.getCell(9).setCellValue(date);
row.getCell(10).setCellValue(populateField(user.getCustOrgUid()));
row.getCell(11).setCellValue(populateField(user.getCustOrgName()));
row.getCell(12).setCellValue(populateField(user.getRole()));
}
currentIndex++;
}
}
}
private String formatPhone(String phone)
{
phone=phone.replaceFirst("(\\d{3})(\\d{3})(\\d+)", "$1-$2-$3");
return phone;
}
private String populateField(String property) {
return StringUtils.isNotBlank(property) ? property : StringUtils.EMPTY;
}
private void exportHeader(final B2BCustomerModel user, final XSSFSheet userSheet) {
// Date and username
final String date = formatDateToString(new Date(), FORMAT_DATE_TO_STRING, US_PACIFIC_TIME_ZONE);
final String username = user != null ? user.getUid() : StringUtils.EMPTY;
userSheet.getRow(2).getCell(0).setCellValue(date + " " + username);
}
public OPCPackage readTemplate() throws IOException, InvalidFormatException {
return OPCPackage.open(DefaultSSTExportToFileService.class
.getResourceAsStream("/sstb2bservices/export/SST_Export_User_Template.xlsx"));
}
public static String formatDateToString(Date date, String format, String timeZone) {
// null check
if (date == null) {
return null;
}
// create SimpleDateFormat object with input format
SimpleDateFormat sdf = new SimpleDateFormat(format);
// default system timezone if passed null or empty
if (timeZone == null || "".equalsIgnoreCase(timeZone.trim())) {
timeZone = Calendar.getInstance().getTimeZone().getID();
}
// set timezone to SimpleDateFormat
sdf.setTimeZone(TimeZone.getTimeZone(timeZone));
return sdf.format(date);
}
}
Comments
Post a Comment