5. DB 데이터을 엑셀 형태로 내보내기

이 내용은 전에 만든 내용에 추가한 것으로 전 글을 봐주세요.

 

2022.03.30 - [개발일지/java spring(egov)] - 4. spring 검색창으로 원하는 항목 찾기

4. spring 검색창으로 원하는 항목 찾기

 

1. jsp 파일에 submit input을 추가합니다.

<form action="/excelDown.do" method="post">
  
   		<input type="submit" value='엑셀 다운로드'>
</form>

 

 

2. Controller에 추가합니다.

package egovframework.example.sample.controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import egovframework.example.sample.service.StockService;
import egovframework.example.sample.vo.SearchVO;
import egovframework.example.sample.vo.StockVO;

@Controller
public class StockController {
    @Autowired
    private StockService stockService;
    
    @RequestMapping(value="/stockList.do")
    public String testListDo(Model model
    		,@RequestParam(required=false,defaultValue="testTitle")String searchType
            ,@RequestParam(required=false)String keyword
            ,@ModelAttribute("search")SearchVO searchVo)throws Exception{
      
    	
    	model.addAttribute("search", searchVo);
    	searchVo.setSearchType(searchType);
    	searchVo.setKeyword(keyword);
        
        model.addAttribute("list", stockService.selectStock(searchVo));


        
        
        return "stockList";
    }
    
    @RequestMapping(value = "/excelDown.do")

    @ResponseBody

    public void excelDown(@ModelAttribute StockVO stockVO, HttpServletResponse response
                                                        , HttpServletRequest request) throws Exception{

       stockService.excelDown(stockVO, response);

    }

}

 

3. DAO에 추가합니다.

package egovframework.example.sample.dao;

import java.util.List;

import egovframework.example.sample.vo.SearchVO;
import egovframework.example.sample.vo.StockVO;

public interface StockDAO {
	  public List<StockVO> selectStock(SearchVO searchVo) throws Exception;

	  
	  public List<StockVO> selectStockList(StockVO stockVo) throws Exception;
}

 

4. DAOImpl에 추가합니다.

package egovframework.example.sample.dao.impl;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import egovframework.example.sample.dao.StockDAO;
import egovframework.example.sample.service.StockMapper;
import egovframework.example.sample.vo.SearchVO;
import egovframework.example.sample.vo.StockVO;

@Repository
public class StockDAOImpl implements StockDAO {
 
    @Autowired
    private SqlSession sqlSession;
    
    @Override
    public List<StockVO> selectStock(SearchVO searchVo) throws Exception {
        StockMapper mapper = sqlSession.getMapper(StockMapper.class);
        return mapper.selectStock(searchVo);
    }
    
    @Override
    public List<StockVO> selectStockList(StockVO stockVo) throws Exception {
        StockMapper mapper = sqlSession.getMapper(StockMapper.class);
        return mapper.selectStockList(stockVo);
    }
 
}

 

5. Service에 추가해줍니다.

package egovframework.example.sample.service;

import java.util.List;

import javax.servlet.http.HttpServletResponse;

import egovframework.example.sample.vo.SearchVO;
import egovframework.example.sample.vo.StockVO;

public interface StockService {
	//검색 리스트
	 public List<StockVO> selectStock(SearchVO searchVo) throws Exception;
	 
	 //엑셀 다운로드
	 void excelDown(StockVO stockVO, HttpServletResponse response) throws Exception;
}

 

6. ServiceImpl에 추가합니다.

package egovframework.example.sample.service.impl;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import egovframework.example.sample.dao.StockDAO;
import egovframework.example.sample.service.StockService;
import egovframework.example.sample.vo.SearchVO;
import egovframework.example.sample.vo.StockVO;

@Service
public class StockServiceImpl implements StockService{
 
    @Autowired
    private StockDAO stockDao;
    
    @Override
    public List<StockVO> selectStock(SearchVO searchVo) throws Exception {
        return stockDao.selectStock(searchVo);
    }
    
    @Override

