将excel数据解析并插入数据库

最新推荐文章于 2024-06-13 18:25:04 发布
最新推荐文章于 2024-06-13 18:25:04 发布
阅读量758 收藏
点赞数
分类专栏： asp.net .net 文章标签： excel string list email regex properties
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/SMG008/article/details/7237444
版权 
 

 

/// <summary>
         /// 将excel数据解析并插入数据库
         /// </summary>
         /// <param name="sender"></param>
         /// <param name="e"></param>
 

        [DirectMethod]
         public void UpLoad_btnClick(object sender, DirectEventArgs e)
         {
             string jobID = Request.Cookies["jobId"].Value;
             string jobName = ZLCP.Common.Function.GetCookieValue(this, "jobName");
 

            int BatchID = Int32.Parse(Request.QueryString["batchid"]);
             int parterID = CurrentUser.PartnerId;
 

            ArrayList list = new ArrayList();
 

            if (!FileUploadField.HasFile)
             {
                 X.Msg.Alert("提示", "请选择上传的文件！").Show();
                 return;
             }
 

            //获取客户端FileUploadField文件的扩展名并验证
 

            fileExtenSion = Path.GetExtension(FileUploadField.FileName).ToLower();
             if (fileExtenSion.ToLower() != ".xlsx" && fileExtenSion.ToLower() != ".xls")
             {
                 X.Msg.Alert("提示", "上传的文件格式不正确！").Show();
                 return;
             }
 


             DataTable dt = xsldata();
 

            if (dt.Rows.Count > 0 && (dt.Rows[0][0].ToString() != "姓名" || dt.Rows[0][4].ToString() != "专业" || dt.Rows[0][6].ToString() != "邮箱" || dt.Rows[0][1].ToString() != "性别"))
             {
 

                X.Msg.Alert("提示", "请使用正确的模板！").Show();
                     return;
                
             }
             if (dt.Rows.Count==1)
                 {
                     X.Msg.Alert("提示", "导入的表格为空！").Show();
                     return;
                 }
             for (int i = 1; i < dt.Rows.Count; i++)
             {
                 Regex emailregex = new Regex(@"^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$");
                 Regex telregex = new Regex(@"(\d{11})|^((\d{7,8})|(\d{4}|\d{3})-(\d{7,8})|(\d{4}|\d{3})-(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1})|(\d{7,8})-(\d{4}|\d{3}|\d{2}|\d{1}))$");
                 Match em = emailregex.Match(dt.Rows[i][6].ToString());
                 string tel = dt.Rows[i][7].ToString().Replace(" ","");
                 Match t = telregex.Match(tel);
                     if (dt.Rows[i][0].ToString() != "" && dt.Rows[i][1].ToString() != "" && dt.Rows[i][2].ToString() != "" && dt.Rows[i][3].ToString() != "" && dt.Rows[i][4].ToString() != "" && dt.Rows[i][5].ToString() != "" && dt.Rows[i][6].ToString() != "" && dt.Rows[i][7].ToString() != ""&&em.Success&&t.Success)
                     {
                         
 

                        string Name = dt.Rows[i][0].ToString();
                         bool Sex = dt.Rows[i][1].ToString() == "男" ? true : false;
                         int Age = Convert.ToInt32(dt.Rows[i][2].ToString());
                         string Degree = dt.Rows[i][3].ToString();
                         string Major = dt.Rows[i][4].ToString();
                         string Current = dt.Rows[i][5].ToString();
                         string Email = dt.Rows[i][6].ToString();
                         string Tel = dt.Rows[i][7].ToString();
 

                        AddToPerson atp = new AddToPerson();
 

                        //判断该职位下边的批次是否关闭
                         int batchID = atp.JobStatus(int.Parse(jobID), BatchID,CurrentUser.UserId);
 


                         //判断Email是否已经存在
                         int a = atp.selectInfo(Email);
 

                        if (a == 0)
                         {
                             DateTime datetime = DateTime.Now;
 

                            //给Person表赋值
                             Person ps = new Person();
                             ps.PartnerID = parterID;
                             ps.RegisterTime = datetime;
 

                            //给InfoToRecruitment表赋值
                             InfoToRecruitment info = new InfoToRecruitment();
                             info.Name = Name;
                             info.Age = Age;
                             info.Degree = atp.getDegree(Degree);
                             info.Gender = Sex;
                             info.Major = Major;
                             info.Career = Current;
                             info.Telephone = Tel;
                             info.Email = Email;
                             info.ResumeDeliverTime = datetime;
 

 
 

                            //给Batch_Person表赋值
                             Batch_Person bp = new Batch_Person();
                             bp.BatchID = batchID;
 

                            int m = atp.InsertTable(ps, info, bp);
                             if (m >= 3)
                             {
                                 //Response.Redirect("~/CompanyPlatform/RecruitmentUI.aspx?JPID=" + Request.QueryString["JPID"] + "");
                                 if (i == dt.Rows.Count - 1)
                                 {
                                     if (list.Count > 1)
                                     {
                                         X.Msg.Show(
                                             new MessageBoxConfig
                                                 {
                                                     Title = "提示",
                                                     Message = "导入成功！" + " <br> " + "以下人员信息不完整或者" + " <br> " + "信息格式非法未能导入（" + list[0] + "，" + list[1] + "...)" ,
                                                     Buttons = MessageBox.Button.OK,
                                                     MessageBoxButtonsConfig = new MessageBoxButtonsConfig
                                                         {
                                                             Ok = new MessageBoxButtonConfig
                                                         {
                                                             Text = "确定",
                                                             Handler = "window.location.href='RecruitmentUI.aspx?JPID=" + int.Parse(jobID) + "'"//Request.QueryString["JPID"] + "')"
                                                         }
                                                         }
                                                 });
                                     }
                                     else
                                     {
                                         X.Msg.Show(
                                             new MessageBoxConfig
                                             {
                                                 Title = "提示",
                                                 Message = list.Count > 0 ? "导入成功！" + " <br> " + "以下人员信息不完整或者" + " <br> " + "信息格式非法未能导入（" + list[0] + ")" : "导入成功！",
                                                 Buttons = MessageBox.Button.OK,
                                                 MessageBoxButtonsConfig = new MessageBoxButtonsConfig
                                                 {
                                                     Ok = new MessageBoxButtonConfig
                                                     {
                                                         Text = "确定",
                                                         Handler = "window.location.href='RecruitmentUI.aspx?JPID=" + int.Parse(jobID) + "'"//Request.QueryString["JPID"] + "')"
                                                     }
                                                 }
                                             });
                                     }
 


                                 }
                             }
                             else
                             {
                                 X.Msg.Alert("提示", "导入失败！").Show();
                                 return;
                             }
                          
                         }
                         else
                         {
                             //判断先插入的职位于之间是是否相同
                             int count = atp.GetjobIDCount(Email, int.Parse(jobID));
                             //给InfoToRecruitment表更新数据
                                 int n = atp.UpdateTable(Name, Age, Degree, Sex, Major, Current, Tel, Email);
                                 if (n == 0)
                                 {
                                     X.Msg.Alert("提示", "导入失败！").Show();
                                     return;
                                 }
                                 if(count==0)
                                 {
                                     atp.InsertTableBP(batchID,Email);
                                 }
                                 if (i == dt.Rows.Count - 1)
                                 {
                                     if (list.Count > 1)
                                     {
                                         X.Msg.Show(
                                            new MessageBoxConfig
                                            {
                                                Title = "提示",
                                                Message = "导入成功！" + " <br> " + "以下人员信息不完整或者" + " <br> " + "信息格式非法未能导入（" + list[0] + "，" + list[1] + "...)",
                                                Buttons = MessageBox.Button.OK,
                                                MessageBoxButtonsConfig = new MessageBoxButtonsConfig
                                                {
                                                    Ok = new MessageBoxButtonConfig
                                                    {
                                                        Text = "确定",
                                                        Handler = "window.location.href='RecruitmentUI.aspx?JPID=" + int.Parse(jobID) + "'"//?JPID=" + Request.QueryString["JPID"] + "')"
                                                    }
                                                }
                                            });
                                     }
                                     else
                                     {
                                         X.Msg.Show(
                                            new MessageBoxConfig
                                            {
                                                Title = "提示",
                                                Message = list.Count > 0 ? "导入成功！" + " <br> " + "以下人员信息不完整或者" + " <br> " + "信息格式非法未能导入（" + list[0] + ")" : "导入成功！",
                                                Buttons = MessageBox.Button.OK,
                                                MessageBoxButtonsConfig = new MessageBoxButtonsConfig
                                                {
                                                    Ok = new MessageBoxButtonConfig
                                                    {
                                                        Text = "确定",
                                                        Handler = "window.location.href='RecruitmentUI.aspx?JPID=" + int.Parse(jobID) + "'"//?JPID=" + Request.QueryString["JPID"] + "')"
                                                    }
                                                }
                                            });
                                     }
                                 }
                             }
                             
                         
                     }
                     else
                     {
                         if (dt.Rows[i][0].ToString() != "")
                             list.Add(dt.Rows[i][0].ToString());
                         if (i == dt.Rows.Count - 1)
                         {
                             if (list.Count > 1)
                             {
                                 X.Msg.Show(
                                    new MessageBoxConfig
                                    {
                                        Title = "提示",
                                        Message = "导入成功！" + " <br> " + "以下人员信息不完整或者" + " <br> " + "信息格式非法未能导入（" + list[0] + "，" + list[1] + "...)",
                                        Buttons = MessageBox.Button.OK,
                                        MessageBoxButtonsConfig = new MessageBoxButtonsConfig
                                        {
                                            Ok = new MessageBoxButtonConfig
                                            {
                                                Text = "确定",
                                                Handler = "window.location.href='RecruitmentUI.aspx?JPID=" + int.Parse(jobID) + "'"//?JPID=" + Request.QueryString["JPID"] + "')"
                                            }
                                        }
                                    });
                             }
                             else
                             {
                                 X.Msg.Show(
                                    new MessageBoxConfig
                                    {
                                        Title = "提示",
                                        Message = list.Count > 0 ? "导入成功！" + " <br> " + "以下人员信息不完整或者" + " <br> " + "信息格式非法未能导入（" + list[0] + ")" : "导入成功！",
                                        Buttons = MessageBox.Button.OK,
                                        MessageBoxButtonsConfig = new MessageBoxButtonsConfig
                                        {
                                            Ok = new MessageBoxButtonConfig
                                            {
                                                Text = "确定",
                                                Handler = "window.location.href='RecruitmentUI.aspx?JPID=" + int.Parse(jobID) + "'"//?JPID=" + Request.QueryString["JPID"] + "')"
                                            }
                                        }
                                    });
                             }
                         }
                     }
                 
                
             }
 

        }
 

        public DataTable xsldata()
         {
 

            try
             {
 

                string FileName = "~/Download/" + Path.GetFileName(FileUploadField.FileName);
 

                if (File.Exists(Server.MapPath(FileName)))
                 {
 

                    File.Delete(Server.MapPath(FileName));
 

                }
 

                FileUploadField.PostedFile.SaveAs(Server.MapPath(FileName));
 

                //HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES 
 

                string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'";
 

                string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties=\"Excel 12.0;HDR=NO\"";
 

                OleDbConnection conn;
 

                if (fileExtenSion.ToLower() == ".xls")
                 {
 

                    conn = new OleDbConnection(connstr2003);
 

                }
 

                else
                 {
 

                    conn = new OleDbConnection(connstr2007);
 

                }
 

                conn.Open();
 

                string sql = "select * from [Sheet1$]";
 

                OleDbCommand cmd = new OleDbCommand(sql, conn);
 

                DataTable dt = new DataTable();
 

                OleDbDataReader sdr = cmd.ExecuteReader();
 


                 dt.Load(sdr);
 

                sdr.Close();
 

                conn.Close();
 

                //删除服务器里上传的文件 
 

                if (File.Exists(Server.MapPath(FileName)))
                 {
 

                    File.Delete(Server.MapPath(FileName));
 

                }
 

                return dt;
 

            }
 

            catch (Exception e)
             {
 

                return null;
 

            }
         }
  
                

        

        

    




    

      

        

            

              
                
                  SMG008
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                      0
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    0
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
Java解析excel导入MySQL数据库

				
			

			

				

					08-09
				

			

		

		

			
				
