日期:2014-05-16  浏览次数:20414 次

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.cor