Skip to content

Instantly share code, notes, and snippets.

@betray32

betray32/Formatted-ReadExcel-IntoNewExcel.java

Last active November 22, 2021 14:40
Show Gist options
  • Save betray32/76c59092301dc1df30f1a07607a67fd8 to your computer and use it in GitHub Desktop.
Save betray32/76c59092301dc1df30f1a07607a67fd8 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Formatted-ReadExcel-IntoNewExcel.java
package quilmes.com.ar.pdo.matcherpdo;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;
import quilmes.com.ar.pdo.matcherpdo.rest.StructureForUserService;
import quilmes.com.ar.pdo.matcherpdo.structure.user.Subregion;
@Service
@Slf4j
public class Automatic implements CommandLineRunner {
public static final String RESOURCE_FILE = "classpath:Usuarios.xlsx";
public static final String TARGET_FILE = "src/main/resources/processedFile_users.xlsx";
public static final int COLUMN_WIDTH = 15;
public static final int COLUMN_HEIGHT_IN_SHORT = 400;
public static final String PROCESSED_FINISHED = "Processed Finished";
public static final String RELATED_SUBREGIONS_FOR_THIS_EMAIL = "Related Subregions for this email = {}";
public static final String PROCESSED_CLIENTS = "Processed Clients";
public static final String ERROR_WHEN_PROCESSING_FILE_DETAIL = "Error when processing file, Detail > ";
public static final String ERROR_WHEN_CLOSING_EXCEL_FILE = "Error when closing Excel File";
@Autowired
private ConfigurableApplicationContext context;
@Autowired
StructureForUserService structureForUserService;
@Override
public void run(String... args) {
Workbook initialWorkbook = null;
try (Workbook resultWorkbook = new XSSFWorkbook()) {
log.info("Reading File {}", RESOURCE_FILE);
try (FileInputStream file = new FileInputStream(ResourceUtils.getFile(RESOURCE_FILE))) {
initialWorkbook = new XSSFWorkbook(file);
}
Sheet sheet = initialWorkbook.getSheetAt(0);
/** Creating File **/
Sheet resultSheet = resultWorkbook.createSheet(PROCESSED_CLIENTS);
resultSheet.setDefaultColumnWidth(COLUMN_WIDTH);
resultSheet.setDefaultRowHeight((short) COLUMN_HEIGHT_IN_SHORT);
CellStyle cellStyle = resultWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.LEFT);
int rowCount = 0;
for (Row row : sheet) {
Row resultHeaderRow = resultSheet.createRow(rowCount);
int cellCount = 0;
for (Cell cell : row) {
if (cell.getRowIndex() == 0) {
// Header Config
insertHeader(resultWorkbook, row, resultHeaderRow, cellCount);
} else {
String userEmail = cell.getStringCellValue().trim();
List<Subregion> userInfo = getDataFromPdeByEmail(userEmail);
int relatedLocations = userInfo.size();
log.info(RELATED_SUBREGIONS_FOR_THIS_EMAIL, relatedLocations);
if (relatedLocations == 0) {
createEmptyRow(resultSheet, cellStyle, rowCount, userEmail);
continue;
}
/*
* We should create as many rows as there are records the user has
*/
if (relatedLocations > 1) {
for (int registry = 0; registry < userInfo.size(); registry++) {
userInfoIntoRow(resultSheet, cellStyle, rowCount, userEmail, userInfo, registry);
rowCount++;
}
// Normalize the row count
rowCount -= 1;
} else {
userInfoIntoRow(resultSheet, cellStyle, rowCount, userEmail, userInfo, 0);
}
}
cellCount++;
}
rowCount++;
}
resultSheet.autoSizeColumn(0);
resultSheet.autoSizeColumn(3);
resultSheet.autoSizeColumn(6);
try (FileOutputStream outputStream = new FileOutputStream(TARGET_FILE)) {
resultWorkbook.write(outputStream);
}
log.info(PROCESSED_FINISHED);
System.exit(SpringApplication.exit(context));
} catch (Exception e) {
log.error(ERROR_WHEN_PROCESSING_FILE_DETAIL, e);
} finally {
try {
if (initialWorkbook != null) initialWorkbook.close();
} catch (IOException e) {
log.error(ERROR_WHEN_CLOSING_EXCEL_FILE);
}
}
}
private List<Subregion> getDataFromPdeByEmail(String email) {
return structureForUserService.getStructureByEmail(email);
}
private void insertHeader(Workbook resultWorkbook, Row row, Row resultHeaderRow, int cellCount) {
CellStyle headerStyle = resultWorkbook.createCellStyle();
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headerStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headerStyle.setBorderTop(BorderStyle.THIN);
headerStyle.setBorderBottom(BorderStyle.THIN);
headerStyle.setBorderLeft(BorderStyle.THIN);
headerStyle.setBorderRight(BorderStyle.THIN);
XSSFFont font = ((XSSFWorkbook) resultWorkbook).createFont();
font.setFontName("Arial");
font.setBold(true);
font.setColor(IndexedColors.WHITE.getIndex());
headerStyle.setFont(font);
Cell headerCell = resultHeaderRow.createCell(cellCount);
headerCell.setCellValue(row.getCell(cellCount).getStringCellValue());
headerCell.setCellStyle(headerStyle);
}
private void createEmptyRow(Sheet resultSheet, CellStyle cellStyle, int rowCount, String userEmail) {
Row resultRow = resultSheet.createRow(rowCount);
resultRow.setRowStyle(cellStyle);
resultRow.createCell(0).setCellValue(userEmail);
resultRow.createCell(1).setCellValue("N/A");
resultRow.createCell(2).setCellValue("N/A");
resultRow.createCell(3).setCellValue("N/A");
resultRow.createCell(4).setCellValue("N/A");
resultRow.createCell(5).setCellValue("N/A");
resultRow.createCell(6).setCellValue("N/A");
resultRow.createCell(7).setCellValue("N/A");
}
private void userInfoIntoRow(Sheet resultSheet, CellStyle cellStyle, int rowCount, String userEmail, List<Subregion> userInfo, int registry) {
Row resultRow = resultSheet.createRow(rowCount);
resultRow.setRowStyle(cellStyle);
resultRow.createCell(0).setCellValue(userEmail);
resultRow.createCell(1).setCellValue(userInfo.get(registry).getId());
resultRow.createCell(2).setCellValue(userInfo.get(registry).getCode());
resultRow.createCell(3).setCellValue(userInfo.get(registry).getName());
resultRow.createCell(4).setCellValue(userInfo.get(registry).getRoles().get(0).getStructure().getId());
resultRow.createCell(5).setCellValue(userInfo.get(registry).getRoles().get(0).getStructure().getCode());
resultRow.createCell(6).setCellValue(userInfo.get(registry).getRoles().get(0).getStructure().getName());
resultRow.createCell(7).setCellValue(userInfo.get(registry).getRoles().get(0).getName());
}
}
Raw
ReadExcel-IntoNewExcel.java
package quilmes.com.ar.pdo.MatcherPdOPdE;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;
import quilmes.com.ar.pdo.MatcherPdOPdE.rest.StructureForUserService;
import quilmes.com.ar.pdo.MatcherPdOPdE.structure.user.Subregion;
@Service
@Slf4j
public class Automatic implements CommandLineRunner {
public static final String RESOURCE_FILE = "classpath:Usuarios.xlsx";
public static final String TARGET_FILE = "src/main/resources/processedFile_users.xlsx";
public static final int COLUMN_WIDTH = 15;
public static final int COLUMN_HEIGHT_IN_SHORT = 400;
public static final String PROCESSED_FINISHED = "Processed Finished";
public static final String RELATED_SUBREGIONS_FOR_THIS_EMAIL = "Related Subregions for this email = {}";
public static final String PROCESSED_CLIENTS = "Processed Clients";
@Autowired
private ConfigurableApplicationContext context;
@Autowired
StructureForUserService structureForUserService;
@Override
public void run(String... args) throws Exception {
log.info("Reading File {}", RESOURCE_FILE);
FileInputStream file = new FileInputStream(ResourceUtils.getFile(RESOURCE_FILE));
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
/** Creating File **/
Workbook resultWorkbook = new XSSFWorkbook();
Sheet resultSheet = resultWorkbook.createSheet(PROCESSED_CLIENTS);
resultSheet.setDefaultColumnWidth(COLUMN_WIDTH);
resultSheet.setDefaultRowHeight((short) COLUMN_HEIGHT_IN_SHORT);
CellStyle cellStyle = resultWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.LEFT);
int rowCount = 0;
for (Row row : sheet) {
Row resultHeaderRow = resultSheet.createRow(rowCount);
int cellCount = 0;
for (Cell cell : row) {
if (cell.getRowIndex() == 0) {
// Header Config
CellStyle headerStyle = generateHeaderStyle(resultWorkbook);
Cell headerCell = resultHeaderRow.createCell(cellCount);
headerCell.setCellValue(row.getCell(cellCount).getStringCellValue());
headerCell.setCellStyle(headerStyle);
} else {
String userEmail = cell.getStringCellValue().trim();
List<Subregion> userInfo = getDataFromManagerEmail(userEmail);
int relatedLocations = userInfo.size();
log.info(RELATED_SUBREGIONS_FOR_THIS_EMAIL, relatedLocations);
if (relatedLocations == 0) {
createEmptyRow(resultSheet, cellStyle, rowCount, userEmail);
continue;
}
if (relatedLocations > 1) {
/*
* We should create as many rows as there are records the user has
*/
for (int registry = 0; registry < userInfo.size(); registry++) {
userInfoIntoRow(resultSheet, cellStyle, rowCount, userEmail, userInfo, registry);
rowCount++;
}
// Normalize the row count
rowCount -= 1;
} else {
userInfoIntoRow(resultSheet, cellStyle, rowCount, userEmail, userInfo, 0);
continue;
}
}
cellCount++;
}
rowCount++;
}
resultSheet.autoSizeColumn(0);
resultSheet.autoSizeColumn(3);
resultSheet.autoSizeColumn(6);
FileOutputStream outputStream = new FileOutputStream(TARGET_FILE);
resultWorkbook.write(outputStream);
resultWorkbook.close();
log.info(PROCESSED_FINISHED);
System.exit(SpringApplication.exit(context));
}
private List<Subregion> getDataFromManagerEmail(String email) {
List<Subregion> res = structureForUserService.getStructureByEmail(email);
return res;
}
private CellStyle generateHeaderStyle(Workbook resultWorkbook) {
CellStyle headerStyle = resultWorkbook.createCellStyle();
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headerStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headerStyle.setBorderTop(BorderStyle.THIN);
headerStyle.setBorderBottom(BorderStyle.THIN);
headerStyle.setBorderLeft(BorderStyle.THIN);
headerStyle.setBorderRight(BorderStyle.THIN);
XSSFFont font = ((XSSFWorkbook) resultWorkbook).createFont();
font.setFontName("Arial");
font.setBold(true);
font.setColor(IndexedColors.WHITE.getIndex());
headerStyle.setFont(font);
return headerStyle;
}
private void createEmptyRow(Sheet resultSheet, CellStyle cellStyle, int rowCount, String userEmail) {
Row resultRow = resultSheet.createRow(rowCount);
resultRow.setRowStyle(cellStyle);
resultRow.createCell(0).setCellValue(userEmail);
resultRow.createCell(1).setCellValue("N/A");
resultRow.createCell(2).setCellValue("N/A");
resultRow.createCell(3).setCellValue("N/A");
resultRow.createCell(4).setCellValue("N/A");
resultRow.createCell(5).setCellValue("N/A");
resultRow.createCell(6).setCellValue("N/A");
resultRow.createCell(7).setCellValue("N/A");
}
private void userInfoIntoRow(Sheet resultSheet, CellStyle cellStyle, int rowCount, String userEmail, List<Subregion> userInfo, int registry) {
Row resultRow = resultSheet.createRow(rowCount);
resultRow.setRowStyle(cellStyle);
resultRow.createCell(0).setCellValue(userEmail);
resultRow.createCell(1).setCellValue(userInfo.get(registry).getId());
resultRow.createCell(2).setCellValue(userInfo.get(registry).getCode());
resultRow.createCell(3).setCellValue(userInfo.get(registry).getName());
resultRow.createCell(4).setCellValue(userInfo.get(registry).getRoles().get(0).getStructure().getId());
resultRow.createCell(5).setCellValue(userInfo.get(registry).getRoles().get(0).getStructure().getCode());
resultRow.createCell(6).setCellValue(userInfo.get(registry).getRoles().get(0).getStructure().getName());
resultRow.createCell(7).setCellValue(userInfo.get(registry).getRoles().get(0).getName());
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment