java简单使用poi导入、导出数据

废话少说，直接上代码。

1，导出数据：从数据库读取数据封装到Workbook中，并通过流输出到客户端浏览器。 

AccountMapper.xml

	<!-- 导出用户使用 -->
	<select id="searchList" resultType="Account" useCache="true">
		select 
			a.phone,a.name,v.name as vipName,a.integral,a.email,a.status,a.gmtCreate 
		from 
			account a 
		join 
			vip v 
		on 
	 		a.integral between v.lowVip and v.higVip
	 	<trim  prefix="where"  prefixOverrides="and|or">
			<if test="status != null ">
				and a.status = #{status}
			</if>
			<if test="vid != null ">
				and v.id = #{vid}
			</if>
		</trim>
		order by a.gmtModified desc,a.gmtCreate desc
	</select>

AccountDao.java

public interface AccountDao {
	/**
	 * 查询所有用户
	 * @return
	 */
	public List<Account> searchList(Map<String,Object> map);
}

AccountService.java

public interface AccountService {

	/**
	 * 
	 * @return
	 */
	public List<Account> searchList(Integer status,Integer vid);
	
}

AccountServiceImpl.java

@Service("accountService")
public class AccountServiceImpl implements AccountService {

	@Autowired
	private AccountDao accountDao;
	
	/**
	 * 查询所有用户
	 */
	@Override
	public List<Account> searchList(Integer status,Integer vid) {
		Map<String,Object> map = new HashMap<String,Object>();
		map.put("status",status);
		map.put("vid", vid);
		return accountDao.searchList(map);
	}

}

AccountController.java

@RequestMapping("/account")
@Controller("accountController")
public class AccountController {

	protected Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private AccountService accountService;
	
	/**
	 * 导出所有用户
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@ResponseBody
	@RequestMapping("/searchList")
	public String searchList(HttpServletRequest request,HttpServletResponse response,Integer status,Integer vid) throws Exception {
		String fileName ="account.xls";
		Workbook wb = new HSSFWorkbook();
		String[] columnNames = {"账号","呢称","会员","积分","邮箱","状态","注册日期"};
		String[] keys = {"phone","name","vipName","integral","email","status","gmtCreate"};
		List<Account> accounts = accountService.searchList(status,vid);
		List<Map<String, Object>> listmap = createExcelRecord(accounts);
		PoiUtil.excelData(listmap, wb, keys,columnNames);
		ResponseUtil.export(response, fileName, wb);
		return JSONResult.create("null").toJSON();
	}
	/**
	 * 将数据封装 List<Map<String,Object>>
	 * @param account
	 * @return
	 */
	private List<Map<String,Object>> createExcelRecord(List<Account> account){
		List<Map<String,Object>> listmap = new ArrayList<Map<String,Object>>();
		for(int i=0;i<account.size();i++) {
			Map<String,Object> map = new HashMap<String,Object>();
			map.put("phone", account.get(i).getPhone());
			map.put("name", account.get(i).getName());
			map.put("vipName", account.get(i).getVipName());
			map.put("integral",account.get(i).getIntegral());
			map.put("email",account.get(i).getEmail()==null?"":account.get(i).getEmail());
			map.put("status",getStatusInfo(account.get(i).getStatus()));
			map.put("gmtCreate",DateUtil.formatDateTime(account.get(i).getGmtCreate()));
			listmap.add(map);
		}
		return listmap;
	}
	/**
	 * 获取状态信息
	 * 
	 * @param status
	 * @return
	 */
	private String getStatusInfo(Integer status) {
		Map<Integer, String> map = new HashMap<Integer, String>();
		map.put(0, "正常");
		map.put(1, "已删除");
		map.put(2, "待审核");
		return map.get(status);
	}
}

PoiUtil.java

@SuppressWarnings("all")
public class PoiUtil {
	public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
	
	/*
	 * getLastRowNum:  
     * 		如果sheet中一行数据都没有则返回-1，只有第一行有数据则返回0，最后有数据的行是第n行则返回 n-1；
	 * getLastCellNum   
     *		如果row中一列数据都没有则返回-1，只有第一列有数据则返回1，最后有数据的列是第n列则返回 n；
	 * getPhysicalNumberOfRows  
     *      获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m；
	 * getPhysicalNumberOfCells   
     *		获取有记录的列数，即：最后有数据的列是第n列，前面有m列是空列没数据，则返回n-m；
	 */
	
