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对象

* @throws IOException

* 输入/输出(i/o)异常

*/

public static LEvwpqUgist readXls(POIFSFileSystem poifsFileSystem) throws IOException {

// InputStream is = new FileInputStream(filepath);

HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);

ExamInfo exam = null;

List list = new ArrayList();

// 循环工作表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 readXlsForjs(POIFSFileSystem poifsFileSystem) throws IOException {

// InputStream is = new FileInputStream(filepath);

HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);

ExamInfo exam = null;

List list = new ArrayList();

// 循环工作表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 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 = xlsDtEvwpqUgo.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();

}

}

导出的功能十分简单，只要封装好对象，直接调用方法即可，现在讲讲导入的时候前台页面怎么调用问题，

导入数据

导入的前台表单提交的时候，要注意设置 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 fileMap = multipartRequest.getFileMap();

//读取需要填写准考http://证号的人员名单

ExamInfo examInfo = new ExamInfo();

List info = examInfoService.queryExamInfoForDownLoad(examInfo);

//获取请求封装对象

for(Entry entry: fileMap.entrySet()){

MultipartFile multipartFile = entry.getValue();

InputStream inputStream = multipartFile.getInputStream();

POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);

//从xml读取需要的数据

List 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相关内容感兴趣的读者可查看本站专题：《Java操作Excel技巧总结》、《Java+mysql数据库程序设计总结》、《Java数据结构与算法教程》、《Java文件与目录操作技巧汇总》及《Java操作DOM节点技巧总结》

希望本文所述对大家java程序设计有所帮助。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。