根据Excel模板，填写报表，并下载到web浏览器端

package com.neusoft.nda.basic.recordmanager.viewelec.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;

public class ExportExcelForArchiveUseServlet extends HttpServlet{

    /**
     * 
     */
    private static final long serialVersionUID = -3695494222618327879L;

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
//获取模板路径    
        String sourcePath=getServletContext().getRealPath("/WEB-INF/")+"/conf/arrangement/cataloguins/exceltemplate/exportArchiveUse.xls";            
        writeExcel(req, resp, sourcePath);
    }
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
    
        doPost(req, resp);
    }
    private static void writeExcel(HttpServletRequest req, HttpServletResponse resp,String finalXlsxPath) {        
        ServletOutputStream out = null;
        // 读取Excel文档
        File finalXlsxFile = createNewFile(finalXlsxPath);//复制模板，
        Workbook workBook = null;
        try {
            workBook = getWorkbok(finalXlsxFile);
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        //收集需要填入报表的数据
// sheet 对应一个工作页  插入数据开始 ------
        Sheet sheet = workBook.getSheetAt(0);
        workBook.createCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        workBook.createCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
       
　　　　//操作单元格，把数据写入即可

        Row row6 = sheet.getRow(5);// 获取到第6行
        Cell cell5 = row6.getCell(4);// 6行 5列
      
        Row row7 = sheet.getRow(6);// 获取到第7行
        Cell cell75 = row7.getCell(4);// 7行 5列
      //插入数据结束
        try {
            //设置下载的Excel的文件名称
            resp.setHeader("Content-Disposition", "attachment;filename=" + new String(finalXlsxFile.getName().getBytes("GBK"),"ISO8859_1"));
        } catch (UnsupportedEncodingException e2) {
            e2.printStackTrace();
        }

        try {
            out=resp.getOutputStream();
        //    out = new FileOutputStream(finalXlsxFile);
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        } catch (IOException e) {
        
            e.printStackTrace();
        }
        try {
            workBook.write(out);
        } catch (IOException e1) {
            e1.printStackTrace();
        }
        try {
            if (out != null) {
                out.flush();
                out.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    
    }

    /**
     * 判断excel格式版本
     * 
     * @param file
     * @return
     * @throws IOException
     */
    private static Workbook getWorkbok(File file) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if (file.getName().endsWith(".xls")) { // Excel 2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith("xlsx")) { // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    private static File createNewFile(String path) {
        // 读取模板，并赋值到新文件************************************************************
        // 文件模板路径
        File file = new File(path);
        if (!file.exists()) {
            System.out.println("原模板文件不存在");
        }
        // 保存文件的路径
        String realPath = file.getParent();
        // 新的文件名
        String newFileName = "报表-" + System.currentTimeMillis()
                + ".xls";
        // 判断路径是否存在
        File dir = new File(realPath);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        // 写入到新的excel
        File newFile = new File(realPath, newFileName);
        try {
            newFile.createNewFile();
            // 复制模板到新文件
            FileUtils.copyFile(file, newFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newFile;
    }
    
    
}

 

转载于:https://www.cnblogs.com/woshuaile/p/9917148.html