Renew
SQL 增/减 字段不需要发版本调整清单标题或模板
无视OOM、 CPU爆表
POM
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
Source Code
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.SimpleDateFormat;
import java.util.*;
public class PoiSXSSFWorkbookExcelUtil {
private static final Logger logger = LoggerFactory.getLogger(PoiSXSSFWorkbookExcel.class);
public static SXSSFWorkbook getSxssfwbManySameSheets(String sheetTitle, ResultSet rs) throws Exception{
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date dateBefore = new Date();
logger.debug("开始导出"+sheetTitle);
SXSSFWorkbook wb = new SXSSFWorkbook();
ResultSetMetaData data = rs.getMetaData();
int sheetNum = 0;// 记录额外创建的sheet数量
Sheet sheet = wb.createSheet(sheetTitle);
// wb.setSheetName(sheetNum, sheetTitle+sheetNum);
int rownum = 0;
Row row = sheet.createRow(rownum);
// 设置并获取到需要的样式
XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(wb);
Cell cell;
// 创建标题,此时row=0,即第一行
String[] titles = new String[data.getColumnCount()];
for (int j = 0; j < data.getColumnCount(); j++) {
//获得指定列的列名
String columnName = data.getColumnName(j+1);
titles[j] =columnName;
cell = row.createCell(j);
cell.setCellValue(columnName);
cell.setCellStyle(xssfCellStyleHeader);
}
int rowIndex =0;
while (rs.next()){
if ((rowIndex + 1) % 1048576 == 0) {
sheetNum++;
sheet = wb.createSheet(sheetTitle +"_"+ sheetNum);
row = sheet.createRow(0);
// 声明列对象，参数为列索引，可以是0～255之间的任何一个
// 创建标题,此时row=0,即第一行
for (int j = 0; j < titles.length; j++) {
cell = row.createCell(j);
cell.setCellValue(titles[j]);
cell.setCellStyle(xssfCellStyleHeader);
}
}
row = sheet.createRow((rowIndex + 1) - (sheetNum * 1048576)+sheetNum);
for (int cellIndex = 1; cellIndex <= data.getColumnCount(); cellIndex++) {
//获得指定列的列名
String columnName = data.getColumnName(cellIndex);
//获得指定列的值
String columnValue = rs.getString(cellIndex);
//对应数据类型的类
String columnClassName = data.getColumnClassName(cellIndex);
Object o = rs.getObject(columnName);
if("java.sql.Timestamp".equals(columnClassName)){
if(StringUtils.isNotBlank(columnValue)){
row.createCell((short) cellIndex-1).setCellValue(columnValue);
}
}
if(o instanceof Byte){
row.createCell((short) cellIndex-1).setCellValue((Byte) o);
}
if(o instanceof Short){
row.createCell((short) cellIndex-1).setCellValue((Short) o);
}
if(o instanceof Integer){
row.createCell((short) cellIndex-1).setCellValue((Integer) o);
}
if(o instanceof Long){
row.createCell((short) cellIndex-1).setCellValue((Long) o);
}
if(o instanceof Character){
row.createCell((short) cellIndex-1).setCellValue((Character) o);
}
if(o instanceof Float){
row.createCell((short) cellIndex-1).setCellValue((Float) o);
}
if(o instanceof Double){
row.createCell((short) cellIndex-1).setCellValue((Double) o);
}
if(o instanceof Boolean){
row.createCell((short) cellIndex-1).setCellValue((Boolean) o);
}
if(o instanceof BigDecimal){
row.createCell((short) cellIndex-1).setCellValue(((BigDecimal) o).doubleValue());
}
if(o instanceof String){
row.createCell((short) cellIndex-1).setCellValue((String) o);
}
if(o instanceof Date){
String dateStr = simpleDateFormat.format((Date)o);
row.createCell((short) cellIndex-1).setCellValue(dateStr);
}
}
rowIndex++;
}
Date dateAfter = new Date();
logger.info("共"+rowIndex+"条数据,导出列表共执行"+(dateAfter.getTime()-dateBefore.getTime())+"ms");
if(rowIndex==0){
throw new Exception(sheetTitle+"无数据");
}
return wb;
}
/**
*
* @param linkedHashMap 多个游标
* @return
* @throws Exception
*/
public static SXSSFWorkbook getSxssfwbManySameSheets(LinkedHashMap<String,ResultSet> linkedHashMap) throws Exception{
Date dateBefore = new Date();
SXSSFWorkbook wb = new SXSSFWorkbook();
int ExportRow = 0;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (Map.Entry<String,ResultSet> entry : linkedHashMap.entrySet()) {
ResultSet rs = entry.getValue();
String sheetTitle = entry.getKey();
logger.debug("开始导出"+sheetTitle);
ResultSetMetaData data = rs.getMetaData();
int sheetNum = 0;// 记录额外创建的sheet数量
Sheet sheet = wb.createSheet(sheetTitle);
int rownum = 0;
Row row = sheet.createRow(rownum);
// 设置并获取到需要的样式
XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(wb);
Cell cell;
// 创建标题,此时row=0,即第一行
String[] titles = new String[data.getColumnCount()];
for (int j = 0; j < data.getColumnCount(); j++) {
//获得指定列的列名
String columnName = data.getColumnName(j+1);
titles[j] =columnName;
cell = row.createCell(j);
cell.setCellValue(columnName);
cell.setCellStyle(xssfCellStyleHeader);
}
int rowIndex =0;
while (rs.next()){
if ((rowIndex + 1) % 1048576 == 0) {
sheetNum++;
sheet = wb.createSheet(sheetTitle +"_"+ sheetNum);
row = sheet.createRow(0);
// 声明列对象，参数为列索引，可以是0～255之间的任何一个
// 创建标题,此时row=0,即第一行
for (int j = 0; j < titles.length; j++) {
cell = row.createCell(j);
cell.setCellValue(titles[j]);
cell.setCellStyle(xssfCellStyleHeader);
}
}
row = sheet.createRow((rowIndex + 1) - (sheetNum * 1048576)+sheetNum);
for (int cellIndex = 1; cellIndex <= data.getColumnCount(); cellIndex++) {
String columnName = data.getColumnName(cellIndex);
String columnValue = rs.getString(cellIndex);
String columnClassName = data.getColumnClassName(cellIndex);
Object o = rs.getObject(columnName);
if("java.sql.Timestamp".equals(columnClassName)){
if(StringUtils.isNotBlank(columnValue)){
row.createCell((short) cellIndex-1).setCellValue(columnValue);
}
}
if(o instanceof Byte){
row.createCell((short) cellIndex-1).setCellValue((Byte) o);
}
if(o instanceof Short){
row.createCell((short) cellIndex-1).setCellValue((Short) o);
}
if(o instanceof Integer){
row.createCell((short) cellIndex-1).setCellValue((Integer) o);
}
if(o instanceof Long){
row.createCell((short) cellIndex-1).setCellValue((Long) o);
}
if(o instanceof Character){
row.createCell((short) cellIndex-1).setCellValue((Character) o);
}
if(o instanceof Float){
row.createCell((short) cellIndex-1).setCellValue((Float) o);
}
if(o instanceof Double){
row.createCell((short) cellIndex-1).setCellValue((Double) o);
}
if(o instanceof Boolean){
row.createCell((short) cellIndex-1).setCellValue((Boolean) o);
}
if(o instanceof BigDecimal){
row.createCell((short) cellIndex-1).setCellValue(((BigDecimal) o).doubleValue());
}
if(o instanceof String){
row.createCell((short) cellIndex-1).setCellValue((String) o);
}
if(o instanceof Date){
String dateStr = simpleDateFormat.format((Date)o);
row.createCell((short) cellIndex-1).setCellValue(dateStr);
}
}
rowIndex++;
ExportRow++;
}
}
if(ExportRow ==0){
Date dateAfter = new Date();
logger.info("共"+ExportRow+"条数据,导出列表共执行"+(dateAfter.getTime()-dateBefore.getTime())+"ms");
if(ExportRow==0){
throw new Exception("无数据");
}
}
return wb;
}
/**
* 获取并设置header样式
*/
private static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
Font font = sxssfWorkbook.createFont();
// 字体大小
font.setFontHeightInPoints((short) 11);
// 字体粗细
font.setBold(true);
font.setFontName("宋体");
// 将字体应用到样式上面
xssfCellStyle.setFont(font);
// 是否自动换行
xssfCellStyle.setWrapText(false);
// 水平居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return xssfCellStyle;
}
}
评论区