aspose.cells java_Java操作Excel aspose.cells

主要是java对ecxel 的批量修改

使用的jar包是aspose-cells-8.5.2.jar

lincense和对应的jar在

https://download.csdn.net/download/ayout_five/13196570

```

import com.aspose.cells.*;

import com.aspose.words.LineStyle;

import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;

import java.io.*;

import java.util.LinkedList;

import static com.aspose.pdf.TextAlignment.Center;

public class ExcelUtil {

public static void main(String[] args) throws Exception {

//加载授权文件

ClassLoader loader = Thread.currentThread().getContextClassLoader();

InputStream license = new FileInputStream(loader.getResource("license.xml").getPath());// 凭证文件

License aposeLic = new License();

aposeLic.setLicense(license);

//循环文件夹

String path = "E:\\服务器版原始记录";     //要遍历的路径

readfile(path);

//FormatNewExcel(".\\data\\频率表85C1附件3：校准证书-标准版0221.xlsx");

}

//循环文件夹

public static void readfile(String filepath) throws Exception {

LinkedList Dirlist = new LinkedList();

LinkedList Filelist = new LinkedList();

File dir = new File(filepath);

File[] files = dir.listFiles();

for(File file : files){

if(file.isDirectory()){

Dirlist.add(file);

}else{

//处理文件内容

Filelist.add(file.getAbsolutePath());

FormatNewExcel(file.getAbsolutePath());

//  System.out.println(file.getAbsolutePath());

}

}

File temp;

while(!Dirlist.isEmpty()){

temp = Dirlist.removeFirst();

if(temp.isDirectory()){

files = temp.listFiles();

if(files == null) continue;

for(File file : files){

if(file.isDirectory()){

Dirlist.add(file);

}else{

//处理文件内容

Filelist.add(file.getAbsolutePath());

//System.out.println(file.getAbsolutePath());

FormatNewExcel(file.getAbsolutePath());

}

}

}else{

//处理文件内容,这种情况好像不会发生

System.out.println("-------------");

}

}

}

/*

按照要求生成新的Excel

*/

public static void  FormatNewExcel(String path) throws Exception {

//System.out.println(path);

Workbook work = new Workbook(path);

//创建文件

String replace = path.replace("E:", "F:");

//System.out.println("replace: "+replace);

File testFile = new File(replace);

if (!testFile.exists()) {

testFile.mkdirs();// 能创建多级目录

}

/*

System.out.println(work.getFileName());*/

WorksheetCollection sheet = work.getWorksheets();

//删除中间一张表

Worksheet worksheet1 = sheet.get(1);

worksheet1.setVisible(false);

//设置第一张表

Worksheet worksheet0 = sheet.get(0);

Cells cells = worksheet0.getCells();

Style style = cells.getRow(0).getStyle();

cells.get(0,0).putValue("");

Row row = cells.getRow(11);

row.setHeight(41);

for (int i = 13; i < 19; i++) {

Row rwo = cells.getRow(i);

rwo.setHeight(61.2);

}

for (int i = 18; i < 24; i++) {

Row rwo = cells.getRow(i);

rwo.setHeight(0);

}

//设置最后一张表的名称

sheet.get(2).setName("证书内页");

Worksheet worksheet2 = sheet.get(2);

Cells cells1 = worksheet2.getCells();

//System.out.println(cells1.getMaxRow()+""+cells1.getMaxColumn());

//统一修改背景色为透明色

for (int i = 0; i <= cells1.getMaxRow(); i++) {

for (int j = 0; j <= cells.getMaxColumn(); j++) {

Cell cell = cells1.get(i, j);

Style cellStyle = cells1.getCellStyle(i, j);

/*if (cellStyle.getForegroundColor() != Color.getEmpty()){

cellStyle.setForegroundColor(Color.getEmpty());

cellStyle.setPattern(BackgroundType.SOLID);

cells.get(i, j).setStyle(cellStyle);

}else {

System.out.println("11");

}*/

//System.out.println(cellStyle.getForegroundColor());

cellStyle.setForegroundColor(Color.getEmpty());

cellStyle.setPattern(BackgroundType.SOLID);

cell.setStyle(cellStyle);

// System.out.println(cell.getStyle().getForegroundColor());

}

}

// work.save(".\\data\\频率表85C1附件3：校准证书-标准版0221finish.xlsx");

work.save(replace);

/* */

}

}

```