jxl.jar调入java_java通过jxl.jar实现excel导入导出

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.io.UnsupportedEncodingException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.Label;

import jxl.write.WritableImage;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

public class CopyOfReaderDate {

public void execute(String sql) {

try {

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

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

Connection conn = null;

try {

conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/ssh", "root", "123456");

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

PreparedStatement ps = conn.prepareStatement(sql);

ps.execute();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/**

* 把excel的数据存入数据库

*/

public void executeAll() {

try {

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

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

Connection conn = null;

try {

conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/ssh", "root", "123456");

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

PreparedStatement ps = conn

.prepareStatement("insert into data(name) values(?)");

String str = readExcel();

String[] s = str.split("\\s");

System.out.println();

for (int j = 0; j < s.length; j++) {

// System.out.println(s.length);

System.out.println(s[j]);

// ps.setString(1, new String(s[j].getBytes("gbk"),"UTF-8"));

// ps.setString(1, new String(s[j].getBytes()));

if (s[j] != null && !"".equals(s[j])) {

ps.setString(1, s[j]);

ps.execute();

}

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/**

* 创建excel的数据 *

*

* @param fileName

*/

public void createExcel(String fileName) {

try {

WritableWorkbook wwb = Workbook.createWorkbook(new File(fileName));

WritableSheet sheet = wwb.createSheet("first", 0);

for (int i = 0; i < 10; i++) {

for (int j = 0; j < 5; j++) {

Label label = new Label(j, i, "这是第" + (i + 1) + "行，第"

+ (j + 1) + "列");

try {

sheet.addCell(label);

} catch (RowsExceededException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (WriteException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

wwb.write();

try {

wwb.close();

} catch (WriteException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/**

* 读取excel中的数据，并在控制台输出

*

* @param fileName

*/

public String readExcel() {

String fileName = "e:/temp2/f.xls";

StringBuffer sb = new StringBuffer();

// List list = new ArrayList();

try {

Workbook wb = Workbook.getWorkbook(new File(fileName));

Sheet[] sheet = wb.getSheets();

for (int k = 0; k < sheet.length; k++) {

sb.append("\n" + sheet[k].getName() + "\n");

if (sheet[k] != null) {

for (int i = 0; i < sheet[k].getRows(); i++) {

Cell[] cells = sheet[k].getRow(i);

if (cells != null && cells.length > 0) {

for (int j = 0; j < cells.length; j++) {

String cellValue = cells[j].getContents();

// System.out.print(cellValue);

// System.out.println(cells[j].getType());

sb.append(cellValue + "\t");

// list.add(cellValue+ "\t");

}

}

sb.append("\r\n");

// list.add("\n");

}

}

}

} catch (BiffException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

System.out.println(sb.toString());

// System.out.println(list.toString());

return sb.toString();

}

/**

* 把图片存入excel中

*

* @param filename

*/

public void writeImage(String filename) {

try {

WritableWorkbook wwb = Workbook.createWorkbook(new File(

"e:/temp2/f.xls"));

WritableSheet ws = wwb.createSheet("copysheet", 0);

File file = new File("E:/temp2/a.png");

// WritableImage image=new WritableImage(d, dg)

WritableImage i = new WritableImage(1, 4, 6, 18, file);

ws.addImage(i);

wwb.write();

try {

wwb.close();

} catch (WriteException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}