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();
        }
    }

}