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<File> Dirlist = new LinkedList<File>();
            LinkedList<String> Filelist = new LinkedList<String>();
            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);

       /* */



    }


}