您的位置：360文档中心› java的poi技术读取Excel数据到MySQL

java的poi技术读取Excel数据到MySQL

合集下载

java获取excel文件内容的方法 -回复

java获取excel文件内容的方法 -回复

java获取excel文件内容的方法-回复Java获取Excel文件内容的方法Excel文件是一种常见的电子表格文件格式，常用于存储和处理大量数据。

在Java中，我们可以使用Apache POI库来读取和操作Excel文件。

下面将一步一步介绍如何使用Java获取Excel文件的内容。

步骤一：引入Apache POI库首先，需要在项目中引入Apache POI库。

可以从官方网站上下载最新版本的jar文件，然后将其导入到项目的classpath中，或者使用构建管理工具（如Maven或Gradle）来管理依赖。

步骤二：创建工作簿对象在Java中，使用HSSFWorkbook或XSSFWorkbook类来表示Excel工作簿。

HSSFWorkbook用于处理旧版本（.xls）的Excel文件，而XSSFWorkbook用于处理新版本（.xlsx）的Excel文件。

可以根据实际需要选择合适的类。

使用HSSFWorkbook类创建一个新的Excel工作簿对象HSSFWorkbook workbook = new HSSFWorkbook();使用XSSFWorkbook类创建一个新的Excel工作簿对象XSSFWorkbook workbook = new XSSFWorkbook();步骤三：获取工作表对象Excel工作簿由一个或多个工作表（也称为工作表）组成。

可以使用getSheet方法从工作簿中获取工作表对象。

getSheet方法接受一个字符串参数，表示要获取的工作表的名称或索引。

获取指定名称的工作表对象Sheet sheet = workbook.getSheet("Sheet1");获取索引为0的工作表对象（第一个工作表）Sheet sheet = workbook.getSheetAt(0);步骤四：遍历单元格获取数据通过工作表对象，可以使用getRow和getCell方法遍历行和列，从而获取单元格数据。

java使用POI读取excel数据

java使用POI读取excel数据

java使⽤POI读取excel数据⼀、定义 Apache POI是Apache软件基⾦会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

