如何在Java中导入Excel表数据

1，加入依赖的罐子文件：

引用：

*mysql的jar文件

*Spring_HOME/lib/poi/*.jar

2，编写数据库链接类

package com.zzg.db

import java.sql.Connection

import java.sql.DriverManager

public class DbUtils {

private static Connection conn

static {

try {

Class.forName("com.mysql.jdbc.Driver")

conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","123456")

} catch (Exception e) {

e.printStackTrace()

}

}

public static Connection getConn() {

return conn

}

public static void setConn(Connection conn) {

DbUtils.conn = conn

}

}

3，编写数据库 *** 作类

package com.zzg.db

import java.sql.Connection

import java.sql.PreparedStatement

import java.sql.SQLException

public class ExcuteData {

private PreparedStatement pstmt

public boolean ExcuData(String sql) {

Connection conn = DbUtils.getConn()

boolean flag=false

try {

pstmt = conn.prepareStatement(sql)

flag=pstmt.execute()

} catch (SQLException e) {

e.printStackTrace()

}

return flag

}

}

4，编写的Excel表格实体类

package com.zzg.model

public class TableCell {

private String _name

private String _value

public String get_name() {

return _name

}

public void set_name(String _name) {

this._name = _name

}

public String get_value() {

return _value

}

public void set_value(String _value) {

this._value = _value

}

}

5，编写主键生成方法

package com.zzg.util

import java.text.SimpleDateFormat

import java.util.Date

import java.util.Random

public class GenericUtil {

public static String getPrimaryKey()

{

String primaryKey

primaryKey = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())

Random r = new Random()

primaryKey +=r.nextInt(100000)+100000

return primaryKey

}

}

6，编写的Excel *** 作类

package com.zzg.deployData

import java.io.File

import java.io.FileInputStream

import java.io.FileNotFoundException

import java.io.IOException

import java.io.Serializable

import java.util.ArrayList

import java.util.List

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

import com.zzg.db.ExcuteData

import com.zzg.model.TableCell

import com.zzg.util.GenericUtil

public class OperExcel<T extends Serializable>{

private HSSFWorkbook workbook

private String tableName

private Class<T>type

private String sheetName

public OperExcel(File excelFile, String tableName, Class<T>type,

String sheetName) throws FileNotFoundException,

IOException {

workbook = new HSSFWorkbook(new FileInputStream(excelFile))

this.tableName = tableName

this.type = type

this.sheetName = sheetName

InsertData()

}

// 向表中写入数据

public void InsertData() {

System.out.println("yyy")

ExcuteData excuteData = new ExcuteData()

List<List>datas = getDatasInSheet(this.sheetName)

// 向表中添加数据之前先删除表中数据

String strSql = "delete from " + this.tableName

excuteData.ExcuData(strSql)

// 拼接sql语句

for (int i = 1i <datas.size()i++) {

strSql = "insert into " + this.tableName + "("

List row = datas.get(i)

for (short n = 0n <row.size()n++) {

TableCell excel = (TableCell) row.get(n)

if (n != row.size() - 1)

strSql += excel.get_name() + ","

else

strSql += excel.get_name() + ")"

}

strSql += " values ("

for (short n = 0n <row.size()n++) {

TableCell excel = (TableCell) row.get(n)

try {

if (n != row.size() - 1) {

strSql += getTypeChangeValue(excel) + ","

} else

strSql += getTypeChangeValue(excel) + ")"

} catch (RuntimeException e) {

e.printStackTrace()

} catch (Exception e) {

e.printStackTrace()

}

}

//执行sql

excuteData.ExcuData(strSql)

}

}

/**

* 获得表中的数据

* @param sheetName 表格索引(EXCEL 是多表文档,所以需要输入表索引号)

* @return 由LIST构成的行和表

*/

