package com.utils;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelUtil {

    static Logger log = Logger.getLogger(ExcelUtil.class);

    public static void writeExcel(File file,List<Map<String,String>> list,String sheetname){
        try {
            if (!file.exists()){
                 boolean flag=  file.createNewFile();
            }
            if (list.isEmpty()){
                return;
            }
            FileOutputStream outstream=new FileOutputStream(file);
            HSSFWorkbook workbook = new HSSFWorkbook();
            int rowIndex =0;
            Set<String> keySet =list.get(0).keySet();
            HSSFSheet sheet =workbook.createSheet(sheetname);
            HSSFRow row =sheet.createRow(rowIndex++);
            int col=0;
            for (String key :keySet){
                row.createCell(col++).setCellValue(key);
            }

            for (Map<String,String> map : list)
            {
                row = sheet.createRow(rowIndex);
                col=0;
                for (String key : keySet){
                    row.createCell(col++).setCellValue(String.valueOf(map.get(key) == null ?"":map.get(key)));
                }
                rowIndex++;
            }
            workbook.write(outstream);

            outstream.flush();
            outstream.close();
        } catch (Exception e) {
            log.error(e,e);
        }
    }


    public static List<Map<String,String>> readExcel(InputStream in){
        List<Map<String,String>> dataList = new ArrayList<Map<String,String>>();
        try{
            // 获得工作簿
            Workbook workbook = WorkbookFactory.create(in);
            // 获得工作表个数
            int sheetCount = workbook.getNumberOfSheets();
            // 遍历工作表
            for (int i = 0; i < sheetCount; i++)
            {
                Sheet sheet = workbook.getSheetAt(i);
                // 获得行数
                int rows = sheet.getLastRowNum() + 1;
                // 读取数据
                for (int rownum = 3; rownum < rows; rownum++)
                {
                    Map<String,String> rowMap = new LinkedHashMap<String, String>();
                    Row r = sheet.getRow(rownum);
                    if(r ==null) continue;
                    boolean flag = false; //行数据全部为空，不继续读
                    for (int col = 0; col < sheet.getRow(2).getLastCellNum(); col++)
                    {
                       Cell cell = r.getCell(col);
                       if(cell !=null){
                            String value = getCellValue(cell);
                            if (!"".equals(value.trim())){
                                flag=true;
                            }
                            rowMap.put(getCellValue(sheet.getRow(2).getCell(col)),value);
                       }else {
                           rowMap.put(getCellValue(sheet.getRow(2).getCell(col)),"");
                       }

                    }
                    if (flag){
                        dataList.add(rowMap);
                    }
                }
            }
        }catch (Exception e) {
            log.error(e,e);
        }
        return dataList;
    }

    public static String getCellValue(Cell cell){
        if(cell.getCellType() == CellType.NUMERIC){
            short format = cell.getCellStyle().getDataFormat();
            SimpleDateFormat sdf = null;
            if(format == 14 || format == 31 || format == 57 || format == 58){
                //日期
                sdf = new SimpleDateFormat("yyyy-MM-dd");
            }else if (format == 20 || format == 32) {
                //时间
                sdf = new SimpleDateFormat("HH:mm");
            }else { // 不是日期格式
                NumberFormat nf = NumberFormat.getInstance();
                String s = nf.format(cell.getNumericCellValue());
                if (s.indexOf(",") >= 0) {
                    s = s.replace(",", "");
                }
                return s;
            }
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            return sdf.format(date);
        } else if (cell.getCellType() == CellType.BOOLEAN){
            return cell.getBooleanCellValue()+"";
        }else {
            return cell.getStringCellValue();
        }

    }

    public static void writeExcel_template(File file,List<Map<String,String>> list,String sheetname,int rowIndex){
        try {
            if (list.isEmpty()){
                return;
            }
            FileOutputStream outstream=new FileOutputStream(file);
            HSSFWorkbook workbook = new HSSFWorkbook();
            //int rowIndex =0;
            Set<String> keySet =list.get(0).keySet();
            HSSFSheet sheet =workbook.createSheet(sheetname);
            HSSFRow row =sheet.createRow(rowIndex++);
            int col=0;
            for (String key :keySet){
                row.createCell(col++).setCellValue(key);
            }

            for (Map<String,String> map : list)
            {
                row = sheet.createRow(rowIndex);
                col=0;
                for (String key : keySet){
                    row.createCell(col++).setCellValue(String.valueOf(map.get(key) == null ?"":map.get(key)));
                }
                rowIndex++;
            }
            workbook.write(outstream);

            outstream.flush();
            outstream.close();
        } catch (Exception e) {
            log.error(e,e);
        }
    }
    public static void main(String[] args) throws  Exception {

        File file = new File("c:\\test\\图书注销清单.xls");
        List<Map<String,String>> list = new ArrayList();
        writeExcel_template(file, list,"test",2);
    }
 }
