static void SimpleSearchValue(Workbook workbook)
{
#region #SimpleSearch
workbook.Calculate();
Worksheet worksheet = workbook.Worksheets["ExpenseReport"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Find and highlight cells that contain the word "holiday".
IEnumerable <Cell> searchResult = worksheet.Search("holiday");
foreach (Cell cell in searchResult)
{
cell.Fill.BackgroundColor = Color.LightGreen;
}
#endregion #SimpleSearch
}
static void Main(string[] args)
{
Workbook.SetLicenseKey("");
// 集計用のワークブックを読み込み
var workbook = new Workbook();
workbook.Open("sendai.xlsx");
// 外部参照を設定
workbook.Worksheets[0].Range["B4:B9"].Formula = "='[aoba.xlsx]Sheet1'!D4+'[izumi.xlsx]Sheet1'!D4+'[miyagino.xlsx]Sheet1'!D4+'[taihaku.xlsx]Sheet1'!D4";
// 外部ワークブックを読み込み（青葉区）
var aoba = new Workbook();
aoba.Open("aoba.xlsx");
// 外部ワークブックを読み込み（泉区）
var izumi = new Workbook();
izumi.Open("izumi.xlsx");
// 外部ワークブックを読み込み（宮城野区）
var miyagino = new Workbook();
miyagino.Open("miyagino.xlsx");
// 外部ワークブックを読み込み（太白区）
var taihaku = new Workbook();
taihaku.Open("taihaku.xlsx");
// 外部参照を更新
workbook.UpdateExcelLink("aoba.xlsx", aoba);
workbook.UpdateExcelLink("izumi.xlsx", izumi);
workbook.UpdateExcelLink("miyagino.xlsx", miyagino);
workbook.UpdateExcelLink("taihaku.xlsx", taihaku);
workbook.Calculate();
// EXCELファイル（.xlsx）に保存
workbook.Save("crossworkbookformula2.xlsx");
// PDFファイル（.pdf）に保存
workbook.Save("crossworkbookformula2.pdf");
}
static void AdvancedSearchValue(Workbook workbook)
{
#region #AdvancedSearch
workbook.Calculate();
Worksheet worksheet = workbook.Worksheets["ExpenseReport"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Specify the search term.
string searchString = DateTime.Today.ToString("d");
// Specify search options.
SearchOptions options = new SearchOptions();
options.SearchBy = SearchBy.Columns;
options.SearchIn = SearchIn.Values;
options.MatchEntireCellContents = true;
// Find and highlight all cells that contain today's date.
IEnumerable <Cell> searchResult = worksheet.Search(searchString, options);
foreach (Cell cell in searchResult)
{
cell.Fill.BackgroundColor = Color.LightGreen;
}
#endregion #AdvancedSearch
}
private void XLS_Load(object sender, EventArgs e)
{
// Create a new workbook.
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
// Access the first worksheet in the workbook.
// Set the unit of measurement.
workbook.Unit = DevExpress.Office.DocumentUnit.Point;
workbook.BeginUpdate();
try
{
// Create a multiplication table.
worksheet.Cells["A1"].Value = "*";
for (int i = 1; i < 11; i++)
{
// Create the header column.
worksheet.Columns["A"][i].Value = i;
// Create the header row.
worksheet.Rows["1"][i].Value = i;
}
// Multiply values of header cells.
worksheet.Range["B2:K11"].Formula = "=B$1*$A2";
// Obtain the data range.
CellRange tableRange = worksheet.GetDataRange();
// Specify the row height and column width.
tableRange.RowHeight = 40;
tableRange.ColumnWidth = 40;
// Align the table content.
tableRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
tableRange.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
// Fill the header cells.
CellRange headerCells = worksheet.Range.Union(worksheet.Range["A1:K1"], worksheet.Range["A2:A11"]);
headerCells.FillColor = Color.FromArgb(0xf7, 0x9b, 0x77);
headerCells.Font.Bold = true;
// Fill cells that contain multiplication results.
worksheet.Range["B2:K11"].FillColor = Color.FromArgb(0xfe, 0xf2, 0xe4);
}
finally
{
workbook.EndUpdate();
}
// Calculate the workbook.
workbook.Calculate();
// Save the document file under the specified name.
workbook.SaveDocument("TestDoc.xlsx", DocumentFormat.OpenXml);
// Export the document to PDF.
workbook.ExportToPdf("TestDoc.pdf");
// Open the PDF document using the default viewer.
System.Diagnostics.Process.Start("TestDoc.pdf");
// Open the XLSX document using the default application.
System.Diagnostics.Process.Start("TestDoc.xlsx");
}
static void Main(string[] args)
{
var files = Directory.GetFiles(InputFilePath);
if (files == null || files.Length == 0)
{
string fullInputFilePath = Path.Combine(Directory.GetCurrentDirectory(), InputFilePath);
Console.WriteLine("Please put a file in \"" + fullInputFilePath + "\"");
Console.WriteLine("Press any key to quit...");
Console.ReadLine();
return;
}
if (files.Length > 1)
{
Console.WriteLine("There are more than one file, but we only test one.");
}
var inputFile = files[0];
var fileName = Path.GetFileName(inputFile);
Console.WriteLine("Benchmark for SpreadServices");
Console.WriteLine();
Console.WriteLine("FileName: \"" + fileName + "\"");
Console.WriteLine();
Workbook workbook = new Workbook();
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
workbook.Open(inputFile);
stopwatch.Stop();
Console.WriteLine("Open time: " + (stopwatch.ElapsedMilliseconds / 1000d).ToString("0.###") + "s");
long memorySize = GC.GetTotalMemory(true);
Console.WriteLine("Used Memory: " + (memorySize / 1024d / 1024d).ToString("##.###") + "M");
Console.WriteLine();
stopwatch.Restart();
workbook.Dirty();
workbook.Calculate();
stopwatch.Stop();
Console.WriteLine("Calclate time " + (stopwatch.ElapsedMilliseconds / 1000d).ToString("0.###") + "s");
memorySize = GC.GetTotalMemory(true);
Console.WriteLine("Used Memory: " + (memorySize / 1024d / 1024d).ToString("##.###") + "M");
Console.WriteLine();
if (!Directory.Exists(OutFilePath))
{
Directory.CreateDirectory(OutFilePath);
}
stopwatch.Restart();
workbook.Save(Path.Combine(OutFilePath, fileName), null, new SaveOptions()
{
IsCompactMode = true
});
stopwatch.Stop();
Console.WriteLine("Save time " + (stopwatch.ElapsedMilliseconds / 1000d).ToString("0.###") + "s");
memorySize = GC.GetTotalMemory(true);
Console.WriteLine("Used Memory: " + (memorySize / 1024d / 1024d).ToString("##.###") + "M");
Console.WriteLine();
// Prevent the GC collect the workbook before we show the memory size.
workbook.Worksheets[0].Cells[0, 0].Value = 1;
Console.WriteLine("Press any key to quit...");
Console.ReadLine();
}
