자바 엑셀 생성-암호걸기-다운로드

[ 만들고자 하는 기능 ]

스프링 jsp화면에서 a태그 누르면 js함수타고 

ajax로 controller로 진입해서

엑셀파일 만들고

만들어진 문서에 암호걸고

사용자에게 다운로드 

---

[ 추가한 jar ]

poi-3.16.jar

poi-ooxml-3.16.jar

poi-ooxml-schemas-3.16.jar

commons-collections4-4.1.jar

xmlbeans-2.6.0.jar

 

* 

poi4부터는 자바 1.8이상만 지원

poi3.11~3.x는 자바 1.6이상 필요

poi3.17까지는 자바 1.6지원

나는 자바 1.8이라서 3.16사용하려고함.

---

 

https://akageun.github.io/2018/07/12/poi-excel-password.html

[Apache poi] 엑셀파일에 암호걸기

여기 보고 따라 했을 때 

샘플 엑셀파일 생성 후 암호 잘 걸렸고 다운로드까지까지 됐다.

근데 다운로드가 사용자 PC가 아니고 개발 PC에 돼서.. 

사용자 PC에 다운받는 방법을 찾다가..꼬여버렸다.

 

그래서 든 생각이.. 일단 파일을 만든 후에 암호를 걸고..

생성된 파일을 주소를 js로 넘겨서 .. 사용자가 내려 받아야 하는 순서인가.. 

싶은데..확실하지가 않다.. 

 

https://offbyone.tistory.com/250

POI를 사용하여 엑셀 출력하기

https://bamdule.tistory.com/232

[JAVA] POI Excel 다운로드 기능 만들기

 

 

빠르게 기능만 만들려고 한거라 사실 각각이 어떻게 작동하는지 잘 모르겠다.

엑셀파일을 만드는 방법은 

File로 만들 경우 , HttpServletResponse 경우 이렇게 2개가 있는 것 같고..

파일을 다운로드 하는 방법도.. 여기저서 본게 4개 쯤 되는 것 같다..

https://ddil-ddil.tistory.com/7

파일 다운로드

https://fishcoding.tistory.com/56

[spring] 파일다운로드 구현

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=hyoun1202&logNo=220245067954 

[JAVA] Apache POI를 사용한 Excel 파일 읽기(대용량 Excel 파일 읽기 포함)

Apache POI를 사용해서 Excel 파일을 읽어들이는 방법(4)
1. FileInputStream() 객체를 사용해서 읽어들이는 방법
2. InputStreams 대신 Files 객체를 사용해서 읽어들이는 방법
3. OPCPackage를 사용해서 읽어들이는 방법, Excel 2007 이상인 경우에만 적용가능
4. SAX를 사용해서 대용량 Excel 파일 처리하기

---

Apache POI를 사용해서 Excel 파일을 읽어들이는 방법에는 4가지 방법이 있다.

 

1번째 방법 - FileInputStream() 객체를 사용해서 읽어들이는 방법

//Excel 2007 이상인 경우 FileInputStream file = new FileInputStream(new File("C:/temp/test.xlsx")); XSSFWorkbook workbook = new XSSFWorkbook(file); //Excel 97 ~ 2003 버전인 경우 FileInputStream file = new FileInputStream(new File("C:/temp/test.xls")); HSSFWorkbook workbook_old = new HSSFWorkbook(file);

 

2번째 방법 - InputStreams 대신 Files 객체를 사용해서 읽어들이는 방법, 1번째 방식보다 메모리 사용이 적은 장점이 있음

Workbook workbook = WorkbookFactory.create(new File("C:/temp/test.xlsx")); //Excel 97 ~ 2007 모두 가능

 

 

3번째 방법 - OPCPackage를 사용해서 읽어들이는 방법, Excel 2007 이상인 경우에만 적용가능

OPCPackage opcPackage = OPCPackage.open(new File("C:/temp/test.xlsx")); XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);

 

일단 상기 3가지 방법은 Excel 파일을 읽어들이는 부분의 코드만 다르고 이후 Excel 파일 내 각 Sheet에 저장되어 있는 cell값들을 읽어

들이는 코드는 동일하다. 다음은 위 3가지 방법으로 Excel 파일을 읽고 쓰는 전체 소스 코드이다.

