当前位置:   article > 正文

Java 读取excel文件_java读取excel

java读取excel

导入:

先导入依赖:

  1. <!-- 文件上传 -->
  2. <dependency>
  3. <groupId>org.apache.httpcomponents</groupId>
  4. <artifactId>httpmime</artifactId>
  5. <version>4.5.7</version>
  6. </dependency>
  7. <!-- JSON -->
  8. <dependency>
  9. <groupId>com.alibaba</groupId>
  10. <artifactId>fastjson</artifactId>
  11. <version>1.2.41</version>
  12. </dependency>
  13. <!-- POI -->
  14. <dependency>
  15. <groupId>org.apache.poi</groupId>
  16. <artifactId>poi-ooxml</artifactId>
  17. <version>3.16</version>
  18. </dependency>

1、获取想要上传excel文件的路径

2、获得路径后,通过路径定位生成数据流,使用FileInputStream方法读取路径对应文件的原始字节流

  1. //根据路径生成 FileInputStream字节流
  2. FileInputStream inputStream = new FileInputStream(new File(fileName));

3、使用ExcelUtils工具类读取 字节流 + 路径。

  1. //通过ExcelUtils工具将Excel数据存入到list中,工具代码下面细讲,这一步读取Excel已经完成了,如果不想进行插入数据库操作,可以直接拿着list用啦。
  2. List<List<Object>> list = ExcelUtils.getListByExcel(inputStream,fileName);

4、ExcelUtils工具类对数据进行处理,因为表格有行和列,所以处理完数据使用二维集合List<List>对数据进行存储。

ExcelUtils类讲解:

  1. public class ExcelUtils {
  2. private final static String excel2003L =".xls"; //2003- 版本的excel
  3. private final static String excel2007U =".xlsx"; //2007版本
  4. /**
  5. * @Description:获取IO流中的数据,组装成List<List<Object>>对象
  6. * @param in,fileName
  7. * @return
  8. * @throws
  9. */
  10. public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception{
  11. List<List<Object>> list = null;
  12. //创建Excel工作薄
  13. Workbook work = getWorkbook(in,fileName);
  14. if(null == work){
  15. throw new Exception("创建Excel工作薄为空!");
  16. }
  17. Sheet sheet = null; //页数
  18. Row row = null; //行数
  19. Cell cell = null; //列数
  20. list = new ArrayList<List<Object>>();
  21. //遍历Excel中所有的sheet
  22. for (int i = 0; i < work.getNumberOfSheets(); i++) {
  23. sheet = work.getSheetAt(i);
  24. if(sheet==null){continue;}
  25. //遍历当前sheet中的所有行
  26. for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
  27. row = sheet.getRow(j);
  28. if(row==null){continue;}
  29. //遍历所有的列
  30. List<Object> li = new ArrayList<Object>();
  31. for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
  32. cell = row.getCell(y);
  33. li.add(getValue(cell));
  34. }
  35. list.add(li);
  36. }
  37. }
  38. return list;
  39. }
  40. /**
  41. * @Description:根据文件后缀,自适应上传文件的版本
  42. * @param inStr,fileName
  43. * @return
  44. * @throws Exception
  45. */
  46. public static Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
  47. Workbook wb = null;
  48. String fileType = fileName.substring(fileName.lastIndexOf("."));
  49. if(excel2003L.equals(fileType)){
  50. wb = new HSSFWorkbook(inStr); //2003-
  51. }else if(excel2007U.equals(fileType)){
  52. wb = new XSSFWorkbook(inStr); //2007+
  53. }else{
  54. throw new Exception("解析的文件格式有误!");
  55. }
  56. return wb;
  57. }
  58. /**
  59. * @Description:对表格中数值进行格式化
  60. * @param cell
  61. * @return
  62. */
  63. //解决excel类型问题,获得数值
  64. public static String getValue(Cell cell) {
  65. String value = "";
  66. if(null==cell){
  67. return value;
  68. }
  69. switch (cell.getCellType()) {
  70. //数值型
  71. case NUMERIC:
  72. if (DateUtil.isCellDateFormatted(cell)) {
  73. //如果是date类型则 ,获取该cell的date
  74. Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
  75. SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
  76. value = format.format(date);;
  77. }else {// 纯数字
  78. BigDecimal big=new BigDecimal(cell.getNumericCellValue());
  79. value = big.toString();
  80. //解决1234.0 去掉后面的.0
  81. if(null!=value&&!"".equals(value.trim())){
  82. String[] item = value.split("[.]");
  83. if(1<item.length&&"0".equals(item[1])){
  84. value=item[0];
  85. }
  86. }
  87. }
  88. break;
  89. //字符串类型
  90. case STRING:
  91. value = cell.getStringCellValue();
  92. break;
  93. // 公式类型
  94. case FORMULA:
  95. //读公式计算值
  96. value = String.valueOf(cell.getNumericCellValue());
  97. if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
  98. value = cell.getStringCellValue();
  99. }
  100. break;
  101. // 布尔类型
  102. case BOOLEAN:
  103. value = " "+ cell.getBooleanCellValue();
  104. break;
  105. default:
  106. value = cell.getStringCellValue();
  107. }
  108. if("null".endsWith(value.trim())){
  109. value="";
  110. }
  111. return value;
  112. }
  113. }

