Excel---导出与读取（大数据量）
2024-09-27 16:10:54
Excel下载
首先大数据量的下载，一般的Excel下载操作是不可能完成的，会导致内存溢出
SXSSFWorkbook 是专门用于大数据了的导出
构造入参rowAccessWindowSize
这个参数，会指定一个sheet可读取的row数目，超过该数目的row，会被写入到磁盘文件中，进而不能在通过getRow访问到，通过这种方式，内存使用空间就缩小很多了。 需要注意的是，如果该值指定为-1，说明不限制行数，所有记录都写入内存中；该值不能取0，因为这意味着任何新row都会写入磁盘，进而不能访问，但是新row还没来得及createCell
setCompressTempFiles方法
当写sheet的时候，临时文件可能远比结果文件要大，所以提供了压缩临时文件的接口，通过压缩，磁盘上的xml的临时文件将会被打包，进而降低磁盘占用空间。
先上代码再说
/**
* Excel通用下载方法
* @param request
* @param response
* @param fileName
* 文件名
* @param titleNameList
* 标题头部
* @param cellNameList
* 行字段的name，主要用于map的get(),无则为null
* @param dataList
* 内容 支持类型 对象 map集合
*/
public static <E> void downLoad(HttpServletRequest request, HttpServletResponse response, String fileName,
List<String> titleNameList,List<String> cellNameList, List<E> dataList) {
OutputStream os = null;
try {
response.setContentType("application/force-download"); // 设置下载类型 response.setHeader("Content-Disposition", "attachment;filename=" + fileName); // 设置文件的名称
os = response.getOutputStream(); // 输出流
SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 内存中保留 1000 条数据，以免内存溢出，其余写入 硬盘
// 获得该工作区的第一个sheet
Sheet sheet1 = wb.createSheet("sheet1");
String mapstring=null;
int excelRow = 0;
List<String> fieldNameList = new ArrayList<>();
// 标题行
Row titleRow = (Row) sheet1.createRow(excelRow++);
int titleSize = titleNameList.size();
for (int i = 0; i < titleSize; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellValue(titleNameList.get(i));
}
int dataSize = dataList.size();
for (int i = 0; i < dataSize; i++) {
// 明细行
Row contentRow = (Row) sheet1.createRow(excelRow++);
Object object = dataList.get(i);
if (object instanceof Map) {
Map<String, Object> objectMap = (Map<String, Object>) object;
for (int j = 0; j < titleSize; j++) {
mapstring=objectMap.get(cellNameList.get(j))+"";
Cell cell = contentRow.createCell(j);
if (!"".equals(mapstring)&&mapstring!=null&&!"null".equals(mapstring)) {
cell.setCellValue(mapstring);
}else {
cell.setCellValue("");
}
}
} else { if (i == 0) {
if (cellNameList!=null&&cellNameList.size()>0) {
fieldNameList.addAll(cellNameList);
}else {
Field[] fields = object.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
fieldNameList.add(field.getName());
}
}
}
for (int j = 0; j < titleSize; j++) {
Cell cell = contentRow.createCell(j);
Field field = object.getClass().getDeclaredField(fieldNameList.get(j));
field.setAccessible(true);
Object fieldObj=field.get(object) ;
if (fieldObj != null&&!"null".equals(fieldObj)) {
cell.setCellValue(fieldObj.toString());
} else {
cell.setCellValue("");
} } }
}
wb.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (os != null) {
os.close();
}
} catch (IOException e) {
e.printStackTrace();
} // 关闭输出流
} }
读取大数据的Excel文件
直接上代码
public class SheetHandler extends DefaultHandler {
/**
* 单元格中的数据可能的数据类型
*/
enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}
/**
* 共享字符串表
*/
private SharedStringsTable sst;
/**
* 上一次的索引值
*/
private String lastIndex;
/**
* 文件的绝对路径
*/
private String filePath = "";
/**
* 工作表索引
*/
private int sheetIndex = 0;
/**
* sheet名
*/
private String sheetName = "";
/**
* 总行数
*/
private int totalRows=0;
/**
* 一行内cell集合
*/
private List<String> cellList = new ArrayList<String>();
/**
* 判断整行是否为空行的标记
*/
private boolean flag = false;
/**
* 当前行
*/
private int curRow = 1;
/**
* 当前列
*/
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 int maxCol=0;
private int nowcol;
/**
* 单元格
*/
private StylesTable stylesTable;
/**
* 日期格式化 yyyy-MM-dd
*/
private SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
/**
* 日期格式化
*/
private SimpleDateFormat sdf2=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* 现在的时间
*/
private String dateTime=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
/**
* 一个表格的所有数据
*/
private List<List<String>> sheetList=new ArrayList<>();
/**
* 操作人员
*/
private String userName;
private static int startElements=0;
private static int endElement=0;
/* public static void main(String[] args) {
SheetHandler sheetHandler=new SheetHandler();
File file=new File("C:\\Users\\Administrator\\Desktop\\jichu.xlsx");
try {
sheetHandler.process(new FileInputStream(file), "周光林");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}*/
/**
* 遍历工作簿中所有的电子表格
* 并缓存在mySheetList中
*
* @param filename
* @throws Exception
*/
public List<List<String>> process(InputStream in,String userName) throws Exception {
this.userName=userName;
OPCPackage pkg = OPCPackage.open(in);
XSSFReader xssfReader = new XSSFReader(pkg);
stylesTable = xssfReader.getStylesTable();
SharedStringsTable sst = xssfReader.getSharedStringsTable();
XMLReader parser = XMLReaderFactory.createXMLReader();
this.sst = sst;
parser.setContentHandler(this);
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
while (sheets.hasNext()) { //遍历sheet
curRow = 1; //标记初始行为第一行
sheetIndex++;
InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
sheetName = sheets.getSheetName();
// System.err.println(new BufferedReader(new InputStreamReader(sheet)).readLine());
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
sheet.close();
}
if (in!=null) {
in.close();
}
return sheetList; //返回该excel文件的总行数，不包括首列和空行
}
/**
* 第一个执行
*
* @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)) {
//当前单元格的位置
ref = attributes.getValue("r");
//设定单元格类型
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.add(curCol, value);
curCol++;
isTElement = false;
//如果里面某个单元格含有值，则标识该行不为空行
if (value != null && !"".equals(value)) {
flag = true;
}
} else if ("v".equals(name)) {
//v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
//补全单元格之间的空单元格
if (preRef!=null) {
if (!ref.equals(preRef)) {
int len = countNullCell(ref, preRef);
for (int i = 0; i < len; i++) {
cellList.add(curCol, "");
curCol++;
}
}
}
preRef=ref;
cellList.add(curCol, value);
curCol++;
//如果里面某个单元格含有值，则标识该行不为空行
if (value != null && !"".equals(value)) {
flag = true;
}
} else {
//如果标签名称为row，这说明已到行尾，调用optRows()方法
if ("row".equals(name)) {
//默认第一行为表头，以该行单元格数目为最大数目
if (curRow == 1) {
maxRef = ref;
maxCol=curCol;
}
//补全一行尾部可能缺失的单元格
if (maxRef != null) {
int len = countNullCell(maxRef, ref);
for (int i = 0; i <= len; i++) {
cellList.add(curCol, "");
curCol++;
}
}
if (flag&&curRow!=1){ //该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）
nowcol=cellList.size();
if (cellList.size()<maxCol) {
for (int i = 0; i <maxCol-nowcol; i++) {
cellList.add("");
}
}
if (nowcol>maxCol) {
for (int i = nowcol-1; i >maxCol-1; i--) {
cellList.remove(i);
}
}
cellList.add(userName);
cellList.add(dateTime);
sheetList.add(new ArrayList<>(cellList));
totalRows++;
}
cellList.clear();
curRow++;
curCol = 0;
preRef = null;
ref = null;
flag=false;
}
}
}
/**
* 处理数据类型
*
* @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"); //
String columnData = attributes.getValue("r"); //获取单元格的位置，如A1,B1
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 (formatString == null) {
nextDataType = CellDataType.NULL;
formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
}else if (formatString.contains("m/d/yy") || formatString.contains("yyyy/mm/dd")|| formatString.contains("yyyy/m/d")) {
nextDataType = CellDataType.DATE;
formatString = "yyyy-MM-dd";
}
}
}
/**
* 对解析出来的数据进行类型处理
* @param value 单元格的值，
* value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
* SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
* @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 = 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;
}
thisStr = thisStr.replace("_", "").trim();
break;
case DATE: //日期
try {
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
}catch (Exception e) {
thisStr="0000-00-00";
}
// 对日期字符串作特殊处理，去掉T
thisStr = thisStr.replace("T", " ");
break;
default:
if (Integer.parseInt(value)>20000) {
Date date = DateUtil.getJavaDate(Double.parseDouble(value));
thisStr =sdf.format(date);
}else {
thisStr="11111";
}
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;
}
}
SheetHandler sheetHandler=new SheetHandler(); List<List<String>> lists=sheetHandler.process(in,userName);
最新文章
- MMORPG大型游戏设计与开发（服务器 游戏场景 地图和区域）
- ES6的Iterator,jquery Fn
- JavaScript的一些知识碎片(2)-反射-全局变量-回调
- Java面试知识的认知描述
- Composer ： php依赖管理工具
- 如何在 IIS 中设置 HTTPS 服务
- Java SE 第二十三讲----static关键字and final关键字
- 【tcl脚本】改变输出字符格式
- bzoj4177: Mike的农场
- 远程测试mysql数据库3306端口报错
- 利用css3-animation来制作逐帧动画
- 在Windows下编译OpenSSL（VS2005和VC6）
- 使用类似于中介者模式实现不同VC之间的跳转
- ASP.NET MVC 设置区域默认定向
- C#图解教程 第十七章 泛型
- 深度剖析linux内核万能--双向链表,Hash链表模版
- linux入门--操作系统是什么，操作系统概述
- sass不识别中文字符的问题
- javascript 中 if (window != top) top.location.href = location.href;的意思
- ASP.NET添加Mysql数据源