简单 ExcelUtils

ExcelUtils

import com.google.common.collect.Maps;

import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.small.smalldemo.util.character.DateUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtils {
    private static final String SUFFIX_XLS = ".xls";
    private static final String SUFFIX_XLSX = ".xlsx";
    private static CellStyle fontStyle;
    private static CellStyle fontStyle2;

    public ExcelUtils() {
    }

/**
     * 头部样式
     * @param workbook
     * @return
     */
    public static HSSFCellStyle genHeaderStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment( HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment( VerticalAlignment.CENTER);//垂直居中

        // 背景色
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_40_PERCENT.getIndex());
        style.setFillPattern( FillPatternType.SOLID_FOREGROUND);

        // 设置边框
        style.setBorderBottom( BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        // 自动换行
        style.setWrapText(false);

        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setColor( HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font.setBold(true);
        font.setFontName("微软雅黑");

        // 把字体 应用到当前样式
        style.setFont(font);
        return style;
    }

    /**
     * 设置表头
     * @param header
     * @param sheet
     * @param headerStyle
     * @return
     */
    public static void setHeader(String[] header, HSSFSheet sheet, HSSFCellStyle headerStyle){
        HSSFRow headrow = sheet.createRow(0);
        //遍历添加表头
        for (int i = 0; i < header.length; i++) {
            //创建一个单元格
            HSSFCell cell = headrow.createCell(i);
            cell.setCellStyle(headerStyle);
            //创建一个内容对象
            HSSFRichTextString text = new HSSFRichTextString(header[i]);
            //将内容对象的文字内容写入到单元格中
            cell.setCellValue(text);
        }
    }

    /**
     * 表格样式
     * @param workbook
     * @return
     */
    public static HSSFCellStyle  genDataStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        // 背景色
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        // 自动换行
        style.setWrapText(true);

        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 9);
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        font.setBold(false);
        font.setFontName("微软雅黑");

        // 把字体 应用到当前样式
        style.setFont(font);
        return style;
    }


    public static Map<String, List<List<Object>>> importExcel(InputStream is, String fileName) throws Exception {
        List<List<Object>> list = null;
        Workbook work = getWorkbook(is, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        } else {
            Sheet sheet = null;
            Row row = null;
            Cell cell = null;
            list = new ArrayList();
            Map<String, List<List<Object>>> result = Maps.newHashMap();

            for(int i = 0; i < work.getNumberOfSheets(); ++i) {
                sheet = work.getSheetAt(i);
                if (sheet != null) {
                    for(int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); ++j) {
                        row = sheet.getRow(j);
                        if (row != null && row.getFirstCellNum() != j) {
                            List<Object> li = new ArrayList();

                            for(int y = row.getFirstCellNum(); y < row.getLastCellNum(); ++y) {
                                cell = row.getCell(y);
                                if (cell != null) {
                                    li.add(getCellValue(cell));
                                }
                            }

                            list.add(li);
                        }
                    }

                    result.put(sheet.getSheetName(), list);
                }
            }

            return result;
        }
    }

    public static void exportExcel(String fileName, String title, List<String> head, List<Map<String, Object>> data, OutputStream os) throws Exception {
        Workbook workbook = getWorkbook(fileName);
        Sheet sheet = workbook.createSheet();
        createTableTitle(sheet, title, head.size());
        createTableHeader(sheet, head);
        createTableRows(sheet, data, head);
        workbook.write(os);
    }

    private static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            wb = new HSSFWorkbook(inStr);
        } else {
            if (!".xlsx".equals(fileType)) {
                throw new Exception("解析的文件格式有误！");
            }

            wb = new XSSFWorkbook(inStr);
        }

        return (Workbook)wb;
    }

    private static Workbook getWorkbook(String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            wb = new HSSFWorkbook();
        } else {
            if (!".xlsx".equals(fileType)) {
                throw new Exception("解析的文件格式有误！");
            }

            wb = new XSSFWorkbook();
        }

        return (Workbook)wb;
    }

    private static Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
        DecimalFormat df2 = new DecimalFormat("0.00");
        switch(cell.getCellType()) {
        case STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case NUMERIC:
            if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
            } else if ("yyyy\\-mm\\-dd;@".equals(cell.getCellStyle().getDataFormatString())) {
                value = sdf.format(cell.getDateCellValue());
            } else {
                value = df2.format(cell.getNumericCellValue());
            }
            break;
        case BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case BLANK:
            value = "";
        }

        return value;
    }

    private static String formatDate(Object rtn) {
        String value = "";
        if (rtn != null) {
            if (rtn instanceof Date) {
                value = DateUtil.getDate((Date)rtn);
            } else if (rtn instanceof BigDecimal) {
                NumberFormat nf = new DecimalFormat("#,##0.00");
                value = nf.format((BigDecimal)rtn).toString();
            } else if (rtn instanceof Integer && Integer.valueOf(rtn.toString()) < 0) {
                value = "--";
            } else {
                value = rtn.toString();
            }
        }

        return value;
    }

    public static void createFont(Workbook workbook) {
        fontStyle = workbook.createCellStyle();
        Font font1 = workbook.createFont();
        font1.setFontName("微软雅黑");
        font1.setFontHeightInPoints((short)10);
        font1.setColor(IndexedColors.BLACK.index);
        fontStyle.setFont(font1);
        fontStyle.setBorderBottom(BorderStyle.THIN);
        fontStyle.setBorderLeft(BorderStyle.THIN);
        fontStyle.setBorderTop(BorderStyle.THIN);
        fontStyle.setBorderRight(BorderStyle.THIN);
        fontStyle.setAlignment(HorizontalAlignment.CENTER);
        fontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        fontStyle2 = workbook.createCellStyle();
        Font font2 = workbook.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short)10);
        fontStyle2.setFont(font2);
        fontStyle2.setBorderBottom(BorderStyle.THIN);
        fontStyle2.setBorderLeft(BorderStyle.THIN);
        fontStyle2.setBorderTop(BorderStyle.THIN);
        fontStyle2.setBorderRight(BorderStyle.THIN);
        fontStyle2.setAlignment(HorizontalAlignment.CENTER);
    }

    public static final void createTableTitle(Sheet sheet, String title, Integer titleCount) {
        Row row = sheet.createRow(0);
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, titleCount - 1);
        sheet.addMergedRegion(range);
        Cell cell = row.createCell(0);
        cell.setCellValue(title);
    }

    public static final void createTableHeader(Sheet sheet, List<String> head) {
        int startIndex = 0;
        int endIndex = 0;
        Row row = sheet.createRow(1);
        if (CollectionUtils.isNotEmpty(head)) {
            for(int i = 0; i < head.size(); ++i) {
                Cell cell = row.createCell(i, CellType.STRING);
                cell.setCellStyle(fontStyle);
                cell.setCellValue((String)head.get(i));
            }
        }

    }

    public static void createTableRows(Sheet sheet, List<Map<String, Object>> data, List<String> head) throws Exception {
        int rowindex = data.size();
        int maxKey = 0;
        if (CollectionUtils.isNotEmpty(data)) {
            for(int i = 0; i < rowindex; ++i) {
                Row row = sheet.createRow(i + 2);
                Map<String, Object> mmp = (Map)data.get(i);

                for(int j = 0; j < head.size(); ++j) {
                    Cell cell = row.createCell(j, CellType.STRING);
                    cell.setCellStyle(fontStyle2);
                    cell.setCellValue(formatDate(mmp.get(head.get(j))));
                }
            }
        }

    }
}