5、已经获取到了Excel表格中所有的数据,包含所有的行和列,都在List<List>中,这一步所有的数据获取完了

6、有了数据,要将数据存储到数据库。

7、需要创建数据库字段的实体类,使用@Excel注解将表格和数据库字段名一一对应

  1. @Data
  2. @TableName("t_student")//数据库名字
  3. public class Student{
  4. @TableId(type = IdType.AUTO)
  5. @Excel(name = "序号", cellType = Excel.ColumnType.NUMERIC)
  6. private Long id;
  7. @Excel(name = "学生ID")
  8. private Long id;
  9. @Excel(name = "名称")
  10. private String name;
  11. @Excel(name = "性别")
  12. private String sex;
  13. @Excel(name = "年龄")
  14. private String age;
  15. }

8、通过双重 for循环遍历List<List>,内层List为列,外层List为行,如下图将第一行数据单独拿出来,作为判断字段,如果实体类等@Excel注解属性和Excel表格第一行名字相同,对实体类进行set赋值

  1. //定义Excel第一行的属性
  2. List<Object> firstRows = null;
  3. //获取第一行属性 放入firstRows中。
  4. if(list != null && list.size() > 0){
  5. firstRows = list.get(0);
  6. }
  7. //遍历除第一行以外的Excel表格中的值
  8. for (int i = 1; i < list.size(); i++) {
  9. //rows是某一行,i = 1 为第二行, i = 2 为第三行
  10. List<Object> rows = list.get(i);
  11. //数据库数据的实体类
  12. Student student =new student();
  13. //遍历这一行所有的值
  14. for (int j = 0; j < rows.size(); j++){
  15. //某一行的某一列 j为列的坐标
  16. String cellVal = (String) rows.get(j);
  17. //对实体类进行赋值,使用setFieldValueByFieldName方法,下面详细讲代码
  18. TestExcel.setFieldValueByFieldName(questionBank, firstRows.get(j).toString().trim(), cellVal);
  19. }
  20. }
  21. // 使用spring插入到数据库
  22. questionBankService.save(questionBank);
  23. }

