java操作excel实现从数据库导入导出（二）
上一篇文章主要针对的是java操作excel的描述，但是具体很没有真正的完善，仍然需要与数据库打交道。先简单介绍这个功能的具体实现吧，主要先从properties读取你实现配置好的数据表，根据你自己定义的规范在数据库里面建模，然后下一步从excel里面读取具体的内容存入到自己的一个容器里面，最后描述生成多个insert语句插入到数据库里面去
//定义读文件接口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;}}