poi实现excel的导入导出
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; } }
