如何用java实现将execl表格导入到数据库中
参考方法:
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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;
public class TestExcel {
//记录类的输出
static Log log = LogFactory.getLog(TestExcel.class);
//获取Excel文档的路径
public static String filePath = "D://excel.xls";
public static void main(String[] args) {
try {
// 创建对Excel工作件的引用
HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));
// 在Excel文档中一张工作表的缺省索引是0
// 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
HSSFSheet sheet = wookbook.getSheet("Sheet1");
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//遍历行
for (int i = 0; i < rows; i ) {
// 读取左上端单元格
HSSFRow row = sheet.getRow(i);
// 行不为空
if (row != null) {
//获取到Excel文件中的所有的列
int cells = row.getPhysicalNumberOfCells();
String value = "";
//遍历列
for (int j = 0; j < cells; j ) {
//获取到列的值
HSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue() ",";
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue() ",";
break;
default:
value = "0";
break;
}
}
}
// 将数据插入到mysql数据库中
String[] val = value.split(",");
TestEntity entity = new TestEntity();
entity.setNum1(val[0]);
entity.setNum2(val[1]);
entity.setNum3(val[2]);
entity.setNum4(val[3]);
entity.setNum5(val[4]);
entity.setNum6(val[5]);
TestMethod method = new TestMethod();
method.Add(entity);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
如何通过java把Excel表导入到数据库中已经创建的表...
jxl或者poi,网上到处能下载.
jxl,最的excelapi,纯java,跨平台,对图片支持差,只能读写png
poi,apache的开目,功能比jxl强大,但是在处理单元格类面有些问题,经常分不清是""还是null.
用java怎么将excel表格数据导入到mysql数据库中
参考方法:
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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;
public class TestExcel {
//记录类的输出
static Log log = LogFactory.getLog(TestExcel.class);
//获取Excel文档的路径
public static String filePath = "D://excel.xls";
public static void main(String[] args) {
try {
// 创建对Excel工作件的引用
HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));
// 在Excel文档中一张工作表的缺省索引是0
// 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
HSSFSheet sheet = wookbook.getSheet("Sheet1");
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//遍历行
for (int i = 0; i < rows; i ) {
// 读取左上端单元格
HSSFRow row = sheet.getRow(i);
// 行不为空
if (row != null) {
//获取到Excel文件中的所有的列
int cells = row.getPhysicalNumberOfCells();
String value = "";
//遍历列
for (int j = 0; j < cells; j ) {
//获取到列的值
HSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue() ",";
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue() ",";
break;
default:
value = "0";
break;
}
}
}
// 将数据插入到mysql数据库中
String[] val = value.split(",");
TestEntity entity = new TestEntity();
entity.setNum1(val[0]);
entity.setNum2(val[1]);
entity.setNum3(val[2]);
entity.setNum4(val[3]);
entity.setNum5(val[4]);
entity.setNum6(val[5]);
TestMethod method = new TestMethod();
method.Add(entity);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
用java编写代码按照正确格式读取完整excel表格的数...
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.sql.*;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import jxl.*;
public class SimUpdate {
private String fileName;
public ZfzSimUpdate(String fileName){
this.fileName = fileName;
}
static Map tNames;
static{
tNames = new HashMap();
}
/**
* 用于产 数据库的 ID 值,组成 [年时分秒(100-999)] 总共 17 位数.
* 根据不同的表名,可保证同一产生的 ID 重复
*/
private static String getDtime() {
String rid;
Date nd = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
rid = sdf.format(nd);
return rid;
}
public String getSeqNumber(String tableName) {
if(tableName == null || "".equals(tableName))
tableName = "GENERY";
Integer it;
// noinspection SynchronizeOnNonFinalField
synchronized(tNames){
it = (Integer)tNames.get(tableName);
if(it == null){
it = new Integer(100);
tNames.put(tableName, it);
}else{
if(it.intValue() > 998)
it = new Integer(100);
else
it = new Integer(1 it.intValue());
tNames.put(tableName, it);
}
}
return getDtime() String.valueOf(it);
}
private void updateDb(){
try{
Connection conn = DbPool.connectDB();
if(conn != null){
Statement stmt = conn.createStatement();
jxl.Workbook rwb = null;
try{
//构建Workbook对象 只读Workbook对象
//直接从本地文件创建Workbook
//从输入流创建Workbook
InputStream is = new FileInputStream(fileName);
rwb = Workbook.getWorkbook(is);
//Sheet(术语:工作表)就是Excel表格左下角的Sheet1,Sheet2,Sheet3但在程序中
//Sheet的下标是从0开始的
//获取第一张Sheet表
Sheet rs = rwb.getSheet(0);
//获取Sheet表中所包含的总列数
int rsColumns = rs.getColumns();
//获取Sheet表中所包含的总行数
int rsRows = rs.getRows();
//获取指这下单元格的对象引用
String simNumber = "",termSeqId = "";
//指定SIM卡号及序列号
for(int i=0;i
for(int j=0;j
Cell cell = rs.getCell(j,i);
if(j==0){
simNumber = cell.getContents();
}
termSeqId = "633" simNumber;
}
String sql = "查询SQL";
int isOk = stmt.executeUpdate(sql);
if(isOk == 0