⼆、所需jar包：三、简单的⼀个读取excel的demo1、读取⽂件⽅法/*** 读取出filePath中的所有数据信息* @param filePath excel⽂件的绝对路径**/public static void getDataFromExcel(String filePath){//String filePath = "E:\\123.xlsx";//判断是否为excel类型⽂件if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx")){System.out.println("⽂件不是excel类型");}FileInputStream fis =null;Workbook wookbook = null;try{//获取⼀个绝对地址的流fis = new FileInputStream(filePath);}catch(Exception e){e.printStackTrace();}try{//2003版本的excel，⽤.xls结尾wookbook = new HSSFWorkbook(fis);//得到⼯作簿}catch (Exception ex){//ex.printStackTrace();try{//2007版本的excel，⽤.xlsx结尾wookbook = new XSSFWorkbook(fis);//得到⼯作簿} catch (IOException e){// TODO Auto-generated catch blocke.printStackTrace();}}//得到⼀个⼯作表Sheet sheet = wookbook.getSheetAt(0);//获得表头Row rowHead = sheet.getRow(0);//判断表头是否正确if(rowHead.getPhysicalNumberOfCells() != 3){System.out.println("表头的数量不对!");}//获得数据的总⾏数int totalRowNum = sheet.getLastRowNum();//要获得属性String name = "";int latitude = 0;//获得所有数据for(int i = 1 ; i <= totalRowNum ; i++){//获得第i⾏对象Row row = sheet.getRow(i);//获得获得第i⾏第0列的 String类型对象Cell cell = row.getCell((short)0);name = cell.getStringCellValue().toString();//获得⼀个数字类型的数据cell = row.getCell((short)1);latitude = (int) cell.getNumericCellValue();System.out.println("名字："+name+",经纬度："+latitude); }}2、测试public static void main(String[] args){getDataFromExcel("E:"+ File.separator +"123.xlsx");}3、原始数据4、结果名字：A1,经纬度：1名字：A2,经纬度：2名字：A3,经纬度：3名字：A4,经纬度：4名字：A5,经纬度：5名字：A6,经纬度：6名字：A7,经纬度：7名字：A8,经纬度：8名字：A9,经纬度：9名字：A10,经纬度：10名字：A11,经纬度：11。

java获取excel文件内容的方法

java获取excel文件内容的方法

一、介绍在开发过程中，我们经常会遇到需要读取Excel文件内容的情况。

而Java作为一种广泛应用的编程语言，具有丰富的库和功能，使得它成为一种很好的选择。

本文将介绍如何使用Java来获取Excel文件的内容，并将分为以下几个部分来进行讲解。

二、使用POI库进行Excel文件操作POI（Poor Obfuscation Implementation）是Apache基金会的一个开源项目，它为Java程序提供了读取和写入Microsoft Office格式文件的功能。

具体来说，POI库中的HSSF模块可以用于读取和操作Excel文件。

以下是使用POI库进行Excel文件操作的步骤。

1. 引入POI库首先需要引入POI库的相关依赖。

可以通过Maven来引入POI库，添加以下依赖到项目的pom.xml文件中即可：```xml<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>```2. 读取Excel文件接下来，我们可以通过POI库的相关类来读取Excel文件。

首先需要创建一个文件输入流来读取Excel文件，然后通过HSSFWorkbook类来加载文件内容，最后可以通过遍历的方式获取Excel文件的内容。

以下是一个简单的示例：```javaFileInputStream file = new FileInputStream("example.xls"); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0);for (Row row : sheet) {for (Cell cell : row) {// 处理单元格的内容}}```3. 处理Excel文件内容在读取Excel文件内容后，我们可以通过POI库提供的类来处理Excel 文件的内容，比如获取单元格的值、设置单元格的值等操作。

poi根据excel模板文件导出数据

poi根据excel模板文件导出数据

poi根据excel模板文件导出数据Poi是一款用于操作Microsoft Office文件（如Excel、Word和PowerPoint）的Java库。

它提供了丰富的API，使开发人员可以读取、创建和修改这些文件。

在本文中，我们将讨论如何使用Poi库根据Excel 模板文件导出数据。

首先，我们需要在项目中导入Poi库的依赖项。

你可以在Maven或Gradle中添加以下依赖项：Maven:```xml<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>```Gradle:```implementation 'org.apache.poi:poi:4.1.2'implementation 'org.apache.poi:poi-ooxml:4.1.2'```接下来，我们需要加载Excel模板文件。

假设我们有一个名为"template.xlsx"的Excel模板文件，其中包含我们想要导出数据的工作表。

我们可以通过以下代码加载模板文件：```javaFileInputStream fis = new FileInputStream("template.xlsx");Workbook workbook = new XSSFWorkbook(fis);Sheet sheet = workbook.getSheetAt(0); // 假设我们要操作的是第一个工作表```现在我们已经加载了模板文件，接下来我们需要根据Excel模板填充数据。

如何用Java实现把excel表中的数据导入到mysql数据库已有的表中

如何用Java实现把excel表中的数据导入到mysql数据库已有的表中

如何用Java实现把excel表中的数据导入到mysql数据库已有的表中？悬赏分：15 |解决时间：2010-12-23 13:53 |提问者：quce227如何用Java实现把excel表中的数据导入到mysql数据库已有的表中？数据库中表的字段已定好~~问题补充：主要是excel中的字段和数据库表中的字段匹配然后批量导入（插入），能否给一个实例最佳答案java 读excel 还是比较方便简单的，原理就是，先用java 读取excel，然后，一行行的写入数据库，字段的话，你自己程序里面写就行了，给你个例子：从Excel读取数据,生成新的Excel，以及修改Excelpackage common.util;import jxl.*;import jxl.format.UnderlineStyle;import jxl.write.*;import jxl.write.Number;import jxl.write.Boolean;import java.io.*;/*** Created by IntelliJ IDEA.* User: xl* Date: 2005-7-17* Time: 9:33:22* To change this template use File | Settings | File Templates.*/public class ExcelHandle{public ExcelHandle(){}/*** 读取Excel** @param filePath*/public static void readExcel(String filePath){try{InputStream is = new FileInputStream(filePath);Workbook rwb = Workbook.getWorkbook(is);//Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字，而为下标，从0开始Sheet st = rwb.getSheet("original");Cell c00 = st.getCell(0,0);//通用的获取cell值的方式,返回字符串String strc00 = c00.getContents();//获得cell具体类型值的方式if(c00.getType() == BEL){LabelCell labelc00 = (LabelCell)c00;strc00 = labelc00.getString();}//输出System.out.println(strc00);//关闭rwb.close();}catch(Exception e){e.printStackTrace();}}/*** 输出Excel** @param os*/public static void writeExcel(OutputStream os){try{/*** 只能通过API提供的工厂方法来创建Workbook，而不能使用WritableWorkbook的构造函数，* 因为类WritableWorkbook的构造函数为protected类型* method(1)直接从目标文件中读取WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile));* method(2)如下实例所示将WritableWorkbook直接写入到输出流*/WritableWorkbook wwb = Workbook.createWorkbook(os);//创建Excel工作表指定名称和位置WritableSheet ws = wwb.createSheet("Test Sheet 1",0);//**************往工作表中添加数据*****************//1.添加Label对象Label label = new Label(0,0,"this is a label test");ws.addCell(label);//添加带有字型Formatting对象WritableFont wf = newWritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);WritableCellFormat wcf = new WritableCellFormat(wf);Label labelcf = new Label(1,0,"this is a label test",wcf);ws.addCell(labelcf);//添加带有字体颜色的Formatting对象WritableFont wfc = newWritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);WritableCellFormat wcfFC = new WritableCellFormat(wfc);Label labelCF = new Label(1,0,"This is a Label Cell",wcfFC);ws.addCell(labelCF);//2.添加Number对象Number labelN = new Number(0,1,3.1415926);ws.addCell(labelN);//添加带有formatting的Number对象NumberFormat nf = new NumberFormat("#.##");WritableCellFormat wcfN = new WritableCellFormat(nf);Number labelNF = new jxl.write.Number(1,1,3.1415926,wcfN);ws.addCell(labelNF);//3.添加Boolean对象Boolean labelB = new jxl.write.Boolean(0,2,false);ws.addCell(labelB);//4.添加DateTime对象jxl.write.DateTime labelDT = new jxl.write.DateTime(0,3,new java.util.Date());ws.addCell(labelDT);//添加带有formatting的DateFormat对象DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");WritableCellFormat wcfDF = new WritableCellFormat(df);DateTime labelDTF = new DateTime(1,3,newjava.util.Date(),wcfDF);ws.addCell(labelDTF);//添加图片对象,jxl只支持png格式图片File image = new File("f:\\2.png");WritableImage wimage = new WritableImage(0,1,2,2,image);ws.addImage(wimage);//写入工作表wwb.write();wwb.close();}catch(Exception e){e.printStackTrace();}}/*** 拷贝后,进行修改,其中file1为被copy对象，file2为修改后创建的对象* 尽单元格原有的格式化修饰是不能去掉的，我们还是可以将新的单元格修饰加上去，* 以使单元格的内容以不同的形式表现* @param file1* @param file2*/public static void modifyExcel(File file1,File file2){try{Workbook rwb = Workbook.getWorkbook(file1);WritableWorkbook wwb =Workbook.createWorkbook(file2,rwb);//copyWritableSheet ws = wwb.getSheet(0);WritableCell wc = ws.getWritableCell(0,0);//判断单元格的类型,做出相应的转换if(wc.getType == BEL){Label label = (Label)wc;label.setString("The value has been modified");}wwb.write();wwb.close();rwb.close();}catch(Exception e){e.printStackTrace();}}//测试public static void main(String[] args){try{//读ExcelExcelHandle.readExcel("f:/testRead.xls");//输出ExcelFile fileWrite = new File("f:/testWrite.xls");fileWrite.createNewFile();OutputStream os = new FileOutputStream(fileWrite);ExcelHandle.writeExcel(os);//修改ExcelExcelHandle.modifyExcel(new file(""),new File(""));}catch(Exception e){e.printStackTrace();}}}2.在jsp中做相关测试，创建一个writeExcel.jsp<%response.reset();//清除Bufferresponse.setContentType("application/vnd.ms-excel");File fileWrite = new File("f:/testWrite.xls");fileWrite.createNewFile();new FileOutputStream(fileWrite);ExcelHandle.writeExcel(new FileOutputStream(fileWrite));%>在IE中浏览writeExcel.jsp就可以动态生成Excel文档了，其中response.setContentType("application/vnd.ms- excel");语句必须要，才能确保不乱码，在jsp中输入<%@page contentType="application/vnd.ms-excel;charset=GBK"%>不行。

java读取excel文件数据导入mysql数据库

java读取excel文件数据导入mysql数据库

java读取excel⽂件数据导⼊mysql数据库这是我来公司的第⼆周的⼀个⼩学习任务，下⾯是实现过程：1.建⽴maven⼯程（⽅便管理jar包）在pom.xml导⼊ jxl,mysql-connector 依赖可以在maven仓库搜索2.建⽴数据库连接类，数据库对应实体类2.编写数据库表对应的实体类，get、set⽅法等3.下⾯是编写读取excel⽂件的类，和运⾏主类package service;import java.io.File;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import jxl.Sheet;import jxl.Workbook;import excel.DB;import excel.Student;public class StudentService {/*** 查询Student表中所有的数据* @return*/public static List<Student> getAllByDb(){List<Student> list=new ArrayList<Student>();try {DB db=new DB();String sql="select * from student";ResultSet rs= db.Search(sql, null);while (rs.next()) {int id=rs.getInt("id");String s_name=rs.getString("s_name");String age=rs.getString("age");String address=rs.getString("address");list.add(new Student(id, s_name, age,address));}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return list;}/*** 查询指定⽬录中电⼦表格中所有的数据* @param file ⽂件完整路径* @return*/public static List<Student> getAllByExcel(String file){List<Student> list=new ArrayList<Student>();try {Workbook rwb=Workbook.getWorkbook(new File("F:\\student.xls"));Sheet rs=rwb.getSheet(0);//表int clos=rs.getColumns();//得到所有的列int rows=rs.getRows();//得到所有的⾏System.out.println("表的列数："+clos+" 表的⾏数:"+rows);for (int i = 1; i < rows; i++) {for (int j = 0; j < clos; j++) {//第⼀个是列数，第⼆个是⾏数String id=rs.getCell(j++, i).getContents();//默认最左边编号也算⼀列所以这⾥得j++ String s_name=rs.getCell(j++, i).getContents();String age=rs.getCell(j++, i).getContents();String address=rs.getCell(j++, i).getContents();System.out.println("id:"+id+" name:"+s_name+" sex:"+age+" address:"+address); list.add(new Student(Integer.parseInt(id), s_name,age,address));}}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}return list;}/*** 通过Id判断是否存在* @param id* @return*/public static boolean isExist(int id){try {DB db=new DB();ResultSet rs=db.Search("select * from student where id=?", new String[]{id+""});if (rs.next()) {return true;}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return false;}public static void main(String[] args) {System.out.println(isExist(1));}}运⾏主类：package service;import java.util.List;import excel.DB;import excel.Student;public class TestExcelToDb {public static void main(String[] args) {//得到表格中所有的数据List<Student> listExcel=StudentService.getAllByExcel("F:\\student.xls");DB db=new DB();for (Student student : listExcel) {int id=student.getId();System.out.println(id);if (!StudentService.isExist(id)) {//不存在就添加String sql="insert into student (id,s_name,age,address) values(?,?,?,?)";String[] str=new String[]{id+"",student.getS_name(),student.getAge(),student.getAddress()+""};db.AddU(sql, str);}else {//存在就更新String sql="update student set s_name=?,age=?,address=? where id=?";String[] str=new String[]{student.getS_name(),student.getAge(),student.getAddress()+"",id+""};db.AddU(sql, str);}}}}数据库截图：[Excel数据表头要与数据库字段对应]总结：以上是使⽤了 jxl实现的读取excel⽂件内容，并且将数据传到mysql，缺陷是:jxl仅⽀持EXCEL2003。

java实现excel表格导入数据库表

java实现excel表格导入数据库表

java实现excel表格导⼊数据库表导⼊excel就是⼀个上传excel⽂件，然后获取excel⽂件数据，然后处理数据并插⼊到数据库的过程⼀、上传excel前端jsp页⾯，我的是index.jsp在页⾯中我⾃⼰加⼊了⼀个下载上传⽂件的功能，其中超链接就是下载<%@ page language="java" contentType="text/html; charset=utf-8"pageEncoding="utf-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "/TR/html4/loose.dtd"> <html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>Insert title here</title></head><script type="text/javascript" src="jquery/1.7.2/jquery-1.7.2.min.js"></script><script type="text/javascript" src="jquery/jquery.form.js"></script><script type="text/javascript">function test1(){var form =new FormData(document.getElementById("uploadForm"));$.ajax({contentType:"multipart/form-data",url:"servlet/UploadHandleServlet",type:"post",async:false,data:form,dataType:"json",processData: false, // 告诉jQuery不要去处理发送的数据contentType: false, // 告诉jQuery不要去设置Content-Type请求头success:function(data){var result=eval(data);var filePath=result[0].filePath;//alert(filePath);var fileName = result[0].imageName;$("#download").attr("href","servlet/DownLoadServlet?filePath="+filePath);document.getElementById("download").innerHTML = fileName;//上传⽂件后得到路径，然后处理数据插⼊数据库表中importExcel(filePath);}});}function importExcel(filePath){$.ajax({url:"${pageContext.request.contextPath}/user/insertUserByExcelPath",type:"post",data:{"filePath":filePath},success:function(data){}});}</script><body>导⼊excel表格<form id="uploadForm" action="" method="post" enctype="multipart/form-data"><table><tr><td>上传⽂件：</td><td><input type="file" name="fileName" id="fileName"/></td></tr></table></form><button id="uploadFile" onclick="test1();">确定</button><!-- servlet/DownLoadServlet -->上传的⽂件<a id="download" href=""></a></body></html>后端的上传的servlet，其中需要commons-fileupload-1.2.1.jar的⽀持，然后我的保存路径savePath是⾃⼰写的⼀个配置⽂件来的，这⾥可以写上⾃⼰的上传⽂件所保存的路径就⾏,返回的是⼀个json，包括⽂件路径还有⽂件名package com.huang.servlet;import java.io.BufferedInputStream;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.util.Iterator;import java.util.List;import java.util.UUID;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import mons.fileupload.FileItem;import mons.fileupload.disk.DiskFileItem;import mons.fileupload.FileUploadBase;import mons.fileupload.FileUploadException;import mons.fileupload.ProgressListener;import mons.fileupload.disk.DiskFileItemFactory;import mons.fileupload.servlet.ServletFileUpload;import mons.fileupload.util.Streams;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.springframework.stereotype.Controller;import er;import com.huang.utils.Excel2Bean;import com.huang.utils.PropertiesUtil;/*** Servlet implementation class UploadHandleServlet*/@WebServlet("/UploadHandleServlet")public class UploadHandleServlet extends HttpServlet {private static final long serialVersionUID = 1L;/*** @see HttpServlet#HttpServlet()*/public UploadHandleServlet() {super();// TODO Auto-generated constructor stub}/*** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)*/protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {doPost(request, response);}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {System.out.println("进⼊servlet");DiskFileItemFactory fac = new DiskFileItemFactory();ServletFileUpload upload = new ServletFileUpload(fac);upload.setHeaderEncoding("UTF-8");// 获取多个上传⽂件List fileList = null;try {fileList = upload.parseRequest(request);} catch (FileUploadException e) {// TODO Auto-generated catch blocke.printStackTrace();}// 遍历上传⽂件写⼊磁盘Iterator it = fileList.iterator();while (it.hasNext()) {Object obit = it.next();if (obit instanceof DiskFileItem) {DiskFileItem item = (DiskFileItem) obit;// 如果item是⽂件上传表单域// 获得⽂件名及路径String fileName = item.getName();if (fileName != null) {String fName = item.getName().substring(item.getName().lastIndexOf("\\") + 1);String formatName = fName.substring(stIndexOf(".") + 1);// 获取⽂件后缀名String savePath = PropertiesUtil.getInstance().getProperty("uploadFile");// String savePath = this.getServletContext().getRealPath("/WEB-INF/upload");File expsfile = new File(savePath);if (!expsfile.exists()) {// 创建⽂件夹expsfile.mkdirs();}String realPath = savePath+"/"+ UUID.randomUUID().toString()+"."+formatName;System.out.println("realPath:"+realPath);BufferedInputStream bis = new BufferedInputStream(item.getInputStream());// 获得⽂件输⼊流BufferedOutputStream outStream = new BufferedOutputStream(new FileOutputStream(new File(realPath)));// 获得⽂件输出流Streams.copy(bis, outStream, true);// 开始把⽂件写到你指定的上传⽂件夹// 上传成功，则插⼊数据库File file = new File(realPath);if (file.exists()) {// request.setAttribute("realPath", realPath);// request.getRequestDispatcher("/user/insertUserByExcelPath").forward(request, response);// 返回⽂件路径String imageName = file.getName();String json = "[{\"filePath\":\""+ realPath+ "\",\"imageName\":\"" + imageName + "\"}]";response.reset();response.setContentType("text/json");response.setCharacterEncoding("UTF-8");response.getWriter().write(json);// response.getWriter().write(realPath);response.getWriter().flush();}}}}}}⼆、处理excel表格并得到含有Javabean的list在⽤ajax调⽤servlet上传⽂件后得到路径和⽂件名，然后进⾏excel数据处理，在前端的页⾯上调⽤importExcel()的js函数，传⼊刚刚得到的⽂件路径我这⾥⽤的是ssm框架中的controller（我⾃⼰也学习⼀下），这⾥也可以⽤servlet,或者Struts等。

Java实现批量导入excel表格数据到数据库中的方法

Java实现批量导入excel表格数据到数据库中的方法

Java实现批量导⼊excel表格数据到数据库中的⽅法本⽂实例讲述了Java实现批量导⼊excel表格数据到数据库中的⽅法。

分享给⼤家供⼤家参考，具体如下：1、创建导⼊抽象类package mon.excel;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.PrintStream;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;import org.apache.poi.hssf.eventusermodel.HSSFListener;import org.apache.poi.hssf.eventusermodel.HSSFRequest;import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;import stCellOfRowDummyRecord;import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;import org.apache.poi.hssf.model.HSSFFormulaParser;import org.apache.poi.hssf.record.BOFRecord;import org.apache.poi.hssf.record.BlankRecord;import org.apache.poi.hssf.record.BoolErrRecord;import org.apache.poi.hssf.record.BoundSheetRecord;import org.apache.poi.hssf.record.FormulaRecord;import belRecord;import belSSTRecord;import org.apache.poi.hssf.record.NoteRecord;import org.apache.poi.hssf.record.NumberRecord;import org.apache.poi.hssf.record.RKRecord;import org.apache.poi.hssf.record.Record;import org.apache.poi.hssf.record.SSTRecord;import org.apache.poi.hssf.record.StringRecord;import ermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;/*** 导⼊抽象类* Created by charlin on 2017/9/7.*/public abstract class HxlsAbstract implements HSSFListener {private int minColumns;private POIFSFileSystem fs;private PrintStream output;private int lastRowNumber;private int lastColumnNumber;/** Should we output the formula, or the value it has? */private boolean outputFormulaValues = true;/** For parsing Formulas */private SheetRecordCollectingListener workbookBuildingListener;private HSSFWorkbook stubWorkbook;// Records we pick up as we processprivate SSTRecord sstRecord;private FormatTrackingHSSFListener formatListener;/** So we known which sheet we're on */private int sheetIndex = -1;private BoundSheetRecord[] orderedBSRs;@SuppressWarnings("unchecked")private ArrayList boundSheetRecords = new ArrayList();// For handling formulas with string resultsprivate int nextRow;private int nextColumn;private boolean outputNextStringRecord;private int curRow;private List<String> rowlist;@SuppressWarnings( "unused")private String sheetName;public HxlsAbstract(POIFSFileSystem fs)throws SQLException {this.fs = fs;this.output = System.out;this.minColumns = -1;this.curRow = 0;this.rowlist = new ArrayList<String>();}public HxlsAbstract(String filename) throws IOException,FileNotFoundException, SQLException {this(new POIFSFileSystem(new FileInputStream(filename)));}//excel记录⾏操作⽅法，以⾏索引和⾏元素列表为参数，对⼀⾏元素进⾏操作，元素为String类型// public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;//excel记录⾏操作⽅法，以sheet索引，⾏索引和⾏元素列表为参数，对sheet的⼀⾏元素进⾏操作，元素为String类型 public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws Exception;/*** 遍历 excel ⽂件*/public void process() throws IOException {MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);formatListener = new FormatTrackingHSSFListener(listener);HSSFEventFactory factory = new HSSFEventFactory();HSSFRequest request = new HSSFRequest();if (outputFormulaValues) {request.addListenerForAllRecords(formatListener);} else {workbookBuildingListener = new SheetRecordCollectingListener(formatListener);request.addListenerForAllRecords(workbookBuildingListener);}factory.processWorkbookEvents(request, fs);}/*** HSSFListener 监听⽅法，处理 Record*/@SuppressWarnings("unchecked")public void processRecord(Record record) {int thisRow = -1;int thisColumn = -1;String thisStr = null;String value = null;switch (record.getSid()) {case BoundSheetRecord.sid:boundSheetRecords.add(record);break;case BOFRecord.sid:BOFRecord br = (BOFRecord) record;//进⼊sheetif (br.getType() == BOFRecord.TYPE_WORKSHEET) {// Create sub workbook if requiredif (workbookBuildingListener != null && stubWorkbook == null) {stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();}// Works by ordering the BSRs by the location of// their BOFRecords, and then knowing that we// process BOFRecords in byte offset ordersheetIndex++;if (orderedBSRs == null) {orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);}sheetName = orderedBSRs[sheetIndex].getSheetname();}break;case SSTRecord.sid:sstRecord = (SSTRecord) record;break;case BlankRecord.sid:BlankRecord brec = (BlankRecord) record;thisRow = brec.getRow();thisColumn = brec.getColumn();thisStr = "";break;case BoolErrRecord.sid:BoolErrRecord berec = (BoolErrRecord) record;thisRow = berec.getRow();thisColumn = berec.getColumn();thisStr = "";break;case FormulaRecord.sid:FormulaRecord frec = (FormulaRecord) record;thisRow = frec.getRow();thisColumn = frec.getColumn();if (outputFormulaValues) {if (Double.isNaN(frec.getValue())) {// Formula result is a string// This is stored in the next recordoutputNextStringRecord = true;nextRow = frec.getRow();nextColumn = frec.getColumn();} else {thisStr = formatListener.formatNumberDateCell(frec);}} else {thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';}break;case StringRecord.sid:if (outputNextStringRecord) {// String for formulaStringRecord srec = (StringRecord) record;thisStr = srec.getString();thisRow = nextRow;thisColumn = nextColumn;outputNextStringRecord = false;}break;case LabelRecord.sid:LabelRecord lrec = (LabelRecord) record;curRow = thisRow = lrec.getRow();thisColumn = lrec.getColumn();value = lrec.getValue().trim();value = value.equals("")?" ":value;this.rowlist.add(thisColumn, value);break;case LabelSSTRecord.sid:LabelSSTRecord lsrec = (LabelSSTRecord) record;curRow = thisRow = lsrec.getRow();thisColumn = lsrec.getColumn();if (sstRecord == null) {rowlist.add(thisColumn, " ");} else {value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();value = value.equals("")?" ":value;rowlist.add(thisColumn,value);}break;case NoteRecord.sid:NoteRecord nrec = (NoteRecord) record;thisRow = nrec.getRow();thisColumn = nrec.getColumn();// TODO: Find object to match nrec.getShapeId()thisStr = '"' + "(TODO)" + '"';break;case NumberRecord.sid:NumberRecord numrec = (NumberRecord) record;curRow = thisRow = numrec.getRow();thisColumn = numrec.getColumn();value = formatListener.formatNumberDateCell(numrec).trim();value = value.equals("")?" ":value;// Formatrowlist.add(thisColumn, value);break;case RKRecord.sid:RKRecord rkrec = (RKRecord) record;thisRow = rkrec.getRow();thisColumn = rkrec.getColumn();thisStr = '"' + "(TODO)" + '"';break;default:break;}// 遇到新⾏的操作if (thisRow != -1 && thisRow != lastRowNumber) {lastColumnNumber = -1;}// 空值的操作if (record instanceof MissingCellDummyRecord) {MissingCellDummyRecord mc = (MissingCellDummyRecord) record;curRow = thisRow = mc.getRow();thisColumn = mc.getColumn();rowlist.add(thisColumn," ");}// 如果遇到能打印的东西，在这⾥打印if (thisStr != null) {if (thisColumn > 0) {output.print(',');}output.print(thisStr);}// 更新⾏和列的值if (thisRow > -1)lastRowNumber = thisRow;if (thisColumn > -1)lastColumnNumber = thisColumn;// ⾏结束时的操作if (record instanceof LastCellOfRowDummyRecord) {if (minColumns > 0) {// 列值重新置空if (lastColumnNumber == -1) {lastColumnNumber = 0;}}// ⾏结束时，调⽤ optRows() ⽅法lastColumnNumber = -1;try {optRows(sheetIndex,curRow, rowlist);} catch (Exception e) {e.printStackTrace();}rowlist.clear();}}}2、创建导⼊接⼝package mon.excel;import java.util.List;public interface HxlsOptRowsInterface {public static final String SUCCESS="success";/*** 处理excel⽂件每⾏数据⽅法* @param sheetIndex* @param curRow* @param rowlist* @return success：成功，否则为失败原因* @throws Exception*/public String optRows(int sheetIndex, int curRow, List<String> rowlist) throws Exception; }3、创建实现类，在这个⽅法实现把导⼊的数据添加到数据库中package mon.excel;import java.util.List;public class HxlsInterfaceImpl implements HxlsOptRowsInterface {@Overridepublic String optRows(int sheetIndex, int curRow, List<String> datalist)throws Exception {//在这⾥执⾏数据的插⼊//System.out.println(rowlist);//saveData(datalist);return "";}}4、导⼊⼯具实现package mon.excel;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;/*** excel导⼊⼯具* Created by charlin on 2017/9/7.*/public class ExcelImportUtil extends HxlsAbstract{//数据处理beanprivate HxlsOptRowsInterface hxlsOptRowsInterface;//处理数据总数private int optRows_sum = 0;//处理数据成功数量private int optRows_success = 0;//处理数据失败数量private int optRows_failure = 0;//excel表格每列标题private List<String> rowtitle ;//失败数据private List<List<String>> failrows;//失败原因private List<String> failmsgs ;//要处理数据所在的sheet索引,从0开始private int sheetIndex;public ExcelImportUtil(String filename, int sheetIndex, HxlsOptRowsInterface hxlsOptRowsInterface) throws IOException, FileNotFoundException, SQLException {super(filename);this.sheetIndex = sheetIndex;this.hxlsOptRowsInterface = hxlsOptRowsInterface;this.rowtitle = new ArrayList<String>();this.failrows = new ArrayList<List<String>>();this.failmsgs = new ArrayList<String>();}@Overridepublic void optRows(int sheetIndex,int curRow, List<String> rowlist) throws Exception {/*for (int i = 0 ;i< rowlist.size();i++){System.out.print("'"+rowlist.get(i)+"',");}System.out.println();*///将rowlist的长度补齐和标题⼀致int k=rowtitle.size()-rowlist.size();for(int i=0;i<k;i++){rowlist.add(null);}if(sheetIndex == this.sheetIndex){optRows_sum++;if(curRow == 0){//记录标题rowtitle.addAll(rowlist);}else{String result = hxlsOptRowsInterface.optRows(sheetIndex, curRow, rowlist);if(!result.equals(hxlsOptRowsInterface.SUCCESS)){optRows_failure++;//失败数据failrows.add(new ArrayList<String>(rowlist));failmsgs.add(result);}else{optRows_success++;}}}}public long getOptRows_sum() {return optRows_sum;}public void setOptRows_sum(int optRows_sum) {this.optRows_sum = optRows_sum;}public long getOptRows_success() {return optRows_success;}public void setOptRows_success(int optRows_success) {this.optRows_success = optRows_success;}public long getOptRows_failure() {return optRows_failure;}public void setOptRows_failure(int optRows_failure) {this.optRows_failure = optRows_failure;}public List<String> getRowtitle() {return rowtitle;}public List<List<String>> getFailrows() {return failrows;}public List<String> getFailmsgs() {return failmsgs;}public void setFailmsgs(List<String> failmsgs) {this.failmsgs = failmsgs;}}5、导⼊实现⽅法：public static void main(String[] args){ExcelImportUtil importUtil;try {importUtil = new ExcelImportUtil("d:/data.xls",0, new HxlsInterfaceImpl());importUtil.process();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}更多关于java相关内容感兴趣的读者可查看本站专题：《》、《》、《》、《》及《》希望本⽂所述对⼤家java程序设计有所帮助。

  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据成功：
如果重复数据，则丢掉：
=============================================
源码部分：
=============================================
/ExcelTest/src/com/b510/client/Client.java
1 /** 2 * 3 */ 4 package com.b510.client; 5 6 import java.io.IOException; 7 import java.sql.SQLException; 8 9 import com.b510.excel.SaveData2DB; 10 11 /** 12 * @author Hongten 13 * @created 2014-5-18 14 */ 15 public class Client { 16 17 18 19 20 21 22 } } public static void main(String[] args) throws IOException, SQLException { SaveData2DB saveData2DB = new SaveData2DB(); saveData2DB.save(); System.out.println("end");
/ExcelTest/src/com/b510/excel/SaveData2DB.java
1 /** 2 * 3 */ 4 package com.b510.excel; 5 6 import java.io.IOException; 7 import java.sql.SQLException; 8 import java.util.List; 9 10 import mon; 11 import com.b510.excel.util.DbUtil; 12 import com.b510.excel.vo.Student; 13 14 /** 15 * @author Hongten 16 * @created 2014-5-18 17 */ 18 public class SaveData2DB { 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 } } } } for (int i = 0; i < list.size(); i++) { student = list.get(i); List l = DbUtil.selectOne(Common.SELECT_STUDENT_SQL + "'%" + student.getName() + "%'", student); if (!l.contains(1)) { DbUtil.insert(Common.INSERT_STUDENT_SQL, student); } else { System.out.println("The Record was Exist : No. = " + student.getNo() + " , Name = " + @SuppressWarnings({ "rawtypes" }) public void save() throws IOException, SQLException { ReadExcel xlsMain = new ReadExcel(); Student student = null; List<Student> list = xlsMain.readXls();
54 55 56 57 58 59 60 61 62 63 ;
@SuppressWarnings("static-access") private String getValue(HSSFCell hssfCell) { if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { // 返回布尔类型的值 return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { // 返回数值类型的值 return String.valueOf(hssfCell.getNumericCellValue()); } else { // 返回字符串类型的值 return String.valueOf(hssfCell.getStringCellValue()); } }
java的poi技术读取Excel数据到MySQL
项目结构：
Excel中的测试数据：
数据库结构：
对应的 SQL：
1 CREATE TABLE `student_info` ( 2 3 4 5 6 7 `id` int(11) NOT NULL AUTO_INCREMENT, `no` varchar(20) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` varchar(10) DEFAULT NULL, `score` float DEFAULT '0', PRIMARY KEY (`id`)
29 }
/ExcelTest/src/com/b510/excel/ReadExcel.java
1 /** 2 * 3 */ 4 package com.b510.excel; 5 6 import java.io.FileInputStream; 7 import java.io.IOException; 8 import java.io.InputStream; 9 import java.util.ArrayList; 10 import java.util.List; 11 12 import ermodel.HSSFCell; 13 import ermodel.HSSFRow; 14 import ermodel.HSSFSheet; 15 import ermodel.HSSFWorkbook; 16 17 import mon; 18 import com.b510.excel.vo.Student; 19 20 /** 21 * @author Hongten 22 * @created 2014-5-18 23 */ 24 public class ReadExcel { 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 } } } } // 循环行Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow != null) { student = new Student(); HSSFCell no = hssfRow.getCell(0); HSSFCell name = hssfRow.getCell(1); HSSFCell age = hssfRow.getCell(2); HSSFCell score = hssfRow.getCell(3); student.setNo(getValue(no)); student.setName(getValue(name)); student.setAge(getValue(age)); student.setScore(Float.valueOf(getValue(score))); list.add(student); public List<Student> readXls() throws IOException { InputStream is = new FileInputStream(Common.EXCEL_PATH); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); Student student = null; List<Student> list = new ArrayList<Student>(); // 循环工作表Sheet for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue;
/ExcelTest/src/com/b510/common/Common.java
1 /** 2 * 3 */ 4 package mon; 5 6 /** 7 * @author Hongten 8 * @created 2014-5-18 9 */ 10 public class Common { 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ?)"; 26 27 28 public static final String UPDATE_STUDENT_SQL = "update student_info set no = ?, name = ?, age= ?, score = ? public static final String SELECT_STUDENT_ALL_SQL = "select id,no,name,age,score from student_info"; public static final String SELECT_STUDENT_SQL = "select * from student_info where name like "; where id = ? "; // sql public static final String INSERT_STUDENT_SQL = "insert into student_info(no, name, age, score) values(?, ?, ?, // common public static final String EXCEL_PATH = "lib/student_info.xls"; // connect the database public static final String DRIVER = "com.mysql.jdbc.Driver"; public static final String DB_NAME = "test"; public static final String USERNAME = "root"; public static final String PASSWORD = "root"; public static final String IP = "192.168.1.103"; public static final String PORT = "3306"; public static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DB_NAME;
相关文档
最新文档