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

最新推荐文章于 2022-08-29 17:13:17 发布

inewzone

最新推荐文章于 2022-08-29 17:13:17 发布

阅读量282

点赞数

分类专栏： j2ee开发文章标签： java excel xml java操作excel j2ee开发excel

j2ee开发专栏收录该内容

3 篇文章 0 订阅

订阅专栏

:lol: j[color=brown][size=medium]ava在操作excel中提供了我常见的api第一个就是POI、JXI。而JXL是由韩国人写出的，功能相对POI没有强大，但它对中文的支持是不错的，它不限制在windows系统下开发，你可以linux下正确处理excel文件，而对与这次我公司正好要用到java操作excel实现从数据库导入导出的功能。所以以下是我对这个网上找的资料进行了总结感觉，代码写的不错，功能相对比较完善基本能实现自己想要的功能！
[/size][/color]
我们简单开始搞起
首页必须先了解这些知识才能进行下一步的操作
HSSF提供给用户使用的对象在org.apache.poi.hssf.usermodel包中,主要部分包括Excell对象，样式和格式，还有辅助操作。有以下几种对象：

HSSFWorkbook excell的文档对象

HSSFSheet excell的表单

HSSFRow excell的行

HSSFCell excell的格子单元

HSSFFont excell字体

HSSFName 名称

HSSFDataFormat 日期格式

在poi1.7中才有以下2项：

HSSFHeader sheet头

HSSFFooter sheet尾

和这个样式

HSSFCellStyle cell样式

辅助操作包括

HSSFDateUtil 日期

HSSFPrintSetup 打印

HSSFErrorConstants 错误信息表

仔细看org.apache.poi.hssf包的结构，不难发现HSSF的内部实现遵循的是MVC模型


HSSFWorkbook即是一个Excell对象。这幅类图体现的是HSSFWorkbook和基本对象的相互关系。可见，许多对象中也建立了Workbook的引用。还需要注意的是在HSSFWorkbook和HSSFSheet中建立了log机制POILogger，而且POILogger也是使用apache Log4J实现的。

先看poi的examples包中提供的最简单的例子，建立一个空xls文件。

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;

import java.io.IOException;

public class NewWorkbook

{

public static void main(String[] args)

throws IOException

{

HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);//把Workbook对象输出到文件workbook.xls中

fileOut.close();

}

}

通过这个例子，我们建立的是一个空白的xls文件（不是空文件）。在此基础上，我们可以进一步看其它的例子。

import org.apache.poi.hssf.usermodel.*;

import java.io.FileOutputStream;

import java.io.IOException;

public class CreateCells

{

public static void main(String[] args)

throws IOException

{

HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象

HSSFSheet sheet = wb.createSheet("new sheet");//建立新的sheet对象

// Create a row and put some cells in it. Rows are 0 based.

HSSFRow row = sheet.createRow((short)0);//建立新行

// Create a cell and put a value in it.

HSSFCell cell = row.createCell((short)0);//建立新cell

cell.setCellValue(1);//设置cell的整数类型的值

// Or do it on one line.

row.createCell((short)1).setCellValue(1.2);//设置cell浮点类型的值

row.createCell((short)2).setCellValue("test");//设置cell字符类型的值

row.createCell((short)3).setCellValue(true);//设置cell布尔类型的值

HSSFCellStyle cellStyle = wb.createCellStyle();//建立新的cell样式

cellStyle.setDataFormat(HSSFDataFormat.getFormat("m/d/yy h:mm"));//设置cell样式为定制的日期格式

HSSFCell dCell =row.createCell((short)4);

dCell.setCellValue(new Date());//设置cell为日期类型的值

dCell.setCellStyle(cellStyle); //设置该cell日期的显示格式

HSSFCell csCell =row.createCell((short)5);

csCell.setEncoding(HSSFCell.ENCODING_UTF_16);//设置cell编码解决中文高位字节截断

csCell.setCellValue("中文测试_Chinese Words Test");//设置中西文结合字符串

row.createCell((short)6).setCellType(HSSFCell.CELL_TYPE_ERROR);//建立错误cell

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

}

}

