excel内存溢出

时间：2024-02-21 01:01:41编辑：优化君

EXCEL查看代码是显示“内存溢出”，怎么回事？

office软件是64位的，从别人电脑考过来一个带宏的文件过来，你运行之后，发现出现这种情况了，关键是别人电脑是32位的，由于64位有字节限制，一下就给你的原来的软件搞坏了。解决方法就是重装一遍软件”。具体如下：1、简介EXCEL表处理软件是美国微软公司研制的办公自动化软件OFFICE 中的重要成员，经过多次改进和升级，最新版本为EXCEL2000。它能够方便的制作出各种电子表格，使用公式和函数对数据进行复杂的运算;用各种图表来表示数据直观明了;利用超级链接功能，用户可以快速打开局域网或Interner上的文件，与世界上任何位置的互联网用户共享工作薄文件。2、软件Excel中大量的公式函数可以应用选择，使用Microsoft Excel可以执行计算，分析信息并管理电子表格或网页中的数据信息列表，可以实现许多方便的功能，带给使用者方便。与其配套组合的有：Word、PowerPoint、Access、InfoPath及Outlook,Publisher3、百科名片Microsoft Excel是微软公司的办公软件Microsoft office的组件之一，是由Microsoft为Windows和Apple Macintosh操作系统的电脑而编写和运行的一款试算表软件。Excel 是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。

EXCEL查看代码是显示“内存溢出”

打开excel出现灾难性错误并提示内存溢出的解决方法

POI的诞生解决了Excel的解析难题（POI即“讨厌的电子表格”），但如果用不好POI，也会导致程序出现一些BUG，例如内存溢出，假空行，公式等等问题。下面介绍一种解决POI读取Excel内存溢出的问题。
POI读取Excel有两种模式，一种是用户模式，一种是SAX模式，将xlsx格式的文档转换成CVS格式后再进行处理用户模式相信大家都很清楚，也是POI常用的方式，用户模式API接口丰富，我们可以很容易的使用POI的API读取Excel，但用户模式消耗的内存很大，当遇到很多sheet、大数据网格、假空行、公式等问题时，很容易导致内存溢出。POI官方推荐解决内存溢出的方式使用CVS格式解析，我们不可能手工将Excel文件转换成CVS格式再上传，这样做太麻烦了，好再POI给出了xlsx转换CVS的例子，基于这个例子进行了一下改造，即可解决用户模式读取Excel内存溢出的问题。下面附上代码:
[java] view plaincopy
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.
==================================================================== */

import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
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;

/**
* 使用CVS模式解决XLSX文件，可以有效解决用户模式内存溢出的问题
* 该模式是POI官方推荐的读取大数据的模式，在用户模式下，数据量较大、Sheet较多、或者是有很多无用的空行的情况
* ，容易出现内存溢出,用户模式读取Excel的典型代码如下： FileInputStream file=new
* FileInputStream("c:\\test.xlsx"); Workbook wb=new XSSFWorkbook(file);
*
*
* @author 山人
*/
public class XLSXCovertCSVReader {

/**
* The type of the data value is indicated by an attribute on the cell. The
* value is usually in a "v" element within the cell.
*/
enum xssfDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
}

/**
* 使用xssf_sax_API处理Excel,请参考： http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api
*
* Also see Standard ECMA-376, 1st edition, part 4, pages 1928ff, at
* http://www.ecma-international.org/publications/standards/Ecma-376.htm
*
* A web-friendly version is http://openiso.org/Ecma/376/Part4
*/
class MyXSSFSheetHandler extends DefaultHandler {

/**
* Table with styles
*/
private StylesTable stylesTable;

/**
* Table with unique strings
*/
private ReadOnlySharedStringsTable sharedStringsTable;

/**
* Destination for data
*/
private final PrintStream output;

/**
* Number of columns to read starting with leftmost
*/
private final int minColumnCount;

// Set when V start element is seen
private boolean vIsOpen;

// Set when cell start element is seen;
// used when cell close element is seen.
private xssfDataType nextDataType;

// Used to format numeric cell values.
private short formatIndex;
private String formatString;
private final DataFormatter formatter;

private int thisColumn = -1;
// The last column printed to the output stream
private int lastColumnNumber = -1;

// Gathers characters as they are seen.
private StringBuffer value;
private String[] record;
private List rows = new ArrayList();
private boolean isCellNull = false;

/**
* Accepts objects needed while parsing.
*
* @param styles
* Table of styles
* @param strings
* Table of shared strings
* @param cols
* Minimum number of columns to show
* @param target
* Sink for output
*/
public MyXSSFSheetHandler(StylesTable styles,
ReadOnlySharedStringsTable strings, int cols, PrintStream target) {
this.stylesTable = styles;
this.sharedStringsTable = strings;
this.minColumnCount = cols;
this.output = target;
this.value = new StringBuffer();
this.nextDataType = xssfDataType.NUMBER;
this.formatter = new DataFormatter();
record = new String[this.minColumnCount];
rows.clear();// 每次读取都清空行集合
}

/*
* (non-Javadoc)
*
* @see
* org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String,
* java.lang.String, java.lang.String, org.xml.sax.Attributes)
*/
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {

if ("inlineStr".equals(name) || "v".equals(name)) {
vIsOpen = true;
// Clear contents cache
value.setLength(0);
}
// c => cell
else if ("c".equals(name)) {
// Get the cell reference
String r = attributes.getValue("r");
int firstDigit = -1;
for (int c = 0; c < r.length(); ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
thisColumn = nameToColumn(r.substring(0, firstDigit));

// Set up defaults.
this.nextDataType = xssfDataType.NUMBER;
this.formatIndex = -1;
this.formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
if ("b".equals(cellType))
nextDataType = xssfDataType.BOOL;
else if ("e".equals(cellType))
nextDataType = xssfDataType.ERROR;
else if ("inlineStr".equals(cellType))
nextDataType = xssfDataType.INLINESTR;
else if ("s".equals(cellType))
nextDataType = xssfDataType.SSTINDEX;
else if ("str".equals(cellType))
nextDataType = xssfDataType.FORMULA;
else if (cellStyleStr != null) {
// It's a number, but almost certainly one
// with a special style or format
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
this.formatString = style.getDataFormatString();
if (this.formatString == null)
this.formatString = BuiltinFormats
.getBuiltinFormat(this.formatIndex);
}
}

}

/*
* (non-Javadoc)
*
* @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String,
* java.lang.String, java.lang.String)
*/
public void endElement(String uri, String localName, String name)
throws SAXException {

String thisStr = null;

// v => contents of a cell
if ("v".equals(name)) {
// Process the value contents as required.
// Do now, as characters() may be called more than once
switch (nextDataType) {

case BOOL:
char first = value.charAt(0);
thisStr = first == '0' ? "FALSE" : "TRUE";
break;

case ERROR:
thisStr = "\"ERROR:" + value.toString() + '"';
break;

case FORMULA:
// A formula could result in a string value,
// so always add double-quote characters.
thisStr = '"' + value.toString() + '"';
break;

case INLINESTR:
// TODO: have seen an example of this, so it's untested.
XSSFRichTextString rtsi = new XSSFRichTextString(
value.toString());
thisStr = '"' + rtsi.toString() + '"';
break;

上一篇：成都航空职业技术学院怎么样

下一篇：统计学基础知识点