POI导出Excel(1000列)优化方案记录
中午我想了下，其实上午写的那个导出方法瓶颈在删除Excel的列，如果能提前删除，那速度就很快了，我也看了下项目的导出方法，发现是写List<List>数据，并不是我想象中的使用ResultSet，所以中午把我写的方法优化了下，导出速度还不错,现在我对我的方法很满意。
思路：
(1)先遍历数据集得到满足值全部为0的index位置,记录到nullList中
(2)删除表头中下标和nullList下标相同的数据
(3)数据集写入时候判断是否在nullList中，有则跳过。
下面直接上代码了，我就不解释了
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;
import java.util.TreeSet;
import org.apache.poi.POIXMLProperties;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class 删除Excel的零值 {
/**
* @param args
*/
public static void main(String[] args) throws Exception {
删除Excel的零值 t = new 删除Excel的零值();
XSSFWorkbook wb = new XSSFWorkbook();
Map<String, XSSFCellStyle> styles = t.createStyles(wb);
String sheetName = "测试数据";
int startR = 2;// 第二行开始写数据
int totalCol = 1000;
XSSFSheet sheet = wb.createSheet(sheetName);
int[] headInfo = new int[] { 9 + totalCol * 3, 9, 3 };
System.out.println(headInfo[0] + "---=" + headInfo[1] + "---="
+ headInfo[2]);
long start = System.currentTimeMillis();
// 填充数据
t.createSheetBody(wb, sheet, styles, totalCol, startR, 40, headInfo);
t.createExcelInfo(wb);
String fileName = "f:/saveFile/temp/" + System.currentTimeMillis()
+ ".xlsx";
t.writeExcel(wb, fileName);
System.out.println((System.currentTimeMillis() - start) / 1000 + "秒");
styles = null;
wb = null;
startR = 0;// 删除标题列
}
private void createSheetTitle(XSSFSheet sheet, List headList,
XSSFCellStyle headStyle) {
createSheetTitle(sheet, 0, headList, headStyle);
}
/**
*
* @param sheet
* @param styles
* @param startR
* 开始行
* @param totalNum
* @param headinfo
* 0:列总长度 1子列开始位置 2子列长度
* @throws Exception
*/
public void createSheetBody(XSSFWorkbook wb, XSSFSheet sheet,
Map<String, XSSFCellStyle> styles, int totalCol, int startR,
int totalNum, int[] headinfo) throws Exception {
// 先产生所有的List数据,在挑选满足等于0的index，干掉index，在写如Excel
XSSFCellStyle cell_style = styles.get("cell_center");
XSSFCellStyle long_style = styles.get("cell_long");
XSSFRow row = null;
XSSFCell cell = null;
//得到测试数据
List<List<Object>> tmpDataList = generateTestData(startR, totalNum,
headinfo);
//产生表头数据
List<String> header = generateExcelHead(totalCol);
List<String> realHeader = new ArrayList<String>();
List<Integer> nullList = deleteListNullValue(tmpDataList, headinfo);
// 删除表头空的数据
List<Object> dataList = null;
int n = 0, col = 0;
for (int i = 0, len = header.size(); i < len; i++) {
if (n < nullList.size() && i == nullList.get(n)) {
//注意为空的子标题数量
for (int k = 1; k < headinfo[2]; k++) {
i++;
}
n++;
} else {
realHeader.add(header.get(i));
}
}
header = null;
n = 0;
// 创建表头
createExcelHeader(wb, sheet, styles, realHeader);
realHeader = null;
for (int i = 0; i < totalNum; i++) {
n = 0;
col = 0;
dataList = tmpDataList.get(i);
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());
col++;
}
//判断下标是否满足条件
for (int s4 = headinfo[1], len4 = headinfo[0]; s4 < len4; s4++) {
//满足值为0,跳过
if (n < nullList.size() && s4 == nullList.get(n)) {
for (int k = 1; k < headinfo[2]; k++) {
s4++;
}
n++;
} else {
cell = row.createCell(col);
cell.setCellStyle(long_style);
cell.setCellValue((Integer) dataList.get(s4));
col++;
}
}
cell = null;
row = null;
dataList.clear();
dataList = null;
}
cell = null;
row = null;
dataList = null;
nullList = null;
tmpDataList = null;
}
// 产生随机数据
public List<List<Object>> generateTestData(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;
}
System.out.println();
List<List<Object>> tmpDataList = new ArrayList<List<Object>>();
List<Object> dataList = null;
random = new Random();
for (int i = 1, len = totalNum + 1; i < len; i++) {
dataList = new ArrayList<Object>();
n = 0;
dataList.add(i);
for (int k = 1; k < headinfo[1]; k++) {
dataList.add("测试" + (i));
}
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));
}
tmpDataList.add(dataList);
dataList = null;
}
return tmpDataList;
}
public List<Integer> deleteListNullValue(List<List<Object>> tmpValueList,
int[] headinfo) {
List<Integer> tmpNullIndexList = new ArrayList<Integer>();
List<Integer> nullIndexList = null;
int startIndex = headinfo[1];
int subLen = headinfo[2];
int beforeIndex = 0;
List<Object> list = null;
boolean isFirst = true;
for (int j = 0, len2 = tmpValueList.size(); j < len2; j++) {
list = tmpValueList.get(j);
nullIndexList = new ArrayList<Integer>();
isFirst = true;
for (int i = startIndex, len = list.size(); i < len; i++) {
if (isFirst && "0".equals(list.get(i).toString())) {
beforeIndex = i;
isFirst = false;
} else if (!"0".equals(list.get(i).toString())) {
beforeIndex = i;
isFirst = true;
} else if (!isFirst && (i - beforeIndex + 1) == subLen) {
nullIndexList.add(beforeIndex);
isFirst = true;
}
}
if (j == 0) {
tmpNullIndexList = new ArrayList(nullIndexList);
} else {
tmpNullIndexList.retainAll(nullIndexList);
tmpNullIndexList = new ArrayList(tmpNullIndexList);
}
nullIndexList.clear();
nullIndexList = null;
}
for (Integer it : tmpNullIndexList) {
System.out.print(it + ",");
}
System.out.println();
list = null;
nullIndexList = null;
return tmpNullIndexList;
}
public void createExcelInfo(XSSFWorkbook workbook) {
POIXMLProperties.CoreProperties coreProp = workbook.getProperties()
.getCoreProperties();
coreProp.setCreator("测试作者属性");
coreProp.setCategory("测试类别属性");
coreProp.setTitle("Excel标题属性");
coreProp.setSubjectProperty("测试主题属性");
coreProp.setKeywords("报表测试,POI测试");
}
/**
* 主要思路：优先把第一行跨多列的数据先合并,再合并第2行子数据,暂只支持2行N列 如果要修改该功能，建议重写一个更简单，如果看不懂我写的
*
* @param sheet
* @startR 从第几行开始(下标从0开始)
* @param headList
* @param headStyle
*/
private void createSheetTitle(XSSFSheet sheet, int startR, List headList,
XSSFCellStyle headStyle) {
XSSFRow row_head_0 = null;
XSSFRow row_head_1 = null;
XSSFCell 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主要是指定样式,防止跨列时少样式
for (int k = start_col + i + 1; k <= start_col + i; k++) {
row_head_0.createCell(k).setCellStyle(headStyle);
row_head_1.createCell(k).setCellStyle(headStyle);
}
range.setFirstRow(start_row + 0);
range.setLastRow(start_row + 1);
range.setFirstColumn(start_col + i);
range.setLastColumn(start_col + i);
sheet.addMergedRegion(range);
if ("xsd".equalsIgnoreCase(tmp[0].split("\\#")[0])) {
sheet.setColumnWidth(i, 50 * 256); // 256基本单位
} else if (tmp[0].split("\\#")[0].getBytes().length > 16) {
sheet.setColumnWidth(i, 25 * 256); // 256基本单位
}
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);
// 创建cell主要是指定样式,防止跨列时少样式
for (int k = start_col + preIdx + 1; k <= start_col
+ preIdx + tmpI - 1; k++) {
row_head_0.createCell(k).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) {
// 创建cell主要是指定样式,防止跨列时少样式
for (int k = start_col + preIdx + 1; k <= start_col
+ preIdx + tmpI2 - 1; k++) {
row_head_1.createCell(k).setCellStyle(headStyle);
}
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++;
}
}
}
// 创建样式库
private 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;
}
public void writeExcel(XSSFWorkbook 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();
}
}
}
public List<String> generateExcelHead(int subTotal) {
List<String> header = new ArrayList<String>();
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$");
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$");
}
header.add("总数量#2#1$");
header.add("总金额#2#1$");
header.add("总计#2#1$");
return header;
}
public void createExcelHeader(XSSFWorkbook wb, XSSFSheet sheet,
Map<String, XSSFCellStyle> styles, List<String> header)
throws Exception {
XSSFCellStyle titleStyle = null;
titleStyle = styles.get("sheet_title");
// 为某一位sheet填充数据
createSheetTitle(sheet, header, titleStyle);
titleStyle = null;
header = null;
}
}
结果为：
也可以使用SXSSFWorkbook，导出100行,1000列速度还不错。
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;
import java.util.TreeSet;
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零值优化版 {
/**
* @param args
*/
public static void main(String[] args) throws Exception {
删除Excel零值优化版 t = new 删除Excel零值优化版();
Workbook wb = new SXSSFWorkbook(200);
Map<String, CellStyle> styles = t.createStyles(wb);
String sheetName = "测试数据";
int startR = 2;// 第二行开始写数据
int totalCol = 1000;
Sheet sheet = wb.createSheet(sheetName);
int[] headInfo = new int[] { 9 + totalCol * 3, 9, 3 };
System.out.println(headInfo[0] + "---=" + headInfo[1] + "---="
+ headInfo[2]);
long start = System.currentTimeMillis();
// 填充数据
t.createSheetBody(wb, sheet, styles, totalCol, startR, 100, headInfo);
String fileName = "f:/saveFile/temp/" + System.currentTimeMillis()
+ ".xlsx";
t.writeExcel(wb, fileName);
System.out.println((System.currentTimeMillis() - start) / 1000 + "秒");
styles = null;
wb = null;
startR = 0;// 删除标题列
}
private void createSheetTitle(Sheet sheet, List headList,
CellStyle headStyle) {
createSheetTitle(sheet, 0, headList, headStyle);
}
/**
*
* @param sheet
* @param styles
* @param startR
* 开始行
* @param totalNum
* @param headinfo
* 0:列总长度 1子列开始位置 2子列长度
* @throws Exception
*/
public void createSheetBody(Workbook wb, Sheet sheet,
Map<String, CellStyle> styles, int totalCol, int startR,
int totalNum, int[] headinfo) throws Exception {
// 先产生所有的List数据,在挑选满足等于0的index，干掉index，在写如Excel
CellStyle cell_style = styles.get("cell_center");
CellStyle long_style = styles.get("cell_long");
Row row = null;
Cell cell = null;
// 得到测试数据
List<List<Object>> tmpDataList = generateTestData(startR, totalNum,
headinfo);
// 产生表头数据
List<String> header = generateExcelHead(totalCol);
List<String> realHeader = new ArrayList<String>();
List<Integer> nullList = deleteListNullValue(tmpDataList, headinfo);
// 删除表头空的数据
List<Object> dataList = null;
int n = 0, col = 0;
for (int i = 0, len = header.size(); i < len; i++) {
if (n < nullList.size() && i == nullList.get(n)) {
// 注意为空的子标题数量
for (int k = 1; k < headinfo[2]; k++) {
i++;
}
n++;
} else {
realHeader.add(header.get(i));
}
}
header = null;
n = 0;
// 创建表头
createExcelHeader(wb, sheet, styles, realHeader);
realHeader = null;
for (int i = 0; i < totalNum; i++) {
n = 0;
col = 0;
dataList = tmpDataList.get(i);
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());
col++;
}
// 判断下标是否满足条件
for (int s4 = headinfo[1], len4 = headinfo[0]; s4 < len4; s4++) {
// 满足值为0,跳过
if (n < nullList.size() && s4 == nullList.get(n)) {
for (int k = 1; k < headinfo[2]; k++) {
s4++;
}
n++;
} else {
cell = row.createCell(col);
cell.setCellStyle(long_style);
cell.setCellValue((Integer) dataList.get(s4));
col++;
}
}
dataList.clear();
dataList = null;
}
cell = null;
row = null;
dataList = null;
nullList = null;
tmpDataList = null;
}
// 产生随机数据
public List<List<Object>> generateTestData(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;
}
System.out.println();
List<List<Object>> tmpDataList = new ArrayList<List<Object>>();
List<Object> dataList = null;
random = new Random();
for (int i = 1, len = totalNum + 1; i < len; i++) {
dataList = new ArrayList<Object>();
n = 0;
dataList.add(i);
for (int k = 1; k < headinfo[1]; k++) {
dataList.add("测试" + (i));
}
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));
}
tmpDataList.add(dataList);
dataList = null;
}
return tmpDataList;
}
public List<Integer> deleteListNullValue(List<List<Object>> tmpValueList,
int[] headinfo) {
List<Integer> tmpNullIndexList = new ArrayList<Integer>();
List<Integer> nullIndexList = null;
int startIndex = headinfo[1];
int subLen = headinfo[2];
int beforeIndex = 0;
List<Object> list = null;
boolean isFirst = true;
for (int j = 0, len2 = tmpValueList.size(); j < len2; j++) {
list = tmpValueList.get(j);
nullIndexList = new ArrayList<Integer>();
isFirst = true;
for (int i = startIndex, len = list.size(); i < len; i++) {
if (isFirst && "0".equals(list.get(i).toString())) {
beforeIndex = i;
isFirst = false;
} else if (!"0".equals(list.get(i).toString())) {
beforeIndex = i;
isFirst = true;
} else if (!isFirst && (i - beforeIndex + 1) == subLen) {
nullIndexList.add(beforeIndex);
isFirst = true;
}
}
if (j == 0) {
tmpNullIndexList = new ArrayList(nullIndexList);
} else {
tmpNullIndexList.retainAll(nullIndexList);
tmpNullIndexList = new ArrayList(tmpNullIndexList);
}
nullIndexList.clear();
nullIndexList = null;
}
for (Integer it : tmpNullIndexList) {
System.out.print(it + ",");
}
System.out.println();
list = null;
nullIndexList = null;
return tmpNullIndexList;
}
/**
* 主要思路：优先把第一行跨多列的数据先合并,再合并第2行子数据,暂只支持2行N列 如果要修改该功能，建议重写一个更简单，如果看不懂我写的
*
* @param sheet
* @startR 从第几行开始(下标从0开始)
* @param headList
* @param headStyle
*/
private void createSheetTitle(Sheet sheet, int startR, List 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主要是指定样式,防止跨列时少样式
for (int k = start_col + i + 1; k <= start_col + i; k++) {
row_head_0.createCell(k).setCellStyle(headStyle);
row_head_1.createCell(k).setCellStyle(headStyle);
}
range.setFirstRow(start_row + 0);
range.setLastRow(start_row + 1);
range.setFirstColumn(start_col + i);
range.setLastColumn(start_col + i);
sheet.addMergedRegion(range);
if ("xsd".equalsIgnoreCase(tmp[0].split("\\#")[0])) {
sheet.setColumnWidth(i, 50 * 256); // 256基本单位
} else if (tmp[0].split("\\#")[0].getBytes().length > 16) {
sheet.setColumnWidth(i, 25 * 256); // 256基本单位
}
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);
// 创建cell主要是指定样式,防止跨列时少样式
for (int k = start_col + preIdx + 1; k <= start_col
+ preIdx + tmpI - 1; k++) {
row_head_0.createCell(k).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) {
// 创建cell主要是指定样式,防止跨列时少样式
for (int k = start_col + preIdx + 1; k <= start_col
+ preIdx + tmpI2 - 1; k++) {
row_head_1.createCell(k).setCellStyle(headStyle);
}
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++;
}
}
}
// 创建样式库
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> stylesMap = new HashMap<String, CellStyle>();
CellStyle style = null;
Font sheetFont = wb.createFont();
sheetFont.setColor(IndexedColors.WHITE.getIndex());
sheetFont.setFontName("楷体");
sheetFont.setFontHeightInPoints((short) 10);
sheetFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setFont(sheetFont);
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderTop(CellStyle.BORDER_DOTTED);
style.setTopBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderLeft(CellStyle.BORDER_DOTTED);
style.setLeftBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderRight(CellStyle.BORDER_DOTTED);
style.setRightBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderBottom(CellStyle.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(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setFont(sheetFont);
style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderTop(CellStyle.BORDER_DOTTED);
style.setTopBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderLeft(CellStyle.BORDER_DOTTED);
style.setLeftBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderRight(CellStyle.BORDER_DOTTED);
style.setRightBorderColor(IndexedColors.WHITE.getIndex());
style.setBorderBottom(CellStyle.BORDER_DOTTED);
style.setBottomBorderColor(IndexedColors.WHITE.getIndex());
stylesMap.put("sheet_title_style", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
stylesMap.put("cell_center", style);
style = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
style.setDataFormat(format.getFormat("0"));
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
stylesMap.put("cell_long", style);
return stylesMap;
}
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();
}
}
}
public List<String> generateExcelHead(int subTotal) {
List<String> header = new ArrayList<String>();
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$");
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$");
}
header.add("总数量#2#1$");
header.add("总金额#2#1$");
header.add("总计#2#1$");
return header;
}
public void createExcelHeader(Workbook wb, Sheet sheet,
Map<String, CellStyle> styles, List<String> header)
throws Exception {
CellStyle titleStyle = null;
titleStyle = styles.get("sheet_title");
// 为某一位sheet填充数据
createSheetTitle(sheet, header, titleStyle);
titleStyle = null;
header = null;
}
}
待解决问题：
(1)多个Excel合并为一个Excel
本文系原创，转载请注明出处，谢谢。
全文完。