Export file with styling
https://poi.apache.org/components/spreadsheet/quick-guide.html#FillsAndFrills
package com.sst.services.export.impl;
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.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.sst.core.constants.SstcoreConstants;
import com.sst.core.data.ExportUser;
import com.sst.services.b2bunit.SSTB2BCommerceUserService;
import com.sst.services.export.SSTExportToFileService;
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 DefaultSSTExportToFileService implements SSTExportToFileService {
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";
private static final int NUMBER_OF_COLUMNS = 13;
@Resource(name = "sessionService")
private SessionService sessionService;
@Resource(name="b2bCommerceUserService")
private SSTB2BCommerceUserService 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
{
B2BUnitData custOrg=sessionService.getAttribute(SstcoreConstants.SESSION_ADMIN_PORTAL_UNIT);
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;
//add in new rows to workbook
addRowsToWorksheet(userSheet, users.size());
//update table to fit in the new width
List<XSSFTable> tables = userSheet.getTables();
if(CollectionUtils.isNotEmpty(tables)){
XSSFTable table = tables.get(0);
CellReference startTable = new CellReference(START_OF_DATA_ROWS-1,0);
CellReference endTable = new CellReference(userSheet.getLastRowNum(), table.getEndColIndex());
AreaReference areaReference = new AreaReference(startTable, endTable, SpreadsheetVersion.EXCEL2007);
table.setArea(areaReference);
table.getCTTable().getAutoFilter().setRef( areaReference.toString() );
table.updateReferences();
}
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 void addRowsToWorksheet(XSSFSheet sheet, int numberOfRows){
XSSFRow templateRow = sheet.getRow(START_OF_DATA_ROWS);
int startInedex = START_OF_DATA_ROWS + 1;
int endIndex = START_OF_DATA_ROWS + numberOfRows;
for(int i = startInedex; i < endIndex; i++){
XSSFRow newRow = sheet.createRow(i);
for(int j = 0; j < NUMBER_OF_COLUMNS; j++){
newRow.createCell(j);
newRow.getCell(j).setCellStyle(templateRow.getCell(j).getCellStyle());
newRow.getCell(j).setCellType(templateRow.getCell(j).getCellType());
}
}
}
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);
}
}
Another approach
package com.sst.services.export.impl;
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.HashMap;
import java.util.List;
import java.util.Map;
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.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;
import com.sst.core.constants.SstcoreConstants;
import com.sst.core.data.ExportUser;
import com.sst.services.b2bunit.SSTB2BCommerceUserService;
import com.sst.services.export.SSTExportToFileService;
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 DefaultSSTExportToFileService implements SSTExportToFileService {
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")
private SSTB2BCommerceUserService 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
{
B2BUnitData custOrg=sessionService.getAttribute(SstcoreConstants.SESSION_ADMIN_PORTAL_UNIT);
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,wb);
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,wb);
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,XSSFWorkbook wb) {
// create cell styles which will have default styling
XSSFCellStyle style = wb.createCellStyle();
// update cell styles which has the ForegroundColor As per the Colour picker in
// the template. it's Hexadecimal color #ebf0f1 i.e
// RGB red (235)
// RGB green(240)
// RGB blue(241)
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(235, 240, 241), new DefaultIndexedColorMap()));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// Style the cell with borders all around.
style.setBorderBottom(BorderStyle.THICK);
style.setBottomBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderLeft(BorderStyle.THICK);
style.setLeftBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderRight(BorderStyle.THICK);
style.setRightBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderTop(BorderStyle.THICK);
style.setTopBorderColor(IndexedColors.WHITE.getIndex());
if (CollectionUtils.isNotEmpty(users)) {
int currentIndex = 0;
for (final ExportUser user : users) {
int startOfDataRows = START_OF_DATA_ROWS;
startOfDataRows += currentIndex;
// Create a row and put some cells in it. Rows will start populating from the count of startOfDataRows
XSSFRow row = userSheet.createRow(startOfDataRows);
if(user != null && row != null) {
// Create each and every cell and put a value in it according to this row population.
row.createCell(0);
row.getCell(0).setCellValue(populateField(user.getUid()));
row.getCell(0).setCellStyle(style);
row.createCell(1);
row.getCell(1).setCellValue(populateField(user.getFirstName()));
row.getCell(1).setCellStyle(style);
row.createCell(2);
row.getCell(2).setCellValue(populateField(user.getLastName()));
row.getCell(2).setCellStyle(style);
row.createCell(3);
row.getCell(3).setCellValue(populateField(user.getJobTitle()));
row.getCell(3).setCellStyle(style);
row.createCell(4);
row.getCell(4).setCellValue(populateField(user.getEmail()));
row.getCell(4).setCellStyle(style);
row.createCell(5);
row.getCell(5).setCellValue(populateField(StringUtils.isNotBlank(user.getPhone()) ? formatPhone(user.getPhone()) : ""));
row.getCell(5).setCellStyle(style);
String disabled=Boolean.TRUE.equals(user.isActive()) ? "N" : "Y";
row.createCell(6);
row.getCell(6).setCellValue(populateField(user.getBranchCodes()));
row.getCell(6).setCellStyle(style);
row.createCell(7);
row.getCell(7).setCellValue(populateField(user.getAccounts()));
row.getCell(7).setCellStyle(style);
row.createCell(8);
row.getCell(8).setCellValue(disabled);
row.getCell(8).setCellStyle(style);
final String date =null!=user.getLastLogin() ? formatDateToString(user.getLastLogin(), FORMAT1_DATE_TO_STRING,US_PACIFIC_TIME_ZONE) : "";
row.createCell(9);
row.getCell(9).setCellValue(date);
row.getCell(9).setCellStyle(style);
row.createCell(10);
row.getCell(10).setCellValue(populateField(user.getCustOrgUid()));
row.getCell(10).setCellStyle(style);
row.createCell(11);
row.getCell(11).setCellValue(populateField(user.getCustOrgName()));
row.getCell(11).setCellStyle(style);
row.createCell(12);
row.getCell(12).setCellValue(populateField(user.getRole()));
row.getCell(12).setCellStyle(style);
}
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