/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2002</p>
* <p>Company: </p>
* @author:LC
* @version 1.0
*/package com.legendit.cm.excel;import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.*;
import java.sql.*;
import java.io.*;
import java.util.*;
import java.text.SimpleDateFormat;
import com.legendit.cm.dbbean.DBOptBean;public class XlsDataTransport {
String fileName;
String path;
public XlsDataTransport() {
fileName = null;
path = "";
} //*数据写入xls文件
public boolean DBToFile(String sql) {
ArrayList colName = new ArrayList();
String tableName = getTableName(sql);
fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new java.util.
Date()) + ".xls";
try {
DBOptBean dbb = new DBOptBean();
Connection con = dbb.getConnection();
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sql); //获得列名
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
colName.add(rs.getMetaData().getColumnName(i));
}
String column[] = new String[colName.size()];
colName.toArray(column);
//获得行数
rs.last();
int rowCount = rs.getRow();
rs.beforeFirst();
//System.out.println(rowCount); HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
wb.setSheetName(0, tableName);
HSSFRow row = s.createRow(0);
for (int i = 0; i < column.length; i++) {
row.createCell( (short) i).setCellValue(column[i]);
}
//创建一空单元 与手工的匹配
row.createCell( (short) column.length);
for (int i = 1; i <= rowCount && rs.next(); i++) {
row = s.createRow(i);
for (int j = 0; j < column.length; j++) {
HSSFCell cell = row.createCell( (short) j);
//解决乱码 设置字符集
cell.setEncoding(cell.ENCODING_UTF_16);
//日期写入
if (rs.getMetaData().getColumnTypeName(j + 1) == "DATE") {
cell.setCellValue(rs.getDate(column[j]).toString());
}
else {
if (rs.getMetaData().getColumnTypeName(j + 1) == "NUMBER") {
cell.setCellValue(rs.getDouble(column[j]));
}
else {
cell.setCellValue(DBOptBean.toChinese(rs.getString(column[j]) == null ?
"" : DBOptBean.toChinese(rs.getString(column[j]))));
}
}
}
//创建一空单元 与手工的匹配
row.createCell( (short) column.length);
}
//导出文件
FileOutputStream fos = new FileOutputStream(path + fileName);
wb.write(fos);
fos.close();
rs.close();
stmt.close();
con.close();
}
catch (Exception e) {
System.out.println(e);
return false;
}
return true;
} //*sql语句中取得表名
private static String getTableName(String sql) {
int pos = sql.toLowerCase().indexOf("from");
if (pos > 0) {
String t = sql.toLowerCase().substring(pos + 4).trim();
pos = t.indexOf(" ");
if (pos > 0)
return t.substring(0, pos).trim();
else
return t;
}
else
return "";
} public String getExportedFileName() {
return this.fileName;
} public void setExportPath(String path) {
this.path = path;
}
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2002</p>
* <p>Company: </p>
* @author:LC
* @version 1.0
*/package com.legendit.cm.excel;import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.*;
import java.sql.*;
import java.io.*;
import java.util.*;
import java.text.SimpleDateFormat;
import com.legendit.cm.dbbean.DBOptBean;public class XlsDataTransport {
String fileName;
String path;
public XlsDataTransport() {
fileName = null;
path = "";
} //*数据写入xls文件
public boolean DBToFile(String sql) {
ArrayList colName = new ArrayList();
String tableName = getTableName(sql);
fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new java.util.
Date()) + ".xls";
try {
DBOptBean dbb = new DBOptBean();
Connection con = dbb.getConnection();
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(sql); //获得列名
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
colName.add(rs.getMetaData().getColumnName(i));
}
String column[] = new String[colName.size()];
colName.toArray(column);
//获得行数
rs.last();
int rowCount = rs.getRow();
rs.beforeFirst();
//System.out.println(rowCount); HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
wb.setSheetName(0, tableName);
HSSFRow row = s.createRow(0);
for (int i = 0; i < column.length; i++) {
row.createCell( (short) i).setCellValue(column[i]);
}
//创建一空单元 与手工的匹配
row.createCell( (short) column.length);
for (int i = 1; i <= rowCount && rs.next(); i++) {
row = s.createRow(i);
for (int j = 0; j < column.length; j++) {
HSSFCell cell = row.createCell( (short) j);
//解决乱码 设置字符集
cell.setEncoding(cell.ENCODING_UTF_16);
//日期写入
if (rs.getMetaData().getColumnTypeName(j + 1) == "DATE") {
cell.setCellValue(rs.getDate(column[j]).toString());
}
else {
if (rs.getMetaData().getColumnTypeName(j + 1) == "NUMBER") {
cell.setCellValue(rs.getDouble(column[j]));
}
else {
cell.setCellValue(DBOptBean.toChinese(rs.getString(column[j]) == null ?
"" : DBOptBean.toChinese(rs.getString(column[j]))));
}
}
}
//创建一空单元 与手工的匹配
row.createCell( (short) column.length);
}
//导出文件
FileOutputStream fos = new FileOutputStream(path + fileName);
wb.write(fos);
fos.close();
rs.close();
stmt.close();
con.close();
}
catch (Exception e) {
System.out.println(e);
return false;
}
return true;
} //*sql语句中取得表名
private static String getTableName(String sql) {
int pos = sql.toLowerCase().indexOf("from");
if (pos > 0) {
String t = sql.toLowerCase().substring(pos + 4).trim();
pos = t.indexOf(" ");
if (pos > 0)
return t.substring(0, pos).trim();
else
return t;
}
else
return "";
} public String getExportedFileName() {
return this.fileName;
} public void setExportPath(String path) {
this.path = path;
}
//*读取文件
public int FileToDB(String fileName) {
int returnVal = 0;
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
String tableName = null;
ArrayList colName = new ArrayList();
ArrayList values = new ArrayList();
try {
FileInputStream fis=new FileInputStream(fileName);
fs = new POIFSFileSystem(fis);
wb = new HSSFWorkbook(fs);
fis.close();
}
catch (IOException e) {
e.printStackTrace();
} HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
tableName = wb.getSheetName(0);
//得到列名
for (short i = 0; i < row.getLastCellNum(); i++) {
colName.add(row.getCell(i).getStringCellValue());
//System.out.println(row.getCell(i).getStringCellValue());
}
String column[] = new String[colName.size()];
colName.toArray(column);
ResultSet rs = null;
//获得表结构
try {
String rsSql = "select " + column[0];
for (int i = 1; i < column.length; i++) {
rsSql += "," + column[i];
}
rsSql += " from " + tableName + " where 1=2";
//System.out.println(rsSql);
DBOptBean dbb = new DBOptBean();
Connection con = dbb.getConnection();
Statement stmt = con.createStatement(); //此记录集用于获得表结构 查询列类型
rs = stmt.executeQuery(rsSql);
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
//行 外循环控制行 内循环控制列 用于读取文件中所有信息
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
for (int j = 0; j < row.getLastCellNum(); j++) {
//System.out.println(j);
//try 当单元内为空时(一般手工录入发生)抛出异常NullPointerException强制其为''
try {
//判断数据库中字段类型 number
if (rs.getMetaData().getColumnTypeName(j + 1) == "NUMBER") { values.add("'" +
String.valueOf(row.getCell( (short) j).
getNumericCellValue()) +
"'");
continue;
}
//判断数据库中字段类型 date
if (rs.getMetaData().getColumnTypeName(j + 1) == "DATE") {
//判断日期在文件中存在形式,0为数字形式1为字符形式,然后用不同方法提取
if (row.getCell( (short) j).getCellType() == 0) {
values.add("to_date('" +
(new SimpleDateFormat("yyyy-MM-dd").
format(row.getCell( (short) j).getDateCellValue())) +
"','yyyy-mm-dd')");
}
else {
values.add("to_date('" +
(row.getCell( (short) j).getStringCellValue()) +
"','yyyy-mm-dd')");
}
continue;
}
//判断数据库中字段类型 varchar & char & other
if (row.getCell( (short) j).getCellType() == 0) { values.add("'" +
String.valueOf( (long) row.getCell( (short) j).
getNumericCellValue()) +
"'");
}
else {
values.add("'" + row.getCell( (short) j).getStringCellValue() +
"'");
}
}
catch (java.lang.NullPointerException npe) {
//System.out.println("err3");
values.add("''");
}
} String[] strValues = new String[values.size()];
values.toArray(strValues);
String sql = "insert into " + tableName + "(" + column[0];
for (int k = 1; k < column.length; k++) {
sql += "," + column[k];
}
sql += ") values(" + DBOptBean.toISO(strValues[0]);
for (int k = 1; k < strValues.length; k++) {
sql += "," + DBOptBean.toISO(strValues[k]);
}
sql += ")";
/*----------调试 测试用
System.out.println(sql);
System.out.println();
/*FileWriter fw=new FileWriter("tese.txt");
fw.write(sql);
fw.flush();
//------------------*/
returnVal += stmt.executeUpdate(sql);
colName = new ArrayList();
values = new ArrayList();
}
rs.close();
stmt.close();
con.close();
} catch (Exception e) {
System.out.println("fileToDB() err:"+e);
return returnVal;
} //System.out.println(new SimpleDateFormat("yyyy-MM-dd").format(row.getCell((short)4).getDateCellValue()));
return returnVal;
}/*-----------------测试用
public static void main(String args[]) {
new XlsDataTransport().DBToFile("select * from logs");
//System.out.println(new XlsDataTransport().FileToDB("d.xls"));
}
//*/
}