-
Notifications
You must be signed in to change notification settings - Fork 0
/
Excel.java
101 lines (89 loc) · 4.02 KB
/
Excel.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
package org.example.excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.model.MemberDTO;
import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Excel {
public void load() {
try {
// Stream을 받아오는 가장 기본적인 형태
FileInputStream file = new FileInputStream(new File("/Users/rookedsysc/Downloads/example.xlsx"));
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
switch (cell.getCellType()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date dateValue = cell.getDateCellValue();
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String formattedDate = dateFormat.format(dateValue);
System.out.print(formattedDate + "\t");
} else {
double numericValue = cell.getNumericCellValue();
if (numericValue == Math.floor(numericValue)) {
int intValue = (int) numericValue;
System.out.print(intValue + "\t");
} else {
System.out.print(numericValue + "\t");
}
}
break;
case STRING:
String stringValue = cell.getStringCellValue();
System.out.print(stringValue + "\t");
break;
case BOOLEAN:
boolean booleanValue = cell.getBooleanCellValue();
System.out.print(booleanValue + "\t");
break;
case FORMULA:
String formulaValue = cell.getCellFormula();
System.out.print(formulaValue + "\t");
break;
default:
System.out.println("\t");
break;
}
}
System.out.println();
}
file.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public void write(MemberDTO[] listMembers) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Members");
int rowCount = 0;
for (MemberDTO member : listMembers) {
Row row = sheet.createRow(rowCount++);
writeMember(workbook,member, row);
}
try (FileOutputStream outputStream = new FileOutputStream("/Users/rookedsysc/Downloads/java-excel-write-example.xlsx")) {
workbook.write(outputStream);
}
}
private void writeMember(XSSFWorkbook workbook, MemberDTO member, Row row) {
Cell cell = row.createCell(0);
cell.setCellValue(member.getName());
// 시간 형식으로 데이터 넣는 방법
// 엑셀에 month/day/year hour:minute 형식으로 데이터 넣기
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(
createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(1);
cell.setCellValue(member.getBirthDate());
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue(member.getPhone());
cell = row.createCell(3);
cell.setCellValue(member.isMarried());
}
}