Java 특정 엑셀 양식 쓰기

안녕하세요.
jave나 .net이나 프로젝트를 하다보면 꼭 있어야할 기능 중 하나가 엑셀 다운로드 입니다.
그리드의 정보를 다운로드 한다던지, 서버에 있는 특정 양식의 엑셀문서를 읽어 DB의 정보를 알맞게 쓰고, 다운로드 한다던지...

그럼 이런 작업들은 어떻게 할까요?
하여 오늘은 Java 특정 엑셀 양식 쓰기에 대해서 알아보겠습니다.




@RequestMapping(value = "/P010001L/getBillingDetailMrsExcelDown/action.do", method = RequestMethod.POST)
    public void getBillingDetailMrsExcelDown(HttpServletRequest request, HttpServletResponse response, Locale locale, Model model) throws Exception
    {
        String fileName = "다운로드엑셀파일명.xlsx";
       
        String userId = "";
        HttpSession session = request.getSession();
        userId = (String) session.getAttribute("SESSION_USER_ID");
       
        // 프로젝트 안에 엑셀 양식을 넣어둠
        String path = this.getClass().getClassLoader().getResource("").getPath();
        String fullPath = URLDecoder.decode(path, "UTF-8");
        String pathArr[] = fullPath.split("/WEB-INF/classes/");
        System.out.println(fullPath);
        System.out.println(pathArr[0]);
        fullPath = pathArr[0] + "/WEB-INF/views/SparePartsExcelForm";
        String reponsePath = "";
        // to read a file from webcontent
        String baseFolder = new File(fullPath).getPath() + File.separatorChar;
        String baseFileName = "SPAREPARTS_MRS.xlsx";
        String outFileName = "SPAREPARTS_MRS_" + userId + ".xlsx";
        reponsePath = baseFolder + outFileName; // 복사될 파일 경로
        // 기본 양식 엑셀 파일을 복사한다.(덮어쓰기)
        fileCopy(baseFolder+baseFileName, reponsePath);
       
        File file = new File(reponsePath);
       
        InputStream workFileInputStream = new FileInputStream(reponsePath);
        Workbook wb = WorkbookFactory.create(workFileInputStream);
        Sheet sheet = wb.getSheetAt(0);
        // int num = sheet.getLastRowNum();
        // Row row = sheet.createRow(++num);
        // 엑셀에 내용 입력하기
        JSONArray jsonDataArray = new JSONArray(request.getParameter("param").toString());
        int iLength = jsonDataArray.length();
        for(int rownum = 0; rownum <iLength; rownum++)
        {
            Row row = sheet.createRow(rownum + 4);
           
            JSONObject jsonObject = jsonDataArray.getJSONObject(rownum);
            Map<String, Object> map = JsonUtil.JsonToMap(jsonObject.toString());
           
            String value = "";
           
            value = ("null".equals(String.valueOf(map.get("listNo"))) ? "" : String.valueOf(map.get("listNo")));
            row.createCell(0).setCellValue(value); // cell1
         
            value = ("null".equals(String.valueOf(map.get("listPreqNo"))) ? "" : String.valueOf(map.get("listPreqNo")));
            row.createCell(1).setCellValue(value); // cell2
         
            value = ("null".equals(String.valueOf(map.get("listBillingDate"))) ? "" : String.valueOf(map.get("listBillingDate")));
            row.createCell(2).setCellValue(value); // cell3
        
            value = ("null".equals(String.valueOf(map.get("listBillingGubun"))) ? "" : String.valueOf(map.get("listBillingGubun")));
            row.createCell(3).setCellValue(value); // cell4
         
            value = ("null".equals(String.valueOf(map.get("listSiteCd"))) ? "" : String.valueOf(map.get("listSiteCd")));
            row.createCell(4).setCellValue(value); // cell5
         
            value = ("null".equals(String.valueOf(map.get("listSaNo"))) ? "" : String.valueOf(map.get("listSaNo")));
            row.createCell(5).setCellValue(value); // cell6
         
            value = ("null".equals(String.valueOf(map.get("listEqpName"))) ? "" : String.valueOf(map.get("listEqpName")));
            row.createCell(6).setCellValue(value); // cell7

            value = ("null".equals(String.valueOf(map.get("listCapaExcel"))) ? "" : String.valueOf(map.get("listCapaExcel")));
            row.createCell(7).setCellValue(value); // cell8
         
            value = ("null".equals(String.valueOf(map.get("listCapaUnit"))) ? "" : String.valueOf(map.get("listCapaUnit")));
            row.createCell(8).setCellValue(value); // cell9

            value = ("null".equals(String.valueOf(map.get("listDetail_Item"))) ? "" : String.valueOf(map.get("listDetail_Item")));
            row.createCell(9).setCellValue(value); // cell10

            value = ("null".equals(String.valueOf(map.get("listSpecification"))) ? "" : String.valueOf(map.get("listSpecification")));
            row.createCell(10).setCellValue(value); // cell11
         
            value = ("null".equals(String.valueOf(map.get("listSpecificationUnit"))) ? "" : String.valueOf(map.get("listSpecificationUnit")));
            row.createCell(11).setCellValue(value); // cell12

            value = ("null".equals(String.valueOf(map.get("listPartNo"))) ? "" : String.valueOf(map.get("listPartNo")));
            row.createCell(12).setCellValue(value); // cell13

            value = ("null".equals(String.valueOf(map.get("listMaker"))) ? "" : String.valueOf(map.get("listMaker")));
            row.createCell(13).setCellValue(value); // cell14

            value = ("null".equals(String.valueOf(map.get("listQty"))) ? "" : String.valueOf(map.get("listQty")));
            row.createCell(14).setCellValue(value); // cell15

            value = ("null".equals(String.valueOf(map.get("listUnit"))) ? "" : String.valueOf(map.get("listUnit")));
            row.createCell(15).setCellValue(value); // cell16

            value = ("null".equals(String.valueOf(map.get("listWbsCode"))) ? "" : String.valueOf(map.get("listWbsCode")));
            row.createCell(16).setCellValue(value); // cell17

            value = ("null".equals(String.valueOf(map.get("listRequsitionDate"))) ? "" : String.valueOf(map.get("listRequsitionDate")));
            row.createCell(17).setCellValue(value); // cell18

            value = ("null".equals(String.valueOf(map.get("listRemark"))) ? "" : String.valueOf(map.get("listRemark")));
            row.createCell(18).setCellValue(value); // cell19
        }




 // 엑셀에 쓰기
        FileOutputStream fileOut = new FileOutputStream(reponsePath);
        wb.write(fileOut);
        fileOut.close();
       
        FileInputStream fileInputStream=null;
        ServletOutputStream servletOutputStream=null;
       
        try
        {
            String downName = null;
            String browser = request.getHeader("User-Agent");
           
            //파일 인코딩
            if(browser.contains("MSIE") || browser.contains("Trident") || browser.contains("Chrome")){            
                downName = URLEncoder.encode(fileName,"UTF-8").replaceAll("\\+", "%20");
            } else {              
                downName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            }
            response.setHeader("Content-Disposition","attachment;filename=\"" + downName+"\"");            
            response.setContentType("application/octer-stream");
            response.setHeader("Content-Transfer-Encoding", "binary;");
            fileInputStream = new FileInputStream(file);
            servletOutputStream = response.getOutputStream();
            byte b [] = new byte[1024];
            int data = 0;
            while((data=(fileInputStream.read(b, 0, b.length))) != -1)
            {
                servletOutputStream.write(b, 0, data);
            }
            servletOutputStream.flush();
       } catch (Exception e) {
           e.printStackTrace();
       } finally{
           if(servletOutputStream!=null){
               try {
                   servletOutputStream.close();
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }
           if (fileInputStream!=null){
               try {
                   fileInputStream.close();
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }
       }
    }




//파일을 복사하는 메소드
    public static void fileCopy(String inFileName, String outFileName) {
        try {
            FileInputStream fis = new FileInputStream(inFileName);
            FileOutputStream fos = new FileOutputStream(outFileName);
     
            int data = 0;
            while((data=fis.read())!=-1) {
                fos.write(data);
            }
            fis.close();
            fos.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

이상으로 Java 특정 엑셀 양식 쓰기에 대해서 알아보았습니다.