/// <summary>
/// 将Excel区域内容置于二维字符串数组中
/// </summary>
/// <param name="rng">Excel区域</param>
/// <returns>存储由Excel内容的二维字符串数组</returns>
public static string[,] GetExcelContent(this Excel.Range rng)
{
string[,] excelContent = new string[rng.Rows.Count, rng.Columns.Count];//初始化内容数组
for (int i = 1; i <= rng.Rows.Count; i++)
{
for (int j = 1; j <= rng.Columns.Count; j++)
{
excelContent[i - 1, j - 1] = rng[i, j].Text as string;
}
}
return(excelContent);
}
static void Main(string[] args)
{
try
{
//
Console.WriteLine("NetOffice Utils Concept Test");
Console.WriteLine("0 Milliseconds trace values is not a bug - its just to fast\r\n");
NetOffice.Settings.Default.PerformanceTrace.Alert += new NetOffice.PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert);
// Criteria 1
// Enable performance trace in excel generaly. set interval limit to 100 to see all actions there need >= 100 milliseconds
NetOffice.Settings.Default.PerformanceTrace["ExcelApi"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["ExcelApi"].IntervalMS = 100;
// Criteria 2
// Enable additional performance trace for all members of Range in excel. set interval limit to 20 to see all actions there need >=20 milliseconds
NetOffice.Settings.Default.PerformanceTrace["ExcelApi", "Range"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["ExcelApi", "Range"].IntervalMS = 20;
// Criteria 3
// Enable additional performance trace for WorkSheet Range property in excel. set interval limit to 0 to see all calls anywhere
NetOffice.Settings.Default.PerformanceTrace["ExcelApi", "Worksheet", "Range"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["ExcelApi", "Worksheet", "Range"].IntervalMS = 0;
// Criteria 4
// Enable additional performance trace for Range this[] indexer in excel. set interval limit to 0 to see all calls anywhere
NetOffice.Settings.Default.PerformanceTrace["ExcelApi", "Range", "_Default"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["ExcelApi", "Range", "_Default"].IntervalMS = 0;
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
Excel.Workbook book = application.Workbooks.Add();
Excel.Worksheet sheet = book.Sheets.Add() as Excel.Worksheet;
for (int i = 1; i <= 5; i++)
{
Excel.Range range = sheet.Range("A" + i.ToString());
range.Value = "Test123";
range[1, 1].Value = "Test234";
}
application.Quit();
application.Dispose();
Console.WriteLine("\r\nTest passed");
Console.ReadKey();
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
Console.ReadKey();
}
}
private static void Thread2Method(object mre)
{
Excel.Worksheet sheet = _application.ActiveSheet as Excel.Worksheet;
foreach (Excel.Range range in sheet.Range("A1:B200"))
{
Excel.Workbook book = range.Application.ActiveWorkbook;
foreach (object item in book.Sheets)
{
Excel.Worksheet otherSheet = item as Excel.Worksheet;
Excel.Range rng = otherSheet.Cells[1, 1];
}
}
(mre as ManualResetEvent).Set();
}
private static void SetPropertyColumnDataTypes(Worksheet ws, PropertyInfo[] simpleProperties)
{
for (var column = 0; column < simpleProperties.Length; column++)
{
var rangeColumn = GetExcelColumnName(column + 1);
NetOffice.ExcelApi.Range range = ws.Range($"{rangeColumn}:{rangeColumn}");
var columnDataTypeAttribute = simpleProperties[column].GetCustomAttribute <ColumnNumberFormatAttribute>();
if (columnDataTypeAttribute == null)
{
SetRangeNumberFormatBasedOnDataType(simpleProperties[column].PropertyType, range);
}
else
{
SetRangeNumberFormatBasedOnAttribute(range, columnDataTypeAttribute.NumberFormat);
}
}
}
static void Main(string[] args)
{
Console.WriteLine("Write 1 million cells in excel.");
NetOffice.Settings.Default.PerformanceTrace.Alert += new NetOffice.PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert);
NetOffice.Settings.Default.PerformanceTrace["ExcelApi"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["ExcelApi"].IntervalMS = 20;
Excel.Application application = new Excel.ApplicationClass();
application.DisplayAlerts = false;
application.Interactive = false;
application.ScreenUpdating = false;
application.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)application.Workbooks[1].Worksheets[1];
Excel.Range rangeCells = workSheet.Cells;
// row
int counter = 0;
DateTime startTime = DateTime.Now;
for (int i = 1; i <= 10000; i++)
{
// column
for (int y = 1; y <= 100; y++)
{
Excel.Range range = rangeCells[i, y];
range.Value = "TestValue";
range.Dispose();
counter++;
}
if (i % 100 == 0)
{
Console.WriteLine("{0} Cells written. Time elapsed: {1}", counter, DateTime.Now - startTime);
}
}
// quit and dispose
application.Quit();
application.Dispose();
Console.WriteLine("Done!");
}
public void Run()
{
// start application
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
// create new Workbook
Excel.Workbook book = application.Workbooks.Add();
Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets[1];
Excel.Range range = sheet.Cells[1, 1];
// Style is defined as Variant in Excel and represents as object in NetOffice
// You can cast them at runtime without problems
Excel.Style style = (Excel.Style)range.Style;
// variant types can be a scalar type at runtime
// another example way to use is
if (range.Style is string)
{
string myStyle = range.Style as string;
}
else if (range.Style is Excel.Style)
{
Excel.Style myStyle = (Excel.Style)range.Style;
}
// Name, Bold, Size are bool but defined as Variant and also converted to object
style.Font.Name = "Arial";
style.Font.Bold = true;
style.Font.Size = 14;
// Please note: the reason for the most variant definition is a more flexible value set.
// the Style property from Range returns always a Style object
// but if you have a new named style created with the name "myStyle" you can set range.Style = myNewStyleObject; or range.Style = "myStyle"
// this kind of flexibility is the primary reason for Variants in Office
// in any case, you dont lost the COM Proxy management from NetOffice for Variants.
// quit & dipose
application.Quit();
application.Dispose();
_hostApplication.ShowFinishDialog();
}
public void Convert(Excel.Worksheet worksheet, IDelimitedTextWriter textWriter)
{
using (Excel.Range usedRange = worksheet.UsedRange)
{
Int32 rowCount;
using (Excel.Range rows = usedRange.Rows)
rowCount = rows.Count;
Int32 columnCount;
using (Excel.Range columns = usedRange.Columns)
columnCount = columns.Count;
for (Int32 rowIndex = 1; rowIndex <= rowCount; rowIndex += 1)
{
Int32 rightColumn = 0;
for (Int32 columnIndex = 1; columnIndex <= columnCount; columnIndex += 1)
{
if (rowIndex > 1 && columnIndex == 1)
{
textWriter.EndRecord();
}
else if (columnIndex > 1)
{
textWriter.EndField();
}
using (Excel.Range cellRange = (Excel.Range)usedRange.Cells[rowIndex, columnIndex])
{
String text = (String)cellRange.Text;
textWriter.Write(text);
if (String.IsNullOrEmpty(text) == false)
{
rightColumn = columnIndex;
}
}
}
this.OnRowConverted(rowIndex, rowCount);
}
}
}
static void Main(string[] args)
{
Console.WriteLine("NetOffice Release Performance Test - 15000 Cells.");
Console.WriteLine("Write simple text, change Font, NumberFormat, WrapText and add a comment.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Worksheet sheet = excelApplication.Workbooks.Add().Worksheets.Add() as Excel.Worksheet;
// do test 10 times
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
sheet.UsedRange.ClearComments();
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 15000; y++)
{
string rangeAdress = "$A" + y.ToString();
Excel.Range cellRange = sheet.get_Range(rangeAdress);
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = true;
cellRange.AddComment("Sample Comment");
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
sheet.DisposeChildInstances();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test3-NetOffice.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
excelApplication.Quit();
excelApplication.Dispose();
}
static void Main(string[] args)
{
Console.WriteLine("NetOffice Release Performance Test - 10000 Cells.");
Console.WriteLine("Write simple text, change Font, NumberFormat and do a BorderArround.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Worksheet sheet = excelApplication.Workbooks.Add().Worksheets.Add() as Excel.Worksheet;
// do test 10 times
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 10000; y++)
{
string rangeAdress = "$A" + y.ToString();
Excel.Range cellRange = sheet.get_Range(rangeAdress);
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, 0);
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
sheet.DisposeChildInstances();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test2-NetOffice.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
excelApplication.Quit();
excelApplication.Dispose();
}
public static void Main()
{
// start excel and turn off msg boxes
NetOffice.ExcelApi.Application excelApplication = new NetOffice.ExcelApi.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
NetOffice.ExcelApi.Workbook workBook = excelApplication.Workbooks.Add();
NetOffice.ExcelApi.Worksheet workSheet = (NetOffice.ExcelApi.Worksheet)workBook.Worksheets[1];
// draw back color and perform the BorderAround method
workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
// draw back color and border the range explicitly
workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen);
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);
workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";
// we need some data to display
NetOffice.ExcelApi.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
NetOffice.ExcelApi.ChartObject chart = ((NetOffice.ExcelApi.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string fileExtension = GetDefaultExtension(excelApplication);
string workbookFile = Path.Combine(Directory.GetCurrentDirectory(), string.Format("Example01{0}", fileExtension));
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
}
public Xl.Worksheet WriteInterchanges(Xl.Worksheet worksheet, IEnumerable <Interchange> interchanges)
{
var tableColOffset = 1;
var tableRowOffset = 1;
var titleCell = worksheet.Cells[1, 1];
titleCell.Value = "Interchange";
//titleCell.Style = _Style.GetMasterTableHeaderStyle(worksheet);
titleCell.EntireColumn.ColumnWidth = 1.0;
var header = new List <object> {
"PrefectureCode", "TempInterchangeId", "IC_Kana", "IC_Kanji", "Highway", "Latitude", "Longitude", "Data_Date"
};
var data = new List <List <object> > {
header
}.Concat(interchanges.Select(r =>
new List <object> {
r.PrefectureCode, r.TempInterchangeId, r.IC_Kana, r.IC_Kanji, r.HighwayDisplay, r.Latitude, r.Longitude
, DateTime.SpecifyKind(r.DataDate, DateTimeKind.Utc).ToLocalTime()
})).ToArray().CreateRectangularArray();
var tableTopLeft = worksheet.Cells[tableRowOffset + 1, tableColOffset + 1];
var tableBottomRight = worksheet.Cells[interchanges.Count() + tableRowOffset + 1, header.Count() + tableColOffset];
Xl.Range range = worksheet.Range(tableTopLeft, tableBottomRight);
range.set_Value(Type.Missing, data);
var opList = worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes);
opList.Name = $"Zenrin.Interchange";
opList.TableStyle = "TableStyleLight9";
opList.ListColumns[8].DataBodyRange.NumberFormatLocal = Format_DateTime;
var colorRange = AddinContext.ExcelApp.Union(opList.ListColumns[1].DataBodyRange,
opList.ListColumns[8].DataBodyRange);
colorRange.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent1;
colorRange.Interior.TintAndShade = 0.5;
opList.Range.Columns.AutoFit();
opList.Range.Rows.AutoFit();
return(worksheet);
}
private void checkBoxColumnAuto_Unchecked(object sender, RoutedEventArgs e)
{
Excel.Application excelApp = Excel.Application.GetActiveInstance(); //获取当前运行的Excel
e2cOptions.ColOptList.Clear(); //清空原有列设置数据
if (excelApp != null)
{
Excel.Range selRange = excelApp.Selection as Excel.Range;
if (selRange != null)
{
double[] tableWidths = selRange.GetTableWidths(true);
for (int i = 1; i <= selRange.Columns.Count; i++)
{
ColumnOptions colOpts = new ColumnOptions(
colName: selRange.Columns[i].Cells[1].Address,
colWidth: tableWidths[i - 1] / e2cOptions.Scale);
e2cOptions.ColOptList.Add(colOpts);
}
}
}
this.listViewColumnSetting.ItemsSource = e2cOptions.ColOptList;
}
/// <summary>
/// 获取Excel区域在CAD中的行高，选择行自动时按字高乘以1.5确定，否则按行高设置值
/// </summary>
/// <param name="rng">Excel区域</param>
/// <param name="isDefault">s是否返回默认值，为true是不管是否选择行自动均返回默认值</param>
/// <returns>Excel区域在CAD中的行高数组</returns>
public static double[] GetTableHeights(this Excel.Range rng, bool isDefault = false)
{
double[] tableHeights = new double[rng.Rows.Count]; //初始化行高数组
if (isDefault || Excel2CADSettings.e2cOptions.RowAuto) //如果选择行自动
{
for (int i = 0; i < rng.Rows.Count; i++)
{
tableHeights[i] = Excel2CADSettings.e2cOptions.TextHeight
* Excel2CADSettings.e2cOptions.Scale * 1.5;//行高度为3单位字高乘以1.5放大系数
}
}
else//如果不采用行自动
{
tableHeights[0] = Excel2CADSettings.e2cOptions.HeaderRowHeight * Excel2CADSettings.e2cOptions.Scale;//表头高度按设置值
for (int i = 1; i < rng.Rows.Count; i++)
{
tableHeights[i] = Excel2CADSettings.e2cOptions.ContentRowHeight * Excel2CADSettings.e2cOptions.Scale;//内容行高度按设置值
}
}
return(tableHeights);
}
public void Run()
{
// Enable and trigger trace alert
NetOffice.Settings.Default.PerformanceTrace.Enabled = true;
NetOffice.Settings.Default.PerformanceTrace.Alert += delegate(NetOffice.PerformanceTrace sender, NetOffice.PerformanceTrace.PerformanceAlertEventArgs args)
{
Console.WriteLine("{0} {1}:{2} in {3} Milliseconds ({4} Ticks)", args.CallType, args.EntityName, args.MethodName, args.TimeElapsedMS, args.Ticks);
};
// Criteria 1
// Enable performance trace in excel generaly. set interval limit to 100ms to see all actions there need >= 100 milliseconds
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].IntervalMS = 100;
// Criteria 2
// Enable additional performance trace for all members of WorkSheet in excel. set interval limit to 20ms to see all actions there need >=20 milliseconds
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet"].IntervalMS = 20;
// Criteria 3
// Enable additional performance trace for WorkSheet Range property in excel. set interval limit to 0ms to see all calls anywhere
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].Enabled = true;
NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].IntervalMS = 0;
// do some stuff
Excel.Application application = new NetOffice.ExcelApi.Application();
application.DisplayAlerts = false;
Excel.Workbook book = application.Workbooks.Add();
Excel.Worksheet sheet = book.Sheets.Add() as Excel.Worksheet;
for (int i = 1; i <= 5; i++)
{
Excel.Range range = sheet.Range("A" + i.ToString());
range.Value = "Test123";
range[1, 1].Value = "Test234";
}
application.Quit();
application.Dispose();
HostApplication.ShowFinishDialog();
}
/// <summary>
/// 返回单元格在合并单元格中的位置
/// </summary>
/// <param name="rng">待检验的区域</param>
/// <returns>位置枚举</returns>
public static PosInMergeRange PositionInMergeRange(this Excel.Range rng)
{
PosInMergeRange pos = PosInMergeRange.LeftTop;
if (rng.MergeCells.Equals(false))//不包含合并单元格
{
pos = PosInMergeRange.NotMergeRange;
}
else if (rng.Row == rng.MergeArea.Row && rng.Column == rng.MergeArea.Column)//左上角
{
pos = PosInMergeRange.LeftTop;
}
else if (rng.Row == rng.MergeArea.Row &&
rng.Column == rng.MergeArea.Column + rng.MergeArea.Columns.Count)//右上角
{
pos = PosInMergeRange.RightTop;
}
else if (rng.Row == rng.MergeArea.Row + rng.MergeArea.Rows.Count &&
rng.Column == rng.MergeArea.Column)//左下角
{
pos = PosInMergeRange.LeftBottom;
}
else if (rng.Row == rng.MergeArea.Row + rng.MergeArea.Rows.Count &&
rng.Column == rng.MergeArea.Column + rng.MergeArea.Columns.Count)//右下角
{
pos = PosInMergeRange.RightBottom;
}
else if (rng.Row == rng.MergeArea.Row + rng.MergeArea.Rows.Count ||
rng.Row == rng.MergeArea.Row ||
rng.Column == rng.MergeArea.Column + rng.MergeArea.Columns.Count ||
rng.Column == rng.MergeArea.Column)//非角点边部单元
{
pos = PosInMergeRange.EdgeMiddle;
}
else//中间单元
{
pos = PosInMergeRange.Middle;
}
return(pos);
}
public void Run()
{
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = (Excel.Worksheet)application.Workbooks[1].Worksheets[1];
Excel.Range sampleRange = sheet.Cells[1, 1];
// we set the COMVariant ColorIndex from Font of ouer sample range with the invoker class
Invoker.PropertySet(sampleRange.Font, "ColorIndex", 1);
// creates a native unmanaged ComProxy with the invoker an release immediately
object comProxy = Invoker.PropertyGet(application, "Workbooks");
Marshal.ReleaseComObject(comProxy);
application.Quit();
application.Dispose();
_hostApplication.ShowFinishDialog();
}
public void RunExample()
{
try { File.Delete(fileName); }
catch (Exception ex) { }
try
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
workBook.SaveAs(fileName);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
//_hostApplication.ShowFinishDialog(null, fileName);
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
//TODO Log Error Helper
MessageBox.Show("The Excel file could not be created. Please check that you have Microsoft Excel 2003 or Higher Installed", "IQTools", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public string CreateExcel()
{
string returnString = "";
try
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
//Add Extension Here
fileName += GetDefaultExtension(excelApplication);
try { File.Delete(fileName); }
catch (Exception ex) { returnString = ex.Message.ToString(); }
Excel.Workbook workBook = excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
Excel.Range dataRange = PutSampleData(workSheet);
// save the book
workBook.SaveAs(fileName);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
// show dialog for the user(you!)
//_hostApplication.ShowFinishDialog(null, fileName);
return("success");
}
catch (Exception ex)
{
returnString += ex.Message.ToString();
return(returnString);
}
}
public Xl.Worksheet WriteHighwayInterchanges(Xl.Worksheet worksheet, IEnumerable <HighwayInterchange> highwayInterchanges)
{
var tableColOffset = 1;
var tableRowOffset = 1;
var titleCell = worksheet.Cells[1, 1];
titleCell.Value = "HighwayInterchange";
//titleCell.Style = _Style.GetMasterTableHeaderStyle(worksheet);
titleCell.EntireColumn.ColumnWidth = 1.0;
var header = new List <object> {
"HighwayId", "Highway", "Interchange", "SortOrder", "Latitude", "Longitude"
};
var data = new List <List <object> > {
header
}.Concat(highwayInterchanges.Select(r =>
new List <object> {
r.TempHighwayId, r.HighwayKanji, r.Interchange.IC_Kanji, r.SortOrder, r.Interchange.Latitude, r.Interchange.Longitude
})).ToArray().CreateRectangularArray();
var tableTopLeft = worksheet.Cells[tableRowOffset + 1, tableColOffset + 1];
var tableBottomRight = worksheet.Cells[highwayInterchanges.Count() + tableRowOffset + 1, header.Count() + tableColOffset];
Xl.Range range = worksheet.Range(tableTopLeft, tableBottomRight);
range.set_Value(Type.Missing, data);
var opList = worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range, null, XlYesNoGuess.xlYes);
opList.Name = $"Zenrin.HighwayInterchange";
opList.TableStyle = "TableStyleLight9";
var colorRange = opList.ListColumns[1].DataBodyRange;
colorRange.Interior.ThemeColor = XlThemeColor.xlThemeColorAccent1;
colorRange.Interior.TintAndShade = 0.5;
opList.Range.Columns.AutoFit();
opList.Range.Rows.AutoFit();
return(worksheet);
}
public void FindApplicableTransformations()
{
addIn.Log("FindApplicableTransformations");
addIn.bbTransformations.clearTransformationsRibbon(addIn);
//Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
Excel.Range selectedRange = addIn.Application.Selection;
Excel.Range selectedCell = selectedRange[1, 1];
string Formula = (string)selectedCell.Formula;
if ((bool)selectedCell.HasFormula && Formula.Length > 0)
{
Formula = RemoveFirstSymbol(Formula);
foreach (FSharpTransformationRule t in AllTransformations)
{
if (t.CanBeAppliedonBool(Formula))
{
RibbonDropDownItem item = Globals.Factory.GetRibbonFactory().CreateRibbonDropDownItem();
item.Label = t.Name;
addIn.theRibbon.dropDown1.Items.Add(item);
}
}
if (addIn.theRibbon.dropDown1.Items.Count > 0)
{
addIn.bbTransformations.MakePreview(addIn);
}
else
{
MessageBox.Show("No applicable rewrites found.");
}
}
else
{
MessageBox.Show("Cell does not contain a formula.");
}
}
public TestResult DoTest()
{
Excel.Application application = null;
DateTime startTime = DateTime.Now;
try
{
// start excel and turn off msg boxes
application = new Excel.Application();
application.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = application.Workbooks.Add();
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
}
catch (Exception exception)
{
return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
}
finally
{
if (null != application)
{
application.Quit();
application.Dispose();
}
}
}
public void Run()
{
// this example demonstrate the NetOffice low-level interface for latebinding calls
Excel.Application application = new Excel.ApplicationClass();
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Worksheet sheet = (Excel.Worksheet)application.Workbooks[1].Worksheets[1];
Excel.Range sampleRange = sheet.Cells[1, 1];
// we set the COMVariant ColorIndex from Font of ouer sample range with the invoker class
Invoker.Default.PropertySet(sampleRange.Font, "ColorIndex", 1);
// creates a native unmanaged ComProxy with the invoker an release immediately
object comProxy = Invoker.Default.PropertyGet(application, "Workbooks");
Marshal.ReleaseComObject(comProxy);
application.Quit();
application.Dispose();
HostApplication.ShowFinishDialog();
}
public virtual NetOffice.ExcelApi.QueryTable Add(object connection, NetOffice.ExcelApi.Range destination)
{
return(InvokerService.InvokeInternal.ExecuteKnownReferenceMethodGet <NetOffice.ExcelApi.QueryTable>(this, "Add", typeof(NetOffice.ExcelApi.QueryTable), connection, destination));
}
public ExcelTags(Excel.Range range)
{
this.element = range;
}
public Double SumIfs(NetOffice.ExcelApi.Range arg1, NetOffice.ExcelApi.Range arg2, object arg3, object arg4, object arg5)
{
return(InvokerService.InvokeInternal.ExecuteDoubleMethodGet(this, "SumIfs", new object[] { arg1, arg2, arg3, arg4, arg5 }));
}
public Double SumIfs(NetOffice.ExcelApi.Range arg1, NetOffice.ExcelApi.Range arg2, object arg3, object arg4, object arg5, object arg6, object arg7, object arg8, object arg9, object arg10, object arg11, object arg12, object arg13, object arg14, object arg15, object arg16, object arg17, object arg18, object arg19, object arg20, object arg21, object arg22, object arg23, object arg24, object arg25, object arg26, object arg27, object arg28)
{
return(InvokerService.InvokeInternal.ExecuteDoubleMethodGet(this, "SumIfs", new object[] { arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8, arg9, arg10, arg11, arg12, arg13, arg14, arg15, arg16, arg17, arg18, arg19, arg20, arg21, arg22, arg23, arg24, arg25, arg26, arg27, arg28 }));
}
public virtual Int32 ModifyLocation(NetOffice.ExcelApi.Range location)
{
return(InvokerService.InvokeInternal.ExecuteInt32MethodGet(this, "ModifyLocation", location));
}
public virtual void FillAcrossSheets(NetOffice.ExcelApi.Range range)
{
InvokerService.InvokeInternal.ExecuteMethod(this, "FillAcrossSheets", range);
}
public virtual Int32 Modify(NetOffice.ExcelApi.Range location, string sourceData)
{
return(InvokerService.InvokeInternal.ExecuteInt32MethodGet(this, "Modify", location, sourceData));
}
public virtual Int32 SheetPivotTableAfterValueChange(object sh, NetOffice.ExcelApi.PivotTable targetPivotTable, NetOffice.ExcelApi.Range targetRange)
{
return(InvokerService.InvokeInternal.ExecuteInt32MethodGet(this, "SheetPivotTableAfterValueChange", sh, targetPivotTable, targetRange));
}
private void EditNewData(Excel.Worksheet xlSheet)
{
bool ExitInnerLoop;
UsedRange = xlRead.RealUsedRange(xlSheet);
DataFromExcel = xlRead.GetDataFromExcelByHeader(xlSheet);
Headers = xlRead.GetHeaders(UsedRange);
isChanged = false;
var Variables = xlRead.GetCellsData("Tag");
var RemovedVariable = variable.RemoveUnusedItems(Variables, xlSheet.Name);
alarm.RemoveUnusedItems(RemovedVariable);
structure.RemoveUnusedItems(RemovedVariable);
driver.RemoveUnusedItems(RemovedVariable);
scale.RemoveUnusedItems(RemovedVariable);
if (Variables != null) {
for (RowIndex = 0; RowIndex < xlRead.NumberOfRows - 1; RowIndex++) {
if(GetValuesByColumn("Tag") != ""){
var AlarmInfo = alarm.GetItemFromList(GetValuesByColumn("Tag"));
var VariableInfo = variable.GetVariableFromList(GetValuesByColumn("Tag"));
var StructureInfo = structure.GetVariableMemberFromList(GetValuesByColumn("Tag"));
var TaskInfo = driver.GetDriverTask(GetValuesByColumn("Tag"));
var ScaleElementInfo = scale.GetScaleElementFromList(GetValuesByColumn("Tag"));
var ThresholdList = alarm.GetThresholdList(AlarmInfo);
ExitInnerLoop = false;
foreach (var header in Headers){
stHeader = header;
structure.EditInitialValueForMembers(GetValuesByColumn("Tag"), GetValuesByColumn(header), header);
switch(header){
case "Tag":
if(VariableInfo == null){
ExitInnerLoop = variable.AddVariable(GetValuesByColumn("Tag"), xlSheet.Name, GetValuesByColumn("Name"), GetValuesByColumn("Area"));
}
if(xlSheet.Name == "AIA" || xlSheet.Name == "DIA"){
ExitInnerLoop = alarm.AddAlarm(GetValuesByColumn("Tag"), xlSheet.Name, GetValuesByColumn("Area"), GetValuesByColumn("Name"),
GetValuesByColumn("Delay"), GetValuesByColumn("Condition"), GetValuesByColumn("StationName"));
}
else if (xlSheet.Name == "ModbusTCPIP" || xlSheet.Name == "S7TCP") {
alarm.AddStationAlarm(GetValuesByColumn("Tag"), GetValuesByColumn("Area"), GetValuesByColumn("Name"));
}
if(xlSheet.Name == "AIA" || xlSheet.Name == "AI"){
if(ScaleElementInfo == null){
scale.AddNormalizer(GetValuesByColumn("Tag"), "-1", GetValuesByColumn("RawMin"), GetValuesByColumn("RawMax"), GetValuesByColumn("MinRange"),GetValuesByColumn("MaxRange"), "1");
}
}
var ProtoStructMembersName = variable.GetStructurePrototypeNameList(xlSheet.Name);
var ProtoStructMembersType = variable.GetStructurePrototypeTypeList(xlSheet.Name);
var MembersInitialValues = GetRowData(RowIndex);
structure.EnableMemberProperties(GetValuesByColumn("Tag"), ProtoStructMembersName, ProtoStructMembersType, MembersInitialValues, Headers);
if( GetValuesByColumn("StationName") != ""){
if (TaskInfo == null) {
string DriverName = driver.GetDriverName(GetValuesByColumn("StationName"));
string taskVarName = "Tag";
int FunctionCode = 2;
int Type = 1;
if(xlSheet.Name == "AIA" || xlSheet.Name == "AI"){
taskVarName = GetValuesByColumn("Tag") + ":Field";
if (DriverName == "ModbusTCPIP") {
FunctionCode = (int)Enums.ModbusFunctionCode.SingleRegister;
}
Type = (int)Enums.TaskType.Input;
}
else if(xlSheet.Name == "DI" || xlSheet.Name == "DIA") {
taskVarName = GetValuesByColumn("Tag") + ":IO";
if (DriverName == "ModbusTCPIP") {
FunctionCode = (int)Enums.ModbusFunctionCode.SingleRegister;
}
Type = (int)Enums.TaskType.Input;
}
else if(xlSheet.Name == "DO") {
taskVarName = GetValuesByColumn("Tag") + ":IO";
if (DriverName == "ModbusTCPIP") {
FunctionCode = (int)Enums.ModbusFunctionCode.SingleRegister;
}
Type = (int)Enums.TaskType.UnconditionalOutput;
}
ExitInnerLoop = driver.AddNewTask(DriverName, GetValuesByColumn("StationName"), GetValuesByColumn("Tag"), taskVarName , GetValuesByColumn("Address"), Type, GetValuesByColumn("Tag") + ":Forced", GetValuesByColumn("UnitID"),FunctionCode);
}
}
break;
case "Name":
VariableInfo.Name.Description = GetValuesByColumn("Name");
if (xlSheet.Name == "AIA") {
alarm.GetThreshold(ThresholdList,"High").Name.Title = GetValuesByColumn("Name");
alarm.GetThreshold(ThresholdList,"HighHigh").Name.Title = GetValuesByColumn("Name");
alarm.GetThreshold(ThresholdList,"Low").Name.Title = GetValuesByColumn("Name");
alarm.GetThreshold(ThresholdList,"LowLow").Name.Title = GetValuesByColumn("Name");
}
else if(xlSheet.Name == "DIA") {
alarm.GetThreshold(ThresholdList,"Digital").Name.Title = GetValuesByColumn("Name");
}
break;
case "Area":
VariableInfo.Name.Group = GetValuesByColumn("Area") + "." + xlSheet.Name;
if(xlSheet.Name == "AIA" || xlSheet.Name == "DIA"){
AlarmInfo.Name.Area = GetValuesByColumn("Area");
}
break;
case "EU":
structure.GetMemberFromList(StructureInfo, "IO").Name.EU = GetValuesByColumn("EU");
break;
case "RawMin":
ScaleElementInfo.Name.RawMin = GetValuesByColumn("RawMin");
break;
case "RawMax":
ScaleElementInfo.Name.RawMax = GetValuesByColumn("RawMax");
break;
case "MaxRange":
ScaleElementInfo.Name.ScaledMax = GetValuesByColumn("MaxRange");
break;
case "MinRange":
ScaleElementInfo.Name.ScaledMin = GetValuesByColumn("MinRange");
break;
case "Delay":
if(xlSheet.Name == "AIA"){
alarm.GetThreshold(ThresholdList,"High").Execution.SecDelay = GetValuesByColumn("Delay");
alarm.GetThreshold(ThresholdList,"HighHigh").Execution.SecDelay = GetValuesByColumn("Delay");
alarm.GetThreshold(ThresholdList,"Low").Execution.SecDelay = GetValuesByColumn("Delay");
alarm.GetThreshold(ThresholdList,"LowLow").Execution.SecDelay = GetValuesByColumn("Delay");
}
else if(xlSheet.Name == "DIA") {
alarm.GetThreshold(ThresholdList,"Digital").Execution.SecDelay = GetValuesByColumn("Delay");
}
break;
case "UnitID":
if( GetValuesByColumn("StationName") != ""){
if(driver.GetDriverName(GetValuesByColumn("StationName")) == "ModbusTCPIP") {
TaskInfo.ModbusTCP.UnitID = GetValuesByColumn("UnitID");
}
}
break;
case "StationName":
if( GetValuesByColumn("StationName") != ""){
TaskInfo.Name.Station = GetValuesByColumn("StationName");
AlarmInfo.Name.EnableVariable = "Not [" + GetValuesByColumn("Tag") + ":Enable]" + " And " + " Not [" + GetValuesByColumn("Tag")
+ ":GroupDisable]" + " And Not CBool([" + GetValuesByColumn("StationName") + ":StationState])";
}
else {
AlarmInfo.Name.EnableVariable = "Not [" + GetValuesByColumn("Tag") + ":Enable]" + " And " + " Not [" + GetValuesByColumn("Tag") + ":GroupDisable]" ;
}
break;
case "Address":
if( GetValuesByColumn("StationName") != ""){
switch(driver.GetDriverName(GetValuesByColumn("StationName"))) {
case "S7TCP":
TaskInfo.DeviceTaskSettings.DeviceAddress = GetValuesByColumn("Address");
break;
case "ModbusTCPIP":
TaskInfo.ModbusTCP.StartAddress = GetValuesByColumn("Address");
break;
}
}
break;
}
if(ExitInnerLoop) break;
}
}
}
}
if (xlSheet.Name == "ModbusTCPIP" || xlSheet.Name == "S7TCP"){
driver.CreateDriverXML(ProjectDirectory + @"\" + "RESOURCES" + @"\" + ProjectFileName.ToUpper() + @"\", xlSheet.Name);
variable.AddDriver(xlSheet.Name);
for (RowIndex = 0; RowIndex < xlRead.NumberOfRows - 1; RowIndex++) {
foreach (var header in Headers){
stHeader = header;
switch (header) {
case "Tag":
if(driver.GetStation(xlSheet.Name, GetValuesByColumn("Tag")) == null){
driver.AddNewStation(xlSheet.Name,GetValuesByColumn("Tag"), GetValuesByColumn("ServerAddress"),
GetValuesByColumn("BackupServerAddress"));
}
driver.CheckIfStationsExist(xlSheet.Name,xlRead.GetCellsData("Tag"));
break;
case "ServerAddress":
Debug.WriteLine(driver.GetStation(xlSheet.Name, GetValuesByColumn("Tag")).Name);
driver.GetStation(xlSheet.Name, GetValuesByColumn("Tag")).Server.ServerAddress = GetValuesByColumn("ServerAddress");
break;
case "BackupServerAddress":
driver.GetStation(xlSheet.Name, GetValuesByColumn("Tag")).Server.BackupServerAddress = GetValuesByColumn("BackupServerAddress");
break;
}
}
}
}
}
