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();
}
}
/// <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);
}
}
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);
}