Java项目中如何将Excel文件从数据库导入与导出
本篇文章给大家分享的是有关Java项目中如何将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="" /> <button type="submit" 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; }
以上就是Java项目中如何将Excel文件从数据库导入与导出,小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。