1在Java中读写Excel文件
Jakarta的POI项目提供了一组操纵Windows文档的Java API,如下几个类提供了操作Excel文件的方便的途径:HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell,分别代表了Excel电子表格中的Book、Sheet、行和单元格。
你可以在下面地址下载到最新的POI工具包:
http://jakarta.apache.org/poi/index.html
下面的代码创建一个Excel文件test.xls,并在第一行的第一格中写入一条消息:
import org.apache.poi.hssf.usermodel.*;
import java.io.FileOutputStream;// code run against the jakarta-poi-1.5.0-FINAL-20020506.jar.
public class PoiTest
{
static public void main(String[] args) throws Exception
{
FileOutputStream fos = new FileOutputStream("d:/test.xls");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
wb.setSheetName(0, "first sheet");
HSSFRow row = s.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue("Hello! This message is generated from POI.");
wb.write(fos);
fos.close();
}
}
读取Excel文件时,首先生存一个POIFSFileSystem对象,由POIFSFileSystem对象构造一个HSSFWorkbook,该HSSFWorkbook对象就代表了Excel文档。下面代码读取上面生成的Excel文件写入的消息字串:
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
try {
fs = new POIFSFileSystem(new FileInputStream("d:/test.xls"));
wb = new HSSFWorkbook(fs);
} catch (IOException e) { e.printStackTrace(); }
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.getCell((short) 0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
String msg = cell.getStringCellValue();
很详细,也亲自用过,不过一定要下那个api才可以,不然不可能读出excel的数据,另外,加入数据库就不用说了吧,把取出来的数据加入数据库还是很简单的吧~
2这个看你的情况吧,如果只是一时所需,用Navicat for MySQL很简单就可以导入,这个软件自身带导入各种文件格式的功能!如果是经常有这样的操作的话用POI试下,我没做过,我给你个读EXCEL的简单例子,你可以深入下:
import
org.apache.poi.poifs.filesystem.POIFSFileSystem;
import
org.apache.poi.hssf.record.*;
import
org.apache.poi.hssf.model.*;
import
org.apache.poi.hssf.util.*;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import
java.io.FileInputStream;
public
class
Xls2table {
public
static
String fileToBeRead =
"D:/joek.xls"
;
public
static
void
main(String argv) {
try
{
HSSFWorkbook workbook =
new
HSSFWorkbook(
new
FileInputStream(
fileToBeRead));
HSSFSheet sheet = workbook.getSheet(
"Sheet1"
);
int
rows = sheet.getPhysicalNumberOfRows();
for
(
int
r =
0
; r < rows; r++) {
HSSFRow row = sheet.getRow(r);
if
(row !=
null
) {
int
cells = row.getPhysicalNumberOfCells();
String value =
""
;
for
(
short
c =
0
; c < cells; c++) {
HSSFCell cell = row.getCell(c);
if
(cell !=
null
) {
switch
(cell.getCellType()) {
case
HSSFCell.CELL_TYPE_FORMULA:
//
break
;
case
HSSFCell.CELL_TYPE_NUMERIC:
value += (
long
) cell.getNumericCellValue()
+
"/t"
;
break
;
case
HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue() +
"/t"
;
break
;
default
:
value +=
"/t"
;
}
}
}
// 下面可以将查找到的行内容用SQL语句INSERT到oracle
System.out.println(value);
//
}
}
}
catch
(Exception e) {
System.out.println(e);
}
}
}
3太具体
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.swing.JOptionPane;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import Bean.DBClass;
import Bean.DBContr;
public class Test {
static String UserName = "newExcel"; // 生成的数据库名字
private static Connection conn = null;
private static String drive = "com.mysql.jdbc.Driver";
private static String DBurl ="jdbc:mysql://localhost:3306/muren";
private static String name = "sa"; //数据库账号
private static String pwd = "sa"; //数据库,密码
private static Statement st=null;
public static void main(String[] args) {
readExcel("D:/newExcel.xls");
UserName = "UserName";
}
public static void readExcel(String url) {
File filename = new File(url);
Workbook wb = null;
String create = "create table " + UserName + "(";
String sql = "insert into " + UserName + "(";
String parameter = "";
String value = "";
String insert = "";
try {
wb = Workbook.getWorkbook(filename);
Sheet s = wb.getSheet(0);// 第1个sheet
Cell c = null;
int row = s.getRows();// 总行数
int col = s.getColumns();// 总列数
for (int i = 0; i < col; i++) {
if (i == col-1) {
create += s.getCell(i, 0).getContents() + " varchar(255)";
parameter += s.getCell(i, 0).getContents();
} else {
create += s.getCell(i, 0).getContents() + " varchar(255),";
parameter += s.getCell(i, 0).getContents() + ",";
}
}
create += ")";
System.out.println("数据库生成表语句---" + create);
try {
Create(create);
} catch (Exception e) {
int a = JOptionPane.showConfirmDialog(null, "数据库已经存在的表,确定删除吗?",
"温馨提示", JOptionPane.YES_NO_OPTION);
if (a == 0) {
try {
Create("drop table " + UserName + "");
Create(create);
} catch (Exception e1) {
return;
}
} else {
int aa = JOptionPane.showConfirmDialog(null, "是否继续添加到原来表单裏面",
"温馨提示", JOptionPane.YES_NO_OPTION);
if (aa == 1) {
return;
} }
}
System.out.println("--------------------------------------");
for (int i = 1; i < row; i++) {
value="";
for (int j = 0; j < col; j++) {
c = s.getCell(j, i);
if (j < col - 1) {
value += "'" + c.getContents() + "',";
} else {
value += "'" + c.getContents() + "'";
}
}
insert = sql + parameter + ") values(" + value + ")";
System.out.println("添加语句-------" + insert);
int a = insert(insert);
if (a > 0) {
System.out.println("成功添加" + i + "次");
} else {
System.out.println("第" + i + "次失败了");
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
}
public static Connection getConn() {
try {
Class.forName(drive);
conn = DriverManager.getConnection(DBurl, name, pwd);
} catch (Exception e) {
e.printStackTrace();
JOptionPane.showMessageDialog(null, "数据库连接错误");
}
return conn;
}
public static void Close() {
if (conn != null)
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static int insert(String sql){
int result=0;
try {
st=getConn().createStatement();
result=st.executeUpdate(sql);
} catch (Exception e) {
System.out.println("添加失败");
}finally{
Close();
}
return result;
}
public static int Create(String sql) throws Exception{
int result=0;
try {
st=getConn().createStatement();
result=st.executeUpdate(sql);
}finally{
Close();
}
return result;
}
}