java之按行、列读excel笔记-原创手记-慕课网

首先jar包地址：https://pan.baidu.com/s/1hrPilta
或者自己去poi官网去下载。

核心工具类

package com.beforeship.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.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 ExcelUtil
{
    //默认单元格内容为数字时格式
    private static DecimalFormat df = new DecimalFormat("0");
    // 默认单元格格式化日期字符串
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    // 格式化数字
    private static DecimalFormat nf = new DecimalFormat("0.00");

    public static ArrayList<ArrayList<Object>> readExcel(final File file, final Integer onesheet)
    {
        if (file == null)
        {
            return null;
        }
        if (file.getName().endsWith("xlsx"))
        {
            //处理ecxel2007
            return readExcel2007(file, onesheet);
        }
        else
        {
            //处理ecxel2003
            return readExcel2003(file, onesheet);
        }
    }

    /*
     * @return 将返回结果存储在ArrayList内，存储结构与二位数组类似 lists.get(0).get(0)表示过去Excel中0行0列单元格
     */
    public static ArrayList<ArrayList<Object>> readExcel2003(final File file, final Integer onesheet)
    {
        try
        {
            final ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> colList;
            final HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
            final HSSFSheet sheet = wb.getSheetAt(onesheet);
            HSSFRow row;
            HSSFCell cell;
            Object value;
            for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++)
            {
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if (row == null)
                {
                    //当读取行为空时
                    if (i != sheet.getPhysicalNumberOfRows())
                    {//判断是否是最后一行
                        rowList.add(colList);
                    }
                    continue;
                }
                else
                {
                    rowCount++;
                }
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++)
                {
                    cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
                    {
                        //当该单元格为空
                        if (j != row.getLastCellNum())
                        {//判断是否是该行中最后一个单元格
                            colList.add("");
                        }
                        continue;
                    }
                    switch (cell.getCellType())
                    {
                        case XSSFCell.CELL_TYPE_STRING:
                            //System.out.println(i + "行" + j + " 列 is String type");
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            if ("@".equals(cell.getCellStyle().getDataFormatString()))
                            {
                                value = df.format(cell.getNumericCellValue());
                            }
                            else if ("General".equals(cell.getCellStyle().getDataFormatString()))
                            {
                                value = nf.format(cell.getNumericCellValue());
                            }
                            else
                            {
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                            }
                            //System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + value.toString());
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
                            //  System.out.println(i + "行" + j + " 列 is Boolean type");
                            value = Boolean.valueOf(cell.getBooleanCellValue());
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
                            //  System.out.println(i + "行" + j + " 列 is Blank type");
                            value = "";
                            break;
                        default:
                            //  System.out.println(i + "行" + j + " 列 is default type");
                            value = cell.toString();
                    }// end switch
                    colList.add(value);
                } //end for j
                rowList.add(colList);
            } //end for i

            return rowList;
        }
        catch (final Exception e)
        {
            return null;
        }
    }

    public static ArrayList<ArrayList<Object>> readExcel2007(final File file, final Integer onesheet)
    {
        try
        {
            final ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> colList;
            final XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
            final XSSFSheet sheet = wb.getSheetAt(onesheet);
            XSSFRow row;
            XSSFCell cell;
            Object value;
            for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++)
            {
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if (row == null)
                {
                    //当读取行为空时
                    if (i != sheet.getPhysicalNumberOfRows())
                    {//判断是否是最后一行
                        rowList.add(colList);
                    }
                    continue;
                }
                else
                {
                    rowCount++;
                }
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++)
                {
                    cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
                    {
                        //当该单元格为空
                        if (j != row.getLastCellNum())
                        {//判断是否是该行中最后一个单元格
                            colList.add("");
                        }
                        continue;
                    }
                    switch (cell.getCellType())
                    {
                        case XSSFCell.CELL_TYPE_STRING:
                            //System.out.println(i + "行" + j + " 列 is String type");
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            if ("@".equals(cell.getCellStyle().getDataFormatString()))
                            {
                                value = df.format(cell.getNumericCellValue());
                            }
                            else if ("General".equals(cell.getCellStyle().getDataFormatString()))
                            {
                                value = nf.format(cell.getNumericCellValue());
                            }
                            else
                            {
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                            }
                            //System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + value.toString());
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
                            //System.out.println(i + "行" + j + " 列 is Boolean type");
                            value = Boolean.valueOf(cell.getBooleanCellValue());
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
                            //  System.out.println(i + "行" + j + " 列 is Blank type");
                            value = "";
                            break;
                        default:
                            //System.out.println(i + "行" + j + " 列 is default type");
                            value = cell.toString();
                    }// end switch
                    colList.add(value);
                } //end for j
                rowList.add(colList);
            } //end for i

            return rowList;
        }
        catch (final Exception e)
        {
            //System.out.println("exception");
            return null;
        }
    }

    public static void writeExcel(final ArrayList<ArrayList<Object>> result, final String path)
    {
        if (result == null)
        {
            return;
        }
        final HSSFWorkbook wb = new HSSFWorkbook();
        final HSSFSheet sheet = wb.createSheet("sheet1");
        for (int i = 0; i < result.size(); i++)
        {
            final HSSFRow row = sheet.createRow(i);
            if (result.get(i) != null)
            {
                for (int j = 0; j < result.get(i).size(); j++)
                {
                    final HSSFCell cell = row.createCell(j);
                    cell.setCellValue(result.get(i).get(j).toString());
                }
            }
        }
        final ByteArrayOutputStream os = new ByteArrayOutputStream();
        try
        {
            wb.write(os);
        }
        catch (final IOException e)
        {
            e.printStackTrace();
        }
        final byte[] content = os.toByteArray();
        final File file = new File(path);//Excel文件生成后存储的位置。
        OutputStream fos = null;
        try
        {
            fos = new FileOutputStream(file);
            fos.write(content);
            os.close();
            fos.close();
        }
        catch (final Exception e)
        {
            e.printStackTrace();
        }
    }

    public static DecimalFormat getDf()
    {
        return df;
    }

    public static void setDf(final DecimalFormat df)
    {
        ExcelUtil.df = df;
    }

    public static SimpleDateFormat getSdf()
    {
        return sdf;
    }

    public static void setSdf(final SimpleDateFormat sdf)
    {
        ExcelUtil.sdf = sdf;
    }

    public static DecimalFormat getNf()
    {
        return nf;
    }

    public static void setNf(final DecimalFormat nf)
    {
        ExcelUtil.nf = nf;
    }

}

