Excel文件的读取下载处理

Excel文件的读取下载:

Excel文件的读取

POM依赖包

    <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
Java代码
import org.apache.poi.hssf.usermodel.*;
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.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

public class ReadExcel {

    /**
     * 对外提供读取excel 的方法
     * */
    public static List<List<Object>> readExcel(File file) throws IOException {
        //获取文件名称
        String fileName = file.getName();
        //获取扩展名
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
                .substring(fileName.lastIndexOf(".") + 1);
        Workbook workbook;
        if ("xls".equals(extension)) {
            workbook = new HSSFWorkbook(new FileInputStream(file));
        } else if ("xlsx".equals(extension)) {
            workbook = new XSSFWorkbook(new FileInputStream(file));
        } else {
            throw new IOException("不支持的文件类型");
        }
        return readExcel(workbook);
    }
    /**
     * 读取 office excel
     * @throws IOException
     * @throws FileNotFoundException
     */
    private static List<List<Object>> readExcel(Workbook workbook) {
        List<List<Object>> list = new LinkedList<>();
        //获取第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        Object value ;
        Row row ;
        Cell cell;
        int counter = 0;

        //遍历行
        for (int i = sheet.getFirstRowNum(); counter < sheet
                .getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            } else {
                counter++;
            }
            List<Object> linked = new LinkedList<>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                //格式类型处理
                value=getCellValue(cell);
                if (value == null || "".equals(value)) {
                    continue;
                }
                linked.add(value);
            }
            list.add(linked);
        }
        return list;
    }

    public static void main(String[] args) {
        try {
            readExcel(new File("C:\Users\86159\Desktop\黑名单.xls"));
            // readExcel(new File("D:\test.xls"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    private static Object getCellValue(Cell cell){
        DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
        SimpleDateFormat sdf = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    return df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle()
                        .getDataFormatString())) {
                    return nf.format(cell.getNumericCellValue());
                } else {
                    return sdf.format(HSSFDateUtil.getJavaDate(cell
                            .getNumericCellValue()));
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case BLANK:
                return "";
            default:
                return cell.toString();
        }
    }


}
参考链接

基本上使用的是Apach 的POI 可以去官网查看更详细的使用方式

http://poi.apache.org/components/spreadsheet/quick-guide.html

匿名

发表评论

匿名网友