본문 바로가기
CLASS/DATABASE

#5-1 / Oracle - Excel 파일 data입력 , 파일로 저장

by hingu 2024. 9. 5.

엑셀 데이터 .xlsx 파일로 내보냄 

 

 

요렇게 table 생성

create sequence cmsno start with 1 increment by 1 MAXVALUE 999999;

create table cms(
cidx number(6) not null,
cid nvarchar2(10) not null,
cname nchar(10) not null,
cmanager nchar(20) not null,
cate nchar(40) constraint cate check('취업','성적','직장','대인'),
cmsdate date not null,
cprocess nchar(1) not null,
ctext nclob null,
indate timestamp not null,
primary key(cidx)
);

//날짜 - 형태로 변경
alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';
alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

 

 

  👀 Excel 파일 업로드 및 Data(Oracle) 입력  

- cms_dto.java

package kr.co.sen;

import org.springframework.stereotype.Repository;

import lombok.Data;

@Data
@Repository("cms_dto")
public class cms_dto {
	Integer cidx;
	String cid,cname,cmanager,cate,cmsdate,cprocess,ctext,indate;
}

 

 

- excel_upload.jsp

<body>
	<form id="frm" method="post" action="./excel_uploadok.do" enctype="multipart/form-data">
		엑셀파일 : <input type="file" name="mfile"><br>
		<input type="button" value="전송" onclick="excelok()">
	</form>
</body>

<script>
	function excelok(){
		frm.submit();
	}
</script>

 

 

- mapper.xml

