import java.text.SimpleDateFormat
import java.util.*
import java.sql.*
import java.util.Date
import java.util.HashMap
import java.util.Map
import jxl.*
public class SimUpdate {
private String fileName
public ZfzSimUpdate(String fileName){
this.fileName = fileName
}
static Map tNames
static{
tNames = new HashMap()
}
/**
* 用于产生 数据库的 ID 值，组成 [年月日时分秒(100-999)] 总共 17 位数.
* 根据不同的表名，可保证同一秒内产生的 ID 号不重复
*/
private static String getDtime() {
String rid
Date nd = new Date()
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss")
rid = sdf.format(nd)
return rid
}
public String getSeqNumber(String tableName) {
if(tableName == null || "".equals(tableName))
tableName = "GENERY"
Integer it
// noinspection SynchronizeOnNonFinalField
synchronized(tNames){
it = (Integer)tNames.get(tableName)
if(it == null){
it = new Integer(100)
tNames.put(tableName, it)
}else{
if(it.intValue() >998)
it = new Integer(100)
else
it = new Integer(1 + it.intValue())
tNames.put(tableName, it)
}
}
return getDtime() + String.valueOf(it)
}
private void updateDb(){
try{
Connection conn = DbPool.connectDB()
if(conn != null){
Statement stmt = conn.createStatement()
/**********************************************/
jxl.Workbook rwb = null
try{
//构建Workbook对象 只读Workbook对象
//直接从本地文件创建Workbook
//从输入流创建Workbook
InputStream is = new FileInputStream(fileName)
rwb = Workbook.getWorkbook(is)
//Sheet(术语：工作表)就是Excel表格左下角的Sheet1,Sheet2,Sheet3但在程序中
//Sheet的下标是从0开始的
//获取第一张Sheet表
Sheet rs = rwb.getSheet(0)
//获取Sheet表中所包含的总列数
int rsColumns = rs.getColumns()
//获取Sheet表中所包含的总行数
int rsRows = rs.getRows()
//获取指这下单元格的对象引用
String simNumber = "",termSeqId = ""
//指定SIM卡号及序列号
for(int i=0i<rsRowsi++){
for(int j=0j<rsColumnsj++){
Cell cell = rs.getCell(j,i)
if(j==0){
simNumber = cell.getContents()
}
termSeqId = "633"+simNumber
}
String sql = "查询SQL"
int isOk = stmt.executeUpdate(sql)
if(isOk == 0 &&!simNumber.equals("")){
String termId = getSeqNumber("termInf")
String insertSql = "自定义INSERT"
int isAdd = stmt.executeUpdate(insertSql)
if(isAdd >0){
System.out.println("成功插入第"+i+"条数据")
}
}
//System.out.println("SIM卡号:"+simNumber+",序列号:"+termSeqId)
}
//以下代码为写入新的EXCEL,这里不使用,所以注释
/*
//利用已经创建的Excel工作薄创建新的可写入的Excel工作薄
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File("D://Book2.xls"),rwb)
//读取第一张工作表
jxl.write.WritableSheet ws = wwb.getSheet(0)
//获取第一个单元格对象
jxl.write.WritableCell wc = ws.getWritableCell(0, 0)
//决断单元格的类型，做出相应的转化
if (wc.getType() == CellType.LABEL) {
Label l = (Label) wc
l.setString("The value has been modified.")
}
//写入Excel对象
wwb.write()
wwb.close()
*/
}catch(Exception e){
e.printStackTrace()
}
finally{
//操作完成时，关闭对象，翻译占用的内存空间
rwb.close()
}
/*********************************************/
}
}catch(Exception e){
e.printStackTrace()
}
}
public static void main(String args[]){
DbPool dbPool = new DbPool("dbConn.cfg")//连接数据库
SimUpdate simUpdate = new SimUpdate("zfz_sim.xls")
simUpdate.updateDb()
}
}
我只用了读取XLS,写入没试,应该没问题吧,你把注释了的拿 来试一下吧
快速导入也是需要java的poi的，可以参照如下代码：public List<ScoreInfo>loadScoreInfo(String xlsPath) throws IOException{
List temp = new ArrayList()
FileInputStream fileIn = new FileInputStream(xlsPath)
//根据指定的文件输入流导入Excel从而产生Workbook对象
Workbook wb0 = new HSSFWorkbook(fileIn)
//获取Excel文档中的第一个表单
Sheet sht0 = wb0.getSheetAt(0)
//对Sheet中的每一行进行迭代
for (Row r : sht0) {
//如果当前行的行号（从0开始）未达到2（第三行）则从新循环
If(r.getRowNum()<1){
continue
}
//创建实体类
ScoreInfo info=new ScoreInfo()
//取出当前行第1个单元格数据，并封装在info实体stuName属性上
info.setStuName(r.getCell(0).getStringCellValue())
info.setClassName(r.getCell(1).getStringCellValue())
info.setRscore(r.getCell(2).getNumericCellValue())
info.setLscore(r.getCell(3).getNumericCellValue())
temp.add(info)
}
fileIn.close()
return temp
}
import java.io.BufferedInputStreamimport java.io.File
import java.io.FileInputStream
import java.io.FileNotFoundException
import java.io.IOException
import java.text.DecimalFormat
import java.text.SimpleDateFormat
import java.util.ArrayList
import java.util.Arrays
import java.util.Date
import java.util.List
import org.apache.poi.hssf.usermodel.HSSFCell
import org.apache.poi.hssf.usermodel.HSSFDateUtil
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
public class ReadExcel2 {
/**
* @param args
* @throws IOException
* @throws FileNotFoundException
*/
public static void main(String[] args) throws FileNotFoundException, IOException {
File file = new File("src/test.xls")
String[][] data = getData(file, 0)
printStringArray(data)
}
public static void printStringArray(String[][] data){
for(int i =0i<data.lengthi++){
for(int j=0j<data[i].lengthj++){
System.out.print(data[i][j]+"\t")
}
System.out.print("\n")
}
}
/**
*
* 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
* @param file 读取数据的源Excel
* @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
* @return 读出的Excel中数据的内容
* @throws FileNotFoundException
* @throws IOException
*/
public static String[][] getData(File file, int ignoreRows)
throws FileNotFoundException, IOException {
List<String[]>result = new ArrayList<String[]>()
int rowSize = 0
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
file))
// 打开HSSFWorkbook
POIFSFileSystem fs = new POIFSFileSystem(in)
HSSFWorkbook wb = new HSSFWorkbook(fs)
HSSFCell cell = null
for (int sheetIndex = 0sheetIndex <wb.getNumberOfSheets()sheetIndex++) {
HSSFSheet st = wb.getSheetAt(sheetIndex)
// 第一行为标题，不取
for (int rowIndex = ignoreRowsrowIndex <= st.getLastRowNum()rowIndex++) {
HSSFRow row = st.getRow(rowIndex)
if (row == null) {
continue
}
int tempRowSize = row.getLastCellNum() + 1
if (tempRowSize >rowSize) {
rowSize = tempRowSize
}
String[] values = new String[rowSize]
Arrays.fill(values, "")
boolean hasValue = false
for (short columnIndex = 0columnIndex <= row.getLastCellNum()columnIndex++) {
String value = ""
cell = row.getCell(columnIndex)
if (cell != null) {
// 注意：一定要设成这个，否则可能会出现乱码
cell.setEncoding(HSSFCell.ENCODING_UTF_16)
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue()
break
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue()
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd")
.format(date)
} else {
value = ""
}
} else {
value = new DecimalFormat("0").format(cell
.getNumericCellValue())
}
break
case HSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue()
} else {
value = cell.getNumericCellValue() + ""
}
break
case HSSFCell.CELL_TYPE_BLANK:
break
case HSSFCell.CELL_TYPE_ERROR:
value = ""
break
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N")
break
default:
value = ""
}
}
if (columnIndex == 0 &&value.trim().equals("")) {
break
}
values[columnIndex] = rightTrim(value)
hasValue = true
}
if (hasValue) {
result.add(values)
}
}
}
in.close()
String[][] returnArray = new String[result.size()][rowSize]
for (int i = 0i <returnArray.lengthi++) {
returnArray[i] = (String[]) result.get(i)
}
return returnArray
}
/**
*
* 去掉字符串右边的空格
* @param str 要处理的字符串
* @return 处理后的字符串
*/
public static String rightTrim(String str) {
if (str == null) {
return ""
}
int length = str.length()
for (int i = length - 1i >= 0i--) {
if (str.charAt(i) != 0x20) {
break
}
length--
}
return str.substring(0, length)
}
}
