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();
        }
    }
    
   /**
     * 导出excel
     *
     * @param list
     * @param params
     * @throws IOException
     */
    public static void exportExcel(List<?> list, Map<String, String> params) throws IOException {

        //Step1 excel头部信息编排 从数据字典中获取
        String headStr = params.get("headStr");
        String keyStr = params.get("keyStr");
        String sheetName = params.get("sheetName");
        String path = params.get("path");
        //头部信息字段
        String[] headArr = headStr.split("\\|");
        //字段名称
        String[] keyArr = keyStr.split("\\|");

        //创建一个Book
        Workbook wb = new XSSFWorkbook();
        //创建一个sheet
        Sheet sheet = wb.createSheet(sheetName);
        //创建头部行
        Row row = sheet.createRow(0);
        //创建单元格字段风格
        CellStyle style = wb.createCellStyle();
        //设置格式居中
        style.setAlignment(HorizontalAlignment.CENTER);
        Cell cell;
        //sheet中添加头部中文信息
        for (int i = 0; i < headArr.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headArr[i]);
            cell.setCellStyle(style);
        }
        try {
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                for (int j = 0; j < keyArr.length; j++) {
                    Object obj = JavaUtils.getGetMethod(list.get(i), keyArr[j]);
                    if (obj == null) {
                        obj = "";
                    }
                    if (obj instanceof Date) {
                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy年MM月dd日HH:mm:ss");
                        row.createCell(j).setCellValue(formatter.format(obj));
                    } else {
                        row.createCell(j).setCellValue(obj.toString());
                    }
                }
            }
        } catch (Exception e) {
            return;
        }
        FileOutputStream os = new FileOutputStream(path);
        try {
            wb.write(os);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            os.close();
        }
    }

}

参考链接

基本上使用的是Apach 的POI 可以去官网查看更详细的使用方式
文件读取的时候会有多种不同的情况,有人是File 有人用的MultiFile，等等，甚至还有可能转换成字节数组的，随机应变调整,比如如果是dubbo服务，就不能使用File而需要使用字节数组，奥力给

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