public static int ObtenerIndiceHojaExcel(Excel.Sheets hojasExcel, string nombreBusqueda)
{
foreach (Excel.Worksheet hoja in hojasExcel)
{
if (hoja.Name.Equals(nombreBusqueda))
{
return(hoja.Index);
}
}
return(-1);
}
private void createExcel()
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;
try
{
FileInfo file = new FileInfo(fileLoc);
if (file.Exists)
{
excelApp = new Excel.Application();
workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
sheets = workbook.Sheets;
//check columns exist
foreach (Excel.Worksheet sheet in sheets)
{
Console.WriteLine(sheet.Name);
sheet.Select(Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = "My New Sheet";
newSheet.Cells[1, 1] = "BOO!";
workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
}
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
newSheet = null;
sheets = null;
workbook = null;
excelApp = null;
GC.Collect();
}
}
private void Dispose(bool disposing)
{
if (disposing)
{
// Dispose managed resources.
Marshal.FinalReleaseComObject(m_objExcel);
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBooks = null;
m_objBook = null;
m_objExcel = null;
}
}
/// <summary>
/// 新建一个Excel实例
/// </summary>
/// <param name="strTitle">Excel表头上的文字</param>
public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle)
{
m_objExcel = new Excel.ApplicationClass();
m_objExcel.Visible = true;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
if (intSheetTotalSize <= 3)
{
if (this.dbTotalSize <= this.dbSheetSize)
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
return;
}
else if (this.dbTotalSize <= this.dbSheetSize * 2)
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
return;
}
else
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle);
return;
}
}
for (int i = 3; i < intSheetTotalSize; i++)
{
m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
}
ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
for (int i = 2; i <= m_objSheets.Count; i++)
{
ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle);
}
}
private void OpenTemplate()
{
try
{
InvoiceTemplate = new Excel.ApplicationClass();
InvoiceTemplate.Visible = false;
//string workbookPath = m_TemplatePath;
excelWorkbook = InvoiceTemplate.Workbooks.Open(m_TemplatePath,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
InvoiceSheets = InvoiceTemplate.Worksheets;
string currentSheet = "Page1";
if (m_iInvoiceType == (int)InvoiceType.ShippingList)
{
currentSheet = "Page2";
}
excelWorksheet = (Excel.Worksheet)InvoiceSheets.get_Item(currentSheet);
}
catch (Exception ex)
{
throw(ex);
}
}
static void Main(string[] args)
{
Console.WriteLine("Interop Performance Test - 10.000 Cells.");
/*
* start excel and disable messageboxes and screen updating
*/
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.ScreenUpdating = false;
//excelApplication.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);
/*
* create new empty worksheet
*/
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook book = books.Add(Missing.Value);
Excel.Sheets sheets = book.Worksheets;
Excel.Worksheet sheet = (Excel.Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
/*
* do the test
* we collect all references and release after time recording
* the 2 latebind libs release the references at end automaticly and we want a fair test
*/
List <object> comReferenesList = new List <object>();
DateTime timeStart = DateTime.Now;
for (int i = 1; i <= 10000; i++)
{
string rangeAdress = "$A" + i.ToString();
Range cellRange = (Range)sheet.Range[rangeAdress];
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = false;
Comment sampleComment = cellRange.AddComment("Sample Comment");
comReferenesList.Add(cellRange);
comReferenesList.Add(sampleComment);
}
DateTime timeEnd = DateTime.Now;
TimeSpan timeElapsed = timeEnd - timeStart;
foreach (var item in comReferenesList)
{
Marshal.ReleaseComObject(item);
}
/*
* display for user
*/
string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);
Console.WriteLine(outputConsole);
Console.Read();
/*
* write result in logfile
*/
string logFile = Path.Combine(Environment.CurrentDirectory, "Interop.log");
string logFileAppend = timeElapsed.ToString() + Environment.NewLine;
File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);
/*
* release & quit
*/
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
}
public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(filePath));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex));
_excelSheet.Name = sheetName;
}
public void OpenExcelApp(bool visible)
{
_excelApplication = new Excel.Application();
_excelApplication.Visible = visible;
_workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
_workBook = (Excel._Workbook)(_workBooks.Add(_value));
_excelSheets = (Excel.Sheets)_workBook.Worksheets;
_excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1));
}
public static string ProcesarArchivo(string pathArchivo)
{
string msgResul = "Migración realizada correctamente.";
ImportadorExcelBE infoImportar = new ImportadorExcelBE();
UIUtility.COMObjectAplication ObjCom = null;
Excel.Application xlApp = null;
try
{
ObjCom = new UIUtility.COMObjectAplication("Excel.Application", "EXCEL");
xlApp = (Excel.Application)ObjCom.ObjetoAplication;
Excel.Workbooks oBooks = xlApp.Workbooks;
oBooks.Open(pathArchivo, ReadOnly: true, IgnoreReadOnlyRecommended: true);
Excel.Workbook xlLibro = oBooks.Item[1];
Excel.Sheets oSheets = xlLibro.Worksheets;
Excel.Worksheet hojaConfig = (Excel.Worksheet)oSheets.Item[1];
DataTable dtConfig = ObtenerConfigPrincipal(infoImportar, hojaConfig);
int indiceHojaParaMigrar;
Excel.Worksheet hojaParaMigrar;
foreach (DataRow infoTabla in dtConfig.Rows)
{
indiceHojaParaMigrar = ObtenerIndiceHojaExcel(oSheets, infoTabla["Pestania"].ToString());
if (indiceHojaParaMigrar > 0)
{
hojaParaMigrar = (Excel.Worksheet)oSheets.Item[indiceHojaParaMigrar];
DataTable dtDefTablas = null;
dtDefTablas = ObtenerConfigColumnasSegunTabla(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());
DataTable dtDatos = null;
dtDatos = ObtenerRegistrosTablaSegunPestania(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());
}
else
{
throw new ImportadorExcelExceptionBE("No se ha encontrado la Hoja Excel: " + infoTabla["Pestania"].ToString());
}
}
ImportadorExcelBM objImportadorBM = new ImportadorExcelBM();
objImportadorBM.GuardarImportacion(infoImportar);
}
catch (Exception ex)
{
if (xlApp != null)
{
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
msgResul = ex.Message;
}
finally
{
if (ObjCom != null)
{
ObjCom.terminarProceso();
}
}
return(msgResul); // Si es "" (VACIO) entonces es PROCESO OK
}