    public void excelDown(StockVO stockVo, HttpServletResponse response) throws Exception {

  
  //원하는 내용

    List<StockVO> testList = stockDao.selectStockList(stockVo);

  

    try {

       //Excel Down 시작

       Workbook workbook = new HSSFWorkbook();

  

       //시트생성

       Sheet sheet = workbook.createSheet("재고_관리");

  

       //행, 열, 열번호

       Row row = null;

       Cell cell = null;

       int rowNo = 0;

  

       // 테이블 헤더용 스타일

       CellStyle headStyle = workbook.createCellStyle();

       // 가는 경계선을 가집니다.

       headStyle.setBorderTop(BorderStyle.THIN);

       headStyle.setBorderBottom(BorderStyle.THIN);

       headStyle.setBorderLeft(BorderStyle.THIN);

       headStyle.setBorderRight(BorderStyle.THIN);

       // 배경색은 노란색입니다.

       headStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());

       headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

       // 데이터용 경계 스타일 테두리만 지정

       CellStyle bodyStyle = workbook.createCellStyle();

       bodyStyle.setBorderTop(BorderStyle.THIN);

       bodyStyle.setBorderBottom(BorderStyle.THIN);

       bodyStyle.setBorderLeft(BorderStyle.THIN);

       bodyStyle.setBorderRight(BorderStyle.THIN);

  

       // 헤더명 설정
       
       //TODO 아래 내용을 원하는 내용으로 바꿔주세요

       String[] headerArray = {"종류", "코드","바코드","상품이름","용량","박스당 수량","박스수","낱개"};

       row = sheet.createRow(rowNo++);

       for(int i=0; i<headerArray.length; i++) {

       cell = row.createCell(i);

       cell.setCellStyle(headStyle);

       cell.setCellValue(headerArray[i]);

       }

  

       for(StockVO excelData : testList ) {

  

       row = sheet.createRow(rowNo++);

       cell = row.createCell(0);

       cell.setCellStyle(bodyStyle);

       cell.setCellValue(excelData.getKind());

  

       cell = row.createCell(1);

       cell.setCellStyle(bodyStyle);

       cell.setCellValue(excelData.getCode());

       cell = row.createCell(2);

       cell.setCellStyle(bodyStyle);

       cell.setCellValue(excelData.getBarcode());

  

       cell = row.createCell(3);

       cell.setCellStyle(bodyStyle);

       cell.setCellValue(excelData.getPartname());
       
       
       cell = row.createCell(4);

       cell.setCellStyle(bodyStyle);

       cell.setCellValue(excelData.getStd());

  

       cell = row.createCell(5);

       cell.setCellStyle(bodyStyle);

       cell.setCellValue(excelData.getQtybox());

  

       cell = row.createCell(6);

       cell.setCellStyle(bodyStyle);

       cell.setCellValue(excelData.getBox());
       
       
       cell = row.createCell(7);

       cell.setCellStyle(bodyStyle);

       cell.setCellValue(excelData.getStock());

       }

  

       // 컨텐츠 타입과 파일명 지정

       response.setContentType("ms-vnd/excel");

       response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode("***_관리.xls", "UTF8"));

  

       // 엑셀 출력

       workbook.write(response.getOutputStream());

       workbook.close();

       } catch (Exception e) {

       e.printStackTrace();

       }

  

       }
 
}

 

7. mapper 인터페이스에 추가합니다.

package egovframework.example.sample.service;

import java.util.List;

import egovframework.example.sample.vo.SearchVO;
import egovframework.example.sample.vo.StockVO;

public interface StockMapper {
	List<StockVO> selectStock(SearchVO searchVo) throws Exception;

	List<StockVO> selectStockList(StockVO stockVo) throws Exception;
}

 

 

8. mapper.xml 에 추가합니다.(원하는 쿼리)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--아래 경로를 mapper파일로 바꾸어 줍니다.  -->
<mapper namespace="egovframework.example.sample.service.StockMapper">
  <select id="selectStock" resultType="egovframework.example.sample.vo.StockVO">
          SELECT * FROM stock
        <where>
            <if test="searchType=='code' and keyword != null and keyword !=''">
                AND code like CONCAT('%',#{keyword},'%')
            </if>
            <if test="searchType=='barcode' and keyword != null and keyword !=''">
                AND barcode like CONCAT('%',#{keyword},'%')
            </if>
            <if test="searchType=='partname' and keyword != null and keyword !=''">
                AND partname like CONCAT('%',#{keyword},'%')
            </if>
        </where>
        limit 100
        
    </select>
    
     <select id="selectStockList" resultType="egovframework.example.sample.vo.StockVO">
          SELECT * FROM stock
        
        
    </select>
</mapper>

여기까지 하시면 목록이 excel로 저장되는 것을 확인할 수 있습니다.

 

참조 : https://eugene-kim.tistory.com/m/73

[spring] list에 뿌려진 데이터 excel download