普通方式导出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_Exp_S01_Test {
public static void main(String[] args) {
Excel_Exp_S01_Test t = new Excel_Exp_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);
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++;
}
}
}
}
via: http://53873039oycg.iteye.com/blog/2142939
poi多sheet分批次导出
普通方式导出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_Exp_S01_Test {
public static void main(String[] args) {
Excel_Exp_S01_Test t = new Excel_Exp_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);
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值:
- 大小: 162.8 KB
- 大小: 130.1 KB
- 大小: 186.3 KB
- 查看图片附件
