java poi 工具類(lèi)

package com.framework.common.util;


import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
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;


public class ExcelUtil {
    private static Logger logger = Logger.getLogger(CodeUtil.class);


    /**
     * 數(shù)據(jù)轉(zhuǎn)EXCEL
     * @return
     */
    @SuppressWarnings("unchecked")
    public static byte[] ExportExcel(String templateFile, int startIndex, String[] headerKey, Map<String, Object> exportObj) {
        byte[] bytes = null;
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            InputStream is = PropertiesUtil.getInputStream(templateFile);
            logger.info(is);
            Workbook workBook;
            try {
                workBook = new XSSFWorkbook(is);
            } catch (Exception ex) {
                workBook = new HSSFWorkbook(is);
            }
            Sheet sheet = workBook.getSheetAt(0);
            int cellCount = headerKey.length;
            int rowNum = sheet.getLastRowNum();
            Map<String, Object> header = exportObj.containsKey("header") ? (Map<String, Object>) exportObj.get("header") : null;
            List<Map<String, Object>> list = exportObj.containsKey("list") ? (List<Map<String, Object>>) exportObj.get("list") : null;
            Map<String, Object> footer = exportObj.containsKey("footer") ? (Map<String, Object>) exportObj.get("footer") : null;
            CellStyle cellStyle = getCellStyle(workBook);
            if (null != header && !header.isEmpty()) {
                for (int i = 0; i < startIndex - 1; i++) {
                    Row row = sheet.getRow(i);
                    for (int k = 0; k < cellCount; k++) {
                        if (null != row.getCell(k)) {
                            Cell cell = row.getCell(k);
                            String cellKey = cell.toString().trim();
                            if (header.containsKey(cellKey)) {
                                cell.setCellValue(getString(header.get(cellKey)));
                            }
                        }
                    }
                }
            }
            int dataNum = (null != list && list.size() > 0) ? list.size() : 0;
            if (dataNum > 0) {
                sheet.shiftRows(startIndex - 1, startIndex - 1 + rowNum, dataNum);
                for (int j = 0; j < dataNum; j++) {
                    Map<String, Object> value = list.get(j);
                    Row bodyRow = sheet.createRow(j + startIndex - 1);
                    for (int i = 0; i < cellCount; i++) {
                        if (value.containsKey(headerKey[i])) {
                            Cell cell = bodyRow.createCell(i);
                            cell.setCellStyle(cellStyle);
                            if (null != value.get(headerKey[i])) {
                                cell.setCellValue(getString(value.get(headerKey[i])));
                            }
                        }
                    }
                }
            }
            if (null != footer && !footer.isEmpty()) {
                for (int i = dataNum + startIndex - 1; i < dataNum + startIndex - 1 + rowNum; i++) {
                    Row row = sheet.getRow(i);
                    if (null != row) {
                        for (int k = 0; k < cellCount; k++) {
                            if (null != row.getCell(k)) {
                                Cell cell = row.getCell(k);
                                String cellKey = cell.toString().trim();
                                if (footer.containsKey(cellKey)) {
                                    cell.setCellValue(getString(footer.get(cellKey)));
                                }
                            }
                        }
                    }


                }
            }
            workBook.write(outputStream);
            bytes = outputStream.toByteArray();
        } catch (Exception e) {
            logger.error(e.getMessage(),e);
            e.printStackTrace();
        } finally {
            try {
                outputStream.flush();
                outputStream.close();
            } catch (Exception e) {
                logger.error(e.getMessage(),e);
                e.printStackTrace();
            }
        }
        return bytes;
    }
    private static CellStyle getCellStyle(Workbook wb) {
        CellStyle   cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下邊框
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左邊框
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上邊框
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右邊框
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 居中 
        return cellStyle;
    }
    private static String getString(Object value) {
        if (value != null&&StringUtil.isNotEmpty(value.toString())) {
            return value.toString();
        } else {
            return "";
        }
    }


    /**
     * 支持Excel 2007 XSSF/ 2003 HSSF EXCEL轉(zhuǎn)數(shù)據(jù)
     * 
     * @throws IOException
     */
    public static List<Map<String, Object>> ExcelToList(InputStream is, int startIndex, String[] headerKey) throws IOException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Workbook book = null;
        try {
            book = new XSSFWorkbook(is);
        } catch (Exception ex) {
            book = new HSSFWorkbook(is);
        }
        Sheet sheet = book.getSheetAt(0);
        int totalRows = sheet.getLastRowNum();
        startIndex = startIndex - 1;
        for (int i = startIndex; i <= totalRows; i++) {
            Row row = sheet.getRow(i);
            Map<String, Object> value = new HashMap<String, Object>();
            for (int k = 0; k < headerKey.length; k++) {
                if (null != row.getCell(k)) {
                    value.put(headerKey[k], getCellValue(row.getCell(k)));
                } else {
                    value.put(headerKey[k], "");
                }


            }
            list.add(value);
        }
        return list;
    }






    public static String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                try {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                        value = sdf.format(cell.getDateCellValue());
                    } else {
                        HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
                        value = dataFormatter.formatCellValue(cell);
                    }
                } catch (Exception e1) {
                    try {
                        value = cell.getNumericCellValue() + "";
                    } catch (Exception e2) {
                        value = String.valueOf(cell.getRichStringCellValue());
                    }
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                try {
                    value = String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    value = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            default:
                value = "";
                break;
            }
        }
        return value;
    }
}