Excel 读取模板文件并写入数据

1、在项目路径下存放模板文件如图：

2、代码实现

package com.jan.base.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.Map;

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

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jan.base.web.actions.util.DownloadFileAction;

public class ExcelFileExportUtil
{

   final static String basePath = JANUtil.basePath;

   /**
    * fileName 模板文件名称 读取模板
    * 
    * @param fileName
    * @return
    */
   public static File createNewFile( String fileName )
   {

      String path = JANUtil.basePath + File.separator + "template" + File.separator + fileName;

      File file = new File( path );

      return file;
   }

   /**
    * 
    * @param exportMap
    *            导出数据
    * @param request
    * @param response
    * @param num
    * num从第几行开始写入 titles 对应模板表头
    * fileName 模板名称
    */
   public static void exportExcel( HttpServletRequest request, HttpServletResponse response, final List< Map< String, String > > exportMap, int num, final List< String > titles, String fileName )
   {
      FileInputStream is = null;
      XSSFWorkbook workbook = null;

      try
      {

         File newFile = createNewFile( fileName );
         is = new FileInputStream( newFile );
         workbook = new XSSFWorkbook( is );
         // 获取第一个sheet
         XSSFSheet sheet = workbook.getSheetAt( 0 );

         for ( Map< String, String > item : exportMap )
         {
            // 循环遍历,新建行
            XSSFRow row = sheet.createRow( ( short ) num );
            // 每一列
            int i = 0;
            // 判断有多少列数据
            for ( String title : titles )
            {
               // 设置每列的数据   设置每列的样式   设置每列的值
               XSSFCell cell = row.createCell( i );
               // cell.setCellStyle(style);
               cell.setCellValue( item.get( title.split( "#" )[ 0 ] ) );
               i++;
            }
            num++;
         }
         DownloadFileAction.download( request, response, workbook, fileName, null );

      }
      catch ( FileNotFoundException e )
      {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
      catch ( IOException e )
      {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
      catch ( JANException e )
      {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
      catch ( Exception e )
      {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
      finally
      {
         try
         {
            is.close();
         }
         catch ( IOException e )
         {
            // TODO Auto-generated catch block
            e.printStackTrace();
         }
      }
   }
}


public static void download( final HttpServletRequest request, final HttpServletResponse response, final Object object, final String fileName, String contentType )
         throws JANException
   {
      // 初始化OutputStream
      OutputStream outputStream = null;
      try
      {
         outputStream = response.getOutputStream();
         // 设置返回文件下载
         if ( null == JANUtil.filterEmpty( contentType ) )
         {
            response.setContentType( DownloadFileAction.ContentType.XLSX.getCode() );
         }
         else
         {
            response.setContentType( contentType );
         }

         //是否是Safari
         String userAgent = request.getHeader( "User-Agent" );
         if ( null != JANUtil.filterEmpty( userAgent ) && userAgent.contains( "Mac OS" ) && userAgent.contains( "Safari" ) )
         {
            response.setContentType( DownloadFileAction.ContentType.OCTET.getCode() );
         }

         // 解决文件中文名下载问题
         response.setHeader( "Content-Disposition", "attachment;filename=" + new String( URLDecoder.decode( fileName, "UTF-8" ).getBytes(), "iso-8859-1" ) );

         if ( object instanceof HSSFWorkbook )
         {
            // Excel2003以前（包括2003）的版本，扩展名是.xls；
            HSSFWorkbook workbook = ( HSSFWorkbook ) object;
            workbook.write( outputStream );
         }
         else if ( object instanceof XSSFWorkbook )
         {
            //操作Excel2007后的版本，扩展名是.xlsx
            XSSFWorkbook workbook = ( XSSFWorkbook ) object;
            workbook.write( outputStream );
         }
         else if ( object instanceof SXSSFWorkbook )
         {
            SXSSFWorkbook workbook = ( SXSSFWorkbook ) object;
            workbook.write( outputStream );
         }
         else if ( object instanceof CustomXWPFDocument )
         {
            CustomXWPFDocument workbook = ( CustomXWPFDocument ) object;
            workbook.write( outputStream );
         }
         else if ( object instanceof ByteArrayOutputStream )
         {
            ByteArrayOutputStream workbook = ( ByteArrayOutputStream ) object;
            workbook.writeTo( outputStream );
         }
         else if ( object instanceof InputStream )
         {
            InputStream inputStream = ( InputStream ) object;
            outputStream( outputStream, inputStream );
         }
         else
         {
            InputStream inputStream = ( InputStream ) object;
            outputStream( outputStream, inputStream );
         }
      }
      catch ( final Exception e )
      {
         log.error( " unknow Exception " + e.getMessage() );
         throw new JANException( e );
      }
      finally
      {
         try
         {
            // 输出OutputStream
            outputStream.flush();
            outputStream.close();
         }
         catch ( IOException e )
         {
            e.printStackTrace();
            log.error( "close outputStream error !" + e.getMessage() );
         }
         // 关闭流
      }
   }