优雅的校验参数

2019-12-15

字数统计: 1.4k | 阅读时长≈ 7 分钟

利用Poi实现顺序读取Excel中图片

先推一下一个Excel处理框架EasyExcel，阿里开源的非常友好，使用中，客户有需求在excel中上传图片直接写入数据库，但是EasyExcel还没有实现这个功能，也还没实现单个sheet并发写入数据库

迫于无奈，只能自己用Poi来提取sheet中的图片，并按图片顺序实现插入。

首先引入Poi包

<!--Excel模块主要是为了处理Image-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.9</version>
</dependency>

复制我写好的ExcelUtil工具，这里根据我的业务进行了定制，请根据实际需求进行修改。把imgdata转成img的方法也在里面了 enjoy

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @description: ExcelInUtil
 * @author: Zjy
 * @create: 2020-06-30 15:22
 **/
public class ExcelInUtil {
    // 默认单元格格式化日期字符串
    public static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    // 默认单元格内容为数字时格式
    private static DecimalFormat df = new DecimalFormat("0");
    // 格式化数字
    private static DecimalFormat nf = new DecimalFormat("0.00");

    /**
     * 读取Excel中sheet1的内容
     * @param file
     * @return ArrayList<ArrayList<Object>>
     */
    public static ArrayList<ArrayList<Object>> readExcel(File file) {
        if (file == null) {
            return null;
        }
        if (file.getName().endsWith("xlsx")) {
            // 处理ecxel2007
            return readExcel2007(file);
        } else if (file.getName().endsWith("xls")) {
            // 处理ecxel2003
            return readExcel2003(file);
        } else {
            return null;
        }
    }

    /**
     * 读取03版本的Excel
     * @param file
     * @return
     */
    private static ArrayList<ArrayList<Object>> readExcel2003(File file) {
        try {
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> oneRow = null;
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
            HSSFSheet sheet = workbook.getSheetAt(0);
            HSSFRow row;
            HSSFCell cell;
            Object value;
            for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                oneRow = new ArrayList<Object>();
                if (row == null || checkRowNull2003(row)) {
                    continue;
                }
                for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                    cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        if (j != row.getLastCellNum()) {
                            oneRow.add("");
                        }
                        continue;
                    }
                    switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            value = cell.getBooleanCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            value = "";
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                double doubleVal = cell.getNumericCellValue();
                                int intVal = (int) Math.round(doubleVal);
                                if (Double.parseDouble(intVal + ".0") == doubleVal) {
                                    value = df.format(intVal);
                                } else {
                                    value = nf.format(doubleVal);
                                }
                            } else {
                                value = ((Double)cell.getNumericCellValue()).toString();
                            }
                            break;
                        default:
                            value = cell.toString();
                            break;
                    }
                    oneRow.add(value);
                }
                rowList.add(oneRow);
            }
            return rowList;
        } catch (IOException e) {
            return null;
        }
    }

    /**
     * 读取07的Excel内容
     * @param file
     * @return
     */
    private static ArrayList<ArrayList<Object>> readExcel2007(File file) {
        try {
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> oneRow = null;
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet sheet = workbook.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;
            Object value;
            for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                oneRow = new ArrayList<Object>();
                if (row == null || checkRowNull2007(row)) {
                    continue;
                }
                for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                    cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
                        if (j != row.getLastCellNum()) {
                            oneRow.add("");
                        }
                        continue;
                    }
                    switch (cell.getCellType()) {
                        case XSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
                            value = cell.getBooleanCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
                            value = "";
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                double doubleVal = cell.getNumericCellValue();
                                int intVal = (int) Math.round(doubleVal);
                                if (Double.parseDouble(intVal + ".0") == doubleVal) {
                                    value = df.format(intVal);
                                } else {
                                    value = nf.format(doubleVal);
                                }
                            } else {
                                value = ((Double)cell.getNumericCellValue()).toString();
                            }
                            break;
                        default:
                            value = cell.toString();
                            break;
                    }
                    oneRow.add(value);
                }
                rowList.add(oneRow);
            }
            return rowList;
        } catch (IOException e) {
            return null;
        }
    }


    /**
     * 把图片输出
     * @param map
     * @throws IOException
     */
    public static void printImg(Map<String, PictureData> map) throws IOException {

            Object key[] = map.keySet().toArray();
            for (int i = 0; i < map.size(); i++) {
                // 获取图片流
                PictureData pic = map.get(key[i]);
                // 获取图片索引
                String picName = key[i].toString();
                // 获取图片格式
                String ext = pic.suggestFileExtension();
                byte[] data = pic.getData();

                FileOutputStream out = new FileOutputStream("D:\\pic" + picName + "." + ext);
                out.write(data);
                out.close();
            }
        }


    /**
     * 判断行为空(xls)
     * @param row
     * @return
     */
    private static boolean checkRowNull2003(HSSFRow row) {
        for (int i = row.getFirstCellNum(); i < row.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 判断行为空(xlsx)
     * @param row
     * @return
     */
    private static boolean checkRowNull2007(XSSFRow row) {
        for (int i = row.getFirstCellNum(); i < row.getPhysicalNumberOfCells(); i++) {
            XSSFCell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != XSSFCell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 获取图片和位置 (xls)
     * @param file
     * @return
     * @throws IOException
     */
    public static Map<String, HSSFPictureData> getPictures03 (File file) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = workbook.getSheetAt(0);
        Map<String, HSSFPictureData> map = new HashMap<String, HSSFPictureData>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                HSSFPictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                map.put(key, pdata);
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     * @param file
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPictures07 (File file) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = workbook.getSheetAt(0);
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, picture.getPictureData());
                }
            }
        }
        return map;
    }

}

版权声明： 本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！