import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class SplitWorksheet {

    public static void main(String[] args) {

        //Create a Workbook object to load the original Excel document
        Workbook bookOriginal = new Workbook();
        bookOriginal.loadFromFile("C:\\Users\\Administrator\\Desktop\\Emplyees.xlsx");

        //Get the first worksheet
        Worksheet sheet = bookOriginal.getWorksheets().get(0);

        //Get the header row
        CellRange headerRow = sheet.getCellRange(1, 1, 1, 5);

        //Get two cell ranges
        CellRange range1 = sheet.getCellRange(2, 1, 6, 5);
        CellRange range2 = sheet.getCellRange(7, 1, 11, 5);

        //Create a new workbook
        Workbook newBook1 = new Workbook();

        //Copy the header row and range 1 to the new workbook
        sheet.copy(headerRow, newBook1.getWorksheets().get(0), 1, 1, true, false);
        sheet.copy(range1, newBook1.getWorksheets().get(0), 2, 1, true, false);

        //Copy the column width from the original workbook to the new workbook
        for (int i = 0; i < sheet.getLastColumn(); i++) {

            newBook1.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
        }

        //Save the new workbook to an Excel file
        newBook1.saveToFile("Sales.xlsx", ExcelVersion.Version2016);

        //Copy the header row and range 2 to another workbook, and save it to another Excel file
        Workbook newBook2 = new Workbook();
        sheet.copy(headerRow, newBook2.getWorksheets().get(0), 1, 1, true, false);
        sheet.copy(range2, newBook2.getWorksheets().get(0), 2, 1, true, false);
        for (int i = 0; i < sheet.getLastColumn(); i++) {

            newBook2.getWorksheets().get(0).setColumnWidth(i + 1, sheet.getColumnWidth(i + 1));
        }
        newBook2.saveToFile("Technicians.xlsx", ExcelVersion.Version2016);
    }
}

import com.spire.xls.*;
import com.spire.xls.core.IChartTrendLine;

import java.awt.*;

public class AddTrendline {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("test.xlsx");

        //Get the first chart in the first worksheet
        Chart chart = workbook.getWorksheets().get(0).getCharts().get(0);

        //Add a Trendline to the first series of the chart
        IChartTrendLine trendLine = chart.getSeries().get(0).getTrendLines().add(TrendLineType.Linear);

        //Set Trendline name
        trendLine.setName("Linear(Series1)");
        //Set line type and color
        trendLine.getBorder().setPattern(ChartLinePatternType.DashDot);
        trendLine.getBorder().setColor(Color.blue);
        //Set forward and backward value
        trendLine.setForward(0.5);
        trendLine.setBackward(0.5);
        //Set intercept value
        trendLine.setIntercept(5);

        //Display equation on chart
        trendLine.setDisplayEquation(true);
        //Display R-Squared value on chart
        trendLine.setDisplayRSquared(true);

        //Save the result file
        workbook.saveToFile("AddTrendline.xlsx", ExcelVersion.Version2013);
    }
}

import com.spire.xls.Chart;
import com.spire.xls.Workbook;
import com.spire.xls.core.IChartTrendLine;

public class ReadEquationOfTrendline {
    public static void main(String[] args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("AddTrendline.xlsx");

        //Get the first chart in the first worksheet
        Chart chart = workbook.getWorksheets().get(0).getCharts().get(0);

        //Read the equation of the first series of the chart
        IChartTrendLine trendLine = chart.getSeries().get(0).getTrendLines().get(0);
        String equation = trendLine.getFormula();
        System.out.println("The equation is: " + equation);
    }
}

import java.awt.*;
import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.shapes.*;

public class SetFontAndBackground {
    public static void main(String[] args) {
        //Load an Excel sample
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Get the textbox which will be edited
        XlsTextBoxShape shape = (XlsTextBoxShape) sheet.getTextBoxes().get(0);

        //Set the font and background color for the textbox
        //Set font
        ExcelFont font = workbook.createFont();
        font.setFontName("Calibri");
        font.setSize(14);
        font.isBold(false);
        font.setColor(Color.MAGENTA);
        (new RichText(shape.getRichText())).setFont(0, shape.getText().length() - 1, font);

        //Set background color
        shape.getFill().setFillType(ShapeFillType.SolidColor);
        shape.getFill().setForeKnownColor(ExcelColors.Yellow);

        //Save to file
        workbook.saveToFile("output/setFontAndBackgroundForTextBox.xlsx", ExcelVersion.Version2013);
    }
}

import com.spire.xls.*;

public class setMargins {
    public static void main(String[] args) {

        String outputFile="output/setMarginsOfExcel.xlsx";

        //Load the sample document from file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx";);

        //Get the first worksheet.
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Get the PageSetup object of the first worksheet.
        PageSetup pageSetup = sheet.getPageSetup();

        //Set the page margins of bottom, left, right and top.
        pageSetup.setBottomMargin(2);
        pageSetup.setLeftMargin(1);
        pageSetup.setRightMargin(1);
        pageSetup.setTopMargin(3);
        
        //Set the margins of header and footer.
        pageSetup.setHeaderMarginInch(2);
        pageSetup.setFooterMarginInch(2);

        //Save to file.
        workbook.saveToFile(outputFile, ExcelVersion.Version2013);

    }
}