工作中有个报表需要导出，报表行不是很多，但列很多,大约有1000个总列,总列下有3个子列，如下所示：
原来的导出是针对Excel 2003的，数据写入到多个sheet页,但客户对结果不满意，因为
(1)数据不在一个Sheet使用不方便,众所周知，Excel 2003对列数有限制，3000个子列不可能放在一个Sheet页中的。
(2)数据中有很多0,客户要求总列下面子列数据全部为0则不显示，也就是总列下面所有行如果全部为0则不显示。
(3)客户对报表时间限制只能查询1个月不满意，因为源表按月份分表，所以限制了只能查询一个月数据
原方案没有采用前台js导出是因为前台是分页显示的，要求导出全部数据，所以没有采用,提前先导出的方法也不可行，因为不知道客户选择的条件是什么,项目中导出是一行一行写的，不可能边写边删(没写完之前不知道那些列满足条件)，写csv文件目前客户也没同意，所以没采用。
在数据库提前把值为0的干掉目前我还不会写，因为项目中的大量采用了sum(decode(name,'101',1,0))这种形式，关联查询了很多表。
和客户沟通后，客户可以接受导出Excel 2007，但还是要求总列下数据为0的不显示。该优化需求交给了另一位同事。下面是我自己模拟做的，项目目前没有采用我这种方法,因为我这种做法效率不行。
优化方案见我的另一篇博客：
http://53873039oycg.iteye.com/blog/2036889
我自己想的方案(最简单)是:
1)先导出Excel 2007数据。
2)找出总列下子列全部为0的列
3)删除该列,保存Excel。
(一)导出Excel
这没什么难度。先写表头，在插入数据。
public int[] createExcelHeader(XSSFWorkbook wb, XSSFSheet sheet,
Map<String, XSSFCellStyle> styles, int subTotal) throws Exception {
List<String> header = new ArrayList<String>();
int[] headInfo = new int[3];
XSSFCellStyle titleStyle = null;
header = new ArrayList<String>();
// 表头_行数_列数$
header.add("编号#2#1$");
header.add("MSN#2#1$");
header.add("一级标题#2#1$");
header.add("二级标题#2#1$");
header.add("品牌名#2#1$");
header.add("型号#2#1$");
header.add("颜色#2#1$");
header.add("性质#2#1$");
header.add("状态#2#1$");
headInfo[1] = header.size();
for (int i = 1; i < subTotal; i++) {
header.add("下游合作商_" + i + "#1#3$数量#1#1$");
header.add("下游合作商_" + i + "#1#3$金额#1#1$");
header.add("下游合作商_" + i + "#1#3$小计#1#1$");
}
headInfo[2] = 3;
header.add("总数量#2#1$");
header.add("总金额#2#1$");
header.add("总计#2#1$");
headInfo[0] = header.size();
titleStyle = styles.get("sheet_title");
// 为某一位sheet填充数据
createSheetTitle(sheet, header, titleStyle);
titleStyle = null;
header = null;
return headInfo;
}
样式:
// 创建样式库
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
Map<String, XSSFCellStyle> stylesMap = new HashMap<String, XSSFCellStyle>();
XSSFCellStyle style = null;
XSSFFont sheetFont = wb.createFont();
sheetFont.setColor(IndexedColors.WHITE.getIndex());
sheetFont.setFontName("楷体");
sheetFont.setFontHeightInPoints((short) 10);
sheetFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setFont(sheetFont);
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
style.setBorderTop(XSSFCellStyle.BORDER_DOTTED);
style.setTopBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderLeft(XSSFCellStyle.BORDER_DOTTED);
style.setLeftBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderRight(XSSFCellStyle.BORDER_DOTTED);
style.setRightBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderBottom(XSSFCellStyle.BORDER_DOTTED);
style.setBottomBorderColor(IndexedColors.WHITE.getIndex());
stylesMap.put("sheet_title", style);
sheetFont = wb.createFont();
sheetFont.setColor(IndexedColors.WHITE.getIndex());
sheetFont.setFontName("楷体");
sheetFont.setFontHeightInPoints((short) 10);
sheetFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setFont(sheetFont);
style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
style.setBorderTop(XSSFCellStyle.BORDER_DOTTED);
style.setTopBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderLeft(XSSFCellStyle.BORDER_DOTTED);
style.setLeftBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderRight(XSSFCellStyle.BORDER_DOTTED);
style.setRightBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderBottom(XSSFCellStyle.BORDER_DOTTED);
style.setBottomBorderColor(IndexedColors.WHITE.getIndex());
stylesMap.put("sheet_title_style", style);
style = wb.createCellStyle();
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
stylesMap.put("cell_center", style);
style = wb.createCellStyle();
XSSFDataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("0"));
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
stylesMap.put("cell_long", style);
return stylesMap;
}
(二)插入数据，这里是随机数据，项目中是从数据库查询得到的。
/**
*
* @param sheet
* @param styles
* @param startR 开始行
* @param totalNum
* @param headinfo 0:列总长度 1子列开始位置 2子列长度
*/
public static void createSheetBody(XSSFSheet sheet,
Map<String, XSSFCellStyle> styles, int startR, int totalNum,
int[] headinfo) {
int subTotal = (headinfo[0] - headinfo[1]) / headinfo[2] - 1;
Random random = new Random();
// 产生随机为0的列
List<Integer> tmpList = new ArrayList<Integer>();
for (int i = 0, len = subTotal * 3 / 4; i < len; i++) {
tmpList.add(random.nextInt(subTotal) % (subTotal + 1));
}
// 去重
Set<Integer> nullValue = new TreeSet(tmpList);
int[] nullIndex = new int[nullValue.size()];
int j = 0, n = 0;
// 赋值给数组,保存为0的列的index
for (Integer it : nullValue) {
nullIndex[j++] = it;
}
tmpList = null;
nullValue = null;
random = new Random();
j = 1;
int page = 10;
int times = 0;
if (totalNum < page) {
page = totalNum;
times = 1;
} else {
times = totalNum / page + totalNum % page;
}
XSSFCellStyle cell_style = styles.get("cell_center");
XSSFCellStyle long_style = styles.get("cell_long");
XSSFRow row = null;
XSSFCell cell = null;
List<Object> dataList = null;
for (int t = 0; t < times; t++) {
if (t == times - 1) {
page = totalNum - t * page;
}
for (int i = 0; i < page; i++) {
dataList = new ArrayList<Object>();
n = 0;
dataList.add(j);
for (int k = 1; k < headinfo[1]; k++) {
dataList.add("测试" + (t * 10 + j));
}
j++;
for (int k = 0; k < subTotal; k++) {
if (n < nullIndex.length && k == nullIndex[n]) {
for (int l = 0; l < headinfo[2]; l++) {
dataList.add(0);
}
n++;
} else {
for (int l = 0; l < headinfo[2]; l++) {
dataList.add(random.nextInt(8000));
}
}
}
for (int l = 0; l < headinfo[2]; l++) {
dataList.add(random.nextInt(20000));
}
row = sheet.createRow(startR);
startR++;
for (int s3 = 0, len3 = headinfo[1]; s3 < len3; s3++) {
cell = row.createCell(s3);
cell.setCellStyle(cell_style);
cell.setCellValue(dataList.get(s3).toString());
}
for (int s4 = headinfo[1], len4 = headinfo[0]; s4 < len4; s4++) {
cell = row.createCell(s4);
cell.setCellStyle(long_style);
cell.setCellValue((Integer) dataList.get(s4));
}
cell = null;
row = null;
dataList.clear();
dataList = null;
}
cell = null;
row = null;
dataList = null;
}
}
(三)创建Excel 信息
public static void createExcelInfo(XSSFWorkbook workbook) {
POIXMLProperties.CoreProperties coreProp = workbook.getProperties()
.getCoreProperties();
coreProp.setCreator("测试作者属性");
coreProp.setCategory("测试类别属性");
coreProp.setTitle("Excel标题属性");
coreProp.setSubjectProperty("测试主题属性");
coreProp.setKeywords("报表测试,POI测试");
}
(四)得到总列下子列全部为0的记录,采用Event模式
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 GetNullValueHandler extends DefaultHandler {
// 共享字符串表
private SharedStringsTable sst;
// 上一次的内容
private String lastContents;
private boolean nextIsString;
private List<Integer> indexRowList = new ArrayList<Integer>();
private int[] headinfo;//列信息
private int startR;//开始行
// 当前行
private int curRow = 0;
// 当前列
private int curCol = 0;
private int beforeCol = 0;
private int endCol = 0;
private boolean isFirst = true, isFirstRow = true;
private List<Integer> resultRowList = new ArrayList<Integer>();
/**
* sheetId为要遍历的sheet索引，从1开始
*/
public void processOneSheet(String filename, int sheetId) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// 根据 rId# 或 rSheet# 查找sheet
InputStream sheet2 = r.getSheet("rId" + sheetId);
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
public void process(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
int sheetIndex = -1;
while (sheets.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
}
public GetNullValueHandler(int[] headinfo, int startR) {
super();
this.headinfo = headinfo;
this.startR = startR;
}
public GetNullValueHandler() {
super();
}
public List<Integer> getResultRowList() {
return resultRowList;
}
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 {
// r = Reference s = Style Index t =Cell Data Type
// c => 单元格
if ("c".equals(name)) {
// 如果下一个元素是 SST 的索引，则将nextIsString标记为true
String cellType = attributes.getValue("t");
if ("s".equals(cellType)) {
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) {
}
}
if ("v".equals(name)) {
String value = lastContents.trim();
value = value.equals("") ? " " : value;
String key = null;
if ("0".equals(value) || "0.0".equals(value)) {
if (isFirst) {
beforeCol = curCol;
isFirst = false;
}
endCol = curCol;
if (!isFirst && (endCol - beforeCol + 1 == headinfo[2])) {
key = new String("" + beforeCol);
indexRowList.add(beforeCol);
isFirst = true;
}
} else {
isFirst = true;
beforeCol = curCol;
endCol = curCol;
}
curCol++;
} else {
// 如果标签名称为 row ，这说明已到行尾，调用 parseRow() 方法
if (name.equals("row")) {
parseRow();
indexRowList.clear();
curRow++;
curCol = 0;
beforeCol = 0;
endCol = 0;
isFirst = true;
if (curRow >= startR + 1) {
isFirstRow = false;
}
}
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
// 得到单元格内容的值
lastContents += new String(ch, start, length);
}
private void parseRow() {
if (isFirstRow && curRow == startR) {
resultRowList = new ArrayList(indexRowList);
} else if (curRow >= startR + 1) {
// 交集
resultRowList.retainAll(indexRowList);
resultRowList = new ArrayList(resultRowList);
}
}
public static void main(String[] args) throws Exception {
int[] headinfo = new int[] { 309, 9, 3 };
int startR = 2;
GetNullValueHandler t = new GetNullValueHandler(headinfo, startR);
// t.processOneSheet("f:/saveFile/temp/1395740729468.xlsx", 1);
t.process("f:/saveFile/temp/1395795063015.xlsx");
List<Integer> resultList = t.getResultRowList();
for (Integer it : resultList) {
System.out.print(it + " ");
}
System.out.println();
}
}
(五)删除满足条件的列
public static void deleteExcelColumn(String fileName, String sheetName,
int startR, int[] headInfo, List<Integer> columns) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(fileName));
XSSFSheet sheet = wb.getSheet(sheetName);
XSSFRow row = null;
int subLen = headInfo[2];// 子列大小
for (int i = 0, len = sheet.getPhysicalNumberOfRows(); i < len; i++) {
row = sheet.getRow(i);
System.out.print("Line_" + i + "=");
for (Integer it : columns) {
for (int c = 0; c < subLen; c++) {
// 删除后还显示空白
row.removeCell(row.getCell(it + c));
// 隐藏空白列
sheet.setColumnHidden(it + c, true);
// sheet.setColumnWidth(it + c, 0);
}
}
row = null;
}
try {
FileOutputStream fileOutStream = new FileOutputStream(fileName);
wb.write(fileOutStream);
if (fileOutStream != null) {
fileOutStream.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
调用方法：
public static void main(String[] args) throws Exception {
CreateExcel2007Test t = new CreateExcel2007Test();
XSSFWorkbook wb = new XSSFWorkbook();
Map<String, XSSFCellStyle> styles = createStyles(wb);
String sheetName = "测试数据";
int startR = 2;// 第二行开始写数据
XSSFSheet sheet = wb.createSheet(sheetName);
// 创建表头
int[] headInfo = t.createExcelHeader(wb, sheet, styles, 100);
// 填充数据
createSheetBody(sheet, styles, startR, 4, headInfo);
createExcelInfo(wb);
String fileName = "f:/saveFile/temp/" + System.currentTimeMillis()
+ ".xlsx";
t.writeExcel(wb, fileName);
styles = null;
wb = null;
System.gc();
GetNullValueHandler handler = new GetNullValueHandler(headInfo, 2);
handler.process(fileName);
List<Integer> resultList = handler.getResultRowList();
handler = null;
System.gc();
startR = 0;// 删除标题列
long start = System.currentTimeMillis();
deleteExcelColumn(fileName, sheetName, startR, headInfo, resultList);
System.out.println((System.currentTimeMillis() - start) / 1000 + "秒");
}
结果如下：
存在问题：
(1)整个导出最消耗时间的是删除空列(值为0),1000*3列,6行删了将近665s，不能忍受。
(2)数据行一大,就OOM了，因为创建了大量的对象
待解决：
(1)是否可以前台导出,但是把总列下子列全部为0的记录不能导出，不知道前台js能做到不。
(2)创建多个Excel,每个Excel写入一个月数据,然后合并Excel,但看网上的合并其实是读取Excel写入sheet，不知道是否OOM。
(3)下次看同事怎么写的。
欢迎各位提出更好的方法，集思广益，谢谢。
本文系原创，转载请注明出处，谢谢。
全文完。
相关推荐
通用的POI导入导出Excel通用解决方案,
Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。...Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。
在项目中，经常免不了要导入、导出Excel，导出Excel稍微简单点，制作一张模板，然后将查询之后的数据写入到模板中即可，导出程序可以做到一次写好，基本上就可以通用。 但导入Excel相对麻烦点，一般情况下，导入的...
改资源包含 数据量超过20万以上导出解决方案，还有动态获取数据公共类 1） 把数据分成多个sheet导出。 2） 把数据分成多个excel 导出。 全部都有，还有实例。
通过系统性的规划和执行，方案能够分析问题的根本原因，提供可行的解决方案，并引导实施过程，确保问题得到合理解决。 目标达成： 方案通常与明确的目标相关联，它提供了一种达成这些目标的计划。无论是企业战略、...
Easy-POI是一款Excel导入导出解决方案组成的轻量级开源组件
轻松解决普通poi形式导出Excel的中出现的栈溢出问题，此资源可实现千万级数据分批导出csv文件，csv大数据量导出(千万级别，不会内存溢出)，多线程导出 ，生产环境已经很稳定的使用着
事件驱动解析是把文件转换成xml，然后一边读取一边解析，这样就对内存的占用就会很少，可以很好的处理poi出现OOM的问题。 maven添加需要的jar包 <groupId>org.apache.poi <artifactId>poi <version>3.15 ...
poi读取大量数据会造成gc内存溢出的报错,由于垃圾回收机制无法将大量的对象及时的回收,而这些对象又会保存在内存中,会导致内存不够用的情况,这时候我们就需要使用新的方法,读取...此解决方案可支持千万数据的传输不报错
解决在Linux系统下，POI、easyExcel 报错 java.lang.IllegalArgumentException: The workbook already contains a sheet named。原因是缺少了字体文件，如果是Docker部署使用本资源即可。内部提供字体资源以及代码...
GridExcel是基于Java8函数式编程和POI EventModel实现的用于Excel简单读写的通用解决方案。 基于POI EventModel，在读写数据量非常大的Excel时，降低内存占用避免OOM与频繁FullGC 基于函数编程，支持关联对象等多种...
它是单点解决方案，为以Excel格式导出Vaadin的数据提供一致且可配置的支持。 网格仅与ListDataProvider一起使用，为其他Provider的pullrequest想法打开。 为什么我们需要这个插件？ 在大多数应用程序中，导出屏幕...
本以为很简单，通过freemarker就可以轻松实现，但是在尝试过后，还知道不是很顺利，网上也提供了不少的解决方案，个人感觉大多都不是好，有一些只支持Window系统，有一些则需要安装Office软件，在这里，给大家提供...
目前来看，java导出word大致有6种解决方案： 1：Jacob是Java-COM Bridge的缩写，它在Java与微软的COM组件之间构建一座桥梁。使用Jacob自带的DLL动态链接库，并通过JNI的方式实现了在Java平台上对COM程序的调用。DLL...