public static void WriteOnlyGoodMarks(List <DeformationMark> defMarks, List <DeformationMark> bearMarks, Excel.Worksheet xlWorkSheet)
{
object[] objHeaders = { "Имя марки", "X", "Y", "Z", "mx", "my", "mz", "m" };
Excel.Range mRange = xlWorkSheet.get_Range("A1", "H1");
mRange.Value = objHeaders;
Excel.Font mFont = mRange.Font;
mFont.Bold = true;
int j = 2;
for (int i = 1; i < defMarks.Count + 1; i++)
{
if (defMarks[i - 1].relevance == true)
{
xlWorkSheet.Cells[j, 1] = defMarks[i - 1].name;
xlWorkSheet.Cells[j, 2] = defMarks[i - 1].xCoordinate;
xlWorkSheet.Cells[j, 3] = defMarks[i - 1].yCoordinate;
xlWorkSheet.Cells[j, 4] = defMarks[i - 1].zCoordinate;
xlWorkSheet.Cells[j, 5] = defMarks[i - 1].mx;
xlWorkSheet.Cells[j, 6] = defMarks[i - 1].my;
xlWorkSheet.Cells[j, 7] = defMarks[i - 1].mz;
xlWorkSheet.Cells[j, 8] = defMarks[i - 1].m;
j++;
}
}
for (int i = 0; i < bearMarks.Count; i++)
{
xlWorkSheet.Cells[i + j, 1] = bearMarks[i].name;
xlWorkSheet.Cells[i + j, 2] = bearMarks[i].xCoordinate;
xlWorkSheet.Cells[i + j, 3] = bearMarks[i].yCoordinate;
xlWorkSheet.Cells[i + j, 4] = bearMarks[i].zCoordinate;
}
}
/// <summary>
/// Function for writing to file all marks
/// </summary>
/// <param name="defMarks">A list of deformation marks</param>
/// <param name="bearMarks">A list of bearing marks</param>
/// <param name="xlWorkSheet">The object of excel file sheet</param>
public static void WriteAllMarks(List <DeformationMark> defMarks, List <Mark> bearMarks, Excel.Worksheet xlWorkSheet)
{
object[] objHeaders = { "Имя марки", "X", "Y", "Z", "mx", "my", "mz", "m", "Статус" };
Excel.Range mRange = xlWorkSheet.get_Range("A1", "I1");
mRange.Value = objHeaders;
Excel.Font mFont = mRange.Font;
mFont.Bold = true;
int j = 2;
for (int i = 1; i < defMarks.Count + 1; i++)
{
xlWorkSheet.Cells[j, 1] = defMarks[i - 1].Name;
xlWorkSheet.Cells[j, 2] = defMarks[i - 1].XCoordinate;
xlWorkSheet.Cells[j, 3] = defMarks[i - 1].YCoordinate;
xlWorkSheet.Cells[j, 4] = defMarks[i - 1].ZCoordinate;
xlWorkSheet.Cells[j, 5] = defMarks[i - 1].Mx;
xlWorkSheet.Cells[j, 6] = defMarks[i - 1].My;
xlWorkSheet.Cells[j, 7] = defMarks[i - 1].Mz;
xlWorkSheet.Cells[j, 8] = defMarks[i - 1].M;
xlWorkSheet.Cells[j, 9] = defMarks[i - 1].Status;
j++;
}
for (int i = 0; i < bearMarks.Count; i++)
{
xlWorkSheet.Cells[i + j, 1] = bearMarks[i].Name;
xlWorkSheet.Cells[i + j, 2] = bearMarks[i].XCoordinate;
xlWorkSheet.Cells[i + j, 3] = bearMarks[i].YCoordinate;
xlWorkSheet.Cells[i + j, 4] = bearMarks[i].ZCoordinate;
}
}
void WriteVsto(int size)
{
Stopwatch sw = Stopwatch.StartNew();
_app.ScreenUpdating = false;
Color[] colors = { Color.Red, Color.Green, Color.Blue,
Color.Bisque, Color.Gray, Color.Pink, Color.GreenYellow };
string[] numberFormats = { "0.00", "0%", "£#,##0;-£#,##0", "#,##0;[Red]-#,##0" };
Worksheet sheet = _app.ActiveSheet;
for (int i = 1; i <= size; i++)
{
for (int j = 1; j <= size; j++)
{
Range cell = sheet.Cells[i, j];
cell.Value = 1000 * i + j;
cell.Interior.Color = colors[_rand.Next(6)];
Font font = cell.Font;
font.Color = colors[_rand.Next(6)];
font.Size = _rand.Next(9, 14);
font.Italic = _rand.Next(1) == 1;
font.Bold = _rand.Next(1) == 1;
cell.NumberFormat = numberFormats[_rand.Next(3)];
}
}
_app.ScreenUpdating = true;
sw.Stop();
MessageBox.Show(sw.Elapsed.ToString(), $"VSTO: write {size * size}");
}
void ReadVsto(int size)
{
Stopwatch sw = Stopwatch.StartNew();
_app.ScreenUpdating = false;
Worksheet sheet = _app.ActiveSheet;
for (int i = 1; i <= size; i++)
{
for (int j = 1; j <= size; j++)
{
Range cell = sheet.Cells[i, j];
dynamic a1 = cell.Value;
dynamic a2 = cell.Interior.Color;
Font font = cell.Font;
dynamic a3 = font.Color;
dynamic a4 = font.Size;
dynamic a5 = font.Italic;
dynamic a6 = font.Bold;
dynamic a7 = cell.NumberFormat;
}
}
_app.ScreenUpdating = true;
sw.Stop();
MessageBox.Show(sw.Elapsed.ToString(), $"VSTO: read {size * size}");
}
/// <summary>
/// Establece el estilo de las celdas
/// </summary>
/// <param name="nombreFuente"></param>
/// <param name="negritas"></param>
/// <param name="size"></param>
/// <param name="color"></param>
private void EstablecerEstilo(string nombreFuente, bool negritas, int size, int color)
{
rango.Font.Name = nombreFuente;
fuente = rango.Font;
fuente.Bold = negritas;
fuente.Size = size;
rango.Cells.Interior.Color = color;
}
//ПРИМЕНЕНИЕ ШРИФТА К ЯЧЕЙКЕ
public void SetFont(Excel.Font font, int colorIndex)
{
_range.Font.Size = font.Size;
_range.Font.Bold = font.Bold;
_range.Font.Italic = font.Italic;
_range.Font.Name = font.Name;
_range.Font.ColorIndex = colorIndex;
}
static void Main(string[] args)
{
Console.WriteLine("Interop Assemblies 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.Workbooks books = excelApplication.Workbooks;
Excel.Workbook book = books.Add(Missing.Value);
Excel.Sheets sheets = book.Worksheets;
Excel.Worksheet sheet = sheets.Add() as Excel.Worksheet;
// do test 10 times
List <MarshalByRefObject> comReferencesList = new List <MarshalByRefObject>();
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.Range[rangeAdress];
cellRange.Value = "value";
Excel.Font font = cellRange.Font;
font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.BorderAround(Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, 0);
comReferencesList.Add(font as MarshalByRefObject);
comReferencesList.Add(cellRange as MarshalByRefObject);
}
TimeSpan timeElapsed = DateTime.Now - timeStart;
// display info and dispose references
Console.WriteLine("Time Elapsed: {0}", timeElapsed);
timeElapsedList.Add(timeElapsed);
foreach (var item in comReferencesList)
{
Marshal.ReleaseComObject(item);
}
comReferencesList.Clear();
}
// display info & log to file
TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test2-Interop.log");
Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
Console.Read();
// release & quit
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
}
private void ApplyFontStyle(ExcelFormat cellFormat, ExcelApp.Font font)
{
font.Bold = cellFormat.Bold;
if (cellFormat.ForeColor.HasValue)
{
font.Color = ColorTranslator.ToOle(cellFormat.ForeColor.Value);
}
}
/// <summary>
/// 设置连续多单元格块范围
/// </summary>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public void SetRange(int startRow, int startCol, int endRow, int endCol)
{
this._range = this._worksheet.Range[this._worksheet.Cells[startRow, startCol], this._worksheet.Cells[endRow, endCol]];
this._font = this._range.Font;
this._borders = this._range.Borders;
this._leftBorder = this._borders[Excel.XlBordersIndex.xlEdgeLeft];
this._topBorder = this._borders[Excel.XlBordersIndex.xlEdgeTop];
this._rightBorder = this._borders[Excel.XlBordersIndex.xlEdgeRight];
this._bottomBorder = this._borders[Excel.XlBordersIndex.xlEdgeBottom];
}
/// <summary>
/// 设置单元格范围
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
public void SetRange(int row, int col)
{
this._range = this._worksheet.Cells[row, col];
this._font = this._range.Font;
this._borders = this._range.Borders;
this._leftBorder = this._borders[Excel.XlBordersIndex.xlEdgeLeft];
this._topBorder = this._borders[Excel.XlBordersIndex.xlEdgeTop];
this._rightBorder = this._borders[Excel.XlBordersIndex.xlEdgeRight];
this._bottomBorder = this._borders[Excel.XlBordersIndex.xlEdgeBottom];
}
/// <summary>
/// 将表头加粗显示
/// </summary>
private void SetHeaderStyle(string startRange)
{
_font = _range.Font;
_font.Bold = true;
_font.Size = 10;
_range = _sheet.get_Range(startRange, Type.Missing);
_range.RowHeight = 25;
_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
_range.WrapText = true;
}
public static void SetFormatSmaller(ExcelInterop.Range range, double limit)
{
Utility.AddNativieResource(range);
ExcelInterop.FormatConditions formcond = range.FormatConditions;
Utility.AddNativieResource(formcond);
ExcelInterop.FormatCondition newcond = formcond.Add(ExcelInterop.XlFormatConditionType.xlCellValue, ExcelInterop.XlFormatConditionOperator.xlLess, limit);
Utility.AddNativieResource(newcond);
ExcelInterop.Font condfont = newcond.Font;
Utility.AddNativieResource(condfont);
condfont.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); //Red letters
}
/// <summary>
/// Chn 设置字体
/// </summary>
/// <param name="workSheet"></param>
/// <param name="range"></param>
/// <param name="fontName"></param>
/// <param name="fontSize"></param>
/// <param name="fontColor"></param>
public static void SetFont(this Excel.Worksheet workSheet, Excel.Range range, string fontName, int fontSize, ExcelColorIndex?fontColor)
{
Excel.Font font = range.Font;
font.Name = fontName;
font.Size = fontSize;
if (fontColor != null)
{
font.ColorIndex = fontColor;
}
}
/// <summary>
/// Chn 设置字体
/// </summary>
/// <param name="workSheet"></param>
/// <param name="range"></param>
/// <param name="fontName"></param>
/// <param name="fontSize"></param>
/// <param name="fontColor"></param>
public static void SetFont(this Excel.Worksheet workSheet, Excel.Range range, ExcelFont excelFont)
{
if (excelFont != null)
{
Excel.Font font = range.Font;
font.Name = excelFont.FontName;
font.Size = excelFont.FontSize;
font.ColorIndex = excelFont.FontColor;
}
}
public void SetFontColor(Color color)
{
AssertNotDisposed();
Excel.Range _range = _GetRange();
Excel.Font _font = _range.Font;
var oleColor = ColorTranslator.ToOle(color);
_font.Color = oleColor;
Marshal.ReleaseComObject(_font);
Marshal.ReleaseComObject(_range);
}
private void CreateTitle(string startRange, int rowCount, int colCount, string titlevalue)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.Merge(Missing.Value);
_range.set_Value(_optionalValue, titlevalue);
_range.RowHeight = 40;
_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
_font = _range.Font;
_font.Bold = true;
_font.Size = 15;
_range.Borders.LineStyle = 1;
}
public void close_excel()
{
if (m_objBooks != null)
{
m_objBook.Close(false, m_objOpt, m_objOpt);
}
m_objExcel.Quit();
m_objFont = null;
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBooks = null;
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
public void WriteReportToFile()
{
// Start a new workbook in Excel.
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
// Create an array for the headers and add it to cells A1:C1.
object[] objHeaders = { "UID", "Группа", "Дата", "Уровень использования", "Скорость", "Задержка", "Ошибки", "Временное окно", "Оценка" };
m_objRange = m_objSheet.get_Range("A1", "I1");
m_objRange.Value = objHeaders;
m_objFont = m_objRange.Font;
m_objFont.Bold = true;
// Create an array and add it to the worksheet starting at cell A2.
object[,] objData = new Object[count, columns];
for (int r = 0; r < count; r++)
{
objData[r, 0] = UID[r];
objData[r, 1] = group[r];
objData[r, 2] = date[r];
objData[r, 3] = kRg[r];
objData[r, 4] = kTh[r];
objData[r, 5] = kDy[r];
objData[r, 6] = kEr[r];
objData[r, 7] = kWd[r];
objData[r, 8] = mark[r];
}
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange = m_objRange.get_Resize(count, columns);
m_objRange.Value = objData;
m_objExcel.DisplayAlerts = false;
now = DateTime.Now;
filename = m_strSampleFolder + "report_" + now.ToString("dd/MM/yyyy_hh-mm-ss") + ".xlsx";
File.Create(filename).Close();
m_objBook.SaveAs(filename, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objExcel.DisplayAlerts = true;
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
Process.Start(filename);
}
/// <summary>
/// Creates the header.
/// </summary>
/// <typeparam name="T">The type of the items.</typeparam>
/// <param name="range">The Excel range.</param>
/// <param name="font">The Excel font.</param>
/// <param name="optionalValue">The optional value.</param>
/// <param name="strHeaderStart">The string header start.</param>
/// <param name="sheet">The worksheet.</param>
/// <param name="objHeaders">The object headers.</param>
#pragma warning disable GCop119 // Don’t use {0} parameters in method definition. To return several objects, define a class or struct for your method return type.
private static void CreateHeader <T>(out Range range, out Font font, object optionalValue, string strHeaderStart, _Worksheet sheet, out Dictionary <string, string> objHeaders)
#pragma warning restore GCop119 // Don’t use {0} parameters in method definition. To return several objects, define a class or struct for your method return type.
{
objHeaders = new Dictionary <string, string>();
foreach (var property in typeof(T).GetProperties())
{
var attribute = property.GetCustomAttributes <DisplayNameAttribute>(inherit: false).FirstOrDefault();
objHeaders.Add(property.Name, attribute == null ? property.Name : attribute.DisplayName);
}
range = sheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(1, objHeaders.Count);
range.set_Value(optionalValue, objHeaders.Values.ToArray());
range.BorderAround(Type.Missing, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Type.Missing);
font = range.Font;
font.Bold = true;
range.Interior.Color = Color.LightGray.ToArgb();
}
private void button1_Click(object sender, EventArgs e)
{
string sFilePathName = EnsureDestFileUnique(MMExt.UserLogLocation() + "Excel01.xlsx");
MMExcel.MMExcel mm = new MMExcel.MMExcel(StartMode.smNew, sFilePathName);
MMWS ws0 = mm.Sheet[0];
ws0.Name = "ZeroSheet";
double dInchValue = 0.25;
ws0["A1", "AB120"].RowHeight = dInchValue.toPointsVertical();
ws0["A1", "AB120"].ColumnWidth = dInchValue.toPointsHorizontal(); // dColWidthPerPoint * dInchValue.toPointsHorizontal();
ws0["A1", "A1"].Rng.Font.Name = "Century Gothic";
Excel.Font fontA = ws0["A1", "A1"].Rng.Font;
mm.Close();
Process.Start(sFilePathName);
}
public static void InitializeFont(Microsoft.Office.Interop.Excel.Font rangeFont, CellFormatSpecification formatSpecification)
{
formatSpecification.Font.Background = rangeFont.Background;
formatSpecification.Font.Bold = rangeFont.Bold;
formatSpecification.Font.Color = rangeFont.Color;
formatSpecification.Font.ColorIndex = rangeFont.ColorIndex;
formatSpecification.Font.FontStyle = rangeFont.FontStyle;
formatSpecification.Font.Italic = rangeFont.Italic;
formatSpecification.Font.Name = rangeFont.Name;
formatSpecification.Font.OutlineFont = rangeFont.OutlineFont;
formatSpecification.Font.Shadow = rangeFont.Shadow;
formatSpecification.Font.Size = rangeFont.Size;
formatSpecification.Font.Strikethrough = rangeFont.Strikethrough;
formatSpecification.Font.Subscript = rangeFont.Subscript;
formatSpecification.Font.Superscript = rangeFont.Superscript;
formatSpecification.Font.Underline = rangeFont.Underline;
// null by default
//formatSpecification.Font.ThemeColor = rangeFont.ThemeColor;
formatSpecification.Font.TintAndShade = rangeFont.TintAndShade;
formatSpecification.Font.ThemeFont = rangeFont.ThemeFont;
}
public void ClearRange(ExcelInterop.Range from, ExcelInterop.Range to, ExcelInterop.Range with)
{
if (from == null)
{
return;
}
ExcelInterop.Worksheet concernedSheet = null;
bool isProtected = false;
try
{
concernedSheet = from.Worksheet;
isProtected = concernedSheet.ProtectContents;
if (isProtected)
{
concernedSheet.Unprotect(Type.Missing);
}
if (to == null)
{
to = concernedSheet.UsedRange;
}
from = from.Resize[to.Rows.Count - from.Rows.Count - 1, to.Columns.Count - from.Columns.Count - 1];
from.Clear();
if (with != null)
{
ExcelInterop.Interior withInterior = with.Interior;
ExcelInterop.Font withFont = with.Font;
ExcelInterop.Interior interior = from.Interior;
ExcelInterop.Font font = from.Font;
font.Color = withFont.Color;
interior.Color = withInterior.Color;
ExcelApplication.ReleaseComObject(interior);
ExcelApplication.ReleaseComObject(font);
ExcelApplication.ReleaseComObject(withInterior);
ExcelApplication.ReleaseComObject(withFont);
interior = null;
font = null;
withInterior = null;
withFont = null;
}
}
catch
{
if (concernedSheet != null)
{
ExcelApplication.ReleaseComObject(concernedSheet);
}
}
finally
{
if (concernedSheet != null && isProtected)
{
ProtectSheet(concernedSheet);
}
}
}
/// <summary>
/// 将表头加粗显示
/// </summary>
static void SetHeaderStyle()
{
_font = _range.Font;
_font.Bold = true;
}
private void Export2Excel()
{
object misValue = System.Reflection.Missing.Value;
var xlApp = new Excel.ApplicationClass();
var xlWorkBook = xlApp.Workbooks.Add(misValue);
var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
//export header
for (i = 1; i <= dtGridView.Columns.Count; i++)
{
xlWorkSheet.Cells[1, i] = dtGridView.Columns[i - 1].HeaderText;
}
//export data
for (i = 1; i <= dtGridView.RowCount; i++)
{
for (j = 1; j <= dtGridView.Columns.Count; j++)
{
xlWorkSheet.Cells[i + 1, j] = dtGridView.Rows[i - 1].Cells[j - 1].Value;
}
}
//set font Khmer OS System to data range
Excel.Range myRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[dtGridView.RowCount + 1, dtGridView.Columns.Count]);
Excel.Font x = myRange.Font;
x.Name = "Arial";
x.Size = 10;
//set bold font to column header
myRange = xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, dtGridView.Columns.Count]);
x = myRange.Font;
x.Bold = true;
//autofit all columns
myRange.EntireColumn.AutoFit();
xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs(
Path.GetDirectoryName(Application.ExecutablePath) + "\\report",
#if (SAVE_AS_XL)
Excel.XlFileFormat.xlWorkbookNormal,
#else
Excel.XlFileFormat.xlHtml,
#endif
misValue,
misValue,
misValue,
misValue,
Excel.XlSaveAsAccessMode.xlExclusive,
misValue,
misValue,
misValue,
misValue,
misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
/// <summary>
/// 设置连续多单元格块范围
/// </summary>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public void SetRange(int startRow, int startCol, int endRow, int endCol)
{
this._range = this._worksheet.Range[this._worksheet.Cells[startRow, startCol], this._worksheet.Cells[endRow, endCol]];
this._font = this._range.Font;
this._borders = this._range.Borders;
this._leftBorder = this._borders[Excel.XlBordersIndex.xlEdgeLeft];
this._topBorder = this._borders[Excel.XlBordersIndex.xlEdgeTop];
this._rightBorder = this._borders[Excel.XlBordersIndex.xlEdgeRight];
this._bottomBorder = this._borders[Excel.XlBordersIndex.xlEdgeBottom];
}
public static void FormatTextColor(Microsoft.Office.Interop.Excel.Font font, Color color)
{
font.Color = XlColor(color);
font.TintAndShade = 0;
}
/// <summary>
/// 将表头加粗显示
/// </summary>
private void SetHeaderStyle()
{
_font = _range.Font;
_font.Bold = true;
}
public static void ToExcel <T>(this List <T> list, string path)
{
#region [ تعریفات ]
if (path.IsNullOrEmpty())
{
throw new Exception(CommonConsts.Messages.Exception.InvalidObject);
}
if (list == null)
{
throw new Exception(CommonConsts.Messages.Exception.InvalidObject);
}
Excel.Application excelApp = null;
Excel.Workbooks workBooks = null;
Excel._Workbook workBook = null;
Excel.Sheets sheets = null;
Excel._Worksheet workSheet = null;
Excel.Range range = null;
Excel.Font font = null;
object optionalValue = Missing.Value;
string strHeaderStart = "A2";
string strDataStart = "A3";
#endregion
#region [ پردازش ]
try
{
#region [ ایجاد ]
excelApp = new Excel.Application();
workBooks = (Excel.Workbooks)excelApp.Workbooks;
workBook = (Excel._Workbook)(workBooks.Add(optionalValue));
sheets = (Excel.Sheets)workBook.Worksheets;
workSheet = (Excel._Worksheet)(sheets.get_Item(1));
#endregion
#region [ هدر ]
Dictionary <string, string> objHeaders = new Dictionary <string, string>();
PropertyInfo[] headerInfo = typeof(T).GetProperties();
foreach (var property in headerInfo)
{
var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false)
.Cast <DisplayNameAttribute>().FirstOrDefault();
objHeaders.Add(property.Name, attribute == null ? property.Name : attribute.DisplayName);
}
range = workSheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(1, objHeaders.Count);
range.set_Value(optionalValue, objHeaders.Values.ToArray());
range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
font = range.Font;
font.Bold = true;
range.Interior.Color = Color.LightGray.ToArgb();
#endregion
#region [ نوشتن دیتا در سلول ها ]
int count = list.Count;
object[,] objData = new object[count, objHeaders.Count];
for (int j = 0; j < count; j++)
{
var item = list[j];
int i = 0;
foreach (KeyValuePair <string, string> entry in objHeaders)
{
var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null);
objData[j, i++] = (y == null) ? "" : y.ToString();
}
}
range = workSheet.get_Range(strDataStart, optionalValue);
range = range.get_Resize(count, objHeaders.Count);
range.set_Value(optionalValue, objData);
range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range = workSheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(count + 1, objHeaders.Count);
range.Columns.AutoFit();
#endregion
#region [ ذخیره فایل ]
if (!path.IsNullOrEmpty())
{
workBook.SaveAs(path);
}
excelApp.Visible = true;
#endregion
#region Release objects
try
{
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
workSheet = null;
if (sheets != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
}
sheets = null;
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
workBook = null;
if (workBooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBooks);
}
workBooks = null;
if (excelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
excelApp = null;
}
catch (Exception ex)
{
workSheet = null;
sheets = null;
workBook = null;
workBooks = null;
excelApp = null;
ex.LogToTextFile("ToExcel");
}
finally
{
GC.Collect();
}
#endregion
}
catch (Exception ex)
{
ex.LogToTextFile("ToExcel");
FarsiBox.ShowInformation(ex.Message, "خطا", true);
}
finally
{
GC.Collect();
}
#endregion
}
protected override void FormatObject(Range range, object formatValue)
{
Font font = range.Font;
font.Color = formatValue;
}
public static ExcelRange FontStyling(this ExcelRange range, Microsoft.Office.Interop.Excel.Font fontStyle)
{
range.GetInteropVersion().Font.FontStyle = fontStyle;
return(range);
}
/// <summary>
/// 设置单元格范围
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
public void SetRange(int row, int col)
{
this._range = this._worksheet.Cells[row, col];
this._font = this._range.Font;
this._borders = this._range.Borders;
this._leftBorder = this._borders[Excel.XlBordersIndex.xlEdgeLeft];
this._topBorder = this._borders[Excel.XlBordersIndex.xlEdgeTop];
this._rightBorder = this._borders[Excel.XlBordersIndex.xlEdgeRight];
this._bottomBorder = this._borders[Excel.XlBordersIndex.xlEdgeBottom];
}
private void SetHeaderStyle()
{
_font = _range.Font;
_font.Bold = true;
}
public override void FormatRange(ExternalExcelRangeFormatInfo formatInfo)
{
ModuleProc PROC = new ModuleProc(this.DYN_MODULE_NAME, "Method");
Range range = null;
Microsoft.Office.Interop.Excel.Font font = null;
Borders borders = null;
Interior interior = null;
Range rows = null;
Range columns = null;
Range entireRow = null;
Range entireColumn = null;
Validation validation = null;
try
{
range = this.GetRange(formatInfo);
if (range == null)
{
return;
}
// colors
font = range.Font;
borders = range.Borders;
interior = range.Interior;
if (formatInfo.ForeColor != Color.Empty)
{
font.ColorIndex = ExternalExcelColorMappings.GetColor(formatInfo.ForeColor.ToArgb());
}
if (formatInfo.BackColor != Color.Empty)
{
interior.ColorIndex = ExternalExcelColorMappings.GetColor(formatInfo.BackColor.ToArgb());
}
if (formatInfo.BorderColor != Color.Empty)
{
borders.ColorIndex = ExternalExcelColorMappings.GetColor(formatInfo.BorderColor.ToArgb());
}
// font
if (formatInfo.Font != null)
{
font.Name = formatInfo.Font.Name;
font.Bold = formatInfo.Font.Bold;
font.Size = formatInfo.Font.Size;
}
// row height and column width
if (formatInfo.AutoFitRows)
{
rows = range.Rows;
rows.AutoFit();
}
if (formatInfo.AutoFitColumns)
{
columns = range.Columns;
columns.AutoFit();
}
if (formatInfo.RowHeight > 0)
{
entireRow = range.EntireRow;
entireRow.RowHeight = formatInfo.RowHeight;
}
if (formatInfo.ColumnWidth > 0)
{
entireColumn = range.EntireColumn;
entireColumn.ColumnWidth = formatInfo.ColumnWidth;
}
// text
range.WrapText = formatInfo.WrapText;
if (!formatInfo.Text.IsEmpty())
{
range.Value2 = formatInfo.Text;
}
// merge
if (formatInfo.Merge)
{
range.Merge(0);
}
// horizontal and vertical alignment
range.HorizontalAlignment = (XlHAlign)formatInfo.HorizontalAlignment;
range.VerticalAlignment = (XlVAlign)formatInfo.VerticalAlignment;
// auto filter
if (formatInfo.AutoFilter)
{
range.AutoFilter(formatInfo.StartColumn);
}
// custom format
if (!formatInfo.CustomFormat.IsEmpty())
{
range.NumberFormat = formatInfo.CustomFormat;
}
if (formatInfo.ValidationType != ExternalExcelValidationType.ValidationNone)
{
validation = range.Validation;
validation.Delete();
object formula1 = Type.Missing;
object formula2 = Type.Missing;
if (formatInfo.ValidationFormat.Length > 0)
{
formula1 = formatInfo.ValidationFormat[0];
}
if (formatInfo.ValidationFormat.Length > 1)
{
formula2 = formatInfo.ValidationFormat[1];
}
switch (formatInfo.ValidationType)
{
case ExternalExcelValidationType.ValidateInputOnly:
break;
case ExternalExcelValidationType.ValidateWholeNumber:
break;
case ExternalExcelValidationType.ValidateDecimal:
{
validation.Add(XlDVType.xlValidateDecimal, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlBetween, formula1, formula2);
validation.ErrorMessage = string.Format("Please enter the value between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateList:
{
validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlBetween, formula1);
validation.ErrorMessage = string.Format("Please enter the valid range of values.");
}
break;
case ExternalExcelValidationType.ValidateDate:
{
validation.Add(XlDVType.xlValidateDate, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlBetween, formula1, formula2);
validation.ErrorMessage = string.Format("Please enter the date between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateTime:
break;
case ExternalExcelValidationType.ValidateTextLength:
{
validation.Add(XlDVType.xlValidateTextLength, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlLessEqual, formula1);
validation.ErrorMessage = string.Format("Please enter the value with max length of {0:D}", formula1.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateCustom:
break;
default:
break;
}
validation.ErrorTitle = formatInfo.ColumName;
}
}
catch (Exception ex)
{
Log.Exception(PROC, ex);
}
finally
{
Extensions.DisposeComObject(validation);
Extensions.DisposeComObject(rows);
Extensions.DisposeComObject(columns);
Extensions.DisposeComObject(entireRow);
Extensions.DisposeComObject(entireColumn);
Extensions.DisposeComObject(borders);
Extensions.DisposeComObject(interior);
Extensions.DisposeComObject(font);
Extensions.DisposeComObject(range);
}
}
