Java读写Excel文件

方法1：

使用JDBC-ODBC桥驱动，在使用之前还需进行ODBC的相关配置。

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

 

public class ExcelJDBC {
 public static void main(String[] args){
  try{
   String strurl = "jdbc:odbc:testExcel";
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection conn = DriverManager.getConnection(strurl);
   
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery("select * from books");
   
   if(rs.next()){
    System.out.println(rs.getString("name"));
   }
   conn.close();
  }catch(Exception e){
   e.printStackTrace();
  }
 }
}

 

 

方法2：

使用开源的第三方包，例如apache的POI

下载地址：http://jakarta.apache.org/poi/index.html

导入包的情况如下：

 

 

package Test;

 

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

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;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

 

public class ExcelBasic {
 private POIFSFileSystem fs;
 private HSSFWorkbook wb;
 private HSSFSheet sheet;
 private HSSFRow row;

//读取Excel表格表头的内容
 public String[] readExcelTitle(InputStream is){
  try{
   fs = new POIFSFileSystem(is);
   wb = new HSSFWorkbook(fs);
  }catch(IOException e){
   e.printStackTrace();
  }
  sheet = wb.getSheetAt(0);
  row = sheet.getRow(0);
  //标题总列数
  int colNum = row.getPhysicalNumberOfCells();
  String[] title = new String[colNum];
  for(int i = 0 ; i < colNum ; i++ ){
   title[i] = getStringCellValue(row.getCell((short)i));
  }
  return title;
 }

//读取Excel数据内容

 public Map<Integer,String> readExcelContent(InputStream is){
  Map<Integer,String> content = new HashMap<Integer,String>();
  String str = "";
  try{
   fs = new POIFSFileSystem(is);
   wb = new HSSFWorkbook(fs);
  }catch(IOException e){
   e.printStackTrace();
  }
  sheet = wb.getSheetAt(0);
  //得到总行数
  int rowNum = sheet.getLastRowNum();
  row = sheet.getRow(0);
  int colNum = row.getPhysicalNumberOfCells();
  
  for(int i = 0 ; i <= rowNum ; i++ ){   //rowNum从0开始算
   row = sheet.getRow(i);
   int j = 0;
   while(j < colNum){
  //每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据  
  //也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean  
    str += getStringCellValue(row.getCell((short)j)).trim() + "-";
    j++;
   }
   content.put(i, str);
   str = "";
  }
  return content;
 }
 
//获取单元格数据内容为字符串类型的数据  

private String getStringCellValue(HSSFCell cell){
  String strCell = "";
  switch(cell.getCellType()){
  case HSSFCell.CELL_TYPE_STRING:
   strCell = cell.getStringCellValue();
   break;
  case HSSFCell.CELL_TYPE_NUMERIC:
   strCell = String.valueOf(cell.getNumericCellValue());
   break;
  case HSSFCell.CELL_TYPE_BOOLEAN:
   strCell = String.valueOf(cell.getBooleanCellValue());
   break;
  case HSSFCell.CELL_TYPE_BLANK:
   strCell = "";
   break;
  default:
   strCell = "";
   break;
  }
  if(strCell.equals("") || strCell == null){
   return "";
  }
  if(cell == null){
   return "";
  }
  return strCell;
 }

//获取单元格数据内容为日期类型的数据 

private String getDateCellValue(HSSFCell cell){
  String result = "";
  try{
   int cellType = cell.getCellType();
   if(cellType == HSSFCell.CELL_TYPE_NUMERIC){
    Date date = cell.getDateCellValue();
    result = (date.getYear() + 2000) + "-" + (date.getMonth() + 1) + "-"
             + date.getDate();
   }
   else if(cellType == HSSFCell.CELL_TYPE_STRING){
    String date = getStringCellValue(cell);
    result = date.replaceAll("[年月]", "-").replace("日","").trim();
   }
   else if(cellType == HSSFCell.CELL_TYPE_BLANK){
    result ="";
   }
  }catch(Exception e){
   System.out.println("日期格式不正确！");
   e.printStackTrace();
  }
  return result;
 }
 
 public void write(InputStream is,String text) throws IOException {
  try{
   fs = new POIFSFileSystem(is);
   wb = new HSSFWorkbook(fs);
  }catch(IOException e){
   e.printStackTrace();
  }
  sheet = wb.getSheetAt(0);
  row = sheet.getRow(1);
//  int colNum = row.getPhysicalNumberOfCells();
  HSSFCell cell = row.getCell((short) 0);
  cell.setCellValue(text);
  try {
   FileOutputStream fileout = new FileOutputStream("try.xls");
   wb.write(fileout);
   fileout.close();
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  }
 }
 
 
 public static void main(String[] args){
  try{
//   FileWriter out = new FileWriter("result.txt");
//   BufferedWriter writer = new BufferedWriter(out);
   //对读取Excel表格标题的测试
   InputStream is = new FileInputStream("balance sheet.xls");
   ExcelBasic excel = new ExcelBasic();
   String[] title = excel.readExcelTitle(is);
   System.out.println("获得Excel表格的标题！");
   for(String s : title){
    System.out.print(s + "");
   }
   print("");
   
   //对读取Excel表格内容的测试
   InputStream is2 = new FileInputStream("try.xls");
   Map<Integer,String> map = excel.readExcelContent(is2);
   print("");
   print("获得Excel表格的内容！");
   for(int i = 0 ; i < map.size(); i++ ){
    print(map.get(i).replace("-"," "));
//    out.write(map.get(i).replace("-"," "));
   }
   is2.close();
   
   //写入测试
   String text = "YY";
   InputStream is3 = new FileInputStream("try.xls");
   excel.write(is3,text);
   
   Map<Integer,String> map2 = excel.readExcelContent(is3);
   print("");
   print("2.获得Excel表格的内容");
   for(int i = 0 ; i < map.size(); i++ ){
    print(map2.get(i).replace("-"," "));
   }
   is3.close();
   
  }catch(FileNotFoundException e){
   print("未找到指定路径的文件");
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }
 }
}