/// <summary> /// 设置条件格式. /// </summary> /// <param name="fromSheetName"> 需要设置条件格式的 Sheet 名字.</param> /// <param name="formula1"> 条件公式. </param> /// <param name="bold"> 满足条件后的行 是否粗体. </param> /// <param name="italic"> 满足条件后的行 是否斜体. </param> /// <param name="color"> 满足条件后的行 字体的颜色. </param> public void SetFormatConditionsExpression( string fromSheetName, string formula1, bool bold, bool italic, int color) { // 选择源工作表. Excel.Worksheet mySheet = (Excel.Worksheet)xlBook.Sheets.get_Item(fromSheetName); // 选择 mySheet.Select(); // 范围选择. Excel.Range myRange = mySheet.Range["A2", GetEndAddress(mySheet)]; // 设置公式. Excel.FormatCondition myCond = myRange.FormatConditions.Add( Excel.XlFormatConditionType.xlExpression, Type.Missing, formula1); // 设置条件字体. myCond.Font.Bold = bold; myCond.Font.Italic = italic; myCond.Font.Color = color; }
/// <summary> /// 设置条件格式. /// </summary> /// <param name="fromSheetName"> 需要设置条件格式的 Sheet 名字.</param> /// <param name="baseAddress"> 基准地址. </param> /// <param name="startAddress"> 起始地址. </param> /// <param name="subRow"> 结束行递减数量. </param> /// <param name="subCol"> 结束列递减数量. </param> /// <param name="oper"> 公式的判断方式. </param> /// <param name="formula1"> 条件公式. </param> /// <param name="bold"> 满足条件后的行 是否粗体. </param> /// <param name="italic"> 满足条件后的行 是否斜体. </param> /// <param name="color"> 满足条件后的行 字体的颜色. </param> public void SetFormatConditionsCellValueLessEqual( string fromSheetName, string baseAddress, string startAddress, int subRow, int subCol, ExcelFormatConditionOperator oper, string formula1, bool bold, bool italic, int color) { // 选择源工作表. Excel.Worksheet mySheet = (Excel.Worksheet)xlBook.Sheets.get_Item(fromSheetName); // 选择 mySheet.Select(); // 范围选择. string finishAddress = GetEndAddress(mySheet, baseAddress, subRow, subCol); Excel.Range myRange = mySheet.Range[startAddress, finishAddress]; // 设置公式. Excel.FormatCondition myCond = myRange.FormatConditions.Add( Excel.XlFormatConditionType.xlCellValue, oper, formula1); // 设置条件字体. myCond.Font.Bold = bold; myCond.Font.Italic = italic; myCond.Font.Color = color; }
public void setPriorityColor(Excel.Range priorityCell) { // get background color and translate to Color object int count = priorityCell.FormatConditions.Count; for (int i = 1; i <= count; i++) { Excel.FormatCondition formatObject = priorityCell.FormatConditions.Item(i) as Excel.FormatCondition; if (formatObject.Operator == (int)Excel.XlFormatConditionOperator.xlEqual) { string formula = formatObject.Formula1; int value = int.Parse(formula.Substring(1, formula.Length - 1)); Color filledColor = System.Drawing.ColorTranslator.FromOle(Convert.ToInt32(formatObject.Interior.Color as object)); Color fontColor = new Color(); object dynamicObj = formatObject.Font.Color; // check DBNull type object if (!(dynamicObj is DBNull)) { fontColor = System.Drawing.ColorTranslator.FromOle(Convert.ToInt32(formatObject.Font.Color as object)); } this.project.putPriorityColor(value, filledColor); this.project.putPriorityFontColor(value, fontColor); } } }
private static void RemoveCondition() { if (ConditionRange.FormatConditions != null && ConditionRange.FormatConditions.Count > 0) { try { for (Int32 i = 0; i < ConditionRange.FormatConditions.Count; i++) { if (i < 0 || i >= ConditionRange.FormatConditions.Count) { break; } MSExcel.FormatCondition c = ConditionRange.FormatConditions[i + 1] as MSExcel.FormatCondition; if (c != null && c.Formula1.Contains(ConditionRangeName)) { c.Delete(); i -= 1; } } } catch (Exception ex) { Msg.ShowError("聚光灯清除条件格式发生异常:" + ex.Message, ex); } } }
internal void AddConditionalFormatting(Excel.ListObject LO) { // for each independent variable foreach (Excel.ListColumn LC in LO.ListColumns) { if (DS.IndependentVariables.Contains(LC.Name)) { double low = 0; double high = 0; double[] values = Utilities.DataHelper.objectTOdblArray(LC.Range.Value2 as object[, ]); string fltr = "VariableName='" + LC.Name.Replace("'", "''") + "'"; DataRow row = DS.PredictorRange().Select(fltr).First(); // get the low value low = Double.TryParse(row[1].ToString(), out low) ? Double.Parse(row[1].ToString()) : 0; // get the high value high = Double.TryParse(row[2].ToString(), out high) ? Double.Parse(row[2].ToString()) : 0; // apply conditional formatting if (low != 0 && high != 0) { Excel.Range rng = Utilities.ExcelHelpers.GetListColumn(LO, LC.Name).DataBodyRange; Excel.FormatCondition fc = (Excel.FormatCondition)rng.FormatConditions.Add( Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlNotBetween, low, high , System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); fc.Font.ColorIndex = 3; } } } }
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 }
public void CreateMainUltimateLoadsSheet() { Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets["MainUltimateLoads"]; if (bladedDatas.Count < 1) { Console.WriteLine("CreateMainUltimateLoadsSheet-bladedDatas.Count < 1 error!"); } else { int col = 0; int colCount = 5; foreach (BladedData b in bladedDatas) { int row = 0; int rowCount = 10; //机组名称 Excel.Range rb = ws.get_Range(ws.Cells[row + 1, col + 1], ws.Cells[row + 1, col + colCount]); rb.Merge(); rb.Value = b.turbineMainCompenontResult.turbineID; //主要部件名称和数据 var comBase = bladedDatas[0].turbineMainCompenontResult.results.ultmateData.component; foreach (var com in b.turbineMainCompenontResult.results.ultmateData.component) { Excel.Range rHeader = ws.get_Range(ws.Cells[row + 2, col + 1], ws.Cells[row + 2, col + colCount]); //主要部件名称 rHeader.Merge(); rHeader.Value = com.name; Excel.Range rHeade1 = ws.get_Range(ws.Cells[row + 3, col + 2], ws.Cells[row + 3, col + colCount]); //主要部件名称 rHeade1.Value = new string[4] { "DLC", "Value", "Path", "Div" }; //数据放入excel表中 Excel.Range rData = ws.get_Range(ws.Cells[row + 4, col + 1], ws.Cells[(row + 4 + rowCount - 2), col + colCount]); rData.Value = com.resultMatrix; for (int i = 0; i < 8; i++) { string basediv = String.Format("R{0}C{1}", (row + 4 + i), 3); string div = String.Format("R{0}C{1}", (row + 4 + i), col + 3); Excel.Range comP = ws.get_Range(ws.Cells[row + 4 + i, col + colCount], ws.Cells[row + 4 + i, col + colCount]); comP.FormulaR1C1 = "=" + div + "/" + basediv; } Excel.Range formatCell = ws.get_Range(ws.Cells[row + 1, col + 1], ws.Cells[row + rowCount + 1, col + colCount]); formatCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //转化为数字,将>1.05的数值标红 Excel.Range numData = ws.get_Range(ws.Cells[row + 4, col + colCount], ws.Cells[row + 4 + rowCount - 2, col + colCount]); Excel.FormatCondition condition1 = (Excel.FormatCondition)numData.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, "1.05", Type.Missing); condition1.Interior.Color = 13551615; row = row + rowCount + 1; } col = col + colCount + 1; } } }
public void SetFormula() { try { Excel.Range rg; rg = this.Range["T" + (TITLE_ROW_COUNT + 1).ToString(), "T" + (TITLE_ROW_COUNT + DataCount).ToString()]; rg.Formula = string.Format("=R{0}+S{0}", TITLE_ROW_COUNT + 1); rg = this.Range["V" + (TITLE_ROW_COUNT + 1).ToString(), "V" + (TITLE_ROW_COUNT + DataCount).ToString()]; rg.Formula = string.Format("=R{0}+U{0}", TITLE_ROW_COUNT + 1); rg = this.Range["X" + (TITLE_ROW_COUNT + 1).ToString(), "X" + (TITLE_ROW_COUNT + DataCount).ToString()]; rg.Formula = string.Format("=S{0}+W{0}", TITLE_ROW_COUNT + 1); rg = this.Range["Y" + (TITLE_ROW_COUNT + 1).ToString(), "Y" + (TITLE_ROW_COUNT + DataCount).ToString()]; if (Globals.ThisWorkbook.TNTPlanCheck.Q1 == 0 && Globals.ThisWorkbook.TNTPlanCheck.Q3 == 0) { rg.Formula = string.Format("=X{0}", TITLE_ROW_COUNT + 1); } else if (Globals.ThisWorkbook.TNTPlanCheck.Q2 == 0 && Globals.ThisWorkbook.TNTPlanCheck.Q4 == 0) { rg.Formula = string.Format("=V{0}", TITLE_ROW_COUNT + 1); } else { rg.Formula = string.Format("=V{0}+X{0}", TITLE_ROW_COUNT + 1); } rg = this.Range["Z" + (TITLE_ROW_COUNT + 1).ToString(), "Z" + (TITLE_ROW_COUNT + DataCount).ToString()]; rg.Formula = string.Format("=IF(OR(AND(DashBoard!$J$2<>\"\",V{0}=0),AND(DashBoard!$J$3<>\"\",X{0}=0)),\"医院对应的岗位指标为0,这家医院的销量将无法计入该岗位\",\"\")", TITLE_ROW_COUNT + 1); // 设置条件公式. Excel.FormatCondition cond = rg.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlNotEqual, "\"\""); cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); rg = this.Range["AA" + (TITLE_ROW_COUNT + 1).ToString(), "AA" + (TITLE_ROW_COUNT + DataCount).ToString()]; cond = rg.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "FALSE"); cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); rg = this.Range["AB" + (TITLE_ROW_COUNT + 1).ToString(), "AB" + (TITLE_ROW_COUNT + DataCount).ToString()]; cond = rg.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "TRUE"); cond.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } catch (Exception ex) { LogHelper.WriteError("", ex); } }
private void ColorColumnForPNCSpec(Excel.Worksheet worksheet, int column) { Excel.Range Start = worksheet.Cells[1, 2]; Excel.Range Finish = worksheet.Cells[1000, 2 + (2 * column)]; worksheet.Range[Start, Finish].Font.Color = Color.Red; Start = worksheet.Cells[1, 3 + (2 * column)]; Finish = worksheet.Cells[1000, 3 + (4 * column)]; worksheet.Range[Start, Finish].Font.Color = Color.Green; Start = worksheet.Cells[1, 4 + (4 * column)]; Finish = worksheet.Cells[1000, 4 + (5 * column)]; worksheet.Range[Start, Finish].Font.Color = Color.Red; Start = worksheet.Cells[1, 5 + (5 * column)]; Finish = worksheet.Cells[1000, 5 + (6 * column)]; worksheet.Range[Start, Finish].Font.Color = Color.Green; Start = worksheet.Cells[1, 7 + (6 * column)]; Finish = worksheet.Cells[1000, 7 + (6 * column)]; worksheet.Range[Start, Finish].Font.Color = Color.Red; Start = worksheet.Cells[1, 8 + (6 * column)]; Finish = worksheet.Cells[1000, 8 + (6 * column)]; worksheet.Range[Start, Finish].Font.Color = Color.Green; Start = worksheet.Cells[2, 9 + (6 * column)]; Finish = worksheet.Cells[1000, 9 + (6 * column)]; Excel.FormatCondition format = (Excel.FormatCondition)worksheet.Range[Start, Finish].FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, 0); format.Font.Color = Color.Green; format = (Excel.FormatCondition)worksheet.Range[Start, Finish].FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlLess, 0); format.Font.Color = Color.Red; Start = worksheet.Cells[2, 1]; Finish = worksheet.Cells[1000, 9 + (6 * column)]; worksheet.Range[Start, Finish].NumberFormat = "0"; }
public void ExportDataTable(DataTable table, ExcelExportParm parms) { int totalCount = parms.UseView ? table.DefaultView.Count : table.Rows.Count; ExportationStarted(this, new EventArgs <int>(table.Rows.Count)); new Thread(new ThreadStart(delegate { try { var excelApp = new ExcelApp.Application(); var excelWorkbook = excelApp.Workbooks.Add(); ExcelApp.Worksheet sheet = excelWorkbook.Worksheets.Add(); foreach (var sh in excelWorkbook.Worksheets.Cast <ExcelApp.Worksheet>().Where(sh => sh != sheet)) { sh.Delete(); } sheet.Name = string.IsNullOrWhiteSpace(table.TableName) ? "Table" : table.TableName; var cols = table.Columns.OfType <DataColumn>().Select(dc => dc.ColumnName).ToList(); var excelHeaders = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, cols.Count]]; excelHeaders.Value2 = cols.ToArray(); ApplyStyle(excelHeaders, parms.DefaultCellFormat); if (parms.HeaderFormat != null) { ApplyStyle(excelHeaders, parms.HeaderFormat); } var i = 2; foreach (DataRow dr in parms.UseView ? table.DefaultView.OfType <DataRowView>().Select(x => x.Row) : table.Rows.OfType <DataRow>()) { ProgressUpdated(this, new EventArgs <int>(i - 1)); var data = new string[cols.Count]; for (var j = 0; j < data.Length; ++j) { data[j] = dr[j].ToString(); } var excelRow = sheet.Range[sheet.Cells[i, 1], sheet.Cells[i, cols.Count]]; ApplyStyle(excelRow, parms.DefaultCellFormat); excelRow.Value2 = data; excelRow.Rows.AutoFit(); i++; } foreach (var colName in parms.ColumnParms.Keys) { var col = parms.ColumnParms[colName]; var colId = cols.IndexOf(colName) + 1; var xlCol = sheet.Range[sheet.Cells[2, colId], sheet.Cells[totalCount + 1, colId]]; if (col.EnumValues != null) { xlCol.Validation.Add(ExcelApp.XlDVType.xlValidateList , ExcelApp.XlDVAlertStyle.xlValidAlertInformation , ExcelApp.XlFormatConditionOperator.xlBetween , string.Join(";", col.EnumValues) , Type.Missing); xlCol.Validation.InCellDropdown = true; xlCol.Validation.ShowError = false; } var style = excelWorkbook.Styles.Add("col#" + colId); style.HorizontalAlignment = col.HorizontalAlignment; style.VerticalAlignment = col.VerticalAlignment; xlCol.Style = style; foreach (var fcond in col.FormatConditions) { ExcelApp.FormatCondition cond = xlCol.FormatConditions.Add(fcond.Type, fcond.Operator, fcond.Formula); ApplyBorderStyle(parms.DefaultCellFormat.Border, cond.Borders, true); ApplyFontStyle(parms.DefaultCellFormat, cond.Font); ApplyInteriorStyle(parms.DefaultCellFormat, cond.Interior); ApplyBorderStyle(fcond.Format.Border, cond.Borders, true); ApplyFontStyle(fcond.Format, cond.Font); ApplyInteriorStyle(fcond.Format, cond.Interior); } ExcelApp.FormatCondition dummyCond = xlCol.FormatConditions.Add(ExcelApp.XlFormatConditionType.xlCellValue, ExcelApp.XlFormatConditionOperator.xlNotEqual, "I AM DUMB FOR DEFINING DEFAULT FORMAT"); ApplyBorderStyle(parms.DefaultCellFormat.Border, dummyCond.Borders, true); ApplyFontStyle(parms.DefaultCellFormat, dummyCond.Font); ApplyInteriorStyle(parms.DefaultCellFormat, dummyCond.Interior); } sheet.Application.ActiveWindow.SplitRow = 1; sheet.Application.ActiveWindow.FreezePanes = true; var firstRow = (ExcelApp.Range)sheet.Rows[1]; firstRow.AutoFilter(1, Type.Missing, ExcelApp.XlAutoFilterOperator.xlAnd, Type.Missing, true); firstRow.EntireColumn.AutoFit(); var firstcol = (ExcelApp.Range)sheet.Columns[1]; firstcol.EntireRow.AutoFit(); excelApp.Visible = true; ExportationEnded(this, new EventArgs <bool>(true)); } catch { ExportationEnded(this, new EventArgs <bool>(false)); } })).Start(); }
public void CreateMainEequivalentFatigueLoadsSheet() { Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets["MainEequivalentFatigueLoads"]; if (bladedDatas.Count < 1) { Console.WriteLine("CreateMainEequivalentFatigueLoadsSheet-bladedDatas.Count < 1 error!"); } else { int colStart = 1; int colCount = 4; foreach (BladedData b in bladedDatas) { int rowStart = 1; //机组名称 Excel.Range rb = ws.get_Range(ws.Cells[rowStart, colStart], ws.Cells[rowStart, colStart + colCount - 1]); rb.Merge(); rb.Value = b.turbineMainCompenontResult.turbineID; //主要部件名称和数据 var comBase = bladedDatas[0].turbineMainCompenontResult.results.equivalentFatigueData.component; foreach (var com in b.turbineMainCompenontResult.results.equivalentFatigueData.component) { Excel.Range rHeader = ws.get_Range(ws.Cells[rowStart + 1, colStart], ws.Cells[rowStart + 1, colStart + colCount - 1]); rHeader.Merge(); rHeader.Value = com.name; //只输出m=4&&m=10 string[,] tempMatrix = new string[7, 5]; for (int i = 0; i < 7; i++) { tempMatrix[i, 0] = com.resultMatrix[0, i]; //表头 tempMatrix[i, 1] = com.resultMatrix[2, i]; // tempMatrix[i, 2] = com.resultMatrix[8, i]; if (i == 0) { tempMatrix[i, 3] = com.resultMatrix[2, 0]; tempMatrix[i, 4] = com.resultMatrix[8, 0]; } } //变换输出格式 Excel.Range header1; for (int i = 0; i < 6; i++) { header1 = ws.get_Range(ws.Cells[rowStart + 3 + i * 2, colStart], ws.Cells[rowStart + 3 + i * 2 + 1, colStart]); header1.Merge(); header1.Value = tempMatrix[i + 1, 0];; } //列表头 string[] headerCol = { "m", "Value", "Div" }; Excel.Range header2 = ws.get_Range(ws.Cells[rowStart + 2, colStart + 1], ws.Cells[rowStart + 2, colStart + colCount - 1]); header2.Value = headerCol; //数据矩阵 float[,] dataMatrixTemp = new float[12, 3]; for (int i = 0; i < 6; i++) { dataMatrixTemp[2 * i, 0] = 4; dataMatrixTemp[2 * i + 1, 0] = 6; dataMatrixTemp[2 * i, 1] = Convert.ToSingle(tempMatrix[i + 1, 1]); dataMatrixTemp[2 * i + 1, 1] = Convert.ToSingle(tempMatrix[i + 1, 2]); } Excel.Range rData = ws.get_Range(ws.Cells[rowStart + 3, colStart + 1], ws.Cells[rowStart + 14, colStart + 2]); rData.Value = dataMatrixTemp; for (int i = 0; i < 12; i++) { string basediv = String.Format("R{0}C{1}", (rowStart + 3 + i), 3); string div = String.Format("R{0}C{1}", (rowStart + 3 + i), colStart + 2); Excel.Range comP = ws.get_Range(ws.Cells[rowStart + 3 + i, colStart + 3], ws.Cells[rowStart + 3 + i, colStart + 3]); comP.FormulaR1C1 = "=" + div + "/" + basediv; } Excel.Range formatCell = ws.get_Range(ws.Cells[rowStart, colStart], ws.Cells[rowStart + 15, colStart + colCount]); formatCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //转化为数字,将>1.05的数值标红 Excel.Range numData = ws.get_Range(ws.Cells[rowStart + 3, colStart + colCount - 1], ws.Cells[rowStart + 14, colStart + colCount - 1]); Excel.FormatCondition condition1 = (Excel.FormatCondition)numData.FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, "1.05", Type.Missing); condition1.Interior.Color = 13551615; rowStart = rowStart + 15; } colStart = colStart + 5; } } }
public static void export2Excel(string heading1, string heading2, string heading3, DataGridView dgv, ListView lvcolumns) { Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; //excel.Windows.Application.ActiveWindow.DisplayGridlines = false; //excel.ScreenUpdating = false; try { worksheet = workbook.ActiveSheet; worksheet.Columns.ColumnWidth = 20; ////worksheet.Cells.HorizontalAlignment = ContentAlignment.MiddleCenter; worksheet.Columns.WrapText = true; worksheet.Cells[1, 1] = heading1; worksheet.Cells[2, 1] = heading2; worksheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ////worksheet.get_Range("A1", "B2").HorizontalAlignment = ContentAlignment.MiddleLeft; ////worksheet.get_Range("A1", "B2").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; worksheet.get_Range("A1", "B2").WrapText = false; worksheet.get_Range("A1", "B2").Font.Bold = true; worksheet.get_Range("A1", "B2").Font.Size = 15; worksheet.get_Range("A1", "B2").Font.Color = Color.SeaGreen; string[] detail = { }; if (heading3.Length != 0) { detail = heading3.Split(Main.delimiter2); } int k = 3; for (int i = 0; i < detail.Length; i++) { try { DateTime temp; string str1 = detail[i].Substring(0, detail[i].IndexOf(Main.delimiter1)); string str2 = detail[i].Substring(detail[i].IndexOf(Main.delimiter1) + 1); worksheet.Cells[k, 1] = str1; if (DateTime.TryParse(str2, out temp)) { worksheet.Cells[k, 2] = (Convert.ToDateTime(str2)).Date; } else { worksheet.Cells[k, 2] = str2; } } catch (Exception ex) { MessageBox.Show("export2Excel() : ERROR in header String format"); } k++; } worksheet.Name = heading1; int col = 0; DateTime dt; //---------16/10/2017 int[] tArray = new Int32[dgv.ColumnCount]; int colCount = 0; int colNumber = 0; foreach (ListViewItem itemRow in lvcolumns.Items) { if (itemRow.Checked) { int cNumber = dgv.Columns.IndexOf(dgv.Columns[itemRow.SubItems[3].Text]); tArray[colNumber] = cNumber; colNumber++; } colCount++; } for (int i = colNumber; i < tArray.Length; i++) { tArray[i] = -1; } //int row = 3; int row = k; //print heading //Boolean flip = true; for (int i = 0; i < colNumber; i++) { int cNumber = tArray[i]; worksheet.Cells[row, i + 1] = dgv.Columns[cNumber].HeaderText; worksheet.Cells[row, i + 1].Interior.Color = Color.Orange; } row++; //Using Range int a = 0; object[,] data = new object[dgv.Rows.Count, dgv.Columns.Count]; foreach (DataGridViewRow rw in dgv.Rows) { int b = 0; foreach (DataGridViewCell c in rw.Cells) { if (tArray.Contains(c.ColumnIndex)) { data[a, b] = c.Value; b++; } } a++; } worksheet.Range[worksheet.Cells[row, 1], worksheet.Cells[dgv.Rows.Count + k, dgv.Columns.Count]].value = data; int lastrow = worksheet.UsedRange.Rows.Count; int lastcolumn = worksheet.UsedRange.Columns.Count; Microsoft.Office.Interop.Excel.Range rng = worksheet.Range[worksheet.Cells[row, 1], worksheet.Cells[lastrow, lastcolumn]]; Microsoft.Office.Interop.Excel.FormatCondition format = rng.Rows.FormatConditions. Add(Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlEqual, "=MOD(ROW(),2)=0"); format.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightGreen; Microsoft.Office.Interop.Excel.Range rangeData = excel.Range[worksheet.Cells[row, 1], worksheet.Cells[lastrow, lastcolumn]]; rangeData.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Microsoft.Office.Interop.Excel.Range rangeHead = excel.Range[worksheet.Cells[row - 1, 1], worksheet.Cells[row - 1, lastcolumn]]; rangeHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //----------- //excel.ScreenUpdating = false; SaveFileDialog saveDialog = new SaveFileDialog(); ////saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx"; saveDialog.FilterIndex = 2; saveDialog.OverwritePrompt = false; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } workbook.Close(false, Type.Missing, Type.Missing); } catch (Exception ex) { MessageBox.Show("Export Failed"); } }
/// <summary> /// Formatta il blocco entità. /// </summary> /// <param name="siglaEntita">Sigla entità.</param> /// <param name="desEntita">Descrizione.</param> /// <param name="codiceRUP">Codice RUP.</param> protected void FormattaBloccoEntita(object siglaEntita, object desEntita, object codiceRUP) { Range rngMercatoPrec = new Range(); //Titolo Range rng = new Range(_definedNames.GetRowByName(siglaEntita, "T"), _struttura.colBlock, 1, 10); Style.RangeStyle(_ws.Range[rng.ToString()], fontSize: 12, merge: true, bold: true, align: Excel.XlHAlign.xlHAlignCenter, borders: "[top:medium,right:medium,bottom:medium,left:medium]"); _ws.Range[rng.ToString()].Value = "PROGRAMMA A 15 MINUTI " + desEntita; _ws.Range[rng.ToString()].RowHeight = 25; //Data rng = new Range(_definedNames.GetRowByName(siglaEntita, "DATA"), _struttura.colBlock, 1, 5); Style.RangeStyle(_ws.Range[rng.ToString()], fontSize: 10, bold: true, align: Excel.XlHAlign.xlHAlignCenter, borders: "[top:medium,right:medium,bottom:medium,left:medium,insidev:medium]", numberFormat: "dd/MM/yyyy"); _ws.Range[rng.ToString()].RowHeight = 18; _ws.Range[rng.Columns[0].ToString()].Value = "Data"; _ws.Range[rng.Columns[1, 3].ToString()].Merge(); _ws.Range[rng.Columns[1].ToString()].Value = Workbook.DataAttiva; _ws.Range[rng.Columns[4].ToString()].Value = _mercato; //Tabella DataTable categoriaEntita = Workbook.Repository[DataBase.TAB.CATEGORIA_ENTITA]; DataView informazioni = Workbook.Repository[DataBase.TAB.ENTITA_INFORMAZIONE].DefaultView; List <DataRow> entitaRif = (from r in categoriaEntita.AsEnumerable() where r["IdApplicazione"].Equals(Workbook.IdApplicazione) && r["Gerarchia"].Equals(siglaEntita) select r).ToList(); bool hasEntitaRif = entitaRif.Count > 0; int numEntita = Math.Max(entitaRif.Count, 1); rng = new Range(_definedNames.GetRowByName(siglaEntita, "UM", "T"), _struttura.colBlock, 1, 5 * numEntita); for (int i = 0; i < numEntita; i++) { informazioni.RowFilter = "SiglaEntita = '" + siglaEntita + "' AND Visibile = '1' " + (hasEntitaRif ? "AND SiglaEntitaRif = '" + entitaRif[i]["SiglaEntita"] + "'" : "") + " AND IdApplicazione = " + _appID; //range grande come tutta la tabella rng = new Range(_definedNames.GetRowByName(siglaEntita, "UM", "T"), _definedNames.GetColFromName("RIF" + (i + 1), "PROGRAMMAQ1") - 1, Date.GetOreGiorno(Workbook.DataAttiva) + 2, 5); Style.RangeStyle(_ws.Range[rng.ToString()], borders: "[top:medium,right:medium,bottom:medium,left:medium,insideH:thin,insideV:thin]", align: Excel.XlHAlign.xlHAlignCenter, numberFormat: "general"); Style.RangeStyle(_ws.Range[rng.Rows[1, rng.Rows.Count - 1].Columns[0].ToString()], backColor: 15, bold: true, align: Excel.XlHAlign.xlHAlignLeft); Style.RangeStyle(_ws.Range[rng.Rows[0].ToString()], backColor: 15, bold: true, fontSize: 11); Style.RangeStyle(_ws.Range[rng.Rows[1].ToString()], backColor: 15, bold: true); _ws.Range[rng.Rows[0].Columns[1, rng.Columns.Count - 1].ToString()].Merge(); if (hasEntitaRif) { _ws.Range[rng.Rows[0].ToString()].Value = new object[] { "UM", entitaRif[i]["CodiceRUP"] is DBNull ? entitaRif[i]["DesEntita"] : entitaRif[i]["CodiceRUP"] } } ; else { _ws.Range[rng.Rows[0].ToString()].Value = new object[] { "UM", codiceRUP is DBNull ? desEntita : codiceRUP } }; for (int h = 1; h <= Date.GetOreGiorno(Workbook.DataAttiva); h++) { _ws.Range[rng.Columns[0].Rows[h + 1].ToString()].Value = "Ora " + h; } var isOrario = informazioni .OfType <DataRowView>() .Any(r => r["SiglaInformazione"].ToString().StartsWith("PROGRAMMA_")); if (!isOrario) { for (int j = 0; j < 4; j++) { _ws.Range[rng.Rows[1].Columns[j + 1].ToString()].Value = 15 * j + "-" + 15 * (j + 1); } } else { _ws.Range[rng.Cells[1, 1].ToString()].Value = "0-60"; } //TODO controllare che non ci siano problemi if (_mercato != "MSD1") { string mercatoPrec = Simboli.GetMercatoPrec(_mercato); //calcolo il range nel foglio del mercato precedente (non è detto che siano nella stessa posizione (anche se non ha senso che non lo siano...)) rngMercatoPrec = new Range(_definedNamesMercatoPrec.GetRowByName(siglaEntita, "UM", "T"), _definedNamesMercatoPrec.GetColFromName("RIF" + (i + 1), "PROGRAMMAQ1") - 1, Date.GetOreGiorno(Workbook.DataAttiva) + 2, 5); Excel.FormatCondition condGreater = _ws.Range[rng.Rows[2, rng.Rows.Count - 1].Columns[1, 4].ToString()].FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Formula1: "=" + rng.Cells[2, 1] + " > '" + mercatoPrec + "'!" + rngMercatoPrec.Cells[2, 1]); condGreater.Interior.ColorIndex = Struct.COLORE_VARIAZIONE_POSITIVA; Excel.FormatCondition condLess = _ws.Range[rng.Rows[2, rng.Rows.Count - 1].Columns[1, 4].ToString()].FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Formula1: "=" + rng.Cells[2, 1] + " < '" + mercatoPrec + "'!" + rngMercatoPrec.Cells[2, 1]); condLess.Interior.ColorIndex = Struct.COLORE_VARIAZIONE_NEGATIVA; } } }