public void readExcel()
{
string valueString = string.Empty;
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
//Open the workbook containing the address data.
objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Select the range of data containing the addresses and get the outer boundaries.
rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
long lLastRow = rngLast.Row;
long lLastCol = rngLast.Column;
// Iterate through the data and concatenate the values into a comma-delimited string.
for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++)
{
for (long colCounter = 1; colCounter <= lLastCol; colCounter++)
{
//Write the next value into the string.
Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter];
string cellvalue = cell.Value.ToString();
//TODO: add your business logic for retrieve cell value
}
}
}
private Worksheet objExcelWorkSheet; //定义Workbook工作表对象
#endregion Fields
#region Methods
// Change the Mapping WorkFlow @2015-7-16 By CC
public void EditWorkFlow(string StrTestData)
{
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objExcelWorkBooks = objExcelApp.Workbooks;
objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["WorkFlow"]; //strSheetName is the Sheet Name of Exce，if there is no name then Defult is "1"
objExcelWorkSheet.Select(Type.Missing);
int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //get the Row Number
int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//get the column number
int rowNo = 0;
int ParameterRowNo = 0;
int columnNo = 0;
int FlagColumnNo = 0;
for (int j = 1; j <= columnsint; j++)
{
string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, j]).Text.ToString();
if (strColumnsName == "Flag")
{
FlagColumnNo = j;
}
if (strColumnsName == "BusinessFlow")
{
rowNo = j;
}
if (strColumnsName == "ParameterValue")
{
ParameterRowNo = j;
}
}
for (int i = 2; i <= rowsint; i++)
{
string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, FlagColumnNo]).Text.ToString();
if (strFlagValue =="Y")
{
int RowNO = i;
objExcelWorkSheet.Cells[RowNO, FlagColumnNo] = "N";
}
string strBusinessFlow = ((Range)objExcelWorkSheet.Cells[i, rowNo]).Text.ToString();
if (strBusinessFlow == "HeaderMapping" || strBusinessFlow == "TaxMapping" || strBusinessFlow == "LineItemsMapping")
{
columnNo = i;
string strParameterValue = ((Range)objExcelWorkSheet.Cells[i, ParameterRowNo]).Text.ToString();
if (strParameterValue == "IR" || strParameterValue == "TradeBilling" || strParameterValue == "TradeCredit" || strParameterValue == "TradeDebit" || strParameterValue == "TradeReturn")
{
objExcelWorkSheet.Cells[i, 1] = "Y";
}
}
}
objExcelWorkbook.Save();
objExcelWorkbook.Close(false, StrTestData, false);
objExcelApp.Quit();
NAR(objExcelApp);
NAR(objExcelWorkbook);
NAR(objExcelWorkSheet);
}
private void OpenExcel()
{
myExcelApp = new Excel.Application();
myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
String fileName = Application.StartupPath + "\\Data\\pttform.xls";;
myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
//HandleRef hwnd = new HandleRef(myExcelApp, (IntPtr)myExcelApp.Hwnd);
//GetWindowThreadProcessId(hwnd, out pid);
}
public override void openExcel(string file)
{
if (File.Exists(file))
{
application = new excel.Application();
application.DisplayAlerts = false;
workBooks = application.Workbooks;
workBook = workBooks.Open(file, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
sheets = workBook.Sheets;
sheet = sheets.get_Item(1);
}
}
private void button3_Click(object sender, EventArgs e)
{
txtLibro.Text = @"V:\Varios\Listas OFAC\PEPS-Subir\LISTA PEPS - CNBS--YA";
// @"V:\Varios\Listas OFAC\PEPS-Subir\Listas PEPS - Periodo 2010 al 2018--YA.xls";
txtHoja.Text = "Hoja1";
var excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbooks books = excelApp.Workbooks;
Excel.Workbook hoja = books.Open(txtLibro.Text);
}
public void OpenExcel(string excelFileName, string sheetName)
{
mHasException = false;
try
{
var annihilationList = new List <object>();
Excel.Application xlApp = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
Excel.Sheets xlWorkSheets = null;
xlApp = new Excel.Application();
annihilationList.Add(xlApp);
xlApp.DisplayAlerts = false;
xlWorkBooks = xlApp.Workbooks;
annihilationList.Add(xlWorkBooks);
xlWorkBook = xlWorkBooks.Open(excelFileName);
annihilationList.Add(xlWorkBook);
xlApp.Visible = false;
xlWorkSheets = xlWorkBook.Sheets;
annihilationList.Add(xlWorkSheets);
for (var index = 1; index <= xlWorkSheets.Count; index++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkSheets[index];
annihilationList.Add(xlWorkSheet);
if (xlWorkSheet.Name == sheetName)
{
break;
}
annihilationList.Add(xlWorkSheet);
}
xlWorkBook.Close();
xlApp.UserControl = true;
xlApp.Quit();
ReleaseObjects(annihilationList);
}
catch (Exception ex)
{
mHasException = true;
mLastException = ex;
}
}
private void openDocument()
{
Debug.WriteLine("openDocument", GetType().Name);
var fname = System.IO.Path.Combine(workingDir, simFile);
//Excel works better with an absolute path.
string speadsheet = System.IO.Path.GetFullPath(fname);
Excel.Workbooks workbooks = o_xlApp.Workbooks;
Debug.WriteLine("workbooks.Open", GetType().Name);
o_xlWorkbook = workbooks.Open(speadsheet, 0, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, 0, true, 1, 0);
Debug.WriteLine("Marshal.ReleaseComObject: workbooks", GetType().Name);
Marshal.ReleaseComObject(workbooks);
}
public void mofileexcelvualuu()
{
if (duongdanfileexcel != null)
{
var app = new excel.Application();
excel.Workbooks book = app.Workbooks;
excel.Workbook sh = book.Open(duongdanfileexcel);
app.Visible = true;
sh.PrintOutEx();
}
}
private static void ExtractDataFromExcel()
{
Excel.Application excelApp = null;
Excel.Workbooks workBooks = null;
Excel.Workbook workBook = null;
Excel.Sheets workSheets = null;
Excel.Worksheet workSheet = null;
try
{
OpenFile("Excel files(*.xls*)|*.xls*");
if (!string.IsNullOrWhiteSpace(FileName))
{
excelApp = new Excel.Application
{
Visible = false,
ScreenUpdating = false,
EnableEvents = false
};
workBooks = excelApp.Workbooks;
workBook = workBooks.Open(FileName);
workSheets = workBook.Worksheets;
workSheet = (Excel.Worksheet)workSheets.get_Item(1);
ReadFromRow(workSheet);
MessageBox.Show("Extract successful!", "Congratulation!", MessageBoxButton.OK);
}
}
catch (FileNotFoundException)
{
MessageBox.Show("File not found!", "Error!", MessageBoxButton.OK);
}
catch (Exception ex)
{
MessageBox.Show($"Work is failed.\n{ex.Message}\n", "Error!", MessageBoxButton.OK);
}
finally
{
if (FileName != null)
{
workBook.Close();
excelApp.Quit();
FileName = null;
Marshal.ReleaseComObject(workSheet);
Marshal.ReleaseComObject(workSheets);
Marshal.ReleaseComObject(workBook);
Marshal.ReleaseComObject(workBooks);
Marshal.ReleaseComObject(excelApp);
}
}
}
public bool MonthlyLog()
{
if (!File.Exists(file))
{
MessageBox.Show("Database can not be found");
return(false);
}
if (File.Exists(file))
{
Excel.Application xlApp = new Excel.Application();
var date = DateTime.Now;
var datex = $"{date.Day}_{date.Month}_{ date.Year}_{date.Minute}_{date.Hour}";
var newbook = $@"C:\Users\kyle.marshall\Documents\Projects\Inventory\Monthly_Log_{datex}.xlsx";
Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
Excel.Workbook xlWorkBook = xlWorkBooks.Open(file);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(4);
object misValue = System.Reflection.Missing.Value;
xlWorkSheet.Copy(misValue, misValue);
var x = Task.Run(async delegate
{
await Task.Delay(5000);
});
x.Wait();
xlApp.Workbooks[1].SaveAs(Filename: newbook);
var t = Task.Run(async delegate
{
await Task.Delay(5000);
});
t.Wait();
foreach (Excel.Workbook book in xlWorkBooks)
{
foreach (Excel.Worksheet sheet in book.Sheets)
{
Marshal.ReleaseComObject(sheet);
}
book.Close(false);
Marshal.ReleaseComObject(book);
}
xlWorkBooks.Close();
Marshal.ReleaseComObject(xlWorkBooks);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
GC.Collect();
return(true);
}
return(false);
}
private void openExcelTemplate()
{
createExcelResult();
if (File.Exists(excelResult))
{
application = new excel.Application();
application.DisplayAlerts = false;
workBooks = application.Workbooks;
workBook = workBooks.Open(excelResult, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
sheets = workBook.Sheets;
sheet = sheets.get_Item(1);
excelUtils.setSheet(sheet);
}
}
/// <summary>
/// Opens the MinMax Document.
/// </summary>
public void Open()
{
try
{
if (Process.GetProcessesByName("EXCEL").Count() > 0)
{
// Try to get an open instance of Excel.
try
{
myApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
Log.WriteLine("Instance of Excel Found");
}
catch (COMException e)
{
Console.WriteLine("No Instance of Excel Found:\n" + e.Message);
}
}
else
{
try
{
// Try to Create new instance of Excel.
myApp = new Excel.Application();
Log.WriteLine("New Instance of Excel Created.");
}
catch (Exception)
{
Log.WriteLine("Cannot Access File on network, try Again.");
}
}
myApp.Visible = true; // True to see new instance, false to hide
// Set the objects to corresponding excel objects
myBooks = myApp.Workbooks;
myBook = myBooks.Open(minMaxPath);
mySheet = myBook.Sheets["Marlin Steel"];
setRange();
// All excel objects are referenced
excelObjSet = true;
Log.WriteLine("Min-Max Document Opened.");
}
catch
{
Log.WriteLine("Cannot Access Min-Max Document.");
}
}
// private Excel.Worksheet sheet;
public override void Convert(String inputFile, String outputFile)
{
Object nothing = Type.Missing;
try
{
if (!File.Exists(inputFile))
{
throw new ConvertException("File not Exists");
}
if (IsPasswordProtected(inputFile))
{
throw new ConvertException("Password Exist");
}
app = new Excel.Application();
books = app.Workbooks;
book = books.Open(inputFile, false, true, nothing, nothing, nothing, true, nothing, nothing, false, false, nothing, false, nothing, false);
bool hasContent = false;
foreach (Excel.Worksheet sheet in book.Worksheets)
{
Excel.Range range = sheet.UsedRange;
if (range != null)
{
Excel.Range found = range.Cells.Find("*", nothing, nothing, nothing, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, nothing, nothing, nothing);
if (found != null)
{
hasContent = true;
}
releaseCOMObject(found);
releaseCOMObject(range);
}
}
if (!hasContent)
{
throw new ConvertException("No Content");
}
book.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, outputFile, Excel.XlFixedFormatQuality.xlQualityMinimum, false, false, nothing, nothing, false, nothing);
}
catch (Exception e)
{
release();
throw new ConvertException(e.Message);
}
release();
}
public XL(string path, bool showUi)
{
mPath = path;
//Start Excel and get Application object.
mApplication = new Excel.Application();
mApplication.Visible = true;
mWorkbooks = mApplication.Workbooks;
mWorkbook = mWorkbooks.Open(path);
mActiveSheet = mWorkbook.ActiveSheet;
mWorksheets = mWorkbook.Worksheets;
mApplication.Visible = showUi;
mApplication.UserControl = showUi;
}
public ExcelApplication(String argosFile)
{
someFile = argosFile;
helloWorld = "Hello World";
mainApp = new Excel.Application();
mainAppWorkbooks = mainApp.Workbooks;
// opening workbook causes leak
argosWorkbook = mainAppWorkbooks.Open(argosFile, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
argosWorksheets = argosWorkbook.Worksheets;
aSheet = argosWorksheets.get_Item(1);
aRange = aSheet.UsedRange;
argosRowCount = aRange.Rows.Count;
argosColumnCount = aRange.Columns.Count;
}
public void openexcel()
{
xlapp = new xl.Application();
workbooks = xlapp.Workbooks;
workbook = workbooks.Open(xlFilepath);
//storing workshetnames in hashtable
int count = 1;
foreach (xl.Worksheet sheet in workbook.Sheets)
{
sheets[count] = sheet.Name;
count++;
}
}
void InitialExcelSetting()
{
if (app == null)
{
Console.WriteLine("Excel无法启动");
return;
}
app.Visible = true;
wbs = app.Workbooks;
//wb = wbs.Add(Missing.Value);
string templatePath = Directory.GetCurrentDirectory() + "\\LoadComparisonTemplate.xlsx";
wb = wbs.Open(templatePath);
}
public void Load(string file)
{
if (string.IsNullOrEmpty(file))
{
throw new SystemException("Unexpected! {DFDE83F4-CB29-4F08-A311-DBA404C844D0}");
}
if (m_app != null)
{
throw new SystemException("Unexpected! {5E4F6BD1-B5B6-418F-BBE7-88E119417E88}");
}
m_app = new Excel.Application();
m_workbooks = m_app.Workbooks;
m_workbook = m_workbooks.Open(file);
}
public void taovainfileexcel(DataTable dt, string tongma, string ngaybatdau, string ngayketthuc)
{
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet worksheet = ExcelPkg.Workbook.Worksheets.Add("hts");
worksheet.Cells["A1:C1"].Merge = true;
worksheet.Cells["A2:C2"].Merge = true;
worksheet.Cells["A3:C3"].Merge = true;
worksheet.Cells["A1"].Value = "Danh mục VM _ Mã chưa trưng";
worksheet.Cells["A2"].Value = "Từ ngày: " + ngaybatdau + " - " + ngayketthuc;
worksheet.Cells["A3"].Value = "Tổng mã chưa trưng: " + tongma;
worksheet.Cells["A5"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light1);
worksheet.Column(1).Width = 10;
worksheet.Column(2).Width = 13;
worksheet.Column(3).Width = 10;
//worksheet.Cells[worksheet.Dimension.End.Row + 1, 1].Value = "Tổng sản phẩm:";
//worksheet.Cells[worksheet.Dimension.End.Row, 2].Value = tongsp;
var allCells = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column];
var cellFont = allCells.Style.Font;
cellFont.SetFromFont(new Font("Calibri", 10));
worksheet.PrinterSettings.LeftMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.RightMargin = 0.2M / 2.54M;
worksheet.PrinterSettings.TopMargin = 0.2M / 2.54M;
worksheet.Protection.IsProtected = false;
worksheet.Protection.AllowSelectLockedCells = false;
if (File.Exists("hts.xlsx"))
{
File.Delete("hts.xlsx");
}
ExcelPkg.SaveAs(new FileInfo("hts.xlsx"));
ExcelPkg.Dispose();
var app = new excel.Application();
excel.Workbooks book = app.Workbooks;
excel.Workbook sh = book.Open(Path.GetFullPath("hts.xlsx"));
//app.Visible = true;
sh.PrintOutEx();
app.Quit();
Marshal.FinalReleaseComObject(app);
Marshal.FinalReleaseComObject(book);
}
public void OpenExcel(string FileName, string SheetName)
{
bool Proceed = false;
Excel.Application xlApp = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;
Excel.Sheets xlWorkSheets = null;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Open(FileName);
xlApp.Visible = false;
xlWorkSheets = xlWorkBook.Sheets;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
if (xlWorkSheet.Name == SheetName)
{
Proceed = true;
break;
}
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
xlWorkSheet = null;
}
xlWorkBook.Close();
xlApp.Quit();
ReleaseComObject(xlWorkSheets);
ReleaseComObject(xlWorkSheet);
ReleaseComObject(xlWorkBook);
ReleaseComObject(xlWorkBooks);
ReleaseComObject(xlApp);
if (Proceed)
{
MessageBox.Show("Found sheet, do your work here.");
}
else
{
MessageBox.Show("Sheet not located");
}
}
private string GetModifyDateExcel(string fullPath)
{
// Require both wkbs and wkb
// If these vars are not declared, Excel instances do not close properly
Excel.Application xlApp;
Excel.Workbooks xlWkbs = null;
Excel.Workbook xlWkb = null;
try
{
xlApp = new Excel.Application()
{
DisplayAlerts = false, Visible = false
};
}
catch
{
return(appInstanceFail);
}
try
{
xlWkbs = xlApp.Workbooks;
xlWkb = xlWkbs.Open(fullPath, false, true);
return(xlWkb.BuiltinDocumentProperties("Last Save Time").Value.ToString() != "" ? xlWkb.BuiltinDocumentProperties("Last Save Time").Value.ToString() : blank);
}
catch
{
return(cannotOpen);
}
finally
{
if (xlWkb != null)
{
xlWkb.Close(false);
Marshal.ReleaseComObject(xlWkb);
}
if (xlWkbs != null)
{
Marshal.ReleaseComObject(xlWkbs);
}
if (xlApp != null)
{
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
}
}
private void prikazDijagramaClick()
{
var parent = System.IO.Directory.GetParent(Environment.CurrentDirectory);
var path = parent.Parent.FullName + "\\XLSX\\" + "\\" + "PodaciKrive.xls";
string mySheet = path;
var excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbooks books = excelApp.Workbooks;
Excel.Workbook sheet = books.Open(mySheet);
}
public void OpenExcel()
{
xlApp = new xl.Application();
workbooks = xlApp.Workbooks;
workbook = workbooks.Open(xlFilePath);
sheets = new Hashtable();
int count = 1;
// Storing worksheet names in Hashtable.
foreach (xl.Worksheet sheet in workbook.Sheets)
{
sheets[count] = sheet.Name;
count++;
}
}
public static void ShowImportTemplete(string TempleteName)
{
try
{
string filename = clsLxms.GetParameterValue("EXCEL_TEMPLETE_FILE");
if (!File.Exists(filename))
{
MessageBox.Show(clsTranslate.TranslateString("Can not find the templete file") + ": \r\n" + filename, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
string destfile = @Path.GetTempPath() + TempleteName + ".xls";
Util.retValue1 = destfile;
File.Copy(filename, destfile, true);
Excel.Application m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
Excel.Workbooks m_objBooks = m_objExcel.Workbooks;
m_objBooks.Open(destfile, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Workbook m_objBook = (Excel.Workbook)m_objBooks.get_Item(1);
Excel.Sheets sm_objSheets = (Excel.Sheets)m_objBook.Worksheets;
Excel.Worksheet m_objSheet = (Excel.Worksheet)sm_objSheets.get_Item(TempleteName);
for (int i = sm_objSheets.Count; i >= 1; i--)
{
Excel.Worksheet m_Sheet = sm_objSheets.get_Item(i);
if (m_Sheet.Name.ToLower().CompareTo(TempleteName.ToLower()) != 0)
{
m_Sheet.Delete();
}
}
m_objSheet.Name = "Sheet1";
//m_objSheet.Activate();
System.Windows.Forms.Application.DoEvents();
m_objExcel.DisplayAlerts = false;
m_objExcel.AlertBeforeOverwriting = false;
//保存工作簿
m_objExcel.Visible = true;
return;
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
public void Open(string FileName)//打开一个Excel文件
{
if (app == null)
{
app = new Excel.Application();
}
//app.WindowState = Excel.XlWindowState.xlMinimized;
//if (wb!=null) wb.Close();
wb = null;
wbs = null;
wbs = app.Workbooks;
wb = wbs.Open(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlPlatform.xlWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ws = GetSheet("单原料求解");
ws.Unprotect();
mFilename = FileName;
}
public void openExcel()
{
myExcelApplication = null;
myExcelApplication = new Excel.Application(); // create Excell App
myExcelApplication.DisplayAlerts = false; // turn off alerts
workbooks = myExcelApplication.Workbooks;
myExcelWorkbook = workbooks.Open(excelFilePath, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing); // open the existing excel file
myExcelWorkSheet = myExcelWorkbook.Worksheets[1]; // define in which worksheet, do you want to add data
myExcelWorkSheet.Name = "WorkSheet 1"; // define a name for the worksheet (optinal)
}
private void openExcelFile()
{
if (File.Exists(headersFile))
{
application = new excel.Application();
application.DisplayAlerts = false;
workBooks = application.Workbooks;
workBook = workBooks.Open(headersFile, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
sheets = workBook.Sheets;
}
else
{
em.showErrorFileNotExist(headersFile);
System.Environment.Exit(0);
}
}
static void ForLoopReleaseCOM()
{
Excel.Application app = null;
Excel.Workbooks books = null;
Excel.Workbook book = null;
Excel.Sheets sheets = null;
try
{
app = new Excel.Application();
books = app.Workbooks;
book = books.Open(@"C:\Temp\ExcelBook1Sheets.xlsx");
sheets = book.Sheets;
for (int i = 1; i <= sheets.Count; i++)
{
Excel.Worksheet sheet = sheets.Item[i];
Console.WriteLine(sheet.Name);
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
}
Console.Read();
book.Close();
app.Quit();
}
finally
{
if (sheets != null)
{
Marshal.ReleaseComObject(sheets);
}
if (book != null)
{
Marshal.ReleaseComObject(book);
}
if (books != null)
{
Marshal.ReleaseComObject(books);
}
if (app != null)
{
Marshal.ReleaseComObject(app);
}
}
}
private void btnGenerarExcel_Click(object sender, EventArgs e)
{
ExportExcel exportExcel = new ExportExcel();
string excelFilename = "Informe";
this.spmostrar_ReporteTableAdapter.Rows.Clear();
var dtInforme = ToDataTable(Conexion.MostrarDatos(this.spmostrar_ReporteTableAdapter));
exportExcel.Export_Ctr_Excel(dtInforme, excelFilename);
//Abrir el documento de excel
string mySheet = @"C:\Users\ibrah\Documents\Materias\Octavo Cuatrimestre\Base de Datos\" + excelFilename + ".xlsx";
var excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbooks books = excelApp.Workbooks;
Excel.Workbook sheet = books.Open(mySheet);
}
private void OpenWorkbook()
{
_application = new Excel.Application()
{
DisplayAlerts = false
};
_application.Application.Interactive = true;
_application.Application.UserControl = true;
_application.Visible = false;
_workbooks = _application.Workbooks;
_workbook = _workbooks.Open(FilePath,
Type.Missing, this._ReadOnly, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);;
}
private void merge(Excel.Workbooks Workbooks, Excel.Worksheet desWorksheet, List <String> files)
{
for (int i = 0; i < files.Count; i++)
{
Excel.Workbook wb = Workbooks.Open(files[i]);
if (i == 0)
{
wb.Sheets[1].Range["a1"].CurrentRegion.Copy(desWorksheet.Cells[1, 1]);
}
else
{
int row = desWorksheet.Range["a1048576"].End[Excel.XlDirection.xlUp].Row;
wb.Sheets[1].Range["a1"].CurrentRegion.Offset(1).Copy(desWorksheet.Cells[row + 1, 1]);
}
wb.Close();
}
}
//◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
/// <summary>
/// シート数取得
/// ※注意※ これを実行するとプロセスが残ってしまう。
/// </summary>
/// <param name="F_P_N"></param>
/// <returns></returns>
//◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
public int Sheet_figure(string F_P_N)
{
Excel.Application Ap = null;
Excel.Workbooks Bk = null;
//Excel.Worksheet St = null;
//Excel起動
Ap = new Excel.Application();
//Excel表示
Ap.Visible = false;
Ap.DisplayAlerts = false;
//Excelブック取得
Bk = Ap.Workbooks;
Bk.Open(F_P_N, // 指定ファイルPath
Type.Missing, // （省略可能）UpdateLinks (0 / 1 / 2 / 3)
false, // （省略可能）ReadOnly (True / False )
Type.Missing, // （省略可能）Format
// 1:タブ / 2:カンマ (,) / 3:スペース / 4:セミコロン (;)
// 5:なし / 6:引数 Delimiterで指定された文字
Type.Missing, // （省略可能）Password
Type.Missing, // （省略可能）WriteResPassword
Type.Missing, // （省略可能）IgnoreReadOnlyRecommended
Type.Missing, // （省略可能）Origin
Type.Missing, // （省略可能）Delimiter
Type.Missing, // （省略可能）Editable
Type.Missing, // （省略可能）Notify
Type.Missing, // （省略可能）Converter
Type.Missing, // （省略可能）AddToMru
Type.Missing, // （省略可能）Local
Type.Missing // （省略可能）CorruptLoad
);
int figure = Ap.Worksheets.Count;
//■Excelファイルを閉じる
Bk.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(Bk);
Ap.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(Ap);
//プロセス強制終了(プロセスが残ってしまった時の為 ※但しなぜか２度目以降は効かない。)
//System.Diagnostics.Process[] ps = System.Diagnostics.Process.GetProcessesByName("EXCEL.EXE");
//foreach (System.Diagnostics.Process p in ps) p.Kill();
return(figure);
}
// private Excel.Worksheet sheet;
public override void Convert(String inputFile, String outputFile)
{
Object nothing = Type.Missing;
try
{
if (!File.Exists(inputFile))
{
throw new ConvertException("File not Exists");
}
if (IsPasswordProtected(inputFile))
{
throw new ConvertException("Password Exist");
}
app = new Excel.Application();
books = app.Workbooks;
book = books.Open(inputFile, false, true, nothing, nothing, nothing, true, nothing, nothing, false, false, nothing, false, nothing, false);
bool hasContent = false;
foreach (Excel.Worksheet sheet in book.Worksheets)
{
Excel.Range range = sheet.UsedRange;
if (range != null) {
Excel.Range found = range.Cells.Find("*", nothing, nothing, nothing, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, nothing, nothing, nothing);
if (found != null) hasContent = true;
releaseCOMObject(found);
releaseCOMObject(range);
}
}
if (!hasContent) throw new ConvertException("No Content");
book.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, outputFile, Excel.XlFixedFormatQuality.xlQualityMinimum, false, false, nothing, nothing, false, nothing);
}
catch (Exception e)
{
release();
throw new ConvertException(e.Message);
}
release();
}
public void Open(string strFilePath)
{
_strFilePath = strFilePath;
_application = new Microsoft.Office.Interop.Excel.Application();
_workbooks = _application.Workbooks;
_workbook = _workbooks.Open(_strFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
_worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets[1];
}
// Get The RunTime For First Run @2015-7-16 by CC
public List<String> GetRunTimeForRun(string StrTestData)
{
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objExcelWorkBooks = objExcelApp.Workbooks;
objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Open the Sheet
objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Global"]; //Exce工作簿的Sheet
objExcelWorkSheet.Select(Type.Missing);
//取得总记录行数 (包括标题列)
int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//得到列数
string temp1 = ((Range)objExcelWorkSheet.Cells[1, 1]).Text.ToString();
//遍历得到RunTime
int rowcolum = 0;
List<String> str = new List<String>();
for (int i = 1; i <= columnsint; i++)
{
string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, i]).Text.ToString();
if (strColumnsName == "RunTime")
{
rowcolum = i;//Get Column No
for (int j = 2; j <= rowsint; j++)
{
string StrRunTimeValue = ((Range)objExcelWorkSheet.Cells[j, i]).Text.ToString();
str.Add(StrRunTimeValue);
}
}
}
objExcelWorkbook.Close(false, StrTestData, false);
objExcelApp.Quit();
NAR(objExcelApp);
NAR(objExcelWorkbook);
NAR(objExcelWorkSheet);
return str;
}
private Excel.Workbook GetActiveWorkbook()
{
oWorkbooks = this.ExcelApp.Workbooks;
localAppPath = Path.Combine(localAppPath, Path.GetFileNameWithoutExtension(@excelFileName));
if (Directory.Exists(path: localAppPath))
{ Directory.Delete(path: localAppPath, recursive: true); }
Directory.CreateDirectory(path: localAppPath);
return oWorkbooks.Open(@excelFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
// Insert Row in the Driver.xls
public void InsertRowsValues(string StrDriverPath, string StrCaseName, string StrTestDataPath, string StrComCode, string StrAsset, string StrRunTime)
{
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objExcelWorkBooks = objExcelApp.Workbooks;
objExcelWorkbook = objExcelWorkBooks.Open(StrDriverPath, 0, false, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Driver"]; //strSheetName是指的Exce工作簿的Sheet名，如果没有命名则为"1"
objExcelWorkSheet.Select(Type.Missing);
int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数
if (rowsint > 1)
{
for (int i = 2;i <= rowsint; i++)
{
string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, 1]).Text.ToString();
if (strFlagValue == "Y")
{
int RowNO = i;
objExcelWorkSheet.Cells[RowNO, 1] = "N";
}
}
}
if (rowsint == 1)
{
rowsint = 2;
}
objExcelWorkSheet.Cells[rowsint, 1] = "Y";
objExcelWorkSheet.Cells[rowsint, 2] = "fa_30_saptao_mec";
objExcelWorkSheet.Cells[rowsint, 3] = StrRunTime;
objExcelWorkSheet.Cells[rowsint, 4] = StrRunTime;
objExcelWorkSheet.Cells[rowsint, 5] = StrCaseName;
objExcelWorkSheet.Cells[rowsint, 6] = StrTestDataPath;
objExcelWorkSheet.Cells[rowsint, 7] = "Run";
objExcelWorkSheet.Cells[rowsint, 8] = StrComCode;
objExcelWorkSheet.Cells[rowsint, 9] = StrAsset;
objExcelWorkbook.Save();
objExcelWorkbook.Close(false, StrDriverPath, false);
objExcelApp.Quit();
NAR(objExcelApp);
NAR(objExcelWorkbook);
NAR(objExcelWorkSheet);
}
// Get the RunTime Values for ReRun & Mapping
public string GetRunTimeVlaue(string StrOutputPath)
{
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objExcelWorkBooks = objExcelApp.Workbooks;
objExcelWorkbook = objExcelWorkBooks.Open(StrOutputPath, 0, false, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Open the Sheet
objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Output"]; //Exce工作簿的Sheet
objExcelWorkSheet.Select(Type.Missing);
//取得总记录行数 (包括标题列)
int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//得到列数
string temp1 = ((Range)objExcelWorkSheet.Cells[1, 1]).Text.ToString();
//遍历得到CompanyCode和TestAsset、Runtime
int rowcolum = 0;
for (int i = 1; i <= columnsint; i++)
{
string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, i]).Text.ToString();
if (strColumnsName == "RunTime")
{
rowcolum= i;//得到Colum号
}
}
String RunTimeValues = ((Range)objExcelWorkSheet.Cells[rowsint,rowcolum]).Text.ToString();
objExcelWorkbook.Close(false, StrOutputPath, false);
objExcelApp.Quit();
NAR(objExcelApp);
NAR(objExcelWorkbook);
NAR(objExcelWorkSheet);
return RunTimeValues;
}
public List<String> GetExcelValuesList(string StrTestData,string StrRuntime)
{
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objExcelWorkBooks = objExcelApp.Workbooks;
objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Open the Sheet
objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Global"]; //Exce工作簿的Sheet
objExcelWorkSheet.Select(Type.Missing);
//取得总记录行数 (包括标题列)
int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//得到列数
string temp1 = ((Range)objExcelWorkSheet.Cells[1, 1]).Text.ToString();
//遍历得到CompanyCode和TestAsset
int rowcolum = 0;
int RowID = 0;
List<String> str = new List<String>();
List<String> strRowID = new List<String>();
for (int i = 1; i <= columnsint; i++)
{
string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, i]).Text.ToString();
if (strColumnsName == "RunTime")
{
rowcolum = i;//Get the columm no
for (int j = 1; j <= rowsint;j++ )
{
string strRowValue = ((Range)objExcelWorkSheet.Cells[j, i]).Text.ToString();
if(strRowValue == StrRuntime)
{
RowID = j;
strRowID.Add(RowID.ToString());
}
}
}
if (strColumnsName == "CompanyCode")
{
rowcolum = i;//得到Column号
string strRowIDNo = strRowID[0];
int RowNo = Convert.ToInt32(strRowIDNo);
String temp = ((Range)objExcelWorkSheet.Cells[RowNo, i]).Text.ToString();
str.Add(((Range)objExcelWorkSheet.Cells[RowNo, i]).Text.ToString());
}
if (strColumnsName == "TargetBoxName")
{
rowcolum = i;//得到行号
string strRowIDNo = strRowID[0];
int RowNo = Convert.ToInt32(strRowIDNo);
String tempAssent = ((Range)objExcelWorkSheet.Cells[RowNo, i]).Text.ToString();
// string Asset = tempAssent.Substring(tempAssent.Length - 3);
str.Add(tempAssent);
}
}
//if (strColumnsName == "CompanyCode")
//{
// rowcolum = i;//得到Column号
// String temp = ((Range)objExcelWorkSheet.Cells[2, i]).Text.ToString();
// str.Add(((Range)objExcelWorkSheet.Cells[2, i]).Text.ToString());
//}
//if (strColumnsName == "TargetBoxName")
//{
// rowcolum = i;//得到行号
// String tempAssent = ((Range)objExcelWorkSheet.Cells[2, i]).Text.ToString();
// string Asset = tempAssent.Substring(tempAssent.Length - 3);
// str.Add(Asset);
//}
objExcelWorkbook.Close(false, StrTestData, false);
objExcelApp.Quit();
NAR(objExcelApp);
NAR(objExcelWorkbook);
NAR(objExcelWorkSheet);
return str;
}
private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true)
{
Console.WriteLine("Open File:【{0}】", excelInfo.FilePath);
if (!File.Exists(excelInfo.FilePath))
{
return $"文件【{excelInfo.FilePath}】不存在";
}
_objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
_objBooks = _objExcel.Workbooks;
if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath))
{
_objBook = _objBooks.Add(ObjOpt);
}
else
{
_objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt,
ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt);
}
if (isOpenSheet)
{
_objSheet = OpenSheet(excelInfo);
if (_objSheet == null)
{
return "没有指定页签";
}
}
return "";
}
private void openWorkbook()
{
OpenFileDialog fileDialog = new OpenFileDialog();
string filepath;
fileDialog.Filter = "Excel|*.xls;*.xlsx|All Files|*.*";
fileDialog.Multiselect = false;
if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filepath = fileDialog.FileName;
m_xlWorkbooks = m_xlApp.Workbooks;
m_xlWorkbook = m_xlWorkbooks.Open(filepath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
m_xlSheets = m_xlWorkbook.Worksheets;
m_xlWorksheets = new Dictionary<string, Excel.Worksheet>();
foreach (Excel.Worksheet sheet in m_xlSheets)
{
m_xlWorksheets.Add(sheet.Name, sheet);
}
comboBoxWorksheetSelector.DataSource = m_xlWorksheets.Keys.ToList<string>();
labelExcelWorkbookName.Text = "\"" + Path.GetFileName(filepath) + "\"";
}
}
public List<AccountExcel> readExcel(string sExcelPath)
{
var lReturn = new List<AccountExcel>();
string valueString = string.Empty;
objExcelApp = new Microsoft.Office.Interop.Excel.Application();
objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
//Open the workbook containing the address data.
objBook = objBooks.Open(sExcelPath, Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
long lLastRow = rngLast.Row;
long lLastCol = rngLast.Column;
for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2
{
if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "")
{
var adAccount = new AccountExcel();
adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value);
adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value);
adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value);
adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value);
adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value);
adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value);
adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value);
adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value);
adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value);
adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value);
adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value);
adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value);
adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value);
lReturn.Add(adAccount);
}
}
//Close the Excel Object
objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
objBooks.Close();
objExcelApp.Quit();
Marshal.ReleaseComObject(objSheet);
Marshal.ReleaseComObject(objSheets);
Marshal.ReleaseComObject(objBooks);
Marshal.ReleaseComObject(objBook);
Marshal.ReleaseComObject(objExcelApp);
objSheet = null;
objSheets = null;
objBooks = null;
objBook = null;
objExcelApp = null;
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
return (lReturn);
}
public void Setup()
{
resourcePath = @"../../../../test.xlsx";
app = new Excel.Application();
var excelSheetPath = Path.GetFullPath(resourcePath);
wbs = app.Workbooks;
wb = wbs.Open(excelSheetPath);
wss = wb.Worksheets;
ws = (Excel.Worksheet)wss["Tasks"];
excelController = new ExcelController(resourcePath);
}
private void PrepareRecord()
{
objRecordExcel = new Excel.Application();
objRecordBooks = objRecordExcel.Workbooks;
if (File.Exists(txtStorage.Text))
objRecordBook = objRecordBooks.Open(txtStorage.Text);
else
objRecordBook = objRecordBooks.Add(true);
objRecordSheets = objRecordBook.Sheets;
objRecordSheet1 = objRecordSheets[1]; // 收件箱
objRecordSheet2 = objRecordSheets[1]; // 已发短信
bool bSheet1Exists = false, bSheet2Exists = false;
foreach (Excel.Worksheet objSheet in objRecordSheets)
{
if (objSheet.Name == "收件箱")
{
objRecordSheet1 = objSheet;
bSheet1Exists = true;
}
else if (objSheet.Name == "已发短信")
{
objRecordSheet2 = objSheet;
bSheet2Exists = true;
}
}
if (!bSheet1Exists)
{
objRecordSheet1 = objRecordSheets.Add();
objRecordSheet1.Name = "收件箱";
objRecordSheet1.Cells[1, 1] = "发件人";
objRecordSheet1.Cells[1, 2] = "短信内容";
objRecordSheet1.Cells[1, 3] = "发送时间";
}
if (!bSheet2Exists)
{
objRecordSheet2 = objRecordSheets.Add();
objRecordSheet2.Name = "已发短信";
objRecordSheet2.Cells[1, 1] = "收件人";
objRecordSheet2.Cells[1, 2] = "短信内容";
objRecordSheet2.Cells[1, 3] = "发送时间";
objRecordSheet2.Cells[1, 4] = "结果";
objRecordSheet2.Cells[1, 5] = "余额";
}
}
private void OpenExcel()
{
myExcelApp = new Excel.Application();
myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
String fileName = Application.StartupPath + "\\Data\\pttform.xls"; ;
myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
//HandleRef hwnd = new HandleRef(myExcelApp, (IntPtr)myExcelApp.Hwnd);
//GetWindowThreadProcessId(hwnd, out pid);
}