Java 通过Poi api操作(read/write)Excel

依赖jar

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

我写的工具类

package com.plugins.core.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * 支持取.txt、.doc、.docx、.xls、.xlsx，5种格式文档的内容，以字符串的形式返回。
 * {@link http://poi.apache.org}
 *
 * Created by simon on 16-6-28.
 */
public class JavaExcelUtils {

//   Excel格式如下示例:

//    systemFileType    fileNo  fileName    responsibleDpt  remark
//    1                 SC0001  test1.clxs  航海部         没有备注1
//    1                 SC0002  test2.clxs  航海部         没有备注2
//    2                 SC0003  test3.clxs  航海部         没有备注3



    private static final Logger logger = LoggerFactory.getLogger(JavaExcelUtils.class);

    //解析Excel文件
    public static List<List<Object>> parseExcel(InputStream input) throws Exception {
        final DecimalFormat decimalFormat = new DecimalFormat("#.####");

        final List<List<Object>> items = new ArrayList<List<Object>>();

        final Workbook wb =WorkbookFactory.create(input);

        final Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        // skip the first row
        if (rowIterator.hasNext()) {
            rowIterator.next();
        }
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            List<Object> object=new ArrayList<Object>();
            items.add(object);

            int colIndex = 0;
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String value = getCellValueAsString(cell, decimalFormat);
                switch(colIndex) {
                    case 0: //文件类型
                        object.add(value);
                        break;
                    case 1: //文件编号
                        object.add(value);
                        break;
                    case 2: //文件名称
                        object.add(value);
                        break;
                    case 3: responsibleDpt
                        object.add(value);
                        break;
                    default:
                        object.add(value);
                        break;
                }
                colIndex++;
            }
        }
        wb.close();
        return items;
    }

    public  static byte[] createExcel(List<List<Object>> lists) throws Exception {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("Sheet 1");
        // Create a row and put some cells in it. Rows are 0 based.
        int colIndex = 0;
        Row row = sheet.createRow(0);
        row.createCell(colIndex++).setCellValue("systemFileType");
        row.createCell(colIndex++).setCellValue("fileNo");
        row.createCell(colIndex++).setCellValue("fileName");
        row.createCell(colIndex++).setCellValue("responsibleDpt");
        row.createCell(colIndex++).setCellValue("remark");

        int rowIndex = 1;
        for (List<Object> item : lists) {
            colIndex = 0; // 重置列索引为0
            row = sheet.createRow(rowIndex);
            for(int i=0;i<item.size();i++){
                row.createCell(colIndex++).setCellValue(item.get(i).toString());
            }
            rowIndex++;
        }

        ByteArrayOutputStream output = new ByteArrayOutputStream();
        wb.write(output);
        output.close();
        wb.close();
        return output.toByteArray();
    }

    // byte[] to file
    public static void getFile(byte[] bfile, String filePath,String fileName) {
        BufferedOutputStream bos = null;
        FileOutputStream fos = null;
        File file = null;
        try {
            File dir = new File(filePath);
            if(!dir.exists()&&dir.isDirectory()){//判断文件目录是否存在
                dir.mkdirs();
            }
            file = new File(filePath+File.separator+fileName);
            fos = new FileOutputStream(file);
            bos = new BufferedOutputStream(fos);
            bos.write(bfile);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }


    private static String getCellValueAsString(Cell cell, DecimalFormat decimalFormat) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                double numericCellValue = cell.getNumericCellValue();
                return decimalFormat.format(numericCellValue);
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                break;
        }
        return null;
    }
}

我写的测试类

@Test
    public  void  test45() throws Exception {
        System.out.println("start......");
        File file=new File("/software/mine-workspace/idea/platform/data/filestorage/1/201606/2815/2348/5de92fb8-c6ce-4ed5-9e8f-b0da0816ce0e.xlsx");
        InputStream inputStream=new FileInputStream(file);
        List<List<Object>> lists= JavaExcelUtils.parseExcel(inputStream);
        for (List<Object> o:lists){
            for(int i=0;i<o.size();i++){
                System.out.print(o.get(i)+"\t");
            }
            System.out.println();
        }

        System.out.println("write.....");
        byte[] bytes=JavaExcelUtils.createExcel(lists);
        JavaExcelUtils.getFile(bytes,"/home/simon/桌面/","data.xlsx");
        System.out.println("end..........");
    }

简单好用。。。。