자바 아파치 poi를 사용해 json 데이터로 엑셀파일 만들기 및 열기

자바로 apache poi를 사용해서 json 데이터를 이용해 엑셀을 만들고 자동으로 여는것(오픈) 까지 알아보겠다. 필요한 라이브러리 jar 들은 첨부가 안되어 쓴다.

commons-collections-3.2.1.jar, json-simple-1.1.1.jar, poi-3.10-FINAL.jar, poi-3.13.jar, poi-excelant-3.13.jar, poi-ooxml-3.10-FINAL.jar, poi-ooxml-3.13.jar, poi-ooxml-schemas-3.10-FINAL.jar, poi-ooxml-schemas-3.13.jar, poi-scratchpad-3.13.jar, xmlbeans-2.3.0.jar

 

package excel;

import java.awt.Desktop;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;

public class ExcelTestJson {
	
	String excelFileName="navermap.xlsx";
	
	public void makeExcel(Object [] columnTitle, String bodyJson) throws FileNotFoundException, IOException, ParseException {
		try (Workbook workbook = new XSSFWorkbook()) {
			Sheet sheet=workbook.createSheet();
			
			Font font = workbook.createFont();
			
			sheet.setColumnWidth(0, 2000);
			sheet.setColumnWidth(1, 5000);
			sheet.setColumnWidth(2, 5000);
			sheet.setColumnWidth(3, 12000);
			
			CellStyle styleTitle=workbook.createCellStyle();
			styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			font.setBoldweight(Font.BOLDWEIGHT_BOLD);
			styleTitle.setFont(font);

			CellStyle style=workbook.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			
			Row row=sheet.createRow(0);
			Cell cell=null;
			//순번 빼고 1번(이름) 부터 한다.
			for(int i=0; i < columnTitle.length; i++) {
				String header = columnTitle[i].toString();
				cell=row.createCell(i);
				cell.setCellValue( header );
				cell.setCellStyle(styleTitle);
			}
			
			JSONArray jsonArr = (JSONArray)new JSONParser().parse(bodyJson);
			System.out.println(jsonArr);
			
			for(int j=0; j<jsonArr.size(); j++) {
				row=sheet.createRow(j+1);
				
				JSONObject obj = (JSONObject)jsonArr.get(j);
				String seq=String.valueOf(j+1);
				String name = obj.get("name").toString();
				String tel = obj.get("tel").toString();
				String address = obj.get("address").toString();
				System.out.println("name="+name);
				System.out.println("tel="+tel);
				System.out.println("address="+address);
				System.out.println();
				
				cell = row.createCell(0);
				cell.setCellValue(seq);
				cell.setCellStyle(style);
				
				cell = row.createCell(1);
				cell.setCellValue(name);
				cell.setCellStyle(style);
				
				cell = row.createCell(2);
				cell.setCellValue(tel);
				cell.setCellStyle(style);
				
				cell = row.createCell(3);
				cell.setCellValue(address);
			}
			
			try(OutputStream fileOut=new FileOutputStream(excelFileName)){
				workbook.write(fileOut);
			}
		}
		Desktop.getDesktop().edit(new File(excelFileName));
	}

	public static void main(String[] args) throws FileNotFoundException, IOException, ParseException {
		
		Object [] columnTitle= {"순번","이름","전화번호","주소"};
		List<Map<String, ?>> bodyList = new ArrayList<>();
		
		Map<String,String> map=new HashMap<>();
		map.put("name", "홍길동");
		map.put("tel", "010");
		map.put("address", "서울");
		
		Map<String,String> map2=new HashMap<>();
		map2.put("name", "성춘향");
		map2.put("tel", "011");
		map2.put("address", "경기");
		
		bodyList.add(map);
		bodyList.add(map2);
		
		String bodyJson = JSONArray.toJSONString(bodyList);
		System.out.println("bodyJson="+bodyJson);
		
		new ExcelTestJson().makeExcel(columnTitle, bodyJson);
	}

}