Java操作Excel

该程序所需要用到的jar包为commons-lang-2.6.jar和poi-3.8.jar

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();
		}
	}
}

导入excel例子

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;
	}
}