public List<List>getDatasInSheet(String sheetName) {

List<List>result = new ArrayList<List>()

// 获得指定的表

HSSFSheet sheet = workbook.getSheet(sheetName)

// 获得数据总行数

int rowCount = sheet.getLastRowNum()

if (rowCount <1) {

return result

}

// 逐行读取数据

for (int rowIndex = 0rowIndex <rowCountrowIndex++) {

// 获得行对象

HSSFRow row = sheet.getRow(rowIndex)

if (row != null) {

List<TableCell>rowData = new ArrayList<TableCell>()

// 获得本行中单元格的个数

int columnCount = sheet.getRow(0).getLastCellNum()

// 获得本行中各单元格中的数据

for (short columnIndex = 0columnIndex <columnCountcolumnIndex++) {

HSSFCell cell = row.getCell(columnIndex)

// 获得指定单元格中数据

Object cellStr = this.getCellString(cell)

TableCell TableCell = new TableCell()

TableCell.set_name(getCellString(

sheet.getRow(0).getCell(columnIndex)).toString())

TableCell.set_value(cellStr == null ? "" : cellStr

.toString())

rowData.add(TableCell)

}

result.add(rowData)

}

}

return result

}

/**

* 获得单元格中的内容

* @param cell

* @return result

*/

protected Object getCellString(HSSFCell cell) {

Object result = null

if (cell != null) {

int cellType = cell.getCellType()

switch (cellType) {

case HSSFCell.CELL_TYPE_STRING:

result = cell.getStringCellValue()

break

case HSSFCell.CELL_TYPE_NUMERIC:

result = cell.getNumericCellValue()

break

case HSSFCell.CELL_TYPE_FORMULA:

result = cell.getNumericCellValue()

break

case HSSFCell.CELL_TYPE_ERROR:

result = null

break

case HSSFCell.CELL_TYPE_BOOLEAN:

result = cell.getBooleanCellValue()

break

case HSSFCell.CELL_TYPE_BLANK:

result = null

break

}

}

return result

}

// 根据类型返回相应的值

@SuppressWarnings("unchecked")

public String getTypeChangeValue(TableCell excelElement)

throws RuntimeException, Exception {

String colName = excelElement.get_name()

String colValue = excelElement.get_value()

String retValue = ""

if (colName.equals("id")) {

retValue = "'" + GenericUtil.getPrimaryKey() + "'"

return retValue

}

if (colName == null) {

retValue = null

}

if (colName.equals("class_createuser")) {

retValue = "yaa101"

return "'" + retValue + "'"

}

retValue = "'" + colValue + "'"

return retValue

}

}

7，编写调用 *** 作的Excel类的方法

package com.zzg.deployData

import java.io.File

import java.io.FileNotFoundException

import java.io.IOException

public class DeployData {

private File fileOut

public void excute(String filepath) {

fileOut = new File(filepath)

this.deployUserInfoData()

}

public void deployUserInfoData() {

try {

new OperExcel(fileOut, "test", Object.class, "Sheet1")

} catch (FileNotFoundException e) {

e.printStackTrace()

} catch (IOException e) {

e.printStackTrace()

}

}

}

8，编写客户端

package com.zzg.client

import com.zzg.deployData.DeployData

public class DeployClient {

public static void main(String[] args) {

DeployData deployData = new DeployData()

deployData.excute("D://test.xls")

}

}

String[] value={"小1","小2","小3","小4","小5",}//列表框有这几个数DefaultListModel<String>model=new DefaultListModel<>()//建立数据列表框模型for(String tmp:value) {model.addElement(tmp)}//向数据列表模型中添加元素JList<Sting>jl=new JList<>(value)//创建列表框 这里面已经有值了（也就是第二行字符串数组），我们的目的是再往里面添加或者删除model.addElement("添加元素")jl.setmodel(model)//最后两行就是把“添加元素”这四个字方进列表框内了，如果想通过按钮这个方法也是可行的

String[] columnNames = {"第一列", "第二列"}//定义列字段信息

Object[][] cellData = {{"11", "12"},{"21", "22"}}//定义表格数据字段

JTable table = new JTable(cellData, columnNames)

然后可以直接将这个table添加到指定的容器上，例如有一个JFrame对象f，可以通过f.add(table)的方式来添加。

---