平时我们写日期格式的时候是这样2020-12-20 23:30:23
当你写进excel表格时候会自动变成这样2019/10/1 15:57:45
后台接收的时候打印出来是这样的,并且本来值为1的变成1.0
解决:
/**
* 解析POI导入Excel中日期格式数据
* @param currentCell
* @return currentCellValue
*/
public static String importByExcelForDate(Cell currentCell) {
String currentCellValue = "";
// 判断单元格数据是否是数值型
if (0 == currentCell.getCellType()) {
// 判断单元格数据是否是日期
if (DateUtil.isCellDateFormatted(currentCell)) {
// 用于转化为日期格式
Date d = currentCell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
currentCellValue = formater.format(d);
} else {
// 把单元格数据当做文本,解决数字1变成1.0的问题
currentCell.setCellType(currentCell.CELL_TYPE_STRING);
currentCellValue = currentCell.toString();
}
} else {
// 不是日期原值返回
currentCellValue = currentCell.toString();
}
return currentCellValue;
}
下面是完整代码
/**
* @Title:
* @Description: 上传excel表格
* @param
* @return
* @throws
*/
@RequestMapping(value = "/upload", method = { RequestMethod.POST, RequestMethod.GET })
public String uploadExcel(Model model, HttpServletRequest request, HttpSession session) throws Exception {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("filename");
if (file.isEmpty()) {
return "redirect:/product/toProduct";
}
InputStream inputStream = file.getInputStream();
List<List<Object>> list = excelUtil.getBankListByExcel(inputStream, file.getOriginalFilename());
inputStream.close();
ProductInfo productInfo = new ProductInfo();
//下面是把处理好的结果一个一个放到对象中,保存数据库
for (int i = 0; i < list.size(); i++) {
try {
List<Object> lo = list.get(i);
productInfo.setVin(lo.get(0).toString());
productInfo.setProductType(Integer.parseInt(lo.get(1).toString()));
productInfo.setProductName(lo.get(2).toString());
productInfo.setProductNo(lo.get(3).toString());
productInfo.setIccId(lo.get(4).toString());
productInfo.setBatch(lo.get(5).toString());
Date productTime = null;
// String 日期转为 Date类型的日期
if (lo.get(6).toString() != null || lo.get(6).toString() != "") {
productInfo.setProductTime(GetDateUtil.parseDate(lo.get(6).toString()));
} else {
productInfo.setProductTime(productTime);
}
} catch (Exception e) {
// TODO: handle exception
// 页面提示错误,重新填写再上传
}
productMapper.save(productInfo);
}
return "redirect:/product/toProduct";
}
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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;
import org.springframework.stereotype.Service;
@Service
public class ExcelUtil {
/**
* 处理上传的文件
*
* @param in
* @param fileName
* @return
* @throws Exception
*/
@SuppressWarnings("unused")
public List getBankListByExcel(InputStream in, String fileName) throws Exception {
List list = new ArrayList<>();
// 创建Excel工作薄
Workbook work = this.getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if (sheet == null) {
continue;
}
// 取每一行
for (int j = 1; j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
List<Object> li = new ArrayList<>();
// 取每一行的每一格
for (int y = 0; y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
String string = "";
if (cell != null) {
string = importByExcelForDate(cell);
}
li.add(string);
}
list.add(li);
}
}
work.close();
return list;
}
/**
* 判断文件格式
*
* @param inStr
* @param fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook workbook = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(fileType)) {
workbook = new HSSFWorkbook(inStr);
} else if (".xlsx".equals(fileType)) {
workbook = new XSSFWorkbook(inStr);
} else {
throw new Exception("请上传excel文件!");
}
return workbook;
}
/**
* 解析POI导入Excel中日期格式数据
* @param currentCell
* @return currentCellValue
*/
public static String importByExcelForDate(Cell currentCell) {
String currentCellValue = "";
// 判断单元格数据是否是数值型
if (0 == currentCell.getCellType()) {
// 判断单元格数据是否是日期
if (DateUtil.isCellDateFormatted(currentCell)) {
// 用于转化为日期格式
Date d = currentCell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
currentCellValue = formater.format(d);
} else {
// 把单元格数据当做文本,解决数字1变成1.0的问题
currentCell.setCellType(currentCell.CELL_TYPE_STRING);
currentCellValue = currentCell.toString();
}
} else {
// 不是日期原值返回
currentCellValue = currentCell.toString();
}
return currentCellValue;
}
}