package com.wfg.excel;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
* JXL 从excel导入数据到mysql 和 从mysql导出数据到excel 的工具类
*
* @author sf
*
* 2016年8月24日 下午11:00:41
*/
public class JXLUtil {
/**
*
* @param excelFilePath excel文件路径
* @param sheetName 第一个工作表名称
* @param cols 对导出的excel的列进行命名的数组
* @param sql JDBC执行的sql查询语句
* @throws Exception
*/
public static void toExcel(String excelFilePath, String sheetName, String[] cols, String sql) throws Exception {
Connection con = null;
PreparedStatement ps = null;
Statement st = null;
ResultSet rs = null;
con = JdbcUtil.getConnection();
// 对于大表，使用setFetchSize进行每次多行读取
ps = (PreparedStatement) con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchDirection(ResultSet.FETCH_REVERSE);
rs = ps.executeQuery();
File excelFile = new File(excelFilePath);
if (!excelFile.exists()) {
excelFile.getParentFile().mkdirs();
}
// 创建输出Excel
WritableWorkbook book = Workbook.createWorkbook(excelFile);
// 创建工作sheet
WritableSheet sheet = book.createSheet(sheetName, 0);
// 传入 String[] clos 作为excel的第一行
for (int i = 0; i < cols.length; i++) {
// Label的坐标(column,row,content)
Label label = new Label(i, 0, cols[i]);// 0是第一行
sheet.addCell(label);
}
int j = 0;
while (rs.next()) {
j++;// 表示行
for (int i = 0; i < cols.length; i++) {
// Label的坐标(column,row,content)
sheet.addCell(new Label(i, j, rs.getString(i + 1)));
}
}
book.write();
book.close();
System.out.println("ok....");
JdbcUtil.closeResource(con, st, rs);
}
/**
*
* @param excelFilePath excel文件路径
* @param sheetName 第一个工作表名称
* @param cols 查询出的列数
* @param sql JDBC执行的sql查询语句
* @throws Exception
*/
public static void toExcel(String excelFilePath, String sheetName, int cols, String sql) throws Exception {
Connection con = null;
PreparedStatement ps = null;
Statement st = null;
ResultSet rs = null;
con = JdbcUtil.getConnection();
// 对于大表，使用setFetchSize进行每次多行读取
ps = (PreparedStatement) con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchDirection(ResultSet.FETCH_REVERSE);
rs = ps.executeQuery();
File excelFile = new File(excelFilePath);
if (!excelFile.exists()) {
excelFile.getParentFile().mkdirs();
}
// 创建输出Excel
WritableWorkbook book = Workbook.createWorkbook(excelFile);
// 创建工作sheet
WritableSheet sheet = book.createSheet(sheetName, 0);
int j = 0;
while (rs.next()) {
for (int i = 0; i < cols; i++) {
// Label的坐标(column,row,content)
sheet.addCell(new Label(i, j, rs.getString(i + 1)));
}
j++;// 表示行
}
book.write();
book.close();
System.out.println("ok....");
JdbcUtil.closeResource(con, st, rs);
}
/**
*
* @param excelFilePath
* @param tableName 使用excel的第一行数据进行建表
* @throws Exception
*/
public static void fromExcel(String excelFilePath, String tableName) throws Exception {
Connection conn = JdbcUtil.getConnection();
Statement st = conn.createStatement();
// 创建读入流
Workbook book = Workbook.getWorkbook(new FileInputStream(excelFilePath));
// 默认获取第一张sheet表
Sheet sheet = book.getSheet(0);
// 获取总行数
int rowSize = sheet.getRows();
// 获取总列数
int cellSize = sheet.getColumns();
// 创建数组，存储每行的数据
String[] val = new String[cellSize];
// 进入循环读取excel文件
for (int j = 0; j < rowSize; j++) {
for (int i = 0; i < cellSize; i++) {
Cell cell = sheet.getCell(i, j);// 坐标是 (column,row)
val[i] = cell.getContents();// 把每行的数据存储到数组里
}
///////////////////////// 取第一行数据进行建表
if (j == 0) {
String sql = "create table " + tableName + "(";
for (int k = 0; k < cellSize; k++) {
if (k == val.length - 1) {
sql += val[k] + " varchar(255))";
} else {
sql += val[k] + " varchar(255),";
}
}
System.out.println(sql);
st.executeUpdate(sql);
} else {
String sql = "insert into " + tableName + " values('";
for (int k = 0; k < val.length; k++) {
if (k == val.length - 1) {
sql += val[k] + "')";
} else {
sql += val[k] + "','";
}
}
System.out.println(sql);
st.executeUpdate(sql);
}
}
// 关流
JdbcUtil.closeResource(conn, st);
}
/**
*
* @param excelFilePath
* @param tableName
* @param cols 使用给定的数组名称进行建表
* @throws Exception
*/
public static void fromExcel(String excelFilePath, String tableName, String[] cols) throws Exception {
Connection conn = JdbcUtil.getConnection();
Statement st = conn.createStatement();
// 创建读入流
Workbook book = Workbook.getWorkbook(new FileInputStream(excelFilePath));
// 默认获取第一张sheet表
Sheet sheet = book.getSheet(0);
// 获取总行数
int rowSize = sheet.getRows();
// 获取总列数
int cellSize = sheet.getColumns();
// 创建数组，存储每行的数据
String[] val = new String[cellSize];
///////////////////////////////////////////////////////// 建表
String sql = "create table " + tableName + "(";
for (int k = 0; k < cols.length; k++) {
if (k == cols.length - 1) {
sql += cols[k] + " varchar(255))";
} else {
sql += cols[k] + " varchar(255),";
}
}
System.out.println(sql);
st.executeUpdate(sql);
///////////////////////////////////////////////////////
// 进入循环读取excel文件
for (int j = 0; j < rowSize; j++) {
for (int i = 0; i < cellSize; i++) {
Cell cell = sheet.getCell(i, j);// 坐标是 (column,row)
val[i] = cell.getContents();// 把每行的数据存储到数组里
}
sql = "insert into " + tableName + " values('";
for (int k = 0; k < val.length; k++) {
if (k == val.length - 1) {
sql += val[k] + "')";
} else {
sql += val[k] + "','";
}
}
System.out.println(sql);
st.executeUpdate(sql);
}
// 关流
JdbcUtil.closeResource(conn, st);
}
}