使用Microsoft.Office.Interop在Excel工作表中对行和列范围进行条件着色 [英] Conditional coloring of the rows and column range in a excel sheet using Microsoft.Office.Interop
本文介绍了使用Microsoft.Office.Interop在Excel工作表中对行和列范围进行条件着色的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
大家好，
我有一个dataTable，想要使用 Microsoft将数据插入excel文件.Office.Interop。我正在迭代dataTable并检查每一行和特定列，并根据我要为行着色的特定列值。我面临
问题我在评论的部分。现在，如果我使用下面的代码，它将采用最后一种颜色并覆盖其他颜色。你能帮我吗
假设我的条件为"失败"和"正在运行" ""Interrupted"和 "成功"需要与dataTable中的特定列进行比较，并根据它我需要为
数据的背景着色。
输出应该是
使用系统;
使用System.Collections.Generic;使用System.Drawing
;
使用System.Linq;
使用System.Text;
使用System.Threading.Tasks;
使用Excel = Microsoft.Office.Interop.Excel;
使用log4net;
使用System.Data;
命名空间ReadAppendExcel
{
公共静态类DataTable_Extensions
{
///< summary>
///将DataTable导出到Excel文件
///< / summary>
///< param name =" DataTable"> Source DataTable< / param>
///< param name =" ExcelFilePath">结果文件名的路径< / param>
private static readonly log4net.ILog log = log4net.LogManager.GetLogger（System.Reflection.MethodBase.GetCurrentMethod（）。DeclaringType）;
public static void ExportToExcel（this System.Data.DataTable DataTable，string ExcelFilePath = null）
{
log4net.Config.BasicConfigurator.Configure（）;
ILog log = log4net.LogManager.GetLogger（typeof（Program））;
try
{
int ColumnsCount;
log.Info（"在ExportToExcel函数中"）;
if（DataTable == null ||（ColumnsCount = DataTable.Columns.Count）== 0）
抛出新的异常（"ExportToExcel：Null或空输入表！\ n"）;
//加载excel，并创建一个新工作簿
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application（）;
Excel.Workbooks.Add（）;
//单个工作表
Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;
int RowsCount = DataTable.Rows.Count;
object [] Header = new object [ColumnsCount];
object [] RowsCol = new object [RowsCount];
//列标题
for（int i = 0; i< ColumnsCount; i ++）
Header [i] = DataTable.Columns [i] .ColumnName;
Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range（（Microsoft.Office.Interop.Excel.Range）（Worksheet.Cells [1,1]），（Microsoft.Office。 Interop.Excel.Range）（Worksheet.Cells [1，ColumnsCount]））;
HeaderRange.Value = Header;
HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Orange）;
HeaderRange.Font.Bold = true;
// DataCells
object [，] Cells = new object [RowsCount，ColumnsCount];
Excel.Range range = Worksheet.get_Range（（Microsoft.Office.Interop.Excel.Range）（Worksheet.Cells [2,1]），（Microsoft.Office.Interop.Excel.Range）（Worksheet.Cells [RowsCount + 1，ColumnsCount]））;
range.Value = RowsCol;
for（int j = 0; j< RowsCount; j ++）
{
for（int i = 0; i< ColumnsCount; i ++）
Cells [j，i ] = DataTable.Rows [j] [i];
if（DataTable.Rows [j] [1] .ToString（）==" Failed"）//用DATATABLE检查条件
{
//用于着色行和COLUMN RANGE
range.Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Red）;
}
else if（DataTable.Rows [j] [1] .ToString（）==" Running"）
{
// USE TO COLOR行和列范围
range.Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Yellow）;
}
else if（DataTable.Rows [j] [1] .ToString（）==" Interrupted"）
{
//用于着色行和THE COLUMN RANGE
range.Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Yellow）;
}
else if（DataTable.Rows [j] [1] .ToString（）==" Succeeded"）
{
range.EntireRow.Interior.Color = System .Drawing.ColorTranslator.ToOle（System.Drawing.Color.White）;
}
}
Worksheet.get_Range（（Microsoft.Office.Interop.Excel.Range）（Worksheet.Cells [2,1]），（Microsoft.Office.Interop.Excel.Range ）（Worksheet.Cells [RowsCount + 1，ColumnsCount]））。Value = Cells;
range.Borders [Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom] .Color = Color.Black.ToArgb（）;
range.Borders [Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft] .Color = Color.Black.ToArgb（）;
range.Borders [Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight] .Color = Color.Black.ToArgb（）;
range.Borders [Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop] .Color = Color.Black.ToArgb（）;
range.Borders [Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal] .Color = Color.Black.ToArgb（）;
range.Borders [Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical] .Color = Color.Black.ToArgb（）;
//检查fielpath
if（ExcelFilePath！= null&& ExcelFilePath！=""）
{
try
{
Worksheet .SaveAs（ExcelFilePath）;
Excel.Quit（）;
log.Info（" excel successfully created"）;
}
catch（exception ex）
{
log.Info（" ExportToExcel：Excel文件无法保存！检查filepath.\ n"+ ex.Message。的ToString（））;
抛出新的异常（"ExportToExcel：Excel文件无法保存！检查filepath.\"
+ ex.Message）;
}
}
其他//没有给出文件路径
{
Excel.Visible = true;
}
}
catch（Exception ex）
{
log.Info（" ExportToExcel：\ n" + ex.Message.ToString（））;
抛出新的异常（" ExportToExcel：\ n" + ex.Message）;
}
}
}
}
解决方案
确定不良结果的最佳方法之一是在if / else-上设置断点如果，通过查看代码并通过检查值以及如何评估它们来查看正在发生的事情。
如果您使用
SpreadSheetLight （免费库）您可以在一个范围内设置条件格式，并允许条件格式根据您在填充单元格之前设置的条件进行着色。 / p>
Hi All,
I have a dataTable and want to insert the data into excel file using Microsoft.Office.Interop. I'm iterating the dataTable and checking each row and particular column and based on the particular column value I want to colour the row. I'm facing issues with section where I have commented. Now below code if I use then it is taking the last colour and overriding the other colors. Could you please help me
Suppose I have condition as "Failed" and "Running" "Interrupted" and "Succeeded" which needs to be compared with the particular column in the dataTable and based on it I need to colour the background of the data.
Output should be
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using log4net;
using System.Data;
namespace ReadAppendExcel
{
public static class DataTable_Extensions
{
/// <summary>
/// Export DataTable to Excel file
/// </summary>
/// <param name="DataTable">Source DataTable</param>
/// <param name="ExcelFilePath">Path to result file name</param>
private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
{
log4net.Config.BasicConfigurator.Configure();
ILog log = log4net.LogManager.GetLogger(typeof(Program));
try
{
int ColumnsCount;
log.Info("In the ExportToExcel function");
if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");
// load excel, and create a new workbook
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks.Add();
// single worksheet
Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;
int RowsCount = DataTable.Rows.Count;
object[] Header = new object[ColumnsCount];
object[] RowsCol = new object[RowsCount];
// column headings
for (int i = 0; i < ColumnsCount; i++)
Header[i] = DataTable.Columns[i].ColumnName;
Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
HeaderRange.Value = Header;
HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange);
HeaderRange.Font.Bold = true;
// DataCells
object[,] Cells = new object[RowsCount, ColumnsCount];
Excel.Range range = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount]));
range.Value = RowsCol;
for (int j = 0; j < RowsCount; j++)
{
for (int i = 0; i < ColumnsCount; i++)
Cells[j, i] = DataTable.Rows[j][i];
if (DataTable.Rows[j][1].ToString() == "Failed") // CHECKING CONDITION WITH THE DATATABLE
{
// USE TO COLOR THE ROW AND THE COLUMN RANGE
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
else if (DataTable.Rows[j][1].ToString() == "Running")
{
// USE TO COLOR THE ROW AND THE COLUMN RANGE
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
}
else if (DataTable.Rows[j][1].ToString() == "Interrupted")
{
// USE TO COLOR THE ROW AND THE COLUMN RANGE
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
}
else if (DataTable.Rows[j][1].ToString() == "Succeeded")
{
range.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
}
}
Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Color = Color.Black.ToArgb();
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Color = Color.Black.ToArgb();
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Color = Color.Black.ToArgb();
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Color = Color.Black.ToArgb();
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = Color.Black.ToArgb();
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = Color.Black.ToArgb();
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
Worksheet.SaveAs(ExcelFilePath);
Excel.Quit();
log.Info("excel successfully created");
}
catch (Exception ex)
{
log.Info("ExportToExcel: Excel file could not be saved! Check filepath.\n" + ex.Message.ToString());
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
Excel.Visible = true;
}
}
catch (Exception ex)
{
log.Info("ExportToExcel: \n" + ex.Message.ToString());
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}
}
解决方案
One of the best methods to determine undesirable results is to set a break-point on the if/else-if, step through the code and see what is going on by inspecting the values and how they are being evaluated.
If you were using SpreadSheetLight (a free library) you could set conditional formatting over a range and allow the conditional formatting to color based on you condition set prior to populating cells.
这篇关于使用Microsoft.Office.Interop在Excel工作表中对行和列范围进行条件着色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
