//---------------------------------------------------------------------
//<Snippet24>
private void namedRange1_BeforeDoubleClick(
Microsoft.Office.Interop.Excel.Range Target, ref bool Cancel)
{
this.namedRange2.Value2 = "The BeforeDoubleClick event occurred.";
this.namedRange2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
this.namedRange2.Font.Italic = true;
}
/// <summary>
/// Handles the NamedRange change event. When this event fires, the
/// original value of the NamedRange control will be restored and a
/// Message Box will be displayed indicating that the user is not
/// authorized to change the value.
/// </summary>
/// <param name="sender">Unused.</param>
/// <param name="e">Unused.</param>
private void usernameNamedRange_Change(Microsoft.Office.Interop.Excel.Range Target)
{
try
{
// Turns off event handler
this.usernameNamedRange.Change -= new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.usernameNamedRange_Change);
this.usernameNamedRange.Value2 = userName;
MessageBox.Show("You are not authorized to change this value.",
"Document Protection Techniques - Excel",
MessageBoxButtons.OK,
MessageBoxIcon.Information,
MessageBoxDefaultButton.Button1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,
"Error handling NamedRange change event.",
MessageBoxButtons.OK,
MessageBoxIcon.Error,
MessageBoxDefaultButton.Button1);
}
finally
{
// Turns on event handler
this.usernameNamedRange.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.usernameNamedRange_Change);
}
}
private void button5_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Range stuff = Globals.ThisAddIn.Application.Selection as Excel.Range;
stuff.Font.ColorIndex = 7;
}
void app_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
//MessageBox.Show("In Sheet Selection Change");
string sheetName = "";
string rangeName = "";
try
{
if ((Excel.Worksheet)Sh is Excel.Worksheet)
{
Excel.Worksheet ws = (Excel.Worksheet)Sh;
sheetName = ws.Name;
Excel.Name nm = (Excel.Name)Target.Name;
rangeName = nm.Name;
notifyRangeSelected(rangeName);
}
}
catch (Exception e)
{
string errorMsg = e.Message;
string donothing_removewarning = "error: " + errorMsg;
//will error when a named range is not selected as name does not exist for other/individual cells
//MessageBox.Show("ERROR: " + sheetName+ " "+donothing_removewarning);
}
//notifyRangeSelected(rangeName);
}
//private TextBox lastFocused;
void workSheet_SelectionChange(Microsoft.Office.Interop.Excel.Range Target)
{
if (textBoxSelector == 1)
{
if (Target.Columns.Count == 1)
{
this.XcoordinateAdrsBox.Text = Target.Address;
}
else
{
}
}
else if (textBoxSelector == 2)
{
if (Target.Columns.Count == 1)
{
this.YcoordinateAdrsBox.Text = Target.Address;
}
else
{
//Target
}
}
else if (textBoxSelector == 3)
{
if (Target.Columns.Count == 1)
{
this.distAdrsBox.Text = Target.Address;
}
else
{
}
}
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//Access vsto application
Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
//Access workbook
Microsoft.Office.Interop.Excel.Workbook workbook = app.ActiveWorkbook;
//Access worksheet
Microsoft.Office.Interop.Excel.Worksheet m_sheet = workbook.Worksheets[1];
//Access vsto worksheet
Microsoft.Office.Tools.Excel.Worksheet sheet = Globals.Factory.GetVstoObject(m_sheet);
//Place some text in cell A1 without wrapping
Microsoft.Office.Interop.Excel.Range cellA1 = sheet.Cells.get_Range("A1");
cellA1.Value = "Sample Text Unwrapped";
//Place some text in cell A5 with wrapping
Microsoft.Office.Interop.Excel.Range cellA5 = sheet.Cells.get_Range("A5");
cellA5.Value = "Sample Text Wrapped";
cellA5.WrapText = true;
//Save the workbook
workbook.SaveAs("OutputVsto.xlsx");
//Quit the application
app.Quit();
}
void PeopleId_Change(Microsoft.Office.Interop.Excel.Range Target)
{
if (ignoreChanges)
{
return;
}
var c = Target as Excel.Range;
var f = c.Cells[1, 3] as Excel.Range;
f.Value2 = BundleFund.Value;
var n = c.Cells[1, 4] as Excel.Range;
if (Globals.ThisWorkbook.ClickToChange)
{
return;
}
if (c.Text.ToString() != "")
{
var ws = Globals.ThisWorkbook.ws;
var header = Globals.ThisWorkbook.header;
var a = ws.SearchPerson(header, c.Text.ToString(), "", "", "");
if (a.Length == 1)
{
n.Value2 = a[0].Name;
var ctl = new PersonResultCtl(a[0], null);
ctl.AddComment(n);
}
}
}
/// <summary>
/// 将单元格格式化为MAC
/// </summary>
/// <param name="rng"></param>
/// <param name="options">参数类型为String，MAC地址分隔符</param>
private void FormatCellToMAC(Microsoft.Office.Interop.Excel.Range rng, Object options)
{
String separator = options != null ? options as String : "-";
if (rng == null)
{
WriteConsole("[Range]参数为null在FormatCellToMAC方法");
return;
}
if (rng.Value == null || Convert.ToString(rng.Value) == "")
{
WriteConsole(rng.Address + "值为空，跳过[格式化MAC]处理。");
}
string val = Convert.ToString(rng.Value);
if (val.IsMacAddress())
{
// 是MAC地址，按格式处理
val = val.Replace(":", "").Replace("-", "");
val = System.Text.RegularExpressions.Regex.Replace(val, @"([\da-fA-F]{2})", "$1" + separator).Trim(separator != ""? separator[0]:'-');
rng.Value = val;
FormatCellToNormalState(rng);
}
else
{
// 不是mac地址，进行格式标记
FormatCellToFormatFaild(rng);
}
}
List <double> rangeToList(Microsoft.Office.Interop.Excel.Range inputRng)
{
object[,] cellValues = (object[, ])inputRng.Value2;
List <double> lst = cellValues.Cast <object>().ToList().ConvertAll(x => Convert.ToDouble(x));
return(lst);
}
private void SheetSelectionChange(object sh, Microsoft.Office.Interop.Excel.Range target)
{
Excel.Worksheet sheet = (Excel.Worksheet)sh;
String cellResult = "";
foreach (Excel.Range c in target.Cells)
{
string changedCell = c.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
/*DialogResult result=MessageBox.Show("Address:" + changedCell + " Value: " + System.Drawing.ColorTranslator.FromOle((int)((double)c.Interior.Color)), "Save", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
* if (result == DialogResult.Yes) {
* MessageBox.Show("Working");
* }*/
//System.Drawing.Color col = System.Drawing.ColorTranslator.FromOle((int)((double)r.Interior.Color));
//cellResult = cellResult + "Address:" + changedCell + " Value: " + System.Drawing.ColorTranslator.FromOle((int)((double)c.Interior.Color))+ "\n";
cellResult = cellResult + "Address:" + changedCell + " Border Info : " + c.Borders.LineStyle + ", " + c.Borders.Weight + ", Font Name: " + c.Font.Name + ", Style :" + c.Style.Font.Size + ", range formula :" + c.HasFormula + "\n";
}
DialogResult result = MessageBox.Show(cellResult, "Save", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
CreateDocument(cellResult);
}
}
private void Status_Change(Microsoft.Office.Interop.Excel.Range Target)
{
// 获取在“Status”命名范围上设置的状态的 StatusID。
Debug.Assert((Globals.ThisWorkbook.CustomerOrdersBindingSource.Current as DataRowView) != null);
DataRowView currentRow = (DataRowView)Globals.ThisWorkbook.CustomerOrdersBindingSource.Current;
Debug.Assert((currentRow.Row as CompanyData.OrdersRow) != null);
CompanyData.OrdersRow orderRow = (CompanyData.OrdersRow)currentRow.Row;
int newStatus = Globals.ThisWorkbook.CurrentCompanyData.Status.FindByStatus(
this.Status.Value2.ToString()).StatusID;
// 检查当订单未能真正履行时，状态是否设置为“已满足”。
// 如果是这样，提醒用户订单无法履行。
if (newStatus == 0 && orderRow.StatusID != 0 && !this.CanFulfillOrder())
{
MessageBox.Show("Order cannot be fulfilled with current inventory levels.");
this.Status.Value2 = orderRow.StatusRow.Status;
return;
}
else if (newStatus == 0 && orderRow.StatusID != 0)
{
// 订单更改为将要履行，因此库存需要
// 更新以移除发货的数量。
this.UpdateInventory();
}
// 更新订单以反映新状态。
orderRow.StatusID = newStatus;
}
private void Status_Change(Microsoft.Office.Interop.Excel.Range Target)
{
// Get the StatusID for the Status just set on the Status named range.
Debug.Assert((Globals.ThisWorkbook.CustomerOrdersBindingSource.Current as DataRowView) != null);
DataRowView currentRow = (DataRowView)Globals.ThisWorkbook.CustomerOrdersBindingSource.Current;
Debug.Assert((currentRow.Row as CompanyData.OrdersRow) != null);
CompanyData.OrdersRow orderRow = (CompanyData.OrdersRow)currentRow.Row;
int newStatus = Globals.ThisWorkbook.CurrentCompanyData.Status.FindByStatus(
this.Status.Value2.ToString()).StatusID;
// Check to see if the status was set to Fulfilled when it could not
// actually be fulfilled. If so, alert the user that the order cannot be fulfilled.
if (newStatus == 0 && orderRow.StatusID != 0 && !this.CanFulfillOrder())
{
MessageBox.Show("Order cannot be fulfilled with current inventory levels.");
this.Status.Value2 = orderRow.StatusRow.Status;
return;
}
else if (newStatus == 0 && orderRow.StatusID != 0)
{
// The order was changed to be fulfilled, so the inventory needs
// to be updated to remove the quantities that were shipped.
this.UpdateInventory();
}
// Update the order to reflect the new status.
orderRow.StatusID = newStatus;
}
void ThisAddIn_SheetChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
//MessageBox.Show("ThisAddIn_SheetChange");
MyRibbon.InvalidateRibbon();
PivotHelper.PostSheetChange(Target);
}
/// FUNCTION FOR FORMATTING EXCEL CELLS
public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)
{
range.Interior.Color=System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);
range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
if (IsFontbool == true)
{
range.Font.Bold = IsFontbool;
}
}
void frm_FormClosed(object sender, System.Windows.Forms.FormClosedEventArgs e)
{
if (frm.DialogResult == System.Windows.Forms.DialogResult.OK)
{
Excel._Worksheet a = (Excel._Worksheet)Application.ActiveWorkbook.ActiveSheet;
if (frm.Status == "C")
{
try
{
a.get_Range(frm.Pos, type).ClearComments();
}
catch { }
a.get_Range(frm.Pos, type).AddComment(frm.TTFormular);
}
else if (frm.Status == "L")
{
try
{
//DataTable dt = frm.DataReturn;
System.Data.DataTable dt = frm.DataReturn;
//Microsoft.Office.Interop.Excel.DataTable dtEx;
//Excel.Workbook _wbook = (Excel.Workbook)Application.ActiveWorkbook;
//_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Excel.Worksheet _wsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet;
Microsoft.Office.Interop.Excel.Range currentRange = _wsheet.get_Range(_address, Type.Missing);
for (int i = 0; i < dt.Columns.Count; i++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[currentRange.Row, i + currentRange.Column];
_range.Font.Bold = true;
_range.set_Value(Type.Missing, dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[i + currentRange.Row + 1, j + currentRange.Column];
_range.set_Value(Type.Missing, dt.Rows[i][j]);
}
}
//string add = _wsheet.Name + "!R1C1:R" + (dt.Rows.Count + 1) + "C" + dt.Columns.Count;
//_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
//Excel.Worksheet _wpivotsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet;
//string des = _wpivotsheet.Name + "!R3C1";
//_wbook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, add).CreatePivotTable(des, "PivotTable1", Type.Missing, Microsoft.Office.Interop.Excel.XlPivotTableVersionList.xlPivotTableVersion10);
}
catch (Exception ex) { BUS.CommonControl.AddLog("ErroLog", __documentDirectory + "\\Log", "[Addin] [" + DateTime.Now.ToString() + "] : " + ex.Message + "\n\t" + ex.Source + "\n\t" + ex.StackTrace); }
//a.get_Range(frm.Pos, type).AddComment(frm.TTFormular);
}
else
{
a.get_Range(frm.Pos, type).set_Value(type, frm.TTFormular);
}
}
}
public string GetGUID(Microsoft.Office.Interop.Excel.Range currentCell)
{
string guid = System.Guid.NewGuid().ToString("N");
if (_GUIDToUpper)
{
guid = guid.ToUpper();
}
return(guid);
}
public void SheetBeforeRightClick(object oSheet, Microsoft.Office.Interop.Excel.Range oTarget, ref bool Cancel)
{
if (xlSheetBeforeRightClick != null)
{
xlSheetBeforeRightClick(oSheet, oTarget, ref Cancel);
}
//Release any COM objects passed into the event
ComRelease(oSheet);
ComRelease(oTarget);
}
public void GetData()
{
Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet);
activeWorksheet.UsedRange.Clear();
//Bind Dataset to the Active Excel Sheet.
Microsoft.Office.Interop.Excel.Range rng = AddData(MeasureExport.ReadStream(GlobalVariables.workingDirectory, GlobalVariables.propertiesDirectory, GlobalVariables.propertiesFile), activeWorksheet);
//Autosize the columns based on the resulting data
activeWorksheet.Columns.EntireColumn.AutoFit();
}
public void SheetChange(object oSheet, Microsoft.Office.Interop.Excel.Range oTarget)
{
if (xlSheetChange != null)
{
xlSheetChange(oSheet, oTarget);
}
//Release any COM objects passed into the event
ComRelease(oSheet);
ComRelease(oTarget);
}
void Application_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
if (frm != null && frm.Status == "I")
{
_address = Target.get_AddressLocal(Target.Row, Target.Column, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, 0, 0);
string address = _address.Replace("$", "");
string value = "";
try { value = Target.get_Value(type).ToString(); }
catch { }
frm.SetValueFocus(address, value);
}
}
private void FormatCellToFirstUpper(Microsoft.Office.Interop.Excel.Range rng, Object options = null)
{
if (rng == null)
{
WriteConsole("[Range]参数为null在[FormatCellToFirstUpper]方法");
return;
}
if (rng.Value == null || Convert.ToString(rng.Value) == "")
{
return;
}
string val = Convert.ToString(rng.Value);
val = val.Substring(0, 1).ToUpper() + val.Substring(1);
rng.Value = val;
}
//
// SOURCE: http://allfaq.org/forums/p/8203/16365.aspx
//
private Microsoft.Office.Interop.Excel.Range AddData(System.Data.DataTable dataTable, Excel.Worksheet activeWorksheet)
{
//create the object to store the column names
object[,] columnNames;
columnNames = new object[1, dataTable.Columns.Count];
//get a range object that the columns will be added to
Microsoft.Office.Interop.Excel.Range columnsNamesRange = (Microsoft.Office.Interop.Excel.Range)activeWorksheet.get_Range(activeWorksheet.Cells[1, 1]
, activeWorksheet.Cells[1, dataTable.Columns.Count]);
//a simple assignement allows the data to be transferred quickly
columnsNamesRange.Value2 = columnNames;
//release the columsn range object now it is finished with
columnsNamesRange = null;
//create the object to store the dataTable data
object[,] rowData;
rowData = new object[dataTable.Rows.Count, dataTable.Columns.Count];
//insert the data into the object[,]
for (int iRow = 0; iRow < dataTable.Rows.Count; iRow++)
{
for (int iCol = 0; iCol < dataTable.Columns.Count; iCol++)
{
rowData[iRow, iCol] = dataTable.Rows[iRow][iCol];
}
}
//get a range to add the table data into
//it is one row down to avoid the previously added columns
Microsoft.Office.Interop.Excel.Range dataCells = (Microsoft.Office.Interop.Excel.Range)activeWorksheet.get_Range(activeWorksheet.Cells[1, 1],
activeWorksheet.Cells[dataTable.Rows.Count, dataTable.Columns.Count]);
//assign data to worksheet
dataCells.Value2 = rowData;
//release range
dataCells = null;
//return the range to the new data
return(activeWorksheet.get_Range(activeWorksheet.Cells[1, 1],
activeWorksheet.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]));
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//Instantiate the Application object.
Excel.Application ExcelApp = Application;
//Add a Workbook.
Excel.Workbook objBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
// Access a Vsto Worksheet
Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
Microsoft.Office.Tools.Excel.Worksheet sheet = Globals.Factory.GetVstoObject(nativeWorksheet);
//Add sample data for pie chart
//Add headings in A1 and B1
sheet.Cells[1, 1] = "Products";
sheet.Cells[1, 2] = "Users";
//Add data from A2 till B4
sheet.Cells[2, 1] = "Aspose.Cells";
sheet.Cells[2, 2] = 10000;
sheet.Cells[3, 1] = "Aspose.Slides";
sheet.Cells[3, 2] = 8000;
sheet.Cells[4, 1] = "Aspose.Words";
sheet.Cells[4, 2] = 12000;
//Chart reference
Microsoft.Office.Tools.Excel.Chart productsChart;
//Add a Pie Chart
productsChart = sheet.Controls.AddChart(0, 105, 330, 200, "ProductUsers");
productsChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie;
//Set chart title
productsChart.HasTitle = true;
productsChart.ChartTitle.Text = "Users";
//Gets the cells that define the data to be charted.
Microsoft.Office.Interop.Excel.Range chartRange = sheet.get_Range("A2", "B4");
productsChart.SetSourceData(chartRange, missing);
//Access the Active workbook from Vsto sheet
Microsoft.Office.Interop.Excel.Workbook workbook = sheet.Application.ActiveWorkbook;
//Save the copy of workbook as OutputVsto.xlsx
workbook.SaveCopyAs("OutputVsto.xlsx");
}
void app_SheetBeforeRightClick(object Sh, Microsoft.Office.Interop.Excel.Range Target, ref bool Cancel)
{
string sheetName = "";
string range = "";
try
{
object missing = Type.Missing;
Excel.Worksheet sheet = (Excel.Worksheet)Sh;
sheetName = sheet.Name;
range = Target.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing);
}
catch (Exception e)
{
string errorMsg = e.Message;
sheetName = "error: " + errorMsg;
}
notifySheetBeforeRightClick(sheetName, range);
}
/// <summary>
/// 向选中的单元格批量写入具体的值
/// </summary>
/// <param name="val"></param>
public void WriteCells(object val)
{
if (val == null)
{
return;
}
Microsoft.Office.Interop.Excel.Range selRang = Globals.ThisAddIn.Application.Selection as Microsoft.Office.Interop.Excel.Range;
Int32 cellTotal = 0;
if (selRang != null && selRang.Cells.Count > 0)
{
cellTotal = selRang.Cells.Count;
for (Int32 i = 1; i <= cellTotal; i++)
{
// 主要任务处理
Microsoft.Office.Interop.Excel.Range c = (Microsoft.Office.Interop.Excel.Range)selRang.Cells[i];
c.Value = val;
}
}
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//Access vsto application
Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
//Access workbook
Microsoft.Office.Interop.Excel.Workbook workbook = app.ActiveWorkbook;
//Access worksheet
Microsoft.Office.Interop.Excel.Worksheet m_sheet = workbook.Worksheets[1];
//Access vsto worksheet
Microsoft.Office.Tools.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(m_sheet);
//Access cells A1, A2, A3 , A4
Microsoft.Office.Interop.Excel.Range cellA1 = worksheet.Range["A1"];
Microsoft.Office.Interop.Excel.Range cellA2 = worksheet.Range["A2"];
Microsoft.Office.Interop.Excel.Range cellA3 = worksheet.Range["A3"];
Microsoft.Office.Interop.Excel.Range cellA4 = worksheet.Range["A4"];
//Set integer values in cells A1, A2 and A3
cellA1.Value = 10;
cellA2.Value = 20;
cellA3.Value = 30;
//Add formula in cell A4
cellA4.Formula = "=Sum(A1:A3)";
//Set the font bold in cell A4
cellA4.Font.Bold = true;
//Set the background color to Yellow in cell A4
cellA4.Interior.Color = Excel.XlRgbColor.rgbYellow;
//Save the workbook
workbook.SaveAs("OutputVsto.xlsx");
//Quit the application
app.Quit();
}
void Application_SheetBeforeDoubleClick(object Sh, Microsoft.Office.Interop.Excel.Range Target, ref bool Cancel)
{
//string _address = "A1";
_address = Target.get_AddressLocal(Target.Row, Target.Column, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, 0, 0).Replace("$", "");
string formular = Target.Formula.ToString();
if (formular.Contains("TT_XLB_EB") || formular.Contains("USER TABLE"))
{
//Target.set_Value(Type.Missing, formular);
//Application.Undo();
if (frm != null)
{
frm.Close();
frm = new QDAddIn(_address, Application, formular, _strConnect, _strConnectDes);
frm.Config = _config;
frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed);
//frm.Pos = _address;
//if (value.Contains("TT_XLB_ED"))
// frm.GetQueryBuilderFromFomular(value);
//frm.TopMost = true;
frm.Show(new WindowWrapper((IntPtr)Application.Hwnd));
}
else
{
frm = new QDAddIn(_address, Application, formular, _strConnect, _strConnectDes);
frm.Config = _config;
frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed);
//frm.Pos = _address;
//if (value.Contains("TT_XLB_ED"))
// frm.GetQueryBuilderFromFomular(value);
//frm.TopMost = true;
//
frm.Show(new WindowWrapper((IntPtr)Application.Hwnd));
}
frm.Focus();
Cancel = true;
}
}
/// <summary>
/// 用指定的方法批量处理单元格
/// </summary>
/// <param name="fun"></param>
public void OperateCells(OperateCellDelegate fun, Object options = null)
{
if (fun == null)
{
WriteConsole("“OperateCells”未设置委托方法。");
return;
}
Microsoft.Office.Interop.Excel.Range selRang = Globals.ThisAddIn.Application.Selection as Microsoft.Office.Interop.Excel.Range;
Int32 cellTotal = 0;
if (selRang != null && selRang.Cells.Count > 0)
{
cellTotal = selRang.Cells.Count;
for (Int32 i = 1; i <= cellTotal; i++)
{
// 主要任务处理
Microsoft.Office.Interop.Excel.Range c = (Microsoft.Office.Interop.Excel.Range)selRang.Cells[i];
fun(c, options);
}
}
}
private void Application_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
Excel.Worksheet sheet = ThisSheet;
if (sheet == null)
{
return;
}
Globals.Ribbons.Ribbon.UsedRangeLabel.Label = "Raw UsedRange: " + sheet.UsedRange.Address[true, true, Excel.XlReferenceStyle.xlA1, true];
Globals.Ribbons.Ribbon.UsedRangeRowCol.Label = $"Raw UsedRange: rows: {sheet.UsedRange.Rows.Count}, cols: {sheet.UsedRange.Columns.Count}";
int E4usedRow = (int)Globals.ThisAddIn.Application.ExecuteExcel4Macro("Get.Document(10)");
int E4usedCol = (int)Globals.ThisAddIn.Application.ExecuteExcel4Macro("Get.Document(12)");
Globals.Ribbons.Ribbon.UsedRangeE4.Label = $"Excel 4 Macro rows: {E4usedRow}, cols: {E4usedCol}";
var fromA1Range = sheet.Range[sheet.Cells[1, 1], sheet.UsedRange] as Excel.Range;
Globals.Ribbons.Ribbon.UsedRangeFromBeginLabel.Label = "From A1: " + fromA1Range.Address[true, true, Excel.XlReferenceStyle.xlA1, true] + ", Area count:" + fromA1Range.Areas.Count;
Globals.Ribbons.Ribbon.UsedRangeFromBeginRowCol.Label = $"From A1: rows: {fromA1Range.Rows.Count}, cols: {fromA1Range.Columns.Count}";
Globals.Ribbons.Ribbon.SelectionLabel.Label = "Selection: " + Target.Address[true, true, Excel.XlReferenceStyle.xlA1, true] + ", Area count:" + Target.Areas.Count;
}
public void read_Excel_File()
{
//----------------< read_Excel_File_into_DataGridView() >------------
//</ init >
Excel.Range usedRange = this.UsedRange;
int nColumnsMax = 0;
if (usedRange.Rows.Count > 0)
{
//----< Read_Header >----
for (int iColumn = 1; iColumn <= usedRange.Columns.Count; iColumn++)
{
Excel.Range cell = usedRange.Cells[1, iColumn] as Excel.Range;
String sValue = cell.Value2.ToString();
if (sValue == "")
{
break;
}
}
//----</ Read_Header >----
//----< Read_DataRows >----
for (int iRow = 2; iRow <= usedRange.Rows.Count; iRow++)
{
for (int iColumn = 1; iColumn <= nColumnsMax; iColumn++)
{
Microsoft.Office.Interop.Excel.Range cell = usedRange.Cells[iRow, iColumn] as Excel.Range;
String sValue = cell.Value2.ToString();
}
}
//----</ Read_DataRows >----
}
//----------------</ read_Excel_File_into_DataGridView() >------------
}
