public static void Run()
{
// Create workbook
Workbook wb = new Workbook();
// Add second sheet with name Sheet2
wb.Worksheets.Add("Sheet2");
// Access first sheet and add some integer value in cell C1
// Also add some value in any cell to increase the number of blank rows and columns
Worksheet sht1 = wb.Worksheets[0];
sht1.Cells["C1"].PutValue(4);
sht1.Cells["K30"].PutValue(4);
// Access second sheet and add formula in cell E3 which refers to cell C1 in first sheet
Worksheet sht2 = wb.Worksheets[1];
sht2.Cells["E3"].Formula = "'Sheet1'!C1";
// Calculate formulas of workbook
wb.CalculateFormula();
// Print the formula and value of cell E3 in second sheet before deleting blank columns and rows in Sheet1.
Console.WriteLine("Cell E3 before deleting blank columns and rows in Sheet1.");
Console.WriteLine("--------------------------------------------------------");
Console.WriteLine("Cell Formula: " + sht2.Cells["E3"].Formula);
Console.WriteLine("Cell Value: " + sht2.Cells["E3"].StringValue);
// If you comment DeleteOptions.UpdateReference property below, then the formula in cell E3 in second sheet will not be updated
DeleteOptions opts = new DeleteOptions();
opts.UpdateReference = true;
// Delete all blank rows and columns with delete options
sht1.Cells.DeleteBlankColumns(opts);
sht1.Cells.DeleteBlankRows(opts);
// Calculate formulas of workbook
wb.CalculateFormula();
// Print the formula and value of cell E3 in second sheet after deleting blank columns and rows in Sheet1.
Console.WriteLine("");
Console.WriteLine("");
Console.WriteLine("Cell E3 after deleting blank columns and rows in Sheet1.");
Console.WriteLine("--------------------------------------------------------");
Console.WriteLine("Cell Formula: " + sht2.Cells["E3"].Formula);
Console.WriteLine("Cell Value: " + sht2.Cells["E3"].StringValue);
Console.WriteLine("UpdateReferenceInWorksheets executed successfully.");
}
public static void Run()
{
// ExStart:UpdateReferenceInWorksheets
// Create workbook
Workbook wb = new Workbook();
// Add second sheet with name Sheet2
wb.Worksheets.Add("Sheet2");
// Access first sheet and add some integer value in cell C1
// Also add some value in any cell to increase the number of blank rows and columns
Worksheet sht1 = wb.Worksheets[0];
sht1.Cells["C1"].PutValue(4);
sht1.Cells["K30"].PutValue(4);
// Access second sheet and add formula in cell E3 which refers to cell C1 in first sheet
Worksheet sht2 = wb.Worksheets[1];
sht2.Cells["E3"].Formula = "'Sheet1'!C1";
// Calculate formulas of workbook
wb.CalculateFormula();
// Print the formula and value of cell E3 in second sheet before deleting blank columns and rows in Sheet1.
Console.WriteLine("Cell E3 before deleting blank columns and rows in Sheet1.");
Console.WriteLine("--------------------------------------------------------");
Console.WriteLine("Cell Formula: " + sht2.Cells["E3"].Formula);
Console.WriteLine("Cell Value: " + sht2.Cells["E3"].StringValue);
// If you comment DeleteOptions.UpdateReference property below, then the formula in cell E3 in second sheet will not be updated
DeleteOptions opts = new DeleteOptions();
opts.UpdateReference = true;
// Delete all blank rows and columns with delete options
sht1.Cells.DeleteBlankColumns(opts);
sht1.Cells.DeleteBlankRows(opts);
// Calculate formulas of workbook
wb.CalculateFormula();
// Print the formula and value of cell E3 in second sheet after deleting blank columns and rows in Sheet1.
Console.WriteLine("");
Console.WriteLine("");
Console.WriteLine("Cell E3 after deleting blank columns and rows in Sheet1.");
Console.WriteLine("--------------------------------------------------------");
Console.WriteLine("Cell Formula: " + sht2.Cells["E3"].Formula);
Console.WriteLine("Cell Value: " + sht2.Cells["E3"].StringValue);
// ExEnd:UpdateReferenceInWorksheets
}
public static void Run()
{
// ExStart:UsingFormulaTextFunction
// Create a workbook object
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Put some formula in cell A1
Cell cellA1 = worksheet.Cells["A1"];
cellA1.Formula = "=Sum(B1:B10)";
// Get the text of the formula in cell A2 using FORMULATEXT function
Cell cellA2 = worksheet.Cells["A2"];
cellA2.Formula = "=FormulaText(A1)";
// Calculate the workbook
workbook.CalculateFormula();
// Print the results of A2, It will now print the text of the formula inside cell A1
Console.WriteLine(cellA2.StringValue);
// ExEnd:UsingFormulaTextFunction
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook
Workbook wb = new Workbook();
Cells cells = wb.Worksheets[0].Cells;
// Set formula
Cell cell = cells[0, 0];
cell.SetArrayFormula("=MYFUNC()", 2, 2);
Style style = cell.GetStyle();
style.Number = 14;
cell.SetStyle(style);
// Set calculation options for formula
CalculationOptions copt = new CalculationOptions();
copt.CustomFunction = new CustomFunctionStaticValue();
wb.CalculateFormula(copt);
// Save to xlsx by setting the calc mode to manual
wb.Settings.CalcMode = CalcModeType.Manual;
wb.Save(dataDir + "output_out.xlsx");
// Save to pdf
wb.Save(dataDir + "output_out.pdf");
// ExEnd:1
}
public static void Run()
{
// ExStart:UsingGlobalizationSettings
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Loads an existing spreadsheet containing some data
Workbook book = new Workbook(dataDir + "sample.xlsx");
// Assigns the GlobalizationSettings property of the WorkbookSettings class to the class created in first step
book.Settings.GlobalizationSettings = new CustomSettings();
// Accesses the 1st worksheet from the collection which contains data resides in the cell range A2:B9
Worksheet sheet = book.Worksheets[0];
// Adds Subtotal of type Average to the worksheet
sheet.Cells.Subtotal(CellArea.CreateCellArea("A2", "B9"), 0, ConsolidationFunction.Average, new int[] { 1 });
// Calculates Formulas
book.CalculateFormula();
// Auto fits all columns
sheet.AutoFitColumns();
// Saves the workbook on disc
book.Save(dataDir + "output_out.xlsx");
// ExEnd:UsingGlobalizationSettings
}
static void Main(string[] args)
{
//Instantiating a Workbook object
Workbook book = new Workbook();
//Obtaining the reference of the newly added worksheet
int sheetIndex = book.Worksheets.Add();
Worksheet worksheet = book.Worksheets[sheetIndex];
Cells cells = worksheet.Cells;
Cell cell = null;
//Adding a value to "A1" cell
cell = cells["A1"];
cell.Value = 1;
//Adding a value to "A2" cell
cell = cells["A2"];
cell.Value = 2;
//Adding a value to "A3" cell
cell = cells["A3"];
cell.Value = 3;
//Adding a SUM formula to "A4" cell
cell = cells["A4"];
cell.Formula = "=SUM(A1:A3)";
//Calculating the results of formulas
book.CalculateFormula();
//Saving the Excel file
book.Save("AsposeFormulaEngine.xls");
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Loads an existing spreadsheet containing some data
Workbook book = new Workbook(sourceDir + "sampleCustomLabelsSubtotals.xlsx");
// Assigns the GlobalizationSettings property of the WorkbookSettings class to the class created in first step
book.Settings.GlobalizationSettings = new CustomSettings();
// Accesses the 1st worksheet from the collection which contains data resides in the cell range A2:B9
Worksheet sheet = book.Worksheets[0];
// Adds Subtotal of type Average to the worksheet
sheet.Cells.Subtotal(CellArea.CreateCellArea("A2", "B9"), 0, ConsolidationFunction.Average, new int[] { 1 });
// Calculates Formulas
book.CalculateFormula();
// Auto fits all columns
sheet.AutoFitColumns();
// Saves the workbook on disc
book.Save(outputDir + "outputCustomLabelsSubtotals.xlsx");
Console.WriteLine("CustomLabelsSubtotals executed successfully.");
}
public static void Run()
{
// ExStart:UsingICustomFunctionFeature
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Open the workbook
Workbook workbook = new Workbook();
// Obtaining the reference of the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Adding sample values to cells
worksheet.Cells["B1"].PutValue(5);
worksheet.Cells["C1"].PutValue(100);
worksheet.Cells["C2"].PutValue(150);
worksheet.Cells["C3"].PutValue(60);
worksheet.Cells["C4"].PutValue(32);
worksheet.Cells["C5"].PutValue(62);
// Adding custom formula to Cell A1
workbook.Worksheets[0].Cells["A1"].Formula = "=MyFunc(B1,C1:C5)";
// Calcualting Formulas
workbook.CalculateFormula(false, new CustomFunction());
// Assign resultant value to Cell A1
workbook.Worksheets[0].Cells["A1"].PutValue(workbook.Worksheets[0].Cells["A1"].Value);
// Save the file
workbook.Save(dataDir + "UsingICustomFunction_out.xls");
// ExEnd:UsingICustomFunctionFeature
}
static void Main(string[] args)
{
string FilePath = @"..\..\..\Sample Files\";
string FileName = FilePath + "Adding Formula.xlsx";
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);
//Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);
//Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
//Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
//Calculating the results of formulas
workbook.CalculateFormula();
//Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
//Saving the Excel file
workbook.Save(FileName);
}
public static void Run()
{
// ExStart:SettingSimpleFormulaForNamedRanges
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create an instance of Workbook
Workbook book = new Workbook();
// Get the WorksheetCollection
WorksheetCollection worksheets = book.Worksheets;
// Add a new Named Range with name "NewNamedRange"
int index = worksheets.Names.Add("NewNamedRange");
// Access the newly created Named Range
Name name = worksheets.Names[index];
// Set RefersTo property of the Named Range to a formula. Formula references another cell in the same worksheet
name.RefersTo = "=Sheet1!$A$3";
// Set the formula in the cell A1 to the newly created Named Range
worksheets[0].Cells["A1"].Formula = "NewNamedRange";
// Insert the value in cell A3 which is being referenced in the Named Range
worksheets[0].Cells["A3"].PutValue("This is the value of A3");
// Calculate formulas
book.CalculateFormula();
// Save the result in XLSX format
book.Save(dataDir + "output_out.xlsx");
// ExEnd:SettingSimpleFormulaForNamedRanges
}
public CorrectSheetOutput(Workbook outputBook, SheetReader reader, TipStyle styles,
ValidateColumnCollection columns)
{
outputBook.CalculateFormula();
_output_book = outputBook;
_reader = reader;
_reader.ConvertFormulaToValue();
_styles = styles;
_new_index = 1;
try
{
_target_sheet = outputBook.Worksheets[SheetName];
_target_sheet.Cells.ClearContents(0, 0, _target_sheet.Cells.MaxDataRow, _target_sheet.Cells.MaxDataColumn);
Range rng = _target_sheet.Cells.CreateRange(0, 0, _target_sheet.Cells.MaxRow + 1, _target_sheet.Cells.MaxColumn + 1);
rng.Style = _styles.Normal;
}
catch (Exception)
{
int index = outputBook.Worksheets.Add();
_target_sheet = outputBook.Worksheets[index];
_target_sheet.Name = SheetName;
}
_columns = columns;
foreach (ValidateColumn each in columns.Values)
{
_target_sheet.Cells[0, each.Index].PutValue(each.Name);
_target_sheet.Cells[0, each.Index].Style = _styles.Normal;
}
_target_sheet.ClearComments();
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
// Set formula
Cell cell = cells[0, 0];
cell.SetArrayFormula("=MYFUNC()", 2, 2);
Style style = cell.GetStyle();
style.Number = 14;
cell.SetStyle(style);
// Set calculation options for formula
CalculationOptions calculationOptions = new CalculationOptions();
calculationOptions.CustomEngine = new CustomFunctionStaticValue();
workbook.CalculateFormula(calculationOptions);
// Save to xlsx by setting the calc mode to manual
workbook.Settings.CalcMode = CalcModeType.Manual;
workbook.Save(dataDir + "output_out.xlsx");
// Save to pdf
workbook.Save(dataDir + "output_out.pdf");
// ExEnd:1
}
public static void Run()
{
// Create an instance of Workbook
Workbook book = new Workbook();
// Get the WorksheetCollection
WorksheetCollection worksheets = book.Worksheets;
// Add a new Named Range with name "NewNamedRange"
int index = worksheets.Names.Add("NewNamedRange");
// Access the newly created Named Range
Name name = worksheets.Names[index];
// Set RefersTo property of the Named Range to a formula. Formula references another cell in the same worksheet
name.RefersTo = "=Sheet1!$A$3";
// Set the formula in the cell A1 to the newly created Named Range
worksheets[0].Cells["A1"].Formula = "NewNamedRange";
// Insert the value in cell A3 which is being referenced in the Named Range
worksheets[0].Cells["A3"].PutValue("This is the value of A3");
// Calculate formulas
book.CalculateFormula();
// Save the result in XLSX format
book.Save(outputDir + "outputSettingSimpleFormulaWithRange.xlsx");
Console.WriteLine("SettingSimpleFormulaWithRange executed successfully.");
}
static void Main(string[] args)
{
//Instantiating a Workbook object
Workbook book = new Workbook();
//Obtaining the reference of the newly added worksheet
int sheetIndex = book.Worksheets.Add();
Worksheet worksheet = book.Worksheets[sheetIndex];
Cells cells = worksheet.Cells;
Cell cell = null;
//Adding a value to "A1" cell
cell = cells["A1"];
cell.Value = 1;
//Adding a value to "A2" cell
cell = cells["A2"];
cell.Value = 2;
//Adding a value to "A3" cell
cell = cells["A3"];
cell.Value = 3;
//Adding a SUM formula to "A4" cell
cell = cells["A4"];
cell.Formula = "=SUM(A1:A3)";
//Calculating the results of formulas
book.CalculateFormula();
//Saving the Excel file
book.Save("AsposeFormulaEngine.xls");
}
public static void Run()
{
// ExStart:CalculateIFNAFunction
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create new workbook
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Add data for VLOOKUP
worksheet.Cells["A1"].PutValue("Apple");
worksheet.Cells["A2"].PutValue("Orange");
worksheet.Cells["A3"].PutValue("Banana");
// Access cell A5 and A6
Cell cellA5 = worksheet.Cells["A5"];
Cell cellA6 = worksheet.Cells["A6"];
// Assign IFNA formula to A5 and A6
cellA5.Formula = "=IFNA(VLOOKUP(\"Pear\",$A$1:$A$3,1,0),\"Not found\")";
cellA6.Formula = "=IFNA(VLOOKUP(\"Orange\",$A$1:$A$3,1,0),\"Not found\")";
// Caclulate the formula of workbook
workbook.CalculateFormula();
// Print the values of A5 and A6
Console.WriteLine(cellA5.StringValue);
Console.WriteLine(cellA6.StringValue);
// ExEnd:CalculateIFNAFunction
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load the template workbook
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Print the time before formula calculation
Console.WriteLine(DateTime.Now);
// Set the CreateCalcChain as false
workbook.Settings.CreateCalcChain = false;
// Calculate the workbook formulas
workbook.CalculateFormula();
// Print the time after formula calculation
Console.WriteLine(DateTime.Now);
// ExEnd:1
}
public static void Run()
{
// ExStart:UsingGlobalizationSettings
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Loads an existing spreadsheet containing some data
Workbook book = new Workbook(dataDir + "sample.xlsx");
// Assigns the GlobalizationSettings property of the WorkbookSettings class to the class created in first step
book.Settings.GlobalizationSettings = new CustomSettings();
// Accesses the 1st worksheet from the collection which contains data resides in the cell range A2:B9
Worksheet sheet = book.Worksheets[0];
// Adds Subtotal of type Average to the worksheet
sheet.Cells.Subtotal(CellArea.CreateCellArea("A2", "B9"), 0, ConsolidationFunction.Average, new int[] { 1 });
// Calculates Formulas
book.CalculateFormula();
// Auto fits all columns
sheet.AutoFitColumns();
// Saves the workbook on disc
book.Save(dataDir + "output_out.xlsx");
// ExEnd:UsingGlobalizationSettings
}
private void button2_Click(object sender, EventArgs e)
{
new Aspose.Cells.License().SetLicense(new MemoryStream(Resource1.Aspose_Cells));
Workbook workbook = new Workbook(textBox1.Text);
string conn = ConfigurationManager.ConnectionStrings["GrReporting"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(conn);
sqlConn.Open();
SqlCommand sqlComm = sqlConn.CreateCommand();
try
{
Handler handler = new Handler();
fillExcle(workbook.Worksheets["Misc Replications"], handler.getMisc(), sqlComm);
fillExcle(workbook.Worksheets["MRI Replications"], handler.getMri(), sqlComm);
fillExcle(workbook.Worksheets["Data Export"], handler.getExport(textBox2.Text, textBox3.Text), sqlComm);
workbook.CalculateFormula();
}
finally
{
sqlConn.Close();
}
workbook.Save(textBox1.Text);
}
public static void Run()
{
// Create an instance of Workbook
Workbook book = new Workbook();
// Get the WorksheetCollection
WorksheetCollection worksheets = book.Worksheets;
// Insert some data in cell A1 of Sheet1
worksheets["Sheet1"].Cells["A1"].PutValue(10);
// Add a new Worksheet and insert a value to cell A1
worksheets[worksheets.Add()].Cells["A1"].PutValue(10);
// Add a new Named Range with name "range"
int index = worksheets.Names.Add("range");
// Access the newly created Named Range from the collection
Name range = worksheets.Names[index];
// Set RefersTo property of the Named Range to a SUM function
range.RefersTo = "=SUM(Sheet1!$A$1,Sheet2!$A$1)";
// Insert the Named Range as formula to 3rd worksheet
worksheets[worksheets.Add()].Cells["A1"].Formula = "range";
// Calculate formulas
book.CalculateFormula();
// Save the result in XLSX format
book.Save(outputDir + "outputCalculatingSumUsingNamedRange.xlsx");
Console.WriteLine("CalculatingSumUsingNamedRange executed successfully.");
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create workbook from source excel file
Workbook workbook = new Workbook(sourceDir + "sampleCalculationOfArrayFormula.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// When you will put 100 in B1, then all Data Table values formatted as Yellow will become 120
worksheet.Cells["B1"].PutValue(100);
// Calculate formula, now it also calculates Data Table array formula
workbook.CalculateFormula();
// Save the workbook in pdf format
workbook.Save(outputDir + "outputCalculationOfArrayFormula.pdf");
Console.WriteLine("CalculationOfArrayFormula executed successfully.");
}
static void Main(string[] args)
{
string MyDir = @"Files\";
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);
//Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);
//Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
//Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
//Calculating the results of formulas
workbook.CalculateFormula();
//Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
//Saving the Excel file
workbook.Save(MyDir + "Adding Formula.xls");
}
private void SaveReportFile()
{
// 设置执行公式计算 - 如果代码中用到公式，需要设置计算公式，导出的报表中，公式才会自动计算
workBook_excel.CalculateFormula(true);
// 保存文件
workBook_excel.Save(excelFilePath, SaveFormat.Xlsx);
}
public static void Run()
{
// ExStart:UsingICustomFunctionFeature
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Open the workbook
Workbook workbook = new Workbook();
// Obtaining the reference of the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Adding sample values to cells
worksheet.Cells["B1"].PutValue(5);
worksheet.Cells["C1"].PutValue(100);
worksheet.Cells["C2"].PutValue(150);
worksheet.Cells["C3"].PutValue(60);
worksheet.Cells["C4"].PutValue(32);
worksheet.Cells["C5"].PutValue(62);
// Adding custom formula to Cell A1
workbook.Worksheets[0].Cells["A1"].Formula = "=MyFunc(B1,C1:C5)";
// Calcualting Formulas
workbook.CalculateFormula(false, new CustomFunction());
// Assign resultant value to Cell A1
workbook.Worksheets[0].Cells["A1"].PutValue(workbook.Worksheets[0].Cells["A1"].Value);
// Save the file
workbook.Save(dataDir + "UsingICustomFunction_out.xls");
// ExEnd:UsingICustomFunctionFeature
}
private void Calculate()
{
if (postedFile != null && postedFile.ContentLength > 0)
{
var wbkMain = new Workbook(postedFile.InputStream);
wbkMain.CalculateFormula();
}
}
private void Calculate()
{
if (postedFile != null && postedFile.ContentLength > 0)
{
var wbkMain = new Workbook(postedFile.InputStream);
wbkMain.CalculateFormula();
}
}
public static void Run()
{
// Instantiate the workbook object
Workbook workbook = new Workbook(sourceDir + "sampleFindingCellsWithStringOrNumber.xlsx");
workbook.CalculateFormula();
// Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.EntireContent;
// Find the cell with the input integer or double
Cell cell1 = cells.Find(224, null, opts);
if (cell1 != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell1.Name);
}
else
{
Console.WriteLine("Record not found ");
}
// Find the cell with the input string
Aspose.Cells.Cell cell2 = cells.Find("Items E", null, opts);
if (cell2 != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell2.Name);
}
else
{
Console.WriteLine("Record not found ");
}
// Find the cell containing with the input string
opts.LookAtType = LookAtType.Contains;
Cell cell3 = cells.Find("Data", null, opts);
if (cell3 != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell3.Name);
}
else
{
Console.WriteLine("Record not found ");
}
Console.WriteLine("FindingCellsWithStringOrNumber executed successfully.");
}
//保存文件
private void SaveReportFile()
{
if (!Directory.Exists(@"C:\Users\14439\Desktop\yingpanhao\报表"))
{
Directory.CreateDirectory(@"C:\Users\14439\Desktop\yingpanhao\报表");
}
// 设置执行公式计算 - 如果代码中用到公式，需要设置计算公式，导出的报表中，公式才会自动计算
workBook_excel.CalculateFormula(true);
// 保存文件
workBook_excel.Save(excelFilePath);
}
public static void SaveAs(Workbook workbook, string outputPath)
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(outputPath);
if (!System.IO.Directory.Exists(fileInfo.DirectoryName))
System.IO.Directory.CreateDirectory(fileInfo.DirectoryName);
workbook.CalculateFormula(true);
workbook.Save(outputPath);
}
public static void Main(string[] args)
{
// Create an instance of Workbook
Workbook workbook = new Workbook();
// Access first Worksheet from the collection
Worksheet sheet = workbook.Worksheets[0];
// Access Cell A1 and put a formula to sum values of B1 to B2
Cell a1 = sheet.Cells["A1"];
a1.Formula = "=Sum(B1:B2)";
// Assign values to cells B1 & B2
sheet.Cells["B1"].PutValue(10);
sheet.Cells["B2"].PutValue(10);
// Calculate all formulas in the Workbook
workbook.CalculateFormula();
// The result of A1 should be 20 as per default calculation engine
Console.WriteLine("The value of A1 with default calculation engine: " + a1.StringValue);
// Create an instance of CustomEngine
CustomEngine engine = new CustomEngine();
// Create an instance of CalculationOptions
CalculationOptions opts = new CalculationOptions();
// Assign the CalculationOptions.CustomEngine property to the instance of CustomEngine
opts.CustomEngine = engine;
// Recalculate all formulas in Workbook using the custom calculation engine
workbook.CalculateFormula(opts);
// The result of A1 will be 50 as per custom calculation engine
Console.WriteLine("The value of A1 with custom calculation engine: " + a1.StringValue);
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
}
public static void Run()
{
// Create an instance of Workbook
Workbook workbook = new Workbook();
// Access first Worksheet from the collection
Worksheet sheet = workbook.Worksheets[0];
// Access Cell A1 and put a formula to sum values of B1 to B2
Cell a1 = sheet.Cells["A1"];
a1.Formula = "=Sum(B1:B2)";
// Assign values to cells B1 & B2
sheet.Cells["B1"].PutValue(10);
sheet.Cells["B2"].PutValue(10);
// Calculate all formulas in the Workbook
workbook.CalculateFormula();
// The result of A1 should be 20 as per default calculation engine
Console.WriteLine("The value of A1 with default calculation engine: " + a1.StringValue);
// Create an instance of CustomEngine
CustomEngine engine = new CustomEngine();
// Create an instance of CalculationOptions
CalculationOptions opts = new CalculationOptions();
// Assign the CalculationOptions.CustomEngine property to the instance of CustomEngine
opts.CustomEngine = engine;
// Recalculate all formulas in Workbook using the custom calculation engine
workbook.CalculateFormula(opts);
// The result of A1 will be 50 as per custom calculation engine
Console.WriteLine("The value of A1 with custom calculation engine: " + a1.StringValue);
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
}
public static void Run()
{
// ExStart:1
// Instantiate the workbook object
Workbook workbook = new Workbook(sourceDir + "sampleFindDataOrFormulas.xlsx");
workbook.CalculateFormula();
// Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;
// Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();
// Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 8;
ca.StartColumn = 2;
ca.EndRow = 17;
ca.EndColumn = 13;
// Set cells area for find options
findOptions.SetRange(ca);
// Set searching properties
findOptions.SearchBackward = false;
findOptions.SeachOrderByRows = true;
// Set the lookintype, you may specify, values, formulas, comments etc.
findOptions.LookInType = LookInType.Values;
// Set the lookattype, you may specify Match entire content, endswith, starwith etc.
findOptions.LookAtType = LookAtType.EntireContent;
// Find the cell with value
Cell cell = cells.Find(276, null, findOptions);
if (cell != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell.Name);
}
else
{
Console.WriteLine("Record not found ");
}
// ExEnd:1
Console.WriteLine("FindDataOrFormulas executed successfully.");
}
public static void Run()
{
// ExStart:SearchDataUsingOriginalValues
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook object
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Add 10 in cell A1 and A2
worksheet.Cells["A1"].PutValue(10);
worksheet.Cells["A2"].PutValue(10);
// Add Sum formula in cell D4 but customize it as ---
Cell cell = worksheet.Cells["D4"];
Style style = cell.GetStyle();
style.Custom = "---";
cell.SetStyle(style);
// The result of formula will be 20 but 20 will not be visible because the cell is formated as ---
cell.Formula = "=Sum(A1:A2)";
// Calculate the workbook
workbook.CalculateFormula();
// Create find options, we will search 20 using original values otherwise 20 will never be found because it is formatted as ---
FindOptions options = new FindOptions();
options.LookInType = LookInType.OriginalValues;
options.LookAtType = LookAtType.EntireContent;
Cell foundCell = null;
object obj = 20;
// Find 20 which is Sum(A1:A2) and formatted as ---
foundCell = worksheet.Cells.Find(obj, foundCell, options);
// Print the found cell
Console.WriteLine(foundCell);
// Save the workbook
workbook.Save(dataDir + "output_out_.xlsx");
// ExEnd:SearchDataUsingOriginalValues
}
public void CreateStaticReport()
{
//Open the workbook
Workbook workbook = new Workbook();
//Obtaining the reference of the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Adding a sample value to "A1" cell
worksheet.Cells["B1"].PutValue(5);
//Adding a sample value to "A2" cell
worksheet.Cells["C1"].PutValue(100);
//Adding a sample value to "A3" cell
worksheet.Cells["C2"].PutValue(150);
//Adding a sample value to "B1" cell
worksheet.Cells["C3"].PutValue(60);
//Adding a sample value to "B2" cell
worksheet.Cells["C4"].PutValue(32);
//Adding a sample value to "B2" cell
worksheet.Cells["C5"].PutValue(62);
//Adding custom formula to Cell A1
workbook.Worksheets[0].Cells["A1"].Formula = "=MyFunc(B1,C1:C5)";
//Calcualting Formulas
workbook.CalculateFormula(false, new CustomFunction());
//Assign resultant value to Cell A1
workbook.Worksheets[0].Cells["A1"].PutValue(workbook.Worksheets[0].Cells["A1"].Value);
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "UsingICustomFunction.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "UsingICustomFunction.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
/// <summary>
/// 保存文件
/// </summary>
/// <returns></returns>
private void SaveReprotFile()
{
if (!Directory.Exists(@".\Excel"))
{
Directory.CreateDirectory(@".\Excel");
}
// 设置执行公式计算 - 如果代码中用到公式，需要设置计算公式，导出的报表中，公式才会自动计算
CurrentWorkbook.CalculateFormula(true);
// 生成的文件名称
string ReportFileName = string.Format("Excel_{0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd"));
// 保存文件
CurrentWorkbook.Save(@".\Excel\" + ReportFileName, SaveFormat.Xlsx);
}
public static void Run()
{
string targetFileName1 = Application.StartupPath + "\\Folder\\Q7\\7_ori.xlsx";
string targetFileName2 = Application.StartupPath + "\\Folder\\Q7\\7_ori.xls";
Workbook wbTarget1 = new Workbook(targetFileName1);
Workbook wbTarget2 = new Workbook(targetFileName2);
wbTarget1.CalculateFormula();
wbTarget2.CalculateFormula();
Cells cells = wbTarget2.Worksheets[0].Cells;
Cell cell = cells["C5"];
var x = cell.Value;
wbTarget1.Save(targetFileName1.Replace("_ori", ""));
wbTarget2.Save(targetFileName2.Replace("_ori", ""));
}
public string Get()
{
var reault = string.Empty;
var book = new Workbook("./Test.xlsx");
var sheet = book.Worksheets[0];
sheet.Cells["A1"].Value = 7;
sheet.Cells["A2"].Value = 8;
book.CalculateFormula();
reault = sheet.Cells["A3"].Value.ToString();
return(_service.GetMessage() + " hello world! " + reault);
}
public static void Run()
{
// ExStart:SearchDataUsingOriginalValues
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook object
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Add 10 in cell A1 and A2
worksheet.Cells["A1"].PutValue(10);
worksheet.Cells["A2"].PutValue(10);
// Add Sum formula in cell D4 but customize it as ---
Cell cell = worksheet.Cells["D4"];
Style style = cell.GetStyle();
style.Custom = "---";
cell.SetStyle(style);
// The result of formula will be 20 but 20 will not be visible because the cell is formated as ---
cell.Formula = "=Sum(A1:A2)";
// Calculate the workbook
workbook.CalculateFormula();
// Create find options, we will search 20 using original values otherwise 20 will never be found because it is formatted as ---
FindOptions options = new FindOptions();
options.LookInType = LookInType.OriginalValues;
options.LookAtType = LookAtType.EntireContent;
Cell foundCell = null;
object obj = 20;
// Find 20 which is Sum(A1:A2) and formatted as ---
foundCell = worksheet.Cells.Find(obj, foundCell, options);
// Print the found cell
Console.WriteLine(foundCell);
// Save the workbook
workbook.Save(dataDir + "output_out.xlsx");
// ExEnd:SearchDataUsingOriginalValues
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);
// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);
// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
// Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
// Calculating the results of formulas
workbook.CalculateFormula();
// Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
// Saving the Excel file
workbook.Save(dataDir + "output.xls");
// ExEnd:1
}
} //clsCalculationMonitor
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Load the sample Excel file
Workbook wb = new Workbook(sourceDir + "sampleCalculationMonitor.xlsx");
//Create calculation options and assign instance of calculation monitor class
CalculationOptions opts = new CalculationOptions();
opts.CalculationMonitor = new clsCalculationMonitor();
//Calculate formula with calculation options
wb.CalculateFormula(opts);
Console.WriteLine("InterruptOrCancelFormulaCalculationOfWorkbook executed successfully.\r\n");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load your source workbook containing MINIFS and MAXIFS functions
Workbook wb = new Workbook(dataDir + "sample_MINIFS_MAXIFS.xlsx");
// Perform Aspose.Cells formula calculation
wb.CalculateFormula();
// Save the calculations result in pdf format
PdfSaveOptions opts = new PdfSaveOptions();
opts.OnePagePerSheet = true;
wb.Save(dataDir + "output_out.pdf", opts);
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load your source workbook containing MINIFS and MAXIFS functions
Workbook wb = new Workbook(dataDir + "sample_MINIFS_MAXIFS.xlsx");
// Perform Aspose.Cells formula calculation
wb.CalculateFormula();
// Save the calculations result in pdf format
PdfSaveOptions opts = new PdfSaveOptions();
opts.OnePagePerSheet = true;
wb.Save(dataDir + "output_out.pdf", opts);
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);
// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);
// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
// Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
// Calculating the results of formulas
workbook.CalculateFormula();
// Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
// Saving the Excel file
workbook.Save(dataDir + "output.xls");
// ExEnd:1
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);
//Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);
//Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
//Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
//Calculating the results of formulas
workbook.CalculateFormula();
//Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
//Saving the Excel file
workbook.Save(dataDir + "output.xls");
}
public CorrectSheetOutput(Workbook outputBook, SheetReader reader, TipStyle styles,
ValidateColumnCollection columns)
{
outputBook.CalculateFormula();
_output_book = outputBook;
_reader = reader;
_reader.ConvertFormulaToValue();
_styles = styles;
_new_index = 1;
try
{
_target_sheet = outputBook.Worksheets[SheetName];
_target_sheet.Cells.ClearContents(0, 0, _target_sheet.Cells.MaxDataRow, _target_sheet.Cells.MaxDataColumn);
Range rng = _target_sheet.Cells.CreateRange(0, 0, _target_sheet.Cells.MaxRow + 1, _target_sheet.Cells.MaxColumn + 1);
// 2017/8/22 穎驊依據高雄小組專案 [03-05][04+] EXCEL匯入格式可否修正為xlsx也可匯入？ 更改為新版 Aspose.Cells_201402 寫法 ，SetStyle()
//rng.Style = _styles.Normal;
rng.SetStyle(_styles.Normal);
}
catch (Exception)
{
int index = outputBook.Worksheets.Add();
_target_sheet = outputBook.Worksheets[index];
_target_sheet.Name = SheetName;
}
_columns = columns;
foreach (ValidateColumn each in columns.Values)
{
_target_sheet.Cells[0, each.Index].PutValue(each.Name);
// 2017/8/22 穎驊依據高雄小組專案 [03-05][04+] EXCEL匯入格式可否修正為xlsx也可匯入？ 更改為新版 Aspose.Cells_201402 寫法 ，SetStyle()
//_target_sheet.Cells[0, each.Index].Style = _styles.Normal;
_target_sheet.Cells[0, each.Index].SetStyle(_styles.Normal);
}
_target_sheet.ClearComments();
}
private static void CalculateSumOfCellRange(string docName, string worksheetName, string firstCellName, string lastCellName, string resultCell)
{
//Instantiating a Workbook object
Workbook workbook = new Workbook(docName);
//Obtaining the reference of the worksheet by passing its Name
Worksheet worksheet = workbook.Worksheets[worksheetName];
//Adding a SUM formula to "A4" cell
worksheet.Cells[resultCell].Formula = "=SUM(" + firstCellName + ":" + lastCellName + ")"; // =SUM(A1:A3)
//Calculating the results of formulas
workbook.CalculateFormula();
//Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
//Saving the Excel file
workbook.Save(docName);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Load the template workbook
Workbook workbook = new Workbook(dataDir + "book1.xls");
//Print the time before formula calculation
Console.WriteLine(DateTime.Now);
//Set the CreateCalcChain as false
workbook.Settings.CreateCalcChain = false;
//Calculate the workbook formulas
workbook.CalculateFormula();
//Print the time after formula calculation
Console.WriteLine(DateTime.Now);
}
static void Main(string[] args)
{
string filePath = "Without creating formula chain.xls";
//Load the template workbook
Workbook workbook = new Workbook(filePath);
//Print the time before formula calculation
Console.WriteLine(DateTime.Now);
//Set the CreateCalcChain as false
workbook.Settings.CreateCalcChain = false;
//Calculate the workbook formulas
workbook.CalculateFormula();
//Print the time after formula calculation
Console.WriteLine(DateTime.Now);
workbook.Save("Without creating formula chain.xls");
}
public void CreateStaticReport()
{
//Open template
string path = System.Web.HttpContext.Current.Server.MapPath("~");
path = path.Substring(0, path.LastIndexOf("\\"));
path += @"\designer\Workbooks\CalculateFormula.xls";
//Instantiate a workbook
Workbook workbook = new Workbook(path);
//Get the cells collection in the first worksheet
Cells cells = workbook.Worksheets[0].Cells;
for (int i = 11; i < 86; i++)
{
//Get a string value from a cell
string strFormula = cells[i, 2].StringValue;
//Set a formula of the Cell
cells[i, 3].Formula = strFormula;
}
//Calculates the result of formulas
workbook.CalculateFormula();
for (int i = 11; i < 86; i++)
{
//Put values obtaining the calculated values
cells[i, 4].PutValue(cells[i, 3].Value);
}
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "CalculateFormula.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "CalculateFormula.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
static void Main(string[] args)
{
string FilePath = @"..\..\..\Sample Files\";
string FileName = FilePath + "Adding Formula.xlsx";
//Load the template workbook
Workbook workbook = new Workbook(FileName);
//Print the time before formula calculation
Console.WriteLine(DateTime.Now);
//Set the CreateCalcChain as false
workbook.Settings.CreateCalcChain = false;
//Calculate the workbook formulas
workbook.CalculateFormula();
//Print the time after formula calculation
Console.WriteLine(DateTime.Now);
workbook.Save(FileName);
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);
//Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);
//Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
//Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
//Calculating the results of formulas
workbook.CalculateFormula();
//Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
//Saving the Excel file
workbook.Save(dataDir + "output.xls");
}
public static void Run()
{
// ExStart:CalculateArrayFormula
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook from source excel file
Workbook workbook = new Workbook(dataDir + "DataTable.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// When you will put 100 in B1, then all Data Table values formatted as Yellow will become 120
worksheet.Cells["B1"].PutValue(100);
// Calculate formula, now it also calculates Data Table array formula
workbook.CalculateFormula();
// Save the workbook in pdf format
workbook.Save(dataDir + "output_out.pdf");
// ExEnd:CalculateArrayFormula
}
public static void Run()
{
// ExStart:CalculatingSumUsingNamedRangeOnDifferentSheets
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create an instance of Workbook
Workbook book = new Workbook();
// Get the WorksheetCollection
WorksheetCollection worksheets = book.Worksheets;
// Insert some data in cell A1 of Sheet1
worksheets["Sheet1"].Cells["A1"].PutValue(10);
// Add a new Worksheet and insert a value to cell A1
worksheets[worksheets.Add()].Cells["A1"].PutValue(10);
// Add a new Named Range with name "range"
int index = worksheets.Names.Add("range");
// Access the newly created Named Range from the collection
Name range = worksheets.Names[index];
// Set RefersTo property of the Named Range to a SUM function
range.RefersTo = "=SUM(Sheet1!$A$1,Sheet2!$A$1)";
// Insert the Named Range as formula to 3rd worksheet
worksheets[worksheets.Add()].Cells["A1"].Formula = "range";
// Calculate formulas
book.CalculateFormula();
// Save the result in XLSX format
book.Save(dataDir + "output_out.xlsx");
// ExEnd:CalculatingSumUsingNamedRangeOnDifferentSheets
}
public static void Run()
{
// ExStart:UsingFormulaTextFunction
// Create a workbook object
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Put some formula in cell A1
Cell cellA1 = worksheet.Cells["A1"];
cellA1.Formula = "=Sum(B1:B10)";
// Get the text of the formula in cell A2 using FORMULATEXT function
Cell cellA2 = worksheet.Cells["A2"];
cellA2.Formula = "=FormulaText(A1)";
// Calculate the workbook
workbook.CalculateFormula();
// Print the results of A2, It will now print the text of the formula inside cell A1
Console.WriteLine(cellA2.StringValue);
// ExEnd:UsingFormulaTextFunction
}
private void CreateDynamicReport(Workbook workbook)
{
//Get the template file path
string path = MapPath("~");
path = path.Substring(0, path.LastIndexOf("\\"));
string dataFile = path + "\\Designer\\SchoolData.xls";
//Get the selected list box item
string name = this.ListBox1.SelectedItem.Text;
//Split the array
string[] nameArray = name.Split(' ');
//Get the first worksheet cells
Cells cells = workbook.Worksheets[0].Cells;
//Put the selected value (in the list box) to the cell
cells["H11"].PutValue(name);
//Instantiate a workbook
Workbook dataWorkbook = new Workbook(dataFile);
//Get teachers' name
string[] teachers = new string[dataWorkbook.Worksheets.Count];
for (int i = 0; i < teachers.Length; i++)
teachers[i] = dataWorkbook.Worksheets[i].Cells["L1"].StringValue;
//Put teachers' name into output workbook
cells.ImportArray(teachers, 15, 3, true);
//Get / Set students data
Cell cell = null;
Worksheet dataSheet = dataWorkbook.Worksheets[dataWorkbook.Worksheets.Count - 1];
for (; ; )
{
cell = dataSheet.Cells.FindString(nameArray[0], cell);
if (cell != null)
{
if (dataSheet.Cells[cell.Row, cell.Column + 1].StringValue == nameArray[1])
{
cells["H12"].PutValue(dataSheet.Cells[cell.Row, cell.Column + 2].Value);
break;
}
}
else
break;
}
for (int i = 0; i < dataWorkbook.Worksheets.Count - 1; i++)
{
DataTable studentData = dataWorkbook.Worksheets[i].Cells.ExportDataTable(1, 0, cells.MaxDataRow, 8);
foreach (DataRow row in studentData.Rows)
{
if (row[0].ToString() == nameArray[0] && row[1].ToString() == nameArray[1])
{
for (int j = 2; j < row.ItemArray.Length; j++)
{
cells[15 + i, j + 2].PutValue(row[j]);
}
}
}
}
//Specify some formulas for Marks Average and Grade
for (int i = 15; i < 21; i++)
{
cells[i, 10].Formula = "=AVERAGE(E" + (i + 1).ToString() + ":J" + (i + 1).ToString() + ")";
cells[i, 11].Formula = "=IF(K" + (i + 1).ToString() + "<>\"\",HLOOKUP(K"
+ (i + 1).ToString() + ",'Grade Table'!$C$3:$O$4,2),\"\")";
}
//Calculate all the formulas
workbook.CalculateFormula();
int courseIndex = -1;
double minScore = -1;
for (int i = 15; i < 21; i++)
{
double score = cells[i, 10].DoubleValue;
if (score < 80)
{
if (minScore < 0)
{
minScore = score;
courseIndex = i;
}
else if (score < minScore)
{
minScore = score;
courseIndex = i;
}
}
}
//Specify some notes for the marksheet
if (courseIndex != -1)
{
string course = cells[courseIndex, 2].StringValue;
string note = "{0} seems to be having difficulties with {1} projects. We offer after school tutoring sessions"; ;
note = string.Format(note, nameArray[0], course);
cells["C25"].PutValue(note);
cells["C26"].PutValue("which may be helpful.");
}
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Open the workbook
Workbook workbook = new Workbook();
//Obtaining the reference of the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Adding a sample value to "A1" cell
worksheet.Cells["B1"].PutValue(5);
//Adding a sample value to "A2" cell
worksheet.Cells["C1"].PutValue(100);
//Adding a sample value to "A3" cell
worksheet.Cells["C2"].PutValue(150);
//Adding a sample value to "B1" cell
worksheet.Cells["C3"].PutValue(60);
//Adding a sample value to "B2" cell
worksheet.Cells["C4"].PutValue(32);
//Adding a sample value to "B2" cell
worksheet.Cells["C5"].PutValue(62);
//Adding custom formula to Cell A1
workbook.Worksheets[0].Cells["A1"].Formula = "=MyFunc(B1,C1:C5)";
//Calcualting Formulas
workbook.CalculateFormula(false, new CustomFunction());
//Assign resultant value to Cell A1
workbook.Worksheets[0].Cells["A1"].PutValue(workbook.Worksheets[0].Cells["A1"].Value);
//Save the file
workbook.Save(dataDir+ "UsingICustomFunction.xls");
}
