POI导出Excel(1000列)解决方案记录_JAVA_编程开发_程序员俱乐部

???? 工作中有个报表需要导出，报表行不是很多，但列很多,大约有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的不显示。该优化需求交给了另一位同事。下面是我自己模拟做的，项目目前没有采用我这种方法,因为我这种做法效率不行。

?

??? 如果我有更好的方法，我就不试着使用这种方法了，各位请轻喷。

??? 我自己想的方案(最简单)是:

??? 1)先导出Excel 2007数据。

??? 2)找出总列下子列全部为0的列

??? 3)删除该列,保存Excel。

??

??? (一)导出Excel

??? 这没什么难度。先写表头，在插入数据。

???

class="java">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)下次看同事怎么写的。

?

???? 欢迎各位提出更好的方法，集思广益，谢谢。

?

???? 本文系原创，转载请注明出处，谢谢。

??? 全文完。

?

???

??

• 
• 大小: 92.7 KB

• 
• 大小: 184.5 KB

• 
• 大小: 111.9 KB

• 
• 大小: 41.6 KB

• 查看图片附件