Java中将数据从excel表格中数据导入到数据库

最新推荐文章于 2024-01-05 00:14:49 发布

MyRecords

最新推荐文章于 2024-01-05 00:14:49 发布

阅读量1.9k

点赞数 2

CC 4.0 BY-SA版权

文章标签： java excel 数据数据库 jsp

本文链接：https://blog.youkuaiyun.com/MyRecords/article/details/53023220

该博客展示了如何使用Java将Excel文件中的数据导入到数据库。通过jsp页面上传Excel文件，利用ajaxfileupload.js处理文件上传，然后在Java后端解析Excel并存储到数据库中，包括读取不同类型的单元格数据并进行相应的转换处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在jsp页面中导入ajaxfileupload.js文件

jsp代码

</head>

<body>

</body>
</html>

Java代码

private File file;
//提交过来的file的名字
private String fileFileName;

public File getFile()
{
return file;
}

public void setFile(File file)
{
this.file = file;
}

public String getFileFileName()
{
return fileFileName;
}

public void setFileFileName(String fileFileName)
{
this.fileFileName = fileFileName;
}

public void importExcel()
{
try
{
InputStream in = new FileInputStream(file);
Workbook wb = openWorkbook(in, fileFileName);
Sheet sheet = (Sheet)wb.getSheetAt(0);
Row row = null;
Cell cell = null;
int totalRows = sheet.getPhysicalNumberOfRows(); //获取到Excel文件中的所有行数
int totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); //获取到Excel文件中的所有的列

for(int r=2; r<=totalRows; r++)
{
row = sheet.getRow(r); // 读取左上端单元格
if(row!=null)
{
String[] cellValue = new String[totalCells];
for(int c = 0; c < totalCells; c++) //遍历列
{
cell = row.getCell(c); //获取到列的值
if(null != cell){
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell))
{
Date dd = cell.getDateCellValue();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue[c] = df.format(dd);
}
else
{
cellValue[c] = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_STRING:
cellValue[c] = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue[c] = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
cellValue[c] = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue[c] = "";
break;
default:
cellValue[c] = "0";
break;
}
}
}

String[] val = cellValue;
if(val[1] != null)
{
SuppliesBasal sp = suppliesBasalService.getByCode(val[1].trim());
Type typeNos = typeService.getByTypeNo(val[1].trim().substring(0,3)); //12位码的前三位同类型信息的编号
if(sp == null)
{
if(typeNos !=null)
{
SuppliesBasal supBasal = new SuppliesBasal();
supBasal.setSuppliesCode(val[1].trim());
supBasal.setSuppliesName(val[2]);
supBasal.setPecifications(val[3]);
supBasal.setReferPrice(Double.parseDouble(val[4]));
supBasal.setType(typeNos);
supBasal.setAmountUnit(val[5]);
supBasal.setPerAmount(Double.parseDouble(val[6]));
supBasal.setMeasuringUnit(val[7]);
supBasal.setIsFixedAssets(val[8]);
supBasal.setUseLife(Integer.parseInt(val[9]));
supBasal.setToolOrMaterials(val[10]);
supBasal.setSuppliesCategory(val[11]);
supBasal.setIsTest(val[12]);
supBasal.setTestCycle(Integer.parseInt(val[13]));
suppliesBasalService.save(supBasal);
}
}
}

}
}
}
catch(Exception e)
{
e.printStackTrace();
}
}

/**

*判断excel的版本

*/
static Workbook openWorkbook(InputStream in,String filename)throws IOException
{
Workbook wb = null;
if(filename.endsWith(".xlsx")){
wb = new XSSFWorkbook(in);//Excel 2007
} else {
wb = new HSSFWorkbook(in);//Excel 2003
}
return wb;
}