java读取excel

java

浏览数:385

2019-1-8

package com.skywares.safety.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletInputStream;
import javax.servlet.ServletOutputStream;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.RichTextString;
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.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.google.common.base.Strings;

public class ExcelUtil {

    /**
     * 导入excel文件,返回List>
     * 
     * @Author 不告诉你
     * @date 2016-09-09 15:25:35
     * @param Object
     *            in 数据输出流(FileInputStream,ServletInputStream)
     * @return
     */
    public static List> importExcel(Object in) throws Exception {
        Workbook tempWorkbook = null;
        Sheet fromsheet = null;
        Row fromRow = null;
        Cell fromCell = null;
        Row headerRow = null;
        Cell headerCell = null;
        List> list = new ArrayList<>();

        if (in instanceof InputStream) {
            tempWorkbook = create((InputStream) in);
        }else if (in instanceof FileInputStream) {
            tempWorkbook = create((FileInputStream) in);
        }else if (in instanceof ServletInputStream) {
            tempWorkbook = create((ServletInputStream) in);
        }else {
            return null;
        }
        for (int i = 0; i < tempWorkbook.getNumberOfSheets(); i++) {
            fromsheet = tempWorkbook.getSheetAt(i);
            if (fromsheet == null || fromsheet.getLastRowNum() == 0 || fromsheet.getLastRowNum() < 3) {
                continue;
            }
            removeRow(fromsheet, 1);
            headerRow = fromsheet.getRow(0);
            for (int k = 1; k < fromsheet.getLastRowNum() + 1; k++) {
                Map map = new HashMap();
                fromRow = fromsheet.getRow(k);
                if (fromRow == null || fromRow.getPhysicalNumberOfCells() == 0) {
                    continue;
                }
                for (int j = headerRow.getFirstCellNum(); j < headerRow.getPhysicalNumberOfCells(); j++) {
                    fromCell = fromRow.getCell(j);
                    headerCell = headerRow.getCell(j);
                    if (fromCell == null || headerCell == null) {
                        continue;
                    }
                    String headerCellValue = headerCell.getStringCellValue();
                    String[] headerCellValues = headerCellValue.split("\\.");
                    if (Strings.isNullOrEmpty(headerCellValue) || headerCellValues.length < 2) {
                        continue;
                    }
                    int cType = fromCell.getCellType();
                    switch (cType) {
                    case Cell.CELL_TYPE_STRING:
                        map.put(headerCellValues[1], fromCell.getRichStringCellValue().toString().trim());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        map.put(headerCellValues[1], fromCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        map.put(headerCellValues[1], fromCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        map.put(headerCellValues[1], fromCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        map.put(headerCellValues[1], fromCell.getErrorCellValue());
                        break;
                    default:
                        map.put(headerCellValues[1], fromCell.getRichStringCellValue().toString().trim());
                        break;
                    }
                }
                list.add(map);
            }
        }

        tempWorkbook.close();
        return list;
    }
    
    /**
     * 根据excel模版创建文件(map或者javaBean或者ResultSet数据源)
     * 
     * @Author 不告诉你
     * @date 2016-09-05 11:11:11
     * @param String
     *            tempPath 模版路径(模版xls只能导出2003,模版xlsx只能导出2007)
     * @param String
     *            sheetName 生成工作表的名字(为空时,默认为sheet1)
     * @param Object
     *            data map或者javaBean或者ResultSet数据源
     * @param Object
     *            os 数据输出流(FileOutputStream,ServletOutputStream)
     * @return
     */
    public static void paddingExcel(String tempPath, String sheetName, Object data, Object os) throws Exception {
        tempExportExcel(tempPath, sheetName, null, null, os, data);
    }

    /**
     * 根据excel模版创建文件(model)
     * 
     * @Author 不告诉你
     * @date 2016-09-05 11:11:11
     * @param String
     *            tempPath 模版路径(模版xls只能导出2003,模版xlsx只能导出2007)
     * @param String
     *            sheetName 生成工作表的名字(为空时,默认为sheet1)
     * @param Object
     *            data model List数据源
     * @param Object
     *            os 数据输出流(FileOutputStream,ServletOutputStream)
     * @param Object
     *            headerData 固定数据源map或者javaBean或者ResultSet
     * @return
     */
    public static void javaBeanToExcel(String tempPath, String sheetName, Object data, Object os,
            Object headerData) throws Exception {
        tempExportExcel(tempPath, sheetName, data, null, os, headerData);
    }

    /**
     * 根据excel模版创建文件(ResultSet rs)
     * 
     * @Author 不告诉你
     * @date 2016-09-05 11:11:11
     * @param String
     *            tempPath 模版路径(模版xls只能导出2003,模版xlsx只能导出2007)
     * @param String
     *            sheetName 生成工作表的名字(为空时,默认为sheet1)
     * @param ResultSet 
     *            rs 结果集 List数据源
     * @param Object
     *            os 数据输出流(FileOutputStream,ServletOutputStream)
     * @param Object
     *            headerData 固定数据源map或者javaBean或者ResultSet
     * @return
     */
    public static void resultSetToExcel(String tempPath, String sheetName, ResultSet rs, Object os, Object headerData)
            throws Exception {
        tempExportExcel(tempPath, sheetName, null, rs, os, headerData);
    }

    private static void tempExportExcel(String tempPath, String sheetName, Object data, ResultSet rs, Object os,
            Object headerData) throws Exception {
        if (!System.getProperty("os.name").equalsIgnoreCase("Linux")) {
            tempPath = tempPath.substring(1);
        }
        String version = tempPath.substring(tempPath.lastIndexOf("."));
        if (version.equals(".xls")) {
            tempExport2003Excel(tempPath, sheetName, data, rs, os, headerData);
        } else if (version.equals(".xlsx")) {
            tempExport2007Excel(tempPath, sheetName, data, rs, os, headerData);
        } else {
            return;
        }
    }

    private static void tempExport2007Excel(String tempPath, String sheetName, Object data, ResultSet rs,
            Object os, Object headerData) throws Exception {
        OutputStream out = null;
        Workbook tempWorkbook = null;
        Sheet fromsheet = null;
        Cell fromCell = null;
        try {
            if (os instanceof FileOutputStream) {
                out = (FileOutputStream) os;
            }else if (os instanceof ServletOutputStream) {
                out = (ServletOutputStream) os;
            }else {
                return;
            }
            if (Strings.isNullOrEmpty(sheetName)) {
                sheetName = "sheet1";
            }
            tempWorkbook = new XSSFWorkbook(OPCPackage.open(new FileInputStream(tempPath)));
            fromsheet = (XSSFSheet) tempWorkbook.getSheetAt(0);
            if (fromsheet == null || fromsheet.getLastRowNum() == 0) {
                return;
            }
            tempWorkbook.setSheetName(0, sheetName);
            if (headerData != null) {
                int firstrow = fromsheet.getFirstRowNum();
                int lastrow = fromsheet.getLastRowNum();
                for (int i = firstrow; i < lastrow; i++) {
                    fromCell = fromsheet.getRow(i).getCell(0);
                    String start = fromCell.getStringCellValue();
                    if ("data.start".equalsIgnoreCase(start.trim())) {
                        lastrow = i;
                        break;
                    }
                    if (i == (lastrow - 1)) {
                        return;
                    }
                }
                paddingExcel(fromsheet, firstrow, lastrow, headerData);
            }
            if (data != null || rs != null) {
                int firstrow = fromsheet.getFirstRowNum();
                int lastrow = fromsheet.getLastRowNum();
                for (int i = firstrow; i < lastrow; i++) {
                    fromCell = fromsheet.getRow(i).getCell(0);
                    String start = fromCell.getStringCellValue();
                    if ("data.start".equalsIgnoreCase(start.trim())) {
                        firstrow = i;
                        break;
                    }
                    if (i == (lastrow - 1)) {
                        return;
                    }
                }
                if (data != null) {
                    copySheets(fromsheet, firstrow, lastrow, data, null);
                }
                if (rs != null) {
                    copySheets(fromsheet, firstrow, lastrow, null, rs);
                }
                removeRow(fromsheet, firstrow);
                removeRow(fromsheet, firstrow);
                removeRow(fromsheet, firstrow);
            }
            tempWorkbook.write(out);
            out.flush();
            tempWorkbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @SuppressWarnings("resource")
    private static void tempExport2003Excel(String tempPath, String sheetName, Object data, ResultSet rs,
            Object os, Object headerData) throws Exception {
        OutputStream out = null;
        Workbook tempWorkbook = null;
        Sheet fromsheet = null;
        Cell fromCell = null;
        try {
            if (os instanceof FileOutputStream) {
                out = (FileOutputStream) os;
            }else if (os instanceof ServletOutputStream) {
                out = (ServletOutputStream) os;
            }else {
                return;
            }
            if (Strings.isNullOrEmpty(sheetName)) {
                sheetName = "sheet1";
            }
            tempWorkbook = new HSSFWorkbook(new FileInputStream(tempPath));
            fromsheet = (HSSFSheet) tempWorkbook.getSheetAt(0);
            if (fromsheet == null || fromsheet.getLastRowNum() == 0) {
                return;
            }
            tempWorkbook.setSheetName(0, sheetName);
            if (headerData != null) {
                int firstrow = fromsheet.getFirstRowNum();
                int lastrow = fromsheet.getLastRowNum();
                for (int i = firstrow; i < lastrow; i++) {
                    fromCell = fromsheet.getRow(i).getCell(0);
                    String start = fromCell.getStringCellValue();
                    if ("data.start".equalsIgnoreCase(start.trim())) {
                        lastrow = i;
                        break;
                    }
                    if (i == (lastrow - 1)) {
                        return;
                    }
                }
                paddingExcel(fromsheet, firstrow, lastrow, headerData);
            }
            if (data != null || rs != null) {
                int firstrow = fromsheet.getFirstRowNum();
                int lastrow = fromsheet.getLastRowNum();
                for (int i = firstrow; i < lastrow; i++) {
                    fromCell = fromsheet.getRow(i).getCell(0);
                    String start = fromCell.getStringCellValue();
                    if ("data.start".equalsIgnoreCase(start.trim())) {
                        firstrow = i;
                        break;
                    }
                    if (i == (lastrow - 1)) {
                        return;
                    }
                }
                if (data != null) {
                    copySheets(fromsheet, firstrow, lastrow, data, null);
                }
                if (rs != null) {
                    copySheets(fromsheet, firstrow, lastrow, null, rs);
                }
                removeRow(fromsheet, firstrow);
                removeRow(fromsheet, firstrow);
                removeRow(fromsheet, firstrow);
            }
            tempWorkbook.write(out);
            out.flush();
            tempWorkbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @SuppressWarnings("unchecked")
    private static void copySheets(Sheet fromsheet, int firstrow, int lastrow, Object data, ResultSet rs)
            throws Exception {
        if ((firstrow == -1) || (lastrow == -1) || lastrow < firstrow) {
            return;
        }
        Row fromRow = null;
        Row newRow = null;
        Cell newCell = null;
        Cell fromCell = null;
        RichTextString textValue = null;
        CellStyle fromCellStyle = null;

        fromCell = fromsheet.getRow(firstrow + 2).getCell(0);
        String end = fromCell.getStringCellValue();
        if (!"data.end".equalsIgnoreCase(end.trim())) {
            return;
        }
        if (data != null) {
            int iRow = firstrow + 3;
            List javaBean = (List) data;
            int j=0;
            for (Object o : javaBean) {
                j++;
                Map map = FormatUtil.javaBeanToMap(o);
                newRow = fromsheet.createRow(iRow);
                fromRow = fromsheet.getRow(firstrow + 1);
                for (int i = fromRow.getFirstCellNum(); i < fromRow.getPhysicalNumberOfCells(); i++) {
                    fromCell = fromRow.getCell(i);
                    newCell = newRow.createCell(i);
                    if (fromCell == null) {
                        continue;
                    }
                    fromCellStyle = fromCell.getCellStyle();
                    newCell.setCellStyle(fromCellStyle);
                    for (Map.Entry entry : map.entrySet()) {
                        Object value = entry.getValue();
                        String fromCellValue = fromCell.getStringCellValue();
                        if (entry.getKey().equalsIgnoreCase(fromCellValue.split("\\.")[1].trim())) {
                            if (value instanceof Date) {
                                Date date = (Date) value;
                                Calendar cDate = Calendar.getInstance();
                                cDate.setTime(date);
                                int hh = cDate.get(Calendar.HOUR_OF_DAY); // 获取当前小时
                                int mm = cDate.get(Calendar.MINUTE); // 获取当前分钟
                                int ss = cDate.get(Calendar.SECOND); // 获取当前秒
                                if (hh == 0 && mm == 0 && ss == 0) {
                                    newCell.setCellValue(DateUtil.dateToString(date, "yyyy-MM-dd"));
                                } else {
                                    newCell.setCellValue(DateUtil.dateToString(date, "yyyy-MM-dd HH:mm:ss"));
                                }
                            }
                            if (value instanceof Integer) {
                                int intValue = (Integer) value;
                                newCell.setCellValue(intValue);
                            } else if (value instanceof Float) {
                                float fValue = (Float) value;
                                if (fromsheet instanceof HSSFSheet) {
                                    textValue = new HSSFRichTextString(String.valueOf(fValue));
                                }
                                if (fromsheet instanceof XSSFSheet) {
                                    textValue = new XSSFRichTextString(String.valueOf(fValue));
                                }
                                newCell.setCellValue(textValue);
                            } else if (value instanceof Double) {
                                double dValue = (Double) value;
                                if (fromsheet instanceof HSSFSheet) {
                                    textValue = new HSSFRichTextString(String.valueOf(dValue));
                                }
                                if (fromsheet instanceof XSSFSheet) {
                                    textValue = new XSSFRichTextString(String.valueOf(dValue));
                                }
                                newCell.setCellValue(textValue);
                            } else if (value instanceof Long) {
                                long longValue = (Long) value;
                                newCell.setCellValue(longValue);
                            } else if (value instanceof String) {
                                newCell.setCellValue(value.toString());
                            } else if (value instanceof Boolean) {
                                if((boolean) value) {
                                    newCell.setCellValue("是");
                                }else {
                                    newCell.setCellValue("否");
                                }
                            }
                            break;
                        }
                        if(fromCellValue.split("\\.")[1].trim().equals("autoNumber")){
                            newCell.setCellValue(j);
                        }
                    }
                }
                iRow++;
            }
        }
        if (rs != null) {
            return;
        }
    }

    /**
     * 
     * @param fromsheet
     * @param firstrow
     * @param lastrow
     * @param data
     *            map或者javaBean或者ResultSet
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    private static void paddingExcel(Sheet fromsheet, int firstrow, int lastrow, Object data) throws Exception {
        if ((firstrow == -1) || (lastrow == -1) || lastrow < firstrow) {
            return;
        }
        Row fromRow = null;
        Cell fromCell = null;
        RichTextString textValue = null;

        if (data != null) {
            if (data instanceof ResultSet) {
                return;
            } else {
                Map map = null;
                if (data instanceof Map) {
                    map = (Map) data;
                } else {
                    map = FormatUtil.javaBeanToMap(data);
                }
                for (int i = firstrow; i < lastrow; i++) {
                    fromRow = fromsheet.getRow(i);
                    for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {
                        fromCell = fromRow.getCell(j);
                        if (fromCell == null) {
                            continue;
                        }
                        String fromCellValue = fromCell.getStringCellValue();
                        if (Strings.isNullOrEmpty(fromCellValue) || (fromCellValue.split("\\.").length < 2)) {
                            continue;
                        }
                        if ("data.start".equalsIgnoreCase(fromCellValue.trim())) {
                            return;
                        }
                        for (Map.Entry entry : map.entrySet()) {
                            Object value = entry.getValue();
                            if (entry.getKey().equalsIgnoreCase(fromCellValue.split("\\.")[1].trim())) {
                                if (value instanceof Date) {
                                    Date date = (Date) value;
                                    Calendar cDate = Calendar.getInstance();
                                    cDate.setTime(date);
                                    int hh = cDate.get(Calendar.HOUR_OF_DAY); // 获取当前小时
                                    int mm = cDate.get(Calendar.MINUTE); // 获取当前分钟
                                    int ss = cDate.get(Calendar.SECOND); // 获取当前秒
                                    if (hh == 0 && mm == 0 && ss == 0) {
                                        fromCell.setCellValue(DateUtil.dateToString(date, "yyyy-MM-dd"));
                                    } else {
                                        fromCell.setCellValue(DateUtil.dateToString(date, "yyyy-MM-dd HH:mm:ss"));
                                    }
                                }
                                if (value instanceof Integer) {
                                    int intValue = (Integer) value;
                                    fromCell.setCellValue(intValue);
                                } else if (value instanceof Float) {
                                    float fValue = (Float) value;
                                    if (fromsheet instanceof HSSFSheet) {
                                        textValue = new HSSFRichTextString(String.valueOf(fValue));
                                    }
                                    if (fromsheet instanceof XSSFSheet) {
                                        textValue = new XSSFRichTextString(String.valueOf(fValue));
                                    }
                                    fromCell.setCellValue(textValue);
                                } else if (value instanceof Double) {
                                    double dValue = (Double) value;
                                    if (fromsheet instanceof HSSFSheet) {
                                        textValue = new HSSFRichTextString(String.valueOf(dValue));
                                    }
                                    if (fromsheet instanceof XSSFSheet) {
                                        textValue = new XSSFRichTextString(String.valueOf(dValue));
                                    }
                                    fromCell.setCellValue(textValue);
                                } else if (value instanceof Long) {
                                    long longValue = (Long) value;
                                    fromCell.setCellValue(longValue);
                                } else if (value instanceof String) {
                                    fromCell.setCellValue(value.toString());
                                } else if (value instanceof Boolean) {
                                    if((boolean) value) {
                                        fromCell.setCellValue("是");
                                    }else {
                                        fromCell.setCellValue("否");
                                    }
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }
    }

    private static Workbook create(InputStream in) throws Exception {
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(in)) {
            return new HSSFWorkbook(in);
        }
        if (POIXMLDocument.hasOOXMLHeader(in)) {
            return new XSSFWorkbook(OPCPackage.open(in));
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");

    }

    private static void removeRow(Sheet sheet, int rowIndex) {
        int lastRowNum = sheet.getLastRowNum();
        if (rowIndex >= 0 && rowIndex < lastRowNum) {
            sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
        }
        if (rowIndex == lastRowNum) {
            Row removingRow = sheet.getRow(rowIndex);
            if (removingRow != null) {
                sheet.removeRow(removingRow);
            }
        }
    }
}