	/**
	 *  将数据写入到表格文件
	 * @param entitys         select的数据
	 * @param wb              表格
	 * @param keys           
	 * @param columnNames     列名
	 */
	public static void excelData(List<Map<String,Object>> entitys, Workbook wb,String[] keys,String[] columnNames) {
		int rowIndex =0;
		Sheet sheet = wb.createSheet();
		Row row = sheet.createRow(rowIndex++);
		for(int i=0;i<columnNames.length;i++) {
			row.createCell(i).setCellValue(columnNames[i]);
		}
		for(int i=0;i<entitys.size();i++) {
			row=sheet.createRow(i+1);
			for(int j=0;j<keys.length;j++) {
				row.createCell(j).setCellValue(entitys.get(i).get(keys[j]).toString());
			}
		}
	}
	/**
	 * 读取模板文件   将数据写入到模板文件
	 * @param list       select的数据
	 * @param keys       列名
	 * @param fileName   模板文件名
	 * @return
	 * @throws Exception
	 */
	public static Workbook excelTemplate(List<Map<String,Object>> list,String[] keys,String fileName) throws Exception {
		InputStream is = PoiUtil.class.getResourceAsStream(fileName);
		POIFSFileSystem poi = new POIFSFileSystem(is);
		Workbook wb = new HSSFWorkbook(poi);
		Sheet sheet = wb.getSheetAt(0);
		int cellNums = sheet.getRow(0).getLastCellNum();
		for(int i=1;i<list.size();i++) {
			Row row = sheet.createRow(i);
			for(int j=0;j<cellNums;j++) {
				row.createCell(j).setCellValue(list.get(i).get(keys[j]).toString());
			}
		}
		return wb;
	}
	/**
	 * 获取HSSFCell类型value：
     *   将单元格类型都设置为String,直接操作字符串，很方便。此方法虽然有点过激，不需要判断复杂的
     *   单元格，很实用。
	 * @param cell
	 * @return
	 */
	public static String formatCell(HSSFCell cell) {
		//如果cell为null直接return
		if(null == cell) {
			return "";
		}else{
			//设置所有单元格为String类型
			cell.setCellType(cell.CELL_TYPE_STRING);
			// 获取String类型单元格内容
			return String.valueOf(cell.getStringCellValue());
		}
	}
	/**
	 *      获取hssfcell单元格格式内容
	 * 
	 * @param hssfCell
	 * @return
	 */
	public static String getHSSFCellValue(HSSFCell hssfcell) {
		if (hssfcell.getCellType() == hssfcell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfcell.getBooleanCellValue());
		} else if (hssfcell.getCellType() == hssfcell.CELL_TYPE_NUMERIC) {
			String cellValue = "";
			if (HSSFDateUtil.isCellDateFormatted(hssfcell)) {
				Date date = HSSFDateUtil.getJavaDate(hssfcell.getNumericCellValue());
				cellValue = sdf.format(date);
			} else {
				DecimalFormat df = new DecimalFormat("#.##");
				cellValue = df.format(hssfcell.getNumericCellValue());
				String strArr = cellValue.substring(cellValue.lastIndexOf("") + 1, cellValue.length());
				if (strArr.equals("00")) {
					cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));
				}
			}
			return cellValue;
		}else if(hssfcell.getCellType() ==HSSFCell.CELL_TYPE_ERROR) {
			return "";
		}else {
			return String.valueOf(hssfcell.getStringCellValue());
		}
	}
	/** 
     *      获取XSSFCell单元格格式的内容
     * @param xssfCell 
     * @return 
     */  
    public static String getXSSFCellValue(XSSFCell xssfCell){  
         if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
             return String.valueOf(xssfCell.getBooleanCellValue());  
         } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {  
             String cellValue = "";  
             DecimalFormat df = new DecimalFormat("#.##");  
             cellValue = df.format(xssfCell.getNumericCellValue());  
             String strArr = cellValue.substring(cellValue.lastIndexOf(".")+1,cellValue.length());  
             if(strArr.equals("00")){  
                 cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));  
             }    
             return cellValue;  
         }else if(xssfCell.getCellType()==HSSFCell.CELL_TYPE_ERROR) {
				return "";
		 } else {  
        	 return String.valueOf(xssfCell.getStringCellValue());  
         }  
    }
	
	
}

