1.从jsp页面提交上传excel文件
<script type="text/javascript">
jQuery(document).ready(function(){
jQuery("#templetId").click(function(){
var btn = $(this);
btn.button('loading');
setTimeout(function () {btn.button('reset')}, 2000);
var url="/cancelInsurance/gettemplet.do";
$("#getTemplet").attr("action",url);
$("#getTemplet").submit();
});
});
<form:form id="getTemplet" action=""></form:form>
<form:form id="cancelInfo" action="cancelInsuranceEdit.do" method="post" enctype="multipart/form-data">
<div class="control-group">
<div class="controls">
<label class="control-label">选择退保文件:</label>
<input type="file" name="cancelFiles" id="cancelFiles" accept=".xls,.xlsx,application/vnd.ms-excel" class="required"/>
<button class="btn btn-info" data-loading-text="loading..." id="templetId" type="button">点击下载退保模板</button>
</div>
</div><br/><br/><br/>
<div>
<input id="btnSubmit" class="btn btn-primary btn-large" type="submit" data-loading-text="loading..." value="确认退保" style="margin-left: 200px;width: 200px;"/>
</div>
</form:form>
其中,点击获取模板访问/cancelInsurance/gettemplet.do,获取模板的java代码如下
@RequestMapping(value = "gettemplet", method = RequestMethod.POST)
public void getTemplate(HttpServletRequest request,
RedirectAttributes redirectAttributes, HttpServletResponse response) {
OutputStream out = null;
InputStream is = null;
try {
is = new BufferedInputStream(this.getClass().getResourceAsStream(
"/templates/jdCancelInsurance.xls"));//找到项目中存储的.xls文件
ExcelUtil.setExcelResponse(response,"批量退保模板");
Map beans = Maps.newHashMap();
beans.put("loginName", UserUtils.getUser().getLoginName());
Workbook workbook = WriteExcel.write(is, beans);
// 将内容写入输出流并把缓存的内容全部发出去
out = response.getOutputStream();
workbook.write(out);
out.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
}
}
}
}
2.解析.xls文件(controller),java代码如下
ReadExcels readExcel=new ReadExcels();
//解析excel,获取客户信息集合。
List<CancleInsuranceInfo> customerList = readExcel.getExcelInfo(filename ,multipartFile);
3.工具类,java代码如下
public class ReadExcels {
//总行数
private int totalRows = 0;
//总条数
private int totalCells = 0;
//错误信息接收器
private String errorMsg;
//构造方法
public ReadExcels(){}
//获取总行数
public int getTotalRows() { return totalRows;}
//获取总列数
public int getTotalCells() { return totalCells;}
//获取错误信息
public String getErrorInfo() { return errorMsg; }
/**
* 验证EXCEL文件
* @param filePath
* @return
*/
public boolean validateExcel(String filePath){
if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))){
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
public List<CancleInsuranceInfo> getExcelInfo(String fileName,MultipartFile multipartFile){
//初始化退保信息的集合
List<CancleInsuranceInfo> customerList=new ArrayList<CancleInsuranceInfo>();
//初始化输入流
InputStream is = null;
try{
//验证文件名是否合格
if(!validateExcel(fileName)){
return null;
}
//根据文件名判断文件是2003版本还是2007版本
boolean isxls = true;
if(WDWUtil.isxlsx(fileName)){
isxls = false;
}
//根据新建的文件实例化输入流
is = multipartFile.getInputStream();
//根据excel里面的内容读取客户信息
customerList = getExcelInfo(is, isxls);
is.close();
}catch(Exception e){
e.printStackTrace();
} finally{
if(is !=null)
{
try{
is.close();
}catch(IOException e){
is = null;
e.printStackTrace();
}
}
}
return customerList;
}
/**
* 根据excel里面的内容读退保信息
* @param isxls excel是xls文件还是xlsx文件
* @return
* @throws IOException
*/
public List<CancleInsuranceInfo> getExcelInfo(InputStream is,boolean isxls){
List<CancleInsuranceInfo> cancleInsuranceInfo=null;
try{
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
//当excel是xls时
if(isxls){
wb = new HSSFWorkbook(is);
}
else{//当excel是xlsx文件时
wb = new XSSFWorkbook(is);
}
//读取Excel里面退保信息
cancleInsuranceInfo=readExcelValue(wb);
}
catch (IOException e) {
e.printStackTrace();
}
return cancleInsuranceInfo;
}
/**
* 读取Excel里面的信息
* @param wb
* @return
*/
private List<CancleInsuranceInfo> readExcelValue(Workbook wb){
//得到第一个shell
Sheet sheet=wb.getSheetAt(0);
//得到Excel的行数
this.totalRows=sheet.getPhysicalNumberOfRows();
//得到Excel的列数
if(totalRows>=1 && sheet.getRow(0) != null){
this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
}
List<CancleInsuranceInfo> cancleInsuranceInfos=new ArrayList<CancleInsuranceInfo>();
CancleInsuranceInfo cancleInsuranceInfo;
//循环Excel行数,从第二行开始。标题不进行统计
for(int r=1;r<totalRows;r++){
Row row = sheet.getRow(r);
if (row == null) continue;
cancleInsuranceInfo = new CancleInsuranceInfo();
//循环取出Excel的列
for(int c = 0; c <this.totalCells; c++){
Cell cell = row.getCell(c);
cell.setCellType(Cell.CELL_TYPE_STRING);
if (null != cell){
if(c==0){//取出第一列
cancleInsuranceInfo.setServerNumber(cell.getStringCellValue());
}else if(c==4){//取出第五列
cancleInsuranceInfo.setBackCode(cell.getStringCellValue());
break;
}
}
}
//添加退保信息
cancleInsuranceInfos.add(cancleInsuranceInfo);
}
return cancleInsuranceInfos;
}
}