2019-09-12 16:17:56 buertianci 阅读数 35
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1271 人正在学习 去看看 钟林森

事件情景

找了不少资料,本文记录一下成果吧,主要使用POI Sax 事件驱动解析Excel,主要是excel2003和excel2007两种类型的工具类,好了话不多说直接上代码。

1.pom.xml引入依赖

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.0.0</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.0.0</version>
</dependency>
<dependency>
	<groupId>xerces</groupId>
	<artifactId>xercesImpl</artifactId>
	<version>2.11.0</version>
</dependency>

2.ExcelReaderUtil

package com.hualala.data.meta.platform.common.common.utils;

public class ExcelReaderUtil {
    // excel2003扩展名
    public static final String EXCEL03_EXTENSION = ".xls";
    // excel2007扩展名
    public static final String EXCEL07_EXTENSION = ".xlsx";

    /**
     * 读取Excel文件,可能是03也可能是07版本
     *
     * @param reader
     * @param fileName
     * @throws Exception
     */
    public static void readExcel(IExcelRowReader reader, String fileName) throws Exception {
        // 处理excel2003文件
        if (fileName.endsWith(EXCEL03_EXTENSION)) {
            ExcelXlsReader exceXls = new ExcelXlsReader();
            exceXls.setRowReader(reader);
            exceXls.process(fileName);
            // 处理excel2007文件
        } else if (fileName.endsWith(EXCEL07_EXTENSION)) {
            ExcelXlsxReader exceXlsx = new ExcelXlsxReader();
            exceXlsx.setRowReader(reader);
            exceXlsx.process(fileName);
        } else {
            throw new Exception("文件格式错误,fileName的扩展名只能是xls或xlsx。");
        }
    }

    /**
     * 测试
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        IExcelRowReader rowReader = new ExcelRowReader();
        //ExcelReaderUtil.readExcel(rowReader, "F://test_one.xls");
        ExcelReaderUtil.readExcel(rowReader, "F://test_two.xlsx");
    }
}

3.IExcelRowReader

package com.hualala.data.meta.platform.common.common.utils;

import java.util.List;

public interface IExcelRowReader {
    /**
     * 业务逻辑实现方法
     *
     * @param sheetIndex
     * @param curRow
     * @param rowlist
     */
    void getRows(int sheetIndex, int curRow, List<String> rowlist);
}

4.ExcelRowReader

package com.hualala.data.meta.platform.common.common.utils;

import lombok.extern.slf4j.Slf4j;

import java.util.List;

@Slf4j
public class ExcelRowReader implements IExcelRowReader {
    @Override
    public void getRows(int sheetIndex, int curRow, List<String> rowlist) {
        System.out.print(curRow+" ");
        for (int i = 0; i < rowlist.size(); i++) {
            System.out.print(rowlist.get(i)==""?"*":rowlist.get(i) + " ");
        }
        System.out.println();
    }
}

5.ExcelXlsReader

package com.hualala.data.meta.platform.common.common.utils;