实现了Excel文件数据的解析，并且导入到对应的数据库，可根据自身需要进行更改和丰富

			
		

	



                

              
                



	

		

			

				
					
应用 POI 解析 Excel 表格并批量上传到数据库

				
			

			

				

					秀岩的博客
				

				

					04-07
					
					1714
					
				

			

		

		

			
				
应用 POI 解析 Excel 表格并批量上传到数据库
  在日常生活中，我们时常会遇到添加用户之类的操作，但是这类操作，只能一位一位的添加。遇到向我这种强迫症晚期患者，会被烦死… 那么应用 POI 解析含有用户信息的 EXCEL 表格会省很多时间。本文针对解析 EXCEL 表格以及将表格上的内容批量上传到数据库提供一个使用的解决方案。


目录应用 POI 解析 Excel 表格并批量上传到数据库一、导入 pom 坐标二、编写解析 Excel 表格的工具类三、批量上传至服务器四、整体代码五、整体效果演示：

			
		

	



                


  
              

                


	

		

			

				
					
Python数据分析之读取Excel数据并导入数据库

				
			

			

				

					zhangliushi的博客
				

				

					03-18
					
					4973
					
				

			

		

		

			
				
入职新公司两个多月，发现这边的数据基础很差，没有维度建模的数仓，很多数据甚至没有系统承载，大量的Excel表，大量的人工处理工作，现阶段被迫“面向Excel”编程。本文主要介绍使用Python读取Excel数据并导入数据库的方法，供各位小伙伴参考。Python可以通过一些非常实用的包，如pandas，numpy等，对数据进行清洗，整理分析，合理利用Python可以提高我们处理分析数据的效率。

			
		

	





	

		

			

				
					
