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;

    }

}