9、从第二行开始,每遍历完一行,将获得一个实体类对象,因为是结合数据库写的实体类,通过springboot可以直接插入数据库。

  1. public class TestExcel {
  2. /**
  3. * 用反射方法,获取列名,比较,为属性赋值
  4. * @param object
  5. * @param fieldName
  6. * @param val
  7. */
  8. public static void setFieldValueByFieldName(Object object, String fieldName, Object val) {
  9. try {
  10. //通过反射获取所有实体类所有定义的方法,object为数据库实体类
  11. Field[] fields = object.getClass().getDeclaredFields();
  12. //遍历fields
  13. for (int i = 0; i < fields.length; i++) {
  14. Field field = fields[i];
  15. //读取注释,如@Excel(name = "科目")
  16. Excel annotation = field.getAnnotation(Excel.class);
  17. //因为private类型,所有要设置允许
  18. field.setAccessible(true);
  19. //annotation.name() 注解里的属性,如果annotation没写注解,读取原始名字如subject
  20. if(annotation == null){
  21. if(fieldName.equals(field.getName())){
  22. if(field.getType() == Integer.class){
  23. field.set(object, Integer.valueOf(val.toString()));
  24. }else if(field.getType() == Long.class){
  25. field.set(object, Long.valueOf(val.toString()));
  26. }else {
  27. field.set(object, val);
  28. }
  29. return;
  30. }
  31. }else {//设置了注解,并且表格里的Excel字段值和注解的name值相同,则为相应字段赋值
  32. if(fieldName.equals(annotation.name())){
  33. //进行类型判断,因为实体类中变量类型不同。
  34. if(field.getType() == Integer.class){
  35. field.set(object, Integer.valueOf(val.toString()));
  36. }else if(field.getType() == Long.class){
  37. field.set(object, Long.valueOf(val.toString()));
  38. }else {
  39. field.set(object, val);
  40. }
  41. return;
  42. }
  43. }
  44. }
  45. }catch (Exception e){
  46. e.printStackTrace();
  47. }
  48. }
  49. }

