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