使用POI对excel文件进行读取

使用POI对excel文件进行读取

• Excel转换为HTML表格（包括样式）
• Excel读取图片
• Excel读取附件

---

excel转换HTML

代码块

ReadExcel2Html 类 ：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class ReadExcel2Html {
   
   

    static String[] bordesr = { "border-top:", "border-right:",
            "border-bottom:", "border-left:" };
    static String[] borderStyles = { "solid ", "solid ", "solid ", "solid ",
            "solid ", "solid ", "solid ", "solid ", "solid ", "solid", "solid",
            "solid", "solid", "solid" };

    /**
     * 转换xls中的颜色代码
     * @param hc
     * @return
     */
    private static String convertToStardColor(HSSFColor hc) {

        StringBuffer sb = new StringBuffer("");
        if (hc != null) {
            if (HSSFColor.AUTOMATIC.index == hc.getIndex()) {
                return null;
            }
            sb.append("#");
            for (int i = 0; i < hc.getTriplet().length; i++) {
                sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i])));
            }
        }

        return sb.toString();
    }

    private static String fillWithZero(String str) {
        if (str != null && str.length() < 2) {
            return "0" + str;
        }
        return str;
    }

    /**
     * 获取xls里面的边框
     * @param palette
     * @param b
     * @param s
     * @param t
     * @return
     */
    private String getBorderStyle(HSSFPalette palette, int b, short s, short t) {

        if (s == 0)
            return bordesr[b] + borderStyles[s] + "#d0d7e5 1px;";
        ;
        String borderColorStr = convertToStardColor(palette.getColor(t));
        borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000"
                : borderColorStr;
        return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;";

    }

    /**
     * 获取xlsx里面的边框
     * @param b
     * @param s
     * @param t
     * @return
     */
    private String getBorderStyle(int b, short s, XSSFColor t) {
        if (s == 0) {
            return bordesr[b] + borderStyles[s] + "#d0d7e5 1px;";
        }
        String borderColorStr = ColorUtil.convertColorToHex(t);
        borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000"
                : borderColorStr;
        return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;";
    }

    /**
     * 转换单元格中上中下对齐
     * 
     * @param verticalAlignment
     * @return
     */
    private String convertVerticalAlignToHtml(short verticalAlignment) {
        String valign = "middle";
        switch (verticalAlignment) {
        case XSSFCellStyle.VERTICAL_BOTTOM:
            valign = "bottom";
            break;
        case XSSFCellStyle.VERTICAL_CENTER:
            valign = "center";
            break;
        case XSSFCellStyle.VERTICAL_TOP:
            valign = "top";
            break;
        default:
            break;
        }
        return valign;
    }

    /**
     * 转换单元格中左中右对齐
     * 
     * @param alignment
     * @return
     */
    private static String convertAlignToHtml(short alignment) {
        String align = "left";
        switch (alignment) {
        case XSSFCellStyle.ALIGN_LEFT:
            align = "left";
            break;
        case XSSFCellStyle.ALIGN_CENTER:
            align = "center";
            break;
        case XSSFCellStyle.ALIGN_RIGHT:
            align = "right";
            break;
        default:
            break;
        }
        return align;
    }

    /**
     * 空值样式
     * 
     * @return
     */
    private String getNullCellBorderStyle() {
        return "border: #d0d7e5 1px 1px 1px 1px;";
    }

    private Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) {
        Map<String, String> map0 = new HashMap<String, String>();
        Map<String, String> map1 = new HashMap<String, String>();
        int mergedNum = sheet.getNumMergedRegions();
        CellRangeAddress range = null;
        for (int i = 0; i < mergedNum; i++) {
            range = sheet.getMergedRegion(i);
            int topRow = range.getFirstRow();
            int topCol = range.getFirstColumn();
            int bottomRow = range.getLastRow();
            int bottomCol = range.getLastColumn();
            map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol);
            int tempRow = topRow;
            while (tempRow <= bottomRow) {
                int tempCol = topCol;
                while (tempCol <= bottomCol) {
                    map1.put(tempRow + "," + tempCol, "");
                    tempCol++;
                }
                tempRow++;
            }
            map1.remove(topRow + "," + topCol);
        }
        @SuppressWarnings("rawtypes")
        Map[] map = { map0, map1 };
        return map;
    }

    /**
     * 获取不同工作簿的函数式方法
     * 
     * @param wb
     * @return
     */
    public FormulaEvaluator getFormulaEvaluator(Workbook wb) {
        FormulaEvaluator evaluator = null;
        if (wb instanceof XSSFWorkbook) {
            XSSFWorkbook xWb = (XSSFWorkbook) wb;
            evaluator = new XSSFFormulaEvaluator(xWb);
        } else if (wb instanceof HSSFWorkbook) {
            HSSFWorkbook hWb = (HSSFWorkbook) wb;
            evaluator = new HSSFFormulaEvaluator(hWb);
        }
        return evaluator;
    }

    /**
     * 详细转换方法
     * 
     * @param wb
     * @return
     * @throws Exception
     */
    private List<String> getExcelInfo(Workbook wb) throws Exception {
        List<String> list=new ArrayList<String>();
        FormulaEvaluator evaluator = getFormulaEvaluator(wb);
        int sheets = wb.getNumberOfSheets();
        for (int i = 0; i < sheets; i++) {
            list.add(Sheet2Html(wb, evaluator, wb.getSheetAt(i)));
        }
        return list;
    }

    private String Sheet2Html(Workbook wb, FormulaEvaluator evaluator, Sheet sheet) {
        StringBuffer sb = new StringBuffer();
        int lastRowNum = sheet.getLastRowNum();
        Map<String, String> map[] = getRowSpanColSpanMap(sheet);
        sb.append("<table style='border-collapse:collapse;' >");
        Row row = null;
        Cell cell = null;
        for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
            row = sheet.getRow(rowNum);
            if (row == null) {
                sb.append("<tr><td style='" + getNullCellBorderStyle()
                        + "' > &nbsp;</td></tr>");
                continue;
            }
            sb.append("<tr>");
            int lastColNum = row.getLastCellNum();
            for (int colNum = 0; colNum < lastColNum; colNum++) {
                cell = row.getCell(colNum);
                if (cell == null) {
                    sb.append("<td style='" + getNullCellBorderStyle()
                            + ";white-space: nowrap;'>&nbsp;</td>");
                    continue;
                }

                String stringValue = null;
                // switch (cell.getCellType()) { //获取单元格的值
                // case HSSFCell.CELL_TYPE_BLANK:
                // stringValue = "";
                // break;
                // case HSSFCell.CELL_TYPE_BOOLEAN:
                // stringValue = String
                // .valueOf(cell.getBooleanCellValue());
                // break;
                // case HSSFCell.CELL_TYPE_ERROR:
                // stringValue = cell.getErrorCellString();
                // break;
                // case HSSFCell.CELL_TYPE_FORMULA:
                // stringValue = cell.getCTCell().getV();
                // break;
                // case HSSFCell.CELL_TYPE_NUMERIC:
                // stringValue = String
                // .valueOf(cell.getNumericCellValue());
                // break;
                // case HSSFCell.CELL_TYPE_STRING:
                // stringValue = cell.getStringCellValue();
                // break;
                // default:
                // break;
                // }

                // String stringValue = null;
                // long longVal;
                // double doubleVal;