调用工具，按行读取、返回数据

    public static ArrayList<String> getHeadOfThird(final String filePath) throws Exception
    {
        final File file = new File(filePath);
        final ArrayList<String> data2 = new ArrayList<>();
        final ArrayList<String> data3 = new ArrayList<>();
        final Integer sheet = 3;
        final ArrayList<ArrayList<Object>> result = ExcelUtil.readExcel(file, sheet);

        for (int j = 0; j < result.get(1).size(); j++)
        {
            data2.add(result.get(1).get(j).toString());
        }

        for (int j = 0; j < result.get(2).size(); j++)
        {
            data3.add(result.get(2).get(j).toString());
        }
        //System.out.println("头部第三行：" + data2);
        return data3;
    }

按列读取、返回数据(行读取比较好读，按列相对麻烦些)

public static RequireOptionPreferred getRequireOption(final String filePath) throws Exception
    {

        final File file = new File(filePath);
        final ArrayList<ArrayList<Object>> result = ExcelUtil.readExcel(file, 2);
        final ArrayList<String> requireWord = new ArrayList<>();
        final ArrayList<String> optionalWord = new ArrayList<>();
        final ArrayList<String> preferredWord = new ArrayList<>();
        for (int i = 0; i < result.size(); i++)
        {
            for (int j = 0; j < result.get(i).size(); j++)
            {
                final String tmpRequired = "Required";
                if (tmpRequired.equals(result.get(i).get(j).toString()))
                {
                    requireWord.add(result.get(i).get(1).toString());
                }

                final String tmpOptional = "Optional";
                if (tmpOptional.equals(result.get(i).get(j).toString()))
                {
                    optionalWord.add(result.get(i).get(1).toString());
                }

                final String tmpPreferred = "Preferred";
                if (tmpPreferred.equals(result.get(i).get(j).toString()))
                {
                    preferredWord.add(result.get(i).get(1).toString());
                }

            }
        }
        final RequireOptionPreferred requireOptionPreferred = new RequireOptionPreferred();
        requireOptionPreferred.setOptionalWord(optionalWord);
        requireOptionPreferred.setOptionalWord(optionalWord);
        requireOptionPreferred.setPreferredWord(preferredWord);
        //System.out.println(requireOptionPreferred);
        return requireOptionPreferred;
    }

readExcel里面传入两个参数：前面的是excel路径
final File file = new File(filePath);
后面是读取excel中哪一个工作空间，第一个sheet为0，第二个为1...以此类推。

java之按行、列读excel笔记原创