Apache POI

스프링

by e7e 2022. 11. 16. 16:20

이너넷에 오래된 것부터 최신 것 까지 자료가 너무 많다는 게 문제!

일단 참공~ 해크씨믄 Always 일낑/쓰낑 이해후 정형화, 소니 꽤 가겠넹~~

XSSF <-> xlsx

HSSF <-> xls

 		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>5.2.3</version>
		</dependency>
 
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.3</version>
		</dependency>

 <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>엑셀파일다루기</h1>
<form action="/basic/excel/upload" method="post" enctype="multipart/form-data">
	<input type="file" name="excelFile"  value="" accept=".xlsx"><br>
	<input type="submit" value="쩐쏭">
</form>
</body>
</html>

 @Controller
@RequestMapping("/excel")
@Slf4j
public class ExcelController {
 
	@GetMapping("/form")
	public String exelForm() {
		return "jsp/excelForm";
	}
 
	@GetMapping(value ="/sampleMake")
	public void getSample(HttpServletResponse response) throws IOException {
		Workbook workbook = new XSSFWorkbook();
 
		Sheet sheet = workbook.createSheet("Persons");
		sheet.setColumnWidth(0, 6000);
		sheet.setColumnWidth(1, 4000);
 
		Row header = sheet.createRow(0);
 
		CellStyle headerStyle = workbook.createCellStyle();
		headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
		headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 
		XSSFFont font = ((XSSFWorkbook) workbook).createFont();
		font.setFontName("Cookirun");
		font.setFontHeightInPoints((short) 50);
		font.setBold(true);
		headerStyle.setFont(font);
 
		Cell headerCell = header.createCell(0);
		headerCell.setCellValue("리름");
		headerCell.setCellStyle(headerStyle);
 
		headerCell = header.createCell(1);
		headerCell.setCellValue("나잉");
		headerCell.setCellStyle(headerStyle);
		
		// 다운로드시 추가 할 내용, AJAX라면 다르당, a태그의 src 속성과 download속성을 이용하면 편리할 듯
		response.setContentType("ms-vnd/excel");
		response.setHeader("Content-Disposition", "attachment;filename=sample.xlsx");
		
		workbook.write(response.getOutputStream());
		workbook.close();		
	}
	
	
	@PostMapping("/upload")
	@ResponseBody
	public Map<Integer, List<String>> excelUpload(MultipartFile excelFile) throws IOException {
		Map<Integer, List<String>> dataList = new HashMap<Integer, List<String>>();
		
		int i=0;
		Workbook workBook = new XSSFWorkbook(excelFile.getInputStream()); 
		Sheet sheet = workBook.getSheetAt(0);
		
		for (Row row : sheet) {
			dataList.put(i, new ArrayList<String>());
		    for (Cell cell : row) {
				switch(cell.getCellType()) {
					case STRING:
						dataList.get(new Integer(i)).add(cell.getRichStringCellValue().getString());
						break;
					case NUMERIC:
						if (DateUtil.isCellDateFormatted(cell)) {
						    dataList.get(i).add(cell.getDateCellValue() + "");
						} else {
						    dataList.get(i).add((int)cell.getNumericCellValue() + "");
						}						
						break;
					case BOOLEAN:
						dataList.get(i).add(cell.getBooleanCellValue() + "");
						break;
					case FORMULA:
						dataList.get(i).add(cell.getCellFormula() + "");
						break;
					default: dataList.get(new Integer(i)).add(" ");
				}
			}
		    i++;
		}
		
		workBook.close();    // 열었으면 닫장
		return dataList;
	}
}

 //MultiPartFile To File
public File multipartFileToFile(MultipartFile multipart) throws IOException 
{
    File convFile = new File( multipart.getOriginalFilename());
    multipart.transferTo(convFile);
    return convFile;
}
 
// 참공
public File change(MultipartFile file)
{    
    File newFile = new File(file.getOriginalFilename());
    newFile.createNewFile(); 
    FileOutputStream fos = new FileOutputStream(newFile); 
    fos.write(file.getBytes());
    fos.close(); 
    return newFile;
}

'스프링' 카테고리의 다른 글

나만의 유튜브(youtube)맹글깅 (0)	2023.02.03
파일 업로드 (0)	2023.01.02
STS3(Spring Tool Suite) 설치 (0)	2022.10.20
sitemesh 적용 (0)	2022.10.12
아주 주의 (2)	2022.06.29

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

