写入：没有关闭流，容错并不完善。
private void insertFile(HttpServletRequest request,
HttpServletResponse response) throws IOException { String path_member = request.getParameter("path_member");
List list = this.insert("f:/tmp001.xls", "gs_sale_members"); // url
// table
PrintWriter pw = response.getWriter();
pw.print("{\"result\":" + list + "}"); // 返回插入失败的行数
pw.close();
}
/**
*
* @param path
* 要解析的excel文件路径
* @param dataTable
* 要写入到数据库中的表名
* @throws BiffException
* @throws IOException
*/
public List insert(String path, String dataTable) throws IOException,
IOException { int a = 0;
File file = new File(path); List list = new ArrayList(); HSSFWorkbook rwb = null;
// 创建输入流
InputStream is = new FileInputStream(path);
rwb = new HSSFWorkbook(is); // 得到工作簿
HSSFSheet sheet = rwb.getSheetAt(0); int rsRows = sheet.getLastRowNum();// 获取总行数
String simNumber = "";// 每个单元格中的数据 DBConn jdbc = new DBConn(); String str = "gs_salemen_seq,gs_salemen_name,gs_salemen_id,gs_salemen_papers_id,gs_salemen_jgid,gs_salemen_type,gs_salemen_status";// 拼接要插入的列
HSSFRow row = sheet.getRow(0); // 获取第一行
int rsColumns = row.getPhysicalNumberOfCells();// 列数
// for (short j = 0; j < rsColumns; j++) {
// HSSFCell cell = row.getCell(j);
// simNumber = cell.getStringCellValue();
// if (j == rsColumns - 1) {
// // 最后一列不用加逗号
// str += simNumber;
// } else {
// str += simNumber + ",";
// }
// }
for (short i = 0; i < rsRows; i++) {
HSSFRow row1 = sheet.getRow(i); // 获取行
// 拼接sql
String sql = "insert into " + dataTable + "(" + str + ") values("; for (short j = 0; j < rsColumns; j++) { HSSFCell cell = row1.getCell(j);
if (cell != null) {
row1.getCell(j).setCellType(cell.CELL_TYPE_STRING);
}
System.out.println(cell);
simNumber = cell.getStringCellValue();
if (j == 0) {
sql += base.createId("gs_salemen_seq") + ",'" + simNumber
+ "',";
} else if (j == 5) {
sql += "'" + simNumber + "'";
} else {
sql += "'" + simNumber + "',";
}
}
sql += " )";
a = jdbc.executeUpdate(sql);// 执行sql
if (a == 0) {
list.add(i);
}
// 查看拼的sql
System.out.println("第" + (i + 1) + "行" + sql);
}
jdbc.closeStmt();
jdbc.closeConnection();
return list;
}
package com.lj.util; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement; /**
* Oracle数据库连接
*
*/
public class DBConn { private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null; /** Oracle数据库连接 URL */
private final static String DB_URL = "jdbc:oracle:thin:@192.168.1.7:1521:orcl"; /** Oracle数据库连接驱动 */
private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; /** 数据库用户名 */
private final static String DB_USERNAME = "scott"; /** 数据库密码 */
private final static String DB_PASSWORD = "tiger"; /**
* 获取数据库连接
*
* @return
*/
public Connection getConnection() {
/** 声明Connection连接对象 */
Connection conn = null;
try {
/** 使用 Class.forName()方法自动创建这个驱动程序的实例且自动调用DriverManager来注册它 */
Class.forName(DB_DRIVER);
/** 通过 DriverManager的getConnection()方法获取数据库连接 */
conn = DriverManager
.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
stmt = conn.createStatement();
} catch (Exception ex) {
ex.printStackTrace();
}
return conn;
} /**
* 查询数据部分
*
* @return ResultSet
*/
public ResultSet executeQuery(String sqlStr) {
if (sqlStr == null || sqlStr.length() == 0)
return null;
try {
this.getConnection();
rs = stmt.executeQuery(sqlStr);
return rs;
} catch (SQLException ex) {
ex.printStackTrace();
return null;
} } /**
* 更新数据部分
*
* @return 更新是否成功
*/
public int executeUpdate(String sqlStr) { if (sqlStr == null || sqlStr.length() == 0)
return 0;
try {
this.getConnection();
stmt.executeUpdate(sqlStr);
return 1;
} catch (SQLException ex) {
ex.printStackTrace();
return 0;
} finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
} public void closeStmt() {
try {
if (stmt != null) {
stmt.close();
}
} catch (Exception e) {
e.printStackTrace();
}
} /**
* 关闭数据库连接
*
* @param connect
*/
public void closeConnection() {
try {
if (conn != null) {
/** 判断当前连接连接对象如果没有被关闭就调用关闭方法 */
if (!conn.isClosed()) {
conn.close();
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
} }
读出：
private void outExcel(HttpServletRequest request,
HttpServletResponse response) throws IOException {
// 输出地址
String loc = request.getParameter("loc");
// 查询的表
String table = request.getParameter("table"); File file = new File(loc);
if (!file.exists()) { file.createNewFile();
}
String sql = "select * from " + table;
List<Map<String, Object>> list = base.querySql(sql);
write2excel(list, file);
}
public static void write2excel(List<Map<String, Object>> list, File file) {
HSSFWorkbook excel = new HSSFWorkbook();
HSSFSheet sheet = excel.createSheet("dept");
HSSFRow firstRow = sheet.createRow(0);
HSSFCell cells[] = new HSSFCell[3];
String[] titles = new String[] { "deptno", "dname", "loc" };
for (int i = 0; i < 3; i++) {
cells[0] = firstRow.createCell(i);
cells[0].setCellValue(titles[i]);
}
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(i + 1);
// Computer computer = computers.get(i);
HSSFCell cell = row.createCell(0);
System.out.println(list.get(i).get("deptno"));
cell.setCellValue(list.get(i).get("deptno").toString());
cell = row.createCell(1);
cell.setCellValue((String) list.get(i).get("dname"));
cell = row.createCell(2);
cell.setCellValue((String) list.get(i).get("loc"));
cell = row.createCell(3);
}
OutputStream out = null;
try {
out = new FileOutputStream(file);
excel.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}