poi excel util

ExcelUtil.java

package com.hp.idm.util;

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

 
}