这篇文章主要介绍了Java实现Excel导入导出数据库的方法,结合实例形式分析了java针对Excel的读写及数据库操作相关实现技巧,需要的朋友可以参考下
本文实例讲述了Java实现Excel导入导出数据库的方法。分享给大家供大家参考，具体如下：
由于公司需求，想通过Excel导入数据添加到数据库中，而导入的Excel的字段是不固定的，使用得通过动态创建数据表，每个Excel对应一张数据表，怎么动态创建数据表，可以参考前面一篇《java使用JDBC动态创建数据表及SQL预处理的方法》。
下面主要讲讲怎么将Excel导入到数据库中，直接上代码：干货走起~~
ExcellToObjectUtil 类
主要功能是讲Excel中的数据导入到数据库中，有几个注意点就是
1.一般Excel中第一行是字段名称，不需要导入，所以从第二行开始计算
2.每列的匹配要和对象的属性一样
import java.io.IOException;import java.text.DecimalFormat;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import com.forenms.exam.domain.ExamInfo;public class ExcellToObjectUtil { //examId,realName,身份证,user_card,sex,没有字段,assessment_project,admission_number,seat_number /** * 读取xls文件内容 * * @return List<XlsDto>对象 * @throws IOException * 输入/输出(i/o)异常 */ public static List<ExamInfo> readXls(POIFSFileSystem poifsFileSystem) throws IOException {// InputStream is = new FileInputStream(filepath); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem); ExamInfo exam = null; List<ExamInfo> list = new ArrayList<ExamInfo>(); // 循环工作表Sheet for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // 循环行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } exam = new ExamInfo(); // 循环列Cell HSSFCell examId = hssfRow.getCell(1); if (examId == null) { continue; } double id = Double.parseDouble(getValue(examId)); exam.setExamId((int)id);// HSSFCell realName = hssfRow.getCell(2);// if (realName == null) {// continue;// }// exam.setRealName(getValue(realName));// HSSFCell userCard = hssfRow.getCell(4);// if (userCard == null) {// continue;// }// // exam.setUserCard(getValue(userCard)); HSSFCell admission_number = hssfRow.getCell(8); if (admission_number == null) { continue; } exam.setAdmission_number(getValue(admission_number)); HSSFCell seat_number = hssfRow.getCell(9); if (seat_number == null) { continue; } exam.setSeat_number(getValue(seat_number)); list.add(exam); } } return list; } public static List<ExamInfo> readXlsForJS(POIFSFileSystem poifsFileSystem) throws IOException {// InputStream is = new FileInputStream(filepath); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem); ExamInfo exam = null; List<ExamInfo> list = new ArrayList<ExamInfo>(); // 循环工作表Sheet for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // 循环行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } exam = new ExamInfo(); // 循环列Cell 准考证号 HSSFCell admission_number = hssfRow.getCell(0); if (admission_number == null) { continue; } exam.setAdmission_number(getValue(admission_number)); //读取身份证号 HSSFCell userCard= hssfRow.getCell(2); if (userCard == null) { continue; } exam.setUserCard(getValue(userCard)); //读取座位号 HSSFCell seat_number = hssfRow.getCell(3); if (seat_number == null) { continue; } exam.setSeat_number(getValue(seat_number)); //读取考场号 HSSFCell fRoomName = hssfRow.getCell(6); if (fRoomName == null) { continue; } exam.setfRoomName(getValue(fRoomName)); //读取开考时间 HSSFCell fBeginTime = hssfRow.getCell(8); if (fBeginTime == null) { continue; } exam.setfBeginTime(getValue(fBeginTime)); //读取结束时间 HSSFCell fEndTime = hssfRow.getCell(9); if (fEndTime == null) { continue; } exam.setfEndTime(getValue(fEndTime)); list.add(exam); } } return list; } /** * 得到Excel表中的值 * * @param hssfCell * Excel中的每一个格子 * @return Excel中每一个格子中的值 */ private static String getValue(HSSFCell hssfCell) { if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { // 返回布尔类型的值 return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // 返回数值类型的值 DecimalFormat df = new DecimalFormat("0"); String strCell = df.format(hssfCell.getNumericCellValue()); return String.valueOf(strCell); } else { // 返回字符串类型的值 return String.valueOf(hssfCell.getStringCellValue()); } }}
当然有导入功能，一定也有导出功能，下面介绍导出功能，直接上代码：
import java.io.OutputStream;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import com.forenms.exam.domain.ExamInfo;public class ObjectToExcellUtil { //导出的文件名称 public static String FILE_NAME = "examInfo"; public static String[] CELLS = {"序号","编号","真实姓名","证件类型","证件号","性别","出生年月","科目","准考证号","座位号","考场号","开考时间","结束时间"}; //examId,realName,身份证,user_card,sex,没有字段,assessment_project,admission_number,seat_number public static void examInfoToExcel(List<ExamInfo> xls,int CountColumnNum,String filename,String[] names,HttpServletResponse response) throws Exception { // 获取总列数// int CountColumnNum = CountColumnNum; // 创建Excel文档 HSSFWorkbook hwb = new HSSFWorkbook(); ExamInfo xlsDto = null; // sheet 对应一个工作页 HSSFSheet sheet = hwb.createSheet(filename);// sheet.setColumnHidden(1,true);//隐藏列 HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始 HSSFCell[] firstcell = new HSSFCell[names.length]; for (int j = 0; j < names.length; j++) { sheet.setColumnWidth(j, 5000); firstcell[j] = firstrow.createCell(j); firstcell[j].setCellValue(new HSSFRichTextString(names[j])); } for (int i = 0; i < CountColumnNum; i++) { // 创建一行 HSSFRow row = sheet.createRow(i + 1); // 得到要插入的每一条记录 xlsDto = xls.get(i); for (int colu = 0; colu <= 12; colu++) { // 在一行内循环 HSSFCell xh = row.createCell(0); xh.setCellValue(i+1); HSSFCell examid = row.createCell(1); examid.setCellValue(xlsDto.getExamId()); HSSFCell realName = row.createCell(2); realName.setCellValue(xlsDto.getRealName()); HSSFCell zjlx = row.createCell(3); zjlx.setCellValue("身份证"); HSSFCell userCard = row.createCell(4); userCard.setCellValue(xlsDto.getUserCard()); HSSFCell sex = row.createCell(5); sex.setCellValue(xlsDto.getSex()); HSSFCell born = row.createCell(6); String bornTime = xlsDto.getUserCard().substring(6, 14); born.setCellValue(bornTime); HSSFCell assessment_project = row.createCell(7); assessment_project.setCellValue(xlsDto.getAssessmentProject()); HSSFCell admission_number = row.createCell(8); admission_number.setCellValue(xlsDto.getAdmission_number()); HSSFCell seat_number = row.createCell(9); seat_number.setCellValue(xlsDto.getSeat_number()); HSSFCell fRoomName = row.createCell(10); fRoomName.setCellValue(xlsDto.getfRoomName()); HSSFCell fBeginTime = row.createCell(11); fBeginTime.setCellValue(xlsDto.getfBeginTime()); HSSFCell fEndTime = row.createCell(12); fEndTime.setCellValue(xlsDto.getfEndTime()); } } // 创建文件输出流，准备输出电子表格 response.reset(); response.setContentType("application/vnd.ms-excel;charset=GBK"); response.addHeader("Content-Disposition", "attachment;filename="+filename+".xls"); OutputStream os = response.getOutputStream(); hwb.write(os); os.close(); }}
导出的功能十分简单，只要封装好对象，直接调用方法即可，现在讲讲导入的时候前台页面怎么调用问题，
<form method="post" action="adminLogin/auditResults/import" enctype="multipart/form-data" onsubmit="return importData();"> <input id="filepath" name="insuranceExcelFile" type="file" size="30" value="" style="font-size:14px" /><button type="submit" style="height:25px" value="导入数据">导入数据</button>
导入的前台表单提交的时候，要注意设置 enctype=”multipart/form-data” ，其他也没什么难度。 后台接受的controller：
/*** 读取用户提供的examinfo.xls* @param request* @param response* @param session* @return* @throws Exception*/@RequestMapping(value="adminLogin/auditResults/import",method=RequestMethod.POST)public ModelAndView importExamInfoExcell(HttpServletRequest request,HttpServletResponse response, HttpSession session)throws Exception{ //获取请求封装 MultipartHttpServletRequest multipartRequest=(MultipartHttpServletRequest)request; Map<String, MultipartFile> fileMap = multipartRequest.getFileMap(); //读取需要填写准考证号的人员名单 ExamInfo examInfo = new ExamInfo(); List<ExamInfo> info = examInfoService.queryExamInfoForDownLoad(examInfo); //获取请求封装对象 for(Entry<String, MultipartFile> entry: fileMap.entrySet()){ MultipartFile multipartFile = entry.getValue(); InputStream inputStream = multipartFile.getInputStream(); POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream); //从xml读取需要的数据 List<ExamInfo> list = ExcellToObjectUtil.readXlsForJS(poifsFileSystem); for (ExamInfo ei : list) { //通过匹配身份证号 填写对应的数据 for (ExamInfo in : info){ //如果身份证号 相同 则录入数据if(in.getUserCard().trim().toUpperCase().equals(ei.getUserCard().trim().toUpperCase())){ ei.setExamId(in.getExamId()); examInfoService.updateExamInfoById(ei); break; } } } } ModelAndView mav=new ModelAndView(PATH+"importExcelTip"); request.setAttribute("data", "ok"); return mav;}
好了，Excel导入导出的功能都搞定了，简单吧，需求自己修改一下 封装的对象格式和设置Excel的每个列即可自己使用！！
以上就是Java中关于Excel导入导出数据库实现的方法的详细内容，更多请关注其它相关文章！
人生重要的不是所站的位置，而是所朝的方向
