网站前端导入Excel数据表到数据库，excel表格自动insert插入

引入jar包，maven工程或者springboot项目工程，在pom.xml文件中加入如下代码：

<!-- 下面两个是导入excel到数据库的jar包,注意一定要是3.15这个版本，其它版本可能会出现问题 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.15</version></dependency>

前端代码：

<form th:action=\"@{/user/upexcel}\" method=\"post\" enctype=\"multipart/form-data\"><div class=\"input-group\"><input type=\"file\" class=\"form-control\" name=\"file\" placeholder=\"用户名\" aria-describedby=\"basic-addon1\"></div><br><button class=\"btn btn-info\">确认导入</button></form>

controller层：

/*** 导入excel文件内容到数据库* @return* @throws Exception*/@RequestMapping(\"/upexcel\")public String upexcel(MultipartFile file, HttpSession session) throws Exception {TempUser tempUser = (TempUser)session.getAttribute(\"user\");boolean flag = excelService.getExcel(file, tempUser.getZhengId());//到这里可以根据flag的值进行判断成功与否，下面是我个人项目中需要的，大家根据自己的项目改改逻辑。if (flag) {session.setAttribute(\"message\", \"success\");} else {session.setAttribute(\"message\", \"fail\");}return \"redirect:/user/allUser\";}

service层：

//service接口层：import org.springframework.web.multipart.MultipartFile;public interface ExcelService {boolean getExcel(MultipartFile file, Integer zhengId) throws Exception;}//service接口实现层：import com.dkf.wzq.beans.User;import com.dkf.wzq.service.ExcelService;import com.dkf.wzq.service.UserService;import org.apache.poi.ss.usermodel.*;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional;import org.springframework.web.multipart.MultipartFile;import java.io.BufferedInputStream;import java.io.FileInputStream;import java.util.ArrayList;import java.util.List;@Service@Transactionalpublic class ExcelServiceImpl implements ExcelService {@Autowiredprivate UserService userService;  //我用来实现user的增删改查的service@Overridepublic boolean getExcel(MultipartFile file, Integer zhengId) throws Exception {//1.得到上传的excel表BufferedInputStream bis = new BufferedInputStream(file.getInputStream());Workbook workbook = WorkbookFactory.create(bis);//2.获取users工作表,新建的表格默认是sheet1,左下角那个Sheet sheet = workbook.getSheet(\"users\");//3.获取表总行数int num = sheet.getLastRowNum();//List<User> users = new ArrayList<>();//4.遍历表格并提取信息到users集合，我这里直接就执行插入了，没有用到List集合for(int i = 1; i <= num;i++){Row row = sheet.getRow(i);Cell cell1 = row.getCell(1);Cell cell2 = row.getCell(2);//电话号码，如158xxxxxx51 ,默认不是String类型，需要使用这个方法改一下类型cell2.setCellType(CellType.STRING);Cell cell3 = row.getCell(3);Cell cell4 = row.getCell(4);User user = new User();user.setUsername(cell1.getStringCellValue());user.setPhone(cell2.getStringCellValue());user.setPos(cell3.getStringCellValue());//这是double类型，需要强转成intuser.setLevel((int)cell4.getNumericCellValue());user.setZhengid(zhengId);//添加的用户默认密码user.setPassword(\"dkf123\");//执行插入操作userService.insertUser(user);}return true;}}

最后附上我的表格截图：