JAVA对excel文件的处理方式

使用Apache的POI类对不同的excel文件对它们能够进行简单的读取操作：

1.读取excel，对于xls和xlsx有不同的类去对他们进行处理，个人理解的便是如果是xls类型的文件则在获取了文件的类型以后调用的类前面主要是HSSF，而xlsx格式的则调用XSSF类。

获取文件类型可以使用substring(filename.lastindexof(“.”));

下面便是读取文件的方法：

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel{

    private static XSSFWorkbook workbook;

    public static void read(InputStream inputStream) throws IOException {
        workbook = new XSSFWorkbook(inputStream);
        for(int sheetIndex = 0;sheetIndex < workbook.getNumberOfSheets();sheetIndex++){
            XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
            int num = 0;
            System.out.println("sheet序号："+sheetIndex+",sheet名称："+workbook.getSheetName(sheetIndex));
            for(int i = 0; i < sheet.getLastRowNum();i++){
                XSSFRow row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                XSSFCell cell = row.getCell(i);
                system.out.println(cell);
            }
        }
    }

    private static String getCellValue(XSSFCell cell){
        String cellValue = "";
        DataFormatter formatter = new DataFormatter();
        if (cell != null) {
            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = formatter.formatCellValue(cell);
                    } else {
                        double value = cell.getNumericCellValue();
                        int intValue = (int) value;
                        cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                    }
                    break;
                case XSSFCell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case XSSFCell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case XSSFCell.CELL_TYPE_FORMULA:
                    cellValue = String.valueOf(cell.getCellFormula());
                    break;
                case XSSFCell.CELL_TYPE_BLANK:
                    cellValue = "";
                    break;
                case XSSFCell.CELL_TYPE_ERROR:
                    cellValue = "";
                    break;
                default:
                    cellValue = cell.toString().trim();
                    break;
            }
        }
        return cellValue.trim();
    }

    public static void main(String[] args) {
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(new File("C:\\test.xlsx"));
            read(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

Excel的写法则有与读取是类似的，只不过是个相反的过程。

import java.io.File; 
import java.io.FileNotFoundException; 
import java.io.FileOutputStream; 
import java.io.IOException; 
import java.io.OutputStream; 
import org.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.hssf.usermodel.HSSFRichTextString; 
import org.apache.poi.hssf.usermodel.HSSFRow; 
import org.apache.poi.hssf.usermodel.HSSFSheet; 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/** 
* POI入门 ：简单写出excel数据 
* */
public class WriteExcelTest { 
    public static void write(OutputStream outputStream) throws IOException{ 
    //初始一个workbook 
    HSSFWorkbook workbook = new HSSFWorkbook(); 
    //创建一个表
    HSSFSheet sheet = workbook.createSheet("firstSheet");
    //创建行
    HSSFRow row = sheet.createRow(0);
    //创建单元格
    HSSFCell cell = row.createCell(0);

    cell.setCellValue(new HSSFRichTextString("hello POI"));
    workbook.write(outputStream);
    }

    public static void main(String[] args) {
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(new File("E:\\helloPOI.xls"));
            write(outputStream);
        }catch (FileNotFoundException e) {
            e.printStackTrace();
            }catch (IOException e) {
            e.printStackTrace();
            }finally{
            if(outputStream != null){
            try {
            outputStream.close();
            } catch (IOException e) {
            e.printStackTrace();
            }
        }
    }
}