private XlRange PutSampleData(XlWorksheet workSheet)
{
workSheet.Cells(2, 2).Value = "Datum";
workSheet.Cells(3, 2).Value = DateTime.Now.ToShortDateString();
workSheet.Cells(4, 2).Value = DateTime.Now.ToShortDateString();
workSheet.Cells(5, 2).Value = DateTime.Now.ToShortDateString();
workSheet.Cells(6, 2).Value = DateTime.Now.ToShortDateString();
workSheet.Cells(2, 3).Value = "Column1";
workSheet.Cells(3, 3).Value = 25;
workSheet.Cells(4, 3).Value = 33;
workSheet.Cells(5, 3).Value = 30;
workSheet.Cells(6, 3).Value = 22;
workSheet.Cells(2, 4).Value = "Column2";
workSheet.Cells(3, 4).Value = 25;
workSheet.Cells(4, 4).Value = 33;
workSheet.Cells(5, 4).Value = 30;
workSheet.Cells(6, 4).Value = 22;
workSheet.Cells(2, 5).Value = "Column3";
workSheet.Cells(3, 5).Value = 25;
workSheet.Cells(4, 5).Value = 33;
workSheet.Cells(5, 5).Value = 30;
workSheet.Cells(6, 5).Value = 22;
return(workSheet.Range("$B2:$E6"));
}
static void Main(string[] args)
{
Console.WriteLine("XlLateBinding Performance Test - 10.000 Cells.");
/*
* start excel and disable messageboxes and screen updating
*/
XlApplication excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
excelApplication.ScreenUpdating = false;
/*
* create new empty worksheet
*/
excelApplication.Workbooks.Add();
XlWorksheet sheet = excelApplication.Workbooks[1].Worksheets[1];
/*
* do the test
*/
DateTime timeStart = DateTime.Now;
for (int i = 1; i <= 10000; i++)
{
string rangeAdress = "$A" + i.ToString();
XlRange cellRange = sheet.Range(rangeAdress);
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = false;
XlComment sampleComment = cellRange.AddComment("Sample Comment");
}
DateTime timeEnd = DateTime.Now;
TimeSpan timeElapsed = timeEnd - timeStart;
/*
* 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, "LateBinding.log");
string logFileAppend = timeElapsed.ToString() + Environment.NewLine;
File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);
excelApplication.Quit();
excelApplication.Dispose();
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
// font action
workSheet.Range("A1").Value = "Arial Size:8 Bold Italic Underline";
workSheet.Range("A1").Font.Name = "Arial";
workSheet.Range("A1").Font.Size = 8;
workSheet.Range("A1").Font.Bold = true;
workSheet.Range("A1").Font.Italic = true;
workSheet.Range("A1").Font.Underline = true;
workSheet.Range("A1").Font.Color = Color.Violet.ToArgb();
workSheet.Range("A3").Value = "Times New Roman Size:10";
workSheet.Range("A3").Font.Name = "Times New Roman";
workSheet.Range("A3").Font.Size = 10;
workSheet.Range("A3").Font.Color = Color.Orange.ToArgb();
workSheet.Range("A5").Value = "Comic Sans MS Size:12 WrapText";
workSheet.Range("A5").Font.Name = "Comic Sans MS";
workSheet.Range("A5").Font.Size = 12;
workSheet.Range("A5").WrapText = true;
workSheet.Range("A5").Font.Color = Color.Navy.ToArgb();
// HorizontalAlignment
workSheet.Range("A7").Value = "xlHAlignLeft";
workSheet.Range("A7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignLeft;
workSheet.Range("B7").Value = "xlHAlignCenter";
workSheet.Range("B7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignCenter;
workSheet.Range("C7").Value = "xlHAlignRight";
workSheet.Range("C7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignRight;
workSheet.Range("D7").Value = "xlHAlignJustify";
workSheet.Range("D7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignJustify;
workSheet.Range("E7").Value = "xlHAlignDistributed";
workSheet.Range("E7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignDistributed;
workSheet.Range("A9").Value = "xlVAlignTop";
workSheet.Range("A9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignTop;
workSheet.Range("B9").Value = "xlVAlignCenter";
workSheet.Range("B9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignCenter;
workSheet.Range("C9").Value = "xlVAlignBottom";
workSheet.Range("C9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignBottom;
workSheet.Range("D9").Value = "xlVAlignDistributed";
workSheet.Range("D9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignDistributed;
workSheet.Range("E9").Value = "xlVAlignJustify";
workSheet.Range("E9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 35;
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example2{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}