首先要有一个工具类
/**
* Excel数据导入数据库
*/
package xx.xx.xx.utills;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
public class GenerateSqlFromExcel {
/**
* 导入报表Excel数据,生成用户表的数据库导入语句
* @param formFile
* @return list ArrayList
* @throws Exception
*/
public static ArrayList<String[]> generateUserSql(File formFile)
throws Exception {
InputStream in = null;
Workbook wb = null;
ArrayList<String[]> list = new ArrayList<String[]>();
try {
if (formFile == null) {
throw new Exception("文件为空!");
}
in = new FileInputStream(formFile);
wb = Workbook.getWorkbook(in);
Sheet sheet[] = wb.getSheets();
if (sheet != null) {
for (int i = 0; i < sheet.length; i++) {
int count = i+1;
if (!sheet[i].getName().equalsIgnoreCase("User"+count)) {
throw new Exception("指定文件中不包含名称为User的sheet,请重新指定!");
}
for (int j = 1; j < sheet[i].getRows(); j++) {
String[] valStr = new String[11];
for (int k = 0; k < sheet[i].getColumns(); k++) {
Cell cell = sheet[i].getCell(k, j);
String content = "";
if (cell.getType() == CellType.DATE) {
DateCell dateCell = (DateCell) cell;
java.util.Date importdate = dateCell.getDate();/**如果excel是日期格式的话需要减去8小时*/
long eighthour = 8*60*60*1000;
SimpleDateFormat simpledate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**在当前日期上减8小时*/
long time = importdate.getTime()-eighthour;
java.util.Date date = new java.util.Date();
date.setTime(time);
content = simpledate.format(date);
} else {
String tempcontent = (cell.getContents() == null ? ""
: cell.getContents());
content = tempcontent.trim();
}
valStr[k] = content;
}
list.add(j-1,valStr);
}
}
}
return list;
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
if (wb != null) {
wb.close();
}
if (in != null) {
try {
in.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
xxxxxAction.java
/**
* Excel表数据导入数据库
* @return
* @throws Exception
*/
public String insert() throws Exception{
File file = model.getFile();
//GenerateSqlFromExcel fromExcel = new GenerateSqlFromExcel();
try
{
/**
* ArrayList<String[]> arrayList:集合就是從Excel中读取的数据集合
* 针对本例
*/
ArrayList<String[]> arrayList = GenerateSqlFromExcel.generateUserSql(file);
//将arrayList的数据导入到数据库中
excelImportData(arrayList);
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
return "toList";
}
public void excelImportData(ArrayList<String[]> arrayList)
{
/**
* 从Excel读取的数据导入到数据库中
*/
for(int i=0;arrayList!=null&&i<arrayList.size();i++)
{
String[] str = arrayList.get(i);
//实例化Account,进行保存
/**
* 数组中存放的顺序:
*/
Account account = new Account();
account.setLoginName(str[0]);
account.setPassword(DigestUtils.md5Hex(str[1]));
account.setStatus(true);
account.setLoginIp(null);
account.setName(str[4]);
account.setNumber(str[5]);
account.setSex(str[6]);
/*//字符串转化为日期格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
Date date = null;
try {
date = sdf.parse(str[7]);
} catch (ParseException e) {
e.printStackTrace();
}
account.setBirthday(date);
account.setTel(str[8]);
account.setEmail(str[9]);
account.setQq(str[10]);
account.setFax(str[11]);
account.setMobile(str[12]);
account.setOnline(null);
//Department department = departmentService.getByName(str[14]);
//account.setDepartment(department);*/
accountService.save(account);
}
}
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
使用 java 代码读取 excel 文件代码时报错如下:
jxl.read.biff.BiffException: Unable to recognize OLE streamat jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
at jxl.read.biff.File.<init>(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:268)
at jxl.Workbook.getWorkbook(Workbook.java:253)
原因:不支出读取 excel 2007 文件(*.xlsx)。只支持 excel 2003 (*.xls),
但我的文件时(*.xls)格式,却依然无法成功,我就另存为excel 2003 (*.xls),就成功了。
》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》》