关于excel导入的类和方法

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;


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


/**
 *主类
 */
@Controller
public class ImportExcel {


    @Autowired
    private ImportExcelService importExcelService;


    @RequestMapping(value = "/excel",method = RequestMethod.GET)
    public String excel(){
        return "importExcel";
    }


    @RequestMapping(value = "/excel",method = RequestMethod.POST)
    public String excel(@RequestParam(value= "filename") MultipartFile file, HttpServletRequest request, HttpServletResponse  response, HttpSession session   ){
        //判断文件是否为空
        if(file==null){
            session.setAttribute("msg","文件不能为空！");
            return "redirect:toUserKnowledgeImport";
        }


        //获取文件名
        String fileName=file.getOriginalFilename();


        //验证文件名是否合格
        if(!ImportExcelUtils.validateExcel(fileName)){
            session.setAttribute("msg","文件必须是excel格式！");
            return "redirect:toUserKnowledgeImport";
        }


        //进一步判断文件内容是否为空（即判断其大小是否为0或其名称是否为null）
        long size=file.getSize();
        if(StringUtils.isEmpty(fileName) || size==0){
            session.setAttribute("msg","文件不能为空！");
            return "redirect:toUserKnowledgeImport";
        }


        //批量导入
        String message = importExcelService.batchImport(fileName,file);
        session.setAttribute("msg",message);
        return "redirect:toUserKnowledgeImport";




    }


    @RequestMapping("/toUserKnowledgeImport")
    public String toUserKnowledgeImport(){
        return "toUserKnowledgeImport";
    }
}

import org.springframework.web.multipart.MultipartFile;


/**
 * 服务类
 */
public interface ImportExcelService {
    public String batchImport(String fileName, MultipartFile mfile);
}

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;


import com.sljr.slmall.data.vo.product.ImportProductVo;
import com.sljr.slmall.product.srv.service.ProductService;


import lombok.extern.slf4j.Slf4j;


/**
 * 实现类
 */
@Service
@Slf4j
public class ImportExcelServiceImpl implements ImportExcelService {


@Autowired
ProductService productService;


public String batchImport(String fileName, MultipartFile mfile) {
// 初始化输入流
InputStream is = null;
try {


is = mfile.getInputStream();
// 根据版本选择创建Workbook的方式，这个方法的话无论是2003版还是2007版本都可以
Workbook wb =  WorkbookFactory.create(mfile.getInputStream());
// 根据excel里面的内容读取知识库信息
return readExcelValue(wb);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
is = null;
e.printStackTrace();
}
}
}
return "导入出错！请检查数据格式！";
}


/**
* 解析Excel里面的数据
* 
* @param wb
* @return
*/
private String readExcelValue(Workbook wb) {


// 错误信息接收器
String errorMsg = "";
// 得到第一个shell
Sheet sheet = wb.getSheetAt(0);
// 得到Excel的行数
int totalRows = sheet.getPhysicalNumberOfRows();
// 总列数
int totalCells = 0;
// 得到Excel的列数(前提是有行数)，从第二行算起
if (totalRows >= 2 && sheet.getRow(1) != null) {
totalCells = sheet.getRow(1).getPhysicalNumberOfCells();
}
List<ImportProductVo> productList = new ArrayList<ImportProductVo>();
ImportProductVo tempProduct = new ImportProductVo();
String br = "<br/>";
// 循环Excel行数,从第二行开始。标题不入库
for (int r = 1; r < totalRows; r++) {
String rowMessage = "";
Row row = sheet.getRow(r);
if (row == null) {
errorMsg += br + "第" + (r + 1) + "行数据有问题，请仔细检查！";
continue;
}
tempProduct = new ImportProductVo();
Integer sku = null;
BigDecimal price = new BigDecimal("0");
// 循环Excel的列
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) {
sku = Integer.parseInt(getValue(cell));
if (StringUtils.isEmpty(sku)) {
rowMessage += "sku不能为空；";
}
tempProduct.setSku(sku);
} else if (c == 3) {
price = new BigDecimal(getValue(cell));
if (StringUtils.isEmpty(price)) {
rowMessage += "商品名称不能为空；";
}
tempProduct.setPrice(price);

}
} else {
rowMessage += "第" + (c + 1) + "列数据有问题，请仔细检查；";
}
}


//拼接每行的错误提示
if(!StringUtils.isEmpty(rowMessage)){
errorMsg += br+"第"+(r+1)+"行，"+rowMessage;
}

// 这部分是用来做属性验证的，比如名字必须是汉字啊，电话号必须是11位之类的，使用valid框架，就先注掉了
// else
// if(!StringUtils.isEmpty(ValidateUtil.validateModel(tempProduct))){
// errorMsg += br+"第"+(r+1)+"行,"+
// ValidateUtil.validateModel(tempProduct);
// }
else{
productList.add(tempProduct);
}
}
// 全部验证通过才导入到数据库
if (StringUtils.isEmpty(errorMsg)) {
int count = productService.updatePriceBySku(productList);
errorMsg = "导入成功,共" + productList.size() + "条数据,修改了"+count+"条数据";
}
return errorMsg;
}


public String getValue(Cell cell) {
String value = "";
if (null == cell) {
return value;
}
switch (cell.getCellType()) {
// 数值型
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是date类型则 ，获取该cell的date值
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
value = format.format(date);
;
} else {// 纯数字
//BigDecimal big = new BigDecimal(cell.getNumericCellValue());
double big = cell.getNumericCellValue();
value = String.valueOf(big);
// 解决1234.0 去掉后面的.0
if (null != value && !"".equals(value.trim())) {
String[] item = value.split("[.]");
if (1 < item.length && "0".equals(item[1])) {
value = item[0];
}
}
}
break;
// 字符串类型
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().toString();
break;
// 公式类型
case Cell.CELL_TYPE_FORMULA:
// 读公式计算值
value = String.valueOf(cell.getNumericCellValue());
if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
value = cell.getStringCellValue().toString();
}
break;
// 布尔类型
case Cell.CELL_TYPE_BOOLEAN:
value = " " + cell.getBooleanCellValue();
break;
// 空值
case Cell.CELL_TYPE_BLANK:
value = "";
log.error("excel出现空值");
break;
// 故障
case Cell.CELL_TYPE_ERROR:
value = "";
log.error("excel出现故障");
break;
default:
value = cell.getStringCellValue().toString();
}
if ("null".endsWith(value.trim())) {
value = "";
}
return value;


}
}