importjava.io.FileOutputStream;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.LinkedHashMap;importjava.util.List;importjava.util.Map;importjava.util.UUID;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.IndexedColors;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.util.CellRangeAddress;importorg.apache.poi.ss.util.RegionUtil;importorg.apache.poi.xssf.streaming.SXSSFWorkbook;importorg.apache.poi.xssf.usermodel.XSSFCellStyle;importorg.apache.poi.xssf.usermodel.XSSFColor;importorg.apache.poi.xssf.usermodel.XSSFDataFormat;importorg.apache.poi.xssf.usermodel.XSSFFont;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importcom.bstek.dorado.common.UserConfig;importcom.sfit.fiss.util.ExcelUtil;importcom.sfit.framework.sp.SpCondition;public classExcelExportUtil {public static void main(String[] args) throwsSQLException {String tempFilePath= "D:/Eclipse/WorkSpaceGanyMede/otcreport/temp/report/";
List dataList = new ArrayList();
//导出数据
exportPositionInfoData(dataList , tempFilePath) ;
}/*** 导出数据
*@paramdataList*/
staticString exportPositionInfoData( List dataList, String tempFilePath) {
List mergeCellConfigList = new ArrayList();
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 cellConfMap = new LinkedHashMap();
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= newSpCondition();
String fileName= "数据";
String tempPath= tempFilePath +"OTCPositionData.xlsx";
createExcel( mergeCellConfigList , cellConfMap , dataList , tempPath, fileName);returntempPath;
}@SuppressWarnings("deprecation")public static void createExcel(List mergeCellConfigList , MapcellConfMap , List dataList ,String tempPath , String fileName ) {try{int excelRowNum = 0;short fontSize = 12;//创建新的Excel 工作簿XSSFWorkbook xssfWorkbook= newXSSFWorkbook();
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= newFileOutputStream(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 cellConfMap, List list, int excelRowNum, short fontSize) throwsSQLException, Exception {
Row row= null;
Cell cell= null;//生成 样式
Map styleMap = new HashMap();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++;
}
}
}
}/*** 设置普通表头信息
*@paramworkbook
*@paramsheet
*@paramcellConfMap
*@paramexcelRowNum
*@paramfontSize
*@throwsException*/
private static void setHeaderCell( SXSSFWorkbook workbook, Sheet sheet, Map cellConfMap ,int excelRowNum, short fontSize ) throwsException {
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++;
}
}
}/*** 生成成交的合并表头信息
*@paramworkbook
*@paramsheet
*@parammergeCellConfigList
*@paramexcelRowNum
*@paramfontSize
*@throwsException*/
private static void setMergeHeaderCellByTrade( SXSSFWorkbook workbook, Sheet sheet, List mergeCellConfigList ,int excelRowNum, short fontSize ) throwsException {
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(newCellRangeAddress(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));
}/*** 设置表头信息
*@paramworkbook
*@paramsheet
*@parammergeCellConfigList
*@paramexcelRowNum
*@paramfontSize
*@throwsException*/
private static void setMergeHeaderCell( SXSSFWorkbook workbook, Sheet sheet, List mergeCellConfigList ,int excelRowNum, short fontSize ) throwsException {
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(newCellRangeAddress(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 , intcellNum) {//设置列宽
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, shortfontSize)throwsException {//设置数据类型
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 { /*默认样式*/}returnstyle;
}public staticSheet 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);//上边框
returnsheet;
}
}