springboot导⼊Excel表格数据时⽇期格式数据解析错误的问题
平时我们写⽇期格式的时候是这样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== CellType()){
// 判断单元格数据是否是⽇期
if(DateUtil.isCellDateFormatted(currentCell)){
// ⽤于转化为⽇期格式
Date d = DateCellValue();
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 = String();
}
}else{
// 不是⽇期原值返回
currentCellValue = String();
}
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 = File("filename");
if(file.isEmpty()){
return"redirect:/product/toProduct";
}
InputStream inputStream = InputStream();
List<List<Object>> list = BankListByExcel(inputStream, OriginalFilename());
inputStream.close();
ProductInfo productInfo =new ProductInfo();
//下⾯是把处理好的结果⼀个⼀个放到对象中,保存数据库
for(int i =0; i < list.size(); i++){
try{
List<Object> lo = (i);
productInfo.(0).toString());
productInfo.setProductType(Integer.(1).toString()));
productInfo.(2).toString());
productInfo.(3).toString());
productInfo.(4).toString());
productInfo.(5).toString());
Date productTime = null;
// String ⽇期转为 Date类型的⽇期
(6).toString()!= null || lo.get(6).toString()!=""){
productInfo.setProductTime(GetDateUtil.(6).toString()));
}else{
productInfo.setProductTime(productTime);
}
}catch(Exception e){
// TODO: handle exception
// 页⾯提⽰错误,重新填写再上传
}
productMapper.save(productInfo);
}
return"redirect:/product/toProduct";
}
import java.io.InputStream;
DateFormat;
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 =Workbook(in, fileName);
if(null == work){
throw new Exception("创建Excel⼯作薄为空!");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
for(int i =0; i < NumberOfSheets(); i++){
sheet = SheetAt(i);
if(sheet == null){
continue;
}
// 取每⼀⾏
for(int j =1; j <= LastRowNum(); j++){
row = Row(j);
List<Object> li =new ArrayList<>();
// 取每⼀⾏的每⼀格
for(int y =0; y < LastCellNum(); y++){
cell = Cell(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中⽇期格式数据
* 解析POI导⼊Excel中⽇期格式数据
* @param currentCell
* @return currentCellValue
*/
public static String importByExcelForDate(Cell currentCell){
String currentCellValue ="";
// 判断单元格数据是否是数值型
if(0== CellType()){
// 判断单元格数据是否是⽇期
if(DateUtil.isCellDateFormatted(currentCell)){
// ⽤于转化为⽇期格式
Date d = DateCellValue();
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 = String();
}
}else{
// 不是⽇期原值返回
currentCellValue = String();
}
getsavefilename
return currentCellValue;
}
}
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论