java操作excel实现从数据库导入导出（二）

[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;
	}
}

这个针对文件操作实现读写流