Data → Excel 변환

Data → Excel  변환

pom.xml

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

 

 

 

Controller 에서 

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

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.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.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;

import rf.data_frame.data.log.vo.DataLogVo;

 

	public String ExcelDownload(List<DataLogVo> list)  {
		
	    SXSSFWorkbook workbook = new SXSSFWorkbook();
	    SXSSFSheet sheet = workbook.createSheet("Log Data");
	        
	    // 열 폭 수정
	    sheet.setColumnWidth(1, 6000);
	    sheet.setColumnWidth(2, 8000);
	    sheet.setColumnWidth(3, 6500);	    
	    sheet.setColumnWidth(4, 8000);
	    sheet.setColumnWidth(5, 8000);
	    sheet.setColumnWidth(6, 4000);
	    sheet.setColumnWidth(7, 4000);
	    sheet.setColumnWidth(8, 4000);
	    
	    
	    // Header Name Style Start =========================================== 
	    Font headerNameFont = workbook.createFont();
	    headerNameFont.setFontName("나눔고딕");
	    headerNameFont.setFontHeight((short)500);
	    headerNameFont.setColor(IndexedColors.GREEN.getIndex());
	    headerNameFont.setBold(true);
	    
	    CellStyle headerNameStyle = workbook.createCellStyle();
	    headerNameStyle.setAlignment(HorizontalAlignment.CENTER);
	    headerNameStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	    headerNameStyle.setBorderTop(BorderStyle.THICK);
	    headerNameStyle.setBorderLeft(BorderStyle.MEDIUM_DASH_DOT_DOT);
	    headerNameStyle.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
	    headerNameStyle.setFillPattern(FillPatternType.BRICKS);
	    headerNameStyle.setFont(headerNameFont);
	    // Header Name Style End ===========================================
	    
	    
	    
		// Title Name Style Start ===========================================
		XSSFCellStyle titleNameStyle = (XSSFCellStyle) workbook.createCellStyle();
		titleNameStyle.setAlignment(HorizontalAlignment.CENTER);
		titleNameStyle.setVerticalAlignment(VerticalAlignment.TOP);
		titleNameStyle.setBorderTop(BorderStyle.THIN);
		titleNameStyle.setBorderLeft(BorderStyle.THIN);
		titleNameStyle.setBorderBottom(BorderStyle.THIN);
		titleNameStyle.setBorderRight(BorderStyle.THIN);
		titleNameStyle.setFillForegroundColor(new XSSFColor(new byte[] { (byte) 192, (byte) 192, (byte) 192 }, null));
		titleNameStyle.setFillPattern(FillPatternType.FINE_DOTS);
		// Title Name Style End ===========================================

		
		
		int rowLocation = 0;

		Row row = null;
		Cell cell = null;

		
		
		// Header Name Start ===========================================
		row = sheet.createRow(++rowLocation);

		for (int i = 1; i < 9; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(headerNameStyle);
			cell.setCellValue("[ Lucy Measure ] Data Log 1");
		}

		// 행시작, 행끝, 열시작, 열끝
		sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 8));
		// Header Name End ===========================================

		
		
		// Title Name Start ===========================================
		rowLocation = rowLocation + 2;

		row = sheet.createRow(++rowLocation);
		cell = row.createCell(1);
		cell.setCellValue("제 목");
		cell.setCellStyle(titleNameStyle);
		cell = row.createCell(2);
		cell.setCellValue("주 제 ( 대 → 중 ) ");
		cell.setCellStyle(titleNameStyle);
		cell = row.createCell(3);
		cell.setCellValue("출 처");
		cell.setCellStyle(titleNameStyle);
		cell = row.createCell(4);
		cell.setCellValue("수집 일시");
		cell.setCellStyle(titleNameStyle);
		cell = row.createCell(5);
		cell.setCellValue("스팸 카테고리");
		cell.setCellStyle(titleNameStyle);
		cell = row.createCell(6);
		cell.setCellValue("감 성");
		cell.setCellStyle(titleNameStyle);
		cell = row.createCell(7);
		cell.setCellValue("작 업 자");
		cell.setCellStyle(titleNameStyle);
		cell = row.createCell(8);
		cell.setCellValue("수 정 일");
		cell.setCellStyle(titleNameStyle);
		// Title Name End ===========================================

		// Data Log Data Start ===========================================
		for (DataLogVo vo : list) {

			int x = 1;
			row = sheet.createRow(++rowLocation);

			cell = row.createCell(x++);
			cell.setCellValue(vo.getDml_title());

			cell = row.createCell(x++);
			cell.setCellValue(vo.getI_p_topic() + " > " + vo.getI_topic());

			cell = row.createCell(x++);
			cell.setCellValue(vo.getDml_sitename());

			cell = row.createCell(x++);
			cell.setCellValue(vo.getDml_crawlstamp());

			cell = row.createCell(x++);
			cell.setCellValue(vo.getDml_spam_category() + " → " + vo.getDml_update_spam_category());

			cell = row.createCell(x++);
			cell.setCellValue(vo.getDml_trend() + " → " + vo.getDml_update_trend());

			cell = row.createCell(x++);
			cell.setCellValue(vo.getSu_seq());

			cell = row.createCell(x++);
			cell.setCellValue(vo.getDml_update_date());
		}
		// Data Log Data End ===========================================
       
		try {
			String home = System.getProperty("user.home");
			File xlsFile = new File(home + "/Downloads/Lucy_Measuer_DataLog.xls");

			FileOutputStream fileOut = new FileOutputStream(xlsFile);
			workbook.write(fileOut);

		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return "엑셀 다운로드가 실패하였습니다";

		} catch (IOException e) {
			e.printStackTrace();
			return "엑셀 다운로드가 실패하였습니다";
		}

		return "엑셀 다운로드가 완료 되었습니다";

	}

 

 

DataBase 에서 Data 가지고 와서 Excel 로 변환 하기 완성.

 

 

---

참고 사이트 

myjamong.tistory.com/115

[Spring] 스프링 엑셀 POI 스타일, 폰트 적용(POI 라이브러리 2편) :: 마이자몽

 

myjamong.tistory.com/115myjamong.tistory.com/115

[Spring] 스프링 엑셀 POI 스타일, 폰트 적용(POI 라이브러리 2편) :: 마이자몽