ResponseUtil.java

public class ResponseUtil {
	
	public static void export(HttpServletResponse response,String fileName,Workbook wb) throws Exception{
		fileName = URLEncoder.encode(fileName,"UTF-8");  
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);    
        response.setContentType("multipart/form-data");   
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
	}
	
	
}

实体类很简单，前端页面，我不是很在行，就不贴了。

          注：如果数据过大暂不支持使用。

 

 

2，导入数据：读取excel表格，将数据封装到List容器，然后批量插入数据库

AccountMapper.xml

		
 	<!-- batchInsert -->
 	<insert id="batchInsert" parameterType="java.util.List">
 		insert into account(name,password) values
	    <foreach collection="list" item="item" index="index" separator=",">
	   		(#{item.name},#{item.password})
	   	</foreach>
		<selectKey resultType="int" keyProperty="id" order="AFTER">
			select @@identity
		</selectKey>
 	</insert>

AccountDao.java

public interface AccountDao{
	/**
	 * 批量新增
	 * @param list
	 */
	public void batchInsert(List<Account> list);
	
}

AccountService.java

public interface AccountService{
	
	/**
	 * 批量新增
	 * @param list
	 */
	public void batchInsert(List<Account> list);
	
}

AccountServiceImpl.java

@Service("accountService")
public class AccountServiceImpl implements AccountService{
	
	@Autowired
	private AccountDao accountDao;
	
	@Override
	public void batchInsert(List<Base> list) {
		accountDao.batchInsert(list);
	}
}

AccountController.java

@RequestMapping("/account")
@Controller("accountController")
public class AccountController {

	protected Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private AccountService accountService;
    
    /**
	 * excel批量新增数据
	 * @param file
	 * @param request
	 * @return
	 * @throws IOException
	 */
	@ResponseBody
	@RequestMapping("batchInsert")
	public String batchInsert(@RequestParam("file")CommonsMultipartFile file,HttpServletRequest request) throws IOException {
		//读取流
		InputStream is = file.getInputStream();
		//根据is流创建wb
		HSSFWorkbook wb = new HSSFWorkbook(is);
		//获取页
		HSSFSheet sheet = wb.getSheetAt(0);
		if(sheet != null) {
			//过滤表格数据到List集合
			List<Account> accountList = filterSheet(sheet);
			accountService.batchInsert(accountList);
			return JSONResult.create("上传成功").toJSON();
		}
		return JSONResult.failCreate("上传失败", "").toJSON();
	}
    /**
	 * 读取表格数据加入到List容器
	 * @return
	 */
	private List<Account> filterSheet(HSSFSheet sheet){
		// 作返回值
		List<Account> list = null;
		// 为null直接返回
		if(null != sheet) {
			list = new ArrayList<Base>();;
			//循环读取表格数据
			for(int i=1;i<sheet.getLastRowNum()+1;i++) {
				HSSFRow row = sheet.getRow(i);
				//当前行为null
				if(null == row) {
					continue;
				}
				// 第二列开始读,当前列如果没有数据，
				String name = PoiUtil.formatCell(row.getCell(1));
				String password = PoiUtil.formatCell(row.getCell(2));
				//name为null或空串直接返回,不要添加到List集合
				if(StringUtils.isBlank(name)) {
					continue;
				}
				//如果没有指定密码，给默认密码
				if(StringUtils.isBlank(password)) {
					password="123456";
				}
				//封装数据到实体类中
				Account account= new Account(name,password);
				//封装对象到List
				list.add(base);
			}
			return list;
		}
		return null;
	}
}

PoiUtil.java

@SuppressWarnings("all")
public class PoiUtil {
	public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
	
	/*
	 * getLastRowNum:  
     * 		如果sheet中一行数据都没有则返回-1，只有第一行有数据则返回0，最后有数据的行是第n行则返回 n-1；
	 * getLastCellNum   
     *		如果row中一列数据都没有则返回-1，只有第一列有数据则返回1，最后有数据的列是第n列则返回 n；
	 * getPhysicalNumberOfRows  
     *      获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m；
	 * getPhysicalNumberOfCells   
     *		获取有记录的列数，即：最后有数据的列是第n列，前面有m列是空列没数据，则返回n-m；
	 */
	
	/**
	 *  将数据写入到表格文件
	 * @param entitys         select的数据
	 * @param wb              表格
	 * @param keys           
	 * @param columnNames     列名
	 */
	public static void excelData(List<Map<String,Object>> entitys, Workbook wb,String[] keys,String[] columnNames) {
		int rowIndex =0;
		Sheet sheet = wb.createSheet();
		Row row = sheet.createRow(rowIndex++);
		for(int i=0;i<columnNames.length;i++) {
			row.createCell(i).setCellValue(columnNames[i]);
		}
		for(int i=0;i<entitys.size();i++) {
			row=sheet.createRow(i+1);
			for(int j=0;j<keys.length;j++) {
				row.createCell(j).setCellValue(entitys.get(i).get(keys[j]).toString());
			}
		}
	}
	/**
	 * 读取模板文件   将数据写入到模板文件
	 * @param list       select的数据
	 * @param keys       列名
	 * @param fileName   模板文件名
	 * @return
	 * @throws Exception
	 */
	public static Workbook excelTemplate(List<Map<String,Object>> list,String[] keys,String fileName) throws Exception {
		InputStream is = PoiUtil.class.getResourceAsStream(fileName);
		POIFSFileSystem poi = new POIFSFileSystem(is);
		Workbook wb = new HSSFWorkbook(poi);
		Sheet sheet = wb.getSheetAt(0);
		int cellNums = sheet.getRow(0).getLastCellNum();
		for(int i=1;i<list.size();i++) {
			Row row = sheet.createRow(i);
			for(int j=0;j<cellNums;j++) {
				row.createCell(j).setCellValue(list.get(i).get(keys[j]).toString());
			}
		}
		return wb;
	}
	/**
	 * 获取HSSFCell类型
	 * @param cell
	 * @return
	 */
	public static String formatCell(HSSFCell cell) {
		//如果cell为null直接return
		if(null == cell) {
			return "";
		}else{
			//设置所有单元格为String类型
			cell.setCellType(cell.CELL_TYPE_STRING);
			// 获取String类型单元格内容
			return String.valueOf(cell.getStringCellValue());
		}
	}
	/**
	 *      获取hssfcell单元格格式内容
	 * 
	 * @param hssfCell
	 * @return
	 */
	public static String getHSSFCellValue(HSSFCell hssfcell) {
		if (hssfcell.getCellType() == hssfcell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfcell.getBooleanCellValue());
		} else if (hssfcell.getCellType() == hssfcell.CELL_TYPE_NUMERIC) {
			String cellValue = "";
			if (HSSFDateUtil.isCellDateFormatted(hssfcell)) {
				Date date = HSSFDateUtil.getJavaDate(hssfcell.getNumericCellValue());
				cellValue = sdf.format(date);
			} else {
				DecimalFormat df = new DecimalFormat("#.##");
				cellValue = df.format(hssfcell.getNumericCellValue());
				String strArr = cellValue.substring(cellValue.lastIndexOf("") + 1, cellValue.length());
				if (strArr.equals("00")) {
					cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));
				}
			}
			return cellValue;
		}else if(hssfcell.getCellType() ==HSSFCell.CELL_TYPE_ERROR) {
			return "";
		}else {
			return String.valueOf(hssfcell.getStringCellValue());
		}
	}
	/** 
     *      获取XSSFCell单元格格式的内容
     * @param xssfCell 
     * @return 
     */  
    public static String getXSSFCellValue(XSSFCell xssfCell){  
         if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
             return String.valueOf(xssfCell.getBooleanCellValue());  
         } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {  
             String cellValue = "";  
             DecimalFormat df = new DecimalFormat("#.##");  
             cellValue = df.format(xssfCell.getNumericCellValue());  
             String strArr = cellValue.substring(cellValue.lastIndexOf(".")+1,cellValue.length());  
             if(strArr.equals("00")){  
                 cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));  
             }    
             return cellValue;  
         }else if(xssfCell.getCellType()==HSSFCell.CELL_TYPE_ERROR) {
				return "";
		 } else {  
        	 return String.valueOf(xssfCell.getStringCellValue());  
         }  
    }
	
	
}

 

到此简单使用poi导入、导出数据操作完毕，不过导入数据操作效率很差，有兴趣的朋友可以参考我的另外两篇博客：

java通过poi上传excel表格并多线程批量插入数据到数据库

java通过poi上传excel表格并简单使用生产者、消费者模式批量插入数据到数据库