1.为什么使用SXSSF,而不使用XSSF或HSSF?
2.使用多线程操作poi时,需注意的事项。
- sheet.creaRow() 方法是非线程安全的 ,需要进行控制
- poi 若需要对每个单元格分别设置样式,则不要每次的创建(cell.createCellStyle()),否则很可能会出现样式混乱情况,正确做法应该复用相同单元格的样式,即使用 cellStyle.cloneStyleFrom(srcCellStyle)方法
3.代码
1.控制层(controller)
@GetMapping(value = "/getExport.do")
public void getExportRcxc(HttpServletRequest request, HttpServletResponse response){
String date = request.getParameter("startTime");
String isUpdate = request.getParameter("isUpdate");
String startTime = DateUtils.getMonthBegin(date);
String endTime = DateUtils.getMonthEnd(date);
String returnName ="XXXX统计表";
try {
ByteArrayOutputStream excelData = getExcelData(startTime,date,endTime, isUpdate);
if(Objects.nonNull(excelData)){
DownloadUtils.downloadExcel(excelData,response,returnName);
}else {
response.setStatus(201);
}
} catch (IOException e) {
e.printStackTrace();
}
}
private ByteArrayOutputStream getExcelData(String startTime,String date,String endTime, String isUpdate) throws IOException {
Instant startT1 = Instant.now();
List<Map> dataList = getData(startTime,date, endTime, isUpdate);
Instant endT1 = Instant.now();
System.out.println("导出功能---获取数据库所需时间:"+Duration.between(startT1,endT1).toMillis()+"毫秒");
Instant startT2 = Instant.now();
SXSSFWorkbook workbook = MergeExcelUtils.creatMyXSSFWorkbook();
if(null != dataList && dataList.size()>0){
SXSSFSheet mySheet = MergeExcelUtils.createMySheet(workbook, "XXXX统计表", true, 4, 4);
mySheet.setRandomAccessWindowSize(-1);
XSSFCellStyle titleStyle = MergeExcelUtils.setCellStyle(workbook, "仿宋", 16, true, true, false, true, IndexedColors.WHITE.getIndex(),null);
XSSFCellStyle dynamic1HeadStyle = MergeExcelUtils.setCellStyle(workbook, "仿宋", 11, true, true, true, true, IndexedColors.SKY_BLUE.getIndex(),null);
XSSFCellStyle dynamic2HeadStyle = MergeExcelUtils.setCellStyle(workbook, "仿宋", 11, true, true, true, true, IndexedColors.PINK.getIndex(),null);
XSSFCellStyle fixedHeadStyle = MergeExcelUtils.setCellStyle(workbook, "仿宋", 11, true, true, true, true, IndexedColors.WHITE.getIndex(),null);
String titleRowCellValue ="我是大标题行";
SXSSFRow row0 = (SXSSFRow) mySheet.createRow(0);
MergeExcelUtils.setMyRow(mySheet, titleStyle, row0, 0, 47, titleRowCellValue, new CellRangeAddress(0, 0, 0, 20));
String keyArr[] = new String[]{
"a","b","c","d","e","f","g","h","i","j",
"k","l","m","n","o" ,"p" ,"q" ,"r"};
Instant startT2_1 = Instant.now();
List<String> dateList = getDateList(dataList);
Instant endT2_1 = Instant.now();
System.out.println("导出功能---获取日期List所需时间:"+Duration.between(startT2_1,endT2_1).toMillis()+"毫秒");
Instant startT2_2 = Instant.now();
int copySize =(dateList.size())-1;
int headRowNums = 3;
String[] head0 = new String[]{"序号", "行1号", "行2号","行3号"};
String[] head01 = new String[]{dateList.get(copySize), dateList.get(copySize), dateList.get(copySize),dateList.get(copySize), dateList.get(copySize),"列合并1", "列合并1", "列合并2", "列合并2", "列合并3", "列合并4", "列合并4", "列合并5","列合并5"};
String[] head1 = new String[]{" ", " ", " ", " "};
String[] head11 = new String[]{"行4号", "行5号", "行6号", "行7号", "行8号", " ", " ", " ", " ", " ", " ", " ", " "," "};
String[] head2 = new String[]{" ", " ", " ", " "};
String[] head21 = new String[]{" ", " ", " ", " ", " ", "z1", "z2", "z3", "z4", "z5", "z6", "z7", "z8","z9"};
String[] mergeCoordinate0 = new String[]{"1,3,0,0", "1,3,1,1,", "1,3,2,2", "1,3,3,3"};
String[] mergeCoordinate01 = new String[]{"1,1,4,8","1,2,9,10","1,2,11,12","1,2,13,13","1,2,14,15","1,2,16,17"};
String[] mergeCoordinate11 = new String[]{ "2,3,4,4","2,3,5,5","2,3,6,6","2,3,7,7","2,3,8,8"};
Map<Integer, Map<String, String[]>> headMap = Maps.newHashMap();
Map<String, String[]> head0Map = new HashMap<>();
head0Map.put(MergeExcelUtils.HEADNAME, head0);
head0Map.put(MergeExcelUtils.MERGECOORDINATE, mergeCoordinate0);
Map<String, String[]> head1Map = new HashMap<>();
head1Map.put(MergeExcelUtils.HEADNAME, head1);
Map<String, String[]> head2Map = Maps.newHashMap();
head2Map.put(MergeExcelUtils.HEADNAME, head2);
headMap.put(1, head0Map);
headMap.put(2, head1Map);
headMap.put(3, head2Map);
Map<Integer, Map<String, String[]>> headMap11 = Maps.newHashMap();
Map<String, String[]> head01Map = new HashMap<>();
head01Map.put(MergeExcelUtils.HEADNAME, head01);
head01Map.put(MergeExcelUtils.MERGECOORDINATE, mergeCoordinate01);
Map<String, String[]> head11Map = new HashMap<>();
head11Map.put(MergeExcelUtils.HEADNAME, head11);
head11Map.put(MergeExcelUtils.MERGECOORDINATE, mergeCoordinate11);
Map<String, String[]> head21Map = Maps.newHashMap();
head21Map.put(MergeExcelUtils.HEADNAME, head21);
headMap11.put(1, head01Map);
headMap11.put(2, head11Map);
headMap11.put(3, head21Map);
MergeExcelUtils.setFixedHead(mySheet,fixedHeadStyle,headMap);
MergeExcelUtils.setDynamicHead(mySheet,dynamic1HeadStyle,headMap11,4);
for (int i = 1; i <= copySize; i++) {
int pPosition =18+((i-1)*14);
int dateIndex = copySize-i;
String headDate = dateList.get(dateIndex);
if(i%2==0){
MergeExcelUtils.copyCols(mySheet,5,18,pPosition,headRowNums,dynamic1HeadStyle,headDate,4,8);
}else{
MergeExcelUtils.copyCols(mySheet,5,18,pPosition,headRowNums,dynamic2HeadStyle,headDate,4,8);
}
}
Instant endT2_2 = Instant.now();
System.out.println("导出功能---生成excel表头所需时间:"+Duration.between(startT2_2,endT2_2).toMillis()+"毫秒");
Instant startT2_3 = Instant.now();
List<List<Map>> list = checkRule(dataList);
Instant endT2_3 = Instant.now();
System.out.println("导出功能---校验生成excel数据所需时间:"+Duration.between(startT2_3,endT2_3).toMillis()+"毫秒");
Instant startT2_4 = Instant.now();
setExcelDataByManyThread(workbook,mySheet,list,keyArr);
Instant endT2_4 = Instant.now();
System.out.println("导出功能---生成excel数据所需时间:"+Duration.between(startT2_4,endT2_4).toMillis()+"毫秒");
}
Instant endT2 = Instant.now();
System.out.println("导出功能---生成excel总所需时间:"+Duration.between(startT2,endT2).toMillis()+"毫秒");
ByteArrayOutputStream result =new ByteArrayOutputStream();
workbook.write(result);
workbook.dispose();
return result;
}
private List<Map> getData(String startTime,String date,String endTime, String isUpdate){
String now = DateUtils.parseDateToString(LocalDate.now());
List<Map> dataResult = new ArrayList<>();
if(DateUtils.beforeDate(LocalDate.now(), DateUtils.convertStr2LocalDate(endTime))){
int num = insert(now,isUpdate);
}
dataResult = rcxcService.getExportRcxcDataInfo(startTime, endTime);
return dataResult;
}
private void setExcelDataByManyThread(SXSSFWorkbook workbook,SXSSFSheet mySheet,List<List<Map>> dataList,String[] keyArr){
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
executor.setCorePoolSize(8);
executor.setMaxPoolSize(8);
executor.setQueueCapacity(350);
executor.setKeepAliveSeconds(60);
executor.setWaitForTasksToCompleteOnShutdown(true);
executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
executor.initialize();
int t = Runtime.getRuntime().availableProcessors();
System.out.println("当前可用的线程数:"+t);
BlockingQueue<List<List<Map>>> queue = new ArrayBlockingQueue(dataList.size());
for (int i = 0; i < dataList.size(); i++){
List<List<Map>> listMap = new ArrayList<>();
List<Map> mapList = dataList.get(i);
listMap.add(mapList);
queue.add(listMap);
}
List<XSSFCellStyle> cellStyles = new ArrayList<>();
XSSFCellStyle normalCellStyle = MergeExcelUtils.setCellStyle(workbook, "仿宋", 11, true, true, true, true, IndexedColors.WHITE.getIndex(),null);
XSSFCellStyle greenStyle = MergeExcelUtils.setCellStyle(workbook,"仿宋", 11, true, true, false, true, IndexedColors.GREEN.getIndex(),null);
XSSFCellStyle yellowStyle = MergeExcelUtils.setCellStyle(workbook,"仿宋", 11, true, true, false, true, IndexedColors.YELLOW.getIndex(),null);
XSSFCellStyle redStyle = MergeExcelUtils.setCellStyle(workbook,"仿宋", 11, true, true, false, true, IndexedColors.RED.getIndex(),null);
cellStyles.add(greenStyle);
cellStyles.add(yellowStyle);
cellStyles.add(redStyle);
cellStyles.add(normalCellStyle);
CountDownLatch latch = new CountDownLatch(queue.size());
int rowNum = 0;
while (queue.size()>0){
try {
rowNum++;
executor.execute(new SetExcelValueTask(latch,workbook,mySheet,cellStyles,queue.take(),rowNum, keyArr, 18,14));
} catch (InterruptedException e) {
e.printStackTrace();
}
}
try {
latch.await();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
private List<String> getDateList(List<Map> dataList){
LinkedHashMap<Object, List<Map>> gxsjCollect = dataList.stream().collect(Collectors.groupingBy(x -> x.get("XCSJ"), LinkedHashMap::new, Collectors.toList()));
Set<Map.Entry<Object, List<Map>>> set = gxsjCollect.entrySet();
Iterator<Map.Entry<Object, List<Map>>> iterator = set.iterator();
List<String> dateList = new ArrayList<>();
while(iterator.hasNext()) {
Map.Entry entry = iterator.next();
String key = entry.getKey().toString();
dateList.add(key);
}
return dateList;
}
private void setValueAndColorToCell(SXSSFWorkbook workbook,String[] keyArr,int keyIndex,Map hashMap,SXSSFSheet sheet,int row ,int startIndex,int cols){
XSSFCellStyle whiteStyle = MergeExcelUtils.setCellStyle(workbook, "仿宋", 11, true, true, false, true, IndexedColors.WHITE.getIndex(),null);
XSSFCellStyle greenStyle = MergeExcelUtils.setCellStyle(workbook, "仿宋", 11, true, true, false, true, IndexedColors.GREEN.getIndex(),null);
XSSFCellStyle yellowStyle = MergeExcelUtils.setCellStyle(workbook, "仿宋", 11, true, true, false, true, IndexedColors.YELLOW.getIndex(),null);
XSSFCellStyle redStyle = MergeExcelUtils.setCellStyle(workbook, "仿宋", 11, true, true, false, true, IndexedColors.RED.getIndex(),null);
for(int i = startIndex; i < cols; i++){
String key = keyArr[keyIndex];
String value = hashMap.get(key).toString();
if(StringUtils.contains(value,"_green")){
MergeExcelUtils.setMyCell(sheet, row, i, value.substring(0,value.lastIndexOf("_")),greenStyle);
}else if(StringUtils.contains(value,"_yellow")){
MergeExcelUtils.setMyCell(sheet, row, i, value.substring(0,value.lastIndexOf("_")),yellowStyle);
}else if(StringUtils.contains(value,"_red")){
MergeExcelUtils.setMyCell(sheet, row, i, value.substring(0,value.lastIndexOf("_")),redStyle);
}else{
MergeExcelUtils.setMyCell(sheet, row, i, value,whiteStyle);
}
keyIndex++;
}
}
private List<List<Map>> checkRule(List<Map> dataList){
LinkedHashMap<Object, List<Map>> xzqhdmCollect = dataList.stream().collect(Collectors.groupingBy(x -> x.get("XZQHDM"), LinkedHashMap::new, Collectors.toList()));
ArrayList list = new ArrayList();
String[] qhCompareKey =new String[]{"i","j","k","l","m","n","o" ,"p" ,"q" };
String[] lxCompareKey =new String[]{"d","e", "f", "g","h",};
List<String[]> compareKey = new ArrayList<>();
compareKey.add(qhCompareKey);
compareKey.add(lxCompareKey);
int xzqhdmCollectSize = xzqhdmCollect.size();
for (int i = 0; i < xzqhdmCollectSize; i++){
ArrayList data = (ArrayList) (xzqhdmCollect.values().toArray()[i]);
List checkoutData = checkoutData(data, compareKey);
list.add(checkoutData);
}
return list;
}
private List<Map> checkoutData(ArrayList<HashMap> dataList,List<String[]> compareKey){
List<Map> listMap = new ArrayList<>();
int listSize = dataList.size();
for(int i = listSize-1; i >= 0; i--){
HashMap hashMap = dataList.get(i);
if((i-1) >= 0){
int qhLen = compareKey.get(0).length;
for(int n = 0; n< qhLen;n++){
String qhKey = compareKey.get(0)[n];
String currentStr = hashMap.get(qhKey).toString();
String nextStr = dataList.get(i-1).get(qhKey).toString();
int current = Integer.parseInt(currentStr);
int next = Integer.parseInt(nextStr);
int diff = next - current;
if(diff<0){
currentStr +="_green";
hashMap.put(qhKey,currentStr);
}else if(diff>0){
if(diff>=3){
currentStr +="_red";
}else {
currentStr +="_yellow";
}
hashMap.put(qhKey,currentStr);
}
}
}
if((i-2)>=0){
if((i-2>=0) && (i-6 < 0)){
int lxLen = compareKey.get(1).length;
for(int n = 0; n< lxLen;n++){
String lxKey = compareKey.get(1)[n];
String currentStr = hashMap.get(lxKey).toString();
String next2Str = dataList.get(i-1).get(lxKey).toString();
String next3Str = dataList.get(i-2).get(lxKey).toString();
String flag1 ="0";
String flag2 ="0.0";
if((StringUtils.equal(flag1,currentStr)
&& StringUtils.equal(flag1,next2Str)
&& StringUtils.equal(flag1,next3Str)) ||
(StringUtils.equal(flag2,currentStr)
&& StringUtils.equal(flag2,next2Str)
&& StringUtils.equal(flag2,next3Str))){
currentStr +="_yellow";
hashMap.put(lxKey,currentStr);
}
}
}
if((i-6)>=0){
int lxLen = compareKey.get(1).length;
for(int n = 0; n< lxLen;n++){
String lxKey = compareKey.get(1)[n];
String currentStr = hashMap.get(lxKey).toString();
String next2Str = dataList.get(i-1).get(lxKey).toString();
String next3Str = dataList.get(i-2).get(lxKey).toString();
String next4Str = dataList.get(i-3).get(lxKey).toString();
String next5Str = dataList.get(i-4).get(lxKey).toString();
String next6Str = dataList.get(i-5).get(lxKey).toString();
String next7Str = dataList.get(i-6).get(lxKey).toString();
String flag1 ="0";
String flag2 ="0.0";
if((StringUtils.equal(flag1,currentStr)
&& StringUtils.equal(flag1,next2Str)
&& StringUtils.equal(flag1,next3Str)
&& StringUtils.equal(flag1,next4Str)
&& StringUtils.equal(flag1,next5Str)
&& StringUtils.equal(flag1,next6Str)
&& StringUtils.equal(flag1,next7Str)) ||
(StringUtils.equal(flag2,currentStr)
&& StringUtils.equal(flag2,next2Str)
&& StringUtils.equal(flag2,next3Str)
&& StringUtils.equal(flag2,next4Str)
&& StringUtils.equal(flag2,next5Str)
&& StringUtils.equal(flag2,next6Str)
&& StringUtils.equal(flag2,next7Str)) ){
currentStr +="_red";
hashMap.put(lxKey,currentStr);
}else {
if((i-2)>=0){
if((StringUtils.equal(flag1,currentStr)
&& StringUtils.equal(flag1,next2Str)
&& StringUtils.equal(flag1,next3Str)) ||
(StringUtils.equal(flag2,currentStr)
&& StringUtils.equal(flag2,next2Str)
&& StringUtils.equal(flag2,next3Str))){
currentStr +="_yellow";
hashMap.put(lxKey,currentStr);
}
}
}
}
}
}
listMap.add(hashMap);
}
return listMap;
}
2.线程任务类
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CountDownLatch;
public class SetExcelValueTask implements Runnable{
private CountDownLatch latch;
private SXSSFWorkbook workbook;
private SXSSFSheet sheet;
private List<XSSFCellStyle> cellStyles;
private List<List<Map>> dataList;
private String[] keyArr;
private int initCol;
private int spanCol;
private int rowNum;
public RcxcSetExcelValueTask(CountDownLatch latch, SXSSFWorkbook workbook, SXSSFSheet sheet,List<XSSFCellStyle> cellStyles, List<List<Map>> dataList, int rowNum, String[] keyArr, int initCol, int spanCol) {
this.latch = latch;
this.workbook =workbook;
this.sheet = sheet;
this.cellStyles = cellStyles;
this.dataList = dataList;
this.keyArr = keyArr;
this.initCol = initCol;
this.spanCol = spanCol;
this.rowNum = rowNum;
}
@Override
public void run() {
try {
setExcelData(dataList,rowNum, keyArr, initCol, spanCol);
}catch (Exception e) {
e.printStackTrace();
} finally {
if (null != latch) {
latch.countDown();
}
}
}
private void setExcelData(List<List<Map>> dataList,int rowNum, String[] keyArr, int initCol, int spanCol){
int listSizeSize = dataList.get(0).size();
XSSFCellStyle indexStyle = MergeExcelUtils.setCellStyle(workbook,"仿宋", 16, true, true, false, true, IndexedColors.WHITE.getIndex(),null);
int mm =3+rowNum;
SXSSFRow rowi = MergeExcelUtils.createMyRow(sheet,mm);
MergeExcelUtils.setMyCell(sheet, mm,0, rowNum + "",indexStyle);
int col=initCol;
for( int j = 0 ; j< listSizeSize; j++){
int dyCol = spanCol;
if(j == 0){
setValueAndColorToCell(keyArr,0,dataList.get(0).get(j),mm,1,initCol);
}else {
int startIndex = col+(j-1)*dyCol;
int endCol = col+((j)*dyCol);
setValueAndColorToCell(keyArr,3,dataList.get(0).get(j),mm,startIndex,endCol);
}
}
}
private void setValueAndColorToCell(String[] keyArr,int keyIndex,Map hashMap,int row ,int startIndex,int cols){
for(int i = startIndex; i < cols; i++){
String key = keyArr[keyIndex];
String value = hashMap.get(key).toString();
myManyThreadCell(sheet,cellStyles,row,i,value);
keyIndex++;
}
}
private String checkNullOrZeroReturnLine(String value){
String result;
if(StringUtils.equal("0",value) || StringUtils.equal("0.0",value) || StringUtils.isBlank(value)){
result ="-";
}else {
result =value;
}
return result;
}
private void myManyThreadCell(SXSSFSheet sheet,List<XSSFCellStyle> cellStyles, int rowIndex, int colIndex, String vuale) {
SXSSFRow myRow = MergeExcelUtils.createMyRow(sheet, rowIndex);
SXSSFCell cell = (SXSSFCell) myRow.createCell(colIndex);
myManyThreadCellStyle(vuale,cell,cellStyles);
}
private void myManyThreadCellStyle(String value, SXSSFCell cell,List<XSSFCellStyle> cellStyles){
if (StringUtils.contains(value,"_green")) {
cell.setCellStyle(cellStyles.get(0));
cell.setCellValue(checkNullOrZeroReturnLine(value.substring(0, value.lastIndexOf("_"))));
}else if(StringUtils.contains(value,"_yellow")){
cell.setCellStyle(cellStyles.get(1));
cell.setCellValue(checkNullOrZeroReturnLine(value.substring(0, value.lastIndexOf("_"))));
}else if(StringUtils.contains(value,"_red")){
cell.setCellStyle(cellStyles.get(2));
cell.setCellValue(checkNullOrZeroReturnLine(value.substring(0, value.lastIndexOf("_"))));
}else {
cell.setCellStyle(cellStyles.get(3));
cell.setCellValue(checkNullOrZeroReturnLine(value));
}
}
}
3.使用到的工具类
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
public class DownloadUtils {
public static void downloadExcel(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
response.setContentType("application/octet-stream");
returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));
response.addHeader("Content-Disposition","attachment;filename="+returnName+".xls");
response.setContentLength(byteArrayOutputStream.size());
response.addHeader("Content-Length", "" + byteArrayOutputStream.size());
ServletOutputStream outputstream = response.getOutputStream();
byteArrayOutputStream.writeTo(outputstream);
byteArrayOutputStream.close();
outputstream.flush();
}
}
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
public class MergeExcelUtils {
public static final String HEADNAME = "headName";
public static final String MERGECOORDINATE = "mergeCoordinate";
public static synchronized XSSFCellStyle setCellStyle(SXSSFWorkbook workbook,String fontName,int fontSize,boolean boldWeight,boolean showBorder,boolean lineFeed,boolean showBgColor,short colorValue,Short txtColor){
XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
XSSFFont font = (XSSFFont) workbook.createFont();
font.setFontName(fontName);
font.setFontHeightInPoints((short) fontSize);
if(boldWeight){
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
if(txtColor!=null){
font.setColor(txtColor);
}
style.setFont(font);
if(showBorder){
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
}
if(lineFeed){
style.setWrapText(true);
}
if(showBgColor){
style.setFillForegroundColor(colorValue);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
}
return style;
}
public static SXSSFSheet createMySheet(SXSSFWorkbook workbook, String sheetName, boolean showFreezePane, int col, int row){
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(sheetName);
if(showFreezePane){
sheet.createFreezePane(col, row,col, row);
}
return sheet;
}
public static void setMyRow(SXSSFSheet sheet, XSSFCellStyle cellStyle, SXSSFRow row, int rowCellIndex, int heightInPoints, String rowCellValue, CellRangeAddress cellRangeAddress){
row.setHeightInPoints(heightInPoints);
SXSSFCell cell = (SXSSFCell) row.createCell(rowCellIndex);
cell.setCellStyle(cellStyle);
cell.setCellValue(rowCellValue);
if(Objects.nonNull(cellRangeAddress)){
sheet.addMergedRegion(cellRangeAddress);
}
}
public static void setFixedHead(SXSSFSheet sheet,XSSFCellStyle cellStyle,Map<Integer,Map<String, String[]>> headMap){
Set<Integer> headKeySet = headMap.keySet();
if (headKeySet.size() > 0) {
headKeySet.stream().sorted();
for (Integer rowNum : headKeySet) {
Map<String, String[]> rowInfo = headMap.get(rowNum);
String[] headName = rowInfo.get(HEADNAME);
String[] mergeCoordinate = rowInfo.get(MERGECOORDINATE);
if (null != headName && headName.length > 0) {
Row row1 = sheet.createRow(rowNum);
for (int i = 0; i < headName.length; i++) {
Cell cell = row1.createCell(i);
cell.setCellValue(headName[i]);
cell.setCellStyle(cellStyle);
}
}
addMyMergedRegion(sheet,mergeCoordinate);
}
}
}
public static void setDynamicHead(SXSSFSheet sheet,XSSFCellStyle cellStyle,Map<Integer,Map<String, String[]>> headMap,int startCol){
Set<Integer> headKeySet = headMap.keySet();
if (headKeySet.size() > 0) {
headKeySet.stream().sorted();
for (Integer rowNum : headKeySet) {
Map<String, String[]> rowInfo = headMap.get(rowNum);
String[] headName = rowInfo.get(HEADNAME);
String[] mergeCoordinate = rowInfo.get(MERGECOORDINATE);
if (null != headName && headName.length > 0) {
Row row1 = sheet.getRow(rowNum);
for (int i = 0; i < headName.length; i++) {
Cell cell = row1.createCell(i+startCol);
cell.setCellValue(headName[i]);
cell.setCellStyle(cellStyle);
}
}
addMyMergedRegion(sheet,mergeCoordinate);
}
}
}
public static SXSSFWorkbook creatMyXSSFWorkbook(){
SXSSFWorkbook workbook = new SXSSFWorkbook();
return workbook;
}
public static SXSSFSheet copyCols(SXSSFSheet currentSheet,int startCol, int endCol, int pPosition,int headRowNums,CellStyle cellStyle,String date,int setValueStartCol,int setValueEndCol) {
int pStartCol= startCol - 1;
int pEndCol = endCol - 1;
int targetColFrom;
int targetColTo;
if (pStartCol == -1 || pEndCol == -1) {
return null;
}
int mergedRegions = currentSheet.getNumMergedRegions();
for (int i = 0; i < mergedRegions; i++) {
CellRangeAddress region = currentSheet.getMergedRegion(i);
int firstColumn = region.getFirstColumn();
int lastColumn = region.getLastColumn();
if ((firstColumn >= pStartCol)&& ( lastColumn<= pEndCol)) {
targetColFrom = firstColumn - pStartCol + pPosition;
targetColTo = lastColumn - pStartCol + pPosition;
CellRangeAddress newRegion = region.copy();
newRegion.setFirstRow(region.getFirstRow());
newRegion.setFirstColumn(targetColFrom);
newRegion.setLastRow(region.getLastRow());
newRegion.setLastColumn(targetColTo);
currentSheet.addMergedRegion(newRegion);
}
}
for (int i = 1; i <= headRowNums; i++) {
int m = 0;
SXSSFRow sourceRow = (SXSSFRow) currentSheet.getRow(i);
if (sourceRow != null) {
SXSSFRow newRow = (SXSSFRow) currentSheet.getRow(i);
for (int j = pStartCol; j <= pEndCol; j++) {
SXSSFCell templateCell = (SXSSFCell) sourceRow.getCell(j);
int newCellIndex = pPosition + m;
int cellIndex = m + j;
currentSheet.setColumnWidth(newCellIndex, currentSheet.getColumnWidth(cellIndex));
if (templateCell != null) {
SXSSFCell newCell = (SXSSFCell) newRow.createCell(newCellIndex);
if( i==1 && j>=setValueStartCol &&j <= setValueEndCol){
newCell.setCellValue(date);
}
copyCell(templateCell, newCell,cellStyle);
m++;
}
}
}
}
return currentSheet;
}
public static void copyCell(SXSSFCell srcCell, SXSSFCell distCell,CellStyle cellStyle) {
distCell.setCellStyle(srcCell.getCellStyle());
distCell.setCellStyle(cellStyle);
if (srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
int srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (srcCellType == XSSFCell.CELL_TYPE_NUMERIC) {
distCell.setCellValue(srcCell.getNumericCellValue());
} else if (srcCellType == XSSFCell.CELL_TYPE_STRING) {
if(StringUtils.isBlank(distCell.getStringCellValue())){
distCell.setCellValue(srcCell.getRichStringCellValue());
}
} else if (srcCellType == XSSFCell.CELL_TYPE_BLANK) {
} else if (srcCellType == XSSFCell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == XSSFCell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == XSSFCell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else {
}
}
public static void setMyCell(SXSSFSheet sheet, int rowIndex, int colIndex, String vuale, XSSFCellStyle cellStyle) {
SXSSFRow myRow = createMyRow(sheet, rowIndex);
SXSSFCell cell = (SXSSFCell) myRow.createCell(colIndex);
cell.setCellValue(vuale);
cell.setCellStyle(cellStyle);
}
public static synchronized SXSSFRow createMyRow(SXSSFSheet sheet, int rownum) {
SXSSFRow row = (SXSSFRow) sheet.getRow(rownum);
if(null != row){
return row;
}
return (SXSSFRow) sheet.createRow(rownum);
}
private static void addMyMergedRegion (SXSSFSheet sheet,String [] mergeCoordinate){
if (null != mergeCoordinate && mergeCoordinate.length > 0) {
for (String mc : mergeCoordinate) {
String[] temp = mc.split(",");
Integer startrow = Integer.parseInt(temp[0]);
Integer overrow = Integer.parseInt(temp[1]);
Integer startcol = Integer.parseInt(temp[2]);
Integer overcol = Integer.parseInt(temp[3]);
CellRangeAddress cra = new CellRangeAddress(startrow, overrow, startcol, overcol);
sheet.addMergedRegion(cra);
}
}
}
}
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
public class DateUtils {
private static final DateTimeFormatter yyyyMMdd = DateTimeFormatter.ofPattern("yyyy-MM-dd");
public static String getMonthBegin(String dateStr) {
return LocalDate.parse(dateStr, yyyyMMdd).with(TemporalAdjusters.firstDayOfMonth()).toString();
}
public static String getMonthEnd(String dateStr) {
return LocalDate.parse(dateStr, yyyyMMdd).with(TemporalAdjusters.lastDayOfMonth()).toString();
}
public static String parseDateToString(LocalDate localDate,String pattern) {
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern);
return localDate.format(dateTimeFormatter);
}
public static String parseDateToString(LocalDate localDate) {
return localDate.format(yyyyMMdd);
}
public static String changeLocalDateByDays(LocalDate localDate, int days){
LocalDate date = localDate.minusDays(days);
return parseDateToString(date);
}
public static LocalDate convertStr2LocalDate(String str){
LocalDate localDate = LocalDate.parse(str, yyyyMMdd);
return localDate;
}
public static boolean beforeDate(LocalDate date1, LocalDate date2){
boolean flag = false;
if(date1.isBefore(date2) || date1.equals(date2) ){
flag = true;
}
return flag;
}
}
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.TimeUnit;
@Component
public class RedisUtils {
@Autowired
private RedisTemplate<String, Object> redisTemplate;
public boolean expire(String key, long time) {
try {
if (time > 0) {
redisTemplate.expire(key, time, TimeUnit.SECONDS);
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public long getExpire(String key) {
return redisTemplate.getExpire(key, TimeUnit.SECONDS);
}
public boolean hasKey(String key) {
try {
return redisTemplate.hasKey(key);
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
@SuppressWarnings("unchecked")
public void del(String... key) {
if (key != null && key.length > 0) {
if (key.length == 1) {
redisTemplate.delete(key[0]);
} else {
redisTemplate.delete(CollectionUtils.arrayToList(key));
}
}
}
public Object get(String key) {
return key == null ? null : redisTemplate.opsForValue().get(key);
}
public boolean set(String key, Object value) {
try {
redisTemplate.opsForValue().set(key, value);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public boolean set(String key, Object value, long time) {
try {
if (time > 0) {
redisTemplate.opsForValue().set(key, value, time, TimeUnit.SECONDS);
} else {
set(key, value);
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public long incr(String key, long delta) {
if (delta < 0) {
throw new RuntimeException("递增因子必须大于0");
}
return redisTemplate.opsForValue().increment(key, delta);
}
public long decr(String key, long delta) {
if (delta < 0) {
throw new RuntimeException("递减因子必须大于0");
}
return redisTemplate.opsForValue().increment(key, -delta);
}
public Object hget(String key, String item) {
return redisTemplate.opsForHash().get(key, item);
}
public Map<Object, Object> hmget(String key) {
return redisTemplate.opsForHash().entries(key);
}
public boolean hmset(String key, Map<String, Object> map) {
try {
redisTemplate.opsForHash().putAll(key, map);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public boolean hmset(String key, Map<String, Object> map, long time) {
try {
redisTemplate.opsForHash().putAll(key, map);
if (time > 0) {
expire(key, time);
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public boolean hset(String key, String item, Object value) {
try {
redisTemplate.opsForHash().put(key, item, value);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public boolean hset(String key, String item, Object value, long time) {
try {
redisTemplate.opsForHash().put(key, item, value);
if (time > 0) {
expire(key, time);
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public void hdel(String key, Object... item) {
redisTemplate.opsForHash().delete(key, item);
}
public boolean hHasKey(String key, String item) {
return redisTemplate.opsForHash().hasKey(key, item);
}
public double hincr(String key, String item, double by) {
return redisTemplate.opsForHash().increment(key, item, by);
}
public double hdecr(String key, String item, double by) {
return redisTemplate.opsForHash().increment(key, item, -by);
}
public Set<Object> sGet(String key) {
try {
return redisTemplate.opsForSet().members(key);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public boolean sHasKey(String key, Object value) {
try {
return redisTemplate.opsForSet().isMember(key, value);
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public long sSet(String key, Object... values) {
try {
return redisTemplate.opsForSet().add(key, values);
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
public long sSetAndTime(String key, long time, Object... values) {
try {
Long count = redisTemplate.opsForSet().add(key, values);
if (time > 0)
expire(key, time);
return count;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
public long sGetSetSize(String key) {
try {
return redisTemplate.opsForSet().size(key);
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
public long setRemove(String key, Object... values) {
try {
Long count = redisTemplate.opsForSet().remove(key, values);
return count;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
public List<Object> lGet(String key, long start, long end) {
try {
return redisTemplate.opsForList().range(key, start, end);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public long lGetListSize(String key) {
try {
return redisTemplate.opsForList().size(key);
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
public Object lGetIndex(String key, long index) {
try {
return redisTemplate.opsForList().index(key, index);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public boolean lSet(String key, Object value) {
try {
redisTemplate.opsForList().rightPush(key, value);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public boolean lSet(String key, Object value, long time) {
try {
redisTemplate.opsForList().rightPush(key, value);
if (time > 0)
expire(key, time);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public boolean lSet(String key, List<Object> value) {
try {
redisTemplate.opsForList().rightPushAll(key, value);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public boolean lSet(String key, List<Object> value, long time) {
try {
redisTemplate.opsForList().rightPushAll(key, value);
if (time > 0)
expire(key, time);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public boolean lUpdateIndex(String key, long index, Object value) {
try {
redisTemplate.opsForList().set(key, index, value);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public long lRemove(String key, long count, Object value) {
try {
Long remove = redisTemplate.opsForList().remove(key, count, value);
return remove;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
}
4.最终导出效果