使用java技术将Excel表格内容导入mysql数据库

电玩女神 2022-08-13 12:54 201阅读 0赞

1、添加POI jar包到项目的lib目录下  
2、Excel文件目录:d://excel.xls  
3、数据库字段为:num1 num2 num3 num4 num5 num6  
4、数据库名：blog  
5、表名:test  
6、编写类：连接mysql的字符串方法、插入的方法、实体类

![复制代码][copycode.gif]

1  import java.io.FileInputStream;  
  2  import java.io.FileNotFoundException;  
  3  import java.io.IOException;  
  4  import org.apache.commons.logging.Log;  
  5  import org.apache.commons.logging.LogFactory;  
  6  import org.apache.poi.hssf.usermodel.HSSFCell;  
  7  import org.apache.poi.hssf.usermodel.HSSFRow;  
  8  import org.apache.poi.hssf.usermodel.HSSFSheet;  
  9  import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
 10    
 11   public   class  TestExcel \{  
 12         // 记录类的输出信息   
 13          static  Log log  =  LogFactory.getLog(TestExcel. class );   
 14         // 获取Excel文档的路径   
 15          public   static  String filePath  =   " D://excel.xls " ;  
 16         public   static   void  main(String\[\] args) \{  
 17               try  \{  
 18                     //  创建对Excel工作簿文件的引用   
 19                     HSSFWorkbook wookbook  =   new  HSSFWorkbook( new  FileInputStream(filePath));    
 21                     //  在Excel文档中，第一张工作表的缺省索引是0  
 22                     //  其语句为：HSSFSheet sheet = workbook.getSheetAt(0);   
 23                     HSSFSheet sheet  =  wookbook.getSheet( " Sheet1 " );   
 25                     // 获取到Excel文件中的所有行数   
 26                      int  rows  =  sheet.getPhysicalNumberOfRows();   
 28                     // 遍历行   
 29                      for  ( int  i  =   0 ; i  <  rows; i \++ ) \{  
 30                           //  读取左上端单元格   
 31                           HSSFRow row  =  sheet.getRow(i);  
 32                           //  行不为空   
 33                            if  (row  !=   null ) \{  
 34                                 // 获取到Excel文件中的所有的列   
 35                                  int  cells  =  row.getPhysicalNumberOfCells();  
 36                                String value  =   "" ;       
 37                                 // 遍历列   
 38                                  for  ( int  j  =   0 ; j  <  cells; j \++ ) \{  
 39                                       // 获取到列的值   
 40                                       HSSFCell cell  =  row.getCell(j);  
 41                                       if  (cell  !=   null ) \{  
 42                                             switch  (cell.getCellType()) \{  
 43                                                   case  HSSFCell.CELL\_TYPE\_FORMULA:  
 44                                                   break ;  
 45                                                   case  HSSFCell.CELL\_TYPE\_NUMERIC:  
 46                                                        value  \+=  cell.getNumericCellValue()  \+   " , " ;          
 47                                                   break ;    
 48                                                   case  HSSFCell.CELL\_TYPE\_STRING:  
 49                                                        value  \+=  cell.getStringCellValue()  \+   " , " ;  
 50                                                   break ;  
 51                                                   default :  
 52                                                        value  \+=   " 0 " ;  
 53                                                   break ;  
 54                                      \}  
 55                                \}        
 56                          \}  
 57                           //  将数据插入到mysql数据库中   
 58                           String\[\] val  =  value.split( " , " );  
 59                          TestEntity entity  =   new  TestEntity();  
 60                          entity.setNum1(val\[ 0 \]);  
 61                          entity.setNum2(val\[ 1 \]);  
 62                          entity.setNum3(val\[ 2 \]);  
 63                          entity.setNum4(val\[ 3 \]);  
 64                          entity.setNum5(val\[ 4 \]);  
 65                          entity.setNum6(val\[ 5 \]);  
 66                          TestMethod method  =   new  TestMethod();  
 67                          method.Add(entity);  
 68                    \}  
 69               \}  
 70        \}  catch  (FileNotFoundException e) \{  
 71              e.printStackTrace();  
 72        \}  catch  (IOException e) \{  
 73              e.printStackTrace();  
 74        \}  
 75     \}  
 76  \}

[copycode.gif]: /images/20220810/3ec4874f22904323980a651010c4d68f.png