解决方法
写excel时使用 SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook , 1000);只在内存中留1000行，不会占用过多的内存。下面只贴了部分代码。
public static void createExcelByTrade(List<String[]> mergeCellConfigList , Map<String, String[]> cellConfMap , List dataList ,String tempPath , String fileName ) {
try {
int excelRowNum = 0;
short fontSize = 12;
// 创建新的Excel 工作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook , 1000);
Sheet sheet = workbook.createSheet(fileName);
// 设置合并表头
setMergeHeaderCellByTrade( workbook, sheet, mergeCellConfigList , excelRowNum, fontSize );
excelRowNum ++ ;
excelRowNum ++ ;
//设置表头
setHeaderCell( workbook, sheet, cellConfMap , excelRowNum, fontSize );
excelRowNum ++ ; //查询数据库中所有的数据
setCellData( workbook, sheet, cellConfMap, dataList, excelRowNum, fontSize);
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(tempPath);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
//清空缓冲区数据
fOut.flush();
// 操作结束，关闭文件
fOut.close();
System.out.println("文件生成...");
} catch (Exception e) {
e.printStackTrace();
System.out.println("已运行 xlCreate() : " + e);
}
}导出excel时进行合并单元格及样式设置。package com.sfit.fiss.otcdownload;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.bstek.dorado.common.UserConfig;
import com.sfit.fiss.util.ExcelUtil;
import com.sfit.framework.sp.SpCondition;
public class ExcelExportUtil {
public static void main(String[] args) throws SQLException {
String tempFilePath = "D:/Eclipse/WorkSpaceGanyMede/otcreport/temp/report/";
List<String[]> dataList = new ArrayList<String[]>();
//导出数据
exportPositionInfoData(dataList , tempFilePath) ;
}
/**
* 导出数据
* @param dataList
*/
static String exportPositionInfoData( List dataList, String tempFilePath) {
List<String[]> mergeCellConfigList = new ArrayList<String[]>();
mergeCellConfigList.add(new String[] {"主体信息" , "2" , "HeaderStyle_2" });
mergeCellConfigList.add(new String[] {"对手方信息" , "2" , "HeaderStyle_2" });
mergeCellConfigList.add(new String[] {"物信息" , "11" , "HeaderStyle_2" });
mergeCellConfigList.add(new String[] {"仓信息" , "8" , "HeaderStyle_2" });
//设置表头
Map<String, String[]> cellConfMap = new LinkedHashMap<String, String[]>();
cellConfMap.put("MAINBODYNAME", new String[] { "ASSS", "HeaderStyle_2", "DataStyle_TEXT_LC", "30" });
cellConfMap.put("NOCID", new String[] { "ASD", "HeaderStyle_2", "DataStyle_TEXT_LC", "30" });
cellConfMap.put("ANALOGUENAME", new String[] { "ASDASD", "HeaderStyle_2", "DataStyle_TEXT_LC", "30" });
cellConfMap.put("ANALOGUENOCID", new String[] { "ASDASDSD", "HeaderStyle_2", "DataStyle_TEXT_LC", "35" });
cellConfMap.put("TRANSCONFIRNUMBER", new String[] { "ASDASDAS", "HeaderStyle_2", "DataStyle_TEXT_LC", "20" });
cellConfMap.put("TRANSCONFIRTIME", new String[] { "DFGDFG", "HeaderStyle_2", "DataStyle_TEXT_LC", "15" });
SpCondition sc = new SpCondition();
String fileName = "数据";
String tempPath = tempFilePath +"OTCPositionData.xlsx";
createExcel( mergeCellConfigList , cellConfMap , dataList , tempPath, fileName);
return tempPath;
}
@SuppressWarnings("deprecation")
public static void createExcel(List<String[]> mergeCellConfigList , Map<String, String[]> cellConfMap , List dataList ,String tempPath , String fileName ) {
try {
int excelRowNum = 0;
short fontSize = 12;
// 创建新的Excel 工作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, 1000);
Sheet sheet = workbook.createSheet(fileName);
// 设置合并表头
setMergeHeaderCell( workbook, sheet, mergeCellConfigList , excelRowNum, fontSize );
excelRowNum ++ ;
//设置表头
setHeaderCell( workbook, sheet, cellConfMap , excelRowNum, fontSize );
excelRowNum ++ ;
//设置cell自动宽度
//setAutoSizeColumn(sheet, cellConfMap.size());
//查询数据库中所有的数据
setCellData( workbook, sheet, cellConfMap, dataList, excelRowNum, fontSize);
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(tempPath);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
//清空缓冲区数据
fOut.flush();
// 操作结束，关闭文件
fOut.close();
System.out.println("文件生成...");
} catch (Exception e) {
e.printStackTrace();
System.out.println("已运行 xlCreate() : " + e);
}
}
//向单元格 填充数据
private static void setCellData( SXSSFWorkbook workbook, Sheet sheet, Map<String, String[]> cellConfMap, List list, int excelRowNum, short fontSize) throws SQLException, Exception {
Row row = null ;
Cell cell = null ;
//生成 样式
Map<String , XSSFCellStyle > styleMap = new HashMap<String, XSSFCellStyle>();
if(cellConfMap!=null && !cellConfMap.isEmpty()) {
for(String keyStr: cellConfMap.keySet() ) {
styleMap.put(keyStr, ExcelExportUtil.createCellStyle(workbook, cellConfMap.get(keyStr)[2], fontSize));
}
}
if(list != null && !list.isEmpty()) {
ResultSet rs = (ResultSet) list.get(0);
if(cellConfMap != null && !cellConfMap.isEmpty()) {
while(rs.next()){
row = sheet.createRow(excelRowNum);
int cellNum = 0;
for (String keyStr: cellConfMap.keySet()) {
cell = row.createCell(cellNum);
cell.setCellStyle(styleMap.get(keyStr));
cell.setCellValue(rs.getString(keyStr));
cellNum++;
}
excelRowNum ++ ;
}
}
}
}
/**
* 设置普通表头信息
* @param workbook
* @param sheet
* @param cellConfMap
* @param excelRowNum
* @param fontSize
* @throws Exception
*/
private static void setHeaderCell( SXSSFWorkbook workbook, Sheet sheet, Map<String, String[]> cellConfMap ,int excelRowNum, short fontSize ) throws Exception {
Row row;
Cell cell;
XSSFCellStyle style;
row = sheet.createRow(excelRowNum);
if(cellConfMap != null && !cellConfMap.isEmpty() ) {
int cellNum = 0;
for(String keyStr: cellConfMap.keySet()) {
cell = row.createCell(cellNum);
cell.setCellValue(cellConfMap.get(keyStr)[0]); // 表头列名
// 设置类表宽度
sheet.setColumnWidth(cellNum, 256 * Integer.parseInt((cellConfMap.get(keyStr)[3] != null && !"".equals(cellConfMap.get(keyStr)[3])) ? cellConfMap.get(keyStr)[3] : "10"));
// 更具样式类型设置表头样式HeaderStyle
String headerStyleTypeStr = cellConfMap.get(keyStr)[1];
if (headerStyleTypeStr == null && "".equals(headerStyleTypeStr)) {
headerStyleTypeStr = "HeaderStyle";
}
style = createCellStyle(workbook, headerStyleTypeStr, fontSize);
cell.setCellStyle(style);
cellNum++;
}
}
}
/**
* 生成成交的合并表头信息
* @param workbook
* @param sheet
* @param mergeCellConfigList
* @param excelRowNum
* @param fontSize
* @throws Exception
*/
private static void setMergeHeaderCellByTrade( SXSSFWorkbook workbook, Sheet sheet, List<String[]> mergeCellConfigList ,int excelRowNum, short fontSize ) throws Exception {
Row row = null ;
int startCellIndex = 0;
int endCellIndex = 0;
Cell cell = null;
XSSFCellStyle style = null;
if (mergeCellConfigList != null && !mergeCellConfigList.isEmpty()) {
row = sheet.createRow(excelRowNum);
for (String[] mergeParamItem : mergeCellConfigList) {
if (endCellIndex != 0) {
startCellIndex = (endCellIndex + 1);
}
endCellIndex = (startCellIndex + Integer.parseInt(mergeParamItem[1]) - 1);
if(mergeParamItem[0] != null && "交易信息".equals(mergeParamItem[0])){
sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, startCellIndex, endCellIndex));
}else {
sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum +1, startCellIndex, endCellIndex));
}
endCellIndex = (startCellIndex + Integer.parseInt(mergeParamItem[1]) - 1);
cell = row.createCell(startCellIndex );
style = ExcelExportUtil.createCellStyle(workbook, "HeaderStyle", fontSize);
cell.setCellStyle(style);
cell.setCellValue(mergeParamItem[0]);
}
}
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 0, 1));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 2, 3));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 4, 5));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 6, 7));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 8, 9));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 10, 10));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 11, 34));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 35, 36));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 37, 41));
excelRowNum=excelRowNum+1;
row = sheet.createRow(excelRowNum);
cell = row.createCell(11 );
style = ExcelExportUtil.createCellStyle(workbook, "HeaderStyle_2", fontSize);
cell.setCellStyle(style);
cell.setCellValue("交易编码");
cell = row.createCell(12);
style = ExcelExportUtil.createCellStyle(workbook, "HeaderStyle_2", fontSize);
cell.setCellStyle(style);
cell.setCellValue("时间信息");
cell = row.createCell(17);
style = ExcelExportUtil.createCellStyle(workbook, "HeaderStyle_2", fontSize);
cell.setCellStyle(style);
cell.setCellValue("产品信息及标的物信息");
cell = row.createCell(29);
style = ExcelExportUtil.createCellStyle(workbook, "HeaderStyle_2", fontSize);
cell.setCellStyle(style);
cell.setCellValue("价格及价值信息");
sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, 12, 16));
sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, 17, 28));
sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, 29, 34));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 0, 1));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 2, 3));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 4, 5));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 6, 7));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 8, 9));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 10, 10));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 11, 11));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 12, 16));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 17, 28));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 29, 34));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 35, 36));
ExcelExportUtil.setMergedCellStyle(workbook, sheet, new CellRangeAddress(excelRowNum, excelRowNum, 37, 41));
}
/**
* 设置表头信息
* @param workbook
* @param sheet
* @param mergeCellConfigList
* @param excelRowNum
* @param fontSize
* @throws Exception
*/
private static void setMergeHeaderCell( SXSSFWorkbook workbook, Sheet sheet, List<String[]> mergeCellConfigList ,int excelRowNum, short fontSize ) throws Exception {
Row row = null ;
int startCellIndex = 0;
int endCellIndex = 0;
Cell cell = null;
if (mergeCellConfigList != null && !mergeCellConfigList.isEmpty()) {
row = sheet.createRow(excelRowNum);
for (String[] mergeParamItem : mergeCellConfigList) {
if (endCellIndex != 0) {
startCellIndex = (endCellIndex + 1);
}
endCellIndex = (startCellIndex + Integer.parseInt(mergeParamItem[1]) - 1);
sheet.addMergedRegion(new CellRangeAddress(excelRowNum, excelRowNum, startCellIndex, endCellIndex));
cell = row.createCell(startCellIndex);
cell.setCellStyle(ExcelExportUtil.createCellStyle(workbook, mergeParamItem[2], fontSize));
cell.setCellValue(mergeParamItem[0]);
}
}
}
private static void setAutoSizeColumn(Sheet sheet , int cellNum) {
//设置列宽
if(sheet!= null ) {
for(int index = 0 ; index<=cellNum ; index++ ) {
sheet.autoSizeColumn(index, true);
//sheet.setColumnWidth(index,sheet.getColumnWidth(index));
}
}
}
/* 更具单元格样式类型 生成相应的单元格样式 */
public static XSSFCellStyle createCellStyle(SXSSFWorkbook workbook, String styleType, short fontSize)
throws Exception {
// 设置数据类型
XSSFDataFormat dataFormat = (XSSFDataFormat) workbook.createDataFormat();
XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
if (styleType != null && "HeaderStyle".equals(styleType)) { // 表头数据格式 HeaderStyle
/*
* // 背景色 style.setFillForegroundColor((short) 11);
* style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
* style.setFillBackgroundColor((short) 11);
*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
/*style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());*/
} else if (styleType != null && "HeaderStyle_1".equals(styleType)) { // 表头数据格式 HeaderStyle1
// 背景色
/*
* style.setFillForegroundColor((short) 8);
* style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
* style.setFillBackgroundColor((short) 8);
*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
} else if (styleType != null && "HeaderStyle_2".equals(styleType)) { // 表头数据格式 HeaderStyle2
// 背景色
/* style.setFillForegroundColor((short) 9);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 9);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
} else if (styleType != null && "HeaderStyle_3".equals(styleType)) { // 表头数据格式 HeaderStyle3
// 背景色
/*
* style.setFillForegroundColor((short) 10);
* style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
* style.setFillBackgroundColor((short) 10);
*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
} else if (styleType != null && "DataStyle_TEXT".equals(styleType)) { // 数据单元格样式 文本
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
} else if (styleType != null && "DataStyle_TEXT_LC".equals(styleType)) { // 数据单元格样式 文本 水平靠左
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_LEFT);// 水平靠左
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
} else if (styleType != null && "DataStyle_TEXT_CC".equals(styleType)) { // 数据单元格样式 文本
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
} else if (styleType != null && "DataStyle_TEXT_RC".equals(styleType)) { // 数据单元格样式 文本
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平靠右
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
} else if (styleType != null && "DataStyle_TEXT_LC_LINE".equals(styleType)) { // 数据单元格样式 文本 水平靠左
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_LEFT);// 水平靠左
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(false);// 设置不自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
} else if (styleType != null && "DataStyle_NUMBER".equals(styleType)) { // 数据单元格样式 数字格式 整数
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setDataFormat(dataFormat.getFormat("#,#0"));
} else if (styleType != null && "DataStyle_NUMBER_F2".equals(styleType)) { // 数据单元格样式 数字格式 两位小数
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setDataFormat(dataFormat.getFormat("#,#0.00"));
} else if (styleType != null && "DataStyle_NUMBER_F3".equals(styleType)) { // 数据单元格样式 数字格式 三位小数
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setDataFormat(dataFormat.getFormat("#,#0.000"));
} else if (styleType != null && "DataStyle_NUMBER_F4".equals(styleType)) { //
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setDataFormat(dataFormat.getFormat("#,#0.0000"));
} else if (styleType != null && "DataStyle_DATE".equals(styleType)) { // 数据单元格样式 日期格式
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setDataFormat(dataFormat.getFormat("yyyy-MM-dd"));
} else if (styleType != null && "DataStyle_TIME".equals(styleType)) { // 数据单元格样式 时间格式
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setDataFormat(dataFormat.getFormat("hh:mm:ss"));
} else if (styleType != null && "DataStyle_DATETIME".equals(styleType)) { // 数据单元格样式 日期时间格式
// 背景色
/*style.setFillForegroundColor((short) 11);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor((short) 11);*/
// 居中显示
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName("宋体");
font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
// 设置自动换行
style.setWrapText(true);// 设置自动换行
/* 设置边框 */
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setDataFormat(dataFormat.getFormat("yyyy-MM-dd hh:mm:ss"));
} else { /* 默认样式 */
}
return style;
}
public static Sheet setMergedCellStyle(SXSSFWorkbook wb , Sheet sheet ,CellRangeAddress cra ) {
RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_THIN, cra, sheet , wb); // 下边框
RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_THIN, cra, sheet,wb); // 左边框
RegionUtil.setBorderRight(XSSFCellStyle.BORDER_THIN, cra, sheet,wb); // 有边框
RegionUtil.setBorderTop(XSSFCellStyle.BORDER_THIN, cra, sheet,wb); // 上边框
return sheet;
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