import org.apache.poi.hssf.eventusermodel.*;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelXlsReader implements HSSFListener {
    private int minColumns = -1;

    private POIFSFileSystem fs;

    private int lastRowNumber;

    private int lastColumnNumber;

    /** Should we output the formula, or the value it has? */
    private boolean outputFormulaValues = true;

    /** For parsing Formulas */
    private SheetRecordCollectingListener workbookBuildingListener;

    // excel2003工作薄
    private HSSFWorkbook stubWorkbook;

    // Records we pick up as we process
    private SSTRecord sstRecord;

    private FormatTrackingHSSFListener formatListener;

    // 表索引
    private int sheetIndex = -1;

    private BoundSheetRecord[] orderedBSRs;

    @SuppressWarnings("unchecked")
    private ArrayList boundSheetRecords = new ArrayList();

    // For handling formulas with string results
    private int nextRow;

    private int nextColumn;

    private boolean outputNextStringRecord;

    // 当前行
    private int curRow = 0;

    // 存储行记录的容器
    private List<String> rowlist = new ArrayList<String>();;

    @SuppressWarnings("unused")
    private String sheetName;

    private IExcelRowReader rowReader;

    public void setRowReader(IExcelRowReader rowReader) {
        this.rowReader = rowReader;
    }

    /**
     * 遍历excel下所有的sheet
     *
     * @throws IOException
     */
    public void process(String fileName) throws IOException {
        this.fs = new POIFSFileSystem(new FileInputStream(fileName));
        MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
        formatListener = new FormatTrackingHSSFListener(listener);
        HSSFEventFactory factory = new HSSFEventFactory();
        HSSFRequest request = new HSSFRequest();
        if (outputFormulaValues) {
            request.addListenerForAllRecords(formatListener);
        } else {
            workbookBuildingListener = new SheetRecordCollectingListener(formatListener);
            request.addListenerForAllRecords(workbookBuildingListener);
        }
        factory.processWorkbookEvents(request, fs);
    }

    /**
     * HSSFListener 监听方法,处理 Record
     */
    @SuppressWarnings("unchecked")
    public void processRecord(Record record) {
        int thisRow = -1;
        int thisColumn = -1;
        String thisStr = null;
        String value = null;
        switch (record.getSid()) {
            case BoundSheetRecord.sid:
                boundSheetRecords.add(record);
                break;
            case BOFRecord.sid:
                BOFRecord br = (BOFRecord) record;
                if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
                    // 如果有需要,则建立子工作薄
                    if (workbookBuildingListener != null && stubWorkbook == null) {
                        stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
                    }

                    sheetIndex++;
                    if (orderedBSRs == null) {
                        orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
                    }
                    sheetName = orderedBSRs[sheetIndex].getSheetname();
                }
                break;

            case SSTRecord.sid:
                sstRecord = (SSTRecord) record;
                break;

            case BlankRecord.sid:
                BlankRecord brec = (BlankRecord) record;
                thisRow = brec.getRow();
                thisColumn = brec.getColumn();
                thisStr = "";
                rowlist.add(thisColumn, thisStr);
                break;
            case BoolErrRecord.sid: // 单元格为布尔类型
                BoolErrRecord berec = (BoolErrRecord) record;
                thisRow = berec.getRow();
                thisColumn = berec.getColumn();
                thisStr = berec.getBooleanValue() + "";
                rowlist.add(thisColumn, thisStr);
                break;

            case FormulaRecord.sid: // 单元格为公式类型
                FormulaRecord frec = (FormulaRecord) record;
                thisRow = frec.getRow();
                thisColumn = frec.getColumn();
                if (outputFormulaValues) {
                    if (Double.isNaN(frec.getValue())) {
                        // Formula result is a string
                        // This is stored in the next record
                        outputNextStringRecord = true;
                        nextRow = frec.getRow();
                        nextColumn = frec.getColumn();
                    } else {
                        thisStr = formatListener.formatNumberDateCell(frec);
                    }
                } else {
                    thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
                }
                rowlist.add(thisColumn, thisStr);
                break;
            case StringRecord.sid:// 单元格中公式的字符串
                if (outputNextStringRecord) {
                    // String for formula
                    StringRecord srec = (StringRecord) record;
                    thisStr = srec.getString();
                    thisRow = nextRow;
                    thisColumn = nextColumn;
                    outputNextStringRecord = false;
                }
                break;
            case LabelRecord.sid:
                LabelRecord lrec = (LabelRecord) record;
                curRow = thisRow = lrec.getRow();
                thisColumn = lrec.getColumn();
                value = lrec.getValue().trim();
                value = value.equals("") ? " " : value;
                this.rowlist.add(thisColumn, value);
                break;
            case LabelSSTRecord.sid: // 单元格为字符串类型
                LabelSSTRecord lsrec = (LabelSSTRecord) record;
                curRow = thisRow = lsrec.getRow();
                thisColumn = lsrec.getColumn();
                if (sstRecord == null) {
                    rowlist.add(thisColumn, " ");
                } else {
                    value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
                    value = value.equals("") ? " " : value;
                    rowlist.add(thisColumn, value);
                }
                break;
            case NumberRecord.sid: // 单元格为数字类型
                NumberRecord numrec = (NumberRecord) record;
                curRow = thisRow = numrec.getRow();
                thisColumn = numrec.getColumn();
                value = formatListener.formatNumberDateCell(numrec).trim();
                value = value.equals("") ? " " : value;
                // 向容器加入列值
                rowlist.add(thisColumn, value);
                break;
            default:
                break;
        }

        // 遇到新行的操作
        if (thisRow != -1 && thisRow != lastRowNumber) {
            lastColumnNumber = -1;
        }

        // 空值的操作
        if (record instanceof MissingCellDummyRecord) {
            MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
            curRow = thisRow = mc.getRow();
            thisColumn = mc.getColumn();
            rowlist.add(thisColumn, " ");
        }

        // 更新行和列的值
        if (thisRow > -1)
            lastRowNumber = thisRow;
        if (thisColumn > -1)
            lastColumnNumber = thisColumn;

        // 行结束时的操作
        if (record instanceof LastCellOfRowDummyRecord) {
            if (minColumns > 0) {
                // 列值重新置空
                if (lastColumnNumber == -1) {
                    lastColumnNumber = 0;
                }
            }
            lastColumnNumber = -1;

            // 每行结束时, 调用getRows() 方法
            rowReader.getRows(sheetIndex, curRow, rowlist);
            // 清空容器
            rowlist.clear();
        }
    }

    public static void main(String[] args) {
        IExcelRowReader rowReader = new ExcelRowReader();
        try {
            System.out.println("**********************************************");
             ExcelReaderUtil.readExcel(rowReader,
             "F://test_one.xls");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

6.ExcelXlsxReader

package com.hualala.data.meta.platform.common.common.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ExcelXlsxReader extends DefaultHandler {
    private IExcelRowReader rowReader;

    public void setRowReader(IExcelRowReader rowReader) {
        this.rowReader = rowReader;
    }

    /**
     * 共享字符串表
     */
    private SharedStringsTable sst;

    /**
     * 上一次的内容
     */
    private String lastContents;

    /**
     * 字符串标识
     */
    private boolean nextIsString;

    /**
     * 工作表索引
     */
    private int sheetIndex = -1;

    /**
     * 行集合
     */
    private List<String> rowlist = new ArrayList<String>();

    /**
     * 当前行
     */
    private int curRow = 0;

    /**
     * 当前列
     */
    private int curCol = 0;

    /**
     * T元素标识
     */
    private boolean isTElement;

    /**
     * 异常信息,如果为空则表示没有异常
     */
    private String exceptionMessage;

    /**
     * 单元格数据类型,默认为字符串类型
     */
    private CellDataType nextDataType = CellDataType.SSTINDEX;

    private final DataFormatter formatter = new DataFormatter();

    private short formatIndex;

    private String formatString;

    // 定义前一个元素和当前元素的位置,用来计算其中空的单元格数量,如A6和A8等
    private String preRef = null, ref = null;

    // 定义该文档一行最大的单元格数,用来补全一行最后可能缺失的单元格
    private String maxRef = null;

    /**
     * 单元格
     */
    private StylesTable stylesTable;

    /**
     * 遍历工作簿中所有的电子表格
     *
     * @param filename
     * @throws IOException
     * @throws OpenXML4JException
     * @throws SAXException
     * @throws Exception
     */
    public void process(String filename) throws IOException, OpenXML4JException, SAXException {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = this.fetchSheetParser(sst);
        Iterator<InputStream> sheets = xssfReader.getSheetsData();
        while (sheets.hasNext()) {
            curRow = 0;
            sheetIndex++;
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
        }
    }

    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        this.sst = sst;
        parser.setContentHandler(this);
        return parser;
    }

    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        // c => 单元格
        if ("c".equals(name)) {
            // 前一个单元格的位置
            if (preRef == null) {
                preRef = attributes.getValue("r");
            } else {
                preRef = ref;
            }
            // 当前单元格的位置
            ref = attributes.getValue("r");
            // 设定单元格类型
            this.setNextDataType(attributes);
            // Figure out if the value is an index in the SST
            String cellType = attributes.getValue("t");
            if (cellType != null && cellType.equals("s")) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        }

        // 当元素为t时
        if ("t".equals(name)) {
            isTElement = true;
        } else {
            isTElement = false;
        }

        // 置空
        lastContents = "";
    }

    /**
     * 单元格中的数据可能的数据类型
     */
    enum CellDataType {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
    }

    /**
     * 处理数据类型
     *
     * @param attributes
     */
    public void setNextDataType(Attributes attributes) {
        nextDataType = CellDataType.NUMBER;
        formatIndex = -1;
        formatString = null;
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        String columData = attributes.getValue("r");

        if ("b".equals(cellType)) {
            nextDataType = CellDataType.BOOL;
        } else if ("e".equals(cellType)) {
            nextDataType = CellDataType.ERROR;
        } else if ("inlineStr".equals(cellType)) {
            nextDataType = CellDataType.INLINESTR;
        } else if ("s".equals(cellType)) {
            nextDataType = CellDataType.SSTINDEX;
        } else if ("str".equals(cellType)) {
            nextDataType = CellDataType.FORMULA;
        }

        if (cellStyleStr != null) {
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            formatIndex = style.getDataFormat();
            formatString = style.getDataFormatString();

            if ("m/d/yy" == formatString) {
                nextDataType = CellDataType.DATE;
                formatString = "yyyy-MM-dd hh:mm:ss.SSS";
            }

            if (formatString == null) {
                nextDataType = CellDataType.NULL;
                formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
            }
        }
    }

    /**
     * 对解析出来的数据进行类型处理
     *
     * @param value
     *            单元格的值(这时候是一串数字)
     * @param thisStr
     *            一个空字符串
     * @return
     */
    @SuppressWarnings("deprecation")
    public String getDataValue(String value, String thisStr) {
        switch (nextDataType) {
            // 这几个的顺序不能随便交换,交换了很可能会导致数据错误
            case BOOL:
                char first = value.charAt(0);
                thisStr = first == '0' ? "FALSE" : "TRUE";
                break;
            case ERROR:
                thisStr = "\"ERROR:" + value.toString() + '"';
                break;
            case FORMULA:
                thisStr = '"' + value.toString() + '"';
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());

                thisStr = rtsi.toString();
                rtsi = null;
                break;
            case SSTINDEX:
                String sstIndex = value.toString();
                try {
                    int idx = Integer.parseInt(sstIndex);
                    XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));
                    thisStr = rtss.toString();
                    rtss = null;
                } catch (NumberFormatException ex) {
                    thisStr = value.toString();
                }
                break;
            case NUMBER:
                if (formatString != null) {
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
                } else {
                    thisStr = value;
                }

                thisStr = thisStr.replace("_", "").trim();
                break;
            case DATE:
                thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);

                // 对日期字符串作特殊处理
                thisStr = thisStr.replace(" ", "T");
                break;
            default:
                thisStr = " ";

                break;
        }

        return thisStr;
    }

    @Override
    public void endElement(String uri, String localName, String name) throws SAXException {
        // 根据SST的索引值的到单元格的真正要存储的字符串
        // 这时characters()方法可能会被调用多次
        if (nextIsString  && StringUtils.isNotEmpty(lastContents) && StringUtils.isNumeric(lastContents)) {
            int idx = Integer.parseInt(lastContents);
            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
        }

        // t元素也包含字符串
        if (isTElement) {
            // 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
            String value = lastContents.trim();
            rowlist.add(curCol, value);
            curCol++;
            isTElement = false;
        } else if ("v".equals(name)) {
            // v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引
            String value = this.getDataValue(lastContents.trim(), "");
            // 补全单元格之间的空单元格
            if (!ref.equals(preRef)) {
                int len = countNullCell(ref, preRef);
                for (int i = 0; i < len; i++) {
                    rowlist.add(curCol, "");
                    curCol++;
                }
            }
            rowlist.add(curCol, value);
            curCol++;
        } else {
            // 如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法
            if (name.equals("row")) {
                // 默认第一行为表头,以该行单元格数目为最大数目
                if (curRow == 0) {
                    maxRef = ref;
                }
                // 补全一行尾部可能缺失的单元格
                if (maxRef != null) {
                    int len = countNullCell(maxRef, ref);
                    for (int i = 0; i <= len; i++) {
                        rowlist.add(curCol, "");
                        curCol++;
                    }
                }
                rowReader.getRows(sheetIndex, curRow, rowlist);

                rowlist.clear();
                curRow++;
                curCol = 0;
                preRef = null;
                ref = null;
            }
        }
    }

    /**
     * 计算两个单元格之间的单元格数目(同一行)
     *
     * @param ref
     * @param preRef
     * @return
     */
    public int countNullCell(String ref, String preRef) {
        // excel2007最大行数是1048576,最大列数是16384,最后一列列名是XFD
        String xfd = ref.replaceAll("\\d+", "");
        String xfd_1 = preRef.replaceAll("\\d+", "");

        xfd = fillChar(xfd, 3, '@', true);
        xfd_1 = fillChar(xfd_1, 3, '@', true);

        char[] letter = xfd.toCharArray();
        char[] letter_1 = xfd_1.toCharArray();
        int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
        return res - 1;
    }

    /**
     * 字符串的填充
     *
     * @param str
     * @param len
     * @param let
     * @param isPre
     * @return
     */
    String fillChar(String str, int len, char let, boolean isPre) {
        int len_1 = str.length();
        if (len_1 < len) {
            if (isPre) {
                for (int i = 0; i < (len - len_1); i++) {
                    str = let + str;
                }
            } else {
                for (int i = 0; i < (len - len_1); i++) {
                    str = str + let;
                }
            }
        }
        return str;
    }

    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        // 得到单元格内容的值
        lastContents += new String(ch, start, length);
    }

    /**
     * @return the exceptionMessage
     */
    public String getExceptionMessage() {
        return exceptionMessage;
    }

    public static void main(String[] args) {
        IExcelRowReader rowReader = new ExcelRowReader();
        try {
            // ExcelReaderUtil.readExcel(rowReader,
            // "E://2016-07-04-011940a.xls");
            System.out.println("**********************************************");
            ExcelReaderUtil.readExcel(rowReader, "F://test_two.xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

7.ExcelXlsxReader(如果6在解析字符串类型数字抛下标越界异常请使用这个试试)

package com.data.meta.platform.common.common.utils.ExcelUtil;

import com.data.meta.platform.common.common.utils.IExcelRowReader;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.File;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelXlsxReader extends DefaultHandler {
    /**
     * 单元格中的数据可能的数据类型
     */
    enum CellDataType {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
    }

    /**
     * 共享字符串表
     */
    private SharedStringsTable sst;

    /**
     * 上一次的索引值
     */
    private String lastIndex;

    /**
     * 工作表索引
     */
    private int sheetIndex = -1;

    /**
     * 总行数
     */
    private int totalRows=0;

    /**
     * 一行内cell集合
     */
    private List<String> cellList = new ArrayList<String>();

    /**
     * 判断整行是否为空行的标记
     */
    private boolean flag = false;

    /**
     * 当前行
     */
    private int curRow = 0;

    /**
     * 当前列
     */
    private int curCol = 0;

    /**
     * T元素标识
     */
    private boolean isTElement;

    /**
     * 异常信息,如果为空则表示没有异常
     */
    private String exceptionMessage;

    /**
     * 单元格数据类型,默认为字符串类型
     */
    private CellDataType nextDataType = CellDataType.SSTINDEX;

    private final DataFormatter formatter = new DataFormatter();

    /**
     * 单元格日期格式的索引
     */
    private short formatIndex;

    /**
     * 日期格式字符串
     */
    private String formatString;

    //定义前一个元素和当前元素的位置,用来计算其中空的单元格数量,如A6和A8等
    private String preRef = null, ref = null;

    //定义该文档一行最大的单元格数,用来补全一行最后可能缺失的单元格
    private String maxRef = null;

    /**
     * 单元格
     */
    private StylesTable stylesTable;

    //数据处理接口
    private IExcelRowReader rowReader;

    public void setRowReader(IExcelRowReader rowReader) {
        this.rowReader = rowReader;
    }

    /**
     * 遍历工作簿中所有的电子表格
     * 并缓存在mySheetList中
     *
     * @param file csv文件(路径+文件)
     * @throws Exception
     */
    public void process(File file) throws Exception {
        OPCPackage pkg = OPCPackage.open(file);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        this.sst = sst;
        parser.setContentHandler(this);
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (sheets.hasNext()) { //遍历sheet
            curRow = 0; //标记初始行为第一行
            sheetIndex++;
            InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置,否则sheetName报错
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource); //解析excel的每条记录,在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
            sheet.close();
            break;
        }
    }

    /**
     * 第一个执行
     *
     * @param uri
     * @param localName
     * @param name
     * @param attributes
     * @throws SAXException
     */
    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        //c => 单元格
        if ("c".equals(name)) {
            //前一个单元格的位置
            if (preRef == null) {
                if (!("A"+(curRow+1)).equals(attributes.getValue("r"))){
                    for(int i=0;i<covertRowIdtoInt(attributes.getValue("r"))-1;i++){
                        cellList.add("");
                        curCol++;
                    }
                }
            }
            preRef = (preRef == null?attributes.getValue("r"):ref);
            //当前单元格的位置
            ref = attributes.getValue("r");
            if (!ref.equals(preRef)) {
                int len = countNullCell(ref, preRef);
                for (int i = 0; i < len; i++) {
                    cellList.add("");
                    curCol++;
                }
            }
            cellList.add("");
            curCol++;
            //设定单元格类型
            this.setNextDataType(attributes);
        }
        //当元素为t时
        if ("t".equals(name)) {
            isTElement = true;
        } else {
            isTElement = false;
        }
        //置空
        lastIndex = "";
    }

    /**
     * 第二个执行
     * 得到单元格对应的索引值或是内容值
     * 如果单元格类型是字符串、INLINESTR、数字、日期,lastIndex则是索引值
     * 如果单元格类型是布尔值、错误、公式,lastIndex则是内容值
     * @param ch
     * @param start
     * @param length
     * @throws SAXException
     */
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        lastIndex += new String(ch, start, length);
    }

    /**
     * 第三个执行
     *
     * @param uri
     * @param localName
     * @param name
     * @throws SAXException
     */
    @Override
    public void endElement(String uri, String localName, String name) throws SAXException {
        //t元素也包含字符串
        if (isTElement) {//这个程序没经过
            //将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
            String value = lastIndex.trim();
            cellList.set(curCol-1, value);
            isTElement = false;
            //如果里面某个单元格含有值,则标识该行不为空行
            if (value != null && !"".equals(value)) {
                flag = true;
            }
        } else if ("v".equals(name)) {
            //v => 单元格的值,如果单元格是字符串,则v标签的值为该字符串在SST中的索引
            String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
            if(StringUtils.isNotBlank(value)){
                cellList.set(curCol-1, value);
            }
            //如果里面某个单元格含有值,则标识该行不为空行
            if (value != null && !"".equals(value)) {
                flag = true;
            }
        } else {
            //如果标签名称为row,这说明已到行尾,调用optRows()方法
            if ("row".equals(name)) {
                //默认第一行为表头,以该行单元格数目为最大数目
                if (curRow == 0) {
                    maxRef = ref;
                }
                //补全一行尾部可能缺失的单元格
                if (maxRef != null) {
                    int len = countNullCell(maxRef, ref);
                    for (int i = 0; i <= len; i++) {
                        cellList.add("");
                    }
                }

                rowReader.getRows(sheetIndex, curRow, cellList);

                cellList.clear();
                curRow++;
                curCol = 0;
                preRef = null;
                ref = null;
            }
        }
    }

    /**
     * 处理数据类型
     *
     * @param attributes
     */
    public void setNextDataType(Attributes attributes) {
        nextDataType = CellDataType.NUMBER; //cellType为空,则表示该单元格类型为数字
        formatIndex = -1;
        formatString = null;
        String cellType = attributes.getValue("t"); //单元格类型
        String cellStyleStr = attributes.getValue("s"); //
        if ("b".equals(cellType)) { //处理布尔值
            nextDataType = CellDataType.BOOL;
        } else if ("e".equals(cellType)) {  //处理错误
            nextDataType = CellDataType.ERROR;
        } else if ("inlineStr".equals(cellType)) {
            nextDataType = CellDataType.INLINESTR;
        } else if ("s".equals(cellType)) { //处理字符串
            nextDataType = CellDataType.SSTINDEX;
        } else if ("str".equals(cellType)) {
            nextDataType = CellDataType.FORMULA;
        }
        if (cellStyleStr != null) { //处理日期
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            formatIndex = style.getDataFormat();
            formatString = style.getDataFormatString();
            if (formatIndex == 14 || formatIndex == 31 || formatIndex == 57 || formatIndex == 58){
                nextDataType = CellDataType.DATE;
                switch (formatIndex) {
                    case 14:
                        formatString = formatString.equals("m/d/yy")?"yyyy/MM/dd":"yyyy-MM-dd";
                        break;
                    case 31:
                        formatString = "yyyy年MM月dd日";
                        break;
                    case 57:
                        formatString = "yyyy年MM月";
                        break;
                    case 58:
                        formatString = "MM月dd日";
                        break;
                    default:
                        break;
                }
            } else if(StringUtils.isNotBlank(formatString)){
                if(formatString.contains("m/d/yy")){
                    nextDataType = CellDataType.DATE;
                    formatString = "yyyy-MM-dd hh:mm:ss";
                }
            } else {
                nextDataType = CellDataType.NULL;
                formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
            }
        }
    }

    /**
     * 对解析出来的数据进行类型处理
     * @param value   单元格的值,
     *                value代表解析:BOOL的为0或1, ERROR的为内容值,FORMULA的为内容值,INLINESTR的为索引值需转换为内容值,
     *                SSTINDEX的为索引值需转换为内容值, NUMBER为内容值,DATE为内容值
     * @param thisStr 一个空字符串
     * @return
     */
    public String getDataValue(String value, String thisStr) {
        switch (nextDataType) {
            // 这几个的顺序不能随便交换,交换了很可能会导致数据错误
            case BOOL: //布尔值
                char first = value.charAt(0);
                thisStr = first == '0' ? "FALSE" : "TRUE";
                break;
            case ERROR: //错误
                thisStr = "\"ERROR:" + value.toString() + '"';
                break;
            case FORMULA: //公式
                thisStr = '"' + value.toString() + '"';
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                thisStr = rtsi.toString();
                rtsi = null;
                break;
            case SSTINDEX: //字符串
                String sstIndex = value.toString();
                try {
                    int idx = Integer.parseInt(sstIndex);
                    XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
                    thisStr = rtss.toString();
                    rtss = null;
                } catch (NumberFormatException ex) {
                    thisStr = value.toString();
                }
                break;
            case NUMBER: //数字
                if (formatString != null) {
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
                } else {
                    thisStr = value.contains(".")?String.valueOf(Double.parseDouble(value)):value;
                }
                thisStr = thisStr.replace("_", "").trim();
                break;
            case DATE: //日期
                SimpleDateFormat targetFormat = new SimpleDateFormat(formatString);
                Calendar calendar = new GregorianCalendar(1900,0,-1);
                calendar.add(Calendar.DATE, Integer.valueOf(value));
                thisStr = targetFormat.format(calendar.getTime());
                thisStr = thisStr.replace("T", " ");
                break;
            default:
                thisStr = "";
                break;
        }
        return thisStr;
    }

    public int countNullCell(String ref, String preRef) {
        //excel2007最大行数是1048576,最大列数是16384,最后一列列名是XFD
        String xfd = ref.replaceAll("\\d+", "");
        String xfd_1 = preRef.replaceAll("\\d+", "");

        xfd = fillChar(xfd, 3, '@', true);
        xfd_1 = fillChar(xfd_1, 3, '@', true);

        char[] letter = xfd.toCharArray();
        char[] letter_1 = xfd_1.toCharArray();
        int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
        return res - 1;
    }

    public String fillChar(String str, int len, char let, boolean isPre) {
        int len_1 = str.length();
        if (len_1 < len) {
            if (isPre) {
                for (int i = 0; i < (len - len_1); i++) {
                    str = let + str;
                }
            } else {
                for (int i = 0; i < (len - len_1); i++) {
                    str = str + let;
                }
            }
        }
        return str;
    }

    /**
     * @return the exceptionMessage
     */
    public String getExceptionMessage() {
        return exceptionMessage;
    }

    /**
     * 列号转数字   AB7-->28 第28列
     * @param rowId
     * @return
     */
    public static int covertRowIdtoInt(String rowId){
        int firstDigit = -1;
        for (int c = 0; c < rowId.length(); ++c) {
            if (Character.isDigit(rowId.charAt(c))) {
                firstDigit = c;
                break;
            }
        }
        //AB7-->AB
        //AB是列号, 7是行号
        String newRowId = rowId.substring(0,firstDigit);
        int num = 0;
        int result = 0;
        int length = newRowId.length();
        for(int i = 0; i < length; i++) {
            //先取最低位,B
            char ch = newRowId.charAt(length - i - 1);
            //B表示的十进制2,ascii码相减,以A的ascii码为基准,A表示1,B表示2
            num = (int)(ch - 'A' + 1) ;
            //列号转换相当于26进制数转10进制
            num *= Math.pow(26, i);
            result += num;
        }
        return result;

    }
}

