本篇文章給大家分享的是有關(guān)Java項(xiàng)目中如何將Excel文件從數(shù)據(jù)庫(kù)導(dǎo)入與導(dǎo)出,小編覺得挺實(shí)用的,因此分享給大家學(xué)習(xí),希望大家閱讀完這篇文章后可以有所收獲,話不多說,跟著小編一起來看看吧。
創(chuàng)新互聯(lián)公司是一家專注于網(wǎng)站建設(shè)、成都網(wǎng)站制作與策劃設(shè)計(jì),襄垣網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)十年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:襄垣等地區(qū)。襄垣做網(wǎng)站價(jià)格咨詢:18982081108
ExcellToObjectUtil 類
主要功能是講Excel中的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)中,有幾個(gè)注意點(diǎn)就是
1.一般Excel中第一行是字段名稱,不需要導(dǎo)入,所以從第二行開始計(jì)算
2.每列的匹配要和對(duì)象的屬性一樣
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文件內(nèi)容 * * @return List<XlsDto>對(duì)象 * @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>(); // 循環(huán)工作表Sheet for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // 循環(huán)行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } exam = new ExamInfo(); // 循環(huán)列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>(); // 循環(huán)工作表Sheet for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // 循環(huán)行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } exam = new ExamInfo(); // 循環(huán)列Cell 準(zhǔn)考證號(hào) HSSFCell admission_number = hssfRow.getCell(0); if (admission_number == null) { continue; } exam.setAdmission_number(getValue(admission_number)); //讀取身份證號(hào) HSSFCell userCard= hssfRow.getCell(2); if (userCard == null) { continue; } exam.setUserCard(getValue(userCard)); //讀取座位號(hào) HSSFCell seat_number = hssfRow.getCell(3); if (seat_number == null) { continue; } exam.setSeat_number(getValue(seat_number)); //讀取考場(chǎng)號(hào) HSSFCell fRoomName = hssfRow.getCell(6); if (fRoomName == null) { continue; } exam.setfRoomName(getValue(fRoomName)); //讀取開考時(shí)間 HSSFCell fBeginTime = hssfRow.getCell(8); if (fBeginTime == null) { continue; } exam.setfBeginTime(getValue(fBeginTime)); //讀取結(jié)束時(shí)間 HSSFCell fEndTime = hssfRow.getCell(9); if (fEndTime == null) { continue; } exam.setfEndTime(getValue(fEndTime)); list.add(exam); } } return list; } /** * 得到Excel表中的值 * * @param hssfCell * Excel中的每一個(gè)格子 * @return Excel中每一個(gè)格子中的值 */ 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) { // 返回?cái)?shù)值類型的值 DecimalFormat df = new DecimalFormat("0"); String strCell = df.format(hssfCell.getNumericCellValue()); return String.valueOf(strCell); } else { // 返回字符串類型的值 return String.valueOf(hssfCell.getStringCellValue()); } } }
當(dāng)然有導(dǎo)入功能,一定也有導(dǎo)出功能,下面介紹導(dǎo)出功能,直接上代碼:
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 { //導(dǎo)出的文件名稱 public static String FILE_NAME = "examInfo"; public static String[] CELLS = {"序號(hào)","編號(hào)","真實(shí)姓名","證件類型","證件號(hào)","性別","出生年月","科目","準(zhǔn)考證號(hào)","座位號(hào)","考場(chǎng)號(hào)","開考時(shí)間","結(jié)束時(shí)間"}; //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 { // 獲取總列數(shù) // int CountColumnNum = CountColumnNum; // 創(chuàng)建Excel文檔 HSSFWorkbook hwb = new HSSFWorkbook(); ExamInfo xlsDto = null; // sheet 對(duì)應(yīng)一個(gè)工作頁(yè) HSSFSheet sheet = hwb.createSheet(filename); // sheet.setColumnHidden(1,true);//隱藏列 HSSFRow firstrow = sheet.createRow(0); // 下標(biāo)為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++) { // 創(chuàng)建一行 HSSFRow row = sheet.createRow(i + 1); // 得到要插入的每一條記錄 xlsDto = xls.get(i); for (int colu = 0; colu <= 12; colu++) { // 在一行內(nèi)循環(huán) 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()); } } // 創(chuàng)建文件輸出流,準(zhǔn)備輸出電子表格 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(); } }
導(dǎo)出的功能十分簡(jiǎn)單,只要封裝好對(duì)象,直接調(diào)用方法即可,現(xiàn)在講講導(dǎo)入的時(shí)候前臺(tái)頁(yè)面怎么調(diào)用問題,
<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="導(dǎo)入數(shù)據(jù)">導(dǎo)入數(shù)據(jù)</button>
導(dǎo)入的前臺(tái)表單提交的時(shí)候,要注意設(shè)置 enctype=”multipart/form-data” ,其他也沒什么難度。
后臺(tái)接受的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{ //獲取請(qǐng)求封裝 MultipartHttpServletRequest multipartRequest=(MultipartHttpServletRequest)request; Map<String, MultipartFile> fileMap = multipartRequest.getFileMap(); //讀取需要填寫準(zhǔn)考證號(hào)的人員名單 ExamInfo examInfo = new ExamInfo(); List<ExamInfo> info = examInfoService.queryExamInfoForDownLoad(examInfo); //獲取請(qǐng)求封裝對(duì)象 for(Entry<String, MultipartFile> entry: fileMap.entrySet()){ MultipartFile multipartFile = entry.getValue(); InputStream inputStream = multipartFile.getInputStream(); POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream); //從xml讀取需要的數(shù)據(jù) List<ExamInfo> list = ExcellToObjectUtil.readXlsForJS(poifsFileSystem); for (ExamInfo ei : list) { //通過匹配身份證號(hào) 填寫對(duì)應(yīng)的數(shù)據(jù) for (ExamInfo in : info){ //如果身份證號(hào) 相同 則錄入數(shù)據(jù) 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項(xiàng)目中如何將Excel文件從數(shù)據(jù)庫(kù)導(dǎo)入與導(dǎo)出,小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘9ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
新聞標(biāo)題:Java項(xiàng)目中如何將Excel文件從數(shù)據(jù)庫(kù)導(dǎo)入與導(dǎo)出
轉(zhuǎn)載來于:http://muchs.cn/article2/piscoc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站制作、手機(jī)網(wǎng)站建設(shè)、品牌網(wǎng)站建設(shè)、App設(shè)計(jì)、面包屑導(dǎo)航、品牌網(wǎng)站設(shè)計(jì)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)