File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
package froala.editor.utils;
import java.awt.font.FontRenderContext;
import java.awt.font.LineBreakMeasurer;
import java.awt.font.TextAttribute;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.AttributedString;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
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.ShapeTypes;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
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.XSSFSimpleShape;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
private XSSFSheet sheet;
private int rowIndex = 0;
private int maxCols = 0;
private XSSFWorkbook workbook;
private int offsetCol = 0;
public ExcelUtil(String sheetName) {
workbook = new XSSFWorkbook();
sheet = workbook.createSheet(sheetName);
}
public void setOffset(int col, int row) {
offsetCol = col;
rowIndex = row;
}
public void addRow(List<String> rows) {
addRow(null, (short) 0, rows);
}
public void addRow(String backgroundColor, List<String> rows) {
addRow(backgroundColor, (short) 0, rows);
}
public int getRowIndex() {
return rowIndex;
}
public void setCellWidth(int cellnum, int width) {
sheet.setColumnWidth(cellnum, width);
}
public void setCell(int cellnum, int rownum, XSSFCellStyle cellStyle, String cellValue) {
XSSFRow row = sheet.getRow(rownum);
if (row == null) {
row = sheet.createRow(rownum);
}
XSSFCell cell = row.getCell(cellnum);
if (cell == null) {
cell = row.createCell(cellnum, XSSFCell.CELL_TYPE_STRING);
}
cell.setCellStyle(cellStyle);
cell.setCellValue(cellValue);
}
public void addRow(String backgroundColor, short boldweight, List<String> cellStrings) {
XSSFRow header = sheet.createRow(rowIndex++);
int cellIndex = offsetCol;
for (String value : cellStrings) {
XSSFCell cell = header.createCell(cellIndex++, XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(value);
if( backgroundColor != null ) {
cell.setCellStyle(createCellStyle(backgroundColor, boldweight));
}
}
if (maxCols < cellIndex) {
maxCols = cellIndex;
}
}
public void appendRow(int cellnum, int rownum, String backgroundColor, short boldweight, List<String> cellStrings) {
XSSFRow header = sheet.getRow(rownum);
if(header == null) {
rowIndex = rownum;
header = sheet.createRow(rowIndex++);
}
int cellIndex = cellnum;
for (String value : cellStrings) {
XSSFCell cell = header.createCell(cellIndex++, XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(value);
cell.setCellStyle(createCellStyle(backgroundColor, boldweight));
}
if (maxCols < cellIndex) {
maxCols = cellIndex;
}
}
public void addRow(List<Map<String, String>> style, List<String> cellStrings) {
XSSFRow header = sheet.createRow(rowIndex++);
int cellIndex = offsetCol;
for (String value : cellStrings) {
int index = cellIndex - offsetCol;
XSSFCell cell = header.createCell(cellIndex++, XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(value);
String backgroundColor = null;
short boldweight = 0;
if (style.size() > index) {
Map<String, String> styleMap = style.get(index);
backgroundColor = styleMap.get("backgroundColor");
if (styleMap.containsKey("boldweight")) {
boldweight = Short.parseShort(styleMap.get("boldweight"));
}
}
cell.setCellStyle(createCellStyle(backgroundColor, boldweight));
}
if (maxCols < cellIndex) {
maxCols = cellIndex;
}
}
public XSSFCellStyle createCellStyle(String backgroundColor, short boldweight) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
if (backgroundColor != null) {
cellStyle.setFillForegroundColor(new XSSFColor(hexToByteArray(backgroundColor)));
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
}
setSolidBorder(cellStyle);
if (boldweight != 0) {
Font headerFont = this.sheet.getWorkbook().createFont();
headerFont.setBoldweight(boldweight);
cellStyle.setFont(headerFont);
}
return cellStyle;
}
public void setFontHeight(int cellnum, int rownum, int height) {
sheet.getRow(rownum).getCell(cellnum).getCellStyle().getFont().setFontHeight(height);
}
public void setCellAlignment(int cellnum, int rownum, HorizontalAlignment align) {
sheet.getRow(rownum).getCell(cellnum).getCellStyle().setAlignment(align);
}
public void setCellVerticalAlignment(int cellnum, int rownum, VerticalAlignment align) {
sheet.getRow(rownum).getCell(cellnum).getCellStyle().setVerticalAlignment(align);
}
public void setCellWrapText(int cellnum, int rownum, boolean b) {
XSSFRow row = sheet.getRow(rownum);
XSSFCellStyle rowStyle = row.getRowStyle();
if (rowStyle == null) {
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setWrapText(b);
row.setRowStyle(cellStyle);
} else {
rowStyle.setWrapText(b);
}
row.getCell(cellnum).getCellStyle().setWrapText(b);
}
// hex to byte[]
public byte[] hexToByteArray(String hex) {
if (hex == null || hex.length() == 0) {
return null;
}
byte[] ba = new byte[hex.length() / 2];
for (int i = 0; i < ba.length; i++) {
ba[i] = (byte) Integer.parseInt(hex.substring(2 * i, 2 * i + 2), 16);
}
return ba;
}
public void setSolidBorder(XSSFCellStyle cellStyle) {
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
}
public void write(OutputStream outputStream) throws IOException {
// adjust column width to fit the content
for (int i = 0; i < maxCols; i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, Math.min(255 * 256, sheet.getColumnWidth(i) + 1500));
}
for (int i = 0; i < offsetCol; i++) {
setCellWidth(i, 900);
}
this.sheet.getWorkbook().write(outputStream);
// this.workbook.close();
}
public void merge(int firstCol, int firstRow, int lastCol, int lastRow) {
// 셀 병합
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); // 병합
}
public void drawRect(int rownum, int scolnum, int ecolnum, int dx1, int dx2) {
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
XSSFClientAnchor a = new XSSFClientAnchor();
a.setCol1(scolnum);
a.setRow1(rownum);
a.setDx1(pxToEmu(dx1));
a.setDy1(pxToEmu(5));
a.setDx2(pxToEmu(dx2));
a.setDy2(pxToEmu(-5));
a.setRow2(rownum + 1);
a.setCol2(ecolnum);
XSSFSimpleShape shape1 = patriarch.createSimpleShape(a);
shape1.setShapeType(ShapeTypes.RECT);
int red = 0, green = 0, blue = 0;
red = Integer.parseInt("f0", 16);
green = Integer.parseInt("ad", 16);
blue = Integer.parseInt("4e", 16);
shape1.setLineStyleColor(red, green, blue);
shape1.setFillColor(red, green, blue);
}
public static int pxToEmu(int px) {
return (int) Math.round(((double) px) * 72 * 20 * 635 / 96); // assume
// 96dpi
}
public static int emuToPx(int emu) {
return (int) Math.round(((double) emu) * 96 / 72 / 20 / 635); // assume
// 96dpi
}
public float getDefaultRowHeightInPoints() {
return this.sheet.getDefaultRowHeightInPoints();
}
public void setRowHeightInPoints(int rownum, float height) {
sheet.getRow(rownum).setHeightInPoints(height);
}
public float getRowHeightInPoints(int rownum) {
return sheet.getRow(rownum).getHeightInPoints();
}
/**
* ROW 높이 자동 조절
*
* @param rownum
* @param cellValue
*/
public void setAutoRowFit(int cellnum, int rownum) {
XSSFRow row = sheet.getRow(rownum);
XSSFCell cell = row.getCell(cellnum);
XSSFFont cellFont = cell.getCellStyle().getFont();
int fontStyle = java.awt.Font.PLAIN;
if (cellFont.getBold())
fontStyle = java.awt.Font.BOLD;
if (cellFont.getItalic())
fontStyle = java.awt.Font.ITALIC;
java.awt.Font currFont = new java.awt.Font(cellFont.getFontName(), fontStyle, cellFont.getFontHeightInPoints());
String cellText = cell.getStringCellValue();
AttributedString attrStr = new AttributedString(cellText);
attrStr.addAttribute(TextAttribute.FONT, currFont);
// Use LineBreakMeasurer to count number of lines needed for the text
//
FontRenderContext frc = new FontRenderContext(null, true, true);
LineBreakMeasurer measurer = new LineBreakMeasurer(attrStr.getIterator(), frc);
int nextPos = 0;
int lineCnt = 1;
float columnWidthInPx = sheet.getColumnWidth(cellnum);
while (measurer.getPosition() < cellText.length()) {
nextPos = measurer.nextOffset(columnWidthInPx);
lineCnt++;
measurer.setPosition(nextPos);
}
int fromIndex = -1;
while ((fromIndex = cellText.indexOf("\n", fromIndex + 1)) >= 0) {
lineCnt++;
}
if (lineCnt > 1) {
row.setHeightInPoints(sheet.getDefaultRowHeightInPoints() * lineCnt * /* fudge factor */ 1.1f);
}
}
public static List<List<String>> readExcel(File file) throws IOException, InvalidFormatException {
List<List<String>> resultList = null;
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
resultList = readExcel(fis, file.getName(), 0, null);
} catch (IOException | InvalidFormatException e) {
LOGGER.error("ERROR! excel read error!!");
return null;
} finally {
fis.close();
}
return resultList;
}
public static List<List<String>> readExcel(File file, int sheetAt) throws IOException, InvalidFormatException {
List<List<String>> resultList = null;
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
resultList = readExcel(fis, file.getName(), sheetAt, null);
} catch (IOException | InvalidFormatException e) {
LOGGER.error("ERROR! excel read error!!");
return null;
} finally {
fis.close();
}
return resultList;
}
public static List<List<String>> readExcel(File file, int sheetAt, Integer requiredIndex) throws IOException, InvalidFormatException {
List<List<String>> resultList = null;
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
resultList = readExcel(fis, file.getName(), sheetAt, requiredIndex);
} catch (IOException | InvalidFormatException e) {
LOGGER.error("ERROR! excel read error!!");
return null;
} finally {
fis.close();
}
return resultList;
}
public static List<List<String>> readExcel(InputStream is) throws IOException, InvalidFormatException {
List<List<String>> resultList = null;
try {
resultList = readExcel(is, "xlsx", 0, null);
} catch (IOException | InvalidFormatException e) {
LOGGER.error("ERROR! excel read error!!");
return null;
}
return resultList;
}
private static Workbook getWorkbook(InputStream inputStream, String fileName) throws IOException {
Workbook workbook = null;
if (fileName.endsWith("xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else if (fileName.endsWith("xls")) {
workbook = new HSSFWorkbook(inputStream);
} else {
throw new IllegalArgumentException("The specified file is not Excel file");
}
return workbook;
}
public static List<List<String>> readExcel(InputStream is, String fileName, int sheetAt, Integer requiredIndex)
throws IOException, InvalidFormatException {
List<List<String>> resultList = new ArrayList<>();
// 파일을 읽기위해 엑셀파일을 가져온다
Workbook workbook = getWorkbook(is, fileName);
int rowindex = 0;
int columnindex = 0;
// 시트 수 (첫번째에만 존재하므로 0을 준다)
// 만약 각 시트를 읽기위해서는 FOR문을 한번더 돌려준다
Sheet sheet = workbook.getSheetAt(sheetAt);
// 병합 셀 조회 후 병합해제
String cellVal="";
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
int colIndex = mergedRegion.getFirstColumn();
int firstRowIndex = mergedRegion.getFirstRow();
int lastRowIndex = mergedRegion.getLastRow();
if(colIndex==0){
cellVal = sheet.getRow(firstRowIndex).getCell(colIndex).getStringCellValue();
}
for (int j = firstRowIndex; j < lastRowIndex+1; j++) {
Cell inputCell = sheet.getRow(j).getCell(0);
inputCell.setCellValue(cellVal);
}
}
// 행의 수
int rows = sheet.getPhysicalNumberOfRows();
// int rows = sheet.getLastRowNum();
int rowCnt = 0;
for (rowindex = 0; rowindex < rows; rowindex++) {
// 행을 읽는다
Row row = sheet.getRow(rowindex);
if (row != null) {
// 필수로 값이 있어야 하는 Cell 체크.
if (requiredIndex != null) {
Cell requiredCell = row.getCell(requiredIndex);
String requiredValue = "";
switch (requiredCell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
requiredValue = requiredCell.getBooleanCellValue() + "";
break;
default:
requiredValue = "true";
break;
}
if ("false".equals(requiredValue)) {
break;
// continue;
}
// Cell requiredCell = row.getCell((short)0x1);
// if (requiredCell == null) {
// continue;
// }
}
resultList.add(new ArrayList<String>());
// 셀의 수
// int cells = row.getPhysicalNumberOfCells();
int cells = row.getLastCellNum();
for (columnindex = 0; columnindex <= cells; columnindex++) {
// 셀값을 읽는다
Cell cell = row.getCell(columnindex);
String value = "";
// 셀이 빈값일경우를 위한 널체크
if (rowindex == 0 && cell == null) {
continue;
}
if (cell != null) {
// 타입별로 내용 읽기
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
case Cell.CELL_TYPE_NUMERIC:
value = String.format("%1$,.0f", cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue() + "";
break;
case Cell.CELL_TYPE_BLANK:
value = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_ERROR:
value = cell.getErrorCellValue() + "";
break;
}
}
if ("false".equals(value)) {
value = "";
}
resultList.get(rowCnt).add(value);
}
}
rowCnt++;
}
// workbook.;
return resultList;
}
public static List<List<String>> readFormulaExcel(File file, int sheetAt)
throws IOException, InvalidFormatException {
List<List<String>> resultList = null;
FileInputStream fis = null;
OPCPackage opcPackage = null;
Workbook workbook = null;
try {
resultList = new ArrayList<List<String>>();
// 파일을 읽기위해 엑셀파일을 가져온다
if (file.getName().endsWith("xlsx")) {
opcPackage = OPCPackage.open(file);
workbook = new XSSFWorkbook(opcPackage);
} else if (file.getName().endsWith("xls")) {
fis = new FileInputStream(file);
workbook = new HSSFWorkbook(fis);
}
int rowindex = 0;
int columnindex = 0;
// 시트 수 (첫번째에만 존재하므로 0을 준다)
// 만약 각 시트를 읽기위해서는 FOR문을 한번더 돌려준다
Sheet sheet = workbook.getSheetAt(sheetAt);
// 행의 수
int rows = sheet.getPhysicalNumberOfRows();
int rowCnt = 0;
for (rowindex = 0; rowindex < rows; rowindex++) {
// 행을 읽는다
Row row = sheet.getRow(rowindex);
if (row != null) {
resultList.add(new ArrayList<String>());
// 셀의 수
int cells = row.getLastCellNum();
for (columnindex = 0; columnindex < cells; columnindex++) {
// 셀값을 읽는다
Cell cell = row.getCell(columnindex);
String value = "";
// 셀이 빈값일경우를 위한 널체크
if (rowindex == 0 && cell == null) {
continue;
}
if (cell != null) {
// 타입별로 내용 읽기
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
// 수식결과형태만 조회
// 값 처리 과정은 속도가 너무 느림
int cellType = cell.getCachedFormulaResultType();
switch(cellType) {
case Cell.CELL_TYPE_NUMERIC:
//날짜인지 아닌지 구분
if( DateUtil.isCellDateFormatted( cell)) {
Date date = cell.getDateCellValue();
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
}else {
if(cell.getNumericCellValue() == (long) cell.getNumericCellValue()) {
value = String.format("%d",(long)cell.getNumericCellValue());
}else {
//CP별 가중치는 서식 백분율 값 들어가있음
if(sheetAt == 4) {
value = String.format("%.3f",cell.getNumericCellValue()*100);
}else {
value = String.format("%.3f",cell.getNumericCellValue());
}
}
}
break;
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue() + "";
break;
case Cell.CELL_TYPE_BLANK:
value = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_ERROR:
value = cell.getErrorCellValue() + "";
break;
}
break;
case Cell.CELL_TYPE_NUMERIC:
//날짜인지 아닌지 구분
if( DateUtil.isCellDateFormatted( cell)) {
Date date = cell.getDateCellValue();
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
}else {
if(cell.getNumericCellValue() == (long) cell.getNumericCellValue()) {
value = String.format("%d",(long)cell.getNumericCellValue());
}else {
//CP별 가중치는 서식 백분율 값 들어가있음
if(sheetAt == 4) {
value = String.format("%.3f",cell.getNumericCellValue()*100);
}else {
value = String.format("%.3f",cell.getNumericCellValue());
}
}
}
break;
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue() + "";
break;
case Cell.CELL_TYPE_BLANK:
value = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_ERROR:
value = cell.getErrorCellValue() + "";
break;
}
}
if ("false".equals(value)) {
value = "";
}
resultList.get(rowCnt).add(value);
}
}
rowCnt++;
}
} catch (IOException e) {
LOGGER.error("ERROR! excel read error!!");
return null;
} finally {
if (file.getName().endsWith("xlsx")) {
opcPackage.close();
} else if (file.getName().endsWith("xls")) {
fis.close();
}
}
return resultList;
}
/**
* 엑셀 출력 시, valueof null 일 때, 빈값으로 값 치환
* @Author : 이다솜
* @Date : 2021. 10. 6.
* @Method Name : stingValueOf
* @return : String
*/
public String stringValueOf(Object object) {
return object == null ? "" : String.valueOf(object);
}
}