// Create a new workbook
Workbook workbook = new Workbook();
InputStream fileStream = this.getResourceStream("xlsx\\AgingReport.xlsx");
// Import data of a range from the fileStream
Object[][] data = Workbook.importData(fileStream, "Aging Report", 8, 2, 21, 7);
// Assign the data to current workbook
workbook.getWorksheets().get(0).getRange(0, 0, 21, 7).setValue(data);
 
// Save to an excel file
workbook.save("ImportData.xlsx")

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
  
// Open an excel file.
var fileStream = GetResourceStream("xlsx\\AgingReport.xlsx");
  
// Get the possible import names in the file.
// The names[0] and names[1] are sheet names: "Aging Report", "Invoices".
// The names[2] and names[3] are table names: "'Aging Report'!tblAging", "Invoices!tblInvoices".
var names = GrapeCity.Documents.Excel.Workbook.GetNames(fileStream);
  
// Import the data of a table "'Aging Report'!tblAging" from the fileStream.
var data = GrapeCity.Documents.Excel.Workbook.ImportData(fileStream, names[2]);
  
// Assign the data to current workbook.
workbook.Worksheets[0].Range[0, 0, data.GetLength(0), data.GetLength(1)].Value = data;
          
// Save to an excel file
workbook.Save("importdatafortable.xlsx");

var workbook = new GrapeCity.Documents.Excel.Workbook();
         
//Use XlsxOpenOptions.ImportFlags to control what you want to import from excel, ImportFlags.Data means only the data will be imported
//Change the path to the real file path when open.
XlsxOpenOptions options = new XlsxOpenOptions
{
    ImportFlags = ImportFlags.Data
};
         
workbook.Open("source.xlsx", options);

worksheet.Range["A2:C3"].Interior.Color = Color.Green;
 
//style used range is A2:C3.
var UsedRange_style = worksheet.GetUsedRange(UsedRangeType.Style);
UsedRange_style.Interior.Color = Color.LightBlue;

worksheet.Cells[2, 3].ToString().Substring(7, 4);

//create a new workbook 
var workbook = new GrapeCity.Documents.Excel.Workbook();   
 
var mc = workbook.Worksheets['Sheet1'].Cells[0, 0];
Console.WriteLine(mc.Address); // $A$1
Console.WriteLine(mc.GetAddress(rowAbsolute: false)); // $A1
Console.WriteLine(mc.GetAddress(referenceStyle: ReferenceStyle.R1C1)); // R1C1
Console.WriteLine(mc.GetAddress(referenceStyle: ReferenceStyle.R1C1, 
    rowAbsolute: false,
    columnAbsolute: false, 
    relativeTo: workbook.Worksheets[0].Cells[2, 2])); // R[-2]C[-2]

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
IWorksheet worksheet = workbook.Worksheets[0];
 
//Range A1's displaying color is table style
worksheet.Tables.Add(worksheet.Range["A1:E5"], true);
var color_A1 = worksheet.Range["A1"].DisplayFormat.Interior.Color;
 
//Range A1's displaying color will be cell style, yellow.
worksheet.Range["A1"].Interior.Color = Color.Yellow;
var color_A1_1 = worksheet.Range["A1"].DisplayFormat.Interior.Color;
 
//Range A1's displaying color will be conditional format style, green.
IFormatCondition condition = worksheet.Range["A1"].FormatConditions.Add(FormatConditionType.NoBlanksCondition) as IFormatCondition;
condition.Interior.Color = Color.Green;
var color_A1_2 = worksheet.Range["A1"].DisplayFormat.Interior.Color;
         
// Save to an excel file
workbook.Save("getdisplayformat.xlsx");

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
using (var existingFile = GetResourceStream("xlsx\\FinancialReport.xlsx"))
{
    workbook.Open(existingFile);
}
 
IRange cells = workbook.ActiveSheet.Cells;
 
// Find all formulas
var allFormulas = cells.SpecialCells(SpecialCellType.Formulas);
// Find all constants
var allConstants = cells.SpecialCells(SpecialCellType.Constants);
 
// Change background color of found cells
allFormulas.Interior.Color = Color.LightGray;
allConstants.Interior.Color = Color.LightBlue;
         
// Save to an excel file
workbook.Save("specialcellsinexistingfiles.xlsx");

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["E2"].Formula = "=sum(A1:A2, B4,C1:C3)";
worksheet.Range["A1"].Value = 1;
worksheet.Range["A2"].Value = 2;
worksheet.Range["B4"].Value = 3;
worksheet.Range["C1"].Value = 4;
worksheet.Range["C2"].Value = 5;
worksheet.Range["C3"].Value = 6;
 
foreach (var item in worksheet.Range["E2"].GetPrecedents())
{
    item.Interior.Color = Color.Pink;
}
         
// Save to an excel file
workbook.Save("precedents.xlsx");

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Value = 100;
worksheet.Range["C1"].Formula = "=$A$1";
worksheet.Range["E1:E5"].Formula = "=$A$1";
foreach (var item in worksheet.Range["A1"].GetDependents())
{
    item.Interior.Color = Color.Azure;
}
         
// Save to an excel file
workbook.Save("dependents.xlsx");

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F16"].Value = sourceData;
worksheet.Range["A:F"].ColumnWidth = 15; 
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]); 
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["H7"], "pivottable1");   
 
// defer layout update
pivottable.DeferLayoutUpdate = true;   
 
//config pivot table's fields 
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.RowField;   
 
var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;   
 
var field_Amount = pivottable.PivotFields["Amount"];
pivottable.AddDataField(field_Amount, "sum amount", ConsolidationFunction.Sum);   
 
// must update the
pivottable.pivottable.Update();   
 
//save to an excel file
workbook.Save("PivotTable.xlsx");