Java tutorial
package com.nc.common.utils; import java.io.File; import java.io.FileInputStream; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.nc.common.exception.NCException; /** * ETRI Distributed Resource/Mediation System for new re-generation Energy Exchange * * Copyright [2016] ETRI. All rights reserved. * * This is a proprietary software of ETRI, and you may not use this file except in * compliance with license agreement with ETRI. Any redistribution or use of this * software, with or without modification shall be strictly prohibited without prior written * approval of ETRI, and the copyright notice above does not evidence any actual or * intended publication of such software. * * com.nc.common.utils : ExcelUtil.java * @author creme55 * @since 2016. 10. 20. * @version 1.0 * @see * @Copyright [2016] By ETRI. All rights reserved. * * <pre> * << ?(Modification Information) >> * ? ? * ------------- ----------- --------------------------------- * 2016. 10. 20. creme55 ? (Excel file management) * * </pre> **/ public class ExcelUtil { private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class); /** * <pre> * 1. : POI Util * 2. : Excel ? ?(*.xls, *.xlsx ? ) * </pre> * * @method Name : readExcel * @param strFullFilePath, serviceType * @return List<Map<String, Object>> * @throws Exception * */ @SuppressWarnings("deprecation") public static List<Map<String, Object>> readExcel(String strFullFilePath, String serviceType) throws Exception { String tmpFile = strFullFilePath; File wbfile = new File(tmpFile); Workbook wb = null; FileInputStream file = null; List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); Map<String, Object> map = null; try { file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); Version change */ /* Sheet ? , ? ?? */ /* for (int sheetIdx=0; sheetIdx<wb.getNumberOfSheets(); sheetIdx++) { */ for (int sheetIdx = 0; sheetIdx < 1; sheetIdx++) { /* 1 ? */ Sheet sheet = wb.getSheetAt(sheetIdx); /* ?? ? ?, */ /* row ? */ int cellCount = 0; for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); cellCount = row.getLastCellNum(); map = new HashMap<String, Object>(); if (row != null) { // cell ? for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) { Cell cell = row.getCell(cellIdx); if (cell != null) { int cellType = cell.getCellType(); String value = ""; // WBS ? ? ?? if (serviceType.equals("order")) { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); value = formatter.format(cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); } break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: ; } } else { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? value = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: } } map.put("colName" + cellIdx, value); } else { map.put("colName" + cellIdx, ""); } } list.add(map); } } } } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } return list; } /** * <pre> * 1. : POI Util * 2. : Excel ? ?(*.xls, *.xlsx ? ), sheet ? * </pre> * * @method Name : readExcelMulti * @param strFullFilePath, serviceType, sheetNo * @return * @throws * */ @SuppressWarnings("deprecation") public static List<LinkedHashMap<String, Object>> readExcelMulti(String strFullFilePath, String serviceType, int sheetNo) throws Exception { String tmpFile = strFullFilePath; File wbfile = new File(tmpFile); Workbook wb = null; FileInputStream file = null; List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>(); LinkedHashMap<String, Object> map = null; try { if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= file path : {} =", strFullFilePath); log.debug("= tmp file : {} =", tmpFile); log.debug( "=========================================================================================="); } file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */ Sheet sheet = wb.getSheetAt(sheetNo); /* row ? */ int cellCount = 0; for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); cellCount = row.getLastCellNum(); map = new LinkedHashMap<String, Object>(); if (rowIdx == 0) { if (log.isDebugEnabled()) { log.debug( "=================================================================================="); log.debug("= sheet no : {} =", sheetNo); log.debug("= row count : {} =", sheet.getLastRowNum()); log.debug("= col count : {} =", cellCount); log.debug( "=================================================================================="); } } if (row != null) { // cell ? for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) { Cell cell = row.getCell(cellIdx); if (cell != null) { int cellType = cell.getCellType(); String value = ""; switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? value = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: } map.put("item" + String.valueOf(cellIdx), value); } else { map.put("item" + String.valueOf(cellIdx), ""); } } if (log.isDebugEnabled()) { log.debug( "=================================================================================="); log.debug("= map : {} =", map); log.debug( "=================================================================================="); } list.add(map); } } } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } return list; } /** * <pre> * 1. : POI Util * 2. : Excel ? ? (? ?), ? ?? ? * </pre> * * @method Name : readWriteExcel * @param response, codeList, serviceType, templateFile * @return none * @throws Exception * */ public static void readWriteExcel(HttpServletResponse response, List<Map<String, Object>> codeList, String serviceType, String templateFile) throws Exception { OutputStream fileOut = null; /* ? ? */ File wbfile = new File(templateFile); Workbook wb = null; FileInputStream file = null; try { file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */ for (int i = 0; i < wb.getNumberOfSheets(); i++) { wb.removeSheetAt(wb.getNumberOfSheets() - 1); } /* Sheet ?, ? , ? ? ? */ /* ?*/ /* ? : wb = createWorkBook(wb, "sheet Name", "dataList", "header column List");*/ } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readWriteExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } String fileName = serviceType + ".xlsm"; /* ? */ response.setHeader("Content-Disposition", "attachment; filename=" + fileName); /* , to file */ fileOut = response.getOutputStream(); wb.write(fileOut); fileOut.close(); fileOut.flush(); } /** * <pre> * 1. : POI UTIL * 2. : Excel ? ? (? ?) * </pre> * * @method Name : readWriteWbsExcel * @param response, codeList, serviceType, templateFile * @return none * @throws Exception * */ public static void readWriteWbsExcel(HttpServletResponse response, List<Map<String, Object>> codeList, String serviceType, String templateFile) throws Exception { OutputStream fileOut = null; templateFile += ".xlsx"; File wbfile = new File(templateFile); Workbook wb = null; FileInputStream file = null; try { file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* Sheet , ? , ? ? */ /* ?*/ /* ? : wb.removeSheetAt(wb.getSheetIndex("?id")); */ /* Sheet ?, ? , ? ? ? */ /* ?*/ /* ? : wb = createWorkBook(wb, "sheet Name", "dataList", "header column List");*/ } catch (Exception e) { e.printStackTrace(); throw new NCException("ExcelUtil > readWriteExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } String fileName = serviceType + ".xlsx"; /* ? */ response.setHeader("Content-Disposition", "attachment; filename=" + fileName); /* */ fileOut = response.getOutputStream(); wb.write(fileOut); fileOut.close(); fileOut.flush(); } /** * <pre> * 1. : POI UTIL * 2. : ? * </pre> * * @method Name : excelFileDownload * @param response, list, serviceType, colNames * @return none * @throws Exception * */ @SuppressWarnings("resource") public static void excelFileDownload(HttpServletResponse response, List<Map<String, Object>> list, String serviceType, List<Map<String, Object>> colNames) throws Exception { OutputStream fileOut = null; String filename = serviceType + ".xlsx"; String currentDateTime = getCurrentDateNum(); String newFileName = filename + "_" + currentDateTime + ".xls"; /* ? */ response.setHeader("Content-Disposition", "attachment; filename=" + newFileName); Workbook workbook = new HSSFWorkbook(); workbook = createWorkBook(workbook, "Sheet1", list, colNames); /* ? */ /* */ fileOut = response.getOutputStream(); workbook.write(fileOut); fileOut.close(); fileOut.flush(); } /** * <pre> * 1. : POI UTIL * 2. : * </pre> * * @method Name : getCurrentDateNum * @param * @return String * @throws * */ public static String getCurrentDateNum() { String strDate = ""; Calendar aCalendar = Calendar.getInstance(); String year = Integer.toString(aCalendar.get(Calendar.YEAR)); String month = Integer.toString(aCalendar.get(Calendar.MONTH) + 1); String date = Integer.toString(aCalendar.get(Calendar.DATE)); String hour = Integer.toString(aCalendar.get(Calendar.HOUR_OF_DAY)); String min = Integer.toString(aCalendar.get(Calendar.MINUTE)); String sec = Integer.toString(aCalendar.get(Calendar.SECOND)); String mil = Integer.toString(aCalendar.get(Calendar.MILLISECOND)); strDate = year + month + date + hour + min + min + sec + mil; return strDate; } /** * <pre> * 1. : POI UTIL * 2. : POI WORKBOOK ? / String , List<Map<String, Object>> ? , List<Map<String, Object>> * </pre> * * @method Name : createWorkBook * @param workbook, sheetName, list, colNames * @return Workbook * @throws Exception * */ @SuppressWarnings("deprecation") public static Workbook createWorkBook(Workbook workbook, String sheetName, List<Map<String, Object>> list, List<Map<String, Object>> colNames) throws Exception { Row row; Cell cell; /* ? ? START */ /* Workbook workbook = new Workbook(); */ CellStyle titleStyle = workbook.createCellStyle(); CellStyle cellStyle = workbook.createCellStyle(); CellStyle contentStyle = workbook.createCellStyle(); CellStyle contentStyle_2 = workbook.createCellStyle(); /* ? */ /* ? ? */ Font titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 13); titleFont.setFontName("? "); /* ? */ Font colNameFont = workbook.createFont(); colNameFont.setFontHeightInPoints((short) 10); colNameFont.setFontName("? "); /* ? */ Font contentFont = workbook.createFont(); /* ? ? ? */ titleStyle.setFont(titleFont); /* ? / ? ? */ cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); /* ? */ cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//? cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setFont(colNameFont); /* ? / ? */ contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setFont(contentFont); /* ? / ? */ contentStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); contentStyle_2.setAlignment(HSSFCellStyle.ALIGN_LEFT); contentStyle_2.setFont(contentFont); /* ? */ Sheet sheet = workbook.createSheet(sheetName); /* ?? */ int sheet1_row = 0; List<Map<String, Object>> colList = colNames; /* ? START */ row = sheet.createRow(sheet1_row); for (int i = 0; i < colList.size(); i++) { cell = row.createCell(i); cell.setCellValue(String.valueOf(colList.get(i).get("item" + i))); cell.setCellStyle(cellStyle); } sheet1_row++; /* ? END */ for (Map<String, Object> map : list) { row = sheet.createRow(sheet1_row); for (int j = 0; j < colList.size(); j++) { cell = row.createCell(j); cell.setCellStyle(contentStyle); if (null != map.get(colList.get(j).get("item" + j))) { cell.setCellValue((double) map.get(String.valueOf(colList.get(j).get("item" + j)))); } else { cell.setCellValue(""); } /* ? ?? */ sheet.autoSizeColumn((short) j); sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 312); } sheet1_row++; } return workbook; } }