1 packagecom.topband.sweepmachine.utils;2
3 importjava.io.File;4 importjava.io.FileInputStream;5 importjava.io.IOException;6 importjava.io.InputStream;7 importjava.text.DecimalFormat;8 importjava.util.ArrayList;9 importjava.util.List;10
11 importorg.apache.commons.io.FileUtils;12 importorg.apache.poi.hssf.usermodel.HSSFDateUtil;13 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;14 importorg.apache.poi.ss.usermodel.Cell;15 importorg.apache.poi.ss.usermodel.CellType;16 importorg.apache.poi.ss.usermodel.Row;17 importorg.apache.poi.ss.usermodel.Sheet;18 importorg.apache.poi.ss.usermodel.Workbook;19 importorg.apache.poi.ss.usermodel.WorkbookFactory;20 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;21 importorg.slf4j.Logger;22 importorg.slf4j.LoggerFactory;23
24 importcom.topband.cloud.common.utils.DateFormatUtil;25 importcom.topband.cloud.common.utils.StringUtil;26
27 public classReadExcelUtil {28
29 private Logger logger = LoggerFactory.getLogger(this.getClass());30 private static final String EXCEL_XLS = ".xls";31 private static final String EXCEL_XLSX = ".xlsx";32
33 /**
34 *读取excel数据35 *@throwsException36 *37 */
38 public static List> readExcelInfo(String url) throwsException{39 /*
40 * workbook:工作簿,就是整个Excel文档41 * sheet:工作表42 * row:行43 * cell:单元格44 */
45
46 //BufferedWriter bw = new BufferedWriter(new FileWriter(new File(url)));47 //支持excel2003、2007
48 File excelFile = new File(url);//创建excel文件对象
49 InputStream is = new FileInputStream(excelFile);//创建输入流对象
50 checkExcelVaild(excelFile);51 Workbook workbook =getWorkBook(is, excelFile);52 //Workbook workbook = WorkbookFactory.create(is);//同时支持2003、2007、201053 //获取Sheet数量
54 int sheetNum =workbook.getNumberOfSheets();55 //创建二维数组保存所有读取到的行列数据,外层存行数据,内层存单元格数据
56 List> dataList = new ArrayList>();57 //FormulaEvaluator formulaEvaluator = null;58 //遍历工作簿中的sheet,第一层循环所有sheet表
59 for(int index = 0;index
66 for(int rowIndex=0;rowIndex<=sheet.getLastRowNum();rowIndex++){67 Row row =sheet.getRow(rowIndex);68 //根据文件头可以控制从哪一行读取,在下面if中进行控制
69 if(row==null){70 continue;71 }72 //遍历每一行的每一列,第三层循环行中所有单元格
73 List cellList = new ArrayList();74 for(int cellIndex=0;cellIndex
84 }85 is.close();86 returndataList;87 }88 /**
89 *获取单元格的数据,暂时不支持公式90 *91 *92 */
93 public staticString getCellValue(Cell cell){94 CellType cellType =cell.getCellTypeEnum();95 String cellValue = "";96 if(cell==null || cell.toString().trim().equals("")){97 return null;98 }99
100 if(cellType==CellType.STRING){101 cellValue =cell.getStringCellValue().trim();102 return cellValue = StringUtil.isEmpty(cellValue)?"":cellValue;103 }104 if(cellType==CellType.NUMERIC){105 if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断日期类型
106 cellValue =DateFormatUtil.formatDurationYMD(cell.getDateCellValue().getTime());107 } else { //否
108 cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());109 }110 returncellValue;111 }112 if(cellType==CellType.BOOLEAN){113 cellValue =String.valueOf(cell.getBooleanCellValue());114 returncellValue;115 }116 return null;117
118 }119 /**
120 *判断excel的版本,并根据文件流数据获取workbook121 *@throwsIOException122 *123 */
124 public static Workbook getWorkBook(InputStream is,File file) throwsException{125
126 Workbook workbook = null;127 if(file.getName().endsWith(EXCEL_XLS)){128 workbook = newHSSFWorkbook(is);129 }else if(file.getName().endsWith(EXCEL_XLSX)){130 workbook = newXSSFWorkbook(is);131 }132
133 returnworkbook;134 }135 /**
136 *校验文件是否为excel137 *@throwsException138 *139 *140 */
141 public static void checkExcelVaild(File file) throwsException {142 String message = "该文件是EXCEL文件!";143 if(!file.exists()){144 message = "文件不存在!";145 throw newException(message);146 }147 if(!file.isFile()||((!file.getName().endsWith(EXCEL_XLS)&&!file.getName().endsWith(EXCEL_XLSX)))){148 System.out.println(file.isFile()+"==="+file.getName().endsWith(EXCEL_XLS)+"==="+file.getName().endsWith(EXCEL_XLSX));149 System.out.println(file.getName());150 message = "文件不是Excel";151 throw newException(message);152 }153 }154 /*public static void main(String[] args) throws Exception {155 readExcelInfo("g://批量新增设备表.xlsx");156 }*/
157 }