public static void Run()
{
// Create an Array (length=2)
string[] files = new string[2];
// Specify files with their paths to be merged
files[0] = sourceDir + "sampleMergeFiles_Book1.xls";
files[1] = sourceDir + "sampleMergeFiles_Book2.xls";
// Create a cachedFile for the process
string cacheFile = outputDir + "cacheMergeFiles.txt";
// Output File to be created
string dest = outputDir + "outputMergeFiles.xls";
// Merge the files in the output file. Supports only .xls files
CellsHelper.MergeFiles(files, cacheFile, dest);
// Now if you need to rename your sheets, you may load the output file
Workbook workbook = new Workbook(outputDir + "outputMergeFiles.xls");
// Browse all the sheets to rename them accordingly
int i = 1;
foreach (Worksheet sheet in workbook.Worksheets)
{
sheet.Name = "Sheet1" + i.ToString();
i++;
}
// Re-save the file
workbook.Save(outputDir + "outputMergeFiles.xls");
Console.WriteLine("MergeFiles executed successfully.");
}
private void RemoveAnnotationFromExcelFile(string outPath, string zipOutFolder)
{
var wb = new Workbook(Opts.WorkingFileName);
var sb = new StringBuilder();
foreach (var ws in wb.Worksheets)
{
foreach (var cm in ws.Comments)
{
var cellName = CellsHelper.CellIndexToName(cm.Row, cm.Column);
var str = $"Sheet Name: \"{ws.Name}\", Cell Name: {cellName}, Comment Note: \r\n\"{cm.Note}\"";
sb.AppendLine(str);
sb.AppendLine();
}
}
File.WriteAllText(zipOutFolder + "\\comments.txt", sb.ToString());
foreach (var ws in wb.Worksheets)
{
ws.Comments.Clear();
}
wb.Save(outPath);
}
private IList <code_set_excel_entity> ParseExcelCodeSetItem(string code_sys_code, string code_sys_name, Cells codeSetCells)
{
IList <code_set_excel_entity> codeSetItem = new List <code_set_excel_entity>();
if (codeSetCells == null)
{
return(codeSetItem);
}
//读取worksheet codeset
for (int k = 3; k <= codeSetCells.MaxDataRow; k++) //单个codeset 从第二行开始
{
var row = codeSetCells.CheckRow(k);
if (row == null || row.FirstCell == null)
{
continue;
}
else if (row.GetCellOrNull(0).StringValue.StartsWith("代码", StringComparison.OrdinalIgnoreCase))
{
continue;
}
else
{
var aaa = new code_set_excel_entity();
aaa.code_sys_code = code_sys_code;
aaa.code_sys_name = code_sys_name;
aaa.code = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("A")).StringValue; //第一列
aaa.name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("B")).StringValue; //第二列
aaa.show_name = row.GetCellOrNull(CellsHelper.ColumnNameToIndex("C")).StringValue; //第三列
codeSetItem.Add(aaa);
}
}
return(codeSetItem);
}
public static void Run()
{
//ExStart:SpecifyingSortWarningWhileSortingData
//The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Create workbook.
Workbook workbook = new Workbook(dataDir + "sampleSortAsNumber.xlsx");
//Access first worksheet.
Worksheet worksheet = workbook.Worksheets[0];
//Create your cell area.
CellArea ca = CellArea.CreateCellArea("A1", "A20");
//Create your sorter.
DataSorter sorter = workbook.DataSorter;
//Find the index, since we want to sort by column A, so we should know the index for sorter.
int idx = CellsHelper.ColumnNameToIndex("A");
//Add key in sorter, it will sort in Ascending order.
sorter.AddKey(idx, SortOrder.Ascending);
sorter.SortAsNumber = true;
//Perform sort.
sorter.Sort(worksheet.Cells, ca);
//Save the output workbook.
workbook.Save(dataDir + "outputSortAsNumber.xlsx");
//ExEnd:SpecifyingSortWarningWhileSortingData
}
public static void GetColumnNumber___Should_throw_ArgumentNullException___When_parameter_columnName_is_null()
{
// Arrange, Act
var actual = Record.Exception(() => CellsHelper.GetColumnNumber(null));
// Assert
actual.Should().BeOfType <ArgumentNullException>();
actual.Message.Should().Contain("columnName");
}
// ExStart:PrintTables
public static void PrintTables(Workbook workbook, Aspose.Cells.ExternalConnections.ExternalConnection ec)
{
// Iterate all the worksheets
for (int j = 0; j < workbook.Worksheets.Count; j++)
{
Worksheet worksheet = workbook.Worksheets[j];
// Check all the query tables in a worksheet
for (int k = 0; k < worksheet.QueryTables.Count; k++)
{
Aspose.Cells.QueryTable qt = worksheet.QueryTables[k];
// Check if query table is related to this external connection
if (ec.Id == qt.ConnectionId &&
qt.ConnectionId >= 0)
{
// Print the query table name and print its refersto range
Console.WriteLine("querytable " + qt.Name);
string n = qt.Name.Replace('+', '_').Replace('=', '_');
Name name = workbook.Worksheets.Names["'" + worksheet.Name + "'!" + n];
if (name != null)
{
Range range = name.GetRange();
if (range != null)
{
Console.WriteLine("refersto: " + range.RefersTo);
}
}
}
}
// Iterate all the list objects in this worksheet
for (int k = 0; k < worksheet.ListObjects.Count; k++)
{
ListObject table = worksheet.ListObjects[k];
// Check the data source type if it is query table
if (table.DataSourceType == Aspose.Cells.Tables.TableDataSourceType.QueryTable)
{
// Access the query table related to list object
QueryTable qt = table.QueryTable;
// Check if query table is related to this external connection
if (ec.Id == qt.ConnectionId &&
qt.ConnectionId >= 0)
{
// Print the query table name and print its refersto range
Console.WriteLine("querytable " + qt.Name);
Console.WriteLine("Table " + table.DisplayName);
Console.WriteLine("refersto: " + worksheet.Name + "!" + CellsHelper.CellIndexToName(table.StartRow, table.StartColumn) + ":" + CellsHelper.CellIndexToName(table.EndRow, table.EndColumn));
}
}
}
}
}
public static void Run()
{
string sourceDir = RunExamples.Get_SourceDirectory();
// ExStart:1
Workbook workbook = new Workbook(sourceDir + "GetTextWidthSample.xlsx");
Console.WriteLine("Text width: " + CellsHelper.GetTextWidth(workbook.Worksheets[0].Cells["A1"].StringValue, workbook.DefaultStyle.Font, 1));
// ExEnd:1
Console.WriteLine("GetTextWidth executed successfully.");
}
static void SetLicense()
{
string licPath = @"Aspose.Cells.lic";
Aspose.Cells.License lic = new Aspose.Cells.License();
lic.SetLicense(licPath);
Console.WriteLine(CellsHelper.GetVersion());
System.Diagnostics.Debug.WriteLine(CellsHelper.GetVersion());
Environment.CurrentDirectory = dirPath;
}
void OnCustomCellEdit(object sender, SpreadsheetCustomCellEditEventArgs e)
{
if (!e.ValueObject.IsText || unitOfWork == null)
{
return;
}
var editorInfo = CellsHelper.FindEditor(e.ValueObject.TextValue);
if (editorInfo != null)
{
e.EditSettings = CreateSpinEditSettings(editorInfo.MinValue, editorInfo.MaxValue, editorInfo.Increment);
}
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load source excel file inside the workbook object
Workbook wb = new Workbook(dataDir + "sample.xlsx");
// Save workbook in html format
wb.Save(dataDir + "ExpandTextFromRightToLeft_out_" + CellsHelper.GetVersion() + ".html", SaveFormat.Html);
// ExEnd:1
}
public override bool OnCircular(IEnumerator circularCellsData)
{
CalculationCell cc = null;
ArrayList cur = new ArrayList();
while (circularCellsData.MoveNext())
{
cc = (CalculationCell)circularCellsData.Current;
cur.Add(cc.Worksheet.Name + "!" + CellsHelper.CellIndexToName(cc.CellRow, cc.CellColumn));
}
circulars.Add(cur);
return(true);
}
public static void GetColumnNumber___Should_throw_ArgumentOutOfRangeException___When_parameter_columnName_corresponds_to_column_number_that_is_greater_than_MaximumColumnNumber()
{
var columnNames = new[] { "XFE", "ZZZ" };
// Act
var actuals = columnNames.Select(_ => Record.Exception(() => CellsHelper.GetColumnNumber(_))).ToList();
// Assert
foreach (var actual in actuals)
{
actual.Should().BeOfType <ArgumentOutOfRangeException>();
actual.Message.Should().Contain("columnNumber");
}
}
public static void GetColumnNumber___Should_roundtrip_columnNumber_through_GetColumnName___When_called()
{
for (int expected = 1; expected <= Constants.MaximumColumnNumber; expected++)
{
// Arrange
var columnName = CellsHelper.GetColumnName(expected);
// Act
var actual = CellsHelper.GetColumnNumber(columnName);
// Assert
actual.Should().Be(expected);
}
}
public override void BeforeCalculate(int sheetIndex, int rowIndex, int colIndex)
{
//Find the cell name
string cellName = CellsHelper.CellIndexToName(rowIndex, colIndex);
//Print the sheet, row and column index as well as cell name
System.Diagnostics.Debug.WriteLine(sheetIndex + "----" + rowIndex + "----" + colIndex + "----" + cellName);
//If cell name is B8, interrupt/cancel the formula calculation
if (cellName == "B8")
{
this.Interrupt("Interrupt/Cancel the formula calculation");
} //if
} //BeforeCalculate
public static void GetColumnNumber___Should_throw_ArgumentOutOfRangeException___When_parameter_columnName_contains_too_many_characters()
{
var columnNames = new[] { "abcd", "wierupweiqrupwqieurpwieorupiwqeurpwoierupioqewurpioeurpoiweurpoiweurpioweurioweuriewoureipwurpiweurepwirupweuirwepoiruwepoiru" };
// Act
var actuals = columnNames.Select(_ => Record.Exception(() => CellsHelper.GetColumnNumber(_))).ToList();
// Assert
foreach (var actual in actuals)
{
actual.Should().BeOfType <ArgumentOutOfRangeException>();
actual.Message.Should().Contain("columnNameLength");
actual.Message.Should().Contain("3");
}
}
public static void GetColumnName___Should_throw_ArgumentOutOfRangeException___When_parameter_columnNumber_is_greater_than_Constants_MaximumColumnNumber()
{
// Arrange
var columnNumbers = new[] { Constants.MaximumColumnNumber + 1, int.MaxValue };
// Act
var actuals = columnNumbers.Select(_ => Record.Exception(() => CellsHelper.GetColumnName(_))).ToList();
// Assert
foreach (var actual in actuals)
{
actual.Should().BeOfType <ArgumentOutOfRangeException>();
actual.Message.Should().Contain("columnNumber");
}
}
public static void GetColumnNumber___Should_throw_ArgumentException___When_parameter_columnName_is_not_alphabetic()
{
var columnNames = new[] { "-", " A", "B ", "4" };
// Act
var actuals = columnNames.Select(_ => Record.Exception(() => CellsHelper.GetColumnNumber(_))).ToList();
// Assert
foreach (var actual in actuals)
{
actual.Should().BeOfType <ArgumentException>();
actual.Message.Should().Contain("columnName");
actual.Message.Should().Contain("alphabetic");
}
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Load source excel file inside the workbook object
Workbook wb = new Workbook(sourceDir + "sampleExpandTextFromRightToLeft.xlsx");
// Save workbook in html format
wb.Save(outputDir + "outputExpandTextFromRightToLeft_" + CellsHelper.GetVersion() + ".html", SaveFormat.Html);
Console.WriteLine("ExpandTextFromRightToLeft executed successfully.");
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
//Load the sample Excel file containing Office Add-Ins
Workbook wb = new Workbook(sourceDir + "sampleRenderOfficeAdd-Ins.xlsx");
//Save it to Pdf format
wb.Save(outputDir + "output-" + CellsHelper.GetVersion() + ".pdf");
Console.WriteLine("RenderOfficeAdd_InsWhileConvertingExcelToPdf executed successfully.");
}
public static void Main()
{
// Long name will be truncated to 31 characters
string name1 = CellsHelper.CreateSafeSheetName("this is first name which is created using CellsHelper.CreateSafeSheetName and truncated to 31 characters");
// Any invalid character will be replaced with _
string name2 = CellsHelper.CreateSafeSheetName(" <> + (adj.Private ? \" Private\" : \")", '_');//? shall be replaced with _
// Display first name
Console.WriteLine(name1);
//Display second name
Console.WriteLine(name2);
Console.WriteLine("CreateSafeSheetNames executed successfully.");
}
private static string GetColumnHeading(string docName, string worksheetName, string cellName)
{
//Instantiating a Workbook object
Workbook workbook = new Workbook(docName);
//Obtaining the reference of the worksheet by passing its Name
Worksheet worksheet = workbook.Worksheets[worksheetName];
//Removing value from cell
Cell Cell = worksheet.Cells[cellName];
//Get First row of the column
string ColumnHeadingName = CellsHelper.CellIndexToName(0, Cell.Column);
//return value of heading cell
return(worksheet.Cells[ColumnHeadingName].Value.ToString());
}
void SpreadsheetControl_CustomCellEdit(object sender, XtraSpreadsheet.SpreadsheetCustomCellEditEventArgs e)
{
if (!e.ValueObject.IsText)
{
return;
}
var editorInfo = CellsHelper.FindEditor(e.ValueObject.TextValue);
if (editorInfo != null && e.RepositoryItem is RepositoryItemSpinEdit)
{
RepositoryItemSpinEdit repositoryItemSpinEdit = e.RepositoryItem as RepositoryItemSpinEdit;
repositoryItemSpinEdit.MinValue = editorInfo.MinValue;
repositoryItemSpinEdit.MaxValue = editorInfo.MaxValue;
repositoryItemSpinEdit.Increment = editorInfo.Increment;
repositoryItemSpinEdit.IsFloatValue = false;
}
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
Cells cells = workbook.Worksheets[0].Cells;
Cell cell = cells["B4"];
ReferredAreaCollection ret = cell.GetPrecedents();
ReferredArea area = ret[0];
Console.WriteLine(area.SheetName);
Console.WriteLine(CellsHelper.CellIndexToName(area.StartRow, area.StartColumn));
Console.WriteLine(CellsHelper.CellIndexToName(area.EndRow, area.EndColumn));
//ExEnd:1
Console.ReadKey();
}
static void Main(string[] args)
{
// Tracing Precedents
//Instantiating a Workbook object
Workbook workbook = new Workbook("C:\\book1.xls");
Cells cells = workbook.Worksheets[0].Cells;
Aspose.Cells.Cell cell = cells["B7"];
//Tracing precedents of the cell B7.
//The return array contains ranges and cells.
ReferredAreaCollection ret = cell.GetPrecedents();
//Printing all the precedent cells' name.
if (ret != null)
{
for (int m = 0; m < ret.Count; m++)
{
ReferredArea area = ret[m];
StringBuilder stringBuilder = new StringBuilder();
if (area.IsExternalLink)
{
stringBuilder.Append("[");
stringBuilder.Append(area.ExternalFileName);
stringBuilder.Append("]");
}
stringBuilder.Append(area.SheetName);
stringBuilder.Append("!");
stringBuilder.Append(CellsHelper.CellIndexToName(area.StartRow, area.StartColumn));
if (area.IsArea)
{
stringBuilder.Append(":");
stringBuilder.Append(CellsHelper.CellIndexToName(area.EndRow, area.EndColumn));
}
Console.WriteLine(stringBuilder.ToString());
}
}
}
//This method specifies the CellArea range (start row, start col, end row, end col etc.)
//for the conditional formatting
internal static CellArea GetCellAreaByName(string s)
{
CellArea area = new CellArea();
string[] strCellRange = s.Replace("$", "").Split(':');
int column;
CellsHelper.CellNameToIndex(strCellRange[0], out area.StartRow, out column);
area.StartColumn = column;
if (strCellRange.Length == 1)
{
area.EndRow = area.StartRow;
area.EndColumn = area.StartColumn;
}
else
{
CellsHelper.CellNameToIndex(strCellRange[1], out area.EndRow, out column);
area.EndColumn = column;
}
return(area);
}
private void writeWSData()
{
for (int sor = 0; sor < translationFiles.Count; sor++)
{
TranslationFile tf = translationFiles.ElementAt(sor).Value;
Cell c = ws.Cells[3 + sor, 0];
c.Value = tf.RelativePath;
c = ws.Cells[3 + sor, 1];
c.Value = tf.FileName;
for (int oszlop = 0; oszlop < TotalColumn - 3; oszlop++)
{
ws.Cells[3 + sor, 2 + oszlop].Value = tf.wCounts[oszlop];
}
string lastWCcolumn = CellsHelper.ColumnIndexToName(TotalColumn - 2);
c = ws.Cells[3 + sor, TotalColumn - 1];
c.Formula = "=SUM(C" + (4 + sor).ToString() + ":" + lastWCcolumn + (4 + sor).ToString() + ")";
Helper.AddBold(c);
c = ws.Cells[3 + sor, TotalColumn];
c.Formula = "=SUMPRODUCT(C" + (4 + sor).ToString() + ":" + lastWCcolumn + (4 + sor).ToString() + ",C$1:" + lastWCcolumn + "$1)";
Helper.AddBold(c);
Helper.AddNumberFormat(c, "0");
for (int oszlop = TotalColumn + 1; oszlop < TotalColumn + 14; oszlop++)
{
c = ws.Cells[3 + sor, oszlop];
Helper.AddBackground(c, columnColors[oszlop]);
if (oszlop == TotalColumn + 3 || oszlop == TotalColumn + 8 || oszlop == TotalColumn + 11)
{
Helper.AddNumberFormat(c, "mmmm d.");
}
}
}
Helper.AddConditionalFormatting(ref ws, translationFiles.Count + 2, TotalColumn);
}
/// <summary>
/// Remove annotations in document
/// </summary>
/// <param name="doc"></param>
/// <param name="outPath"></param>
private static void RemoveAnnotations(DocumentInfo doc, string outPath)
{
try
{
var(filename, folder) = PrepareFolder(doc, outPath);
// doc.Workbook.Save($"{folder}/{filename}");
var wb = doc.Workbook;
var sb = new StringBuilder();
foreach (var ws in wb.Worksheets)
{
foreach (var cm in ws.Comments)
{
var cellName = CellsHelper.CellIndexToName(cm.Row, cm.Column);
var str = $"Sheet Name: \"{ws.Name}\", Cell Name: {cellName}, Comment Note: \r\n\"{cm.Note}\"";
sb.AppendLine(str);
sb.AppendLine();
}
}
File.WriteAllText($"{folder}/comments.txt", sb.ToString());
foreach (var ws in wb.Worksheets)
{
ws.Comments.Clear();
}
// wb.Save(outPath);
wb.Save($"{folder}/{filename}");
}
catch (Exception e)
{
NLogger.LogError(App, "RemoveAnnotations", e.Message, outPath);
}
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create an Array (length=2)
string[] files = new string[2];
// Specify files with their paths to be merged
files[0] = dataDir + "Book1.xls";
files[1] = dataDir + "Book2.xls";
// Create a cachedFile for the process
string cacheFile = dataDir + "test.txt";
// Output File to be created
string dest = dataDir + "output.xlsx";
// Merge the files in the output file. Supports only .xls files
CellsHelper.MergeFiles(files, cacheFile, dest);
// Now if you need to rename your sheets, you may load the output file
Workbook workbook = new Workbook(dataDir + "output.xlsx");
int i = 1;
// Browse all the sheets to rename them accordingly
foreach (Worksheet sheet in workbook.Worksheets)
{
sheet.Name = "Sheet1" + i.ToString();
i++;
}
// Re-save the file
workbook.Save(dataDir + "output.xlsx");
// ExEnd:1
}
/// <summary>
/// Gets the name of the range (e.g. A3:B5).
/// </summary>
/// <param name="range">The range.</param>
/// <returns>
/// The name of the range (e.g. A3:B5).
/// </returns>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static string GetName(
this Range range)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
var rowNumbers = range.GetRowNumbers();
var columnNumbers = range.GetColumnNumbers();
string result;
if ((rowNumbers.Count == 1) && (columnNumbers.Count == 1))
{
result = CellsHelper.CellIndexToName(rowNumbers.First() - 1, columnNumbers.First() - 1);
}
else
{
result = Invariant($"{CellsHelper.CellIndexToName(rowNumbers.First() - 1, columnNumbers.First() - 1)}:{CellsHelper.CellIndexToName(rowNumbers.Last() - 1, columnNumbers.Last() - 1)}");
}
return(result);
}
public void Process(int sheetIndex, int rowIndex, int colIndex, String tableName, String columnName)
{
Console.WriteLine("Processing Cell: " + workbook.Worksheets[sheetIndex].Name + "!" + CellsHelper.CellIndexToName(rowIndex, colIndex));
Console.WriteLine("Processing Marker: " + tableName + "." + columnName);
}
