Java导出xlsm后缀Excel文件

标签: Java POI xlsm
5人阅读 评论(0) 收藏 举报
分类:

1、创建 Util 类

FileUtils

package com.glaf.apps.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class FileUtils {

    private static final Logger logger = LoggerFactory.getLogger(FileUtils.class);
    /**
     * Construct a file from the set of name elements.
     * 
     * @param directory
     *            the parent directory
     * @param names
     *            the name elements
     * @return the file
     */
    public static File getFile(File directory, String... names) {
        if (directory == null) {
            throw new NullPointerException(
                    "directorydirectory must not be null");
        }
        if (names == null) {
            throw new NullPointerException("names must not be null");
        }
        File file = directory;
        for (String name : names) {
            file = new File(file, name);
        }
        return file;
    }

    public static void Copy(String oldPath, String newPath)throws IOException {
        int byteread = 0;
        File oldfile = new File(oldPath);
        if (oldfile.exists()) {
            InputStream inStream = new FileInputStream(oldPath);
            FileOutputStream fs = new FileOutputStream(newPath);
            byte[] buffer = new byte[1444];
            while ((byteread = inStream.read(buffer)) != -1) {
                fs.write(buffer, 0, byteread);
            }
            inStream.close();
        } else {
            logger.error("文件不存在:{}",oldPath);
        }
    }

    /**
     * Construct a file from the set of name elements.
     * 
     * @param names
     *            the name elements
     * @return the file
     */
    public static File getFile(String... names) {
        if (names == null) {
            throw new NullPointerException("names must not be null");
        }
        File file = null;
        for (String name : names) {
            if (file == null) {
                file = new File(name);
            } else {
                file = new File(file, name);
            }
        }
        return file;
    }

    /**
     * Opens a {@link FileInputStream} for the specified file, providing better
     * error messages than simply calling <code>new FileInputStream(file)</code>
     * .
     * <p>
     * At the end of the method either the stream will be successfully opened,
     * or an exception will have been thrown.
     * <p>
     * An exception is thrown if the file does not exist. An exception is thrown
     * if the file object exists but is a directory. An exception is thrown if
     * the file exists but cannot be read.
     * 
     * @param file
     *            the file to open for input, must not be {@code null}
     * @return a new {@link FileInputStream} for the specified file
     * @throws FileNotFoundException
     *             if the file does not exist
     * @throws IOException
     *             if the file object is a directory
     * @throws IOException
     *             if the file cannot be read
     */
    public static FileInputStream openInputStream(File file) throws IOException {
        if (file.exists()) {
            if (file.isDirectory()) {
                throw new IOException("File '" + file
                        + "' exists but is a directory");
            }
            if (file.canRead() == false) {
                throw new IOException("File '" + file + "' cannot be read");
            }
        } else {
            throw new FileNotFoundException("File '" + file
                    + "' does not exist");
        }
        return new FileInputStream(file);
    }

    /**
     * 创建文件
     * 
     * @param path
     * @param fileName
     * @return
     */
    public static File createFile(String path, String fileName) {
        File file = new File(path);
        if (!file.exists()) {
            file.mkdir();
        }
        file = new File(path, fileName);
        try {
            file.createNewFile();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return file;
    }

    /**
     * Opens a {@link FileOutputStream} for the specified file, checking and
     * creating the parent directory if it does not exist.
     * <p>
     * At the end of the method either the stream will be successfully opened,
     * or an exception will have been thrown.
     * <p>
     * The parent directory will be created if it does not exist. The file will
     * be created if it does not exist. An exception is thrown if the file
     * object exists but is a directory. An exception is thrown if the file
     * exists but cannot be written to. An exception is thrown if the parent
     * directory cannot be created.
     * 
     * @param file
     *            the file to open for output, must not be {@code null}
     * @param append
     *            if {@code true}, then bytes will be added to the end of the
     *            file rather than overwriting
     * @return a new {@link FileOutputStream} for the specified file
     * @throws IOException
     *             if the file object is a directory
     * @throws IOException
     *             if the file cannot be written to
     * @throws IOException
     *             if a parent directory needs creating but that fails
     */
    public static FileOutputStream openOutputStream(File file, boolean append)
            throws IOException {
        if (file.exists()) {
            if (file.isDirectory()) {
                throw new IOException("File '" + file
                        + "' exists but is a directory");
            }
            if (file.canWrite() == false) {
                throw new IOException("File '" + file
                        + "' cannot be written to");
            }
        } else {
            File parent = file.getParentFile();
            if (parent != null) {
                if (!parent.mkdirs() && !parent.isDirectory()) {
                    throw new IOException("Directory '" + parent
                            + "' could not be created");
                }
            }
        }
        return new FileOutputStream(file, append);
    }

    public static FileOutputStream openOutputStream(File file)
            throws IOException {
        return openOutputStream(file, false);
    }

    /**
     * Cleans a directory without deleting it.
     * 
     * @param directory
     *            directory to clean
     * @throws IOException
     *             in case cleaning is unsuccessful
     */
    public static void cleanDirectory(File directory) throws IOException {
        if (!directory.exists()) {
            String message = directory + " does not exist";
            throw new IllegalArgumentException(message);
        }

        if (!directory.isDirectory()) {
            String message = directory + " is not a directory";
            throw new IllegalArgumentException(message);
        }

        File[] files = directory.listFiles();
        if (files == null) { // null if security restricted
            throw new IOException("Failed to list contents of " + directory);
        }

        IOException exception = null;
        for (File file : files) {
            try {
                forceDelete(file);
            } catch (IOException ioe) {
                exception = ioe;
            }
        }

        if (null != exception) {
            throw exception;
        }
    }

    /**
     * 创建目录
     * 
     * @Title: createDirectory
     * @param:@param directoryPath
     * @param:@return
     * @return:boolean
     * @Description:TODO(这里用一句话描述这个方法的作用)
     * @author 
     * @date 2016-9-9 上午11:31:37
     * @throws
     */
    public static boolean createDirectory(String directoryPath) {
        boolean bFlag = false;
        try {
            File file = new File(directoryPath.toString());
            if (!file.exists()) {
                bFlag = file.mkdir();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bFlag;
    }

    // -----------------------------------------------------------------------
    /**
     * Deletes a directory recursively.
     * 
     * @param directory
     *            directory to delete
     * @throws IOException
     *             in case deletion is unsuccessful
     */
    public static void deleteDirectory(File directory) throws IOException {
        if (!directory.exists()) {
            return;
        }

        cleanDirectory(directory);

        if (!directory.delete()) {
            String message = "Unable to delete directory " + directory + ".";
            throw new IOException(message);
        }
    }

    /**
     * Deletes a file. If file is a directory, delete it and all
     * sub-directories.
     * <p>
     * The difference between File.delete() and this method are:
     * <ul>
     * <li>A directory to be deleted does not have to be empty.</li>
     * <li>You get exceptions when a file or directory cannot be deleted.
     * (java.io.File methods returns a boolean)</li>
     * </ul>
     * 
     * @param file
     *            file or directory to delete, must not be {@code null}
     * @throws NullPointerException
     *             if the directory is {@code null}
     * @throws FileNotFoundException
     *             if the file was not found
     * @throws IOException
     *             in case deletion is unsuccessful
     */
    public static void forceDelete(File file) throws IOException {
        if (file.isDirectory()) {
            deleteDirectory(file);
        } else {
            boolean filePresent = file.exists();
            if (!file.delete()) {
                if (!filePresent) {
                    throw new FileNotFoundException("File does not exist: "
                            + file);
                }
                String message = "Unable to delete file: " + file;
                throw new IOException(message);
            }
        }
    }

    /**
     * Deletes a file, never throwing an exception. If file is a directory,
     * delete it and all sub-directories.
     * <p>
     * The difference between File.delete() and this method are:
     * <ul>
     * <li>A directory to be deleted does not have to be empty.</li>
     * <li>No exceptions are thrown when a file or directory cannot be deleted.</li>
     * </ul>
     * 
     * @param file
     *            file or directory to delete, can be {@code null}
     * @return {@code true} if the file or directory was deleted, otherwise
     *         {@code false}
     * 
     */
    public static boolean deleteQuietly(File file) {
        if (file == null) {
            return false;
        }
        try {
            if (file.isDirectory()) {
                cleanDirectory(file);
            }
        } catch (Exception ignored) {
        }

        try {
            return file.delete();
        } catch (Exception ignored) {
            return false;
        }
    }

    /**
     * Makes a directory, including any necessary but nonexistent parent
     * directories. If a file already exists with specified name but it is not a
     * directory then an IOException is thrown. If the directory cannot be
     * created (or does not already exist) then an IOException is thrown.
     * 
     * @param directory
     *            directory to create, must not be {@code null}
     * @throws NullPointerException
     *             if the directory is {@code null}
     * @throws IOException
     *             if the directory cannot be created or the file already exists
     *             but is not a directory
     */
    public static void forceMkdir(File directory) throws IOException {
        if (directory.exists()) {
            if (!directory.isDirectory()) {
                String message = "File " + directory + " exists and is "
                        + "not a directory. Unable to create directory.";
                throw new IOException(message);
            }
        } else {
            if (!directory.mkdirs()) {
                // Double-check that some other thread or process hasn't made
                // the directory in the background
                if (!directory.isDirectory()) {
                    String message = "Unable to create directory " + directory;
                    throw new IOException(message);
                }
            }
        }
    }

    /**
     * Returns the size of the specified file or directory. If the provided
     * {@link File} is a regular file, then the file's length is returned. If
     * the argument is a directory, then the size of the directory is calculated
     * recursively. If a directory or subdirectory is security restricted, its
     * size will not be included.
     * 
     * @param file
     *            the regular file or directory to return the size of (must not
     *            be {@code null}).
     * 
     * @return the length of the file, or recursive size of the directory,
     *         provided (in bytes).
     * 
     * @throws NullPointerException
     *             if the file is {@code null}
     * @throws IllegalArgumentException
     *             if the file does not exist.
     * 
     */
    public static long sizeOf(File file) {

        if (!file.exists()) {
            String message = file + " does not exist";
            throw new IllegalArgumentException(message);
        }

        if (file.isDirectory()) {
            return sizeOfDirectory(file);
        } else {
            return file.length();
        }

    }

    /**
     * Counts the size of a directory recursively (sum of the length of all
     * files).
     * 
     * @param directory
     *            directory to inspect, must not be {@code null}
     * @return size of directory in bytes, 0 if directory is security
     *         restricted, a negative number when the real total is greater than
     *         {@link Long#MAX_VALUE}.
     * @throws NullPointerException
     *             if the directory is {@code null}
     */
    public static long sizeOfDirectory(File directory) {
        checkDirectory(directory);

        final File[] files = directory.listFiles();
        if (files == null) { // null if security restricted
            return 0L;
        }
        long size = 0;

        for (final File file : files) {

            size += sizeOf(file);
            if (size < 0) {
                break;

            }

        }

        return size;
    }

    /**
     * Checks that the given {@code File} exists and is a directory.
     * 
     * @param directory
     *            The {@code File} to check.
     * @throws IllegalArgumentException
     *             if the given {@code File} does not exist or is not a
     *             directory.
     */
    private static void checkDirectory(File directory) {
        if (!directory.exists()) {
            throw new IllegalArgumentException(directory + " does not exist");
        }
        if (!directory.isDirectory()) {
            throw new IllegalArgumentException(directory
                    + " is not a directory");
        }
    }
}

ExcelUtil

package com.glaf.apps.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.glaf.core.config.SystemProperties;
/**
 * Excel导出封装类
 * 
 * @author 
 * @param <T>
 */
public class ExcelUtil<T> {
    /**
     * 基于Excel 2007模板写入数据
     * 
     * @Title: writeExcel
     * @param:@param file 模板文件
     * @param:@param dataSet 数据集
     * @param:@throws IOException
     * @param:@throws NoSuchMethodException
     * @param:@throws SecurityException
     * @param:@throws InvocationTargetException
     * @return:void
     * @author 
     * @date 2017-3-14 下午3:13:12
     * @throws
     */
    @SuppressWarnings("unused")
    public void writeExcel(File file,List<T> dataSet,String newfilepath) 
            throws IOException, NoSuchMethodException,SecurityException, 
            InvocationTargetException {
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));

        XSSFSheet sheet = workbook.getSheet("数据输入");//读取 '数据输入' sheet 页
        // 写入内容
        Iterator<T> iterator = dataSet.iterator();
        int index = 4;
        while (iterator.hasNext()) {
            XSSFRow row = sheet.getRow(index);//获取从第 5行开始的行对象
            if(row == null){
                row = sheet.createRow(index);//该行无数据,创建行对象
            }
            T t = (T) iterator.next();
            // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();

            for (short i = 0; i < fields.length; i++) {
                if (i == 0) {
                    XSSFCell cell = row.createCell(i);
//                    cell.setCellValue(index);
                    cell = row.createCell(i);
                    //设置单元格样式
                    XSSFCellStyle cellStyle = workbook.createCellStyle(); 

                    //设置单元格边框线
                    cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框    
                    cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框    
                    cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框    
                    cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框    

                    // 设置单元格边框颜色     
                    cellStyle.setBottomBorderColor(new XSSFColor(java.awt.Color.gray));     
                    cellStyle.setTopBorderColor(new XSSFColor(java.awt.Color.gray));     
                    cellStyle.setLeftBorderColor(new XSSFColor(java.awt.Color.gray));     
                    cellStyle.setRightBorderColor(new XSSFColor(java.awt.Color.gray));
                    cell.setCellStyle(cellStyle);

                    fields[i].setAccessible(true);
                    try {
                        String fieldName = fields[i].getName();
                        String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                        Object valueObject = fields[i].get(t);
                        Class<? extends Object> tCls = t.getClass();
                        Method getMethod = tCls.getMethod(getMethodName,new Class[]{});
                        Object value = getMethod.invoke(t, new Object[]{});
                        if (valueObject instanceof String) {
                            cell.setCellValue(valueObject.toString());
                        } else {
                            cell.setCellValue(valueObject + "");
                        }
                    } catch (IllegalArgumentException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                } else {
                    XSSFCell cell = row.createCell(i);
                    //设置单元格样式
                    XSSFCellStyle cellStyle = workbook.createCellStyle(); 

                    //设置单元格边框线
                    cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框    
                    cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框    
                    cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框    
                    cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框    

                    // 设置单元格边框颜色     
                    cellStyle.setBottomBorderColor(new XSSFColor(java.awt.Color.gray));     
                    cellStyle.setTopBorderColor(new XSSFColor(java.awt.Color.gray));     
                    cellStyle.setLeftBorderColor(new XSSFColor(java.awt.Color.gray));     
                    cellStyle.setRightBorderColor(new XSSFColor(java.awt.Color.gray));
                    cell.setCellStyle(cellStyle);

                    fields[i].setAccessible(true);
                    try {
                        String fieldName = fields[i].getName();
                        String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                        Object valueObject = fields[i].get(t);
                        Class<? extends Object> tCls = t.getClass();
                        Method getMethod = tCls.getMethod(getMethodName,new Class[]{});
                        Object value = getMethod.invoke(t, new Object[]{});
                        if (valueObject instanceof String) {
                            if (valueObject == null) {
                                cell.setCellValue("");
                            } else {
                                cell.setCellValue(valueObject.toString());
                            }
                        } else if (valueObject instanceof BigDecimal) {
                            BigDecimal vDecimal = (BigDecimal) value;
                            cell.setCellValue(vDecimal.doubleValue());
                        } else if (valueObject instanceof Integer) {
                            cell.setCellValue((Integer) valueObject);
                        } else if (valueObject instanceof Double) {
                            cell.setCellValue((Double) valueObject);
                        } else {
                            if (valueObject == null) {
                                cell.setCellValue("");
                            } else {
                                cell.setCellValue(valueObject.toString());
                            }

                        }
                    } catch (IllegalArgumentException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
            index++;
        }
        OutputStream outputStream = new FileOutputStream(newfilepath);
        workbook.write(outputStream);
        outputStream.close();
    }
    /**
     * 下载Excel
     * 
     * @param request
     * @param response
     * @param list 要导出的数据
     * @param model  模板名称
     * @param name 导出Excel文件名
     * @return
     * @throws IOException
     */
    public void download(HttpServletRequest request,HttpServletResponse response, List<T> list,String model,String fname,String name) throws IOException {
        ServletOutputStream out = null;
        FileInputStream inputStream = null;
        String newfilepath = null;
        try {
            response.setContentType("multipart/form-data");
            String path = SystemProperties.getAppPath();// 获取模板路径
//            path += model + ".xlsm";//excel模板
            String fileName = name +"_" + System.currentTimeMillis() + ".xlsm";
            response.setHeader("Content-Disposition","attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
//            FileUtils.Copy(path + model + ".xlsm", path + model + "NewFileExcel.xlsm");
            File file = new File(path + model + fname + ".xlsm");
            newfilepath = path + model + "ImportPath\\" + fname + "new.xlsm";//数据组装后excel 存放路径
            writeExcel(file, (List<T>) list, newfilepath);//组装数据
            out = response.getOutputStream();
            inputStream = new FileInputStream(newfilepath);
            int b = 0;
            byte[] buffer = new byte[4096];
            while ((b = inputStream.read(buffer)) != -1) {
                out.write(buffer, 0, b);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            inputStream.close();
            out.close();
            out.flush();
        }
    }

    /**
     * 基于Excel 2003模板写入数据
     * 
     * @Title: writeExcelContent
     * @param:@param file
     * @param:@param dataSet
     * @param:@throws IOException
     * @param:@throws NoSuchMethodException
     * @param:@throws SecurityException
     * @param:@throws InvocationTargetException
     * @Description:TODO()
     * @date 
     * @throws
     */
    @SuppressWarnings("unused")
    public void writeExcel2003(File file, Collection<T> dataSet)
            throws IOException, NoSuchMethodException, SecurityException,
            InvocationTargetException {
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = workbook.getSheetAt(0);

        // 写入内容
        Iterator<T> iterator = dataSet.iterator();
        int index = 1;
        while (iterator.hasNext()) {
            HSSFRow row = sheet.createRow(index);
            T t = (T) iterator.next();
            // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();

            for (short i = 0; i < fields.length; i++) {
                if (i == 0) {
                    @SuppressWarnings("deprecation")
                    HSSFCell cell = row.createCell(i);
                    cell.setCellValue(index);
                    cell = row.createCell(i + 1);
                    fields[i].setAccessible(true);
                    try {
                        String fieldName = fields[i].getName();
                        String getMethodName = "get"
                                + fieldName.substring(0, 1).toUpperCase()
                                + fieldName.substring(1);
                        Object valueObject = fields[i].get(t);

                        Class<? extends Object> tCls = t.getClass();
                        Method getMethod = tCls.getMethod(getMethodName,
                                new Class[]{});
                        Object value = getMethod.invoke(t, new Object[]{});
                        if (valueObject instanceof String) {
                            cell.setCellValue(valueObject.toString());
                        } else {
                            cell.setCellValue(valueObject + "");
                        }
                    } catch (IllegalArgumentException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                } else {
                    HSSFCell cell = row.createCell(i + 1);
                    fields[i].setAccessible(true);
                    try {
                        String fieldName = fields[i].getName();
                        String getMethodName = "get"
                                + fieldName.substring(0, 1).toUpperCase()
                                + fieldName.substring(1);
                        Object valueObject = fields[i].get(t);

                        Class<? extends Object> tCls = t.getClass();
                        Method getMethod = tCls.getMethod(getMethodName,
                                new Class[]{});
                        Object value = getMethod.invoke(t, new Object[]{});
                        if (valueObject instanceof String) {
                            if (valueObject == null) {
                                cell.setCellValue("");
                            } else {
                                cell.setCellValue(valueObject.toString());
                            }

                        } else if (valueObject instanceof BigDecimal) {
                            BigDecimal vDecimal = (BigDecimal) value;
                            cell.setCellValue(vDecimal.doubleValue());
                        } else if (valueObject instanceof Integer) {
                            cell.setCellValue((Integer) valueObject);
                        } else if (valueObject instanceof Double) {
                            cell.setCellValue((Double) valueObject);
                        } else {
                            if (valueObject == null) {
                                cell.setCellValue("");
                            } else {
                                cell.setCellValue(valueObject.toString());
                            }

                        }
                    } catch (IllegalArgumentException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
            index++;
        }
        OutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);

        outputStream.close();
    }
}

2、 创建测试类

//导出 Excel
    @RequestMapping(params = "method=importExcel")
    public void importExcel(ModelMap modelMap, HttpServletRequest request, HttpServletResponse response){
        LoginContext loginContext = RequestUtils.getLoginContext(request);
        Map<String, Object> params = RequestUtils.getParameterMap(request);
        FillCardQuery query = new FillCardQuery();
        Tools.populate(query, params);
//      query.deleteFlag(0);
//      query.setLoginContext(loginContext);
        query.setActorId(loginContext.getActorId());
        Integer state = query.getStatus();
        if (!loginContext.isSystemAdministrator()) {
            query.setCreator(loginContext.getActorId());//申请人
        }
        //查询条件
        if(state != null){
            if(state==0){//未提交
                query.setStatus(null);
                query.setStatusGreaterThanOrEqual(-15);
                query.setStatusLessThanOrEqual(0);
            }else if(state==10){//审批中
                query.setStatus(null);
                query.setStatusGreaterThanOrEqual(10);
                query.setStatusLessThanOrEqual(10);
            }else if(state==20){//审批通过
                query.setStatus(null);
                query.setStatusGreaterThanOrEqual(20);
            }
        }else{
            query.setStatusGreaterThanOrEqual(-20);
        }

        String basePath = SystemProperties.getAppPath();
        ExcelUtil<User> vExcelUtil = new ExcelUtil<User>();//导出类初始化
        List<User> list = new ArrayList<>();
        try {
            List<FillCard> fillCards = fillCardService.list(query);
            List ImportFillCardToXlsmExcels = new ArrayList<T>();
            for (FillCard fillCard : fillCards) {

                //三个时间要自己转成 String 类型传入 Excel,因为 Excel 不能解析 Date 类型数据, 例如: Sat Apr 14 13:25:00 CST 2018
                SimpleDateFormat formatteryyyymmdd = new SimpleDateFormat("yyyy/MM/dd");
                SimpleDateFormat formatterhhmm = new SimpleDateFormat("hh:mm");

                String fillCardDateyyyymmdd = "";
                String fillCardDatehhmm = "";

                if(fillCard.getFillCardDate() != null){
                    fillCardDateyyyymmdd = formatteryyyymmdd.format(fillCard.getFillCardDate());
                    fillCardDatehhmm = formatterhhmm.format(fillCard.getFillCardDate());
                }

                ImportFillCardToXlsmExcel xlsmExcel = 
                        new ImportFillCardToXlsmExcel(fillCard.getEmployeeNo(), fillCardDateyyyymmdd, fillCardDatehhmm, fillCard.getReason());
                ImportFillCardToXlsmExcels.add(xlsmExcel);
                String roolPath = "\\templates\\FillCard\\";
                String fname = "HPS_ABS_CI_003_CI_打卡数据CI";
                vExcelUtil.download(request, response, ImportFillCardToXlsmExcels, roolPath, fname, "HPS_ABS_CI_003_CI_打卡数据CI");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
查看评论

我参加系统分析员考试的一点感受

本文已发表在《中国系统分析员》杂志2003第2期上我去年10月参加系统分析员考试,以48,47,50的成绩通过,5月便可领取证 书。以下是我的一点感受,希望能给将要参加和准备参加系统分析员考试 的朋友...
  • weck
  • weck
  • 2003-10-29 17:56:00
  • 692

EXCEL的扩展名xls与xlsm的区别

xls是2003版本下的文件 ,不管有没有宏程序的话都是xls文件 ,从2007开始做了区分,XLSM文件和XLSX文件都是excel2007及其以后的文件,但前者是含有宏启用,Excel中默认情况下...
  • whatday
  • whatday
  • 2013-10-19 11:58:03
  • 15549

java poi 导出.xls的Excel报表 IE11导出文件名称后缀是.action,待修复

// public void exportStationWaterQualityData(){ List list=treeService.getExportStationWater...
  • big1989wmf
  • big1989wmf
  • 2017-04-12 16:29:42
  • 686

poi读取excel(xls和xlsx,xlsm)给定单元格内容

xls和xlsx文件要读取的内容均在(2,2)单元格中,且均为String类型(ver3 1.1.3); xlsm文件要读取sheet名为"システム管理"的(3,2)单元格中的内容,且为Numeri...
  • zhangdingJava
  • zhangdingJava
  • 2018-01-14 20:01:05
  • 270

IE11浏览器下导出Excel没有文件扩展名

使用poi,做传统的excel导出,然后想在浏览器中,让用户选择另存为,保存用户下载的xls文件,这个时候,可能的是在ie下出现乱码(ie,9,10,11),但在firefox,chrome下没乱码,...
  • zdp072
  • zdp072
  • 2016-07-06 15:56:05
  • 2130

VBA程序xlsm文件另存xlsx不能保存的问题

表达式.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, Acce...
  • wenjianzhiqin
  • wenjianzhiqin
  • 2016-09-26 14:50:42
  • 2587

java使用POI实现excel文件的读取,兼容后缀名xls和xlsx

首先,引入所需的jar包: 如果是maven管理项目的jar包,只需在pom.xml中加上: POIUtil工具类的代码: package com.cn.util; import java.io.Fi...
  • aqsunkai
  • aqsunkai
  • 2016-08-21 23:40:44
  • 28551

jxl导出xls后缀的excel文件

基于ssm+jsp的导出方法,使用的是jxl import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServl...
  • xyz112007241012
  • xyz112007241012
  • 2017-05-10 19:45:53
  • 217

java web项目导出excel,chrome和fireFox的文件名编码问题

java web项目导出excel表格时chrome和fireFox的文件名含有中文编码问题
  • nwsuaf2009012882
  • nwsuaf2009012882
  • 2014-12-11 17:55:15
  • 2108

POI导出Excel文件名消失问题

POI导出Excel文件名消失问题  使用poi,在网页端导出excel的时候,遇到了一个问题,当我设置的文件名是英文的时候导出正常,但是当我设置文件名是中文名称的时候,发现在网页上下载的excel只...
  • JerryITGO
  • JerryITGO
  • 2017-06-20 15:41:10
  • 498
    个人资料
    持之以恒
    等级:
    访问量: 4766
    积分: 428
    排名: 11万+