2019-11-02 15:36:01 qq_28603127 阅读数 123
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1271 人正在学习 去看看 钟林森


  百度了好久关于解析excel的内容都找不到自己想要的东西,所以希望跟我有一样需求的人,能够因为这篇文章少走弯路.
  
  excel有两种格式,一种xls格式(97),一种xlsx格式(07). 提到excel API可能首先想到的是POI,使用POI能够读写所有的excel,但是POI针对于每种格式的excel分了好几种模式,UserModel EventModel UserEventModel

在这里插入图片描述
  本人使用usermodel解析excel,10M大小的文件,就会OOM,因为usermodel模式是一次性加载所有的内容到内存,每个Cell就是一个对象,导致内存被撑爆.对于内存要求比较高或者说是文件比较大(10M也算大?)的场景,应该使用EventModel或者UserEventModel的API(我没研究,官网给的东西比较难看).这两种API比较复杂.
  点击前往POI excel官网
  
  因为本人的需求是解析xlsx格式的excel,针对于读取/解析excel的需求,POI不是唯一选择 , Streaming-Reader是对于POI的再次封装,并且简单易懂,虽然只能针对于xlsx格式的excel读取/解析(xls跟xlsx底层本质是不一样的),但是针对于这种需求,可以完美的解决掉爆内存的问题.
  
  解决内存问题的关键就是使用流式处理,读取一批数据解析完后就释放,再进行下一批,Streaming-Reader就是使用这种方式.

        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
            <version>2.0.0</version>
        </dependency>