我稍微修改了原来的examples包中的CreateCells类写了上面的功能测试类。通过这个例子，我们可以清楚的看到xls文件从大到小包括了HSSFWorkbook HSSFSheet HSSFRow HSSFCell这样几个对象。我们可以在cell中设置各种类型的值。尤其要注意的是如果你想正确的显示非欧美的字符时，尤其象中日韩这样的语言，必须设置编码为16位的即是HSSFCell.ENCODING_UTF_16，才能保证字符的高8位不被截断而引起编码失真形成乱码。

其他测试可以通过参考examples包中的测试例子掌握poi的详细用法，包括字体的设置，cell大小和低纹的设置等。需要注意的是POI是一个仍然在完善中的公开代码的项目，所以有些功能正在不断的扩充。如HSSFSheet的getFooter() getHeader()和setFooter(HSSFFooter hsf) setHeader(HSSFHeader hsh)是在POI1.7中才有的，而POI1.5中就没有。运行测试熟悉代码或者使用它做项目时请注意POI的版本。

另外需要注意的是HSSF也有它的对xls基于事件的解析。可以参考例程中的EventExample.java。它通过实现HSSFListener完成从普通流认知Xls中包含的内容，在apache Cocoon中的org.apache.cocoon.serialization.HSSFSerializer中用到了这个解析。因为Cocoon2是基于事件的，所以POI为了提供快速的解析也提供了相应的事件。当然我们自己也可以实现这个事件接口。

因为POI还不是一个足够成熟的项目，所以有必要做进一步的开发和测试。但是它已经为我们用纯java操作ole2对象提供了可能，而且克服了ole对象调用的缺陷，提供了服务器端的Excel解决方案。

[size=large][align=center][color=red]利用Java 创建和读取Excel文档 [/color][/align][/size]

示例1将演示如何利用Jakarta POI API 创建Excel 文档。



示例1程序如下：
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import java.io.FileOutputStream;
public class CreateXL {
　/** Excel 文件要存放的位置，假定在D盘JTest目录下*/
　public static String outputFile="D:/JTest/ gongye.xls";
　public static void main(String argv[])
  {
　try
  {
　　// 创建新的Excel 工作簿
　　HSSFWorkbook workbook = new HSSFWorkbook();
　　// 在Excel工作簿中建一工作表，其名为缺省值
　　// 如要新建一名为"效益指标"的工作表，其语句为：
　　// HSSFSheet sheet = workbook.createSheet("效益指标");
　　HSSFSheet sheet = workbook.createSheet();
　　// 在索引0的位置创建行（最顶端的行）
　　HSSFRow row = sheet.createRow((short)0);
　　//在索引0的位置创建单元格（左上端）
　　HSSFCell cell = row.createCell((short) 0);
　　// 定义单元格为字符串类型
　　cell.setCellType(HSSFCell.CELL_TYPE_STRING);
　　// 在单元格中输入一些内容
　　cell.setCellValue("增加值");
　　// 新建一输出文件流
　　FileOutputStream fOut = new FileOutputStream(outputFile);
　　// 把相应的Excel 工作簿存盘
　　workbook.write(fOut);
　　fOut.flush();
　　// 操作结束，关闭文件
　　fOut.close();
　　System.out.println("文件生成...");



　}catch(Exception e) {
　　System.out.println("已运行 xlCreate() : " + e );
　}
}
}