<insert id="cmsdata">
    <if test="ctext==null">
    insert into cms values (cmsno.nextval,#{cid},#{cname},#{cmanager},#{cate},#{cmsdate},#{cprocess},null,sysdate)
    </if>
    <if test="ctext!=null">
    insert into cms values (cmsno.nextval,#{cid},#{cname},#{cmanager},#{cate},#{cmsdate},#{cprocess},#{ctext},sysdate)
    </if>
</insert>

data type이 not null이 아닌 null임

oracle은 알아서 안들어감 => 조건문으로 null일경우 처리해줘야함 ~

 

 

- member_repo (interface)

package kr.co.sen;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface member_repo {
	//CMS XLSX 데이터 입력
	int cmsdata(cms_dto cms_dto);
}

 

 

- member_service (interface)

package kr.co.sen;

import java.util.List;

public interface member_service {

	//xlsx 파일을 이용한 데이터 입력 파트
	public int xlsx_cms(cms_dto cms_dto);
}

 

-memberServiceImpl.java 

package kr.co.sen;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class memberServiceImpl implements member_service{
	@Autowired
	private member_repo member_repo;
	
	@Override
	public int xlsx_cms(cms_dto cms_dto) { //xlsx 데이터 처리
		int result = member_repo.cmsdata(cms_dto);
		return result;
	}
}

 

- Controller

PrintWriter pw = null;

@Resource(name="member_dto")
member_dto mdto;

@Autowired
private member_service member_service;

@Resource(name="cms_dto")
cms_dto cms_dto;


@PostMapping("/excel_uploadok.do")
public String excel_upload(
        @RequestPart(value="mfile",required=false) MultipartFile mfile,
        ServletResponse res
        ) throws Exception{
    res.setContentType("text/html;charset=utf-8");

    //data 입력 성공 여부
    int sign = 1;

    //XLSX, XLS를 읽어들임
    InputStream is = mfile.getInputStream();
    Workbook workbook = WorkbookFactory.create(is);

    //EXCEL 시트를 가져와서 핸들링함
    Sheet sheet = workbook.getSheetAt(0);

    //전체 rows 갯수를 파악
    Iterator<Row> rows = sheet.iterator();
    while(rows.hasNext()) {
        Row row = rows.next(); //값이 있는 전체 을 읽어서 확인
        Iterator<Cell> cell = row.cellIterator(); //열을 배열 형태로 읽음

        if(row.getRowNum() > 0) { //첫번째 행 이상부터 열을 읽기 위함 
            while(cell.hasNext()) { //해당 열에대한 데이터가 true일 경우 반복

                Cell ce = cell.next(); //열에 있는 값을 가져옴
                int cellno = ce.getColumnIndex(); //열 번호 확인

                switch(cellno) {
                case 0: //아이디
                    cms_dto.setCid(ce.toString());
                break;
                case 1: //상담 신청자
                    cms_dto.setCname(ce.toString());
                break;
                case 2: //상담 담당자
                    cms_dto.setCmanager(ce.toString());
                break;
                case 3: //상담내용
                    cms_dto.setCate(ce.toString());
                break;
                case 4: //상담일시
                    SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd");

                    //getDateCellValue : 해당 Cell값을 날짜로 변환해서 가져오는 클래스
                    cms_dto.setCmsdate(sdf.format(row.getCell(4).getDateCellValue()));
                break;
                case 5: //진행상황
                    cms_dto.setCprocess(ce.toString());
                break;
                case 6: //상담처리내용
                    cms_dto.setCtext(ce.getStringCellValue());
                break;
                }
            }

            //Oracle DB저장
            try {
                sign= sign&member_service.xlsx_cms(cms_dto);					
            }catch(Exception e) {
                System.out.println(e);
            }

        }
    }
    if(sign > 0) {
        System.out.println("올바르게 XLSX 데이터가 저장 되었습니다.");
    }else {
        System.out.println("database 쿼리오류 발생");
    }

    return null;
}

 

한글깨질시 maven repository 

Apache POI / Apache POI API Based On OPC and OOXML Schemas 이거 두개

//추가

implementation 'org.apache.poi:poi:4.1.2'
implementation 'org.apache.poi:poi-ooxml:4.1.2'

 

 

 

우왕

 

 

 

  👀 excel 다운로드  

- jsp

<body>
	<form id="frm2" method="get" action="./excel_downok.do">
		<input type="button" onclick="exceldown()" value="xlsx 다운로드">
	</form>
</body>

<script>
	function exceldown(){
		//원래는 ajax로 해야함
		frm2.submit();
	}
</script>

 

- mapper.xml

<select id="cmslist" resultType="kr.co.sen.cms_dto">
select * from cms order by cidx desc
</select>

 

- member_repo.java (interface)

package kr.co.sen;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface member_repo {
	//CMS Data 전제 list - where ~ 을 안써서 따로 전달할 인자값없음
	List<cms_dto> cmslist();
}

 

- member_service.java (interface) 

package kr.co.sen;

public interface member_service {
	//데이터 전체 cms 메소드
	public List<cms_dto> cms_list();
}

 

 

- memberServiceImpl.java 

package kr.co.sen;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class memberServiceImpl implements member_service{
	@Autowired
	private member_repo member_repo;
    
	@Override
	public List<cms_dto> cms_list() { //cms에 저장된 모든 데이터 이관
		List<cms_dto> result = member_repo.cmslist();
		
		return result;
	}
}

 

 

- Controller 

PrintWriter pw = null;

@Resource(name="member_dto")
member_dto mdto;

@Autowired
private member_service member_service;

@Resource(name="cms_dto")
cms_dto cms_dto;

@GetMapping("/excel_downok.do")
public void excel_down(HttpServletResponse res,HttpServletRequest req) {

    //Oracle db에 저장된 전체 데이터를 배열화 함
    List<cms_dto> cmslist = member_service.cms_list();
    try {
        //worksheet을 생성하는 클래스
        Workbook wb = new HSSFWorkbook();

        //sheet을 생성하는 타이틀
        Sheet sheet = wb.createSheet("cms 상담 데이터");

        //목차파트
        //row값에 대한 데이터 추가 형태
        int rows=0;
        Row row = sheet.createRow(rows);

        //node 배열에 맞게 순서대로 데이터를 추가하는 방식
        row.createCell(0).setCellValue("번호");
        row.createCell(1).setCellValue("상담 신청자");
        row.createCell(2).setCellValue("상담 담당자");
        row.createCell(3).setCellValue("상담 내용");
        row.createCell(4).setCellValue("상담 날짜");
        row.createCell(5).setCellValue("상담 진행 상황");
        row.createCell(6).setCellValue("상담 처리내용");

        //데이터 리스트 파트
        int rows2 = 1;
        for(cms_dto cd : cmslist) {
            Row row2 = sheet.createRow(rows2++);
            row2.createCell(0).setCellValue(rows2-1);
            row2.createCell(1).setCellValue(cd.getCname());
            row2.createCell(2).setCellValue(cd.getCmanager());
            row2.createCell(3).setCellValue(cd.getCate());
            row2.createCell(4).setCellValue(cd.getCmsdate());
            row2.createCell(5).setCellValue(cd.getCprocess());
            row2.createCell(6).setCellValue(cd.getCtext());
        }

        //excel파일로 변경하여 해당 filename을 통해서 다운로드받을 수 있는 형태
        res.setContentType("ms-vnd/excel");
        //res.setCharacterEncoding("utf-8");
        res.setHeader("content-disposition", "attachment;filename=cms_datalist.xls");

        wb.write(res.getOutputStream());
        wb.close();
    }catch(Exception e) {

    }		
}

 

 

엑셀 잘뜸ㅎ

 

 

@GetMapping("/excel_downok.do")
public void excel_down(HttpServletResponse res,HttpServletRequest req) {
    try {
        //worksheet을 생성하는 클래스
        Workbook wb = new HSSFWorkbook();

        //sheet을 생성하는 타이틀
        Sheet sheet = wb.createSheet("cms 상담 데이터");

        //row값에 대한 데이터 추가 형태
        int rows=0;
        Row row = sheet.createRow(rows++);

        //node 배열에 맞게 순서대로 데이터를 추가하는 방식
        row.createCell(0).setCellValue("홍길동");
        row.createCell(1).setCellValue("강감찬");
        row.createCell(2).setCellValue("이순신");

        //excel파일로 변경하여 해당 filename을 통해서 다운로드받을 수 있는 형태
        res.setContentType("ms-vnd/excel");
        //res.setCharacterEncoding("utf-8");
        
        //보안때문에 xls 로 저장해야함
        res.setHeader("content-disposition", "attachment;filename=cms_datalist.xls");

        wb.write(res.getOutputStream());
        wb.close();
    }catch(Exception e) {

    }		
}

=> 이건 연습

 

 

 

'CLASS > DATABASE' 카테고리의 다른 글

#6-2/ Oracle - sequence reset, ROW_NUMBER(),view+join  (0) 2024.09.06
#6-1 / Oracle - data 미러링 , table 제작 상세내역 확인 , trigger  (2) 2024.09.06
#4-4 / Oracle + Mybatis 로그인  (0) 2024.09.04
#4-2 / Oracle 실제 테이블에 commit  (0) 2024.09.04
#4-1 / Oracle sts 연결  (0) 2024.09.03

관련글

티스토리툴바