下面是示例代码

import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;

public class Test {
    public static void main(String[] args) throws Exception {
    
        FileInputStream in = new FileInputStream(new File("src/190917_MEAC Aug Database - v1.xlsx"));
        Workbook open = StreamingReader.builder()
                .rowCacheSize(100)//一次读取多少行(默认是10行)
                .bufferSize(1024)//使用的缓冲大小(默认1024)
                .open(in);
        for (Sheet sheet : open) {
            for (Row row : sheet) {
                for (Cell cell : row) {

                }
            }
        }
    }
}

基本逻辑就是这样子,是不是很简单? 可以根据sheet的名字取出特定sheet,但是无法指定具体的rownumber来获取行,因为这是基于流的方式读取excel内容,流的单位是.rowCacheSize(100)指定的行数,我们不可能在当前100行的流中,去指定第101行的Row.至于其他的API 可以自行翻看源码,源码中的内容通俗易懂.

下面贴出来的是我在解析中的一个实例.


public class DataImport {

    public static void main(String[] args) throws FileNotFoundException, SQLException {
        DataImportJdbc dataImportJdbc = new DataImportJdbc();
        HashMap<String, String> excDBMapping = dataImportJdbc.readMapping("smme");
        Map<String, Integer> fieldsIndexMapping = new HashMap<String, Integer>();
        FileInputStream in = new FileInputStream(new File("src/190917_MEAC Aug Database - v1.xlsx"));
        Workbook open = StreamingReader.builder()
                .rowCacheSize(100)
                .bufferSize(1024)
                .open(in);
        HashMap<String, Integer> monthMapping = SMMEFields.monthMapping;
        HashMap<String, Integer> realMonthMapping = new HashMap<>();
        StreamingSheet sheet = (StreamingSheet) open.getSheet("Raw");
        for (Row row : sheet) {
            if (row.getRowNum() == 0) {
                for (Cell cell : row) {
                    String stringCellValue = cell.getStringCellValue().toLowerCase();
                    if (excDBMapping.containsKey(stringCellValue)) {
                        fieldsIndexMapping.put(excDBMapping.get(stringCellValue), cell.getColumnIndex());
                    } else if (monthMapping.containsKey(stringCellValue)) {
                        if("year".equals(stringCellValue)) {
                            realMonthMapping.put(stringCellValue, cell.getColumnIndex());
                        }else{
                            String month = MonthMatch.SMMEMonthMatch(stringCellValue);
                            realMonthMapping.put(month, cell.getColumnIndex());
                        }
                    }
                }
                System.out.println(realMonthMapping);
            } else {
                Integer countryIndex = fieldsIndexMapping.get("country");
                String country = row.getCell(countryIndex).getStringCellValue();
                if (SMMEFields.countryList.contains(country)) {
                    System.out.println(country);
                    continue;
                }
                HashMap<String, String> stringStringHashMap = new HashMap<String, String>();
                for (String key : fieldsIndexMapping.keySet()) {
                    Cell cell = row.getCell(fieldsIndexMapping.get(key));
                    String stringCellValue = cell.getStringCellValue();
                    stringStringHashMap.put(key, stringCellValue);
                }
                for (String key : realMonthMapping.keySet()) {
                    if (!"year".equals(key) && realMonthMapping.get(key) != null) {
                        Integer integer = realMonthMapping.get(key);
                        Cell cell = row.getCell(integer);
                        String volume = cell.getStringCellValue();
                        if (volume.trim().isEmpty() || volume.length() == 0 || volume.equals("- 0")) {
                            volume = "0";
                        } else if (volume.contains(",")) {
                            volume = volume.replace(",", "");
                        }
                        String year = row.getCell(realMonthMapping.get("year")).getStringCellValue();
                        String yearMonth = year + key;
                        String time = DateUtils.getStringTime();
                        OneData oneData = new OneData();
                        oneData.setMap(stringStringHashMap);
                        oneData.setTime(time);
                        oneData.setYearmonth(yearMonth);
                        oneData.setVolume(volume);
                        String id = DigestUtils.md5DigestAsHex(oneData.toString().getBytes());
                        oneData.setId(id);
                        dataImportJdbc.upsertData(oneData);
                    }
                }
            }
        }
    }
}

