package com.zanchina.check.common; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import lombok.extern.slf4j.Slf4j; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; 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; @Slf4j public class ExcelRead { private static ExcelRead inst = new ExcelRead(); private ExcelRead() { } public static ExcelRead getInst() { return inst; } public ExcelData parse(String fileName) { Workbook wb = null; try { InputStream is = new FileInputStream(fileName); String postfix = fileName.substring(fileName.lastIndexOf("."), fileName.length()); if (postfix.equals(".xls")) { // 针对 2003 Excel 文件 wb = new HSSFWorkbook(new POIFSFileSystem(is)); } else { // 针对2007 Excel 文件 wb = new XSSFWorkbook(is); } } catch (IOException e) { log.error(e.getMessage(), e); } Sheet sheet = wb.getSheetAt(0); int rowNum = sheet.getLastRowNum();// 得到总行数 Row row = sheet.getRow(0); int colNum = row.getPhysicalNumberOfCells(); String titles[] = readExcelTitle(row); List<String[]> list = new ArrayList<String[]>(); String[] content = null; // 正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i <= rowNum; i++) { int j = 0; row = sheet.getRow(i); content = new String[colNum]; do { content[j] = getCellFormatValue(row.getCell(j)).trim(); j++; } while (j < colNum); list.add(content); } ExcelData data = new ExcelData(); data.setDatas(list); data.setTitles(titles); return data; } public ExcelData parse(InputStream in, String fileName, boolean bool, Integer titleIndex) { Workbook wb = null; try { String postfix = fileName.substring(fileName.lastIndexOf("."), fileName.length()); if (postfix.equals(".xls")) { // 针对 2003 Excel 文件 wb = new HSSFWorkbook(new POIFSFileSystem(in)); } else { // 针对2007 Excel 文件 wb = new XSSFWorkbook(in); } } catch (IOException e) { log.error(e.getMessage(), e); } Sheet sheet = wb.getSheetAt(0); int rowNum = sheet.getLastRowNum();// 得到总行数 Row row = sheet.getRow(titleIndex); int colNum = row.getPhysicalNumberOfCells(); String titles[] = readExcelTitle(row); List<String[]> list = new ArrayList<String[]>(); String[] content = null; // 正文内容应该从第二行开始,第一行为表头的标题 //bool是否从标题行开始 if (bool) { for (int i = 0 + titleIndex; i <= rowNum; i++) { int j = 0; row = sheet.getRow(i); content = new String[colNum]; do { if (null != row.getCell(j)) { if (null != getCellFormatValue(row.getCell(j))) { content[j] = getCellFormatValue(row.getCell(j)); } } j++; } while (j < colNum); list.add(content); } } else { for (int i = 1 + titleIndex; i <= rowNum; i++) { int j = 0; row = sheet.getRow(i); content = new String[colNum]; do { content[j] = getCellFormatValue(row.getCell(j)); j++; } while (j < colNum); list.add(content); } } ExcelData data = new ExcelData(); data.setDatas(list); data.setTitles(titles); return data; } private String[] readExcelTitle(Row row) { int colNum = row.getPhysicalNumberOfCells();// 获取行的列数 String[] titles = new String[colNum]; for (int i = 0; i < titles.length; i++) { titles[i] = getCellFormatValue(row.getCell(i)); } return titles; } private String getCellFormatValue(Cell cell) { String cellvalue = ""; DecimalFormat df = new DecimalFormat("#"); if (cell != null) { // 判断当前Cell的Type switch (cell.getCellType()) { // 如果当前Cell的Type为NUMERIC case Cell.CELL_TYPE_NUMERIC: { // 判断当前的cell是否为Date if (HSSFDateUtil.isCellDateFormatted(cell)) { // 方法2:这样子的data格式是不带带时分秒的:2011-10-12 Date date = cell.getDateCellValue(); SimpleDateFormat sdf = new SimpleDateFormat(DateUtils.yyyyMMddHHmm1); cellvalue = sdf.format(date); } else { // 如果是纯数字取得当前Cell的数值 cellvalue = String.valueOf(cell.getNumericCellValue()); } break; } // 如果当前Cell的Type为STRIN case Cell.CELL_TYPE_STRING: // 取得当前的Cell字符串 cellvalue = cell.getRichStringCellValue().getString(); break; default: cellvalue = " "; } } return cellvalue; } }