六、通用读取excel工具类 

  1. package com.ssm;
  2. import java.io.File;
  3. import java.io.FileInputStream;
  4. import java.io.InputStream;
  5. import java.util.ArrayList;
  6. import java.util.HashMap;
  7. import java.util.List;
  8. import java.util.Map;
  9. import org.apache.poi.hssf.usermodel.HSSFCell;
  10. import org.apache.poi.hssf.usermodel.HSSFRow;
  11. import org.apache.poi.hssf.usermodel.HSSFSheet;
  12. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  13. import org.apache.poi.ss.usermodel.CellType;
  14. import org.apache.poi.xssf.usermodel.XSSFCell;
  15. import org.apache.poi.xssf.usermodel.XSSFRow;
  16. import org.apache.poi.xssf.usermodel.XSSFSheet;
  17. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  18. public class ReadExcel {
  19. public List<Map<String,String>> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
  20. List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
  21. String suffix = filename.substring(filename.lastIndexOf(".") + 1);
  22. if ("xls".equals(suffix)) {
  23. varList = readExcel2003(filepath, filename, startrow, startcol, sheetnum);
  24. } else if ("xlsx".equals(suffix)) {
  25. varList = readExcel2007(filepath, filename, startrow, startcol, sheetnum);
  26. } else {
  27. System.out.println("Only excel files with XLS or XLSX suffixes are allowed to be read!");
  28. return null;
  29. }
  30. return varList;
  31. }
  32. /**
  33. * 读取2003Excel
  34. *
  35. * @param filepath 文件路径
  36. * @param filename 文件名,包括扩展名
  37. * @param startrow 开始行号,索引从0开始
  38. * @param startcol 开始列号,索引从0开始
  39. * @param sheetnum 工作簿,索引从0开始
  40. * @return
  41. */
  42. public static List<Map<String,String>> readExcel2003(String filepath, String filename, int startrow, int startcol, int sheetnum) {
  43. List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
  44. try {
  45. File target = new File(filepath, filename);
  46. FileInputStream fis = new FileInputStream(target);
  47. HSSFWorkbook wb = new HSSFWorkbook(fis);
  48. fis.close();
  49. // sheet 从0开始
  50. HSSFSheet sheet = wb.getSheetAt(sheetnum);
  51. // 取得最后一行的行号
  52. int rowNum = sheet.getLastRowNum() + 1;
  53. HSSFRow rowTitle = sheet.getRow(0);
  54. // 标题行的最后一个单元格位置
  55. int cellTitleNum = rowTitle.getLastCellNum();
  56. String[] title = new String[cellTitleNum];
  57. for (int i = startcol; i < cellTitleNum; i++) {
  58. HSSFCell cell = rowTitle.getCell(Short.parseShort(i + ""));
  59. if (cell != null) {
  60. cell.setCellType(CellType.STRING);
  61. title[i] = cell.getStringCellValue();
  62. } else {
  63. title[i] = "";
  64. }
  65. }
  66. // 行循环开始
  67. for (int i = startrow + 1; i < rowNum; i++) {
  68. Map<String, String> varpd = new HashMap<String, String>();
  69. //
  70. HSSFRow row = sheet.getRow(i);
  71. // 列循环开始
  72. for (int j = startcol; j < cellTitleNum; j++) {
  73. HSSFCell cell = row.getCell(Short.parseShort(j + ""));
  74. String cellValue = "";
  75. if (cell != null) {
  76. // 把类型先设置为字符串类型
  77. cell.setCellType(CellType.STRING);
  78. cellValue = cell.getStringCellValue();
  79. }
  80. varpd.put(title[j], cellValue);
  81. }
  82. varList.add(varpd);
  83. }
  84. wb.close();
  85. } catch (Exception e) {
  86. System.out.println(e);
  87. }
  88. return varList;
  89. }
  90. /**
  91. * 读取2007Excel
  92. *
  93. * @param filepath 文件路径
  94. * @param filename 文件名,包括扩展名
  95. * @param startrow 开始行号,索引从0开始
  96. * @param startcol 开始列号,索引从0开始
  97. * @param sheetnum 工作簿,索引从0开始
  98. * @return
  99. */
  100. public List<Map<String,String>> readExcel2007(String filepath, String filename, int startrow, int startcol, int sheetnum) {
  101. List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
  102. try {
  103. File target = new File(filepath, filename);
  104. InputStream ins = new FileInputStream(target);
  105. XSSFWorkbook wb = new XSSFWorkbook(ins);
  106. ins.close();
  107. // 得到Excel工作表对象
  108. XSSFSheet sheet = wb.getSheetAt(sheetnum);
  109. // 取得最后一行的行号
  110. int rowNum = sheet.getLastRowNum() + 1;
  111. XSSFRow rowTitle = sheet.getRow(0);
  112. int cellTitleNum = rowTitle.getLastCellNum();
  113. String[] title = new String[cellTitleNum];
  114. for (int i = startcol; i < cellTitleNum; i++) {
  115. XSSFCell cell = rowTitle.getCell(Short.parseShort(i + ""));
  116. if (cell != null) {
  117. // 把类型先设置为字符串类型
  118. cell.setCellType(CellType.STRING);
  119. title[i] = cell.getStringCellValue();
  120. } else {
  121. title[i] = "";
  122. }
  123. }
  124. // 行循环开始
  125. for (int i = startrow + 1; i < rowNum; i++) {
  126. Map<String, String> varpd = new HashMap<String, String>();
  127. // 得到Excel工作表的行
  128. XSSFRow row = sheet.getRow(i);
  129. // 列循环开始
  130. for (int j = startcol; j < cellTitleNum; j++) {
  131. // 得到Excel工作表指定行的单元格
  132. XSSFCell cell = row.getCell(j);
  133. String cellValue = "";
  134. if (cell != null) {
  135. // 把类型先设置为字符串类型
  136. cell.setCellType(CellType.STRING);
  137. cellValue = cell.getStringCellValue();
  138. }
  139. varpd.put(title[j], cellValue);
  140. }
  141. varList.add(varpd);
  142. }
  143. wb.close();
  144. } catch (Exception e) {
  145. System.out.println(e);
  146. }
  147. return varList;
  148. }
  149. }

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/凡人多烦事01/article/detail/725999
推荐阅读
相关标签
  

闽ICP备14008679号