poi实现excel的导入导出-CSDN博客

import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;

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

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.alibaba.fastjson.JSONObject;

/**
 * 
* @ClassName: ImportInfo 
* @Description: TODO(用户数据 导入) 
* @author liubf
* @date 2015年12月24日 下午3:50:19 
*
 */
@SuppressWarnings("serial")
public class CustManageAction extends BaseServlet {

    CustManageService custManageService = new CustManageServiceImpl();
    
    /**
     * 
    * @Title: importInfo 
    * @Description: TODO(批量导入数据) 
    * @param @param req
    * @param @param resp    设定文件 
    * @return void    返回类型
     * @throws Exception 
    * @throws 
    * @author liubf
    * @date 2015年12月24日 下午3:50:40
     */
    @SuppressWarnings("rawtypes")
    public void importInfo(HttpServletRequest request, HttpServletResponse response) throws Exception{
    
        try {
            String sellerId = (String)request.getSession().getAttribute("sellerId");
            ArrayList<HashMap<String,Object>> infoList = new ArrayList<HashMap<String,Object>>();
            String tempPath  = getServletContext().getRealPath("/temp");//缓存地址
            HashMap<String, Object> fileMap = FileUpload.fileToStream(request, tempPath);
            InputStream stream = (InputStream)fileMap.get("inputStream");
            String suffix = (String)fileMap.get("suffix");
            Workbook rwb = null;
                if((".xlsx").equals(suffix)){
                        rwb = new XSSFWorkbook(stream);//Excel 2007
                } else {
                    rwb = new HSSFWorkbook(stream);//Excel 2003
                }
                Sheet sheet = rwb.getSheetAt(0);
                //校验各列的数据准确性
                String title_userPhone= sheet.getRow(4).getCell(0).toString().trim();//手机号
                String title_userName = sheet.getRow(4).getCell(1).toString().trim();//姓名
                String title_userSex = sheet.getRow(4).getCell(2).toString().trim();//性别
                String title_carModel = sheet.getRow(4).getCell(3).toString().trim();//车型
                String title_carNumber = sheet.getRow(4).getCell(4).toString().trim();//车牌号
                String title_remarks = sheet.getRow(4).getCell(5).toString().trim();//备注
            if ( !"手机号".equals(title_userPhone) || !"姓名".equals(title_userName)
                    || !"性别".equals(title_userSex) || !"车型".equals(title_carModel)
                    || !"车牌号".equals(title_carNumber)|| !"备注".equals(title_remarks)) {
                throw new AppException("请检查模板是否正确,或下载使用新模板!");
            }
                for(int i=5;i<sheet.getPhysicalNumberOfRows();i++){
                    HashMap<String,Object> map = new HashMap<String,Object>();
                    String userPhone     = sheet.getRow(i).getCell(0).toString();
                    if(StringUtils.isEmpty(userPhone)){
                        throw new AppException("第"+i+1+"行手机号不能为空!");
                    }
                    String userName     = sheet.getRow(i).getCell(1).toString();
                    if(StringUtils.isEmpty(userName)){
                        throw new AppException("第"+i+1+"行姓名不能为空!");
                    }
                    String userSex         = sheet.getRow(i).getCell(2).toString();
                    String carModel     = sheet.getRow(i).getCell(3).toString();
                    String carNumber = sheet.getRow(i).getCell(4).toString();
                    String remarks         = sheet.getRow(i).getCell(5).toString();
                    map.put("userPhone", userPhone);
                    map.put("userName", userName);
                    map.put("userSex", userSex);
                    map.put("sellerId", sellerId);
                    map.put("carModel", carModel);
                    map.put("carNumber", carNumber);
                    map.put("remarks", remarks);
                    infoList.add(map);
                }
                if(infoList.size()<1){
                    throw new AppException("上传表格不能为空");
                }
                String msg = custManageService.importInfo(infoList);
                JSONObject jsonObject = new JSONObject();
                if(StringUtils.isEmpty(msg)){
                    msg = "导入成功!";
                }else{
                    msg = "导入成功!部分已存在的数据未进行导入,未导入的手机号为:"+msg;
                }
                jsonObject.put("msg", msg);
                jsonObject.put("success", "true");
                print(response, jsonObject);
        } catch (AppException e) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("msg", "导入失败!错误原因:"+e);
            jsonObject.put("success", "false");
            print(response, jsonObject);
            e.printStackTrace();
        }catch (Exception e) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("msg", "导入失败!请稍后重试.");
            jsonObject.put("success", "false");
            print(response, jsonObject);
            e.printStackTrace();
        }
        
    }
    
    /**
     * 
    * @Title: exportInfo 
    * @Description: TODO(用户信息导出) 
    * @param @param request
    * @param @param response
    * @param @throws Exception    设定文件 
    * @return void    返回类型 
    * @throws 
    * @author liubf
    * @date 2015年12月30日 下午2:42:00
     */
    public void exportInfo(HttpServletRequest request,HttpServletResponse response) throws Exception{
                String sellerId = "1061"  ;   // (String)request.getSession().getAttribute("sellerId");  写完登陆后改回来
                String fileName = "userInfo.xls";
                //根据浏览器进行判断文件名转码方式>>>>>>>
                String agent = request.getHeader("USER-AGENT");  
                if ((null != agent && -1 != agent.indexOf("MSIE")) || (null != agent   && -1 != agent.indexOf("Trident"))) {// ie  
                    fileName = URLEncoder.encode("用户信息表.xls","utf-8");
                } else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,chrome等  
                    fileName = new String("用户信息表.xls".getBytes("UTF-8"), "iso-8859-1");  
                //根据浏览器进行判断文件名转码方式<<<<<<<<<<
                HSSFWorkbook wb = custManageService.exportInfo(sellerId);    
                response.setContentType("application/vnd.ms-excel");    
                response.setHeader("Content-disposition", "attachment;filename="+fileName); 
                OutputStream ouputStream = response.getOutputStream();    
                wb.write(ouputStream);    
                ouputStream.flush();    
                ouputStream.close();    
           }  
}
}

