sunny123456

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
java中解析上传的excel文件（xls,xlsx）

java中解析上传的excel文件（xls,xlsx）

一、在maven库中导入相关依赖

其中poi是解析xls格式的，poi-ooxml是解析xlsx格式的

  1. <!-- POI,excel解析相关 -->
  2. <dependency>
  3. 	<groupId>org.apache.poi</groupId>
  4. 	<artifactId>poi</artifactId>
  5. 	<version>3.17</version>
  6. </dependency>
  7. <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
  8. <dependency>
  9. 	<groupId>org.apache.poi</groupId>
  10. 	<artifactId>poi-ooxml</artifactId>
  11. 	<version>3.17</version>
  12. </dependency>
  13. <dependency>
  14.     <groupId>net.sf.json-lib</groupId>
  15.     <artifactId>json-lib</artifactId>
  16.     <version>2.4</version>
  17.     <classifier>jdk15</classifier>
  18. </dependency>

 

二、解析代码

  1. import java.io.File;
  2. import java.io.FileInputStream;
  3. import java.io.FileNotFoundException;
  4. import java.io.IOException;
  5. import java.text.DateFormat;
  6. import java.text.SimpleDateFormat;
  7. import java.util.Date;
  8. import java.util.HashMap;
  9. import java.util.Map;
  10.  
  11.  
  12. import org.apache.poi.hssf.usermodel.HSSFDateUtil;
  13. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  14. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  15. import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  16. import org.apache.poi.ss.usermodel.Cell;
  17. import org.apache.poi.ss.usermodel.Row;
  18. import org.apache.poi.ss.usermodel.Sheet;
  19. import org.apache.poi.ss.usermodel.Workbook;
  20. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  21.  
  22.  
  23. import net.sf.json.JSONArray;
  24. import net.sf.json.JSONObject;
  25. import org.springframework.stereotype.Component;
  26. import sun.applet.Main;
  27.  
  28. @Component
  29. public class ExcelResolve {
  30.  
  31.     public final String XLSX = ".xlsx";
  32.     public final String XLS=".xls";
  33.  
  34.     /**
  35.      * 获取Excel文件（.xls和.xlsx都支持）
  36.      * @param file
  37.      * @return  解析excle后的Json数据
  38.      * @throws IOException
  39.      * @throws FileNotFoundException
  40.      * @throws InvalidFormatException
  41.      */
  42.     public JSONArray readExcel(File file) throws Exception{
  43.         int res = checkFile(file);
  44.  
  45.         if (res == 0) {
  46.             System.out.println("File not found");
  47.         }else if (res == 1) {
  48.             return readXLSX(file);
  49.         }else if (res == 2) {
  50.             return readXLS(file);
  51.         }
  52.         JSONArray array = new JSONArray();
  53.         return array;
  54.     }
  55.  
  56.     /**
  57.      * 判断File文件的类型
  58.      * @param file 传入的文件
  59.      * @return 0-文件为空，1-XLSX文件，2-XLS文件，3-其他文件
  60.      */
  61.     public int checkFile(File file){
  62.         if (file==null) {
  63.             return 0;
  64.         }
  65.         String flieName = file.getName();
  66.         if (flieName.endsWith(XLSX)) {
  67.             return 1;
  68.         }
  69.         if (flieName.endsWith(XLS)) {
  70.             return 2;
  71.         }
  72.         return 3;
  73.     }
  74.  
  75.     /**
  76.      * 读取XLSX文件
  77.      * @param file
  78.      * @return
  79.      * @throws IOException
  80.      * @throws InvalidFormatException
  81.      */
  82.     public JSONArray readXLSX(File file) throws InvalidFormatException, IOException{
  83.         Workbook book = new XSSFWorkbook(file);
  84.         Sheet sheet = book.getSheetAt(0);
  85.         return read(sheet, book);
  86.     }
  87.  
  88.     /**
  89.      * 读取XLS文件
  90.      * @param file
  91.      * @return
  92.      * @throws IOException
  93.      * @throws FileNotFoundException
  94.      */
  95.     public JSONArray readXLS(File file) throws FileNotFoundException, IOException{
  96.         POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
  97.         Workbook book = new HSSFWorkbook(poifsFileSystem);
  98.         Sheet sheet = book.getSheetAt(0);
  99.         return read(sheet, book);
  100.     }
  101.  
  102.     /**
  103.      * 解析数据
  104.      * @param sheet 表格sheet对象
  105.      * @param book 用于流关闭
  106.      * @return
  107.      * @throws IOException
  108.      */
  109.     public JSONArray read(Sheet sheet,Workbook book) throws IOException{
  110.         int rowStart = sheet.getFirstRowNum();	// 首行下标
  111.         int rowEnd = sheet.getLastRowNum();	// 尾行下标
  112.         // 如果首行与尾行相同，表明只有一行，直接返回空数组
  113.         if (rowStart == rowEnd) {
  114.             book.close();
  115.             return new JSONArray();
  116.         }
  117.         // 获取第一行JSON对象键
  118.         Row firstRow = sheet.getRow(rowStart);
  119.         int cellStart = firstRow.getFirstCellNum();
  120.         int cellEnd = firstRow.getLastCellNum();
  121.         Map<Integer, String> keyMap = new HashMap<Integer, String>();
  122.         for (int j = cellStart; j < cellEnd; j++) {
  123.             keyMap.put(j,getValue(firstRow.getCell(j), rowStart, j, book, true));
  124.         }
  125.         // 获取每行JSON对象的值
  126.         JSONArray array = new JSONArray();
  127.         for(int i = rowStart+1; i <= rowEnd ; i++) {
  128.             Row eachRow = sheet.getRow(i);
  129.             JSONObject obj = new JSONObject();
  130.             StringBuffer sb = new StringBuffer();
  131.             for (int k = cellStart; k < cellEnd; k++) {
  132.                 if (eachRow != null) {
  133.                     String val = getValue(eachRow.getCell(k), i, k, book, false);
  134.                     sb.append(val);		// 所有数据添加到里面，用于判断该行是否为空
  135.                     obj.put(keyMap.get(k),val);
  136.                 }
  137.             }
  138.             if (sb.toString().length() > 0) {
  139.                 array.add(obj);
  140.             }
  141.         }
  142.         book.close();
  143.         return array;
  144.     }
  145.  
  146.     /**
  147.      * 获取每个单元格的数据
  148.      * @param cell 单元格对象
  149.      * @param rowNum 第几行
  150.      * @param index 该行第几个
  151.      * @param book 主要用于关闭流
  152.      * @param isKey 是否为键：true-是，false-不是。 如果解析Json键，值为空时报错；如果不是Json键，值为空不报错
  153.      * @return
  154.      * @throws IOException
  155.      */
  156.     public String getValue(Cell cell,int rowNum,int index,Workbook book,boolean isKey) throws IOException{
  157.  
  158.         // 空白或空
  159.         if (cell == null || cell.getCellType()==Cell.CELL_TYPE_BLANK ) {
  160.             if (isKey) {
  161.                 book.close();
  162.                 throw new NullPointerException(String.format("the key on row %s index %s is null ", ++rowNum,++index));
  163.             }else{
  164.                 return "";
  165.             }
  166.         }
  167.  
  168.         // 0. 数字 类型
  169.         if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
  170.             if (HSSFDateUtil.isCellDateFormatted(cell)) {
  171.                 Date date = cell.getDateCellValue();
  172.                 DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  173.                 return df.format(date);
  174.             }
  175.             String val = cell.getNumericCellValue()+"";
  176.             val = val.toUpperCase();
  177.             if (val.contains("E")) {
  178.                 val = val.split("E")[0].replace(".", "");
  179.             }
  180.             return val;
  181.         }
  182.  
  183.         // 1. String类型
  184.         if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
  185.             String val = cell.getStringCellValue();
  186.             if (val == null || val.trim().length()==0) {
  187.                 if (book != null) {
  188.                     book.close();
  189.                 }
  190.                 return "";
  191.             }
  192.             return val.trim();
  193.         }
  194.  
  195.         // 2. 公式 CELL_TYPE_FORMULA
  196.         if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
  197.             return cell.getStringCellValue();
  198.         }
  199.  
  200.         // 4. 布尔值 CELL_TYPE_BOOLEAN
  201.         if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
  202.             return cell.getBooleanCellValue()+"";
  203.         }
  204.  
  205.         // 5.	错误 CELL_TYPE_ERROR
  206.         return "";
  207.     }
  208.  
  209.  
  210. }

 

三、单元测试

  这里点出调用封装好的组件时，要用try catch将异常抛出

  1. @RunWith(SpringRunner.class)
  2. @SpringBootTest
  3. public class ExcelTest extends TestCase {
  4.  
  5.     @Autowired
  6.     private ExcelResolve excelResolve;
  7.  
  8.     @Test
  9.     public void readTest() throws Exception{
  10.         File f1 = new File("D:/work/1.xlsx");
  11.  
  12.         try {
  13.             System.out.println(excelResolve.readExcel(f1));
  14.         } catch (Exception e) {
  15.             e.printStackTrace();
  16.         }
  17.     }
  18.  
  19.  
  20. }

 

四、测试结果

 

https://blog.csdn.net/huoji555/article/details/81697968
posted on 2022-03-23 19:52  sunny123456  阅读(1181)  评论(0)    收藏  举报