[color=darkred] 上一篇文章主要针对的是java操作excel的描述,但是具体很没有真正的完善,仍然需要与数据库打交道。先简单介绍这个功能的具体实现吧,主要先从properties读取你实现配置好的数据表,根据你自己定义的规范在数据库里面建模,然后下一步从excel里面读取具体的内容存入到自己的一个容器里面,最后描述生成多个insert语句插入到数据库里面去[/color]
这个针对文件操作实现读写流
//定义读文件接口
package xls.io;
import xls.core.CoreException;
import xls.core.Record;
import xls.core.Schema;
public interface Reader {
public boolean hasNext() throws CoreException;
public Record next() throws CoreException;
public void close() throws CoreException;
public Schema getSchema();
}
//接口的实现类
package xls.io;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import xls.core.Cell;
import xls.core.Column;
import xls.core.CoreException;
import xls.core.Record;
import xls.core.Schema;
import xls.core.Type;
import xls.util.ConnUtils;
import xls.util.IoUtils;
public class SQLReader implements Reader {
private Schema schema;
private ResultSet rs;
private ResultSetMetaData metadata;
private int rowCount;
private Record record;
public SQLReader(Schema schema, ResultSet rs) {
this.schema = schema;
this.rs = rs;
this.rowCount = 0;
}
public boolean hasNext() throws CoreException {
try {
while (rowCount < schema.getStartRow()) {
System.err.println("[Skip Record] row=" + rowCount);
if (!rs.next()) {
return false;
}
++rowCount;
}
if (rowCount > schema.getEndtRow()) {
return false;
}
return rs.next();
} catch (SQLException sqlex) {
throw new CoreException(sqlex);
}
}
public Record next() throws CoreException {
if (record == null) {
record = new Record(schema, rowCount);
} else {
record.setRowIndex(rowCount);
record.clearCells();
}
try {
if (metadata == null) {
metadata = rs.getMetaData();
}
int colTotal = metadata.getColumnCount();
Type srcType = null;
Type dstType = null;
Column column = null;
Cell cellObject = null;
for (int i = 1; i <= colTotal && (i <= schema.getColumnCount()); ++i) {
column = schema.getColumn(i - 1);
if (column.isNull()) {
record.addCell(Cell.NULL_CELL);
} else {
srcType = column.getInType();
dstType = column.getType();
if (srcType == null) {
srcType = dstType;
}
if (column.useDefault()) {
cellObject = new Cell(schema, rowCount, i - 1,
column.getDefaultValue());
} else {
cellObject = new Cell(schema, rowCount, i - 1,
IoUtils.readCell(rs, i, srcType, dstType));
}
record.addCell(cellObject);
}
}
} catch (SQLException sqlex) {
throw new CoreException(sqlex);
}
++rowCount;
System.err.println("[Read]{" + record + "}");
return record;
}
public void close() throws CoreException {
}
public Schema getSchema() {
return schema;
}
public static void main(String args[]) throws Exception {
//POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(".\\conf\\in.xls"));
//HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
//HSSFSheet sheet = hssfworkbook.getSheetAt(0);
Schema schema = new Schema();
schema.open();
Connection conn = ConnUtils.getConnection(schema.getStoreConfig());
Statement stmt = conn.createStatement();
ResultSet rs = stmt
.executeQuery("select vc_billmonth,vc_clientId,vc_clientName from study");
SQLReader reader = new SQLReader(schema, rs);
while (reader.hasNext()) {
reader.next();
}
conn.close();
}
}
package xls.io;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import xls.core.Cell;
import xls.core.Column;
import xls.core.CoreException;
import xls.core.Record;
import xls.core.Schema;
import xls.core.Type;
import xls.util.IoUtils;
public class XlsReader implements Reader {
private Iterator<?> rowIterator;
private int rowCount;
private Record record;
private Schema schema;
public XlsReader(Schema schema, HSSFSheet sheet) {
this.schema = schema;
this.rowIterator = sheet.rowIterator();
this.rowCount = 0;
}
public boolean hasNext() throws CoreException {
while (rowCount < schema.getStartRow()) {
System.err.println("[Skip Record] row=" + rowCount);
if (!rowIterator.hasNext()) {
return false;
}
rowIterator.next();
++rowCount;
}
if (rowCount > schema.getEndtRow()) {
return false;
}
return rowIterator.hasNext();
}
public Record next() throws CoreException {
int cellCount = 0;
try {
HSSFRow row = (HSSFRow) rowIterator.next();
Iterator<?> cells = row.cellIterator();
HSSFCell cell = null;
if (record == null) {
record = new Record(schema, rowCount);
} else {
record.setRowIndex(rowCount);
record.clearCells();
}
Cell cellObject = null;
Type srcType = null;
Type dstType = null;
Column column = null;
while (cells.hasNext() && (cellCount < schema.getColumnCount())) {
cell = row.getCell((short) cellCount);
cells.next();
if (cell == null) {
column = schema.getColumn(cellCount);
srcType = column.getInType();
dstType = column.getType();
if (srcType == null) {
srcType = dstType;
}
if (column.useDefault()) {
cellObject = new Cell(schema, rowCount, cellCount,
column.getDefaultValue());
} else {
cellObject = new Cell(schema, rowCount, cellCount, null);
}
record.addCell(cellObject);
} else {
column = schema.getColumn(cell.getCellNum());
if (column.isNull()) {
record.addCell(Cell.NULL_CELL);
} else {
srcType = column.getInType();
dstType = column.getType();
if (srcType == null) {
srcType = dstType;
}
if (column.useDefault()) {
cellObject = new Cell(schema, rowCount, cellCount,
column.getDefaultValue());
} else {
cellObject = new Cell(schema, rowCount, cellCount,
IoUtils.readCell(cell, srcType, dstType));
}
record.addCell(cellObject);
}
}
++cellCount;
}
int columnCount = schema.getColumnCount();
if (cellCount < columnCount) {
for (int i = cellCount; i < columnCount; ++i) {
column = schema.getColumn(cellCount);
srcType = column.getInType();
dstType = column.getType();
cellObject = new Cell(schema, rowCount, i, null);
record.addCell(cellObject);
}
}
++rowCount;
} catch (Exception ex) {
System.err.println("[XlsReader read] { row=" + rowCount
+ ",column=" + cellCount + " }");
throw new CoreException(ex);
}
return record;
}
public void close() throws CoreException {
}
public Schema getSchema() {
return (this.schema);
}
public static void main(String args[]) throws Exception {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
"D://project/excelUtil/conf_file/out.xls"));
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
HSSFSheet sheet = hssfworkbook.getSheetAt(0);
Schema schema = new Schema();
schema.open();
XlsReader reader = new XlsReader(schema, sheet);
while (reader.hasNext()) {
System.out.println(reader.next());
}
}
}
//定义一个写的接口
package xls.io;
import xls.core.CoreException;
import xls.core.Record;
import xls.core.Schema;
public interface Writer {
public void write(Record record) throws CoreException;
public void close() throws CoreException;
public Schema getSchema();
}
package xls.io;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import xls.core.Cell;
import xls.core.Column;
import xls.core.CoreException;
import xls.core.Record;
import xls.core.Schema;
import xls.util.IoUtils;
public class SQLWriter implements Writer {
private Schema schema;
private Connection conn;
private PreparedStatement stmt;
public SQLWriter(Schema schema, Connection conn) {
this.schema = schema;
this.conn = conn;
}
public void write(Record record) throws CoreException {
try {
if (stmt == null) {
stmt = conn.prepareStatement(generateSQL(record));
}
PreparedStatement pstmt = (PreparedStatement) stmt;
setParameter(pstmt, record);
pstmt.execute();
} catch (SQLException sqlex) {
System.err.println("[SQLWriter write]{" + record + "}");
throw new CoreException(sqlex);
}
}
public void close() throws CoreException {
try {
if (stmt != null) {
PreparedStatement pstmt = (PreparedStatement) stmt;
pstmt.close();
}
} catch (SQLException sqlex) {
throw new CoreException(sqlex);
}
}
public Schema getSchema() {
return schema;
}
private String generateSQL(Record record) {
int colTotal = schema.getColumnCount();
StringBuffer columns = new StringBuffer();
StringBuffer values = new StringBuffer();
Column column = null;
String colName = null;
for (int i = 0; i < colTotal; ++i) {
column = schema.getColumn(i);
if (!column.isNull()) {
colName = column.getName();
columns.append(colName + ",");
values.append("?,");
}
}
String col = columns.toString();
String val = values.toString();
int chopIndex = col.lastIndexOf(",");
if (chopIndex > 0) {
col = col.substring(0, chopIndex);
}
chopIndex = val.lastIndexOf(",");
if (chopIndex > 0) {
val = val.substring(0, chopIndex);
}
return "insert into " + schema.getTableName() + "(" + col + ") values("
+ val + ")";
}
private void setParameter(PreparedStatement pstmt, Record record)
throws SQLException, CoreException {
int cellSize = record.getCellCount();
int paramIndex = 1;
for (int i = 0; i < cellSize; ++i) {
Cell cell = record.getCell(i);
if (!cell.isNull()) {
IoUtils.writeCell(pstmt, cell, paramIndex);
++paramIndex;
}
}
}
public static void main(String args[]){
}
}
package xls.io;
import java.io.OutputStream;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import xls.core.Cell;
import xls.core.Column;
import xls.core.CoreException;
import xls.core.Record;
import xls.core.Schema;
import xls.util.IoUtils;
public class XlsWriter {
private Schema schema;
private OutputStream fout;
HSSFWorkbook workBook;
HSSFSheet sheet;
int rowCount;
public XlsWriter(Schema schema_, OutputStream fout_) {
this.schema = schema_;
this.fout = fout_;
this.workBook = new HSSFWorkbook();
this.sheet = workBook.createSheet();
this.rowCount = 0;
}
public void write(Record record) throws CoreException {
HSSFRow row = sheet.createRow(rowCount);
int sizeCell = record.getCellCount();
Cell cellObject = null;
int cellIndex = 0;
for (int i = 0; i < sizeCell; ++i) {
cellObject = record.getCell(i);
if (!cellObject.isNull()) {
IoUtils.writeCell(workBook, row, cellIndex, cellObject);
++cellIndex;
}
}
++rowCount;
System.err.println("[Write]{" + record + "}");
}
public void close() throws CoreException {
try {
workBook.write(fout);
} catch (Exception ex) {
throw new CoreException(ex);
}
}
public void writeTitle() throws CoreException {
HSSFRow row = sheet.createRow(rowCount);
int sizeColumn = schema.getColumnCount();
Column column = null;
int titleIndex = 0;
for (int i = 0; i < sizeColumn; ++i) {
column = schema.getColumn(i);
if (!column.isNull()) {
IoUtils.writeTitle(row, titleIndex, column.getName());
++titleIndex;
}
}
++rowCount;
}
public Schema getSchema() {
return schema;
}
}
这个针对文件操作实现读写流