python脚本：从Excel读取数据插入oracle数据库

					
最新发布

				
			

			

				

					a949368227的博客
				

				

					06-13
					
					697
					
				

			

		

		

			
				
把Excel文件绝对路径和数据表名称作为参数，传入insert_from_excel方法。1、Excel第一行，作为列名，需要与数据库字段名一致。4、把解压后的文件夹路径配置到path环境变量。3、把5个压缩包解压到同一个文件夹。1、访问oracle官网。2、针对于不同类型数据。

			
		

	



		

			
		



	

		

			

				
					
使用xlsx库进行解析excel 并将解析的excel数据插入到数据库对应的字段中

				
			

			

				

					qq_45668217的博客
				

				

					05-25
					
					849
					
				

			

		

		

			
				
一、首先新建一个html页面，引入xlsx.core.min.js和jquery-3.4.0.js包。
1、添加点击上传excel文件如下：
<input type='file' id="upLoadExcel"/>

2、前端上传文件及封装自己需要的数据类型并传入后台的代码如下：
//上传解析excel表格并插入到数据库
    $("#upLoadExcel").change(function (e) {
        var files = e.target.files;

      

			
		

	





	

		

			

				
					
springboot将Excel文件数据导入数据库

				
			

			

				

					Eternity_he的博客
				

				

					08-21
					
					945
					
				

			

		

		

			
				
Override//将文件中读到的数据存到街镇实体类中return Result.error("街道名称不能为空");return Result.error("街道详细地址不能为空");return Result.error("经纬度不能为空");//判断是否存在重复的街道名称if (inDb!= null) {return Result.error("街道名称" + name + "不能重复");return Result.error("批量导入街镇失败");

			
		

	





	

		

			

				
					
springboot 实现导入excel，解析excel数据并向数据库插入insert

				
			

			

				

					喵仙人
				

				

					08-10
					
					1837
					
				

			

		

		

			
				
导入excel文件，大致如下：

controller层：此处file文件写死，自行修改
@RestController
@RequestMapping("/import")
public class ImportExcelController {

    @Autowired
    private ImportExcelService importExcelService;

    @RequestMapping(value = "/excel",method = RequestMethod.POS

			
		

	





	

		

			

				
					
java实现Excel数据导入到mysql数据库.zip

				
			

			

				

					04-26
				

			

		

		

			
				
本项目"java实现Excel数据导入到mysql数据库"旨在利用Java技术将Excel表格中的数据高效地导入到MySQL数据库，并且在数据库中存在相同数据时进行更新，同时也支持将数据库中的数据导出到Excel表中。这个过程涉及到多...

			
		

	





	

		

			

				
					
简单java做excel解析并验证插入数据库.pdf

				
			

			

				

					10-05
				

			

		

		

			
				
这个PDF文档标题提到的“简单java做excel解析并验证插入数据库”涉及到的关键技术包括Excel文件解析、文件上传以及数据库操作。下面我们将详细讨论这些知识点。  首先，Excel文件解析通常使用Apache POI库，这是一个...

			
		

	





	

		

			

				
					
如何用Java将excel数据导入数据库

				
			

			

				

					01-20
				

			

		

		

			
				
	　这是已经成功插入数据化数据库中数据的记录，我新建的表为db. 这是在控制台的数据输入 	　很多时候拼接字符串是很多项目必须做的，该代码中我拼接了不少sql语句，也许有更简单的方式，但是我现在只能想到这一

			
		

	





	

		

			

				
					
使用POI解析excel文件并写入数据库

				
			

			

				

					05-08
				

			

		

		

			
				
 将解析后的数据插入数据库。例如，如果你使用PreparedStatement，可以这样做：  ```java  Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);  PreparedStatement pstmt = conn.prepareStatement...

			
		

	





	

		

			

				
					
JSP上传excel及excel插入至数据库的方法

				
			

			

				

					10-23
				

			

		

		

			
				
本文主要讨论了使用JSP上传Excel文件，并将读取的数据插入数据库的方法。此过程涉及几个关键步骤：首先是使用JSP实现文件上传功能，接着是读取Excel文件内容，然后是将数据插入数据库，最后是对文件上传进行一些验证...

			
		

	





	

		

			

				
					
Python将Excel文件插入Mysql数据库(脚本)

				
			

			

				

					weixin_42636075的博客
				

				

					11-30
					
					5113
					
				

			

		

		

			
				
最近接到一个需求，就是将多个Eccel文件（表头相同；每个都非常大，约60多万行，每个都是！！）先合并在一起，再做一些处理，但是Excel表格一个文件根本存不下这么多！更别提打开再做处理，怎么办？当然是借助Mysql啦，利用Python脚本将每一个文件内容插入进去，（其实也可以单个依次导入，但是有多个Excel文件，这样比较繁琐。更重要的是我会Python直接写个脚本）

			
		

	





	

		

			

				
					
Java解析Excel表中数据并导入数据库

				
			

			

				

					07-15
				

			

		

		

			
				
你可以使用 Apache POI 库来解析 Excel 表中的数据，并使用 JDBC 将数据导入数据库。是一个简单的 Java 代码示：

首先，确保你已经将 POI 和 JDBC 相关的 JAR 文件添加到你的项目中。

```java
import java.FileInputStream;
import java.sql;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelImporter {
    public static void main(String[] args) {
        String excelFilePath = "path/to/your/excel/file.xlsx";
        
        try (FileInputStream inputStream = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = 0;

            for (Row row : sheet) {
                if (rowCount == 0) {
                    rowCount++;
                    continue;
                }

                String column1 = row.getCell(0).getStringCellValue();
                int column2 = (int) row.getCell(1).getNumericCellValue();
                // 获取其他列的数据

                // 将数据插入数据库
                insertToDatabase(column1, column2);
                
                rowCount++;
            }
            
            System.out.println("数据导入成功！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private static void insertToDatabase(String column1, int column2) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String username = "your_username";
        String password = "your_password";
        
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            String sql = "INSERT INTO your_table (column1, column2) VALUES (?, ?)";
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, column1);
            statement.setInt(2, column2);
            
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
```

在上面的示例中，你需要将 `path/to/your/excel/file.xlsx` 替换为你实际的 Excel 文件路径。此外，你还需要根据你的数据库设置更新 `url`、`username`、`password`、`your_database` 和 `your_table`。

这个示例假设 Excel 表的第一行为标题，从第二行开始是数据。你可以根据需要调整代码以适应不同的表格结构。同时，你可以根据你的数据库表结构调整 `insertToDatabase()` 方法中的 SQL 语句和参数设置。

请确保你已正确引入所需的库和驱动程序，并在执行代码之前设置好数据库连接信息。

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值