导入导出EXCEL笔记

导出Excel

package application.common.util;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExportExcelUtil
{

    private static short  XLS_ENCODING  = HSSFWorkbook.ENCODING_UTF_16;

    private static String DATE_FORMAT   = " m/d/yy ";                  //  "m/d/yy h:mm"

    private static String NUMBER_FORMAT = " #,##0.00 ";

    private String        xlsFileName;

    private HSSFWorkbook  workbook;

    private HSSFSheet     sheet;

    private HSSFRow       row;

    /**
     * @param fileName
     */
    public ExportExcelUtil(String fileName)
    {
        this.xlsFileName = fileName;
        this.workbook = new HSSFWorkbook();
        this.sheet = workbook.createSheet();
    }

    /**
     * @param response
     * @throws Exception
     */
    public void exportXLS(HttpServletResponse response) throws Exception
    {
        try
        {
            FileOutputStream fOut = new FileOutputStream(xlsFileName);
            workbook.write(fOut);
            fOut.flush();
            fOut.close();
        }
        catch (FileNotFoundException e)
        {
            throw new Exception(" ��ɵ���Excel�ļ����! ", e);
        }
        catch (IOException e)
        {
            throw new Exception(" д��Excel�ļ����! ", e);
        }

    }

    /**
     * @param index
     */
    public void createRow(int index)
    {
        this.row = this.sheet.createRow(index);
    }

    /**
     * @param index
     * @param value
     */
    public void setCell(int index, String value)
    {
        HSSFCell cell = this.row.createCell((short) index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setEncoding(XLS_ENCODING);
        cell.setCellValue(value);
    }


    /**
     * 设置单元格的日期型格式的值
     * @param index
     * @param value
     */
    public void setCell(int index, Calendar value)
    {
        HSSFCell cell = this.row.createCell((short) index);
        cell.setEncoding(XLS_ENCODING);
        cell.setCellValue(value.getTime());
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); //  ����cell��ʽΪ���Ƶ����ڸ�ʽ 
        cell.setCellStyle(cellStyle);
    }


    /**
     * 设置单元格的int值
     * @param index
     * @param value
     */
    public void setCell(int index, int value)
    {
        HSSFCell cell = this.row.createCell((short) index);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
    }


    /**
     * 设置单元格的double值
     * @param index
     * @param value
     */
    public void setCell(int index, double value)
    {
        HSSFCell cell = this.row.createCell((short) index);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
        HSSFCellStyle cellStyle = workbook.createCellStyle();         　　　　　HSSFDataFormat format = workbook.createDataFormat();
        cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));         
　　　　　cell.setCellStyle(cellStyle);     
　　　}
}

DownloadExcelTable

package application.common.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

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


public class DownloadExcelTable extends HttpServlet
{

    /**
     * 构造方法
     */
    public DownloadExcelTable()
    {
        super();
    }

    /**
     * 资源释放
     */
    public void destroy()
    {
        super.destroy();
    }

    /**
     * 请求接收处理方法
     * 
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
        doPost(request, response);
    }

    /**
     * 请求接收处理方法
     * 
     * @param request the request send by the client to the server
     * @param response the response send by the server to the client
     * @throws ServletException if an error occurred
     * @throws IOException if an error occurred
     */
    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
        String dir = request.getSession().getServletContext().getRealPath("/");
        String fileName = request.getParameter("fileName");
        InputStream in = new FileInputStream(dir + "temp" + fileName);
        responseWrite(in, response, fileName);
        File file = new File(fileName);
        if (file.exists())
        {
            file.deleteOnExit();
        }
    }

    /**
     * 初始化方法
     *
     * @throws ServletException if an error occure
     */
    public void init() throws ServletException
    {
    }

    /**
     * 输出响应方法
     * 
     * @param in
     * @param response
     * @param fileName
     */
    public void responseWrite(InputStream in, HttpServletResponse response, String fileName)
    {
        ServletOutputStream out = null;
        try
        {
            response.reset();
            response.addHeader("content-type", "application/x-msdownload");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/vnd.ms-excel");
            out = response.getOutputStream();
            BufferedInputStream buffer = new BufferedInputStream(in);
            byte[] by = new byte[3 * 1024];
            int length = 0;
            while ((length = buffer.read(by)) > 0)
                out.write(by, 0, length);
            out.flush();
            if (out != null)
            {
                out.close();
            }
            if (in != null)
            {
                in.close();
            }
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }
}

导入Excel

package ui.web.whitelistmgr;

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 java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
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.poifs.filesystem.POIFSFileSystem;

public class ImportWhiteExcelAction extends BaseAction
{

    private Logger logger              = Logger.getLogger(ImportWhiteExcelAction.class);

    private String COULUMN_LINKMANNAME = "联系人";

    private String COULUMN_FROM        = "来自";

