引用:
*mysql的jar文件
*Spring_HOME/lib/poi/*.jar
package com.zzg.db
import java.sql.Connection
import java.sql.DriverManager
public class DbUtils {
private static Connection conn
static {
try {
Class.forName("com.mysql.jdbc.Driver")
conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","123456")
} catch (Exception e) {
e.printStackTrace()
}
}
public static Connection getConn() {
return conn
}
public static void setConn(Connection conn) {
DbUtils.conn = conn
}
}
3,编写数据库 *** 作类
package com.zzg.db
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.SQLException
public class ExcuteData {
private PreparedStatement pstmt
public boolean ExcuData(String sql) {
Connection conn = DbUtils.getConn()
boolean flag=false
try {
pstmt = conn.prepareStatement(sql)
flag=pstmt.execute()
} catch (SQLException e) {
e.printStackTrace()
}
return flag
}
}
4,编写的Excel表格实体类
package com.zzg.model
public class TableCell {
private String _name
private String _value
public String get_name() {
return _name
}
public void set_name(String _name) {
this._name = _name
}
public String get_value() {
return _value
}
public void set_value(String _value) {
this._value = _value
}
}
5,编写主键生成方法
package com.zzg.util
import java.text.SimpleDateFormat
import java.util.Date
import java.util.Random
public class GenericUtil {
public static String getPrimaryKey()
{
String primaryKey
primaryKey = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())
Random r = new Random()
primaryKey +=r.nextInt(100000)+100000
return primaryKey
}
}
6,编写的Excel *** 作类
package com.zzg.deployData
import java.io.File
import java.io.FileInputStream
import java.io.FileNotFoundException
import java.io.IOException
import java.io.Serializable
import java.util.ArrayList
import java.util.List
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 com.zzg.db.ExcuteData
import com.zzg.model.TableCell
import com.zzg.util.GenericUtil
public class OperExcel<T extends Serializable>{
private HSSFWorkbook workbook
private String tableName
private Class<T>type
private String sheetName
public OperExcel(File excelFile, String tableName, Class<T>type,
String sheetName) throws FileNotFoundException,
IOException {
workbook = new HSSFWorkbook(new FileInputStream(excelFile))
this.tableName = tableName
this.type = type
this.sheetName = sheetName
InsertData()
}
// 向表中写入数据
public void InsertData() {
System.out.println("yyy")
ExcuteData excuteData = new ExcuteData()
List<List>datas = getDatasInSheet(this.sheetName)
// 向表中添加数据之前先删除表中数据
String strSql = "delete from " + this.tableName
excuteData.ExcuData(strSql)
// 拼接sql语句
for (int i = 1i <datas.size()i++) {
strSql = "insert into " + this.tableName + "("
List row = datas.get(i)
for (short n = 0n <row.size()n++) {
TableCell excel = (TableCell) row.get(n)
if (n != row.size() - 1)
strSql += excel.get_name() + ","
else
strSql += excel.get_name() + ")"
}
strSql += " values ("
for (short n = 0n <row.size()n++) {
TableCell excel = (TableCell) row.get(n)
try {
if (n != row.size() - 1) {
strSql += getTypeChangeValue(excel) + ","
} else
strSql += getTypeChangeValue(excel) + ")"
} catch (RuntimeException e) {
e.printStackTrace()
} catch (Exception e) {
e.printStackTrace()
}
}
//执行sql
excuteData.ExcuData(strSql)
}
}
/**
* 获得表中的数据
* @param sheetName 表格索引(EXCEL 是多表文档,所以需要输入表索引号)
* @return 由LIST构成的行和表
*/
public List<List>getDatasInSheet(String sheetName) {
List<List>result = new ArrayList<List>()
// 获得指定的表
HSSFSheet sheet = workbook.getSheet(sheetName)
// 获得数据总行数
int rowCount = sheet.getLastRowNum()
if (rowCount <1) {
return result
}
// 逐行读取数据
for (int rowIndex = 0rowIndex <rowCountrowIndex++) {
// 获得行对象
HSSFRow row = sheet.getRow(rowIndex)
if (row != null) {
List<TableCell>rowData = new ArrayList<TableCell>()
// 获得本行中单元格的个数
int columnCount = sheet.getRow(0).getLastCellNum()
// 获得本行中各单元格中的数据
for (short columnIndex = 0columnIndex <columnCountcolumnIndex++) {
HSSFCell cell = row.getCell(columnIndex)
// 获得指定单元格中数据
Object cellStr = this.getCellString(cell)
TableCell TableCell = new TableCell()
TableCell.set_name(getCellString(
sheet.getRow(0).getCell(columnIndex)).toString())
TableCell.set_value(cellStr == null ? "" : cellStr
.toString())
rowData.add(TableCell)
}
result.add(rowData)
}
}
return result
}
/**
* 获得单元格中的内容
* @param cell
* @return result
*/
protected Object getCellString(HSSFCell cell) {
Object result = null
if (cell != null) {
int cellType = cell.getCellType()
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue()
break
case HSSFCell.CELL_TYPE_NUMERIC:
result = cell.getNumericCellValue()
break
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getNumericCellValue()
break
case HSSFCell.CELL_TYPE_ERROR:
result = null
break
case HSSFCell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue()
break
case HSSFCell.CELL_TYPE_BLANK:
result = null
break
}
}
return result
}
// 根据类型返回相应的值
@SuppressWarnings("unchecked")
public String getTypeChangeValue(TableCell excelElement)
throws RuntimeException, Exception {
String colName = excelElement.get_name()
String colValue = excelElement.get_value()
String retValue = ""
if (colName.equals("id")) {
retValue = "'" + GenericUtil.getPrimaryKey() + "'"
return retValue
}
if (colName == null) {
retValue = null
}
if (colName.equals("class_createuser")) {
retValue = "yaa101"
return "'" + retValue + "'"
}
retValue = "'" + colValue + "'"
return retValue
}
}
7,编写调用 *** 作的Excel类的方法
package com.zzg.deployData
import java.io.File
import java.io.FileNotFoundException
import java.io.IOException
public class DeployData {
private File fileOut
public void excute(String filepath) {
fileOut = new File(filepath)
this.deployUserInfoData()
}
public void deployUserInfoData() {
try {
new OperExcel(fileOut, "test", Object.class, "Sheet1")
} catch (FileNotFoundException e) {
e.printStackTrace()
} catch (IOException e) {
e.printStackTrace()
}
}
}
8,编写客户端
package com.zzg.client
import com.zzg.deployData.DeployData
public class DeployClient {
public static void main(String[] args) {
DeployData deployData = new DeployData()
deployData.excute("D://test.xls")
}
}
HSSFWorkbook wb=new HSSFWorkbook()//创建一个ExcelHSSFSheet sheet=wb.createSheet("sheet1")//创建一个Sheet
HSSFRow row=sheet.createRow(0)//创建一个行
HSSFCell cell=row.createCell((short)0) //创建一个单元格
cell.setEncoding(HSSFCell.ENCODING_UTF_16)
cell.setCellValue("序号")//设置值
cell=row.createCell((short)1)
cell.setEncoding(HSSFCell.ENCODING_UTF_16)
cell.setCellValue("姓")
这有两个方法,一个是POI,一个是JXL,通过楼上几位大侠的答案,我想你也了解了。在这就不说这一块了。我想给你说说这两者的比较和应用领域,楼主根据自己的需求确定使用某种技术。当数据量较大时(超过1万行),无论是时间消耗还是内存消耗,JXL均优于POI。根据最终的测试结果,估计得出来不同的结论可能是采用的版本不同所致(还没研究JXL及POI的源码)。
具体情况请参见此文及相关下方我测试的Java源码及测试数据:
http://blog.csdn.net/haotangyu/archive/2009/12/03/4931670.aspx
我的测试环境:
jxl-2.4.2.jar
poi-3.1.jar
MyEclipse8.0 Run JVM设置:-Xms256m -Xmx512m
机器:4G、P7
分别准备了1千、1万、5万行的Excel,各为58列。(此测试代码稍加整理即可做为Excel导入工具类)
加载时间(ms)
总耗时(ms)
总消耗内存量(M)
1千条:746K
POI
572
609
8
JXL
296
422
12
JXL-Close
172
329
-2
1万条:12.8M
POI
4847
5066
91
JXL
634
2236
135
1万条:7.3M
POI
1266
2019
73
JXL
488
1159
70
JXL-Close
343
1107
2
5万条:36.8M
POI
7556
8633
208
JXL
2319
5581
96
JXL-Close
2543
6210
-201
可以看出
1.加载速度及总耗时,POI3.2始终劣于JXL2.4
2.后续解析速度上(没列,是总耗时减去加载时间),POI3.2总体上优于JXL2.4
3.1万条数据以上时,消耗内存量方面,POI3.2劣于JXL2.4
两点说明:
1.这里面有个有趣的细节:那个1万条12.8M的文件是我根据那个5万条的Excel选中后4万行按Del生成的,后来发现那4万行并没有真正删除,还有,是空行而已。后选中后右键删除(试了几次后才成功),总算真正清除了,体积也缩小了很多,从实验看来,空白格不但对Excel体积有影响,对程序读取影响也很大。程序中POI方式读取中设计了对空白行的判断,但加载还是多花了时间;
2.标识为JXL的数据,为代码中未加入WorkBook.close()方法时试验(注意代码中注释掉的wb.close()一行);而JXL-Close则添加了close方法调用,可以看出时间消耗未明显增加,但内存消耗却大大降低(负的)。刚看了反编代码,果然,close方法中调用了System.gc()。
根据 haotangyu的代码进行的测试结果,还是上面那些文件,同样可以得出以上第1、2条结论:
加载时间(ms)
总耗时(ms)
1千条:746K
POI
499
515
JXL
203
359
1万条:7.3M
POI
1716
1731
JXL
390
1108
5万条:36.8M
POI
7878
7925
JXL
2309
5711
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/amosryan/archive/2010/03/24/5412340.aspx
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)