java控制excel文件的 写入和读出操作

该程序所需要用到的jar包为commons-lang-2.5.jar和poi-2.5.1.jar 请去apache官网下载

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;//创建sheetHSSFSheet 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,890double 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 blocke.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 blocke.printStackTrace();}HSSFSheet sheet = wb.createSheet("sheet1"); //生成Excel中的sheetHSSFRow 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 blocke1.printStackTrace();}}}