static void Main(string[] args)
{
Console.WriteLine("NetOffice Release Performance Test - 10000 Cells.");
Console.WriteLine("Write simple text, change Font, NumberFormat and do a BorderArround.");
// start excel, and get a new sheet reference
Excel.Application excelApplication = CreateExcelApplication();
Excel.Worksheet sheet = excelApplication.Workbooks.Add().Worksheets.Add() as Excel.Worksheet;
// do test 10 times
List <TimeSpan> timeElapsedList = new List <TimeSpan>();
for (int i = 1; i <= 10; i++)
{
DateTime timeStart = DateTime.Now;
for (int y = 1; y <= 10000; y++)
{
string rangeAdress = "$A" + y.ToString();
Excel.Range cellRange = sheet.get_Range(rangeAdress);
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, 0);
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
sheet.DisposeChildInstances();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test2-NetOffice.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
excelApplication.Quit();
excelApplication.Dispose();
}
private void BorderAround(Excel.Range range, int colour)
{
range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
}
public static void Export <T>(
this ICollectionView collectionView,
Dictionary <string, Model.DataCellFormats> fieldsAndFormats,
string reportTitle,
string reportDescription,
Func <T, string, string, object> getValueDelegate, Action <string> callBack = null)
{
if (getValueDelegate == null || fieldsAndFormats == null || reportTitle == null)
{
return;
}
const string commentFieldName = nameof(Model.Meter.Коментарий);
bool hasCommentColumn = fieldsAndFormats.ContainsKey(commentFieldName);
IEnumerable <T> collection = null;
Application.Current.Dispatcher.Invoke(() =>
{
collection = collectionView.Cast <T>();
});
int numberOfRows = collection.Count();
// +1 т.к. первый столбец номер по порядку
int numberOfColumns = fieldsAndFormats.Count + 1;
callBack?.Invoke("чтение данных");
object[,] output = BuildDataArray();
object[,] outputWithTwoRowPerRecord = null;
if (hasCommentColumn)
{
outputWithTwoRowPerRecord = BuildDataArrayWithTwoRowPerRecord();
}
callBack?.Invoke("поиск MS Excel");
string fileName = System.IO.Path.GetTempFileName();
fileName = System.IO.Path.ChangeExtension(fileName, "xlsx");
System.Globalization.CultureInfo defaultCulture = System.Threading.Thread.CurrentThread.CurrentCulture;
// HACK: Workaround for Excel bug on machines which are set up in the English language, but not an English region.
System.Globalization.CultureInfo enusCultureInfo = System.Globalization.CultureInfo.GetCultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = enusCultureInfo;
Excel.Application excelApplication = null;
Excel.Workbook xlWorkbook = null;
Excel.Worksheet xlWorksheet = null;
Excel.Worksheet xlWorksheet2 = null;
NetOffice.OfficeApi.Tools.Contribution.CommonUtils utils = null;
Exception exception = null;
Process(output, outputWithTwoRowPerRecord);
OpenCreatedWorkBook();
// возвращает значение указанного поля записи
object GetValueOfField(T item, string fieldName)
{
object value = string.Empty;
if (string.IsNullOrWhiteSpace(fieldsAndFormats[fieldName].ExcelFormat) == false)
{
value = getValueDelegate(item, string.Empty, fieldName);
}
else
{
value = getValueDelegate(item, fieldsAndFormats[fieldName].ContentDisplayFormat, fieldName);
}
if (value is DateOnly dateOnlyValue)
{
value = dateOnlyValue.ToDateTime(TimeOnly.MinValue);
}
return(value);
}
// создание массива данных
object[,] BuildDataArray()
{
// +1 т.к. первый столбец номер по порядку
int countOfColumns = fieldsAndFormats.Count + 1;
// +1 т.к. первая строка шапка
object[,] output = new object[numberOfRows + 1, countOfColumns];
// for (int i = 0; i < numberOfRows + 1; i++)
// output[i] = new object[numberOfColumns];
output[0, 0] = "№ п/п";
int ind = 1;
foreach (KeyValuePair <string, Model.DataCellFormats> field in fieldsAndFormats)
{
output[0, ind++] = Utils.ConvertFromTitleCase(field.Key);
}
callBack?.Invoke("заполнение таблицы");
int rowIndex = 1;
foreach (T item in collection)
{
output[rowIndex, 0] = rowIndex;
ind = 1; // т.к. первый столбец номер по порядку
foreach (string field in fieldsAndFormats.Keys)
{
output[rowIndex, ind++] = GetValueOfField(item, field);
}
rowIndex++;
}
return(output);
}
// создание массива данных со второй строкой в каждой записи где указан комментарий
object[,] BuildDataArrayWithTwoRowPerRecord()
{
// -1 т.к. исключен столбец Комментарий
// +1 т.к. первый столбец номер по порядку
int countOfColumns = fieldsAndFormats.Count - 1 + 1;
int numberOfRecords = collection.Count();
// +1 т.к. первая строка шапка
object[,] output = new object[(2 * numberOfRecords) + 1, countOfColumns];
// for (int i = 0; i < numberOfRows + 1; i++)
// output[i] = new object[numberOfColumns];
output[0, 0] = "№ п/п";
int ind = 1;
foreach (KeyValuePair <string, Model.DataCellFormats> field in fieldsAndFormats)
{
if (field.Key == commentFieldName)
{
continue;
}
else
{
output[0, ind++] = Utils.ConvertFromTitleCase(field.Key);
}
}
callBack?.Invoke("заполнение таблицы");
int rowIndex = 1;
int recordIndex = 1;
foreach (T item in collection)
{
output[rowIndex, 0] = recordIndex;
ind = 1; // т.к. первый столбец номер по порядку
foreach (string field in fieldsAndFormats.Keys)
{
if (field == commentFieldName)
{
continue;
}
output[rowIndex, ind++] = GetValueOfField(item, field);
}
string commentValue = GetValueOfField(item, commentFieldName).ToString().Trim().Replace('\n', '\t');
output[rowIndex + 1, 1] = commentValue;
rowIndex += 2;
recordIndex++;
}
return(output);
}
void ApplyDataFormatForSheet(Excel.Worksheet xlWorksheet, Excel.Range rangeToSetData, bool thisWorkSheetIsForPrint)
{
// -1 т.к. исключен столбец Комментарий
// +1 т.к. первый столбец номер по порядку
int countOfColumns = hasCommentColumn ? fieldsAndFormats.Count - 1 + 1 : fieldsAndFormats.Count + 1;
callBack?.Invoke("установка формата данных");
int rowIndex = 2; // 1 - шапка таблицы
int ind = 1;
int recordIndex = 1;
foreach (T item in collection)
{
rangeToSetData[rowIndex, 0 + 1].NumberFormat = "0";
ind = 1; // т.к. первый столбец номер по порядку
foreach (string field in fieldsAndFormats.Keys)
{
if (thisWorkSheetIsForPrint && field == commentFieldName)
{
continue;
}
try
{
object value = string.Empty;
if (string.IsNullOrWhiteSpace(fieldsAndFormats[field].ExcelFormat) == false)
{
string format = string.IsNullOrWhiteSpace(fieldsAndFormats[field].ExcelFormat) ? "General" : fieldsAndFormats[field].ExcelFormat;
rangeToSetData[rowIndex, 0 + 1 + ind].NumberFormat = format;
}
else if (string.IsNullOrWhiteSpace(fieldsAndFormats[field].ContentDisplayFormat) == false)
{
rangeToSetData[rowIndex, 0 + 1 + ind].NumberFormat = "General";
}
}
catch (Exception e)
{
#if DEBUG
App.ToDebug(e);
#endif
}
finally
{
ind++;
}
}
if (thisWorkSheetIsForPrint && hasCommentColumn)
{
// ячейка с номером по порядку
Excel.Range rng = rangeToSetData[rowIndex, 1];
rng = rng.Resize(2, 1);
rng.Merge();
rng.HorizontalAlignment = XlHAlign.xlHAlignLeft;
rng.VerticalAlignment = XlVAlign.xlVAlignCenter;
// ячейка с комментарием
rng = rangeToSetData[rowIndex + 1, 2].Resize(1, countOfColumns - 1);
rng.Merge();
rng.WrapText = true;
rng.NumberFormat = "@";
rng = rangeToSetData[rowIndex, 1].Resize(2, countOfColumns);
rng.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
rng.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin;
rng.Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(System.Drawing.Color.Gray);
rng.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDot;
rng.Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
rng.Borders[XlBordersIndex.xlInsideVertical].Color = utils.Color.ToDouble(System.Drawing.Color.Gray);
if (recordIndex % 2 == 0)
{
rng = rangeToSetData[rowIndex, 1].Resize(2, countOfColumns);
rng.Interior.Color = utils.Color.ToDouble(System.Drawing.Color.WhiteSmoke);
}
rowIndex += 2;
}
else
{
rowIndex++;
}
recordIndex++;
}
}
void Process(object[,] outputData1, object[,] outputData2)
{
try
{
callBack?.Invoke("создание книги MS Excel");
excelApplication = new Excel.Application
{
DisplayAlerts = false,
ScreenUpdating = false,
};
utils = new NetOffice.OfficeApi.Tools.Contribution.CommonUtils(excelApplication);
xlWorkbook = excelApplication.Workbooks.Add();
xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];
Excel.Range all = xlWorksheet.Range("A1");
Excel.Range header = xlWorksheet.Range("A1");
header.WrapText = true;
header.Resize(1, numberOfColumns).Merge();
using (Excel.Font font = header.Font)
{
font.Size = 14;
font.Bold = true;
}
header.HorizontalAlignment = XlHAlign.xlHAlignCenter;
header.VerticalAlignment = XlVAlign.xlVAlignCenter;
double oneRowHeight = (double)header.RowHeight;
int rowsCount = reportTitle.Split(new[] { Environment.NewLine }, StringSplitOptions.None).Length;
header.RowHeight = oneRowHeight * rowsCount * 1.1;
header.Value2 = reportTitle;
Excel.Range description = xlWorksheet.Range("A2");
description.Resize(1, numberOfColumns).Merge();
description.WrapText = true;
using (Excel.Font font = description.Font)
{
font.Size = 12;
font.Italic = true;
}
description.HorizontalAlignment = XlHAlign.xlHAlignLeft;
description.VerticalAlignment = XlVAlign.xlVAlignCenter;
oneRowHeight = (double)description.RowHeight;
rowsCount = reportDescription.Split(new[] { Environment.NewLine }, StringSplitOptions.None).Length;
description.RowHeight = oneRowHeight * rowsCount * 1.1;
description.Value2 = reportDescription;
callBack?.Invoke("настройка книги MS Excel");
if (hasCommentColumn)
{
// создание копии листа
xlWorksheet.Copy(xlWorksheet);
xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];
xlWorksheet2 = (Excel.Worksheet)xlWorkbook.Sheets[2];
xlWorksheet.Name = "Данные";
xlWorksheet2.Name = "Для печати";
Excel.Range data2 = xlWorksheet2.Range("A3").Resize((2 * numberOfRows) + 1, numberOfColumns - 1);
data2.VerticalAlignment = VerticalAlignment.Center;
data2.NumberFormat = "@";
data2.Value = outputData2;
ApplyDataFormatForSheet(xlWorksheet2, data2, true);
Excel.Range tableHeader = xlWorksheet2.Range("A3").Resize(1, numberOfColumns - 1);
tableHeader.WrapText = true;
using (Excel.Font font = tableHeader.Font)
{
font.Size = 12;
font.Bold = true;
}
header.HorizontalAlignment = XlHAlign.xlHAlignCenter;
header.VerticalAlignment = XlVAlign.xlVAlignCenter;
tableHeader.Interior.Color = utils.Color.ToDouble(System.Drawing.Color.LightGray);
tableHeader.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic);
tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot;
tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin;
tableHeader.Borders[XlBordersIndex.xlInsideHorizontal].Color = utils.Color.ToDouble(System.Drawing.Color.Black);
}
Excel.Range data = xlWorksheet.Range("A3").Resize(numberOfRows + 1, numberOfColumns);
data.NumberFormat = "@";
data.Value = outputData1;
ApplyDataFormatForSheet(xlWorksheet, data, false);
xlWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, data,
Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "DataTable";
xlWorksheet.ListObjects["DataTable"].TableStyle = "TableStyleMedium6";
SetupWorkSheet(xlWorksheet, numberOfColumns);
if (hasCommentColumn)
{
SetupWorkSheet(xlWorksheet2, numberOfColumns - 1);
}
callBack?.Invoke("сохранение книги MS Excel");
xlWorkbook.SaveAs(fileName);
xlWorkbook.Close(false);
callBack?.Invoke("завершение");
logger?.Info($"Export >> файл сформирован и сохранен: '{fileName}'");
}
catch (Exception e)
{
exception = e;
#if DEBUG
App.ToDebug(e);
#endif
App.ShowError("Произошла ошибка:\n" + App.GetExceptionDetails(e));
return;
}
finally
{
System.Threading.Thread.CurrentThread.CurrentCulture = defaultCulture;
excelApplication.Quit();
excelApplication.ScreenUpdating = true;
excelApplication.DisplayAlerts = true;
if (exception != null)
{
if (excelApplication.Workbooks.Any())
{
foreach (Excel.Workbook workbook in excelApplication.Workbooks.Where(x => !x.IsDisposed))
{
workbook.Close(false, System.Reflection.Missing.Value, Missing.Value);
workbook.Dispose();
}
}
if (excelApplication.IsDisposed == false)
{
excelApplication.Quit();
excelApplication.Dispose();
}
}
}
}
void SetupWorkSheet(Excel.Worksheet xlWorksheet, int numberOfColumns)
{
foreach (int i in Enumerable.Range(1, numberOfColumns))
{
xlWorksheet.Columns[i].AutoFit();
}
Excel.PageSetup ps = xlWorksheet.PageSetup;
ps.PaperSize = XlPaperSize.xlPaperA4;
ps.Orientation = XlPageOrientation.xlLandscape;
ps.Zoom = false;
ps.FitToPagesWide = 1;
ps.FitToPagesTall = false;
ps.PrintTitleRows = "$3:$3";
ps.LeftMargin = excelApplication.CentimetersToPoints(1.0);
ps.RightMargin = excelApplication.CentimetersToPoints(1.0);
ps.TopMargin = excelApplication.CentimetersToPoints(2.0);
ps.BottomMargin = excelApplication.CentimetersToPoints(1.0);
ps.HeaderMargin = excelApplication.CentimetersToPoints(0.6);
ps.FooterMargin = excelApplication.CentimetersToPoints(0.6);
ps.CenterHorizontally = true;
ps.RightHeader = DateTime.Now.ToString("D", defaultCulture);
ps.CenterFooter = "Страница &P / &N";
ps.PrintArea = xlWorksheet.Range("A1").Resize(numberOfRows + 3, numberOfColumns).Address;
}
void OpenCreatedWorkBook()
{
try
{
callBack?.Invoke("открытие созданной книги MS Excel");
using System.Diagnostics.Process p = new System.Diagnostics.Process
{
StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true,
},
};
p.Start();
// System.Diagnostics.Process.Start(fileName);
}
catch (Exception e)
{
#if DEBUG
App.ToDebug(e);
#endif
App.ShowError("Произошла ошибка при открытии файла:\n" + App.GetExceptionDetails(e));
}
}
}