不用揣想上面的代码中我要做什么,只需要看基本的逻辑,以及API的使用就好.

如果使用中出现异常

Exception in thread "main" java.lang.UnsupportedOperationException
	at com.monitorjbl.xlsx.impl.StreamingSheet.getFirstRowNum(StreamingSheet.java:118)
	at com.parsh.Test.main(Test.java:21)

那是因为源码方法中没有逻辑内容,直接通过Throw来抛出了这个异常.看到这个异常就是说明,有这个方法,但是功能没实现,请不要使用这个方法的意思.

2011-03-31 13:23:10 aeoluswind 阅读数 19
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1271 人正在学习 去看看 钟林森
[url=http://www.neverevernote.com/?p=17]excel的大数据量用POI写入 香菜个人博客[/url]

昨天刚解决了POI的大数据量写,今天又碰到一个问题,客户把50万的数据用excel传了过来T.T
普通的读取依然会OOM

无奈继续查资料。记得看到一篇文章上说XSSF默认用DOM解析XML的,那必然会OOM了,于是开始找用SAX读的例子,找到了应该比较经典的FromHowTo.java(http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/examples/FromHowTo.java)

用这个例子还是碰到了挺多问题
1. 写入的逻辑会嵌入到这个读取里..它并没有提供比较友好的getRow之类的一行一行处理的方法..导致我的代码写地很猥琐..
2. SharedStringsTable sst = r.getSharedStringsTable();这段代码依然会抛OOM. 看提问频道有一个哥们也碰到了同样的问题..都已经用SAX解析了还是OOM是挺尴尬的..目前的解决方法是开大了一些内存..- -..简单有效..基本上代码跑过了这行以后的读取就不怕OOM了..
2011-12-13 16:37:27 javaedge3 阅读数 446
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1271 人正在学习 去看看 钟林森

 工作当中遇到要读取大数据量Excel(10万行以上,Excel 2007),用POI方式读取,用HSSFWorkbook读取时,超过2万行JVM的内存就会溢出,在网上找到原来要用XML方式逐行读取,记录下来,以供参考。

    注意:运行环境是jdk1.6,如果要在1.5的环境中运行,要把jdk1.6中的rt.jar中javax.xml包下所有类加到运行的环境中。

    下面是代码:

package com.bill.excel;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

public class ExcelUtil extends DefaultHandler {
	
	private SharedStringsTable sst;
	private String lastContents;
	private boolean nextIsString;

	private int sheetIndex = -1;
	private List<String> rowlist = new ArrayList<String>();
	private int curRow = 0;
	private int curCol = 0;
	
	
	/**
	 * 读取第一个工作簿的入口方法
	 * @param path
	 */
	public void readOneSheet(String path) throws Exception {
		OPCPackage pkg = OPCPackage.open(path);		
		XSSFReader r = new XSSFReader(pkg);
		SharedStringsTable sst = r.getSharedStringsTable();
			
		XMLReader parser = fetchSheetParser(sst);
			
		InputStream sheet = r.getSheet("rId1");

		InputSource sheetSource = new InputSource(sheet);
		parser.parse(sheetSource);
			
		sheet.close();		
	}
	
	
	/**
	 * 读取所有工作簿的入口方法
	 * @param path
	 * @throws Exception
	 */
	public void process(String path) throws Exception {
		OPCPackage pkg = OPCPackage.open(path);
		XSSFReader r = new XSSFReader(pkg);
		SharedStringsTable sst = r.getSharedStringsTable();

		XMLReader parser = fetchSheetParser(sst);

		Iterator<InputStream> sheets = r.getSheetsData();
		while (sheets.hasNext()) {
			curRow = 0;
			sheetIndex++;
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
		}
	}
	
	/**
	 * 该方法自动被调用,每读一行调用一次,在方法中写自己的业务逻辑即可
	 * @param sheetIndex 工作簿序号
	 * @param curRow 处理到第几行
	 * @param rowList 当前数据行的数据集合
	 */
	public void optRow(int sheetIndex, int curRow, List<String> rowList) {
		String temp = "";
		for(String str : rowList) {
			temp += str + "_";
		}
		System.out.println(temp);
	}
	
	
	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser = XMLReaderFactory
				.createXMLReader("org.apache.xerces.parsers.SAXParser");
		this.sst = sst;
		parser.setContentHandler(this);
		return parser;
	}
	
	public void startElement(String uri, String localName, String name,
			Attributes attributes) throws SAXException {
		// c => 单元格
		if (name.equals("c")) {
			// 如果下一个元素是 SST 的索引,则将nextIsString标记为true
			String cellType = attributes.getValue("t");
			if (cellType != null && cellType.equals("s")) {
				nextIsString = true;
			} else {
				nextIsString = false;
			}
		}
		// 置空
		lastContents = "";
	}
	
	
	public void endElement(String uri, String localName, String name)
			throws SAXException {
		// 根据SST的索引值的到单元格的真正要存储的字符串
		// 这时characters()方法可能会被调用多次
		if (nextIsString) {
			try {
				int idx = Integer.parseInt(lastContents);
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
						.toString();
			} catch (Exception e) {

			}
		}

		// v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引
		// 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
		if (name.equals("v")) {
			String value = lastContents.trim();
			value = value.equals("") ? " " : value;
			rowlist.add(curCol, value);
			curCol++;
		} else {
			// 如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法
			if (name.equals("row")) {
				optRow(sheetIndex, curRow, rowlist);
				rowlist.clear();
				curRow++;
				curCol = 0;
			}
		}
	}

	public void characters(char[] ch, int start, int length)
			throws SAXException {
		// 得到单元格内容的值
		lastContents += new String(ch, start, length);
	}

}

 

