java代码实现excel数据导入数据库

最新推荐文章于 2022-12-08 11:51:32 发布

低调的洋仔

最新推荐文章于 2022-12-08 11:51:32 发布

阅读量1.3w

点赞数 5

文章标签： excel

本文链接：https://blog.csdn.net/wangyang1354/article/details/49735431

版权

Java 专栏收录该内容

29 篇文章 2 订阅

订阅专栏

1、添加POI jar包到项目的lib目录下
2、Excel文件目录:d://excel.xls
3、数据库字段为:num1 num2 num3 num4 num5 num6
4、数据库名：blog
5、表名:test

6、编写类：连接mysql的字符串方法、插入的方法、实体类

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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 TestExcel {
      //记录类的输出信息
      static Log log = LogFactory.getLog(TestExcel.class); 
      //获取Excel文档的路径
      public static String filePath = "D://excel.xls";
      public static void main(String[] args) {
            try {
                  // 创建对Excel工作簿文件的引用
                  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 = 0; 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(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(",");
                        TestEntity entity = new TestEntity();
                        entity.setNum1(val[0]);
                        entity.setNum2(val[1]);
                        entity.setNum3(val[2]);
                        entity.setNum4(val[3]);
                        entity.setNum5(val[4]);
                        entity.setNum6(val[5]);
                        TestMethod method = new TestMethod();
                        method.Add(entity);
                  }
             }
      } catch (FileNotFoundException e) {
            e.printStackTrace();
      } catch (IOException e) {
            e.printStackTrace();
      }
   }
}

 try {  
             // 创建对Excel工作簿文件的引用-  
			 
             HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(realpath+"\\"+fileName));                
             // 在Excel文档中，第一张工作表的缺省索引是0  
             // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);  
             HSSFSheet sheet = wookbook.getSheet("Sheet2");  
            //获取到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(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;  
                               }  
                         }        
                   }
                   String[] val=value.split(",");