public void DisplayCell()
{
string fp = ChooseFilePath(); // Uses function below to find file path
Excel excel = new Excel(fp, 1); // Creates a new instance of the Excel class
addListItem(excel.ReadCell(0, 0)); // Uses the excel class function ReadCell and displays result in the listbox
}
public int Run()
{
bool result = InitPara();
if (!result)
{
return(-999);
}
int failCount = 0;
result = FailCount(ref failCount);
if (!result)
{
Wb.Close(false, Type.Missing, Type.Missing);
Excel.Quit();
return(-998);
}
Wb.Close(false, Type.Missing, Type.Missing);
Excel.Quit();
if (failCount < 0)
{
failCount = failCount * -1;
}
return(0);
}
/// <summary/>
public PluginManager( MSExcel.Application application )
{
myApplication = application;
myPluginContext = new PluginContext( myApplication );
myPlugins = new List<AbstractPlugin>();
}
/// <summary>
/// Class constructor does all the work
/// </summary>
/// <param name="excelApp"></param>
/// <param name="fileName"></param>
public StudentWorkbook( Excel.Application excelApp, string fileName )
{
try // to open the student's spreadsheet
{
excelWorkbook = excelApp.Workbooks.Open( fileName, 0,
true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, // read only
false, 0, true, false, false );
}
catch ( Exception e )
{
Console.WriteLine( "error: " + e.Message );
Console.WriteLine( "Could not open spreadsheet " + fileName );
}
Excel.Sheets excelSheets = excelWorkbook.Worksheets; // get the Worksheets collection
Excel.Worksheet excelWorksheet = excelSheets[ 1 ]; // get the first one
// get the Team Number cell
Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range( "B4", "B4" );
// try to convert this cell to an integer
if ( ( teamNumber = TryForInt( excelCell.Value ) ) == 0 )
{
Console.WriteLine( "\nTeam number invalid in " + fileName + "\n" );
}
// get the scores cells
scores = excelWorksheet.get_Range( "B7", "B15" );
// get the Additional Comments cell
comments = excelWorksheet.get_Range( "B18", "B18" );
} // end of StudentWorkbook()
private void WriteDataToWorkSheet(IScriptWorker CurrentScriptWorker, Excel.Worksheet xlWorkSheet, out int RowCounter, out int ColumnCounterForNamesOfTests)
{
xlWorkSheet.Cells[1, 1] = "";
dynamic DatesOfTests = CurrentScriptWorker.GetDatesOfTests();
RowCounter = 2;
foreach (dynamic CurrentDate in DatesOfTests)
{
xlWorkSheet.Cells[RowCounter, 1] = CurrentDate;
++RowCounter;
}
dynamic TestsForSave = CurrentScriptWorker.GetListOfTests();
ColumnCounterForNamesOfTests = 2;
foreach (dynamic CurrentTest in TestsForSave)
{
xlWorkSheet.Cells[1, ColumnCounterForNamesOfTests] = (string)CurrentTest.GetTestName();
dynamic CurrentTestResults = CurrentTest.GetValues();
int RowCounterForTestValues = 2;
foreach (dynamic CurrentValue in CurrentTestResults)
{
xlWorkSheet.Cells[RowCounterForTestValues, ColumnCounterForNamesOfTests] = CurrentValue;
++RowCounterForTestValues;
}
++ColumnCounterForNamesOfTests;
}
}
private void protectWS(Excel.Worksheet pWS, String pPwdWrite) {
if((pWS != null) && (pPwdWrite != null)) {
Object vWritePwd = Type.Missing;
if(pPwdWrite != null)
vWritePwd = pPwdWrite;
pWS.Protect(
vWritePwd,
true,
true,
true,
false,
true,
true,
true,
false,
false,
false,
false,
false,
true,
true,
true
);
}
}
public FrmAbout(Excel.Application excelapp, CultureInfo culture)
: base(excelapp)
{
InitializeComponent();
//txtVersion.Text = Assembly.GetExecutingAssembly().GetName().Version.ToString();
txtVersion.Text = FileVersionInfo.GetVersionInfo(Assembly.GetExecutingAssembly().Location).FileVersion;
}
public void CloseBook(Excel.Workbook book, bool save)
{
Log.Info(LoggerConstants.ENTER);
if (book == null)
{
Log.Warn(LoggerConstants.BAD_VALIDATION);
Log.Info(LoggerConstants.EXIT);
return;
}
try
{
Log.Info("Book name is `" + book.Name + "`");
if (save)
book.Save();
book.Close();
}
catch (COMException e)
{
Log.Warn("COMException with closing `" + book.Name + "`", e);
}
catch (Exception e)
{
Log.Warn("Exception with closing `" + book.Name + "`", e);
}
Log.Info(LoggerConstants.EXIT);
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (System.Exception)
{
// Response.Write("在释放Excel内存空间时发生了一个错误:" + ex);
}
finally
{
foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
System.GC.SuppressFinalize(this);
}
private void EditCells(Excel.Worksheet excelWorksheet)
{
var col = excelWorksheet.UsedRange.Columns["H:H"];
Random rnd = new Random();
string tempsubstr = String.Empty;
string substr = String.Empty;
foreach (Excel.Range item in col.Cells)
{
tempsubstr = item.Value.ToString();
if (item.Value != "CarPlate")
{
switch (tempsubstr.Length)
{
case 6 :
substr=item.Value.Substring(0, 2);
break;
case 7 :
substr=item.Value.Substring(0, 3);
break;
default :
substr = item.Value.Substring(0, 2);
break;
}
int newNumber = rnd.Next(1, 9999);
item.Value = substr + newNumber.ToString().PadLeft(4, '0');
}
}
}
internal Excel.PivotTable CreateSalesPivotTable(Excel.Range range, String filePath)
{
string fileDirectory = System.IO.Path.GetDirectoryName(filePath);
string fileName = System.IO.Path.GetFileName(filePath);
string pivotTableName = Properties.Resources.SalesAndProfitPivotTableName;
Excel.PivotCache cache = this.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, missing);
cache.Connection = String.Format(
CultureInfo.CurrentUICulture,
salesPivotTableConnectionTemplate,
fileDirectory);
cache.CommandType = Excel.XlCmdType.xlCmdSql;
cache.CommandText = String.Format(
CultureInfo.CurrentUICulture,
salesPivotTableQueryTemplate,
fileName);
Excel.PivotTable pivotTable = cache.CreatePivotTable(range, pivotTableName, missing, Excel.XlPivotTableVersionList.xlPivotTableVersionCurrent);
// 将新的数据透视表的属性调整为
// 所需方式的格式信息。
pivotTable.ErrorString = " -- ";
pivotTable.DisplayErrorString = true;
pivotTable.NullString = " -- ";
return pivotTable;
}
public ReportGenerator(Excel.Worksheet src_worksheet, Excel.Worksheet dest_worksheet)
{
_src_worksheet = src_worksheet;
_dest_worksheet = dest_worksheet;
writeProjectNamesToDestSheet();
}
public static void InsertColumn(Excel.Worksheet xlSht, int ColumnNo)
{
Excel.Range range = (Excel.Range)xlSht.Cells[1, ColumnNo];
Excel.Range column = range.EntireColumn;
column.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Type.Missing);
}
protected void OnSaving(Excel.Workbook xlBook)
{
if (Saving != null)
{
Saving(this, xlBook);
}
}
protected void ReleaseExcel(ref MSOffice.Application excelApp, ref MSOffice.Workbook excelBook, ref MSOffice.Worksheet excelSheet)
{
try
{
excelBook.Close(false, null, null);
excelApp.Quit();
GC.Collect();
IntPtr ptr = new IntPtr(excelApp.Hwnd);
int pid = 0;
GetWindowThreadProcessId(ptr, out pid);
System.Diagnostics.Process proc = System.Diagnostics.Process.GetProcessById(pid);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excelSheet);
excelApp = null;
excelBook = null;
excelSheet = null;
//最后尝试结束进程，出错表示已销毁
try
{ proc.Kill(); }
catch (Exception) { }
}
catch (Exception ex)
{
throw ex;
}
}
public WindowMessageHandler(Excel.Application application)
{
var target = this.FindTarget(new IntPtr(application.Hwnd));
if (target == IntPtr.Zero) return;
AssignHandle(target);
}
protected void OnCompleted(Excel.Workbook xlBook)
{
if (Completed != null)
{
Completed(this, xlBook);
}
}
public Sheet(Excel.Worksheet _worksheet)
{
this.Base = _worksheet;
this.CurrentRow = 1;
this.CurrentCol = 1;
this.Valid = ReadValues();
}
public FrmImport(Excel.Application excelapp, CultureInfo culture)
: base(excelapp)
{
_excelapp = excelapp;
InitializeComponent();
_ctrlAddress = new[] { ctrlAddress1, ctrlAddress2, ctrlAddress3, ctrlAddress4, ctrlAddress5, ctrlAddress6 };
}
public FunctionOutput<string>[] FastReplace(Excel.Range com, DAG dag, InputSample original, InputSample sample, AST.Address[] outputs, bool replace_original)
{
FunctionOutput<string>[] fo_arr;
if (!_d.TryGetValue(sample, out fo_arr))
{
// replace the COM value
ReplaceExcelRange(com, sample);
// initialize array
fo_arr = new FunctionOutput<string>[outputs.Length];
// grab all outputs
for (var k = 0; k < outputs.Length; k++)
{
// save the output
fo_arr[k] = new FunctionOutput<string>(dag.readCOMValueAtAddress(outputs[k]), sample.GetExcludes());
}
// Add function values to cache
// Don't care about return value
_d.Add(sample, fo_arr);
// restore the COM value
if (replace_original)
{
ReplaceExcelRange(com, original);
}
}
return fo_arr;
}
// closes Excel
public static void CloseExcel(Excel.Application xlApp, Excel.Workbook wb)
{
// Close Excel
object misValue = System.Reflection.Missing.Value;
wb.Close(false, misValue, misValue);
xlApp.Quit();
}
public Workbook(Excel.Workbook wb, Excel.Application app, Action dispose_callback)
{
_app = app;
_wb = wb;
_wb_name = wb.Name;
_dispose_callback = dispose_callback;
}
private void ExportPOProjection(WIPItemNeed wipItemNeed, Excel.Worksheet xlSht, int row)
{
SortedDictionary<DateTime, float> dictList = new SortedDictionary<DateTime, float>(new IDateTimeDecreaseComparer());
Dictionary<int, float> dictProj = new Dictionary<int, float>();
UDFunction.ConvertStringToPromisedDate(POPromisedDateList(wipItemNeed), dictList);
int dateDiff = 0;
foreach (KeyValuePair<DateTime, float> kvp in dictList)
{
DateTime pDate = (DateTime)kvp.Key;
float pQty = (float)kvp.Value;
TimeSpan span = pDate.Subtract(DateTime.Today.AddDays(-1));
dateDiff = span.Days;
if (dateDiff < 0)
dateDiff = 0;
if (dateDiff > TOTALDAY)
dateDiff = TOTALDAY;
if (dictProj.ContainsKey(dateDiff))
dictProj[dateDiff] = dictProj[dateDiff] + pQty;
else
dictProj.Add(dateDiff, pQty);
}
foreach (KeyValuePair<int, float> kvp in dictProj)
{
xlSht.Cells[row, COL_STARTDAY + kvp.Key] = kvp.Value;
}
}
private static void GetStations(TrackLayout layout, Excel.Workbook book) {
Excel.Worksheet sheet = book.Worksheets["StationTrack"];
var r = 2;
Station current = null;
var loop = true;
while (loop) {
var row = (Array)sheet.get_Range(Cell("A", r), Cell("G", r)).Cells.Value;
if (row.GetValue(1, 1) == null) {
break;
}
else {
var rowType = row.Value(6);
switch (rowType) {
case "Station":
if (current != null) layout.Add(current);
current = new Station( row.Value(5), row.Value(1));
break;
case "Track":
current.Add(new StationTrack(row.Value(3)));
break;
default:
loop = false;
break;
}
r++;
}
}
if (current != null) layout.Add(current);
}
public override void InitModuleObjectRanges(InteropExcel.Workbook ActiveWorkbook, bool AddToWorkbook = false)
{
string formula = string.Empty;
formula = @"=INDIRECT(""_sqlstatements!$A$1:$J""&MAX(1;COUNTA(_sqlstatements!$A$1:$A$5000)))";
rngp_SqlStatements = new ObjectRange();
rngp_SqlStatements.InitRange(ActiveWorkbook, "_sqlstatements", "rngSqlstatements", formula, AddToWorkbook);
}
internal static void MergeExcels(Excel._Application app,
IEnumerable<string> sourcePaths,
string destinationPath)
{
var dstWb = app.Workbooks.Add("");
var srcWbs = sourcePaths.Select(sourcePath => app.Workbooks.Add(sourcePath));
// Don't start with i = 1 because trying to delete the last sheet makes an error.
for (var i = dstWb.Worksheets.Count; 2 <= i; i--)
{
dstWb.Worksheets[i].Delete();
}
// Keep the last sheet to be deleted when it is no longer the last sheet.
Excel.Worksheet ws1 = dstWb.Worksheets[1];
// app.Workbooks[1] is a destination so needs to be skipped.
for (var i = app.Workbooks.Count; 2 <= i; i--)
{
var srcWb = app.Workbooks[i];
for (var j = srcWb.Worksheets.Count; 1 <= j; j--)
{
srcWb.Worksheets[j].Copy(dstWb.Worksheets[1]);
}
}
ws1.Delete();
dstWb.SaveAs(destinationPath);
foreach (var srcWb in srcWbs)
{
srcWb.Close();
}
}
private void QuitExcel()
{
//WriteLog("Quit and clean up");
if (Book != null)
{
Book.Close(false, _missing, _missing);
Marshal.ReleaseComObject(Book);
Book = null;
}
if (Books != null)
{
Marshal.ReleaseComObject(Books);
Books = null;
}
if (Excel != null)
{
Excel.Quit();
Marshal.ReleaseComObject(Excel);
Excel = null;
}
//Garbage collection
GC.Collect();
}
private void MakeBoldText(Excel.Worksheet sheet, int firstCell, int lastCell, int row)
{
for (int i = firstCell; i < lastCell; i++)
{
mySheet.Cells[row, i].Font.Bold = true;
}
}
/// <summary>
/// Writes a set of project invocation rules to a worksheet.
/// </summary>
/// <param name="ws">The worksheet to which the rules are to be written.</param>
/// <param name="mode">The ExecutionMode to be written.</param>
/// <param name="rules">The project invocation rules to be written.</param>
public void Save(Excel.Worksheet ws, ExecutionMode mode, ProjectInvocationRule[] rules)
{
ws.Cells.ClearContents();
Helper.WriteCell(ws, 1, 1, mode.ToString());
var writer = new ProjectRuleDataWriter();
writer.Write(ws, rules, 2);
}
public CellsOperator(Excel.Range range, bool withNullCells = false, Content contentType = Content.Value)
{
if (range == null) return;
_withNullCells = withNullCells;
bool fullCol = range.EntireColumn.Address == range.Address;
if (fullCol && range.Worksheet.AutoFilter != null) {
var filterRange = range.Worksheet.AutoFilter.Range;
var intersect = range.Application.Intersect(range, filterRange.Offset[1, Type.Missing].Resize[filterRange.Rows.Count - 1, Type.Missing]);
if (intersect != null)
range = intersect;
} else if (withNullCells == false)
range = range.Application.Intersect(range, range.Worksheet.UsedRange);
if (range == null)
return;
_areas = new List<Area>(range.Areas.Count);
_nbCells = 0;
foreach (Excel.Range rgarea in range.Areas) {
var area = rgarea;
if (area.Worksheet.Cells.Address == area.Address)
area = rgarea.Resize[1000, 100];
if (area.EntireColumn.Address == area.Address)
area = rgarea.Resize[1000, Type.Missing];
else if (area.EntireRow.Address == area.Address)
area = rgarea.Resize[Type.Missing, 100];
var data = ExcelArray<object>.Get(area, contentType);
var nbrow = data.GetLength(0);
var nbcol = data.GetLength(1);
var hiddenrows = new bool[nbrow + 1];
var hiddencols = new bool[nbcol + 1];
_nbCells += nbrow * nbcol;
if (area.EntireRow.RowHeight is System.DBNull)
for (int r = 1; r <= nbrow; r++) {
if ((bool)((Excel.Range)area.Rows[r]).Hidden) {
hiddenrows[r] = true;
_nbCells -= nbcol;
}
}
if (area.EntireColumn.ColumnWidth is System.DBNull)
for (int c = 1; c <= nbcol; c++) {
if ((bool)((Excel.Range)area.Columns[c]).Hidden) {
hiddencols[c] = true;
_nbCells -= nbrow;
}
}
_areas.Add(new Area {
Data = data,
NbRow = nbrow, NbCol = nbcol,
HiddenRows = hiddenrows, HiddenCols = hiddencols,
Range = area
});
}
}
public Insert(Excel.Application application, CultureInfo culture)
{
try {
_excelapp = application;
_culture = culture;
_range = application.ActiveWindow.RangeSelection;
} catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); }
}
void Application_WorkbookActivate(Excel.Workbook Wb)
{
ModuleHandler.EnableModules(Globals.Factory, Globals.ThisAddIn.Application.ActiveWorkbook);
ModuleHandler.RunModuleEventHandler(Globals.Factory, Globals.ThisAddIn.Application.ActiveWorkbook,
ModuleEventHandler.WorkbookActivateHandler, null);
Globals.ThisAddIn.Application.ActiveWorkbook.SheetChange -= ActiveWorkbook_SheetChange;
Globals.ThisAddIn.Application.ActiveWorkbook.SheetChange += new Excel.WorkbookEvents_SheetChangeEventHandler(ActiveWorkbook_SheetChange);
}
public static Excel.Worksheet getWorkSheet(Excel.Workbook wb, int index)
{
if (index > 0 && index <= wb.Worksheets.Count)
{
return wb.Worksheets[index] as Excel.Worksheet;
}
return null;
}
public void WriteData()
{
string fp = ChooseFilePath(); // Uses function below to find file path
Excel excel = new Excel(fp, 1); // Creates a new instance of the Excel class
excel.WriteToCell(6, 6, "BOOM");
excel.SaveAs(@"BOOM3.xlsx"); //The @ symbol adds the file path This PC/ My Documents
excel.Close();
addListItem("File with new content added saved in ThisPC/My Documents/ BOOM3.xlsx"); //Message saying what has happened
}
private void button3_Click(object sender, EventArgs e)
{
try
{
Excel.ShowDialog();
ex.Workbooks.Open(Excel.FileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ex.Sheets[1];
textBox1.Text = Excel.FileName.ToString();
}
catch
{
}
}
private bool SaveReport()
{
Console.WriteLine("Save Report...");
try
{
bool allPass = IsAllPass();
string resultStr = allPass ? "(Pass)" : "(Fail)";
string savePathFile = ExcelFileDirectory + @"\" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + resultStr + ".xlsx";
Wb.SaveAs(savePathFile);
Wb.Close(false, Type.Missing, Type.Missing);
Excel.Quit();
MoveReportToPreFolder(savePathFile);
}
catch (Exception ex)
{
Console.WriteLine("儲存檔案出錯，檔案可能正在使用" + Environment.NewLine + ex.Message);
return(false);
}
return(true);
}
public void OpenFile(int sheet)
{
Excel excel = new Excel("C:\\Users\\320051\\Desktop\\Copy_EAM_WIP_v7.xlsx", sheet);
MessageBox.Show(excel.ReadCell(1, 0), excel.LastRow().ToString());
}
