首页 > 代码库 > excel文件导入数据库
excel文件导入数据库
1、准备jar包：poi
2、前端jsp页面：
<body>
<!--
form表单中必须添加enctype="multipart/form-data"才可以上传文件
servlet中根据file名称来获取文件相对路径
-->
<form action="/phoneQuery" method="post" enctype="multipart/form-data">
<table align="center">
<tr>
<td>
请输入路径：
</td>
<td>
<input type="file" name="file"/>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="http://www.mamicode.com/提交"/>
</td>
</tr>
</table>
</form>
3、实体类
//注释完成自动建表
@TableAnnotaion(tableName="PHONE_LOCATION")
public class PhoneInfo {
@ColumnAnnotation(name="id",length=11,type=ColumnTypeEnum.INT,key=true)
private int id;
@ColumnAnnotation(name="phone_segment",length=11,type=ColumnTypeEnum.INT)
private int phone_segment;
@ColumnAnnotation(name="province",length=128)
private String province;
@ColumnAnnotation(name="city",length=128)
private String city;
@ColumnAnnotation(name="operator",length=128)
private String operator;
@ColumnAnnotation(name="zone_code",length=6,type=ColumnTypeEnum.INT)
private int zone_code;
@ColumnAnnotation(name="zip_code",length=8,type=ColumnTypeEnum.INT)
private int zip_code;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getPhone_segment() {
return phone_segment;
}
public void setPhone_segment(int phone_segment) {
this.phone_segment = phone_segment;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getOperator() {
return operator;
}
public void setOperator(String operator) {
this.operator = operator;
}
public int getZone_code() {
return zone_code;
}
public void setZone_code(int zone_code) {
this.zone_code = zone_code;
}
public int getZip_code() {
return zip_code;
}
public void setZip_code(int zip_code) {
this.zip_code = zip_code;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return ReflectionToStringBuilder.toString(this, ToStringStyle.MULTI_LINE_STYLE);
}
}
4、servlet文件
//注解中加上MultipartConfig才可以处理页面发送过来的file路径
@MultipartConfig
@WebServlet(name="phoneQuery",urlPatterns="/phoneQuery")
public class PhoneQueryServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.dataImport(req, resp);
}
/**
* Excel文件导入
*/
public void PhoneInsert(HttpServletRequest req, HttpServletResponse resp){
try {
Collection<Part> ps = req.getParts();//获取web页面输入框的集合值
for(Part p : ps){//分批处理输入框的集合值
List<PhoneInfo> phones = new ArrayList<PhoneInfo>();
InputStream in = p.getInputStream();//创建输入流
HSSFWorkbook wb = new HSSFWorkbook(in);//创建一个workbook对象(操作/解析excel)
int sheetNum = wb.getNumberOfSheets();//获取总的sheet数量
// System.out.println("sheetNum = "+sheetNum);
for(int i = 0;i<sheetNum;i++){
HSSFSheet sh = wb.getSheetAt(i);
int rowNum = sh.getLastRowNum();//获取总的行数
// System.out.println("rowNum = "+rowNum);
for(int j = 0;j<rowNum;j++){
HSSFRow row = sh.getRow(j);
int cellNum = row.getLastCellNum();//获取总的列数
// System.out.println("cellNum = "+cellNum);
PhoneInfo phone = new PhoneInfo();
phone.setId((int)row.getCell(0).getNumericCellValue());
phone.setPhone_segment((int)row.getCell(1).getNumericCellValue());
phone.setProvince(row.getCell(2).getStringCellValue());
phone.setCity(row.getCell(3).getStringCellValue());
phone.setOperator(row.getCell(4).getStringCellValue());
phone.setZone_code((int)row.getCell(5).getNumericCellValue());
phone.setZip_code((int)row.getCell(6).getNumericCellValue());
// System.out.println(phone.toString());
phones.add(phone);
// for(int k = 0;k<cellNum;k++){
// HSSFCell cell = row.getCell(k);//取到每一列的单元格
// int cellType = cell.getCellType();//取到单元格的类型
// System.out.println(cellType);
// switch(cellType){//根据单元格类型来选择接受类型
// case 0://双精度浮点数
// double cellD = cell.getNumericCellValue();
// System.out.println(cellD);
// break;
// case 1://字符串
// String cellS = cell.getStringCellValue();
// System.out.println(cellS);
// break;
// default:
// System.out.println("类型不匹配");
/*
* 2、CELL_TYPE_FORMULA 公式型
* 3、CELL_TYPE_BLANK 空值
* 4、CELL_TYPE_BOOLEAN 布尔型
* 5、CELL_TYPE_ERROR 错误
*/
// }
}
}
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ServletException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
dao.phoneInsert(phones);
}
5、dao层
/**
* 将数据导入数据库
*/
public void phoneInsert(List<PhoneInfo> phones){
// PhoneInfo phone = new PhoneInfo();
DBManager db = new DBManager();
String sql = "Insert into PHONE_LOCATION values (?,?,?,?,?,?,?)";
PreparedStatement state = db.getPreStatement(sql);
int flag = 1;
long startTime = System.currentTimeMillis();
for(PhoneInfo phone : phones){
try {
// long id = db.getMaxId("PHONE_LOCATION");
// System.out.println((int) id);
state.setInt(1,phone.getId());
state.setInt(2, phone.getPhone_segment());
state.setString(3, phone.getProvince());
state.setString(4, phone.getCity());
state.setString(5, phone.getOperator());
state.setInt(6, phone.getZone_code());
state.setInt(7, phone.getZip_code());
state.addBatch();
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(flag%2000==0||flag==phones.size()){
try {
state.executeBatch();
db.commit();
System.out.println("已经处理"+flag+"条数据");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
flag++;
}
db.close();
System.out.println(System.currentTimeMillis()-startTime);
}
excel文件导入数据库