xlsx 다운로드

첫째줄 style 적용

xlsx의 한셀에서 최대 크기는 32767이다. 그래서 최대크기를 넘어갈때 해당 셀의 다음줄에 이어서 보여줄 수 있는 작업을 하였다.

수기로 작성하여 오타가 있을 수 있음...

xlsx다운로드

public class xlsxDownload extedns AbstractView {

 @Resource(name="excelDownloadService")

 private ExcelDownloadSercie excelDownloadService;

@Override

protected void renderMergedOutputModel(Map<string, object=""> modelMap, HttpServletRequest request, HttpServletResponse response) throws Exception{

 XSSFWorkbook workBook = new XSSFWorkbook();

String excelName = modelMap.get("excelName").toString();

XSSFSheet workSheet = null;

XSSFRow row = null;

XSSFCEll cell = null;

CellStyle headStyle = workBook.createCellStyle();

headStyle.setFillForegroundColor(HSSFColor.YELLOW.index);

headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

headStyle.setAlignment(CellStyle.ALIGN_CENTER);

headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

 workSheet = workBook.createSheet();

 workBook.setCheetName(0, modelMap.get("sheetName1").toString()); 

 row = workSheet.createRow(0);

 if(modelMap.get("excelColumn") != null){

  String[] excelColumn = (String[])modelMap.get("excelColumn"); // 엑셀항목이 배열에 담겨있다

  if(excelColumn.length &gt; = 0){

   for(int i=0;i<excelcolumn.length-1;i++){ 

   cell= row.createCell(i+1);

   cell.setcellstyle(headstyle);

   cell.setcellvalue(excelcolumn[i+1].tostring());

  }

  XSSFrow rows = null;

   String temp="";

 if(list="" !="null" &&="" list.size()=""> 0){

   int reCell = 0; //출력해야할 cell

   int reNum = 0;

   int rowStart = 1; 뿌려줄 row

   String reStr = ""; //나머지 출력할 문자

   for(int i=0;i<list.size();i++){ 

      int cnt = 0;

     if(!reStr.equals("")){ // 한셀에 32767자가  넘을시 실행

      rows=workSheet.createRow(rowStart);

      cell=rows.createCell(cnt);

      cell.setcellvalue(renum+1);

     if(reStr.length() > 32767){

      cell.setCellValue(reStr.substring(0,32767));

      reStr = reStr.substring(32767, reStr.length());

      i = reNum;

     }else{

      cell.setCellValue(reStr);

      reStr = "";

      i = reNum;

     }

    }else{

     rows = workSheet.createRow(rowStart);

     EgovMap em = (EgovMap)list.get(i);

    for(Iterator iterator = em.keySet().iterator(); iterator.hasNext();){

     String keyName = (String)iterator.next();

     if(cnt == 0){

      cell = rows.createCell(cnt);

       cell.setCellValue(i+1);

       cnt++;

      }

      cell = rows.crateCell(cnt);

      if(em.get(keyName) != null){

       temp = em.get(keyName).toString();

       while(temp.indexOf("&lt;") &gt;= 0 &amp;&amp; temp.indexOf("&lt;") &lt;  temp.indexOf("&gt;")){

         temp = temp.replace(temp.substring(temp.indexOf("&lt;"),temp.indexOf("&gt;"+1),"");

       }

    

       temp = temp.replaceAll("\"","");

       temp = temp.replaceAll("&nbsp;","");

       if(temp.length() &gt; 32767){

        cell.setCellValue(temp.substring(0,32767));

        reStr = temp.substring(32767,temp.length());

        reCell = cnt;

        reNum = i;

       }else{

         cell.setCellValue(temp);

       }

      }

     cnt++;

    }

   }

   rowStart++;

  }

 }

Date date = new Date();

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");

String today = sdf.format(date);

response.setContentType("application/Msexcel");

response.setHeader("Content-Disposition", "ATTachment; Filename="+URLEncoder.encode(excelName,"UTF-8")+"_"+today+".xlsx");

workBook.write(response.getOutputStream());

workBook.close();

}

}

public class xlsxDownload extedns AbstractView {
 @Resource(name="excelDownloadService")
 private ExcelDownloadSercie excelDownloadService;
 
@Override
protected void renderMergedOutputModel(Map<string, object=""> modelMap, HttpServletRequest request, HttpServletResponse response) throws Exception{
 XSSFWorkbook workBook = new XSSFWorkbook();
 
String excelName = modelMap.get("excelName").toString();
 
XSSFSheet workSheet = null;
XSSFRow row = null;
XSSFCEll cell = null;
 
CellStyle headStyle = workBook.createCellStyle();
 
headStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headStyle.setAlignment(CellStyle.ALIGN_CENTER);
headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
 
 workSheet = workBook.createSheet();
 workBook.setCheetName(0, modelMap.get("sheetName1").toString()); 
 row = workSheet.createRow(0);
 
 if(modelMap.get("excelColumn") != null){
  String[] excelColumn = (String[])modelMap.get("excelColumn"); // 엑셀항목이 배열에 담겨있다
  if(excelColumn.length &gt; = 0){
   for(int i=0;i<excelcolumn.length-1;i++){ 
   cell= row.createCell(i+1);
   cell.setcellstyle(headstyle);
   cell.setcellvalue(excelcolumn[i+1].tostring());
  }
  XSSFrow rows = null;
   String temp="";
 if(list="" !="null" &&="" list.size()=""> 0){
   int reCell = 0; //출력해야할 cell
   int reNum = 0;
   int rowStart = 1; 뿌려줄 row
   String reStr = ""; //나머지 출력할 문자
 
   for(int i=0;i<list.size();i++){ 
      int cnt = 0;
     if(!reStr.equals("")){ // 한셀에 32767자가  넘을시 실행
      rows=workSheet.createRow(rowStart);
      cell=rows.createCell(cnt);
      cell.setcellvalue(renum+1);
 
     if(reStr.length() > 32767){
      cell.setCellValue(reStr.substring(0,32767));
      reStr = reStr.substring(32767, reStr.length());
      i = reNum;
     }else{
      cell.setCellValue(reStr);
      reStr = "";
      i = reNum;
     }
    }else{
     rows = workSheet.createRow(rowStart);
     EgovMap em = (EgovMap)list.get(i);
 
    for(Iterator iterator = em.keySet().iterator(); iterator.hasNext();){
     String keyName = (String)iterator.next();
     if(cnt == 0){
      cell = rows.createCell(cnt);
       cell.setCellValue(i+1);
       cnt++;
      }
 
      cell = rows.crateCell(cnt);
      if(em.get(keyName) != null){
       temp = em.get(keyName).toString();
 
       while(temp.indexOf("&lt;") &gt;= 0 &amp;&amp; temp.indexOf("&lt;") &lt;  temp.indexOf("&gt;")){
         temp = temp.replace(temp.substring(temp.indexOf("&lt;"),temp.indexOf("&gt;"+1),"");
       }
    
       temp = temp.replaceAll("\"","");
       temp = temp.replaceAll("&nbsp;","");
 
       if(temp.length() &gt; 32767){
        cell.setCellValue(temp.substring(0,32767));
        reStr = temp.substring(32767,temp.length());
        reCell = cnt;
        reNum = i;
       }else{
         cell.setCellValue(temp);
       }
      }
     cnt++;
    }
   }
   rowStart++;
  }
 }
 
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String today = sdf.format(date);
 
response.setContentType("application/Msexcel");
response.setHeader("Content-Disposition", "ATTachment; Filename="+URLEncoder.encode(excelName,"UTF-8")+"_"+today+".xlsx");
 
workBook.write(response.getOutputStream());
workBook.close();
}
}