Excel文件读取写入（导入导出）工具类

一时失言乱红尘 2022-04-10 01:20 378阅读 0赞

读取得到的数据格式为Map<String, List<List>>  
List为某一行的数据集  
List<List> 为当前sheet工作簿的数据集  
Map<String, List<List>> 为所有工作簿的数据集，Map的键为工作簿的sheet的名称

依赖包：

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>4.0.1</version>
            </dependency>

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    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;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    
    /** * description: excel工具类 * * @author sanchar * @date 12/10/2018 17:10 * lastUpdateBy: sanchar * lastUpdateDate: 12/10/2018 */
    public class ExcelUtils { 
    
        private static final String EXCEL_XLS = ".xls";
        private static final String EXCEL_XLSX = ".xlsx";
    
        /** * description: 读取excel文件,默认文件格式为 .xlsx * * @param inputStream excel文件输入流 * @return 以sheet页签名为键，页签的数据为值，数据以行为list存入map，以列为list存入行list */
        public static Map<String, List<List<Object>>> readExcel(InputStream inputStream) { 
            return readExcel(EXCEL_XLSX, inputStream);
        }
    
        /** * description: 读取excel文件 * * @param fileType 文件格式 * @param inputStream excel文件输入流 * @return 以sheet页签名为键，页签的数据为值，数据以行为list存入map，以列为list存入行list */
        public static Map<String, List<List<Object>>> readExcel(String fileType, InputStream inputStream) { 
            return readExcel(fileType, inputStream, 1, (short) 1);
        }
    
        /** * description: 读取excel文件 * * @param fileType 文件格式 * @param inputStream excel文件输入流 * @param startRow 开始读取的第几行 * @param startColumn 开始读取的第几列 * @return 以sheet页签名为键，页签的数据为值，数据以行为list存入map，以列为list存入行list */
        public static Map<String, List<List<Object>>> readExcel(String fileType, InputStream inputStream,
                                                                int startRow, short startColumn) { 
            Map<String, List<List<Object>>> dataMap = new LinkedHashMap<>();
            int rowSize = 0;
            Workbook wb;
            Sheet st;
            Row row;
            Cell cell;
            try { 
                if (EXCEL_XLS.equalsIgnoreCase(fileType)) { 
                    wb = new HSSFWorkbook(inputStream);
                } else if (EXCEL_XLSX.equalsIgnoreCase(fileType)) { 
                    wb = new XSSFWorkbook(inputStream);
                } else { 
                    return dataMap;
                }
                for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { 
                    List<List<Object>> outerList = new ArrayList<>();
                    st = wb.getSheetAt(sheetIndex);
                    String sheetName = st.getSheetName();
                    for (int rowIndex = startRow - 1; rowIndex <= st.getLastRowNum(); rowIndex++) { 
                        List<Object> innerList = new ArrayList();
                        row = st.getRow(rowIndex);
                        if (row == null) { 
                            continue;
                        }
                        int tempRowSize = row.getLastCellNum() + 1;
                        if (tempRowSize > rowSize) { 
                            rowSize = tempRowSize;
                        }
                        boolean hasValue = false;
                        for (short columnIndex = (short) (startColumn - 1); columnIndex < row.getLastCellNum(); columnIndex++) { 
                            cell = row.getCell(columnIndex);
                            Object value = getFormatValue(cell);
                            if (columnIndex == startColumn && value == null) { 
                                break;
                            }
                            innerList.add(value);
                            hasValue = true;
                        }
                        if (hasValue) { 
                            outerList.add(innerList);
                        }
                    }
                    dataMap.put(sheetName, outerList);
                }
                inputStream.close();
            } catch (Exception e) { 
                e.printStackTrace();
            }
            return dataMap;
        }
    
    
        /** * description: 写excel文件 * * @param dataMap 写入的数据 * @return 文件的输出流 */
        public static Workbook writeExcel(Map<String, List<List<Object>>> dataMap) { 
            return writeExcel(dataMap, EXCEL_XLSX);
        }
    
    
        /** * description: 写excel文件 * * @param dataMap 写入的数据 * @param startSheet 从第几个sheet开始写入 * @param startRow 从第几行开始写入 * @param startColumn 从第几列开始写入 * @return 文件的输出流 */
        public static Workbook writeExcel(Map<String, List<List<Object>>> dataMap, int startSheet, int startRow, short startColumn) { 
            return writeExcel(dataMap, EXCEL_XLSX, startSheet, startRow, startColumn);
        }
    
        /** * description: 写excel文件 * * @param dataMap 写入的数据 * @param fileType 文件类型 * @return 文件的输出流 */
        public static Workbook writeExcel(Map<String, List<List<Object>>> dataMap, String fileType) { 
            return writeExcel(dataMap, fileType, 1, 1, (short) 1);
        }
    
        /** * description: 写excel文件 * * @param dataMap 写入的数据 * @param fileType 文件类型 * @param startSheet 从第几个sheet开始写入 * @param startRow 从第几行开始写入 * @param startColumn 从第几列开始写入 * @return 文件的输出流 */
        public static Workbook writeExcel(Map<String, List<List<Object>>> dataMap, String fileType,
                                          int startSheet, int startRow, short startColumn) { 
            Workbook wb = null;
            Sheet st;
            try { 
                if (EXCEL_XLS.endsWith(fileType)) { 
                    wb = new HSSFWorkbook();
                } else if (EXCEL_XLSX.endsWith(fileType)) { 
                    wb = new XSSFWorkbook();
                } else { 
                    wb = new XSSFWorkbook();
                }
                int emptySheet = startSheet;
                while (startSheet > 1) { 
                    wb.createSheet("Sheet" + (emptySheet - startSheet + 1));
                    startSheet--;
                }
                for (String name : dataMap.keySet()) { 
                    st = wb.createSheet(name);
                    List<List<Object>> dataList = dataMap.get(name);
                    for (int i = 0; i < dataList.size(); i++) { 
                        Row row = st.createRow(i + startRow - 1);
                        List<Object> valueList = dataList.get(i);
                        for (int j = 0; j < valueList.size(); j++) { 
                            // 自适应宽度
                            st.autoSizeColumn(j + startColumn - 1, true);
                            Cell cell = row.createCell(j + startColumn - 1);
                            Object value = valueList.get(j);
                            if (value instanceof Integer) { 
                                cell.setCellValue((Integer) value);
                            } else if (value instanceof String) { 
                                cell.setCellValue((String) value);
                            } else if (value instanceof Double) { 
                                cell.setCellValue((Double) value);
                            } else if (value instanceof Float) { 
                                cell.setCellValue((Float) value);
                            } else if (value instanceof Long) { 
                                cell.setCellValue((Long) value);
                            } else if (value instanceof Boolean) { 
                                cell.setCellValue((Boolean) value);
                            } else if (value instanceof Date) { 
                                cell.setCellValue((Date) value);
                            }
                        }
                    }
                }
            } catch (Exception e) { 
                e.printStackTrace();
            }
            return wb;
        }
    
        /** * description: 获取数据 * * @param cell 单元格 * @return 单元格的数据 */
        private static Object getFormatValue(Cell cell) { 
            Object value = null;
            if (cell != null) { 
                switch (cell.getCellType()) { 
                    case STRING:
                        value = rightTrim(cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) { 
                            value = cell.getDateCellValue();
                        } else { 
                            value = cell.getNumericCellValue();
                        }
                        break;
                    case FORMULA:
                        // 导入时如果为公式生成的数据则无值
                        if (!cell.getStringCellValue().equals("")) { 
                            value = cell.getStringCellValue();
                        } else { 
                            value = cell.getNumericCellValue() + "";
                        }
                        break;
                    case BLANK:
                        break;
                    case ERROR:
                        value = null;
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    default:
                        value = null;
                }
            }
            return value;
        }
    
        /** * 去掉字符串右边的空格 * * @param str 要处理的字符串 * @return 处理后的字符串 */
    
        public static String rightTrim(String str) { 
            if (str == null) { 
                return "";
            }
            int length = str.length();
            for (int i = length - 1; i >= 0; i--) { 
                if (str.charAt(i) != 0x20) { 
                    break;
                }
                length--;
            }
            return str.substring(0, length);
        }
    
        public static void main(String[] args) throws IOException { 
            File file1 = new File("C:\\Users\\97773\\Desktop\\test.xlsx");
            Map<String, List<List<Object>>> dataMap = readExcel(file1.getName().substring(file1.getName().lastIndexOf(".")),
                    new FileInputStream(file1.getAbsolutePath()), 2, (short) 2);
            File file = new File("C:\\Users\\97773\\Desktop\\test2.xlsx");
            if (!file.exists()) { 
                file.createNewFile();
            }
            OutputStream outputStream = new FileOutputStream(file);
            writeExcel(dataMap, 1, 2, (short) 2).write(outputStream);
            outputStream.close();
            System.out.println(dataMap);
        }
    }