精华内容
下载资源
问答
  • 使用poi读取excel文件

    万次阅读 2019-05-22 14:45:29
    注:读取的文件格式是xls,如果xlsx格式文件,可以自己通过excel文件右键→另存为,转换一下格式 导包 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId&...

    注:读取的文件格式是xls,如果xlsx格式文件,可以自己通过excel文件右键→另存为,转换一下格式
    导包

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    
    package com.jd.jrmserver.base.test;
    
    import org.apache.poi.hssf.usermodel.*;
    
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.util.HashSet;
    import java.util.Iterator;
    import java.util.Set;
    
    /**
     * 读取上篇中的xls文件的内容,并打印出来
     *
     * @author Administrator
     */
    public class ExcelTest {
    
        /**
         * 读取一个excel文件的内容
         *
         * @param args
         * @throws Exception
         */
        public static void main(String[] args) throws Exception {
            //extractor();
            readTable();
        }
    
    
        //通过对单元格遍历的形式来获取信息 ,这里要判断单元格的类型才可以取出值
        public static void readTable() throws Exception {
            InputStream ips = new FileInputStream("C:\\Users\\liubin52\\Desktop\\test.xls");
            HSSFWorkbook wb = new HSSFWorkbook(ips);
            HSSFSheet sheet = wb.getSheetAt(0);
            for (Iterator ite = sheet.rowIterator(); ite.hasNext(); ) {
                HSSFRow row = (HSSFRow) ite.next();
                System.out.println();
                for (Iterator itet = row.cellIterator(); itet.hasNext(); ) {
                    HSSFCell cell = (HSSFCell) itet.next();
                    switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            //得到Boolean对象的方法
                            System.out.print(cell.getBooleanCellValue() + " ");
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            //先看是否是日期格式
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                //读取日期格式
                                System.out.print(cell.getDateCellValue() + " ");
                            } else {
                                //读取数字
                                System.out.print(cell.getNumericCellValue() + " ");
                            }
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            //读取公式
                            System.out.print(cell.getCellFormula() + " ");
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            //读取String
                            System.out.print(cell.getRichStringCellValue().toString() + " ");
                            break;
                    }
                }
            }
        }
    
    }
    
    展开全文
  • 使用POI 读取Excel文件数据/*POI常用类说明 类名 说明 HSSFWorkbook Excel的文档对象 HSSFSheet Excel的表单 HSSFRow Excel的行 HSSFCell Excel的格子单元 HSSFFont

    使用POI 读取Excel文件数据

    /*POI常用类说明
         类名                说明
         HSSFWorkbook        Excel的文档对象
         HSSFSheet      Excel的表单
         HSSFRow                Excel的行
         HSSFCell       Excel的格子单元
         HSSFFont            Excel字体
         HSSFDataFormat      格子单元的日期格式
         HSSFHeader          Excel文档Sheet的页眉
         HSSFFooter          Excel文档Sheet的页脚
         HSSFCellStyle       格子单元样式
         HSSFDateUtil        日期
         HSSFPrintSetup      打印
         HSSFErrorConstants  错误信息表*/
    
        /**
         * 读取Excel文件数据插入数据库
         * @param filePath
         * @return
         *
         */
    
        public static String readExcel(String filePath){
            InputStream in = null;//读取刘文件
            HSSFWorkbook workbook = null;//Excel文件对象
            HSSFSheet sheet = null;//表单对象
            HSSFRow row = null;//行
            HSSFCell cell = null;//列
    
            try {
                filePath = System.getProperty("user.dir")+File.separator+"excelDownload"+File.separator+"test.xls";
                in = new FileInputStream(filePath);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
            try {
                 workbook = new HSSFWorkbook(in);
            } catch (IOException e) {
                e.printStackTrace();
            }
    
            sheet = workbook.getSheetAt(0);//获取第一个表单对象
            row = sheet.getRow(0);//获取第一个表单的第一行
            int rows = sheet.getPhysicalNumberOfRows();//获取总行数
            int columns = row.getPhysicalNumberOfCells();//获取第一行的列数
    
            //遍历行列
            for (int i=0;i<rows;i++){
                row = sheet.getRow(i);
                for (int j=0;j<columns;j++){
                    cell = row.getCell(j);
                    String cellValue = cell.getStringCellValue();
                    System.out.println("cellValue"+cellValue);
                }
            }
            return "读取完成";
        }
    展开全文
  • 使用POI对excel文件进行读取

    千次阅读 2018-01-31 11:09:32
    使用POI对excel文件进行读取 Excel转换为HTML表格(包括样式) Excel读取图片 Excel读取附件 使用POI对excel文件进行读取 excel转换HTML 代码块 ReadExcel2Html 类 ColorInfo 类 ColorUtil 类 OperaColor ...

    使用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;
                    // int intvalue;
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_NUMERIC: // 数值型
                        if (HSSFDateUtil.isCellDateFormatted(cell)) { // 如果是时间类型
                            SimpleDateFormat sdf = new SimpleDateFormat(
                                    "yyyy/MM/dd");
                            stringValue = sdf.format(cell.getDateCellValue());
                        } else { // 纯数字
                            double value = cell.getNumericCellValue();
                            CellStyle style1 = cell.getCellStyle();
                            DecimalFormat format = new DecimalFormat();
                            String temp = style1.getDataFormatString();
                            // 单元格设置成常规
                            if (temp.equals("General")) {
                                format.applyPattern("#");
                            }
                            stringValue = format.format(value);
                            // doubleVal = cell.getNumericCellValue();
                            // intvalue = (int) cell.getNumericCellValue();
                            // if(doubleVal == intvalue)
                            // stringValue = String.valueOf(intvalue);
                            // else
                            // // cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                            // // stringValue = cell.getStringCellValue();
                            // stringValue =
                            // String.valueOf(cell.getNumericCellValue());
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING: // 字符串型
                        stringValue = cell.getStringCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔
                        stringValue = " " + cell.getBooleanCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_BLANK: // 空值
                        stringValue = "";
                        break;
                    case HSSFCell.CELL_TYPE_ERROR: // 故障
                        stringValue = "";
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA: // 公式型
                        try {
                            CellValue cellValue;
                            cellValue = evaluator.evaluate(cell);
                            switch (cellValue.getCellType()) { // 判断公式类型
                            case Cell.CELL_TYPE_BOOLEAN:
                                stringValue = String.valueOf(cellValue
                                        .getBooleanValue());
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
    
                                // 处理日期
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    SimpleDateFormat sdf = new SimpleDateFormat(
                                            "yyyy/MM/dd");
                                    stringValue = sdf.format(cell
                                            .getDateCellValue());
                                } else {
                                    // longVal =
                                    // Math.round(cell.getNumericCellValue());
                                    // doubleVal =
                                    // Math.round(cell.getNumericCellValue());
                                    // if(Double.parseDouble(longVal + ".0") ==
                                    // doubleVal)
                                    // stringValue = String.valueOf(longVal);
                                    // else
                                    // stringValue = String.valueOf(doubleVal);
                                    double value = cell.getNumericCellValue();
                                    CellStyle style1 = cell.getCellStyle();
                                    DecimalFormat format = new DecimalFormat();
                                    String temp = style1.getDataFormatString();
                                    // 单元格设置成常规
                                    if (temp.equals("General")) {
                                        format.applyPattern("#");
                                    }
                                    stringValue = format.format(value);
                                }
    
                                break;
                            case Cell.CELL_TYPE_STRING:
                                stringValue = cellValue.getStringValue();
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                stringValue = "";
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                stringValue = "";
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                stringValue = "";
                                break;
                            }
                        } catch (Exception e) {
                            // stringValue = cell.;
                            cell.getCellFormula();
                        }
                        break;
                    default:
                        stringValue = cell.getStringCellValue().toString();
                        break;
                    }
    
                    // switch (cell.getCellType()) {
                    // case HSSFCell.CELL_TYPE_FORMULA:
                    // // cell.getCellFormula();
                    // try {
                    // stringValue = String.valueOf(cell.getNumericCellValue());
                    // } catch (IllegalStateException e) {
                    // stringValue =
                    // String.valueOf(cell.getRichStringCellValue());
                    // }
                    // break;
                    // case HSSFCell.CELL_TYPE_NUMERIC:
                    // stringValue = String.valueOf(cell.getNumericCellValue());
                    // break;
                    // case HSSFCell.CELL_TYPE_STRING:
                    // stringValue =
                    // String.valueOf(cell.getRichStringCellValue());
                    // break;
                    // }
                    //
                    if (map[0].containsKey(rowNum + "," + colNum)) {
                        String pointString = map[0].get(rowNum + "," + colNum);
                        map[0].remove(rowNum + "," + colNum);
                        int bottomeRow = Integer
                                .valueOf(pointString.split(",")[0]);
                        int bottomeCol = Integer
                                .valueOf(pointString.split(",")[1]);
                        int rowSpan = bottomeRow - rowNum + 1;
                        int colSpan = bottomeCol - colNum + 1;
                        sb.append("<td  rowspan= '" + rowSpan + "' colspan= '"
                                + colSpan + "' ");
    
                    } else if (map[1].containsKey(rowNum + "," + colNum)) {
                        map[1].remove(rowNum + "," + colNum);
                        continue;
                    } else {
                        sb.append("<td ");
                    }
                    // 获取样式的内容
                    if (wb instanceof XSSFWorkbook) {
                        XSSFCellStyle xcellStyle = ((XSSFCell) cell)
                                .getCellStyle();
                        if (xcellStyle != null) {
                            short alignment = xcellStyle.getAlignment();
                            sb.append("align='" + convertAlignToHtml(alignment)
                                    + "' ");
                            short verticalAlignment = xcellStyle
                                    .getVerticalAlignment();
                            sb.append("valign='"
                                    + convertVerticalAlignToHtml(verticalAlignment)
                                    + "' ");
                            sb.append("style='");
                            XSSFFont xf = xcellStyle.getFont();
                            short boldWeight = xf.getBoldweight();
                            XSSFColor xc = xf.getXSSFColor();
                            String fontColorStr = ColorUtil
                                    .convertColorToHex(xc);
                            String fontName=xf.getFontName();
                            int fontsize=xf.getFontHeightInPoints();
                            int columnWidth = (int)sheet.getColumnWidthInPixels(cell
                                    .getColumnIndex());
                            int rowHeight=(int) row.getHeightInPoints();
                            sb.append("width:" + columnWidth + "px;");
                            sb.append("height:" + rowHeight + "px;");
                            if (fontColorStr != null
                                    && !"".equals(fontColorStr.trim())) {
                                sb.append("color:" + fontColorStr + ";"); // 字体颜色
                            }
                            if(fontName!=null&& !"".equals(fontName.trim())){
                                sb.append("font-family:\"" + fontName + "\";"); // 字体
                            }
                            if(fontsize!=0){
                                sb.append("font-size:" + fontsize + "px;"); // 字体大小
                            }
                            XSSFColor xbgColor = null;
                            if (xcellStyle.getFillPattern() == CellStyle.SOLID_FOREGROUND) {
                                xbgColor = xcellStyle
                                        .getFillForegroundXSSFColor();
                            }
                            xbgColor = xcellStyle.getFillForegroundXSSFColor();
                            String bgColorStr = ColorUtil
                                    .convertColorToHex(xbgColor);
                            if (bgColorStr != null
                                    && !"".equals(bgColorStr.trim())) {
                                sb.append("background-color:" + bgColorStr
                                        + ";"); // 背景颜色
                            }
                            sb.append(getBorderStyle(0,
                                    xcellStyle.getBorderTop(),
                                    xcellStyle.getTopBorderXSSFColor()));
                            sb.append(getBorderStyle(1,
                                    xcellStyle.getBorderRight(),
                                    xcellStyle.getRightBorderXSSFColor()));
                            sb.append(getBorderStyle(2,
                                    xcellStyle.getBorderBottom(),
                                    xcellStyle.getBottomBorderXSSFColor()));
                            sb.append(getBorderStyle(3,
                                    xcellStyle.getBorderLeft(),
                                    xcellStyle.getLeftBorderXSSFColor()));
                            sb.append("font-weight:" + boldWeight + ";"); // 字体加粗
                            sb.append("font-size: " + xf.getFontHeight() / 2.5
                                    + "%;"); // 字体大小
                            sb.append("white-space: nowrap;");
                        }
                    } else if (wb instanceof HSSFWorkbook) {
    
                        HSSFCellStyle hcellStyle = ((HSSFCell) cell)
                                .getCellStyle();
                        if (hcellStyle != null) {
                            short alignment = hcellStyle.getAlignment();
                            sb.append("align='" + convertAlignToHtml(alignment)
                                    + "' ");
                            short verticalAlignment = hcellStyle
                                    .getVerticalAlignment();
                            sb.append("valign='"
                                    + convertVerticalAlignToHtml(verticalAlignment)
                                    + "' ");
                            sb.append("style='");
                            HSSFFont hf = hcellStyle.getFont(wb);
                            short boldWeight = hf.getBoldweight();
                            short fontColor = hf.getColor();
                            String fontName=hf.getFontName();
                            int fontsize=hf.getFontHeightInPoints();
                            HSSFPalette palette = ((HSSFWorkbook) wb)
                                    .getCustomPalette(); // 类HSSFPalette用于求的颜色的国际标准形式
                            HSSFColor hc = palette.getColor(fontColor);
                            String fontColorStr = ColorUtil
                                    .convertColorToHex(hc);
                            int columnWidth = (int)sheet.getColumnWidthInPixels(cell
                                    .getColumnIndex());
                            int rowHeight=(int) row.getHeightInPoints();
                            sb.append("width:" + columnWidth + "px;");
                            sb.append("height:" + rowHeight + "px;");
                            if (fontColorStr != null
                                    && !"".equals(fontColorStr.trim())) {
                                sb.append("color:" + fontColorStr + ";"); // 字体颜色
                            }
                            if(fontName!=null&& !"".equals(fontName.trim())){
                                sb.append("font-family:\"" + fontName + "\";"); // 字体
                            }
                            if(fontsize!=0){
                                sb.append("font-size:" + fontsize + "px;"); // 字体大小
                            }
                            short bgColor = hcellStyle.getFillForegroundColor();
                            hc = palette.getColor(bgColor);
                            String bgColorStr = convertToStardColor(hc);
                            if (bgColorStr != null
                                    && !"".equals(bgColorStr.trim())) {
                                sb.append("background-color:" + bgColorStr
                                        + ";"); // 背景颜色
                            }
                            sb.append(getBorderStyle(palette, 0,
                                    hcellStyle.getBorderTop(),
                                    hcellStyle.getTopBorderColor()));
                            sb.append(getBorderStyle(palette, 1,
                                    hcellStyle.getBorderRight(),
                                    hcellStyle.getRightBorderColor()));
                            sb.append(getBorderStyle(palette, 3,
                                    hcellStyle.getBorderLeft(),
                                    hcellStyle.getLeftBorderColor()));
                            sb.append(getBorderStyle(palette, 2,
                                    hcellStyle.getBorderBottom(),
                                    hcellStyle.getBottomBorderColor()));
                            sb.append("font-weight:" + boldWeight + ";"); // 字体加粗
                            sb.append("font-size: " + hf.getFontHeight() / 2.5
                                    + "%;"); // 字体大小
                            sb.append("white-space: nowrap;");
                        }
                    }
                    sb.append("' ");
                    sb.append(">");
                    if (stringValue == null || "".equals(stringValue.trim())) {
                        sb.append(" &nbsp; ");
                    } else {
                        // 将ascii码为160的空格转换为html下的空格(&nbsp;)
                        sb.append(stringValue.replace(
                                String.valueOf((char) 160), "&nbsp;"));
                    }
                    sb.append("</td>");
                }
                sb.append("</tr>");
            }
            sb.append("</table>");
            return sb.toString();
        }
    
        /**
         * 转换excel2html方法
         * 
         * @param wb
         *            工作簿
         * @return map key:sheet1 value:
         *         <table>
         *         ...
         *         </table>
         *         字符串
         */
        public List<String> getExcelToHtml(Workbook wb) {
            try {
                List<String> htmlPage = getExcelInfo(wb);
                return htmlPage;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }
    
        /**
         * 程序入口方法
         * 
         * @param filePath
         *            文件的路径
         * @return <table>
         *         ...
         *         </table>
         *         字符串
         */
        public List<String> readExcelToHtml(String filePath) {
                List<String> htmlExcel=null;
                try {
                    File sourcefile = new File(filePath);
                    InputStream is = new FileInputStream(sourcefile);
                    Workbook wb = WorkbookFactory.create(is);
                    htmlExcel = getExcelToHtml(wb);
                } catch (EncryptedDocumentException e) {
                    e.printStackTrace();
                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                } catch (InvalidFormatException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            return htmlExcel;
        }
    
    }

    ColorInfo 类

    public class ColorInfo{
        /**
         * 颜色的alpha值,此值控制了颜色的透明度
         */
        public int A;
        /**
         * 颜色的红分量值,Red
         */
        public int R;
        /**
         * 颜色的绿分量值,Green
         */
        public int G;
        /**
         * 颜色的蓝分量值,Blue
         */
        public int B;
    
        public int toRGB() {
            return this.R << 16 | this.G << 8 | this.B;
        }
    
        public java.awt.Color toAWTColor(){
            return new java.awt.Color(this.R,this.G,this.B,this.A);
        }
    
        public static ColorInfo fromARGB(int red, int green, int blue) {
            return new ColorInfo((int) 0xff, (int) red, (int) green, (int) blue);
        }
        public static ColorInfo fromARGB(int alpha, int red, int green, int blue) {
            return new ColorInfo(alpha, red, green, blue);
        }
        public ColorInfo(int a,int r, int g , int b ) {
            this.A = a;
            this.B = b;
            this.R = r;
            this.G = g;
        }
    }

    ColorUtil 类

    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.Color;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFColor;
    
    public class ColorUtil{ 
    
    /**
         * excel97中颜色转化为uof颜色
         * 
         * @param color
         *            颜色序号
         * @return 颜色或者null
         */
        public static ColorInfo excel97Color2UOF(Workbook book, short color) {
            if (book instanceof HSSFWorkbook) {
                HSSFWorkbook hb = (HSSFWorkbook) book;
                HSSFColor hc = hb.getCustomPalette().getColor(color);
                ColorInfo ci = excelColor2UOF(hc);
                return ci;
            }
            return null;
        }
    
        /**
         * excel(包含97和2007)中颜色转化为uof颜色
         * 
         * @param color
         *            颜色序号
         * @return 颜色或者null
         */
        public static ColorInfo excelColor2UOF(Color color) {
            if (color == null) {
                return null;
            }
            ColorInfo ci = null;
            if (color instanceof XSSFColor) {// .xlsx
                XSSFColor xc = (XSSFColor) color;
                byte[] b = xc.getRgb();
                if (b != null) {// 一定是argb
                    if(b.length==4)
                    ci = ColorInfo.fromARGB(b[0], b[1], b[2], b[3]);
                    else
                        ci = ColorInfo.fromARGB(b[0], b[1], b[2]);
                }
            } else if (color instanceof HSSFColor) {// .xls
                HSSFColor hc = (HSSFColor) color;
                short[] s = hc.getTriplet();// 一定是rgb
                if (s != null) {
                    ci = ColorInfo.fromARGB(s[0], s[1], s[2]);
                }
            }
            return ci;
        }
    
        //将颜色转换为16进制的数
        public static String convertColorToHex(Color c){
            ColorInfo ci  = ColorUtil.excelColor2UOF(c);
            String colorStr = null;
            if(ci != null){
                colorStr = OperaColor.toHex(ci.R, ci.G, ci.B);
            }
            return colorStr;
        }
    }

    OperaColor 类

    import java.awt.Color;
    
    public class OperaColor extends Color {
        public OperaColor(int r, int g, int b) {
            super(r, g, b);
        }
    
        /**
         * * Returns the HEX value representing the colour in the default sRGB
         * ColorModel. * *
         * 
         * @return the HEX value of the colour in the default sRGB ColorModel
         */
        public String getHex() {
            return toHex(getRed(), getGreen(), getBlue());
        }
    
        /**
         * * Returns a web browser-friendly HEX value representing the colour in the
         * default sRGB * ColorModel. * *
         * 
         * @param r
         *            red *
         * @param g
         *            green *
         * @param b
         *            blue *
         * @return a browser-friendly HEX value
         */
        public static String toHex(int r, int g, int b) {
            return "#" + toBrowserHexValue(r) + toBrowserHexValue(g)
                    + toBrowserHexValue(b);
        }
    
        private static String toBrowserHexValue(int number) {
            StringBuilder builder = new StringBuilder(Integer
                    .toHexString(number & 0xff));
            while (builder.length() < 2) {
                builder.append("0");
            }
            return builder.toString().toUpperCase();
        }
    }
    

    Excel读取图片

    代码块

    Picture 类(主要用于保存图片所在位置x和y分别是左上角的点)

    public class Picture {
        private int x;
        private int y;
        private String pictureData;
    
        public int getX() {
            return x;
        }
    
        public void setX(int x) {
            this.x = x;
        }
    
        public int getY() {
            return y;
        }
    
        public void setY(int y) {
            this.y = y;
        }
    
        public String getPictureData() {
            return pictureData;
        }
    
        public void setPictureData(String pictureDate) {
            this.pictureData = pictureDate;
        }
    }

    ReadExcelPicture 类(读取图片)

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.poi.POIXMLDocumentPart;
    import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
    import org.apache.poi.hssf.usermodel.HSSFPatriarch;
    import org.apache.poi.hssf.usermodel.HSSFPicture;
    import org.apache.poi.hssf.usermodel.HSSFShape;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
    import org.apache.poi.xssf.usermodel.XSSFDrawing;
    import org.apache.poi.xssf.usermodel.XSSFPicture;
    import org.apache.poi.xssf.usermodel.XSSFShape;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import sun.misc.BASE64Encoder;
    
    
    public class ReadExcelPicture {
        /**
         * 获取图片
         * 
         * @param workbook
         * @return
         */
        public static List<Picture>  getPic(Workbook workbook, int sheetNum) {
            List<Picture> picList = new ArrayList<Picture>();
            if (workbook instanceof XSSFWorkbook) {
                XSSFWorkbook xwb = (XSSFWorkbook) workbook;
                XSSFSheet xsheet = xwb.getSheetAt(sheetNum);
                for (POIXMLDocumentPart dr : xsheet.getRelations()) {
                    if (dr instanceof XSSFDrawing) {
                        XSSFDrawing drawing = (XSSFDrawing) dr;
                        List<XSSFShape> shapes = drawing.getShapes();
                        for (XSSFShape shape : shapes) {
                            Picture picture = new Picture();
                            XSSFPicture pic = (XSSFPicture) shape;
                            XSSFClientAnchor anchor = pic.getPreferredSize();
                            picture.setX(anchor.getRow1());// 左上角坐标
                            picture.setY(anchor.getCol1());
                            byte[] data = pic.getPictureData().getData();// 图片数据
                            picture.setPictureData(new BASE64Encoder().encode(data));
                            picList.add(picture);
                            picture = null;
                        }
                    }
                }
            } else if (workbook instanceof HSSFWorkbook) {
                HSSFWorkbook hwb = (HSSFWorkbook) workbook;
                HSSFSheet hsheet = hwb.getSheetAt(sheetNum);
                HSSFPatriarch hssfPatriarch= hsheet.getDrawingPatriarch();
                if(hssfPatriarch!=null)
                for (HSSFShape shape : hssfPatriarch.getChildren()) {
                    HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                    if (shape instanceof HSSFPicture) {
                        Picture picture = new Picture();
                        picture.setX(anchor.getRow1());// 左上角坐标
                        picture.setY(anchor.getCol1());
                        HSSFPicture pic = (HSSFPicture) shape;
                        byte[] data = pic.getPictureData().getData();// 图片数据
                        picture.setPictureData(new BASE64Encoder().encode(data));
                        picList.add(picture);
                    }
                }
            }
            return  picList;
        }
    
        /**
         * 获取图片
         * 
         * @param filePath
         * @return
         */
        public static List<List<Picture> > readExcelPicture(String filePath) {
            List<List<Picture> > allpic = new ArrayList<List<Picture>>();
            try {
                File file = new File(filePath);
                FileInputStream fis = new FileInputStream(file);
                Workbook workbook = WorkbookFactory.create(fis);
                int sheetNum = workbook.getNumberOfSheets();// sheet的页数
                for (int i = 0; i < sheetNum; i++) {
                    allpic.add(getPic(workbook, i));
                }
            } catch (InvalidFormatException | IOException e) {
                e.printStackTrace();
            }
            return allpic;
        }

    这里将图片转换为base64编码字符串,需要用 Base64进行解码,有需要可以自行修改输出的方法

    excel读取附件

    附件中读取excel,word,powerPoint 相关文档可以通过POI的示例实现,均在POI示例代码包(poi-example)

    03版xls(EmeddedObjects )

    /* ====================================================================
       Licensed to the Apache Software Foundation (ASF) under one or more
       contributor license agreements.  See the NOTICE file distributed with
       this work for additional information regarding copyright ownership.
       The ASF licenses this file to You under the Apache License, Version 2.0
       (the "License"); you may not use this file except in compliance with
       the License.  You may obtain a copy of the License at
    
           http://www.apache.org/licenses/LICENSE-2.0
    
       Unless required by applicable law or agreed to in writing, software
       distributed under the License is distributed on an "AS IS" BASIS,
       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
       See the License for the specific language governing permissions and
       limitations under the License.
    ==================================================================== */
    package org.apache.poi.hssf.usermodel.examples;
    
    import java.io.FileInputStream;
    import java.util.Iterator;
    
    import org.apache.poi.hslf.usermodel.HSLFSlideShow;
    import org.apache.poi.hslf.usermodel.HSLFSlideShowImpl;
    import org.apache.poi.hssf.usermodel.HSSFObjectData;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hwpf.HWPFDocument;
    import org.apache.poi.poifs.filesystem.DirectoryNode;
    import org.apache.poi.poifs.filesystem.Entry;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    
    /**
     * Demonstrates how you can extract embedded data from a .xls file
     */
    public class EmeddedObjects {
        @SuppressWarnings("unused")
        public static void main(String[] args) throws Exception {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(args[0]));
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            for (HSSFObjectData obj : workbook.getAllEmbeddedObjects()) {
                //the OLE2 Class Name of the object
                String oleName = obj.getOLE2ClassName();
                if (oleName.equals("Worksheet")) {
                    DirectoryNode dn = (DirectoryNode) obj.getDirectory();
                    HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(dn, fs, false);
                    //System.out.println(entry.getName() + ": " + embeddedWorkbook.getNumberOfSheets());
                    embeddedWorkbook.close();
                } else if (oleName.equals("Document")) {
                    DirectoryNode dn = (DirectoryNode) obj.getDirectory();
                    HWPFDocument embeddedWordDocument = new HWPFDocument(dn);
                    //System.out.println(entry.getName() + ": " + embeddedWordDocument.getRange().text());
                }  else if (oleName.equals("Presentation")) {
                    DirectoryNode dn = (DirectoryNode) obj.getDirectory();
                    HSLFSlideShow embeddedPowerPointDocument = new HSLFSlideShow(new HSLFSlideShowImpl(dn));
                    //System.out.println(entry.getName() + ": " + embeddedPowerPointDocument.getSlides().length);
                } else {
                    if(obj.hasDirectoryEntry()){
                        // The DirectoryEntry is a DocumentNode. Examine its entries to find out what it is
                        DirectoryNode dn = (DirectoryNode) obj.getDirectory();
                        for (Iterator<Entry> entries = dn.getEntries(); entries.hasNext();) {
                            Entry entry = entries.next();
                            //System.out.println(oleName + "." + entry.getName());
                        }
                    } else {
                        // There is no DirectoryEntry
                        // Recover the object's data from the HSSFObjectData instance.
                        byte[] objectData = obj.getObjectData();
                    }
                }
            }
            workbook.close();
        }
    }
    

    07版xlsx()

    /* ====================================================================
       Licensed to the Apache Software Foundation (ASF) under one or more
       contributor license agreements.  See the NOTICE file distributed with
       this work for additional information regarding copyright ownership.
       The ASF licenses this file to You under the Apache License, Version 2.0
       (the "License"); you may not use this file except in compliance with
       the License.  You may obtain a copy of the License at
    
           http://www.apache.org/licenses/LICENSE-2.0
    
       Unless required by applicable law or agreed to in writing, software
       distributed under the License is distributed on an "AS IS" BASIS,
       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
       See the License for the specific language governing permissions and
       limitations under the License.
    ==================================================================== */
    package org.apache.poi.xssf.usermodel.examples;
    
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.openxml4j.opc.PackagePart;
    import org.apache.poi.xslf.usermodel.XSLFSlideShow;
    import org.apache.poi.xwpf.usermodel.XWPFDocument;
    import org.apache.poi.hslf.usermodel.HSLFSlideShowImpl;
    import org.apache.poi.hwpf.HWPFDocument;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    import java.io.InputStream;
    
    /**
     * Demonstrates how you can extract embedded data from a .xlsx file
     */
    public class EmbeddedObjects {
        public static void main(String[] args) throws Exception {
            OPCPackage pkg = OPCPackage.open(args[0]);
            XSSFWorkbook workbook = new XSSFWorkbook(pkg);
            for (PackagePart pPart : workbook.getAllEmbedds()) {
                String contentType = pPart.getContentType();
                // Excel Workbook - either binary or OpenXML
                if (contentType.equals("application/vnd.ms-excel")) {
                    HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream());
                }
                // Excel Workbook - OpenXML file format
                else if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {
                    XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(pPart.getInputStream());
                }
                // Word Document - binary (OLE2CDF) file format
                else if (contentType.equals("application/msword")) {
                    HWPFDocument document = new HWPFDocument(pPart.getInputStream());
                }
                // Word Document - OpenXML file format
                else if (contentType.equals("application/vnd.openxmlformats-officedocument.wordprocessingml.document")) {
                    XWPFDocument document = new XWPFDocument(pPart.getInputStream());
                }
                // PowerPoint Document - binary file format
                else if (contentType.equals("application/vnd.ms-powerpoint")) {
                    HSLFSlideShowImpl slideShow = new HSLFSlideShowImpl(pPart.getInputStream());
                }
                // PowerPoint Document - OpenXML file format
                else if (contentType.equals("application/vnd.openxmlformats-officedocument.presentationml.presentation")) {
                    OPCPackage docPackage = OPCPackage.open(pPart.getInputStream());
                    XSLFSlideShow slideShow = new XSLFSlideShow(docPackage);
                }
                // Any other type of embedded object.
                else {
                    System.out.println("Unknown Embedded Document: " + contentType);
                    InputStream inputStream = pPart.getInputStream();
                }
            }
            pkg.close();
        }
    }

    其他附件实现方法

    ReadExcelOle 类(该类仅支持单个sheet进行读取,导入附件方式必须为ole package方式才可以读取)

    package cn.gov.customs.xshg.base.exceltool;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.nio.charset.Charset;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.poi.hssf.usermodel.HSSFObjectData;
    import org.apache.poi.hssf.usermodel.HSSFPatriarch;
    import org.apache.poi.hssf.usermodel.HSSFShape;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.openxml4j.opc.PackagePart;
    import org.apache.poi.openxml4j.opc.PackageRelationship;
    import org.apache.poi.poifs.filesystem.DirectoryNode;
    import org.apache.poi.poifs.filesystem.Ole10Native;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    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.xssf.usermodel.XSSFRelation;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    
    public class ReadExcelOle {
        /**
         * 获取附件并生成附件到与excel同一目录下面
         * 
         * @param path
         *            excel文件所在文件夹路径
         * @param s
         *            工作簿
         * @return List<文件名称>
         */
        public static List<String> getOleBySheet(String path, Sheet s) {
            List<String> oleNames = new ArrayList<String>();
            try {
                if (s instanceof XSSFSheet) {
                    XSSFSheet sheet = (XSSFSheet) s;
                    for (PackageRelationship rel : sheet.getPackagePart()
                            .getRelationshipsByType(
                                    XSSFRelation.OLEEMBEDDINGS.getRelation())) {
                        PackagePart pPart = sheet.getPackagePart().getRelatedPart(
                                rel);
                        InputStream inputStream = pPart.getInputStream();
                        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(
                                inputStream);
                        Ole10Native ole = Ole10Native
                                .createFromEmbeddedOleObject(poifsFileSystem);
                        oleNames.add(write(ole, path));
                    }
                } else if (s instanceof HSSFSheet) {
                    HSSFSheet sheet = (HSSFSheet) s;
                    HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
                    if (patriarch != null)
                        for (HSSFShape shape : patriarch.getChildren()) {
                            if (shape instanceof HSSFObjectData) {
                                HSSFObjectData obj = (HSSFObjectData) shape;
                                String oleName = obj.getOLE2ClassName();
                                if (!(oleName.equals("Worksheet")
                                        || oleName.equals("Document") || oleName
                                            .equals("Presentation"))) {
                                    if (obj.hasDirectoryEntry()) {
                                        DirectoryNode dn = (DirectoryNode) obj
                                                .getDirectory();
                                        Ole10Native ole = Ole10Native
                                                .createFromEmbeddedOleObject(dn);
                                        oleNames.add(write(ole, path));
                                    }
                                }
                            }
                        }
    
                }else{
                    throw new RuntimeException("没找到对应工作簿解析方法");
                }
            } catch (IOException e) {
                e.printStackTrace();
                return null;
    
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
    
            return oleNames;
    
        }
        //此处文件名的编码可能会有问题,POI实现自动将编码设置为ISO-8859-1,我们需要将他转换成中文才可以正常显示
        static String write(Ole10Native ole, String path) throws IOException {
            String string = new String(ole.getLabel().getBytes(
                    Charset.forName("ISO-8859-1")), "GBK");
            FileOutputStream os = new FileOutputStream(
                    new File(path + "/" + string));
            os.write(ole.getDataBuffer());
            os.close();
            return string;
        }
    
        //示例调用
        public static void main(String[] args) throws InvalidFormatException, IOException {
            File file=new File("C:/Users/Administrator/Desktop/test.xls");
            Workbook wb=WorkbookFactory.create(file);
            List<String> string=getOleBySheet(file.getParent(),wb.getSheetAt(0));
            wb.close();
            for (String string2 : string) {
                System.out.println(string2);
            }
        }
    }
    
    展开全文
  • 使用POI读取excel文件内容 1.前言 项目中要求读取excel文件内容,并将其转化为xml格式。常见读取excel文档一般使用POI和JExcelAPI这两个工具。这里我们介绍使用POI实现读取excel文档。 2.代码实例: ...

    使用POI读取excel文件内容

    1.前言

    项目中要求读取excel文件内容,并将其转化为xml格式。常见读取excel文档一般使用POI和JExcelAPI这两个工具。这里我们介绍使用POI实现读取excel文档。

    2.代码实例:

    复制代码
    package edu.sjtu.erplab.poi;

    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.Map;

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;

    /**
    * 操作Excel表格的功能类
    */
    public class ExcelReader {
    private POIFSFileSystem fs;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private HSSFRow row;

    /**
    * 读取Excel表格表头的内容
    *
    @param InputStream
    *
    @return String 表头内容的数组
    */
    public String[] readExcelTitle(InputStream is) {
    try {
    fs = new POIFSFileSystem(is);
    wb = new HSSFWorkbook(fs);
    } catch (IOException e) {
    e.printStackTrace();
    }
    sheet = wb.getSheetAt(0);
    row = sheet.getRow(0);
    // 标题总列数
    int colNum = row.getPhysicalNumberOfCells();
    System.out.println("colNum:" + colNum);
    String[] title = new String[colNum];
    for (int i = 0; i < colNum; i++) {
    //title[i] = getStringCellValue(row.getCell((short) i));
    title[i] = getCellFormatValue(row.getCell((short) i));
    }
    return title;
    }

    /**
    * 读取Excel数据内容
    *
    @param InputStream
    *
    @return Map 包含单元格数据内容的Map对象
    */
    public Map<Integer, String> readExcelContent(InputStream is) {
    Map<Integer, String> content = new HashMap<Integer, String>();
    String str = "";
    try {
    fs = new POIFSFileSystem(is);
    wb = new HSSFWorkbook(fs);
    } catch (IOException e) {
    e.printStackTrace();
    }
    sheet = wb.getSheetAt(0);
    // 得到总行数
    int rowNum = sheet.getLastRowNum();
    row = sheet.getRow(0);
    int colNum = row.getPhysicalNumberOfCells();
    // 正文内容应该从第二行开始,第一行为表头的标题
    for (int i = 1; i <= rowNum; i++) {
    row = sheet.getRow(i);
    int j = 0;
    while (j < colNum) {
    // 每个单元格的数据内容用"-"分割开,以后需要时用String类的replace()方法还原数据
    // 也可以将每个单元格的数据设置到一个javabean的属性中,此时需要新建一个javabean
    // str += getStringCellValue(row.getCell((short) j)).trim() +
    // "-";
    str += getCellFormatValue(row.getCell((short) j)).trim() + " ";
    j++;
    }
    content.put(i, str);
    str = "";
    }
    return content;
    }

    /**
    * 获取单元格数据内容为字符串类型的数据
    *
    *
    @param cell Excel单元格
    *
    @return String 单元格数据内容
    */
    private String getStringCellValue(HSSFCell cell) {
    String strCell = "";
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
    strCell = cell.getStringCellValue();
    break;
    case HSSFCell.CELL_TYPE_NUMERIC:
    strCell = String.valueOf(cell.getNumericCellValue());
    break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
    strCell = String.valueOf(cell.getBooleanCellValue());
    break;
    case HSSFCell.CELL_TYPE_BLANK:
    strCell = "";
    break;
    default:
    strCell = "";
    break;
    }
    if (strCell.equals("") || strCell == null) {
    return "";
    }
    if (cell == null) {
    return "";
    }
    return strCell;
    }

    /**
    * 获取单元格数据内容为日期类型的数据
    *
    *
    @param cell
    * Excel单元格
    *
    @return String 单元格数据内容
    */
    private String getDateCellValue(HSSFCell cell) {
    String result = "";
    try {
    int cellType = cell.getCellType();
    if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
    Date date = cell.getDateCellValue();
    result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
    + "-" + date.getDate();
    } else if (cellType == HSSFCell.CELL_TYPE_STRING) {
    String date = getStringCellValue(cell);
    result = date.replaceAll("[年月]", "-").replace("日", "").trim();
    } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
    result = "";
    }
    } catch (Exception e) {
    System.out.println("日期格式不正确!");
    e.printStackTrace();
    }
    return result;
    }

    /**
    * 根据HSSFCell类型设置数据
    *
    @param cell
    *
    @return
    */
    private String getCellFormatValue(HSSFCell cell) {
    String cellvalue = "";
    if (cell != null) {
    // 判断当前Cell的Type
    switch (cell.getCellType()) {
    // 如果当前Cell的Type为NUMERIC
    case HSSFCell.CELL_TYPE_NUMERIC:
    case HSSFCell.CELL_TYPE_FORMULA: {
    // 判断当前的cell是否为Date
    if (HSSFDateUtil.isCellDateFormatted(cell)) {
    // 如果是Date类型则,转化为Data格式

    //方法1:这样子的data格式是带时分秒的:2011-10-12 0:00:00
    //cellvalue = cell.getDateCellValue().toLocaleString();

    //方法2:这样子的data格式是不带带时分秒的:2011-10-12
    Date date = cell.getDateCellValue();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    cellvalue = sdf.format(date);

    }
    // 如果是纯数字
    else {
    // 取得当前Cell的数值
    cellvalue = String.valueOf(cell.getNumericCellValue());
    }
    break;
    }
    // 如果当前Cell的Type为STRIN
    case HSSFCell.CELL_TYPE_STRING:
    // 取得当前的Cell字符串
    cellvalue = cell.getRichStringCellValue().getString();
    break;
    // 默认的Cell值
    default:
    cellvalue = " ";
    }
    } else {
    cellvalue = "";
    }
    return cellvalue;

    }

    public static void main(String[] args) {
    try {
    // 对读取Excel表格标题测试
    InputStream is = new FileInputStream("d:\\test2.xls");
    ExcelReader excelReader = new ExcelReader();
    String[] title = excelReader.readExcelTitle(is);
    System.out.println("获得Excel表格的标题:");
    for (String s : title) {
    System.out.print(s + " ");
    }

    // 对读取Excel表格内容测试
    InputStream is2 = new FileInputStream("d:\\test2.xls");
    Map<Integer, String> map = excelReader.readExcelContent(is2);
    System.out.println("获得Excel表格的内容:");
    for (int i = 1; i <= map.size(); i++) {
    System.out.println(map.get(i));
    }

    } catch (FileNotFoundException e) {
    System.out.println("未找到指定路径的文件!");
    e.printStackTrace();
    }
    }
    }
    复制代码

    3.总结

    因为excel单元格中的内容往往都有一定的格式,比如日期型,数字型,字符串型,因此在读取的时候要进行格式判断,不然会出现错误。常见的就是不能正常读取日期。在代码实例中有一个方法:

    getCellFormatValue(HSSFCell cell)

    往这个方法中传入excel单元格就能识别单元格格式,并转化为正确的格式。

    ps:2012-2-23

    代码实例中有一段代码:

    int colNum = row.getPhysicalNumberOfCells();

    其中的HSSFRow.getPhysicalNumberOfCells();这个方法是用于获取一行中存在的单元格数,POI的官方API中有给出getPhysicalNumberOfCells方法的解释

    getPhysicalNumberOfCells

    public int getPhysicalNumberOfCells()
    gets the number of defined cells (NOT number of cells in the actual row!).  That is to say if only columns 0,4,5 have values then there would be 3.
    Specified by:
    getPhysicalNumberOfCells in interface  Row
    Returns:
    int representing the number of defined cells in the row.
    展开全文
  • JAVA使用POI读取EXCEL文件的简单model

    万次阅读 2017-05-23 14:42:03
    [java] view plain copy print?packagepoi;importjava.io.FileInputStream;importjava.io.IOException;importjava.io.InputStream;importjava.util.Iterator;importorg.apache.poi.hssf.usermodel.HS
  • 使用POI读取excel文件,识别空行

    万次阅读 2018-09-29 15:04:02
    最近在做项目时,需要使用解析excel数据,我使用的是poi,这个时候遇到 poi 的大坑,读取数据时,不会过滤空行!是的,不会过滤空行,比如一份excel,原来有30条数据,从底下往上删除了20条,但当使用poi 解析的...
  • NULL 博文链接:https://qq-24665727.iteye.com/blog/2339730
  • 1,通过usermodel读取文件 , 2,通过 usermodel写入文件 3,通过eventusermodel读取文件
  • Java 使用 POIExcel文件进行读写操作 一、背景 目前正在做一个问卷功能模块,收集完问卷信息后,需要将数据库中的数据导出到Excel数据表中进行留存,因此就学习了在Java中如何对Excel表进行读写操作。经过...
  • 使用POI 读取 Excel 文件读取手机号码 变成 1.3471022771E10 [问题点数:40分,结帖人xieyongqiu]   不显示删除回复   显示所有回复   显示星级回复   显示得分回复   只显示楼主  收藏 ...
  • 开发中经常需要读取excel文件进行数据的导入或者其他处理,本文通过POI直接对excel文件直接进行操作 首先是导入相关的jar包,如下图。最下面的3个jar包是读取excel时依赖的包 读取excel文件 public Workbook ...
  • 今天刚好研究了一下使用poi读取Excel文件,闲来没事分享一下, 直接上代码: **工具类===此类可以直接进行调用. **看不懂的直接复制进去就好.(请先导入相关的jar包) package com.caituo.test; import java.io.File; ...
  • JAVA:使用POI读取excel文件中的日期

    万次阅读 2017-10-19 10:13:41
    最近开发的项目,遇到用户上传excel文件并导入数据到系统这个需求,而有excel中有的单元格是日期格式,本文介绍怎么从excel中读取日期格式的数据。先上代码: InputStream inputStream = new FileInputStream("D://...
  • 1.maven管理项目,在pom.xml里加依赖,或者直接引用jar包     org.apache.poi poi 3.10-FINAL org.apache.poi poi-ooxml ...2.ReadExcel实现类,运行main方法 import java.io.FileInputStream; i
  • 主要介绍了java使用poi读取ppt文件poi读取excel、word示例,需要的朋友可以参考下

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 24,135
精华内容 9,654
关键字:

使用poi读取excel文件