`
53873039oycg
  • 浏览: 840201 次
  • 性别: Icon_minigender_1
社区版块
存档分类
最新评论

[简单]poi多sheet分批次导出

    博客分类:
  • poi
 
阅读更多

        普通方式导出Excel,比较可行的方法是多Sheet页分批次写,如10000数据分2个Sheet,每个Sheet页5000,分5次每次取1000写,下面代码简单的实现了这个功能,可以指定Sheet页大小和每次取数大小。写的比较随意,可能会有bug,欢迎提出更好的写法

      

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Random;
import java.util.Set;
import java.util.TreeSet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class Excel_导出_S01_Test {
	public static void main(String[] args) {
		Excel_导出_S01_Test t = new Excel_导出_S01_Test();
		t.exportExcel("2007", true, 1000, 5000, "f:/saveFile/temp/"+ System.currentTimeMillis() + ".xlsx"); 
		t.exportExcel("2007", false, 1000, 5000, "f:/saveFile/temp/"+System.currentTimeMillis() + ".xlsx");
		t.exportExcel("2007", false, 5000, 1000, "f:/saveFile/temp/"+System.currentTimeMillis() + ".xlsx");
	}

	/**
	 * @Description: 普通的导出excel方法
	 * @param expType
	 *            excel类型2003 2007
	 * @param isDelZero
	 *            是否删除动态列中全部为0的值
	 * @param savePath
	 */
	public void exportExcel(String expType, boolean isDelZero, int pageSize,
			int sheetSize, String savePath) {
		List<String> columnList = new ArrayList<String>();
		// 表头_行数_列数$
		columnList.add("编号#2#1$");
		columnList.add("品牌名#2#1$");
		columnList.add("型号#2#1$");
		StringBuffer cellSb = new StringBuffer();
		cellSb.append("000");
		List<String> dyColumnList = new ArrayList<String>();// 动态列
		int dyNum = 4;
		int subLen = 3;// 子列长度
		for (int i = 1; i < dyNum + 1; i++) {
			// 表头_行数_列数$子列_行数_列数$ 行数固定为1
			dyColumnList.add("下游合作商_" + i + "#1#3$数量#1#1$");
			dyColumnList.add("下游合作商_" + i + "#1#3$金额#1#1$");
			dyColumnList.add("下游合作商_" + i + "#1#3$小计#1#1$");
		}
		dyColumnList.add("合计#1#3$数量合计#1#1$");
		dyColumnList.add("合计#1#3$金额合计#1#1$");
		dyColumnList.add("合计#1#3$小计合计#1#1$");
		String sql = "select 1 from dual";
		Workbook wb = null;
		long resultSize = getSqlResultSize(sql);
		if (isDelZero) {
			if (resultSize * dyColumnList.size() > 65535 * 10L) {
				System.err.println("数据量太大,请使用异步导出");
				return;
			}
			wb = new SXSSFWorkbook(1000);
		} else {
			if ("2003".equals(expType)
					&& columnList.size() + dyColumnList.size() < 256) {
				wb = new HSSFWorkbook();
			} else {
				wb = new SXSSFWorkbook(1000);
			}
		}
		if (resultSize * (columnList.size() + dyColumnList.size()) > 65535 * 15L) {
			System.err.println("数据量太大,请使用异步导出");
			return;
		}
		List<List<Object>> dataList = null;
		Map<Integer, Integer> zeroCols = null;
		//删除0值直接取全部数据,不分sheet页
		if (isDelZero) {
			dataList = getSqlResult(sql, 1, (int) resultSize, dyNum);
			zeroCols = getAllZeroDyColumn(columnList, dyColumnList, dataList,subLen);
		}
		columnList.addAll(dyColumnList);
		if (columnList.size() >= 16383) {
			System.err.println("暂未实现列分割");
			return;
		}else if(columnList.size()>256&&"2003".equals(expType)){
			System.err.println("超出Excel2003限制,选择Excel2007导出");
			wb = new SXSSFWorkbook(1000);
		}
		int startR = 1;
		//动态列求和1合并 0不合并
		for (int p = 0, plen = dyColumnList.size() * 3; p < plen; p++) {
			cellSb.append("1");
		}
		String cellInfo = cellSb.toString();
		boolean isSplitRow = isSplitExcelHead(columnList);// 是否存在动态列
		boolean isCellSum = cellInfo.indexOf("1") > -1;// 是否对列求和
		BigDecimal[] cellSum = new BigDecimal[cellInfo.length()];
		// 初始化
		for (int i = 0; i < cellSum.length; i++) {
			cellSum[i] = new BigDecimal(0);
		}
		final int cellWidth =8;
		final short rowHeight = (short)380;
		// 设置表头数据样式
		CellStyle headStyle = wb.createCellStyle();
		Font headFont = wb.createFont();
		headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

		headStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
		headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		headStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		headStyle.setBorderBottom(CellStyle.BORDER_THIN);
		headStyle.setBorderLeft(CellStyle.BORDER_THIN);
		headStyle.setBorderRight(CellStyle.BORDER_THIN);
		headStyle.setBorderTop(CellStyle.BORDER_THIN);

		headStyle.setTopBorderColor(IndexedColors.LIME.getIndex());
		headStyle.setRightBorderColor(IndexedColors.LIME.getIndex());
		headStyle.setBottomBorderColor(IndexedColors.LIME.getIndex());
		headStyle.setLeftBorderColor(IndexedColors.LIME.getIndex());
		headStyle.setFont(headFont);

		// 普通单元格样式
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		// 整型样式
		CellStyle longStyle = wb.createCellStyle();
		longStyle.setAlignment(CellStyle.ALIGN_CENTER);
		longStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		DataFormat format = wb.createDataFormat();
		longStyle.setDataFormat(format.getFormat("####0"));

		// double类型样式
		CellStyle doubleStyle = wb.createCellStyle();
		doubleStyle.setAlignment(CellStyle.ALIGN_CENTER);
		doubleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		format = wb.createDataFormat();
		doubleStyle.setDataFormat(format.getFormat("####0.###"));

		// final int pageSize = 502;// 每次取1000条数据
		// final int sheetSize = 101;// 每个sheet大小
		int totalSheet = (int) ((resultSize - 1) / sheetSize + 1);
		boolean isNeedMutil = (sheetSize > pageSize) && (resultSize > pageSize);// 是否需要多次取数据
		if (isDelZero) {
			totalSheet = 1;
		}
		int remainSize = 0, currentRow = 0, lastRow = 0;
		int blen = 0;
		// 分次写满一个sheet页
		for (int st = 1; st <= totalSheet; st++) {
			Sheet sheet = wb.createSheet("报表_" + st);
			sheet.setDefaultColumnWidth(cellWidth);
			sheet.setDefaultRowHeight(rowHeight);
			currentRow = 0;
			blen = 0;
			//写满一个sheet页是否需要多次取值
			isNeedMutil = (sheetSize > pageSize) && (resultSize > pageSize);
			if (isSplitRow) {
				createSheetTitle(sheet, 0, columnList, headStyle);//存在动态列
				startR = 2;
			} else {
				createSheetTitleNormal(sheet, 0, columnList, headStyle);
			}
			System.out.println("-------------------------报表头部写完成--------");
			if (isDelZero) {
				createExcelBody(sheet, startR, dataList, zeroCols, isCellSum,
						true, cellInfo, cellSum, cellStyle, longStyle,doubleStyle);
				dataList.clear();
				dataList=null;
			} else if (!isNeedMutil) {
				//先写上次剩余的数据
				if (remainSize > 0) {
					if (st == totalSheet&& remainSize == resultSize % sheetSize) {
						createExcelSheetBody(sheet,startR,0,dataList,dataList.size() - remainSize,
								(int) Math.min(st * sheetSize, dataList.size()),isCellSum, true, cellInfo, cellSum, cellStyle,longStyle, doubleStyle);
					    dataList.clear();
					    dataList=null;
					    break;
					} else {
						createExcelSheetBody(sheet,startR,0,dataList,dataList.size() - remainSize,
								(int) Math.min(st * sheetSize, dataList.size()),isCellSum, false, cellInfo, cellSum, cellStyle,longStyle, doubleStyle);
					}
					currentRow = remainSize;
					remainSize = Math.max(dataList.size() - st * sheetSize, 0);
					//一次数据填充多个sheet页
					if (remainSize > 0) {
						continue;
					}
					dataList.clear();
					dataList = null;
				}
				int startIndex = remainSize + (st - 1) * sheetSize + 1;// 分页开始字段
				int endIndex = remainSize + st * sheetSize;
				if (endIndex >= resultSize) {
					endIndex = (int) resultSize;
				}
				//System.out.println(startIndex + "----=" + endIndex);
				String pageSql = "select  rptrt.*  from (select rptrow.*, rownum rn from ( "
						+ sql
						+ " ) rptrow  where rownum <="
						+ endIndex
						+ ") rptrt where rn >=" + startIndex;
				dataList = getSqlResult(pageSql, startIndex, endIndex, dyNum);
				if (st == totalSheet) {
					createExcelSheetBody(sheet,startR,currentRow,dataList,0,(int) Math.min(sheetSize - currentRow,
									dataList.size()), isCellSum, true,cellInfo, cellSum, cellStyle, longStyle,doubleStyle);
					dataList.clear();
					dataList=null;
					break;
				} else {
					createExcelSheetBody(sheet,startR,currentRow,dataList,0,(int) Math.min(sheetSize - currentRow,dataList.size()), 
							isCellSum, false,cellInfo, cellSum, cellStyle, longStyle,doubleStyle);
				}
				remainSize = currentRow + dataList.size() - sheetSize;
			} else {
				// 先把剩余的部分写完
				if (remainSize > 0) {
					// 剩余数量最多写一次
					if (st == totalSheet&& remainSize == resultSize % sheetSize) {
						createExcelSheetBody(sheet, startR, 0, dataList,dataList.size() - remainSize,
								Math.min(dataList.size(), sheetSize),
								isCellSum, true, cellInfo, cellSum, cellStyle,
								longStyle, doubleStyle);
						dataList.clear();
						dataList=null;
						break;
					} else {
						createExcelSheetBody(sheet, startR, 0, dataList,
								dataList.size() - remainSize,
								Math.min(dataList.size(), sheetSize),
								isCellSum, false, cellInfo, cellSum, cellStyle,
								longStyle, doubleStyle);
					}
					dataList.clear();
					dataList = null;
					currentRow = remainSize;
				}
				if (resultSize - (st - 1) * sheetSize - 1 - currentRow < 0) {
					continue;
				} else {
					blen = (int) Math.min((sheetSize - 1) / pageSize + 1, (resultSize- (st - 1) * sheetSize - 1 - currentRow)
									/ pageSize + 1);
				}
				//分次取数据
				for (int b = 0; b < blen; b++) {
					if (!isNeedMutil) {
						break;
					}
					lastRow = currentRow;
					int startIndex = remainSize + (st - 1) * sheetSize + 1 + b
							* pageSize;// 分页开始字段
					int endIndex = remainSize + (st - 1) * sheetSize + (b + 1)
							* pageSize;
					if (endIndex >= resultSize) {
						endIndex = (int) resultSize;
						isNeedMutil = false;
						remainSize = 0;
					}
					if (endIndex > st * sheetSize) {
						remainSize = endIndex - st * sheetSize;
						currentRow = st * sheetSize + 1;
						isNeedMutil = false;
					}
					//System.out.println(startIndex + "---=" + endIndex + "---="+ lastRow);
					String pageSql = "select  rptrt.*  from (select rptrow.*, rownum rn from ( "
							+ sql
							+ " ) rptrow  where rownum <="
							+ endIndex
							+ ") rptrt where rn >=" + startIndex;
					dataList = getSqlResult(pageSql, startIndex, endIndex,
							dyNum);
					if (st == totalSheet&&endIndex==resultSize) {
						createExcelSheetBody(sheet, startR, lastRow, dataList,0, dataList.size() - remainSize, 
								isCellSum,true, cellInfo, cellSum, cellStyle, longStyle,doubleStyle);
						dataList.clear();
						dataList=null;
						break;
					} else {
						createExcelSheetBody(sheet, startR, lastRow, dataList,0, dataList.size() - remainSize,
								isCellSum,false, cellInfo, cellSum, cellStyle, longStyle,doubleStyle);
					}
					currentRow += dataList.size() - remainSize;
				}
			}
		}
		System.out.println("------------报表保存----");
		writeExcel(wb, savePath);
	}

	public void createExcelSheetBody(Sheet sheet, int headSize, int startR,
			List bodyList, int startIndex, int endIndex, boolean isCellSum,
			boolean isLast, String cellInfo, BigDecimal[] cellSum,
			CellStyle cellStyle, CellStyle longStyle, CellStyle doubleStyle) {
		Row rowBody = null;
		Cell cell = null;
		if (bodyList.size() > 0) {
			List rowList = null;
			int colEnd = ((List) bodyList.get(0)).size();
			int rowEnd = bodyList.size();// TODO
			for (int i = startIndex, idx = 0; i < endIndex; i++, idx++) {
				rowBody = sheet.createRow(idx + headSize + startR);
				rowList = (List) bodyList.get(i);
				if (!isCellSum) {
					for (int j = 0; j < colEnd; j++) {
						cell = rowBody.createCell(j);
						Object obj = rowList.get(j);
						// double long string判断
						try {
							double var = Double.parseDouble(obj + "");
							cell.setCellValue(var);
							if (var != (long) var) {
								cell.setCellStyle(doubleStyle);
							} else {
								cell.setCellStyle(longStyle);
							}
						} catch (Exception e) {
							cell.setCellValue(obj + "");
							cell.setCellStyle(cellStyle);
						}
					}
				} else {
					for (int j = 0; j < colEnd; j++) {
						cell = rowBody.createCell(j);
						Object obj = rowList.get(j);
						try {
							double var = Double.parseDouble(obj + "");
							cell.setCellValue(var);
							if (var != (long) var) {
								cell.setCellStyle(doubleStyle);
							} else {
								cell.setCellStyle(longStyle);
							}
							if (cellInfo.charAt(j) == '1') {
								cellSum[j] = cellSum[j]
										.add(new BigDecimal(var));
							}
						} catch (Exception e) {
							cell.setCellValue(obj + "");
							cell.setCellStyle(cellStyle);
						}
					}
				}
				rowList = null;
			}
			if (isCellSum) {
				if (isLast) {
					rowBody = sheet.createRow(headSize + startR + endIndex
							- startIndex);
					cell = rowBody.createCell(0);
					cell.setCellStyle(doubleStyle);
					cell.setCellValue("合计");
					for (int j = 1; j < colEnd; j++) {
						if (cellInfo.charAt(j) == '1') {
							cell = rowBody.createCell(j);
							cell.setCellStyle(doubleStyle);
							cell.setCellValue(cellSum[j]
									.setScale(3, BigDecimal.ROUND_HALF_UP)
									.stripTrailingZeros().toPlainString());
						}
					}
				}
			}
		}
		rowBody = null;
		cell = null;
	}

	/**
	 * @Description: 得到List中0值位置
	 */
	public Map<Integer, Integer> getAllZeroDyColumn(List<String> columnList,
			List<String> dyList, List<List<Object>> dataList, int subLen) {
		Map<Integer, Integer> nullIndexMap = new HashMap<Integer, Integer>();
		int startIndex = columnList.size();
		List<Object> list = null;
		boolean isNull = true;
		list = dataList.get(0);
		// 得到第一行数据所有为0的索引位置
		for (int i = startIndex, len = list.size(); i < len; i += subLen) {
			isNull = true;
			for (int k = i, klen = i + subLen; k < klen && k < len; k++) {
				if (!isZero(list.get(k).toString())) {
					isNull = false;
					break;
				}
			}
			if (isNull) {
				nullIndexMap.put((i - startIndex) / subLen, 1);
			}
		}
		for (int j = 1, len2 = dataList.size(); j < len2; j++) {
			list = dataList.get(j);
			for (int i = startIndex, len = list.size(); i < len; i += subLen) {
				isNull = true;
				if (nullIndexMap.get((i - startIndex) / subLen) != null) {
					for (int k = i, klen = i + subLen; k < klen && k < len; k++) {
						if (!isZero(list.get(k).toString())) {
							isNull = false;
							break;
						}
					}
					if (!isNull) {
						// 逐步缩小为0的索引值
						nullIndexMap.remove((i - startIndex) / subLen);
					}
				}
			}
			list = null;
		}
		List<String> dyList2 = new ArrayList<String>();
		Map<Integer, Integer> resultMap = new HashMap<Integer, Integer>();
		for (Entry<Integer, Integer> entry : nullIndexMap.entrySet()) {
			for (int k = 0; k < subLen; k++) {
				resultMap.put(entry.getKey() * subLen + startIndex + k, subLen);
				dyList2.add(dyList.get(entry.getKey() * subLen + k));
			}
		}
		dyList.removeAll(dyList2);
		dyList = new ArrayList(dyList);
		dyList2.clear();
		dyList2 = null;
		list = null;
		return resultMap;
	}

	/**
	 * @Description: 是否为0
	 */
	public boolean isZero(String str) {
		return str == null || str.replaceAll("(0|,|\\.)", "").length() == 0;
	}

	private List<List<Object>> getSqlResult(String pageSql, int startIndex,
			int endIndex, int dyNum) {
		return generateTestData(startIndex, endIndex, dyNum);
	}

	// 产生随机数据
	public List<List<Object>> generateTestData(int startIndex, int totalNum,
			int dyNum) {
		Random random = new Random(System.nanoTime());
		List<List<Object>> tmpDataList = new ArrayList<List<Object>>();
		List<Object> dataList = null;
		// 产生随机为0的列
		List<Integer> tmpList = new ArrayList<Integer>();
		for (int i = 0, len = dyNum * 3 / 4; i < len; i++) {
			tmpList.add(random.nextInt(dyNum) % (dyNum - 1));
		}
		random = new Random(System.nanoTime());
		// 去重
		Set<Integer> nullValue = new TreeSet(tmpList);
		System.out.println("----重复数据-----");
		for (Integer it : nullValue) {
			System.out.print((3 + it * 3) + ",");
		}
		System.out.println();
		for (int i = startIndex; i <= totalNum; i++) {
			dataList = new ArrayList<Object>();
			dataList.add(i);
			dataList.add("测试");
			dataList.add("数据");
			for (int k = 0; k < dyNum + 1; k++) {
				if (nullValue.contains(k)) {
					dataList.add(String.valueOf(0));
					dataList.add(String.valueOf(0));
					dataList.add(String.valueOf(0));
				} else {
					dataList.add(String.valueOf(Math.abs(random.nextLong()) % 4000 * 0.098));
					dataList.add(String.valueOf(Math.abs(random.nextInt() % 100)));
					dataList.add(String.valueOf(Math.abs(random.nextInt() % 10000)));
				}
			}
			tmpDataList.add(dataList);
			dataList = null;
		}
		return tmpDataList;
	}

	public long getSqlResultSize(String sql) {
		return 10000;
	}

	/**
	 * @Description: 是否存在动态列
	 */
	public boolean isSplitExcelHead(List<String> headList) {
		for (String str : headList) {
			if (str.indexOf("#") > -1 && str.indexOf("$") > -1) {
				return true;
			}
		}
		return false;
	}

	public  void writeExcel(Workbook wb, String fileName) {
		if (wb != null) {
			try {
				FileOutputStream fileOutStream = new FileOutputStream(fileName);
				wb.write(fileOutStream);
				if (fileOutStream != null) {
					fileOutStream.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * @Description:删除0值
	 * @param startR
	 *            开始行
	 * @param bodyList
	 *            数据
	 * @param isCellSum
	 *            是否求列和
	 * @param isLast
	 *            是否是最后一个Sheet页
	 * @param cellInfo
	 *            列求和信息1求和0不求和
	 * @param cellSum
	 *            列求和数组
	 */
	public void createExcelBody(Sheet sheet, int startR, List bodyList,
			Map<Integer, Integer> zeroCols, boolean isCellSum, boolean isLast,
			String cellInfo, BigDecimal[] cellSum, CellStyle cellStyle,
			CellStyle longStyle, CellStyle doubleStyle) {
		Row rowBody = null;
		Cell cell = null;
		if (bodyList.size() > 0) {
			List rowList = null;
			int rowEnd = bodyList.size() + startR;
			int colEnd = ((List) bodyList.get(0)).size();
			for (int i = startR, idx = 0; i < rowEnd; i++, idx++) {
				rowBody = sheet.createRow(i);
				rowList = (List) bodyList.get(idx);
				if (!isCellSum) {
					for (int j = 0, col = 0; j < colEnd; j++) {
						if (zeroCols.containsKey(j)) {
							j += zeroCols.get(j) - 1;
							continue;
						}
						cell = rowBody.createCell(col);
						Object obj = rowList.get(j);
						// double long string判断
						try {
							double var = Double.parseDouble(obj + "");
							cell.setCellValue(var);
							if (var != (long) var) {
								cell.setCellStyle(doubleStyle);
							} else {
								cell.setCellStyle(longStyle);
							}
						} catch (Exception e) {
							cell.setCellValue(obj + "");
							cell.setCellStyle(cellStyle);
						}
						col++;
					}
				} else {
					for (int j = 0, col = 0; j < colEnd; j++) {
						if (zeroCols.containsKey(j)) {
							j += zeroCols.get(j) - 1;
							continue;
						}
						cell = rowBody.createCell(col);
						Object obj = rowList.get(j);
						try {
							double var = Double.parseDouble(obj + "");
							cell.setCellValue(var);
							if (var != (long) var) {
								cell.setCellStyle(doubleStyle);
							} else {
								cell.setCellStyle(longStyle);
							}
							if (cellInfo.charAt(col) == '1') {
								cellSum[col] = cellSum[col].add(new BigDecimal(
										var));
							}
						} catch (Exception e) {
							cell.setCellValue(obj + "");
							cell.setCellStyle(cellStyle);
						}
						col++;
					}
				}
				rowList = null;
			}
			if (isCellSum) {
				if (isLast) {
					rowBody = sheet.createRow(rowEnd);
					cell = rowBody.createCell(0);
					cell.setCellStyle(doubleStyle);
					cell.setCellValue("合计");
					for (int j = 1, col = 1; j < colEnd; j++) {
						if (zeroCols.containsKey(j)) {
							j += zeroCols.get(j) - 1;
							continue;
						}
						if (cellInfo.charAt(col) == '1') {
							cell = rowBody.createCell(col);
							cell.setCellStyle(doubleStyle);
							cell.setCellValue(cellSum[col]
									.setScale(3, BigDecimal.ROUND_HALF_UP)
									.stripTrailingZeros().toPlainString());
						}
						col++;
					}
				}
			}
		}
		rowBody = null;
		cell = null;
	}
	
	public  void createSheetTitleNormal(Sheet sheet, int startR,
			List<String> headList, CellStyle headStyle) {
		Row row_head = sheet.createRow(startR);
		Cell cell = null;
		int start_col = 0;// 起始列
		String headStr = null;
		for (int i = 0; i < headList.size(); i++) {
			headStr = String.valueOf(headList.get(i));
			cell = row_head.createCell(start_col + i);
			cell.setCellValue(headStr);
			cell.setCellStyle(headStyle);
		}
	}

	/**
	 * @Description: Excel 表头为固定2行
	 */
	public  void createSheetTitle(Sheet sheet, int startR,
			List<String> headList, CellStyle headStyle) {
		Row row_head_0 = null;
		Row row_head_1 = null;
		Cell cell = null;
		// 保存未拆分时的值
		String attr = null;
		// 保存第一次拆分时的值
		String[] tmp = null;
		// 拆分后第一行的值
		String[] tmp2 = null;
		// 拆分后第2行的值
		String[] tmp3 = null;
		// 第一次匹配时的值
		String preAttr = null;
		// 下一次匹配时的值
		String lastAttr = null;
		int preIdx = 0;
		int start_row = startR;// 起始行
		int start_col = 0;// 起始列
		row_head_0 = sheet.createRow(start_row + 0);
		row_head_1 = sheet.createRow(start_row + 1);
		CellRangeAddress range = new CellRangeAddress(0, 0, 0, 0);
		for (int i = 0; i < headList.size(); i++) {
			attr = String.valueOf(headList.get(i));
			// 以$分割数据
			tmp = attr.split("\\$");
			if (tmp.length == 1) {
				cell = row_head_0.createCell(start_col + i);
				// #分割的是行 列信息
				cell.setCellValue(tmp[0].split("\\#")[0]);
				cell.setCellStyle(headStyle);
				cell = row_head_1.createCell(start_col + i);
				cell.setCellStyle(headStyle);
				// 创建cell主要是指定样式,防止跨列时少样式
				range.setFirstRow(start_row + 0);
				range.setLastRow(start_row + 1);
				range.setFirstColumn(start_col + i);
				range.setLastColumn(start_col + i);
				sheet.addMergedRegion(range);
				preIdx = i + 1;
			} else if (tmp.length == 2) {
				preAttr = tmp[0];
				tmp2 = tmp[0].split("\\#");
				tmp3 = tmp[1].split("\\#");
				int tmpI = Integer.parseInt(tmp2[2]);// 跨列
				int tmpI2 = Integer.parseInt(tmp3[2]);// 跨列信息
				if (!preAttr.equals(lastAttr)) {
					lastAttr = tmp[0];
					cell = row_head_0.createCell(start_col + i);
					cell.setCellValue(tmp2[0]);
					cell.setCellStyle(headStyle);
					range.setFirstRow(start_row + 0);
					range.setLastRow(start_row + 0);
					range.setFirstColumn(start_col + preIdx);
					range.setLastColumn(start_col + preIdx + tmpI - 1);
					sheet.addMergedRegion(range);
				}
				cell = row_head_1.createCell(start_col + i);
				cell.setCellValue(tmp3[0]);
				cell.setCellStyle(headStyle);
				// 第二行跨列
				if (tmpI2 != 1) {
					range.setFirstRow(start_row + 1);
					range.setLastRow(start_row + 1);
					range.setFirstColumn(start_col + preIdx);
					range.setLastColumn(start_col + preIdx + tmpI2 - 1);
					sheet.getRow(start_row + 1)
							.createCell(start_col + preIdx + tmpI2 - 1)
							.setCellStyle(headStyle);
					sheet.addMergedRegion(range);
					start_col = start_col + tmpI2 - 1;
				}
				preIdx++;
			}
		}
	}

}

 

    效果如下:

    5000一个sheet页:

   

       1000一个sheet页:

      

       去除0值:

      

       转载请注明原处,原文链接: http://53873039oycg.iteye.com/blog/2142939,谢谢。

       全文完

   

  • 大小: 162.8 KB
  • 大小: 130.1 KB
  • 大小: 186.3 KB
0
0
分享到:
评论

相关推荐

    poi 基于excel模板导出功能

    2. 分批次写入:如果数据量过大,可分批次读取和写入,减少一次性内存占用。 六、异常处理 在实际应用中,需要添加适当的异常处理机制,如FileNotFoundException、IOException等,确保程序的健壮性。 总结,Apache...

    POI实现的基于动态模板的EXCEL数据导出

    它可能采用了分批次写入或者优化的内存管理策略,以减少内存占用并提高处理速度。同时,提及的“使用方法见com.teacherInfo.export.test.Test”,意味着在提供的代码示例中,你可以找到一个名为Test的测试类,这个类...

    jsp导出excel并支持分sheet导出的方法.docx

    可以通过分批次导出或者使用流式写入的方式来减少内存占用。 2. **异常处理**:在实际开发中,需要充分考虑到各种可能发生的异常情况,并进行妥善处理,比如文件路径不存在、文件已存在等。 3. **安全性**:在...

    easyExcel实现大数据导出

    在Java开发中,数据导出是一项常见的任务,特别是在大数据处理中。阿里巴巴的开源库`easyExcel`为此提供了高效且易用的解决方案。`easyExcel`是专门为处理大量数据设计的一个轻量级工具,它能够有效地解决内存溢出...

    springboot + poi导出指定格式Excel模板

    此外,为了提高性能,可以考虑使用内存映射或分批次处理大量数据。 总结起来,SpringBoot结合Apache POI提供了一种强大而灵活的方式来导出指定格式的Excel模板。通过创建工作簿、工作表、行和单元格,我们可以轻松...

    poi-4.1.2.zip

    - 分批次处理大文件以避免内存溢出。 - 使用`SXSSFWorkbook`代替`XSSFWorkbook`,对于大量数据写入,可以提高性能并降低内存需求。 通过理解和熟练应用Apache POI 4.1.2,开发者可以高效地在Java应用程序中处理...

    Excel导出数据(根据Excel模板定义)

    可以采用流式处理或分批次写入来优化内存使用。 - **SXSSFWorkbook**:Apache POI提供SXSSFWorkbook类,支持在硬盘上临时存储部分数据,减少内存占用。 5. **项目集成**: - **Maven或Gradle**:通常会将Apache ...

    Java文件导入导出

    关键在于合理分批次处理数据,避免内存溢出。 3. **批量导出**:导出数据到Excel文件通常用于报表生成。Java程序可以遍历数据库查询结果或其他数据源,然后使用POI将这些数据写入新创建或已存在的Excel文件。 二、...

    java导出大批量(百万以上)数据的excel文件

    - 并行处理:可以利用多线程并行导出不同批次的数据,进一步提高效率。 - 延迟写入:在数据量非常大的情况下,可以采用延迟写入策略,先将数据缓存起来,达到一定阈值或者指定条件时才批量写入文件。 总结来说,...

    我的boss的excel导出解决方案20170316

    - **性能优化**:对于大量数据的处理,可以考虑分批次上传或者采用异步处理机制,避免长时间阻塞用户界面。 - **异常处理**:添加异常处理逻辑,确保程序在遇到问题时能够妥善处理并给出反馈。 综上所述,本方案...

    easyExcel.zip

    - **合理使用监听器**:在处理大量数据时,利用监听器进行分批次处理,避免一次性加载过多数据。 - **优化性能**:根据实际业务场景选择合适的读写模式,如流式、缓存等。 - **错误处理**:在读取过程中,注意...

    NPOI汇出excel例子

    在大数据量汇出时,考虑到性能和内存消耗,我们需要采用分批次写入的方式。这可以通过创建一个缓冲区,将数据分批写入到Excel工作表中,而不是一次性加载所有数据。例如,我们可以设定每次写入1000行数据,然后清空...

Global site tag (gtag.js) - Google Analytics