利用java操作Excel表格（把Excel中的数据导入数据库中）

最新推荐文章于 2023-03-17 11:43:55 发布

最新推荐文章于 2023-03-17 11:43:55 发布 · 168 阅读

文章标签：

#Excel #Java #SQL #MySQL #Apache

java 专栏收录该内容

43 篇文章

订阅专栏

本文介绍了一个使用Java实现从Excel文件批量导入数据到MySQL数据库的方法。包括数据库连接配置、Excel文件解析、数据插入等关键步骤。

0.加入依赖的jar文件
[quote]
*mysql的jar文件
*Spring_HOME/lib/poi/*.jar
[/quote]
1.编写数据库链接类


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;
	}
}

2.编写数据库操作类


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;
	}
}

3.编写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;
	}
}

4.编写主键生成方法


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;
	}
}

5.编写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 = 1; i < datas.size(); i++) {
			strSql = "insert into " + this.tableName + "(";
			List row = datas.get(i);
			for (short n = 0; n < 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 = 0; n < 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 = 0; rowIndex < rowCount; rowIndex++) {
			// 获得行对象
			HSSFRow row = sheet.getRow(rowIndex);
			if (row != null) {
				List<TableCell> rowData = new ArrayList<TableCell>();
				// 获得本行中单元格的个数
				int columnCount = sheet.getRow(0).getLastCellNum();
				// 获得本行中各单元格中的数据
				for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) {
					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;
	}
}

6.编写调用操作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();
		}
	}
}

7.编写客户端


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");
	}
}