How to implement Excel import and export database in Java

This article mainly introduces the method of Java to implement Excel import and export database, and analyzes Java's implementation skills for Excel's reading, writing and database operations in the form of examples. Friends in need can refer to the examples of this article

Describes how to implement Excel import and export database in Java. Share it with everyone for your reference. The details are as follows:

Due to the company's needs, I want to import data through Excel and add it to the database. However, the fields of the imported Excel are not fixed. To use it, you need to dynamically create a data table. Each time Each Excel corresponds to a data table. How to dynamically create a data table, you can refer to the previous article "Java uses JDBC to dynamically create data tables and SQL preprocessing methods."

The following mainly talks about how to import Excel into the database and directly enter the code: Let’s get started~~

ExcellToObjectUtil class

The main function is When it comes to importing data from Excel into the database, there are a few points to note:

1. Generally, the first row in Excel is the field name and does not need to be imported, so calculation starts from the second row

2. The matching of each column must be the same as the attributes of the object

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());
    }
  }
}

Of course there is an import function, and there must also be an export function. The export function is introduced below, and the code is directly uploaded:

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();
    }
}

The export function is very simple. Just encapsulate the object and call the method directly. Now let’s talk about how to call the front page when importing,

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

When submitting the imported front-end form, pay attention to setting enctype="multipart/form-data", otherwise there is no difficulty.
Controller accepted by the background:

/**
* 读取用户提供的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;
}

Okay, the Excel import and export functions are all done. It’s simple. You need to modify the encapsulated object format yourself. And set each column of Excel for your own use! !

The above is the detailed content of How to implement Excel import and export database in Java. For more information, please follow other related articles on the PHP Chinese website!