2012-09-06 11:58:43 kjkhi 阅读数 181
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1271 人正在学习 去看看 钟林森

POI读取Excel文件有两种方式,一种是使用usermodel方式读取,这种方式的优势是统一接口开发,读取.xls文件的HSSFWorkbook与读取.xlsx文件的XSSFWorkbook两个类都实现了Workbook接口。另外一种是eventusermodel方式读取,这种方式比前面一种方式读取复杂很多,并且对与2003版本和2007版本的Excel处理没有统一接口,需要了解Excel的内部文件组织原理,但是效率却比第一种方式快得多,并却能轻松读取大量数据的Excel文件,而不会把内存溢出。本文也是主要介绍使用eventusermodel方式读取2007版本的Excel文件。

eventusermodel其实是使用了XML的文件格式读取Excel的,因为Excel内部组织也是通过XML实现了(可以把后缀名改为.zip)。

xl\worksheets\sheet1.xml  - 第一个sheet的内容

 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"><dimension ref="A1:D1"/>
<sheetViews><sheetView workbookViewId="0"><selection sqref="A1:XFD1"/></sheetView></sheetViews>
<sheetFormatPr defaultRowHeight="13.5" x14ac:dyDescent="0.15"/>
	<sheetData>
		<row r="1" spans="1:4" x14ac:dyDescent="0.15">
			<c r="A1" t="s"><v>0</v></c>
			<c r="B1" t="s"><v>1</v></c>
			<c r="C1" t="s"><v>2</v></c>
			<c r="D1" t="s"><v>15</v></c>
		</row>
	</sheetData>
