Java如何实现把Excel文件数据导入到数据库

   最近笔者在使用Java给客户制作项目时，遇到需要把Excel文件数据导入到数据库的情况。今天笔者就跟大家介绍Java如何实现把Excel文件数据导入到数据库，这里以2003版本的excel版本为例，分享我的实战经验。

第一：需要下载必要的JAR包

用到的JAR包如下（可以直接到POI官网上下载也可以在文章的附件中下载）：

poi-3.9-20121203.jar

poi-ooxml-3.9-20121203.jar

poi-ooxml-schemas-3.9-20121203.jar

xmlbeans-2.3.0.jar

第二：具体代码实现的Java代码 

package com.struts.action;

import java.io.IOException;

import java.text.SimpleDateFormat;

import java.util.Calendar;

import java.util.Date;

import java.util.List;

import com.base.BaseAction;

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;

public class importExcelAction extends BaseAction {

 //导入Excel数据

          public ActionForward importExcelAction(ActionMapping mapping, ActionForm form,

                            HttpServletRequest request, HttpServletResponse response) throws Exception {

                              ZsbzDAO dbo=new ZsbzDAO();

                               String filePath ="C:/testBook.xls";

                               HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));

                                // 在Excel文档中，第一张工作表的缺省索引是0

                                 // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);

                                  HSSFSheet sheet = wookbook.getSheet("Sheet1");

                                   //获取到Excel文件中的所有行数

                                    int rows = sheet.getPhysicalNumberOfRows();

                                     //遍历行

                                       for (int i = 1; i < rows; i++) {

                                             // 读取左上端单元格

                                             HSSFRow row = sheet.getRow(i);

                                              // 行不为空

                                             if (row != null) {

                                                    //获取到Excel文件中的所有的列

                                                   int cells = row.getPhysicalNumberOfCells();

                                                   String value = "";   

                                                   //遍历列

                                                    for (int j = 0; j < cells; j++) {

                                                         //获取到列的值

                                                         HSSFCell cell = row.getCell((short) j);

                                                          if (cell != null) {

                                                                switch (cell.getCellType()) {

                                                                     case HSSFCell.CELL_TYPE_FORMULA:

                                                                     break;

                                                                      case HSSFCell.CELL_TYPE_NUMERIC:

                                                                           value += cell.getNumericCellValue() + ",";

                                                                      break; 

                                                                     case HSSFCell.CELL_TYPE_STRING:

                                                                           value += cell.getStringCellValue() + ",";

                                                                      break;

                                                                      default:

                                                                            value += "0";

                                                                      break;

                                                         }

                                                   }     

                                              }

                                              // 将数据插入到mysql数据库中

                                              String[] val = value.split(",");

                                             Zsbz student = new Zsbz();

                                          student.setZsbzid(val[0]);

                                          student.setZsbzpay(val[1]);

                                          student.setPaixuid(val[2]);

                                         dbo.addzsbz(student);

     }

      }

                            }catch(Exception ex){

                                          ex.printStackTrace();

                                   }

                                   return mapping.findForward("toImportExcel");   

                                   }

}

 

 

 

免责声明：本站所有资讯内容搜集整理于互联网或者网友提供，并不代表本网赞同其观点，仅供学习与交流使用，如果不小心侵犯到你的权益，如果你对文章内容、图片和版权等问题存在异议，请及时联系我们删除该信息。