java操作excel文件

采用POI操作excel

API:http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html

poi包：http://pan.baidu.com/s/1hmIQU

一.读取excel内容

1.excel内容的值如果不是string，则用getNumericCellValue()，得到double类型，再做相应转换，如果为string，则用getStringCellValue()

public static String getExcel(int index,int rowNum,int colNum) {
     
    //File file = new File("D:/BaiduYunDownload/excel/testdata.xls");
    File file = new File("./POIexcel/testdata.xls");
    String cellValue = null;
    int rowN = rowNum-1;//将excel的行数-1
     
    Row row = null;
    Cell cell= null;
    HSSFCell hf = null;
   // Cell cell_b = null;
 
   try {
       FileInputStream in = new FileInputStream(file);
       HSSFWorkbook wb = new HSSFWorkbook(in);     
       HSSFSheet sheet = wb.getSheetAt(index);//sheet页，index从0开始
        
       //从哪行读取
      // int firstRowNum = sheet.getFirstRowNum()+1;
     //  int lastRowNum = sheet.getLastRowNum();
        
       row = sheet.getRow(rowN);       //取得第几行
       cell = row.getCell(colNum);        //取得行的第3列,从0开始
       if(cell!=null){
           //((Object) hf).setEncoding(HSSFCell.ENCODING_UTF_16);
            
           //判断excel内容的数值类型
           switch(cell.getCellType()) {
               case Cell.CELL_TYPE_STRING://String 
                   cellValue = cell.getStringCellValue().trim();
                   break;
               case Cell.CELL_TYPE_NUMERIC://number
                   if(HSSFDateUtil.isCellDateFormatted(cell)) {
                       Date date = cell.getDateCellValue();
                       if (date != null) {//date
                           cellValue = new SimpleDateFormat("yyyy-MM-dd").format(date);
                       } else {
                           cellValue = "";
                       }
                   }else {
                       cellValue = new DecimalFormat("###.###").format(cell.getNumericCellValue());
                    }
                    break;
               case HSSFCell.CELL_TYPE_FORMULA:
 
                   // 导入时如果为公式生成的数据则无值
 
                   if (!cell.getStringCellValue().equals("")) {
                       cellValue = cell.getStringCellValue();
                   } else {
                       cellValue = cell.getNumericCellValue() + "";
                   }
                   break;
 
               case HSSFCell.CELL_TYPE_BLANK:
                   break;
 
               case HSSFCell.CELL_TYPE_ERROR:
                   cellValue = "";
                   break;
 
               case HSSFCell.CELL_TYPE_BOOLEAN:
 
                   cellValue = (cell.getBooleanCellValue() == true ? "Y": "N");
                   break;
                    
               default:
                   cellValue = "";
             /* 
               if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC) {
                   double i = cell.getNumericCellValue();     
                   cellValue = String.valueOf(i); 
               }else{
                   cellValue = cell.getStringCellValue().trim();
                   if(cellValue.equals("")){
                       System.out.println(rowNum+"行的值为空");
                   }
               }
           */
           }
       }
   }catch (Exception e) {
          e.printStackTrace();
   }
    
    return cellValue;
}

double转换为int：int i_yhfw= (int) Double.parseDouble(Demo.getExcel(index, 13));

二.设置excel内容

        public static void setExcel(String path, int sheet, int row, int col,String value) {

            try {
                File file = new File(path);

                FileInputStream in = new FileInputStream(file);
                HSSFWorkbook hw = new HSSFWorkbook(in);
        
                HSSFSheet hsheet= hw.getSheetAt(sheet);//目标sheet的索引            
                HSSFRow hrow = hsheet.getRow(row-1);//目标行的索引
                HSSFCell cell = hrow.createCell(col-1);//目标列的索引
                HSSFRichTextString val = new HSSFRichTextString(value);
                cell.setCellValue(val);
                            
                OutputStream out = new FileOutputStream(file);//获取文件输出流
                hw.write(out);//将内容写到excel
                out.close();
                in.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }

以上的方法的是HSFF只能操作03的excel，通过官方api介绍，使用XSFF可以操作07的excel，故优化代码如下，自动识别传入的excel是03的还是07的。

package com.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/** 
 * @author QiaoJiafei 
 * @version 创建时间：2015年11月12日 上午10:35:03 
 * 类说明 
 */
public class TestExcel037 {
    public static void main(String args[]) {
        System.out.println(getExcel("D:/03excel.xls",1,2,2));
        System.out.println(getExcel("D:/07excel.xlsx",1,2,2));

    }

    public static String getExcel(String path,int index,int rowNum,int colNum) {
        File file = new File(path);
        String cellValue = "";
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        try {
            FileInputStream in = new FileInputStream(file);
            if(path.endsWith(".xls")) {
                wb = new HSSFWorkbook(in);
                sheet = wb.getSheetAt(index-1);
            }else if (path.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(in);
                sheet = wb.getSheetAt(index-1);
            }
            row = sheet.getRow(rowNum);
            cell = row.getCell(colNum);
            if(cell!=null){
               switch(cell.getCellType()) {
                   case Cell.CELL_TYPE_STRING:
                       cellValue = cell.getStringCellValue().trim();
                       break;
                   case Cell.CELL_TYPE_NUMERIC:
                       if(HSSFDateUtil.isCellDateFormatted(cell)) {
                           Date date = cell.getDateCellValue();
                           if (date != null) {
                               cellValue = new SimpleDateFormat("yyyy-MM-dd").format(date);
                           } else {
                               cellValue = "";
                           }
                       }else {
                           cellValue = new DecimalFormat("###.###").format(cell.getNumericCellValue());
                        }
                        break;
                   case Cell.CELL_TYPE_FORMULA:


                       if (!cell.getStringCellValue().equals("")) {
                           cellValue = cell.getStringCellValue();
                       } else {
                           cellValue = cell.getNumericCellValue() + "";
                       }
                       break;

                   case Cell.CELL_TYPE_BLANK:
                       break;

                   case Cell.CELL_TYPE_ERROR:
                       cellValue = "";
                       break;

                   case HSSFCell.CELL_TYPE_BOOLEAN:

                       cellValue = (cell.getBooleanCellValue() == true ? "Y": "N");
                       break;
                       
                   default:
                       cellValue = "";
               }
           }
               in.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        
        return cellValue;
    
    }
    
}

所用jar包：

关于DecimalFormat的用法，参考http://www.cnblogs.com/lsun/archive/2011/06/22/2087116.html

DecimalFormat 是 NumberFormat 的一个具体子类，用于格式化十进制数字。

DecimalFormat 包含一个模式和一组符号

符号含义：

0 一个数字

# 一个数字，不包括 0

. 小数的分隔符的占位符

, 分组分隔符的占位符

; 分隔格式。

- 缺省负数前缀。

% 乘以 100 和作为百分比显示

? 乘以 1000 和作为千进制货币符显示；用货币符号代替；如果双写，用

国际货币符号代替。如果出现在一个模式中，用货币十进制分隔符代

替十进制分隔符。

X 前缀或后缀中使用的任何其它字符，用来引用前缀或后缀中的特殊字符。

例子：

DecimalFormat df1 = new DecimalFormat("0.0");

DecimalFormat df2 = new DecimalFormat("#.#");

DecimalFormat df3 = new DecimalFormat("000.000");

DecimalFormat df4 = new DecimalFormat("###.###");

System.out.println(df1.format(12.34));

System.out.println(df2.format(12.34));

System.out.println(df3.format(12.34));

System.out.println(df4.format(12.34));

结果：

12.3

12.3

012.340

12.34