ExcelUtil - genscript-lz/manufacturing GitHub Wiki

package com.genscript.gsscm.common.util;

import java.io.BufferedReader; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.LinkedHashMap; import java.util.List; import java.util.Map;

import org.apache.commons.io.FileUtils; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; 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 org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Service;

/**

  • @description: 解析Excel文件工具类

  • @author: Golf

  • @createDate: 2010/7/25 4:42 PM

  • Modify by Duanjunwei 2016/09/05修复疑似文件句柄未关闭导致内存泄漏,同时重构了方法，去掉了Service的本地变量（可能的线程安全问题） */ @Service public class ExcelUtil {

    private final static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public boolean isExcel2003(String fileName) { boolean isExcel2003 = true; if (fileName.matches("^.+\.(?i)(xlsx)$")) { isExcel2003 = false; } return isExcel2003; }

    /**

    • 创建excel文档，

    • @param keys list中map的key数组集合

    • @param columnNames excel的列名

    • */ public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) { // 创建excel工作簿 Workbook wb = new HSSFWorkbook(); // 创建第一个sheet（页），并命名 Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。 for (int i = 0; i < keys.length; i++) { sheet.setColumnWidth((short) i, (short) (35.7 * 150)); }

      // 创建第一行 Row row = sheet.createRow((short) 0);

      // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle();

      // 创建两种字体 Font f = wb.createFont(); Font f2 = wb.createFont();

      // 创建第一种字体样式（用于列名） f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD);

      // 创建第二种字体样式（用于值） f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex());

      // Font f3=wb.createFont(); // f3.setFontHeightInPoints((short) 10); // f3.setColor(IndexedColors.RED.getIndex());

      // 设置第一种单元格的样式（用于列名） cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER);

      // 设置第二种单元格的样式（用于值） cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //设置列名 for (int i = 0; i < columnNames.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //设置每行每列的值 for (short i = 1; i < list.size(); i++) { // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的 // 创建一行，在页sheet上 Row row1 = sheet.createRow((short) i); // 在row行上创建一个方格 for (short j = 0; j < keys.length; j++) { Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; }

    public List<ArrayList> read(File file, String fileName) { List<ArrayList> dataLst = new ArrayList<ArrayList>();

    if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
    return dataLst;
}

boolean isExcel2003 = isExcel2003(fileName);

if (file == null || !file.exists()) {
    return dataLst;
}

FileInputStream fin = null;
try {
    fin = new FileInputStream(file);
    dataLst = read(fin, isExcel2003);
} catch (Exception ex) {
    logger.error("Read excel file thru file input stream exception.", ex);
} finally {
    if (fin != null) {
        try {
            fin.close();
        } catch (IOException e) {
            logger.error("Close input stream failed.", e);
        }
    }
}
return dataLst;

    }

    /**

    • 重写read方法，对于第三方工具生成的Excel读取异常时以文件流的形式读取文件内容

    • @author Zhang Yong

    • add date 2011-11-25 */ public List<ArrayList> readTool(File file, String fileName) { List<ArrayList> dataLst = new ArrayList<ArrayList>(); if (fileName == null || !fileName.matches("^.+\.(?i)((xls)|(xlsx))$")) { return dataLst; } boolean isExcel2003 = isExcel2003(fileName); if (file == null || !file.exists()) { return dataLst; }

      FileInputStream fin = null; try { fin = new FileInputStream(file); dataLst = readTool(fin, isExcel2003); } catch (Exception ex) { logger.error("Read excel file thru file input stream exception.", ex); //对于第三方工具生成的Excel读取异常时以文件流的形式读取文件内容 dataLst = readToolByStream(file); } finally { if (fin != null) { try { fin.close(); } catch (IOException e) { logger.error("Close input stream failed.", e); } } }

      return dataLst; }

    /**

    • 对于第三方工具生成的Excel读取异常时以文件流的形式读取文件内容

    • @param file

    • @param fileName

    • @return */ private List<ArrayList> readToolByStream(File file) { List<ArrayList> dataLst = new ArrayList<ArrayList>();

      //对于第三方工具生成的Excel读取异常时以文件流的形式读取文件内容 BufferedReader br = null; InputStreamReader reader = null; FileInputStream inputStream = null; try { inputStream = new FileInputStream(file); reader = new InputStreamReader(inputStream); br = new BufferedReader(reader);

       String data = null;
 ArrayList<String> list = null;
 while ((data = br.readLine()) != null) {
     String[] datas = data.split("\t");
     list = new ArrayList<String>();
     for (String dataStr : datas) {
         list.add(dataStr);
     }
     dataLst.add(list);
 }

      } catch (FileNotFoundException e) { logger.error("File is not exists", e); } catch (IOException e) { logger.error("IO exception", e); } finally { try { //关闭文件句柄 if (br != null) { br.close(); } if (reader != null) { reader.close(); } if (inputStream != null) { inputStream.close(); } } catch (IOException e) { logger.error("Close file reader stream failed.", e); } }

      return dataLst; }

    public Map<String, List<ArrayList>> readAllExcelSheetTool(File file, String fileName) { Map<String, List<ArrayList>> dataLst = new LinkedHashMap<String, List<ArrayList>>(); if (fileName == null || !fileName.matches("^.+\.(?i)((xls)|(xlsx))$")) { return dataLst; } boolean isExcel2003 = isExcel2003(fileName); if (file == null || !file.exists()) { return dataLst; }

    FileInputStream inputStream = null;
try {
    inputStream = new FileInputStream(file);
    Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
    dataLst = readAllSheet(wb);
} catch (Exception ex) {
    logger.error("Read excel file thru file input stream exception.", ex);
} finally {
    if (inputStream != null) {
        try {
            inputStream.close();
        } catch (IOException e) {
            logger.error("Close input stream failed.", e);
        }
    }
}
return dataLst;

    }

    /**

    • 处理批量Excel文件，重写read方法，对于第三方工具生成的Excel读取异常时以文件流的形式读取文件内容

    • @author Zhang Yong

    • add date 2011-11-25 */ public List<ArrayList> readTool(List fileList, List fileNameList) { List<ArrayList> dataLst = new ArrayList<ArrayList>(); int i = 0; for (File file : fileList) { String fileName = fileNameList.get(i); i++;

       List<ArrayList<String>> tmpDataLst = this.readTool(file, fileName);
 if (tmpDataLst != null && tmpDataLst.size() > 0) {
     dataLst.addAll(tmpDataLst);
 }

      }

      return dataLst; }

    /**

    • 重写read方法，对于第三方工具生成的Txt读取异常时以文件流的形式读取文件内容
    • @author fang quan
    • add date 2011-11-25 */ public List<String[]> readTxtTool(File file, String fileName) { List<String[]> dataLst = new ArrayList<String[]>(); if (fileName == null) { return dataLst; } if (file == null || !file.exists()) { return dataLst; } try { @SuppressWarnings("rawtypes") List l = FileUtils.readLines(file, "utf-8"); for (int i = 0; i < l.size(); i++) { String rowData = l.get(i).toString().trim(); //拿到了每一行的数据了 //这个时候做个分割 String[] sl = rowData.split("\t"); if (!"".equals(sl[0])) { dataLst.add(sl); } } } catch (Exception ex) { logger.error("Read txt file exception", ex); } return dataLst; }

    public List<ArrayList> read(String fileName) { List<ArrayList> dataLst = new ArrayList<ArrayList>();

    if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
    return dataLst;
}

boolean isExcel2003 = isExcel2003(fileName);

File file = new File(fileName);
if (file == null || !file.exists()) {
    return dataLst;
}

FileInputStream fin = null;
try {
    fin = new FileInputStream(file);
    dataLst = read(fin, isExcel2003);
} catch (Exception ex) {
    logger.error("Read excel file thru file input stream exception.", ex);
} finally {
    if (fin != null) {
        try {
            fin.close();
        } catch (IOException ioex) {
            logger.error("Close input stream failed.", ioex);
        }
    }
}

return dataLst;

    }

    public List<ArrayList> read(InputStream inputStream, boolean isExcel2003) { List<ArrayList> dataLst = null; try {

        Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
    //            Workbook wb =   new HSSFWorkbook(inputStream) ;
    dataLst = read(wb);
} catch (IOException e) {
    logger.error("Read excel file thru file input stream exception.", e);
} finally {
    if (inputStream != null) {
        try {
            inputStream.close();
        } catch (IOException ex) {
            logger.error("Close input stream failed.", ex);
        }
    }
}
return dataLst;

    }

    /**

    • 重写read方法，遇异常时抛出异常
    • @author Zhang Yong
    • add date 2011-11-25
    • @param inputStream
    • @param isExcel2003
    • @return
    • @throws IOException */ public List<ArrayList> readTool(InputStream inputStream, boolean isExcel2003) throws IOException { List<ArrayList> dataLst = null; Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream); dataLst = read(wb); return dataLst; }

    public List<ArrayList> read_new(InputStream inputStream) { List<ArrayList> dataLst = null; try { Workbook wb = new HSSFWorkbook(inputStream); dataLst = read(wb); } catch (IOException e) { logger.error("Read excel file thru file input stream exception.", e); } finally { if (inputStream != null) { try { inputStream.close(); } catch (IOException ex) { logger.error("Close input stream failed.", ex); } } }

    return dataLst;

    }

    private Map<String, List<ArrayList>> readAllSheet(Workbook wb) { Map<String, List<ArrayList>> ret = new LinkedHashMap<String, List<ArrayList>>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); ret.put(sheet.getSheetName(), anlyisSheet(sheet, "yyyy-MM-dd HH:mm:ss")); } return ret; }

    public String getCellValue(Cell cell, String dateFormat) { String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat(dateFormat); cellValue = formater.format(d); } else if (cell.getNumericCellValue() < 1) { cellValue = cell.getNumericCellValue() + ""; } else { cellValue = getRightStr(cell.getNumericCellValue() + ""); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: try { cellValue = getRightStr(cell.getNumericCellValue() + ""); } catch (Exception e) { cellValue = cell.getCellFormula(); } break; case Cell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue() + ""; break; default: break; } } return cellValue; }

    private List<ArrayList> anlyisSheet(Sheet sheet, String... param) { List<ArrayList> dataLst = new ArrayList<ArrayList>(); int totalRows = sheet.getPhysicalNumberOfRows(); /if (this.totalRows >= 1 && sheet.getRow(0) != null) { this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); }/ String dateFormat = "yyyy-MM-dd"; if (param != null) { if (param.length > 0) { dateFormat = param[0]; } }

    for (int r = 0; r < totalRows; r++) {
    Row row = sheet.getRow(r);
    if (row == null) {
        continue;
    }

    ArrayList<String> rowLst = new ArrayList<String>();
    int cells = row.getLastCellNum();
    for (short c = 0; c < cells; c++) {
        Cell cell = row.getCell(c);
        String cellValue = "";
        if (cell == null) {
            rowLst.add(cellValue);
            continue;
        }

        if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date d = cell.getDateCellValue();
                DateFormat formater = new SimpleDateFormat(dateFormat);
                cellValue = formater.format(d);
                // 						cellValue = getRightStr(cell.getDateCellValue() + "");
            } else if (cell.getNumericCellValue() < 1) {
                cellValue = cell.getNumericCellValue() + "";
            } else {
                cellValue = getRightStr(cell.getNumericCellValue() + "");
            }
        } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
            cellValue = cell.getStringCellValue();
        } else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
            //这个方法为得到Excel表格的计算公式
            //                    cellValue = cell.getCellFormula();
            try {
                cellValue = getRightStr(cell.getNumericCellValue() + "");
            } catch (Exception e) {
                cellValue = cell.getCellFormula();
            }
        } else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
            cellValue = cell.getBooleanCellValue() + "";
        } else {
        }
        rowLst.add(cellValue);
    }
    dataLst.add(rowLst);

}
return dataLst;

    }

    public List<ArrayList> read(Workbook wb) { Sheet sheet = wb.getSheetAt(0); List<ArrayList> dataLst = anlyisSheet(sheet); return dataLst; }

    private String getRightStr(String sNum) { DecimalFormat decimalFormat = new DecimalFormat("#.000000"); String resultStr = decimalFormat.format(new Double(sNum)); if (resultStr.matches("^[-+]?\d+\.[0]+$")) { resultStr = resultStr.substring(0, resultStr.indexOf(".")); } return resultStr; } }

⚠️ **GitHub.com Fallback** ⚠️