工作中有个报表需要导出，报表行不是很多，但列很多,大约有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)下次看同事怎么写的。
欢迎各位提出更好的方法，集思广益，谢谢。
本文系原创，转载请注明出处，谢谢。
全文完。
相关推荐
通过以上步骤，我们成功地实现了使用Apache POI在Java中导出Excel文件，并实现了自动换行的功能。这种方式不仅可以提高工作效率，还能确保数据的准确性和完整性。 #### 六、注意事项与优化建议 - **兼容性问题**：...
Apache POI是一个强大的Java库，专门用于处理Microsoft Office...使用POI库，你可以构建一个灵活且高效的Excel导出解决方案，满足各种复杂需求。在实际开发中，记得对可能出现的异常进行妥善处理，提高程序的健壮性。
总之，这个项目是一个完整的Java解决方案，用于处理Excel文件的导入导出，同时具备用户友好的进度条显示。它利用了Apache POI的强大功能，结合maven的依赖管理，以及可能的GUI组件，提供了高效且直观的数据操作体验...
总之，"Springboot+Poi实现Excel的导入导出"项目结合了多种强大的工具，提供了一种高效、灵活的数据处理解决方案。无论是开发数据管理应用，还是进行数据分析任务，这样的功能都极具价值。通过深入了解和实践这些...
jsp+poi导出excel.docx 本文档主要讲述了使用jsp和poi组件来导出excel文件的方法。下面是对标题、描述、标签和部分内容的详细解释。 首先，标题“jsp+poi导出excel.docx”表明了本文档的主要内容是使用jsp和poi来...
### 使用Apache POI导出Excel 2007并避免内存溢出问题 #### 背景介绍 在处理大量数据时，使用Java导出Excel文件可能会遇到内存溢出的问题，尤其是在导出Excel 2007（.xlsx格式）时更为常见。这是因为传统的`...
本项目“poi多线程大数据导出excel文件”提供了一个解决方案，利用多线程来提高Excel的大数据导出效率。 Apache POI 3.1版本是较早的版本，而项目中使用了更新的4.1版本，这意味着它可能利用了更多优化和新特性。在...
以下是一些关于如何使用Java POI导出Excel并触发浏览器下载的步骤： 1. **创建Workbook对象**： 首先，你需要创建一个Workbook对象，它是Excel文件的容器。根据你的Excel文件类型，可以选择HSSFWorkbook或...
总的来说，Apache POI为Java开发者提供了一套强大的工具，可以方便地处理Excel文件，实现各种复杂的操作，包括创建和导出带有合并行和列的表头。对于大型企业或数据分析应用，这种能力尤为关键，因为它可以极大地...
二、解决方案 为了解决上述问题，我们需要对POI框架进行深入了解和分析。 （一）模板/指标定位位置的问题 在解决这个问题时，我们需要使用Tree结构来描述模板和指标之间的关系。Tree结构是指模板和指标之间的父子...
本教程将深入探讨如何使用Apache POI实现一个通用的Excel导入导出解决方案。 1. **Apache POI简介** Apache POI 是一个开源项目，允许开发者使用Java读写Microsoft Office格式的文件。它提供了HSSF（Horrible ...
Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。...Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。
在现代企业级应用开发中，数据的导入与导出功能是非常常见且重要的。SpringBoot结合Mybatis框架提供了高效...通过理解SpringBoot的自动配置、Mybatis的SQL映射以及POI的Excel操作，我们可以轻松地构建类似的解决方案。
总结，"poi导入导出Excel通用工具类 兼容xls，xlsx"这个工具实现了利用Apache POI库处理Excel文件的功能，涵盖了从旧版的.xls到新版的.xlsx格式，提供了方便的导入和导出接口，使得在Java开发中处理Excel数据变得...
在项目中，经常免不了要导入、导出Excel，导出Excel稍微简单点，制作一张模板，然后将查询之后的数据写入到模板中即可，导出程序可以做到一次写好，基本上就可以通用。 但导入Excel相对麻烦点，一般情况下，导入的...
"使用POI导出大数据量到EXCEL"这个主题涉及到如何高效地利用POI处理大量数据并将其导出到Excel文件中。以下是对这个主题的详细讲解。 1. **Apache POI简介** Apache POI 是一个开源项目，它提供了Java API来创建、...
在本项目中，"SpringBootMybatis+poi+Thymeleaf实现excel文件数据导入到数据库以及从数据库将数据导出成excel.zip"，我们主要关注的是如何利用Java技术栈来处理Excel文件，并与数据库进行交互。以下是相关知识点的...
标题中的“用Poi导出Excel表格数据”指的是使用Apache POI库来创建和操作Microsoft Excel文件的过程。Apache POI是Java领域一个广泛使用的库，它允许开发者读取、写入以及修改MS Office格式的文件，包括Excel。下面...
当你需要从网页数据导出Excel时，Easy POI提供了一种方便的方式。首先，你需要将HTML内容转换为表格数据结构，例如使用Jsoup解析HTML获取表格元素。然后，利用Easy POI的API，创建一个Excel工作簿对象，将HTML表格...