这篇文章主要介绍了java实现excel导入导出数据库的方法,结合实例形式分析了java针对excel的读写及数据库操作相关实现技巧,需要的朋友可以参考下
本文实例讲述了java实现excel导入导出数据库的方法。分享给大家供大家参考，具体如下：
由于公司需求，想通过excel导入数据添加到数据库中，而导入的excel的字段是不固定的，使用得通过动态创建数据表，每个excel对应一张数据表，怎么动态创建数据表，可以参考前面一篇《java使用jdbc动态创建数据表及sql预处理的方法》。
下面主要讲讲怎么将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="" style="font-size:14px" /> <button type="submit" style="height:25px" 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; }
好了，excel导入导出的功能都搞定了，简单吧，需求自己修改一下 封装的对象格式和设置excel的每个列即可自己使用！！
以上就是java中关于excel导入导出数据库实现的方法的详细内容。