POI导出excel的一个辅助工具类，poiexcel工具类,import org.a

POI导出excel的一个辅助工具类，poiexcel工具类,import org.a

import org.apache.poi.ss.usermodel.Sheet;import java.io.UnsupportedEncodingException;import java.net.URLEncoder;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.w3c.dom.Document;import com.hp.idm.business.excel.impl.ExcelFactory;import com.hp.idm.business.excel.impl.ExcelFactoryProduct;import com.hp.idm.business.excel.impl.ExportExcelToWeb;import com.hp.idm.exception.BusinessException;import com.hp.idm.log.IDMLogHelper;import java.util.HashMap;import java.util.LinkedHashMap;import java.util.Map;import org.apache.commons.lang.StringUtils;import org.apache.poi.ss.SpreadsheetVersion;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import static org.apache.poi.ss.util.CellReference.convertNumToColString;/** * @author dylan * */public class ExcelUtil {    public static String SEPERATOR = "__";    /**     * @param list     * @return     * @throws BusinessException     */    public static Workbook createExcel(Document list) throws BusinessException {        ExportExcelToWeb excel = new ExportExcelToWeb(list);        excel.transformToExcel();        return excel.getWorkBook();    }    public static Workbook createExcel(List<?> rows, String export) {        if (rows.size() == 0) {            Workbook wb = createWorkbook(export);            return createEmptySheet(wb);        }        ExcelFactoryProduct excel = ExcelFactory.getExcel(rows, export);        excel.transformToExcel();        return excel.getWorkBook();    }    public static Workbook createExcel(            List<LinkedHashMap<String, String>> rows,            HashMap<String, Object> paramMap) {        ExcelFactoryProduct excel = null;        if (rows.size() == 0) {            Workbook wb = createWorkbook((String) paramMap                    .get("selectedExcelVersion"));            return createEmptySheet(wb);        }        excel = ExcelFactory.getExcel(rows, paramMap);        excel.transformToExcel();        return excel.getWorkBook();    }    /**     * @param hssfWorkbook     * @return     */    private static Workbook createEmptySheet(Workbook workbook) {        workbook.createSheet();        workbook.setSheetName(0, "Empty Sheet");        return workbook;    }    /**     * Create an blank excel workbook based on excel version     * @param version     * @return     */    public static Workbook createWorkbook(String excelVersion) {        if ("2003".equals(excelVersion)) {            return new HSSFWorkbook();        } else if ("2007".equals(excelVersion)) {            return new XSSFWorkbook();        } else {            throw new IllegalStateException(                    "Only 2003 and 2007 excel exports defined.  Add another else if branch to add extra functionality.");        }    }    public static void setupMIMEHeader(HttpServletResponse response, String fileName, String excelVersion) {        response.setHeader("Expires", "-1");        String inlineName;        try {            inlineName = URLEncoder.encode(fileName, "UTF-8");        } catch (UnsupportedEncodingException ex) {            ex.printStackTrace();            inlineName = "unknown";        }        if ("2003".equals(excelVersion)) {            response.setContentType("application/vnd.ms-excel");            response.setHeader("Content-disposition", "inline;filename=" + inlineName + ".xls");        } else if ("2007".equals(excelVersion)) {            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");            response.setHeader("Content-disposition", "inline;filename=" + inlineName + ".xlsx");        } else {            throw new IllegalStateException(                    "Only 2003 and 2007 excel exports defined.  Add another else if branch to add extra functionality.");        }    }    public static void setupMIMEHeader(HttpServletResponse response, String fileName, Workbook wb) throws UnsupportedEncodingException {        String excelVersion = "";        if (wb instanceof HSSFWorkbook) {            excelVersion = "2003";        } else if (wb instanceof XSSFWorkbook) {            excelVersion = "2007";        }        setupMIMEHeader(response, fileName, excelVersion);    }    /**     *      * @param name     * @param num     * @return     */    public static String getNumberedSheetName(String name, int num) {        String name_suffix = (num > 0) ? SEPERATOR + num : "";        //check if the sheet name is valid        StringBuilder sheetName = new StringBuilder();        for (int i = 0; i < name.length(); i++) {            char ch = name.charAt(i);            switch (ch) {                case '/':                case '\\':                case '?':                case '*':                case ']':                case '[':                    continue;                default:                    if (sheetName.length() + name_suffix.length() < 31) {                        sheetName.append(ch);                    } else {                        break;                    }            }        }        return sheetName.append(name_suffix).toString();    }    /**     * How many columns excel support     * @param excelVersion     * @return     */    public static int getMaxColumns(String excelVersion) {        if ("2003".equals(excelVersion)) {            return SpreadsheetVersion.EXCEL97.getMaxColumns();        } else if ("2007".equals(excelVersion)) {            return SpreadsheetVersion.EXCEL2007.getMaxColumns();        } else {            throw new IllegalStateException(                    "Only 2003 and 2007 excel exports defined.  Add another else if branch to add extra functionality.");        }    }    /**     * How many rows excel support     * @param excelVersion     * @return     */    public static int getMaxRows(String excelVersion) {        if ("2003".equals(excelVersion)) {            return SpreadsheetVersion.EXCEL97.getMaxRows();        } else if ("2007".equals(excelVersion)) {            return SpreadsheetVersion.EXCEL2007.getMaxRows();        } else {            throw new IllegalStateException(                    "Only 2003 and 2007 excel exports defined.  Add another else if branch to add extra functionality.");        }    }    /**     * Create often used styles in excel export     * @param wb     * @return     */    public static Map<String, CellStyle> createDataStyles(Workbook wb) {        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();        CellStyle style = wb.createCellStyle();        Font font = wb.createFont();        font.setFontName("Arial");        font.setFontHeightInPoints((short) 9);        style = wb.createCellStyle();        // set the data style        style = wb.createCellStyle();        style.setFont(font);        //4, "#,##0.00"        style.setDataFormat((short) 4);        styles.put("data", style);        // set the gray style        style = wb.createCellStyle();        style = wb.createCellStyle();        style.setFont(font);        style.setDataFormat((short) 4);        style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());        style.setFillPattern(CellStyle.SOLID_FOREGROUND);        styles.put("grayData", style);        return styles;    }    /**     * Create often used styles in excel export     * @param wb     * @return     */    public static Map<String, CellStyle> createHeaderStyles(Workbook wb) {        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();        CellStyle style = wb.createCellStyle();        //cellDataFormat = wb.createDataFormat();        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());        style.setFillPattern(CellStyle.SOLID_FOREGROUND);        Font font = wb.createFont();        font.setFontName("Arial");        font.setFontHeightInPoints((short) 9);        // font.setColor(HSSFColor.ROSE.index);        style.setFont(font);        styles.put("head", style);        //set the yellow style        style = wb.createCellStyle();        style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());        style.setFillPattern(CellStyle.SOLID_FOREGROUND);        style.setFont(font);        styles.put("lightblueHead", style);        //blue gray style        style = wb.createCellStyle();        style.setFont(font);        style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());        style.setFillPattern(CellStyle.SOLID_FOREGROUND);        styles.put("bluegrayHead", style);        return styles;    }    /**Get double results from excel cell     * for Strings and empty cell return null     * @param cell     * @return     */    public static Double getDoubleCellValue(Cell cell) {        if (cell == null) {            return null;        }        try {            switch (cell.getCellType()) {                case Cell.CELL_TYPE_BOOLEAN:                    boolean val = cell.getBooleanCellValue();                    return val ? 1d : 0d;                case Cell.CELL_TYPE_NUMERIC:                    return cell.getNumericCellValue();                case Cell.CELL_TYPE_STRING:                    String strval = cell.getStringCellValue();                    strval = StringUtils.replace(strval, "$", "");                    strval = StringUtils.replace(strval, ",", "");                    return Double.parseDouble(strval);                default:                    //cell blank or other types                    return null;            }        } catch (Exception e) {            e.printStackTrace();            //log the sheet name, row and column            IDMLogHelper.error(53550027, cell.getSheet().getSheetName(),                    cell.getRow().getRowNum() + 1, convertNumToColString(cell.getColumnIndex()), cell.toString());        }        return null;    }    /**     * Excel column width is not set precise by autoSizeColumn,     * it may different on different platforms, i.e. there can be minor differences between     * text metrics calculated under Linux and under WinXP.     * Need make the column width larger to look better.     * @param st     * @param j     */    public static void widenColumn(Sheet st, int j){        //widen width use 3 char width        st.setColumnWidth(j, st.getColumnWidth(j)+3*256);    }}