1.jxl (支持2003 不支持 2007 貌似最新版支持)
/**
* 规则设置的模板导入
* @param file
* @param exportDefindColum
* @param exportDefindRemark
* @param exportDefineid
* @return
* @throws Exception
*/
public List readSmProfitRuleExcel(File file,String[] defineColums,String[] defineRemarks,String[] defineids) throws Exception{
Workbook book = null;
Cell cell = null;
try {
WorkbookSettings setting = new WorkbookSettings();
java.util.Locale locale = new java.util.Locale("zh","CN");
setting.setLocale(locale);
setting.setEncoding("ISO-8859-1");
book = Workbook.getWorkbook(file, setting);
} catch (Exception e2) {
throw new Exception("Excel文件" + file.getName() + "读取出错。");
}
List list = new ArrayList();
try {
Sheet sheet = book.getSheet(0);
int rows=sheet.getRows();
int columns=sheet.getColumns();
if(rows==0){
throw new Exception("Excel文件" + file.getName() + "无任何内容,禁止导入。");
}
if(columns!=defineColums.length+1){
System.out.println("取出execl"+columns+"行,实际取出"+defineColums+1+"行");
throw new Exception("Excel文件" + file.getName() + "列头与模板列头不符 ,禁止导入 (如修改了规则,需存档重新生成模板再导入)。");
}
for (int i = 0; i < defineids.length; i++) {
String head = defineRemarks[i]+"-"+defineColums[i].substring(defineColums[i].indexOf(":")+1);
if(!sheet.getCell(i,0).getContents().equals(head)){
System.out.println("取出execl列头"+sheet.getCell(i,0).getContents()+",实际取出"+head+"不匹配");
throw new Exception("Excel文件" + file.getName() + "列头与模板列头不符 ,禁止导入 (如修改了规则,需存档重新生成模板再导入)。");
}
}
for (int i = 1; i < rows; i++) {
for (int j = 0; j < columns; j++) {
String content=sheet.getCell(j,i).getContents().trim();
if(content.equals("")){
throw new Exception("Excel文件" + file.getName() + "第"+(i+1)+"行,第"+(j+1)+"列存在空数据,禁止导入");
}
Map<String, String> map = new HashMap<String, String>();
if(j>defineids.length-1){
map.put("defineid", "-1");
}else{
map.put("defineid", defineids[j]);
}
map.put("linenum",(i+1)+"");
map.put("value", content);
list.add(map);
}
}
book.close();
} catch (Exception e) {
book.close();
throw new Exception(e.getMessage());
}
return list;
}
附:
![jxl.jar](https://i-blog.csdnimg.cn/blog_migrate/f9d1804d6f808c428ce72d481610ca84.png)
2.poi (apache 下的开源项目 03,07都支持)
package com.dadi.oa.util.poi;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.dadi.oa.util.StringUtil;
/**
* poi 读取 </br>
* 1.execl兼容 03,07 <br/>
* 2.构造方法需传入标准 <br/>
* 3.对应的标准可以在checkReadStandard()扩展 <br/>
*
* @author ao.ouyang
*/
public class ExeclReader {
private final Log logger = LogFactory.getLog(this.getClass());
/**
* 数据Map集合 dataMap ,默认标准Map keys :<br/>
* rowNum : 行号 <br/>
* ColumnNum : 列号 <br/>
* val : String类型值
*/
private List dataMap = new ArrayList();
//读取标准,定义新的标准可以在检查标准方法中拓展
private String readStandard;
private File readFile;
private Workbook wb;
private Sheet st;
private Row row;
//抽取文本工具
private ExeclExtractor extractor;
/**
* ExeclReader
* @param readItem 读取的execl
* @param readStandards 无标准 Null
*/
public ExeclReader(File readFile,String readStandards) throws Exception{
this.readFile = readFile;
this.readStandard = readStandard;
readExecl();
}
private void readExecl() throws Exception{
InputStream inputStream = new FileInputStream(readFile);
try {
if(readFile.getAbsolutePath().endsWith("xlsx")){
wb=new XSSFWorkbook(inputStream);
//实例化抽取工具
extractor = new XSSFExeclExtractor((XSSFWorkbook)wb);
}else{
wb=new HSSFWorkbook(inputStream);
extractor = new HSSFExeclExtractor((HSSFWorkbook)wb);
}
extractor.setFormulasNotResults(true);
extractor.setIncludeCellComments(true);
} catch (Exception e) {
e.printStackTrace();
throw new Exception("EXECL文件读取失败,请检查文件!");
}
st=wb.getSheetAt(0);
//检查标准
checkReadStandard();
}
/**
* 检查标准
* @throws Exception
*/
private void checkReadStandard() throws Exception{
if(StringUtil.isEmptyString(readStandard)){ //默认标准,无操作,读取execl数据
readExeclToMapList();
}
}
/**
* 读取execl数据 封装数据
* @throws Exception
*/
private void readExeclToMapList() throws Exception{
logger.info("============ExeclReader.readExeclToMapList() begin===========");
try {
for (int i = st.getFirstRowNum(); i <= st.getLastRowNum(); i++) {
row=st.getRow(i);
List<HashMap<String, String>> rowMap = new ArrayList<HashMap<String,String>>();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
HashMap<String,String> cellMap = new HashMap<String, String>();
cellMap.put("rowNum", String.valueOf(i+1));
cellMap.put("ColumnNum", String.valueOf(j+1));
Cell cell = row.getCell(j) ;
cellMap.put("val",extractor.getText(cell));
rowMap.add(cellMap);
}
dataMap.add(rowMap);
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception("读取execl 数据异常,请检查!!!");
}
logger.info("============ExeclReader.readExeclToMapList() end===========");
}
/**
* 返回头数据
* @return
*/
public List getHeaderData(){
return (List) dataMap.get(0);
}
/**
* 返回底部数据
* @return
*/
public List getBottomData(){
return (List) dataMap.get(dataMap.size()-1);
}
/**
* 返回指定行的数据
* @param columnIndex
* @return
*/
public List getRowData(int rowIndex){
if(rowIndex<0){
return getHeaderData();
}else if(rowIndex > dataMap.size()){
return getBottomData();
}else{
return (List) dataMap.get(rowIndex-1);
}
}
/**
* 获取指定列数据
* @return
*/
public List getcolumnData(int coloumIndex){
if(coloumIndex > getColumnNum()){
coloumIndex = getColumnNum();
}else if(coloumIndex < 1){
coloumIndex = 1;
}else{
coloumIndex = coloumIndex-1;
}
List columnData = new ArrayList();
for (int i = 0; i < dataMap.size(); i++) {
List rowData = (List) dataMap.get(i);
for (int j = 0; j < rowData.size(); j++) {
if(i==coloumIndex){
columnData.add(rowData.get(j));
}
}
}
return columnData;
}
/**
* 返回行总数
* @return
*/
public int getRowCount(){
return st.getLastRowNum();
//return st.getPhysicalNumberOfRows();
}
/**
* 返回列总数
* @return
*/
public int getColumnNum(){
return st.getRow(st.getFirstRowNum()).getLastCellNum();
/* row= st.getRow(0);
return row.getPhysicalNumberOfCells();*/
}
/**
* 返回数据总数
* @return
*/
public int getDataCount(){
return getRowCount() * getColumnNum();
}
/**
* 返回数据总数 不含头
* @return
*/
public int getNotHeaderDataCount(){
return getRowCount()-1 * getColumnNum();
}
public List getDataMap() {
return dataMap;
}
public void setDataMap(List dataMap) {
this.dataMap = dataMap;
}
}
附:
![poi-bin-3.10.1-20140818.zip](https://i-blog.csdnimg.cn/blog_migrate/cacb262a4a3c8e21c8913605b40bf7b9.png)
3.可使用 浏览器下的 ACTIVEX 空间读取 execl (ie浏览器)
附:
![exceltest.jsp](https://i-blog.csdnimg.cn/blog_migrate/629f3db54e0d79a9c737549bd2a95a98.png)
附件列表