读取Excel文档中的数据
　　示例2将演示如何读取Excel文档中的数据。假定在D盘JTest目录下有一个文件名为gongye.xls的Excel文件。
　　示例2程序如下：
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import java.io.FileInputStream;
public class ReadXL {
　/** Excel文件的存放位置。注意是正斜线*/
　public static String fileToBeRead="D:/JTest/ gongye.xls";
　public static void main(String argv[]){
　try{
　　// 创建对Excel工作簿文件的引用
　　HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
　　// 创建对工作表的引用。
　　// 本例是按名引用（让我们假定那张表有着缺省名"Sheet1"）
　　HSSFSheet sheet = workbook.getSheet("Sheet1");
　　// 也可用getSheetAt(int index)按索引引用，
　　// 在Excel文档中，第一张工作表的缺省索引是0，
　　// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
　　// 读取左上端单元
　　HSSFRow row = sheet.getRow(0);
　　HSSFCell cell = row.getCell((short)0);
　　// 输出单元内容，cell.getStringCellValue()就是取所在单元的值
　　System.out.println("左上端单元是： " + cell.getStringCellValue());
　}catch(Exception e) {
　　System.out.println("已运行xlRead() : " + e );
　}
}
}


　　设置单元格格式
　　在这里，我们将只介绍一些和格式设置有关的语句，我们假定workbook就是对一个工作簿的引用。在Java

 中，第一步要做的就是创建和设置字体和单元格的格式，然后再应用这些格式： 

