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