Elastic to excel

P_Sangsu·2021년 9월 16일
0
post-thumbnail

이번 장에서는 Elasticsearch 에 인덱싱 되어있는 문서들을 로컬 저장소에 excel 형태로 저장할 때 사용한 코드를 다뤄보도록 하겠다.

  • Elasticsearch에 match all request를 선언
SearchRequest searchRequest = new SearchRequest(index);
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.query(QueryBuilders.matchAllQuery());
searchRequest.source(searchSourceBuilder);
  • client에서 search request 받아오기
SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);

SearchHit[] searchHits = hits.getHits();

for (SearchHit hit : searchHits) { 
// JSON-String
String sourceAsString = hit.getSourceAsString();

// JSON-Map
Map<String, Object> sourceAsMap = hit.getSourceAsMap();
dataList.add(sourceAsMap);
}// end for
  • excel (.xls 확장자)로 저장 (use poi library)
// Java to excel
HSSFWorkbook workbook = new HSSFWorkbook(); // Create new excel
HSSFSheet sheet = workbook.createSheet(index); // Create new Sheet

int rowIndex = 0;

for (Map<String, Object> map : dataList) {
	int columnIndex = 0;
	HSSFRow row = sheet.createRow(rowIndex); // Excel's row starts from sequence 0
	HSSFCell cell = row.createCell(columnIndex); // Excel's column starts from sequence 0

	if (rowIndex == 0) { // Insert "field" in 1st row
		for (Entry<String, Object> entry : map.entrySet()) {
			cell = row.createCell(columnIndex); 
			cell.setCellValue(entry.getKey());
			columnIndex++;
		}
		rowIndex++;
		columnIndex=0;
	}
	row = sheet.createRow(rowIndex); 

	for (Entry<String, Object> entry : map.entrySet()) {
		cell = row.createCell(columnIndex); // Excel's column starts from sequence 0
		cell.setCellValue(entry.getValue().toString());
		columnIndex++;
	} //end for
	rowIndex++;
} //end for

try {
	FileOutputStream fileoutputstream = new FileOutputStream(
			"C:/Users/XXX/Desktop/ElasticToExcel.xls");
	workbook.write(fileoutputstream);
	fileoutputstream.close();
	System.out.println("엑셀파일 생성 성공");
} catch (IOException e) {
	e.printStackTrace();
	System.out.println("엑셀파일 생성 실패");
}
  • excel (.xlsx 확장자)로 저장은 HSSF 를 XSSF 로 모두 바꿔주면 끝이다.

elasticsearch 공식 홈페이지에서 제공하는 search scroll의 코드를 사용해보았다.
참고: https://www.elastic.co/guide/en/elasticsearch/client/java-rest/6.8/java-rest-high-search-scroll.html

profile
Wannabe Sexy Developer

0개의 댓글