service代码

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class CustManageServiceImpl  implements CustManageService{
    CustManageDao custManageDao= new CustManageDaoImpl();
    public String importInfo(ArrayList<HashMap<String,Object>> infoList) throws Exception {
        Connection conn = null;
        StringBuffer stringBuffer = new StringBuffer();
        try{
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);
            String sql_query = "select userPhone, userName from cbb_sellerMember where userPhone = ?";
            String sql_insert = "insert into cbb_sellerMember(userPhone,userName,userSex,sellerId,carModel,carNumber,remarks) values(?,?,?,?,?,?)";
            for(int i=0; i<infoList.size(); i++){
                HashMap<String,Object> rowMap = infoList.get(i);//取出每一行的数据
                String userPhone = String.valueOf(rowMap.get("userPhone"));
                String userName = String.valueOf(rowMap.get("userName"));
                String userSex = String.valueOf(rowMap.get("userSex"));
                String sellerId = String.valueOf(rowMap.get("sellerId"));
                String carModel = String.valueOf(rowMap.get("carModel"));
                String carNumber = String.valueOf(rowMap.get("carNumber" ));
                String remarks = String.valueOf(rowMap.get("remarks"));
                Object[] params = {userPhone};
                Map<String, Object> existInfo = custManageDao.query(conn,sql_query, params);//根据手机号查是否有此记录
                if(null == existInfo || null == existInfo.get("userPhone") ){
                    Object[] in_params = {userPhone,userName,userSex,sellerId,carModel,carNumber,remarks};
                    custManageDao.update(conn,sql_insert,in_params);//没有此记录的话则插入一条记录
                }else{
                    stringBuffer.append(userPhone).append(",");
                }
            }
            DbUtils.commitAndCloseQuietly(conn);
        }catch(Exception e){
            DbUtils.rollbackAndCloseQuietly(conn);
                throw e;
            }
        return stringBuffer.substring(0, stringBuffer.length()-1).toString();
    }
    
    
    public HSSFWorkbook exportInfo(String sellerId) throws Exception {
         String[] excelHeader = { "手机号", "姓名", "性别","车型","车牌号","备注"}; 
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet("会员信息");    
                HSSFRow row = sheet.createRow((int) 0);    
                HSSFCellStyle style = wb.createCellStyle();    
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);    
                //设置header栏
                for (int i = 0; i < excelHeader.length; i++) {
                    HSSFCell cell = row.createCell(i);
                    cell.setCellValue(excelHeader[i]);
                    cell.setCellStyle(style);
                    sheet.autoSizeColumn(i);
                    sheet.setColumnWidth(i, 5000);
                }    
                
                List<Map<String, Object>> customerList = custManageDao.querySellerMember( sellerId);
                for (int i = 0; i < customerList.size(); i++) {
                    row = sheet.createRow(i + 1);    
                    Map<String, Object> customer = customerList.get(i);    
                    row.createCell(0).setCellValue(String.valueOf(customer.get("userPhone")));    
                    row.createCell(1).setCellValue(String.valueOf(customer.get("userName")) == null ? "" : String.valueOf(customer.get("userName")));   
                    String sex = String.valueOf(customer.get("userSex"));
                    if("1".equals(sex)){
                        sex = "男";
                    }else if("0".equals(sex)){
                        sex = "女";
                    }else{
                        sex = "";
                    }
                    row.createCell(2).setCellValue(sex);    
                    row.createCell(3).setCellValue(String.valueOf(customer.get("carModel")) == null ? "" : String.valueOf(customer.get("carModel")) );    
                    row.createCell(4).setCellValue(String.valueOf(customer.get("carNumber"))== null ? "" : String.valueOf(customer.get("carNumber")));    
                    row.createCell(5).setCellValue(String.valueOf(customer.get("remarks")) == null ? "" : String.valueOf(customer.get("remarks")) );    
                }    
                return wb;    
    }

}