本文實例講述了Java實現Excel導入導出數據庫的方法。分享給大家供大家參考，具體如下：
由于公司需求，想通過Excel導入數據添加到數據庫中，而導入的Excel的字段是不固定的，使用得通過動態創建數據表，每個Excel對應一張數據表，怎么動態創建數據表，可以參考前面一篇《java使用JDBC動態創建數據表及SQL預處理的方法》。
下面主要講講怎么將Excel導入到數據庫中，直接上代碼：干貨走起~~
ExcellToObjectUtil 類
主要功能是講Excel中的數據導入到數據庫中，有幾個注意點就是
1.一般Excel中第一行是字段名稱，不需要導入，所以從第二行開始計算
2.每列的匹配要和對象的屬性一樣
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
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()); } }} |
當然有導入功能，一定也有導出功能，下面介紹導出功能，直接上代碼：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
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(); }} |
導出的功能十分簡單，只要封裝好對象，直接調用方法即可，現在講講導入的時候前臺頁面怎么調用問題，
|
1
2
3
|
<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：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
/*** 讀取用戶提供的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程序設計有所幫助。
原文鏈接：http://blog.csdn.net/lovelong8808/article/details/44098179
