POI導出excel的一個輔助工具類

jopen 9年前發布 | 2K 次閱讀 Java POI
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);
}



}</pre>
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！
本文地址：http://www.baiduhome.net/code/view/1444206787963
Java POI
POI導出excel的一個輔助工具類

相關代碼

相關文檔

相關經驗

目錄
