/**
* 给不同记录标红 只能是xls格式 xlsx格式不行
*/
@Component
public class AutomaticTestContrastExcelBL {
private final Log logger = LogFactory.getLog(this.getClass());
public void dealDate(String excelFileName) {
File filedir = new File(excelFileName);
if (filedir.isDirectory()) {
long start = System.currentTimeMillis();
String[] files = filedir.list();
BlockingQueue blockingQueue=new ArrayBlockingQueue<>(16);
ThreadPoolExecutor threadPoolExecutor=new ThreadPoolExecutor((files.length + 1), (files.length + 3), 2, TimeUnit.MINUTES, blockingQueue);
//ExecutorService pool = Executors.newCachedThreadPool(); //创建线程池
for (int i = 0; i < files.length; i++) {
//logger.info("第: " + i);
Runnable runnable= new DealExcel(files, i,excelFileName);
threadPoolExecutor.execute(runnable);
//DealExcel dealExcel = new DealExcel(files, i,excelFileName);
//pool.submit(dealExcel);
}
threadPoolExecutor.shutdown();//不会触发中断
//threadPoolExecutor.
//pool.shutdown(); //结束线程池
long end = System.currentTimeMillis();
logger.info("-----------------------------------" + (end - start) );
}
}
}
技术交流群:816227112
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
package com.sinosoft.auto.thread;
import com.sinosoft.utility.ExceptionUtils;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.write.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.io.File;
import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Map;
public class DealExcel implements Runnable{
private final Log logger = LogFactory.getLog(this.getClass());
private String[] files;
private int index;
private String fileName;
public DealExcel(String[] files, int index,String fileName) {
this.files = files;
this.index = index;
this.fileName = fileName;
}
@Override
public void run() {
logger.info(fileName +"/" + files[index]);
contrastExcel(new File((fileName +"/" + files[index])));
}
public void contrastExcel(File file) {
try {
logger.info("开始标记:" + file.getAbsoluteFile() + " ::: " + file.getName());
FileInputStream inputStream = new FileInputStream(file.getAbsoluteFile());
Workbook workbook = Workbook.getWorkbook(inputStream);
WritableWorkbook wbe = Workbook.createWorkbook(file.getAbsoluteFile(), workbook);
int numberOfSheets = wbe.getNumberOfSheets();
WritableCellFormat wcf = new WritableCellFormat();
wcf.setBackground(Colour.RED);
for (int index = 0; index < numberOfSheets; index++) {
// 每个页签创建一个Sheet对象
WritableSheet sheet = wbe.getSheet(index); //获取sheet
// sheet.getColumns()返回该页的总列数
int column_total = sheet.getColumns();
int rows = sheet.getRows();
Map<String, Integer> map = new HashMap<>(16);
WritableCell cell = sheet.getWritableCell(0, 0); //获取第一行的所有单元格
CellFormat cf = cell.getCellFormat();
//List<String> arr = new ArrayList<>();
//boolean aa = arr.contains("aa");
for (int j = 0; j < column_total; j++) {
for (int i = 1; i < rows; i++) {
if (i == 1) {
String cellinfo1 = sheet.getCell(j, i).getContents();
map.put(cellinfo1, i);
} else {
String cellinfo2 = sheet.getCell(j, i).getContents();
if (!map.containsKey(cellinfo2)) {
for (int k = 1; k < rows; k++) {
String cellinfo3 = sheet.getCell(j, k).getContents();
Label lbl = new Label(j, k, cellinfo3, wcf);//修改後的值
sheet.addCell(lbl);
}
}
if (i == (rows - 1)) { //循环到每列的最后一个,将清空
map.clear();
}
}
}
}
}
wbe.write(); //将修改保存到workbook
wbe.close();
logger.info("标记:" + file.getAbsoluteFile() + " ::: " + file.getName() + " ::: 完毕 ");
} catch (Exception e) {
e.printStackTrace();
logger.error("Excel文件区分标记异常" + ExceptionUtils.exceptionToString(e));
}
}
}