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