该程序所需要用到的jar包为commons-lang-2.6.jar和poi-3.8.jar
[size=large][b]导入excel例子[/b][/size]
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Iterator;
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;
public class Excel {
//读取excel表格中的信息
public List loadExcel(String filePath) {
List list = new ArrayList();
//创建一个excel表对象
HSSFWorkbook wb = null;
//用来读取"本地"的excel文件
InputStream is = null;
try {
is = new FileInputStream(filePath);
//将输入流传给 excel对象
wb = new HSSFWorkbook(is);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
String str = null;
//创建sheet
HSSFSheet sheet = wb.getSheet("Sheet1");
//获得sheet1中的所有HSSFRow对象
Iterator it = sheet.rowIterator();
while (it.hasNext()) {
//如需要便利行中的列可调用
//temp.getFirstCellNum();
//temp.getLastCellNum();当做条件进行循环
HSSFRow temp = (HSSFRow) it.next();
try {
switch(temp.getCell((short) 0).getCellType()){
case HSSFCell.CELL_TYPE_STRING://字符串类型
str = temp.getCell((short)0).getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC://数值类型
NumberFormat nf = NumberFormat.getInstance();
nf.setGroupingUsed(false);//true时的格式:1,234,567,890
double acno=temp.getCell((short)0).getNumericCellValue();//将科学技术法的值转换为计算之前的值
str = nf.format(acno);
break;
case HSSFCell.CELL_TYPE_FORMULA://公式
str = String.valueOf(temp.getCell((short)0).getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
str = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
break;
default :
break;
}
} catch (Exception e) {
continue;
}
list.add(str);
}
try {
//关闭输入流
is.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
public void write() {
HSSFWorkbook wb = new HSSFWorkbook(); //相当于Excel整个文件
FileOutputStream fos = null;
try {
//创建一个文件
fos = new FileOutputStream("D:/data.xls");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
HSSFSheet sheet = wb.createSheet("sheet1"); //生成Excel中的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("姓名");
cell = row.createCell((short) 2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("年龄");
/**至此为止,表头部分就定义好了**/
for (int i = 0; i < 50; ++i) {
row = sheet.createRow(i + 1); //创建行
cell = row.createCell((short) 0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(i + 1); //设定序号
cell = row.createCell((short) 1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("a");
cell = row.createCell((short) 2);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("b");
}
try {
//将这个文件交给HSSFWorkbook类 由它负责写入
wb.write(fos);
//关闭输出流
fos.close();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
[size=large][b]导入excel例子[/b][/size]
public class ExcelUtils {
@SuppressWarnings("all")
public static List<MonitorObjectVO> loadExcel(File file) {
List<MonitorObjectVO> excelGroups = null;
String errorMsg = "";
if (file.exists()) {
try {
Workbook workBook = null;
InputStream is = new FileInputStream(file);
try {
if (".xls".equals(".xls")) { // 97-03
workBook = new HSSFWorkbook(is);
} else if (".xlsx".equals(".xls")) { // 2007
workBook = new XSSFWorkbook(is);
} else {
System.out.println("不支持的文件类型!");
return null;
}
} catch (Exception e) {
System.out.println("解析xls文件出错!");
e.printStackTrace();
} finally {
try {
is.close();
} catch (Exception e2) {
}
}
int sheets = null != workBook ? workBook.getNumberOfSheets()
: 0;
excelGroups = new ArrayList<MonitorObjectVO>();
Sheet sheet = workBook.getSheetAt(0); // 读取第一个sheet
int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
if (rows > 1) { // 第一行默认为标题
// sheet.getMargin(HSSFSheet.TopMargin);
for (int j = 1; j < rows; j++) {
Row row = sheet.getRow(j);
MonitorObjectVO groupObj = new MonitorObjectVO();
int cells = row.getLastCellNum();// 获得列数
if (cells > 0) {
for (int k = 0; k < cells; k++) {
Cell cell = row.getCell(k);
// 全部置成String类型的单元格
cell.setCellType(Cell.CELL_TYPE_STRING);
if (k <= 50) {
groupObj.setGroup_name(cell
.getStringCellValue());
System.out.println(cell
.getStringCellValue());
} else {
break;
}
}
} else {
errorMsg = "EXCEL没有数据,请确定。";
}
excelGroups.add(groupObj);
}
} else {
errorMsg = "EXCEL没有数据,请确定。";
}
} catch (Exception ex) {
ex.printStackTrace();
}
} else {
errorMsg = "文件不存在。";
}
if (errorMsg.length() > 0) {
System.out.println("错误消息:" + errorMsg);
}
return excelGroups;
}
}