[Java/POI/엑셀출력] POI 라이브러리를 이용한 엑셀 출력 - POI-3.16

사용 라이브러리 

poi-3.16

poi-3.16.jar

poi-examples-3.16.jar

poi-excelant-3.16.jar

poi-ooxml-3.16.jar

poi-ooxml-schemas-3.16.jar

poi-scratchpad-3.16.jar

curvesapi-1.04.jar

xmlbeans-2.6.0.jar

해당 라이브러리 모두 참조 바랍니다.

저는 2007이하 버전으로 사용하였습니다. 2007이하 버전에서는 6만몇천건이상은 출력이 안되므로 

5만건 이상일때 마다 시트를 새로 생성하여 출력하였습니다.

해당 소스는  스타일 설정만빼면 간단합니다.

테스트 출력시 30만건까지는 1분내로 거뜬히 출력하였습니다.

출력양이 많을 경우 데이터 조회에 대한 쿼리 성능을 확인해주세요

=================================================================

ExcelService.java

import java.io.BufferedReader; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.hssf.util.HSSFColor; 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.IndexedColors; public Request printExcel(Request request) throws Exception {     // 쿼리조회로 엑셀을 출력할 데이터 조회     List<ExcelExVO> testList = testServiceMapper.getTestExcel(request.getParameter("param"));     // Excel 2007 이하     Workbook xlsWb = new HSSFWorkbook();     // Excel 2007 이상은 위부분을 이렇게 사용하세요     //Workbook xlsWb = new XSSFWorkbook();     // Sheet1라는 시트를 생성합니다.     Sheet sheet = xlsWb.createSheet("Sheet1");          // 컬럼 너비 설정     sheet.setColumnWidth(0, 2000);     sheet.setColumnWidth(1, 6000);     sheet.setColumnWidth(2, 6000);     sheet.setColumnWidth(3, 6000);     sheet.setColumnWidth(4, 6000);     sheet.setColumnWidth(5, 6000);     sheet.setColumnWidth(6, 6000);     sheet.setColumnWidth(7, 6000);          CellStyle titleStyle = xlsWb.createCellStyle();     titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);     titleStyle.setAlignment(CellStyle.ALIGN_CENTER);             Font titleFont = xlsWb.createFont();     titleFont.setFontHeightInPoints((short)24);     titleStyle.setFont(titleFont);              titleStyle.setWrapText(true); // 줄 바꿈     titleStyle.setFillForegroundColor(HSSFColor.LIME.index);     titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);              CellStyle headerStyle = xlsWb.createCellStyle();     headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);     headerStyle.setAlignment(CellStyle.ALIGN_CENTER);     headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);     headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);              headerStyle.setBorderBottom(CellStyle.BORDER_THIN);     headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());     headerStyle.setBorderLeft(CellStyle.BORDER_THIN);     headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());     headerStyle.setBorderRight(CellStyle.BORDER_THIN);     headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());     headerStyle.setBorderTop(CellStyle.BORDER_THIN);     headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());              CellStyle cellStyle = xlsWb.createCellStyle();     cellStyle.setBorderBottom(CellStyle.BORDER_THIN);     cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());     cellStyle.setBorderLeft(CellStyle.BORDER_THIN);     cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());     cellStyle.setBorderRight(CellStyle.BORDER_THIN);     cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());     cellStyle.setBorderTop(CellStyle.BORDER_THIN);     cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());              Row row = null;     Cell cell = null;              // Title     row = sheet.createRow(0);     row.setHeightInPoints(30);     cell = row.createCell(0);     cell.setCellValue("조회");     cell.setCellStyle(titleStyle);     CellRangeAddress region = CellRangeAddress.valueOf("A1:H1");     sheet.addMergedRegion(region);              // 헤더     row = sheet.createRow(2);     createCell(row, cell, "No", 0, headerStyle);     createCell(row, cell, "이름", 1, headerStyle);     createCell(row, cell, "나이", 2, headerStyle);     createCell(row, cell, "전화번호", 3, headerStyle);     createCell(row, cell, "주소", 4, headerStyle);     createCell(row, cell, "이메일", 5, headerStyle);     createCell(row, cell, "직업", 6, headerStyle);     createCell(row, cell, "혈액형", 7, headerStyle);              //---------------------------------              Iterator<ExcelExVO> testIterator = testList.iterator();     int i = 0;     int sheetNum = 1;     while (testIterator.hasNext()) { i++;   if(i % 50001 == 0) {             // 5만건 이상일 경우 시트를 새로 생성     sheetNum++;     sheet = xlsWb.createSheet("Sheet" + sheetNum);                           // 컬럼 너비 설정     sheet.setColumnWidth(0, 2000);     sheet.setColumnWidth(1, 6000);     sheet.setColumnWidth(2, 6000);             sheet.setColumnWidth(3, 6000);             sheet.setColumnWidth(4, 6000);             sheet.setColumnWidth(5, 6000);             sheet.setColumnWidth(6, 6000);             sheet.setColumnWidth(7, 6000);                           row = null;             cell = null;                           // 헤더             row = sheet.createRow(2);             createCell(row, cell, "No", 0, headerStyle);             createCell(row, cell, "이름", 1, headerStyle);             createCell(row, cell, "나이", 2, headerStyle);             createCell(row, cell, "전화번호", 3, headerStyle);             createCell(row, cell, "주소", 4, headerStyle);             createCell(row, cell, "이메일", 5, headerStyle);             createCell(row, cell, "직업", 6, headerStyle);             createCell(row, cell, "혈액형", 7, headerStyle);                           i = 1;         }                  ExcelExVO item = historyIterator.next();         row = sheet.createRow(i+2);                  createCell(row, cell, item.getColumn1(), 0, cellStyle);         createCell(row, cell, item.getColumn2(), 1, cellStyle);         createCell(row, cell, item.getColumn3(), 2, cellStyle);         createCell(row, cell, item.getColumn4(), 3, cellStyle);         createCell(row, cell, item.getColumn5(), 4, cellStyle);         createCell(row, cell, item.getColumn6(), 5, cellStyle);         createCell(row, cell, item.getColumn7(), 6, cellStyle);         createCell(row, cell, item.getColumn8(), 7, cellStyle);     }          try {         char fileSeparator = File.separatorChar;         String path = "/excel";         String fileName = "통합문서1";         HttpServletRequest req = ((ServletRequestAttributes)RequestContextHolder.currentRequestAttributes()).getRequest();              String objPath = "저장위치";                  File dir = new File(objPath); if (!dir.isDirectory()) {     dir.mkdirs(); }                   objPath += fileName; objPath = objPath.replace('/', fileSeparator);              File xlsFile = new File(objPath); FileOutputStream fileOut = new FileOutputStream(xlsFile); xlsWb.write(fileOut); fileOut.flush(); fileOut.close();          } catch (FileNotFoundException e) { e.printStackTrace();     } catch (IOException e) {  e.printStackTrace();     }               return request; } // Row, Cell, Index, Style private void createCell(Row row, Cell cell, String value, int i, CellStyle style) {     cell = row.createCell(i);     cell.setCellValue(value);     cell.setCellStyle(style); }

...

ExcelExVO.java

public class ExcelExVO { private String column1; private String column2; private String column3; private String column4; private String column5; private String column6; private String column7; private String column8; private String column9; private String column10; public String getColumn1() { return column1; } public void setColumn1(String column1) { this.column1 = column1; } public String getColumn2() { return column2; } public void setColumn2(String column2) { this.column2 = column2; } public String getColumn3() { return column3; } public void setColumn3(String column3) { this.column3 = column3; } public String getColumn4() { return column4; } public void setColumn4(String column4) { this.column4 = column4; } public String getColumn5() { return column5; } public void setColumn5(String column5) { this.column5 = column5; } public String getColumn6() { return column6; } public void setColumn6(String column6) { this.column6 = column6; } public String getColumn7() { return column7; } public void setColumn7(String column7) { this.column7 = column7; } public String getColumn8() { return column8; } public void setColumn8(String column8) { this.column8 = column8; } public String getColumn9() { return column9; } public void setColumn9(String column9) { this.column9 = column9; } public String getColumn10() { return column10; } public void setColumn10(String column10) { this.column10 = column10; } }