　　1、创建字体，设置其为红色、粗体：
HSSFFont font = workbook.createFont();
font.setColor(HSSFFont.COLOR_RED);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
　　2、创建格式
HSSFCellStyle cellStyle= workbook.createCellStyle();
cellStyle.setFont(font);
　　3、应用格式
HSSFCell cell = row.createCell((short) 0);
cell.setCellStyle(cellStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("标题 "); 

　　总之，如本篇文章所演示的一样，Java程序员不必担心Excel工作表中的数据了，利用Jakarta POI API， 

我们就可以轻易的在程序中存取Excel文档。

[align=center][size=large][color=red]

从上面相信对java具体的操作excel有一定的了解现在就直接奔主题[/color][/size][/align]

/**
 *  导入数据类型列值保存类型对应：
 *  (1) VARCHAR,INTEGER,FLOAT      -->java.lang.String
 *  (2) DATE                       -->java.sql.Date
 *  (3) TIME                       -->java.sql.Time
 *  (4) TIMESTAMP                  -->java.sql.Timestamp
 */

package xls.core;

public class Cell {

	public final static Cell NULL_CELL = new Cell(null, -1, -1, null);
	private Object value;
	private Schema schema;
	private int rowNum;
	private int colNum;

	public Cell(Schema schema, int rowNum, int colNum, Object value) {
		this.schema = schema;
		this.value = value;
		this.rowNum = rowNum;
		this.colNum = colNum;
	}

	public boolean validate() {
		Column col = schema.getColumn(colNum);
		Type colType = col.getType();
		if (Type.VARCHAR.equals(colType)) {
			if (value != null && value.toString().length() > col.getLength()) {
				return false;
			}
		}
		return true;
	}

	public Schema getSchema() {
		return schema;
	}

	public int getRowIndex() {
		return rowNum;
	}

	public int getColumnIndex() {
		return colNum;
	}

	public Object getValue() {
		return value;
	}

	public boolean isNull() {
		return schema == null;
	}

	public String toString() {
		if (isNull()) {
			return "[Cell] <null>";
		}
		String string = "[Cell] row=" + rowNum + ",column=" + colNum
				+ ",value=" + value;
		return string;
	}
}

package xls.core;

public class Column {

	public final static Column NULL_COLUMN = new Column(-1, null);
	private int columnIndex;
	private Type type;
	private Type inType;
	private Type outType;

	private String name;

	// 需要时候用于列长度检查
	private int length;

	// 某些列将不从EXCEL/JDBC 中获取的数值
	private Object defaultValue;

	public Column(int index_, String name_) {
		this(index_, null, name_, Integer.MAX_VALUE, null);
	}

	public Column(int index_, Type type_, String name_) {
		this(index_, type_, name_, Integer.MAX_VALUE, null);
	}

	public Column(int index_, Type type_, String name_, Cell defaultValue) {
		this(index_, type_, name_, Integer.MAX_VALUE, defaultValue);
	}

	public Column(int index_, Type type_, String name_, int length_,
			Cell defaultValue) {
		this(index_, type_, null, null, name_, length_, null);
	}

	public Column(int index_, Type type_, Type in_, Type out_, String name_) {
		this(index_, type_, in_, out_, name_, Integer.MAX_VALUE);
	}

	public Column(int index_, Type type_, Type in_, Type out_, String name_,
			int length_) {
		this(index_, type_, in_, out_, name_, length_, null);
	}

	public Column(int index_, Type type_, Type in_, Type out_, String name_,
			int length_, Cell defaultValue) {
		this.columnIndex = index_;
		this.type = type_;
		this.inType = in_;
		this.outType = out_;

		this.name = name_;
		this.length = length_;
		this.defaultValue = defaultValue;
	}

	public String getName() {
		return name;
	}

	public Type getType() {
		return type;
	}

	public boolean isNull() {
		return name == null;
	}

	public void setType(Type colType) {
		type = colType;
	}

	public Type getInType() {
		return inType;
	}

	public void setInType(Type inType_) {
		inType = inType_;
	}

	public Type getOutType() {
		return outType;
	}

	public void setOutType(Type outType_) {
		outType = outType_;
	}

	public int getIndex() {
		return this.columnIndex;
	}

	public void setIndex(int index_) {
		this.columnIndex = index_;
	}

	public int getLength() {
		return length;
	}

	public void setLength(int length_) {
		length = length_;
	}

	public Object getDefaultValue() {
		return defaultValue;
	}

	public void setDefaultValue(Object defValue) {
		this.defaultValue = defValue;
	}

	public boolean useDefault() {
		return defaultValue != null;
	}

	public String toString() {
		if (isNull()) {
			return "[Column] <null>";
		}
		String string = "[Column] index=" + columnIndex + ",name=" + name
				+ ",type={" + type + "}" + ",inType={" + inType + "}"
				+ ",outType={" + outType + "}" + ",length=" + length
				+ ",default=" + defaultValue;
		return string;
	}

}


package xls.core;

public class CoreException extends RuntimeException {

	private static final long serialVersionUID = 1L;

	public CoreException(String message) {
		super(message);
	}

	public CoreException(String message, Throwable cause) {
		super(message);
		this.cause = cause;
	}

	public CoreException(Throwable cause) {
		super(cause.getMessage());
		this.cause = cause;
	}

	protected Throwable cause = null;

	public Throwable getCause() {
		return (this.cause);
	}

}


package xls.core;

import java.util.Vector;

public class Record {

	private Schema schema;
	private int rowNum;
	private Vector<Cell> cells;

	public Record(Schema schema_, int rowNum_) {
		this(schema_, rowNum_, null);
	}

	public Record(Schema schema_, int rowNum_, Vector<Cell> cells_) {
		this.schema = schema_;
		this.rowNum = rowNum_;
		this.cells = cells_;
		if (this.cells == null) {
			cells = new Vector<Cell>();
		}
	}

	public void addCell(Cell cell) {
		cells.addElement(cell);
	}

	public Cell getCell(int index) {
		return (Cell) cells.elementAt(index);
	}

	public int getRowNum() {
		return rowNum;
	}

	public int getCellCount() {
		return cells.size();
	}

	public Schema getSchema() {
		return schema;
	}

	public void setRowIndex(int rowIndex) {
		this.rowNum = rowIndex;
	}

	public int getRowIndex() {
		return this.rowNum;
	}

	public void clearCells() {
		cells.clear();
	}

	public String toString() {
		return "[Record] row=" + rowNum + ",cells=" + cells;
	}
}

以下这个类是对properties文件的操作，自己实现配置好文件，这样程序就能执行自己想要的功能，而对于数据库的描述其实看个人的习惯，你可以采用xml方式去描述，然后使用DOM4J去解析这样也是一个不错的选择。



public class Schema {

	private String tableName;
	private String queryString;
	private Type defaultType;
	private int startRow;
	private int endRow;
	private Vector<Column> columns;
	private StoreConfig storeConfig;
	private boolean validating;
	private String pathname="D://project/excelUtil/conf_file/xls2sql.properties";

	public Schema() {
	}

	public void open() throws Exception {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(configFile());
		props.load(fis);
		fis.close();

		this.tableName = props.getProperty("schema.table.name");
		this.queryString = props.getProperty("schema.table.query");

		String defType = props.getProperty("schema.column.default");
		String colCount = props.getProperty("schema.column.count");
		String check = props.getProperty("schema.column.validating");
		String firstRow = props.getProperty("schema.row.start");
		String endRow = props.getProperty("schema.row.end");

		String connURL = props.getProperty(StoreConfig.STORE_URL);
		String connDriver = props.getProperty(StoreConfig.STORE_DRIVER);
		String connType = props.getProperty(StoreConfig.STORE_TYPE);
		String connUSR = props.getProperty(StoreConfig.STORE_USERNAME);
		String connPWD = props.getProperty(StoreConfig.STORE_PASSWORD);

		this.storeConfig = new StoreConfig(connType, connURL, connUSR, connPWD,
				connDriver);

		this.defaultType = Type.valueOf(defType);

		int columnCount = 0;

		if (colCount != null && colCount.length() > 0) {
			columnCount = Integer.parseInt(colCount);
		}

		if (firstRow != null && firstRow.length() > 0) {
			this.startRow = Integer.parseInt(firstRow);
		} else {
			this.startRow = 0;
		}

		if (endRow != null && endRow.length() > 0) {
			this.endRow = Integer.parseInt(endRow);
		} else {
			this.endRow = Integer.MAX_VALUE;
		}

		this.validating = Boolean.valueOf(check).booleanValue();
		this.columns = new Vector<Column>();

		String colName = null;
		String colType = null;
		String inType = null;
		String outType = null;
		String colDefault = null;
		String colLength = null;

		for (int i = 0; i < columnCount; i++) {
			colName = props.getProperty("schema.column." + i + ".name");
			if (colName == null || colName.length() == 0) {
				System.err.println("[skip column " + i + " ] name is invalid");
				this.columns.addElement(Column.NULL_COLUMN);
			} else {
				colType = props.getProperty("schema.column." + i + ".type");
				inType = props.getProperty("schema.column." + i + ".in");
				outType = props.getProperty("schema.column." + i + ".out");
				colDefault = props.getProperty("schema.column." + i
						+ ".default");
				colLength = props.getProperty("schema.column." + i + ".length");
				Column col = new Column(i, colName);
				this.columns.addElement(col);
				if (colType != null && !"".equals(colType)) {
					col.setType(Type.valueOf(colType).copy());
				}
				if (inType != null && !"".equals(inType)) {
					col.setInType(Type.valueOf(inType).copy());
				}
				if (outType != null && !"".equals(outType)) {
					col.setOutType(Type.valueOf(outType).copy());
				}
				/**
				 * 直接赋值保存的方式不是很合理,鉴于类型转换可能运行期被程序自动修改, 故采用这样的赋值设计
				 * 
				 * @see com.zosatapo.xls.io.XlsReader
				 *      ,com.zosatapo.xls.io.SQLReader
				 */
				if (col.getType() == null) {
					col.setType(this.defaultType.copy());
				}
				if (col.getInType() == null) {
					col.setInType(col.getType().copy());
				}
				if (col.getOutType() == null) {
					col.setOutType(col.getType().copy());
				}
				if (colDefault != null && !"".equals(colDefault)) {
					col.setDefaultValue(IoUtils.formatDefaultValue(colDefault,
							col.getType()));
				}
				if (colLength != null && !"".equals(colLength)) {
					col.setLength(Integer.parseInt(colLength));
				}
			}
		}
	}

	public int getStartRow() {
		return startRow;
	}

	public void setEndRow(int endRow_) {
		this.endRow = endRow_;
	}

	public int getEndtRow() {
		return endRow;
	}

	public void setStartRow(int startRow_) {
		this.startRow = startRow_;
	}

	public Column getColumn(int index) {
		Column column = (Column) columns.elementAt(index);
		if (column.isNull() || column.getIndex() == index) {
			return column;
		}
		int sizeColumn = this.columns.size();
		for (int i = 0; i < sizeColumn; ++i) {
			column = (Column) columns.elementAt(i);
			if (column.getIndex() == index) {
				return column;
			}
		}
		return null;
	}

	public void addColumn(Column column) {
		columns.addElement(column);
	}

	public StoreConfig getStoreConfig() {
		return this.storeConfig;
	}

	public void setStoreConfig(StoreConfig storeConfig_) {
		this.storeConfig = storeConfig_;
	}

	public int getColumnCount() {
		return columns.size();
	}

	public Type getDefaultType() {
		return defaultType;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName_) {
		tableName = tableName_;
	}

	public String getQuery() {
		return queryString;
	}

	public void setQuery(String query_) {
		queryString = query_;
	}

	public boolean isValidating() {
		return validating;
	}

	public void setValidating(boolean validating) {
		this.validating = validating;
	}

	public void setPathname(String pathname) {
		this.pathname = pathname;
	}

	public String getPathname() {
		return pathname;
	}

	private File configFile() {
		return new File(pathname);
	}

	public static void main(String args[]) throws Exception {
		Schema schema = new Schema();
		schema.setPathname("D://project/excelUtil/conf_file/xls2sql.properties");
		schema.open();
		System.err.println(schema.getTableName());
		int colCount = schema.getColumnCount();
		System.err.println(colCount);
		for (int i = 0; i < colCount; ++i) {
			System.err.println(schema.getColumn(i));
		}
	}
}

类型的定义，是很关键的，一般在excel里面是一个字符串，而在数据库里面我们定义的类型是多样式的，所以事先定义很重要，如果我们明确具体的类型我们可以采用一种默认值的方式去定义与描述！


package xls.core;

import java.util.HashMap;
import java.util.Map;

public class Type {

	public final static Type VARCHAR = new Type("VARCHAR");
	public final static Type INTEGER = new Type("INTEGER");
	public final static Type FLOAT = new Type("FLOAT");
	public final static Type DATE = new Type("DATE");
	public final static Type TIME = new Type("TIME");
	public final static Type TIMESTAMP = new Type("TIMESTAMP");
	private static final Map<String, Type> types = new HashMap<String, Type>();

	private static synchronized void populateTypes() {
		types.put("VARCHAR", VARCHAR);
		types.put("INTEGER", INTEGER);
		types.put("FLOAT", FLOAT);
		types.put("DATE", DATE);
		types.put("TIME", TIME);
		types.put("TIMESTAMP", TIMESTAMP);
	}

	static {
		populateTypes();
	}

	private String typeTag;

	private Type(String typeTag_) {
		this.typeTag = typeTag_;
	}

	public static Type valueOf(String typeDefine) {
		return Type.valueOf(typeDefine, false);
	}

	public static Type valueOf(String typeDefine, boolean useDefault) {
		Type type = (Type) types.get(typeDefine);
		if (type == null) {
			if (useDefault) {
				type = VARCHAR;
			}
		}
		return type;
	}

	public void reset(Type dstType) {
		typeTag = dstType.typeTag;
	}

	public Type copy() {
		return new Type(typeTag);
	}

	public String tag() {
		return typeTag;
	}

	public int hasCode() {
		return typeTag.hashCode();
	}

	public boolean equals(Object obj) {
		if (this == obj) {
			return true;
		}
		if (obj instanceof Type) {
			Type anotherColumnType = (Type) obj;
			if (anotherColumnType.typeTag.equals(typeTag)) {
				return true;
			}
		}
		return false;
	}

	public String toString() {
		return "[Type] " + typeTag;
	}
}