package sample.file.excel; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.Iterator; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; 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.ss.usermodel.WorkbookFactory; public class ExcelReadExam {  /*   * Author : 황철연   */  public static void main(String[] args) {      //String excelReadPath = "C:/temp/excelread/ExcelReadExam.xls";   String excelReadPath = "C:/temp/excelread/ExcelReadExam.xlsx";   //String excelWritePath = "C:/temp/excelwrite/ExcelWrieExam.xls";   String excelWritePath = "C:/temp/excelwrite/ExcelReadResult.xlsx";      try {     /*** CASE-1 : Get the workbook instance for XLS file - 구 방식 ***/     /*     FileInputStream file = new FileInputStream(new File(excelReadPath));     //HSSFWorkbook workbook = new HSSFWorkbook(file); //Excel 97 ~ 2003 버전인 경우      XSSFWorkbook workbook = new XSSFWorkbook(file); //Excel 2007 이상인 경우    */            /*** CASE-2 : Files vs InputStreams - InputStreams 대신 Files 객체 사용방법(Excel 97 ~ 2007 모두 가능)             "When opening a workbook, either a .xls HSSFWorkbook, or a .xlsx XSSFWorkbook,             the Workbook can be loaded from either a File or an InputStream.              Using a File object allows for lower memory consumption, while an InputStream             requires more memory as it has to buffer the whole file."    ***/    Workbook workbook = WorkbookFactory.create(new File(excelReadPath)); //Excel 97 ~ 2007 모두 가능    // 또는 아래와 같이 사용해도 무방    /*    XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new File(excelReadPath)); //Excel 2007 이상인 경우    */           /*** CASE-3  : OPCPackage 사용(Excel 2007 이상인 경우) ***/    /*    OPCPackage opcPackage = OPCPackage.open(new File(excelReadPath));    XSSFWorkbook workbook = new XSSFWorkbook(opcPackage); //Excel 2007 이상인 경우    */        /*  [참고]     *  SXSSFWorkBook 객체는 'Write Only' 이므로 Excel파일 읽기에는 사용불가     *  SXSSFWorkBook is 'Write Only', it doesn't support reading, Excel 2007 이상이며, 대용량 Excel 처리에 적합     */        //Get first sheet from the workbook    int sheetNum = workbook.getNumberOfSheets();    for (int i=0; i < sheetNum; i++) {          Sheet sheet = workbook.getSheetAt(i);          System.out.print("Sheet Name : " + sheet.getSheetName() + "\r\n");                        /*         int rows = sheet.getPhysicalNumberOfRows();         System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s).");          for (int r = 0; r < rows; r++) {               ...          }         */           //또는 아래와 같이 사용          Iterator<Row> rowIterator = sheet.iterator(); //Iterate through each rows from first sheet          while(rowIterator.hasNext()) {              Row row = rowIterator.next();               /*              int cells = row.getPhysicalNumberOfCells();              System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s).");              for (int c = 0; c < cells; c++) {                 ...              }              */              //또는 아래와 같이 사용              Iterator<Cell> cellIterator = row.cellIterator(); //For each row, iterate through each columns              while(cellIterator.hasNext()) {                                         Cell cell = cellIterator.next();                                         switch(cell.getCellType()) {                      case Cell.CELL_TYPE_BOOLEAN:                           System.out.print(cell.getBooleanCellValue() + "\t\t");                            break;                      case Cell.CELL_TYPE_NUMERIC:                           System.out.print(cell.getNumericCellValue() + "\t\t");                           break;                      case Cell.CELL_TYPE_STRING:                           System.out.print(cell.getStringCellValue() + "\t\t");                           break;                      case Cell.CELL_TYPE_FORMULA :                           System.out.print(cell.getCellFormula() + "\t\t");                           break;                      }                  }                  System.out.println("");              }             }                         //file.close(); //CASE-1 방법에서만 사용             FileOutputStream out = new FileOutputStream(new File(excelWritePath));             workbook.write(out);             out.close();             //opcPackage.close(); //CASE-3 방법에서만 사용                      } catch (FileNotFoundException e) {             e.printStackTrace();         } catch (IOException e) {             e.printStackTrace();         } catch (InvalidFormatException e) {    e.printStackTrace();    }  } }

 

 

그러나 위 3가지 방법 모두 읽어들이는 Excel 파일의 크기가 수십Mbyte 이상되는 Large File일 경우 OutOfMemory 오류가

발생한다. 그래서 Apache POI 프로젝트에서 SAX를 이용한 Large Excel File Read 방법을 제공하고 있다. 자세한 사항은

http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api 에 소개되어 있는 'XSSF and SAX (Event API) ' 를 참고하기 바란다.

 

4번째 방법 - SAX를 사용해서 대용량 Excel 파일 처리하기(원본 코드를 필자가 약간 수정했으며, 변경된 부분은 '변경' 이라는

주석과 '추가시작', '추가끝' 이라는 주석을 달아놓았다. ) 

package sample.file.excel; import java.io.File; import java.io.InputStream; import java.util.Iterator; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.xml.sax.Attributes; import org.xml.sax.ContentHandler; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.DefaultHandler; import org.xml.sax.helpers.XMLReaderFactory; /**  * XSSF and SAX (Event API)  */ public class FromHowTo {   //public void processOneSheet(String filename) throws Exception {   public void processOneSheet(File filename) throws Exception { //변경      OPCPackage pkg = OPCPackage.open(filename);      XSSFReader r = new XSSFReader( pkg );      SharedStringsTable sst = r.getSharedStringsTable();      XMLReader parser = fetchSheetParser(sst);         // rId2 found by processing the Workbook      // Seems to either be rId# or rSheet#      InputStream sheetStream = r.getSheet("rId2");      InputSource sheetSource = new InputSource(sheetStream);      parser.parse(sheetSource);      sheetStream.close();   }   //public void processAllSheets(String filename) throws Exception {   public void processAllSheets(File filename) throws Exception { //변경        OPCPackage pkg = OPCPackage.open(filename);        //추가시작 -------------------------------------     XSSFWorkbook workbook = new XSSFWorkbook(pkg);     //추가끝---------------------------------------     XSSFReader r = new XSSFReader( pkg );     SharedStringsTable sst = r.getSharedStringsTable();        XMLReader parser = fetchSheetParser(sst);     //추가시작 -------------------------------------     int i = 0;     String sheetName;     //추가끝---------------------------------------     InputStream sheetStream;     InputSource sheetSource;     Iterator<InputStream> sheets = r.getSheetsData();     while(sheets.hasNext()) {         //추가시작 -------------------------------------        Sheet wbSheet = workbook.getSheetAt(i);        sheetName = wbSheet.getSheetName();        //추가끝---------------------------------------        System.out.println("Processing new sheet:[" + sheetName + "]\n");        sheetStream = sheets.next();        sheetSource = new InputSource(sheetStream);        parser.parse(sheetSource);        sheetStream.close();        System.out.println("");           //추가시작 -------------------------------------        i++;        //추가끝---------------------------------------     }   }   public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {     XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");     ContentHandler handler = new SheetHandler(sst);     parser.setContentHandler(handler);     return parser;   }   /**   * See org.xml.sax.helpers.DefaultHandler javadocs   */   private static class SheetHandler extends DefaultHandler {     private SharedStringsTable sst;     private String lastContents;     private boolean nextIsString;        private SheetHandler(SharedStringsTable sst) {       this.sst = sst;     }       public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {      // c => cell      if(name.equals("c")) {         // Print the cell reference         System.out.print(attributes.getValue("r") + " - ");         // Figure out if the value is an index in the SST         String cellType = attributes.getValue("t");         if(cellType != null && cellType.equals("s")) {            nextIsString = true;         } else {            nextIsString = false;         }      }      // Clear contents cache      lastContents = "";   }      public void endElement(String uri, String localName, String name) throws SAXException {     // Process the last contents as required.     // Do now, as characters() may be called more than once     if(nextIsString) {       int idx = Integer.parseInt(lastContents);       lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();       nextIsString = false;     }     // v => contents of a cell     // Output after we've seen the string contents     if(name.equals("v")) {        System.out.println(lastContents);     }   }      public void characters(char[] ch, int start, int length)  throws SAXException {     lastContents += new String(ch, start, length);   }  }    public static void main(String[] args) throws Exception {      //추가시작 -------------------------------------   String filename = "C:/temp/ExcelReadExam.xlsx";   //String filename = "C:/temp/LargeExcelfile.xlsx";   File file = new File(filename);    //추가끝---------------------------------------      FromHowTo howto = new FromHowTo();   //howto.processOneSheet(args[0]);   //howto.processAllSheets(args[0]);   //howto.processOneSheet(file); //변경   howto.processAllSheets(file); //변경  } }

 

상기 코드들은 JAVA 6(jdk1.6) 환경에서 테스트됨.( jdk1.4에서는 동작하지 않습니다.)

 

 

---

 

 

 

 

---

일단 .. 작동은 한다..

• xls 확장자를 가진 엑셀 파일 -> HSSFWorkbook
• xlsx 확장자를 가진 엑셀 파일 -> XSSFWorkbook

//jsp에 버튼
<a onclick="fn_downloadExcel();">엑셀다운로드</a>


//js
function fn_downloadExcel(){
	//방법1
	window.location="/excelDown";
    
    //방법2
    $.ajax({
    	type : "GET"
    	, cache : false
    	, success : function(){
    		window.location = '/excelDown';
    	}
    });
    
    
    //이렇게 되면 a태그에서 바로 이동해도 될 것 같은데 
    //함수로 만들어놔야 ..이것저것 붙이기 좋으니까.. 함수로 진행.
    //ajax는 써야 할지 ..고민중
}

 

엑셀파일을 만드는 방법도 2가지?

@RequestMapping(value="/excelDown",method=RequestMethod.GET)
public boolean excelDown(HttpServletRequest request,HttpServletResponse response) {
	
	try{ // 예외처리는 필요에 따라 하면 된다.
	
		//1. 테스트용 엑셀파일 생성################################################################
		//원하는 엑셀파일 만드는 건 다시 찾아야함..정렬 색 데이터..
		//생성 순서 : workbook -> Sheet -> Row -> Cell
		
		Workbook workbook = new XSSFWorkbook(); //workbook 생성 (ms2007이상 엑셀파일 생성이라고 이해함 )
		Sheet sheet = workbook.createSheet("시트1이름");
		Row titleRow = sheet,createRow(0); //row생성 0부터
		Cell titleCell = titleRow.createCell(0); //cell추가
		titleCell.setCellValue("값1");
		
		
		//2. 엑셀파일 만들기 #####################################################################
		//파일이 생성될 경로 및 파일명 //다운로드 후 삭제할거다.
		String fileLocation = "D:\\Users\\download\\tempExcel.xlsx"; 
		//└ 윈도우 서버라..c밑에 다운로드 폴더에 임시파일경로 두려고 했는데 권한 때문인지 파일생성이 안됐음.
		
		ByteArrayOutputStream fileOut = new ByteArrayOutputStream(); //임시 데이터 저장을 위한 곳
		FileOutputStream fileOutputStream = new FileOutputStream(fileLocation);
		workbook.write(fileOut); //엑셀파일에 한번 작성
		
		InputStream filein = new ByteArrayInputStream(fileOut.toByteArray());
		OPCPackage opc = OPCPackage.open(filein);
		POIFSFilesSystem filesSystem = new POIFSFilesSystem();
		
		//3.엑셀파일 암호걸기 #######################################################################
		String password = "password1234";
		EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.agile);
		Encryptor encryptor = encryptionInfo.getEncryptor();
		encryptor.confirmPassword(password);
		
		opc.save(encryptor.getDataStream(fileSystem));
		fileSystem.writeFilesystem(fileOutputStream);
		
		workbook.write(fileOutputStream);//fileLocation에 작성한 경로에 파일 생성됨.
		workbook.close(); 
		
		
		//4.엑셀파일 다운로드 ######################################################################
		//조건 세팅
		response.setContentType("application/octet-stream");
		response.setHeader("Content-Disposition","attachment;fileName=\""+URLEncoder.encode("사용자에게 보여질 파일명.xlsx","UTF-8")+"\";");
		response.setHeader("Content-Transfer-Encoding","binary");
		//위에서 만든 파일 읽어와서 사용자 PC에 출력
		byte[] fileByte = FileUtiles.readFileToByteArray(new File(fileLocation));
		response.getOutputStream().write(fileByte);
		response.getOutputStream().flush();
		response.getOutputStream.close();
		
		//5.임시 엑셀 파일 삭제 ######################################################################
		File deleteTempFile = new File(fileLocation);
		deleteTempFile.delete();
		

	}catch(IOException e){
	
	}..
	
}

 

 

- 복사한게 아니라 친거라 오타 있을 수 있음

- 아직 완전히 정리된 코드는 아니나..작동은 함.

- 지금은 순서가 엑셀 파일을 일단 서버에 만든 후에

해당 경로에서 파일을 읽어와서 사용자 PC에 출력하고

서버에 생성한 엑셀파일을 지우게 했는데..

일반적으로 어떤 순서로 매번 데이터가 바뀌는 엑셀파일을 사용자가 내려받는 건지..흐름을 알고 싶다.

- 엑셀파일에 암호거는게 목적이었으니까..이 부분이 가장 중요한데..

String password = "password1234";
EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.agile);
Encryptor encryptor = encryptionInfo.getEncryptor();
encryptor.confirmPassword(password);

opc.save(encryptor.getDataStream(fileSystem));

다른 방식으로 엑셀파일을 만든다면 어떻게 적용해야 하는건지..그것도 알아봐야 한다.