java之——poi导入导出

 导入

<!-- 模态框（Modal） -->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
	<div class="modal-dialog">
		<div class="modal-content">
			<div class="modal-header">
				<button type="button" class="close" data-dismiss="modal" aria-hidden="true">
					&times;
				</button>
				<h4 class="modal-title" id="myModalLabel">
					导入
				</h4>
			</div>
			<div class="modal-body">
				<form id="signupListImportForm" class="import-file-form" enctype="multipart/form-data">
					<input type="file" name="excelFile" id="excelFile" style="width:160px" value="选择文件">
				</form>
				<a href="${contextPath}/static/perCredential_template.xlsx" download="${contextPath}/static/perCredential_template.xlsx">模板下载</a>
			</div>
			<div class="modal-footer">
				<button type="button" class="btn btn-default" data-dismiss="modal">关闭
				</button>
				<button type="button" class="btn btn-success" @click="importExcel">导入
				</button>
			</div>
		</div><!-- /.modal-content -->
	</div><!-- /.modal -->
</div>

<script src="${contextPath}/static/libs/jquery.form.js"></script>
<script>
importExcel: function (){
	var excelFile = $("#excelFile").val();
	 
	//如果文件为空
	if (excelFile == '') {

		alert('请上传excel文件!');
		return;
	}

	//如果文件不是xls或者xlsx 提示输入正确的excel文件
	if ((excelFile.indexOf('.xls') == -1 && excelFile.indexOf('.xlsx') == -1)) {

		alert('请上传正确的excel,后缀名为xls或xlsx!');
		return;
	}

	var option = {
		url : contextPath + "/per/percredential/importExcel",
		type : 'POST',
		clearForm: true,
		success : function(r) {
			alert(r);
			$("#myModal").modal('hide');
			$("#jqGrid").jqGrid('setGridParam',{ 
				postData:{'query': JSON.stringify(vm.q)},
				page:1
			}).trigger("reloadGrid");
		}
	};
	$("#signupListImportForm").ajaxSubmit(option);
	return false;
}
</script>

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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
 /**
     * 导入
     */
    @Transactional
  	public @ResponseBody String importExcel(@RequestParam(value = "excelFile", required = false)MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {
    	InputStream inputStream = file.getInputStream();
    	Workbook wb;
    	String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
    	if (suffix.equalsIgnoreCase("xls")) {
    		wb = new HSSFWorkbook(inputStream);
		}else {
			wb = new XSSFWorkbook(inputStream);
		}
		Sheet sheet = wb.getSheetAt(0);
		Row row = null;
		int rows = sheet.getPhysicalNumberOfRows();
  		/*
  		 * 校验
  		 * */
  		try{
  			for (int i = 1; i < rows; i++) {
  				row = sheet.getRow(i); 
				//字符串
  				row.getCell(0).getStringCellValue();
				//时间
				row.getCell(1).getDateCellValue();
				//浮点类型
				row.getCell(2).getNumericCellValue();
  			}
  		}catch(Exception e){  			
  			return "导入失败";
  		}
  		return "导入成功";
  	}

导出 

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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


 public void export(List<PerCredentialExport> list,Integer i,HttpServletRequest request, HttpServletResponse response){
    	HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFRow row = sheet.createRow(0);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
   		//设置excel头信息
   		row.createCell(0).setCellValue("员工编号");
		row.createCell(1).setCellValue("姓名");
		row.createCell(2).setCellValue("岗位");
		int index = 1;
     	for (PerCredentialExport perCredentialExport : list) {
			//填充excel内容
			row = sheet.createRow(index++);
			row.createCell(0).setCellValue(perCredentialExport.getWorkNo());
			row.createCell(1).setCellValue(perCredentialExport.getUserName());
			row.createCell(2).setCellValue(perCredentialExport.getDutyName());
			}
		}
     	response.setContentType("application/vnd.ms-excel; charset=utf-8");
		SimpleDateFormat sdff = new SimpleDateFormat("yyyyMMddHHmmss");
		//excel文件名称
		String filename = "XXX" + sdff.format(new Date()) + ".xls";

		// 解决中文乱码
		// 从请求头中获取浏览器类型
		//String agent = request.getHeader("User-Agent");
		request.getHeader("User-Agent");
		// 需要fileUtils工具类
		// 使用工具类根据浏览器类型编码
		// fileUtils.new String(fileName.getBytes(),"iso-8859-1"；
		
		try {
			response.setHeader("Content-disposition",
					"attachment;filename=" + new String(filename.getBytes(), "iso-8859-1"));
			ServletOutputStream os = response.getOutputStream();
			wb.write(os);
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
    }

仅供参考