Excel模板导出

Excel模板导出

引入jar包：

<dependency>

                     <groupId>org.apache.poi</groupId>

                     <artifactId>poi</artifactId>

                     <version>3.9</version>

       </dependency>

 

<!-- 百万级大数据导出，不支持excel模板格式 -->

       <dependency>

                     <groupId>org.apache.poi</groupId>

                     <artifactId>poi-ooxml</artifactId>

                     <version>3.9</version>

       </dependency>

 

 

import java.io.ByteArrayOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.lang.reflect.Field;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

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.xssf.usermodel.XSSFWorkbook;

 

public class ExportUtil {

       //list为数据库查出来的Entity集合  fileName为excel名字

       public staticvoid exportByExcel(List<Object>list,String fileName,HttpServletRequestrequest,

                     HttpServletResponseresponse) throwsIOException, IllegalArgumentException, IllegalAccessException{

             

              //获取模板excel

String path = request.getSession().getServletContext().getRealPath("/") + "/resources/xls/";

              InputStreamis = newFileInputStream(new File(path + fileName));

             

              Workbookwb = newXSSFWorkbook(is);

             

              //获取第一个sheet页

              Sheetsheet = wb.getSheetAt(0);

             

              RownRow = null;

              CellnCell = null;

             

              int rowNo =1,colNo = 0;

             

              //模板ecxel的样式

              RownRowTemp = sheet.getRow(1);

             

              //获取excel 第2行的所有cellStyle集合 (第一行为标题)

              List<CellStyle>styleList = newArrayList();

              for(inti=0; i<nRowTemp.getLastCellNum();i++){

                     CellStylecellStyle = nRowTemp.getCell(i).getCellStyle();

                     styleList.add(cellStyle);

              }

             

              DateFormatdf = newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");

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

                     colNo = 0;

                     Objectt = list.get(i);

                     Classclazz = t.getClass();

                     Field[]fs = clazz.getDeclaredFields();

                    

                     nRow = sheet.createRow(rowNo++);

                    

                     for(intj=0; j<fs.length;j++){

                            Fieldf = fs[j];

                            f.setAccessible(true);

                           

                            //属性的值

                            Objectval = f.get(t);

                            //属性的名称

                            //String propertyName = f.getName();

                            if(val instanceof Date){

                                   val = df.format(val);

                            }

                           

                            nCell = nRow.createCell(colNo++);

                            nCell.setCellValue(val.toString());

                            nCell.setCellStyle(styleList.get(j));

                     }

                    

              }

             

             

              ByteArrayOutputStreamos = newByteArrayOutputStream();

              wb.write(os);

 

              new DownloadUtil().download(os, response, fileName);

              os.flush();

              os.close();

             

       }

 

DownloadUtil：

public void download(ByteArrayOutputStreambyteArrayOutputStream, HttpServletResponse response, String returnName)throws IOException{

              response.setContentType("application/octet-stream;charset=utf-8");

              returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));                   //保存的文件名,必须和页面编码一致,否则乱码

              response.addHeader("Content-Disposition",   "attachment;filename="+ returnName); 

              response.setContentLength(byteArrayOutputStream.size());

             

              ServletOutputStreamoutputstream = response.getOutputStream();       //取得输出流

              byteArrayOutputStream.writeTo(outputstream);                                  //写到输出流

              byteArrayOutputStream.close();                                                          //关闭

              outputstream.flush();                                                                          //刷数据

       }