<phoneticPr fontId="1" type="noConversion"/><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/><pageSetup paperSize="0" orientation="portrait" horizontalDpi="0" verticalDpi="0" copies="0"/></worksheet>

<c />标签表示单元格,t=“s”,说明当前的单元格类型是字符串,那此时<v>0</v>,0则是在sharedStrings.xml的一个索引值。是<si />标签序号。 

 

xl\sharedStrings.xml - Excel文件中字符串的值,如其内容片段

 

<si>
	<t>col1</t><phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
	<t>col2</t><phoneticPr fontId="1" type="noConversion"/>
</si>

 POI的eventusermodel也是通过这样的原理读取Excel文件的。

首先读取Excel文件,取得XSSFReader实例:

 

XSSFReader reader =  new XSSFReader(OPCPackage.open(file));
XMLReader xmlReader = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");

// sharedStrings.xml实体
SharedStringsTable table = reader.getSharedStringsTable();

xmlReader.setContentHandler(new ContentHandler()//实现该接口的一个实例);

InputStream sheet = reader.getSheet("rId"+sheetId);

InputSourcesheetSource  =  new InputSource(sheet )

xmlReader.parse(sheetSource);
 
package net.bingosoft.import4excel.common;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

public class TestContentHandler extends DefaultHandler{

	private SharedStringsTable table;
	
	private boolean isString;
	
	private String value;
	
	private FileWriter writer;
	
	public TestContentHandler(SharedStringsTable table){
		this.table = table;
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#characters(char[], int, int)
	 */
	@Override
	public void characters(char[] ch, int start, int length)
			throws SAXException {
		value = new String(ch,start,length);
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#endDocument()
	 */
	@Override
	public void endDocument() throws SAXException {
		try {
			writer.flush();
			writer.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public void endElement(String uri, String localName, String qName)
			throws SAXException {
		try {
			if(qName.equals("v")){
				if(isString) value = table.getEntryAt(Integer.valueOf(value.trim())).getT();
				writer.write(value+",");
			}
			if(qName.equals("row")){
				writer.write("\r\n");
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#startDocument()
	 */
	@Override
	public void startDocument() throws SAXException {
		try {
			File file = new File("D:/test.txt");
			if(file.exists()) file.delete();
			writer = new FileWriter(file);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
	 */
	@Override
	public void startElement(String uri, String localName, String qName,
			Attributes attributes) throws SAXException {
		if(qName.equals("c")){
			String type = attributes.getValue("t");
			if(StringUtils.isNotBlank(type) && type.equals("s")){
				isString = true;
			}else{
				isString = false;
			}
		}
		value = "";
	}
	
	
}

 

没有更多推荐了,返回首页