e7e

고정 헤더 영역

메뉴 레이어

메뉴 리스트

검색 레이어

검색 영역

상세 컨텐츠

본문 제목

본문

'스프링' 카테고리의 다른 글

관련글 더보기

추가 정보

인기글

최신글

티스토리툴바

개인정보

단축키

내 블로그

블로그 게시글

모든 영역

	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
	<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>5.2.3</version>
	</dependency>

	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
	<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>5.2.3</version>
	</dependency>

	<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
	<!DOCTYPE html>
	<html>
	<head>
	<meta charset="UTF-8">
	<title>Insert title here</title>
	</head>
	<body>
	<h1>엑셀파일다루기</h1>
	<form action="/basic/excel/upload" method="post" enctype="multipart/form-data">
	<input type="file" name="excelFile" value="" accept=".xlsx"><br>
	<input type="submit" value="쩐쏭">
	</form>
	</body>
	</html>

	@Controller
	@RequestMapping("/excel")
	@Slf4j
	public class ExcelController {

	@GetMapping("/form")
	public String exelForm() {
	return "jsp/excelForm";
	}

	@GetMapping(value ="/sampleMake")
	public void getSample(HttpServletResponse response) throws IOException {
	Workbook workbook = new XSSFWorkbook();

	Sheet sheet = workbook.createSheet("Persons");
	sheet.setColumnWidth(0, 6000);
	sheet.setColumnWidth(1, 4000);

	Row header = sheet.createRow(0);

	CellStyle headerStyle = workbook.createCellStyle();
	headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
	headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

	XSSFFont font = ((XSSFWorkbook) workbook).createFont();
	font.setFontName("Cookirun");
	font.setFontHeightInPoints((short) 50);
	font.setBold(true);
	headerStyle.setFont(font);

	Cell headerCell = header.createCell(0);
	headerCell.setCellValue("리름");
	headerCell.setCellStyle(headerStyle);

	headerCell = header.createCell(1);
	headerCell.setCellValue("나잉");
	headerCell.setCellStyle(headerStyle);

	// 다운로드시 추가 할 내용, AJAX라면 다르당, a태그의 src 속성과 download속성을 이용하면 편리할 듯
	response.setContentType("ms-vnd/excel");
	response.setHeader("Content-Disposition", "attachment;filename=sample.xlsx");

	workbook.write(response.getOutputStream());
	workbook.close();
	}


	@PostMapping("/upload")
	@ResponseBody
	public Map<Integer, List<String>> excelUpload(MultipartFile excelFile) throws IOException {
	Map<Integer, List<String>> dataList = new HashMap<Integer, List<String>>();

	int i=0;
	Workbook workBook = new XSSFWorkbook(excelFile.getInputStream());
	Sheet sheet = workBook.getSheetAt(0);

	for (Row row : sheet) {
	dataList.put(i, new ArrayList<String>());
	for (Cell cell : row) {
	switch(cell.getCellType()) {
	case STRING:
	dataList.get(new Integer(i)).add(cell.getRichStringCellValue().getString());
	break;
	case NUMERIC:
	if (DateUtil.isCellDateFormatted(cell)) {
	dataList.get(i).add(cell.getDateCellValue() + "");
	} else {
	dataList.get(i).add((int)cell.getNumericCellValue() + "");
	}
	break;
	case BOOLEAN:
	dataList.get(i).add(cell.getBooleanCellValue() + "");
	break;
	case FORMULA:
	dataList.get(i).add(cell.getCellFormula() + "");
	break;
	default: dataList.get(new Integer(i)).add(" ");
	}
	}
	i++;
	}

	workBook.close(); // 열었으면 닫장
	return dataList;
	}
	}

	//MultiPartFile To File
	public File multipartFileToFile(MultipartFile multipart) throws IOException
	{
	File convFile = new File( multipart.getOriginalFilename());
	multipart.transferTo(convFile);
	return convFile;
	}

	// 참공
	public File change(MultipartFile file)
	{
	File newFile = new File(file.getOriginalFilename());
	newFile.createNewFile();
	FileOutputStream fos = new FileOutputStream(newFile);
	fos.write(file.getBytes());
	fos.close();
	return newFile;
	}