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

转载自：http://skyuck.iteye.com/blog/453540

myeclipse中要先导入POI包（网上有下载的）。再选择lib文件夹，右击选择import将这个包import进来。

1.编写数据库链接类

Java代码

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.编写数据库操作类

Java代码

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表格实体类

Java代码

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.编写主键生成方法

Java代码

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操作类

Java代码

 

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类的方法

Java代码

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.编写客户端

Java代码

 

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

 

心得和修改：

因为excel表会导入到数据库中，所以表格实体类可以用hibernate的方法自动生成（变量名均和数据库她table中的字段名相同）