Apache的POI工具类读写Excel操作

电玩女神 2022-05-11 02:36 188阅读 0赞

package com.shtel.cpc.data.poi.example;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    
    import java.io.*;
    import java.text.DecimalFormat;
    import java.util.*;
    
    
    public class EasyExcelSimpleExcel {
    
        public static void main(String[] args) throws IOException {
            String filePath = "C:\\XXXXXXXXX.xls";
            String[] titles = {"Name", "Age", "Class"};
            List<Map<String,String>> list = new ArrayList<>();
            Map m1= new HashMap();
            m1.put("Name","zhangan");m1.put("Age","11");m1.put("Class","一年级");
            Map m2= new HashMap();
            m2.put("Name","lisi");m2.put("Age","12");m2.put("Class","二年级");
            list.add(m1);
            list.add(m2);
            writeSimpleExcelByListMap(list,titles,filePath);
            readSimpleExcelByListMap(filePath, titles);
        }
    
        public static void readSimpleExcelByListMap(String filePath, String[] titles) throws IOException {
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new HSSFWorkbook(fis);
            List<Map<String, String>> list = new ArrayList();
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                    Row row = sheet.getRow(j);
                    Map<String, String> rowMap = new HashMap<String, String>();
                    String cellValue = "";
                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        Cell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            case NUMERIC:
                                cellValue = new DecimalFormat("0").format(cell.getNumericCellValue());
                                break;
                            case STRING:
                                cellValue = cell.getStringCellValue() + "";
                                break;
                            case BOOLEAN:
                                cellValue = cell.getBooleanCellValue() + "";
                                break;
                            case FORMULA:
                                cellValue = cell.getCellFormula() + "";
                                break;
                            default:
                                cellValue = "";
                                break;
                        }
                        if (k < titles.length) {
                            rowMap.put(titles[k], cellValue);
                        }
                    }
                    list.add(rowMap);
                }
            }
            System.out.print("=====" + list);
        }
    
    
        public static void writeSimpleExcelByListMap(List<Map<String,String>> list,String[] titles,String filePath) throws IOException {
            Workbook workbook = new HSSFWorkbook();
            for (int i = 0; i < 1; i++) {
                Sheet sheet = workbook.createSheet();
                for (int j = 0; j < list.size(); j++) {
                    Row row = sheet.createRow(j);
                    Map<String,String> rowMap = list.get(j);
                    for (int k = 0; k < titles.length; k++) {
                        Cell cell = row.createCell(k);
                        if (k == 0) {
                            cell.setCellValue(titles[k]);
                        }
                        if (k < titles.length) {
                            cell.setCellValue(rowMap.get(titles[k]));
                        }
                    }
                }
            }
            FileOutputStream out = new FileOutputStream(filePath);
            workbook.write(out);
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
    }