- import java.io.File;
- import java.io.FileInputStream;
- import java.io.InputStream;
- import opensource.jpinyin.PinyinFormat;
- import opensource.jpinyin.PinyinHelper;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- 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;
- /**
- *
- * @author lxf
- *
- */
- public class ExcelUtil {
- public static void main(String[] args) throws Exception {
- System.out.println(" 开始");
- String path="d:/test/1234.xls";
- String convertFileType = convertFileType(path);
- System.out.println(convertFileType);
- readExcel(path,convertFileType);
- System.out.println(" 结束");
- }
- /**
- * 读取Excel
- * @param filePath
- * 文件路径
- * @param fileType
- * 文件类型
- * @throws Exception
- */
- public static void readExcel(String filePath,String fileType) throws Exception {
- Workbook wb = null;
- InputStream fileInputStream = new FileInputStream(new File(filePath));
- if (fileType.equals("xls")) {
- wb = new HSSFWorkbook(fileInputStream);
- }
- else if(fileType.equals("xlsx"))
- {
- wb=new XSSFWorkbook(fileInputStream);
- }
- else
- {
- throw new IllegalArgumentException("无法识别: "+fileType+" 的文档。");
- }
- Sheet sheetAt = wb.getSheetAt(0);
- int firstRowNum = sheetAt.getFirstRowNum();
- int lastRowNum = sheetAt.getLastRowNum();
- System.out.println("一共" + (lastRowNum - firstRowNum) + "行记录!");
- for (int i = firstRowNum; i < lastRowNum - firstRowNum; i++) {
- Row row = sheetAt.getRow(i);
- short firstCellNum = row.getFirstCellNum();
- short lastCellNum = row.getLastCellNum();
- for (int j = firstCellNum; j < lastCellNum - firstCellNum; j++) {
- Cell cell = row.getCell(j);
- if (cell != null) {
- int cellType = cell.getCellType();
- String cellValue = null;
- switch (cellType) {
- case Cell.CELL_TYPE_STRING:
- cellValue = cell.getStringCellValue();
- System.out.println("解析到第" + i + "行,第" + j + "列记录!该单元格类型为:STRING ,值为:"+cellValue);
- break;
- case Cell.CELL_TYPE_NUMERIC:
- cellValue = cell.getNumericCellValue() + "";
- System.out.println("解析到第" + i + "行,第" + j + "列记录!该单元格类型为:NUMERIC ,值为:"+cellValue);
- break;
- case Cell.CELL_TYPE_FORMULA:
- cellValue = cell.getCellFormula();
- System.out.println("解析到第" + i + "行,第" + j + "列记录!该单元格类型为:FORMULA ,值为:"+cellValue);
- break;
- case Cell.CELL_TYPE_ERROR:
- cellValue = cell.getErrorCellValue() + "";
- System.out.println("解析到第" + i + "行,第" + j + "列记录!该单元格类型为:ERROR ,值为:"+cellValue);
- break;
- case Cell.CELL_TYPE_BOOLEAN:
- cellValue = cell.getBooleanCellValue() + "";
- System.out.println("解析到第" + i + "行,第" + j + "列记录!该单元格类型为:BOOLEAN ,值为:"+cellValue);
- break;
- case Cell.CELL_TYPE_BLANK:
- cellValue = cell.getRichStringCellValue() + "";
- System.out.println("解析到第" + i + "行,第" + j + "列记录!该单元格类型为:BLANK ,值为:"+cellValue);
- break;
- default:
- break;
- }
- if(i==firstRowNum){
- String convertToPinyinString = convertToPinyinString(cellValue);
- System.out.println("convertToPinyinString="+convertToPinyinString);
- }
- }
- }
- }
- }
- /**
- * 获取文件的扩展名
- * @param filepath
- * @return
- */
- public static String convertFileType(String filepath){
- int lastIndexOf = filepath.lastIndexOf(".");
- return filepath.substring(lastIndexOf+1);
- }
- /**
- * 将汉字转化成拼音
- * @param filepath
- * @return
- */
- public static String convertToPinyinString(String name){
- return PinyinHelper.convertToPinyinString(name, ",", PinyinFormat.WITH_TONE_MARK);
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/0912201514210.html
来源: http://www.codesnippet.cn/detail/0912201514210.html