    private int    LOCATION            = 1;

    private String fileExtName         = "xls";

    private String ISOVER              = "isOver";

    private String NOTOVER             = "notOver";
    
    private int    empID               = 0;

    /**
     * 批量从EXCEL导入信息
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws UnsupportedEncodingException
     */
    public ActionForward importWhiteExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request,
            HttpServletResponse response) throws UnsupportedEncodingException
    {
        
        //获取登陆ID
        LoginInfo loginIfo = (LoginInfo) request.getSession().getAttribute(SysConstants.GLOBAL_USER_SESSION);

        this.empID = Integer.parseInt(loginIfo.getLoginID());
        
        ImportExcelForm importExcelForm = (ImportExcelForm) form;

        FormFile formFile = importExcelForm.getTheFile();

        String fileName = formFile.getFileName();

        // 获取资源文件,并取得键值
        ApplicationGlobalResource appresource = ApplicationGlobalResource.getInstance();

        String filePath = SysUtils.trim(appresource.getValueByKey("takepath.shangchuan"));

        //String filePath ="d:/temp/";
        
        try
        {
            File file = new File(filePath);
            if (!file.exists())
            {
                file.mkdir();
            }
        }
        catch (Exception e1)
        {
            logger.info("导入Excel时创建文件异常");
        }
        
        filePath = filePath.replace("\\", "/");

        if (!filePath.endsWith("/"))
        {
            filePath += "/";
        }

        String fullPath =  filePath + fileName;;

        try
        {
            InputStream inputStream = formFile.getInputStream();

            //建立一个上传文件的输出流 

            OutputStream bos = new FileOutputStream(fullPath);

            int bytesRead = 0;

            byte[] buffer = new byte[8192];

            while ((bytesRead = inputStream.read(buffer, 0, 8192)) != -1)
            {
                bos.write(buffer, 0, bytesRead);//将文件写入服务器 
            }

            inputStream.close();

            bos.close();
        }

        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {

            e.printStackTrace();
        }

        boolean isOver = false;

        String error = "";

        int errorNum = 0;

        String Over = importExcelForm.getIsOver();

        if (Over.equalsIgnoreCase(ISOVER))
        {
            isOver = true;
        }
        if (Over.equalsIgnoreCase(NOTOVER))
        {
            isOver = false;
        }

        //如果文件路径不为空
        if (fullPath != null && !fullPath.equalsIgnoreCase(""))
        {
 
            // 获取附件的后缀名
            String last = fileName.substring(fileName.lastIndexOf(".") + LOCATION);

            if (last != null && last.equalsIgnoreCase(fileExtName))
            {
                errorNum = writeExcel(importExcelForm, fullPath, isOver);

                if (errorNum == -1)
                {
                    String msg = "excel文件的内容不合规范！";
                    logger.error(msg);
                    error = getTheErrorMsg(msg);
                    request.setAttribute("error", error);
                    return mapping.findForward("inputWhiteList");
                }
                if (errorNum == -2)
                {
                    String msg = "excel文件内容为空";
                    logger.error(msg);
                    error = getTheErrorMsg(msg);
                    request.setAttribute("error", error);
                    return mapping.findForward("inputWhiteList");
                }
                if (errorNum == -3)
                {
                    String msg = "导入Excel保存数据时异常！";
                    logger.error(msg);
                    error = getTheErrorMsg(msg);
                    request.setAttribute("error", error);
                    return mapping.findForward("inputWhiteList");
                }
                if (errorNum == -5)
                {
                    String msg = "你所导入的文件不存在！";
                    logger.error(msg);
                    error = getTheErrorMsg(msg);
                    request.setAttribute("error", error);
                    return mapping.findForward("inputWhiteList");
                }
            }
            else
            {
                String msg = "您选中文件的格式不正确，请选择其他文件！";
                logger.error(msg);
                error = getTheErrorMsg(msg);
                request.setAttribute("error", error);
                return mapping.findForward("inputWhiteList");
            }

        }
        //如果文件路径为空
        else
        {
            String msg = "您没有选择待导入的文件！";
            logger.error(msg);
            error = getTheErrorMsg(msg);
            request.setAttribute("error", error);
        }
        return mapping.findForward("inputWhiteList");

    }

    /**
     * 得到Excel　sheet
     * @param filePath
     * @return
     */
    private HSSFSheet getTheSheet(String filePath)
    {
        POIFSFileSystem fs = null;

        HSSFWorkbook wb = null;

        try
        {
            fs = new POIFSFileSystem(new FileInputStream(filePath));

            wb = new HSSFWorkbook(fs);
        }

        catch (IOException e)
        {
            logger.error("你导入的文件不存在");
            return null;
        }

        HSSFSheet sheet = wb.getSheetAt(0);

        return sheet;
    }

    /**
     * 写入EXCEL文件
     * @param importExcelForm
     * @param filePath
     * @param isOver
     * @return
     */
    private int writeExcel(ImportExcelForm importExcelForm, String filePath, boolean isOver)
    {

        int f = 0;

        HSSFSheet sheet = getTheSheet(filePath);
        
        if (sheet == null)
        {
            f = -5;
            return f;
        }

        boolean excelFormat = checkExcelFile(sheet);

        //如果excel文件的格式不合规范
        if (!excelFormat)
        {
            f = -1;
            return f;
        }

        //如果excel文件为空
        if (sheet == null)
        {
            f = -2;
            return f;
        }

        int rowNum = 0; //Excel文件记录数

        int i = 0;

        int failSum = 0;

        int successSum = 0;

        rowNum = sheet.getLastRowNum();

        IWhitelistMgrDS whitelistMgrDS = (IWhitelistMgrDS) SSBBus.findDomainService("whitelistMgsDS");

        ILinkManDS iLinkManDS = (ILinkManDS) SSBBus.findDomainService("linkmanDS");

        //从excel的第一行开始而不是从第零行开始
        for (i = 1; i <= rowNum; i++)
        {

            Map map = getTheWhitemanDVO(sheet, i);

            //设置联系人ID
            WhitelistDVO newWhitelistDVO = new WhitelistDVO();

            String linkmanName = (String) map.get("linkmanName");

            if (linkmanName != null && linkmanName.equalsIgnoreCase(""))
            {
                failSum = failSum + 1;
                return -1;
            }

            int isFrom = 0;

            isFrom = (Integer) map.get("isFrom");

            int empId = 0;

            empId = (Integer) map.get("empID");

            //封装新的白名单对象
            newWhitelistDVO.setUserFrom(isFrom);

            newWhitelistDVO.setEmpID(empId);

            newWhitelistDVO.setEnabled(1);

            newWhitelistDVO.setWhitelistType(2);

            List linkmanIdList = iLinkManDS.getLinkmanIdByName(linkmanName);

            //根据联系人名称去联系人查询，如果linkmanIdList不为null并且查到有这个人
            if (linkmanIdList != null && linkmanIdList.size() > 0)
            {
                for (Iterator iter = linkmanIdList.iterator(); iter.hasNext();)
                {
                    int linkmanId = 0;

                    linkmanId = (Integer) iter.next();

                    newWhitelistDVO.setUserCode(linkmanId);

                    //根据linkmanName查去联系人表里查询，如果有这个联系人，则把它加到白名单里，否则跳过不加，失败数加1                    
                    if (linkmanId == 0)
                    {
                        failSum = failSum + 1;
                    }
                    else
                    {
                        List list = whitelistMgrDS.getWhitelistDVOByLinkmanId(String.valueOf(linkmanId));

                        if (list != null && list.size() > 0)
                        {
                            for (Iterator witer = list.iterator(); witer.hasNext();)
                            {
                                WhitelistDVO whitelistDVO = (WhitelistDVO) witer.next();

                                //如果白名单里有此linkmanName的人，那就新增一条记录。成功数加1
                                if (whitelistDVO != null)
                                {
                                    //则如果用户选择了覆盖，则修改记录为此userCode的人,成功数都加1;
                                    if (isOver)
                                    {

                                        try
                                        {
                                            newWhitelistDVO.setAutoSN(whitelistDVO.getAutoSN());
                                            whitelistMgrDS.updateWhitelistDVO(newWhitelistDVO);

                                        }
                                        catch (RuntimeException e)
                                        {
                                            //失败数加1
                                            failSum++;
                                            logger.error("导入Excel保存数据时异常");
                                            e.printStackTrace();
                                        }
                                        //成功数加1
                                        successSum = successSum + 1;
                                    }
                                    //如果用户选择了不覆盖，则新增一条记录;成功数都加1
                                    else
                                    {
                                        try
                                        {
                                            whitelistMgrDS.insertWhitelistDVO(newWhitelistDVO);
                                            //成功数加1
                                            successSum = successSum + 1;

                                        }
                                        catch (RuntimeException e)
                                        {
                                            //失败数加1
                                            failSum++;
                                            logger.error("导入Excel保存数据时异常");
                                            e.printStackTrace();
                                        }
                                    }
                                }
                                //如果白名单里没有此人，则加进该人
                                else
                                {
                                    try
                                    {
                                        whitelistMgrDS.insertWhitelistDVO(newWhitelistDVO);
                                        //成功数加1
                                        successSum = successSum + 1;
                                    }
                                    catch (RuntimeException e)
                                    {
                                        //失败数加1
                                        failSum++;
                                        logger.error("导入Excel保存数据时异常");
                                        e.printStackTrace();
                                    }
                                }
                            }
                        }
                        //如果白名单里没有此人，则加进该人
                        else
                        {

                            try
                            {
                                whitelistMgrDS.insertWhitelistDVO(newWhitelistDVO);
                                //成功数加1
                                successSum = successSum + 1;

                            }
                            catch (RuntimeException e)
                            {
                                //失败数加1
                                failSum++;
                                logger.error("导入Excel保存数据时异常");
                                e.printStackTrace();
                            }

                        }
                    }
                }
            }
            //根据联系人名称去联系人查询，如果联系人表里没有此人
            else
            {
                failSum = failSum + 1;
            }
        }
        importExcelForm.setFailSum(failSum);
        importExcelForm.setSuccessSum(successSum);
        return f;
    }

    /**
     * 封装白名单对象
     * @param sheet
     * @param i
     * @return
     */
    private Map getTheWhitemanDVO(HSSFSheet sheet, int i)
    {
        Map map = new HashMap();
        
        map.put("empID", empID);

        HSSFRow row = null;

        row = sheet.getRow(i);

        String   linkmanName = getStringCellValue(row.getCell((short) 0));

        if ("".equals(linkmanName))
        {
            logger.error("Excel里的联系人名称字段不能为空");
        }
        
        map.put("linkmanName", linkmanName);

        String userFrom = getStringCellValue(row.getCell((short) 1));

        if ("".equals(userFrom))
        {
            logger.error("白名单联系人来自（是企业通讯录的还是个人通讯录的）为空");
        }

        int isFrom = 0;

        if (userFrom.equalsIgnoreCase("企业通讯录"))
        {
            isFrom = 1;
        }
        else
        {
            isFrom = 0;
        }
        map.put("isFrom", isFrom);

        return map;
    }

    /**
     * 验证EXCEL文件
     * @param sheet
     * @return
     */
    private boolean checkExcelFile(HSSFSheet sheet)
    {
        HSSFRow row = null;

        HSSFCell cell = null;

        row = sheet.getRow(0);

        cell = row.getCell((short) 0);
        
        if (cell==null)
        {
            return false;
        }

        String userID = cell.getStringCellValue();

        if (!userID.equalsIgnoreCase(COULUMN_LINKMANNAME))
        {
            return false;
        }

        cell = row.getCell((short) 1);
        
        if (cell==null)
        {
            return false;
        }

        String from = cell.getStringCellValue();

        if (!from.equalsIgnoreCase(COULUMN_FROM))
        {
            return false;
        }
        return true;
    }

    /**
     * 封装错误消息
     * @param msg
     * @return
     */
    private String getTheErrorMsg(String msg)
    {
        StringBuffer errorMsg = new StringBuffer();
        errorMsg.append("<script language=\"javascript\">");
        errorMsg.append("alert('");
        errorMsg.append(msg);
        errorMsg.append("\');");
        errorMsg.append("</script>");
        String error = errorMsg.toString();
        return error;
    }
    
    /**
     * 根据不同的CELL类型，取出字符串格式的文本值: 
     * lizan 2008-3-12
     * @param cell
     * @return String
     */
    private String getStringCellValue(HSSFCell cell)
    {
        try
        {
            switch (cell.getCellType())
            {
                case HSSFCell.CELL_TYPE_STRING:
                    return cell.getStringCellValue();
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case HSSFCell.CELL_TYPE_NUMERIC:
                    return String.valueOf((int)cell.getNumericCellValue());
                default:
                    return "";
            }
        }
        catch(Exception e)
        {
            logger.error("获取单元格文本内容出错:", e);
            return "";
        }
    }
    
    /**
     * 通过文件路径，获取文件方法，在Windows和linux下适用
     * 注意，传入文件路径，需要截断第一个"/"字符
     * 
     * @param filePath 文件路径
     * 例:"d:\windows\abc.txt"or"var/log/abc.txt"
     * @return File对象
     * @throws Exception
     */
    private File filePath2File(String filePath) throws Exception
    {
        File temp = null;

        try
        {
            URL urlTmp = null;
            //判定是Windows操作系统,还是linux操作系统
            String osName = System.getProperty("os.name");

            //java在windows和linux下的URL存在不同，
            //既windows下是"file:///+文件路径",linux下是"file:/+文件路径"
            if (-1 == osName.toUpperCase().indexOf("WINDOWS"))
            {
                urlTmp = new URL("file:/" + filePath);
            }
            else
            {
                //如果存在Windows名称,说明是windows操作系统
                urlTmp = new URL("file:///" + filePath);
            }
            temp = new File(urlTmp.toURI());
        }
        catch (Exception e)
        {
            throw new Exception("文件路径转化异常，请确认文件路径正确，例如:/var/log/abcd.log。异常:" + e.getMessage());
        }
        if (temp != null && temp.exists())
        {
            return temp;
        }
        else
        {
            throw new Exception("文件路径不存在。文件路径:" + filePath);
        }
    }
}