本文实例讲述了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