none
Conditional coloring of the rows and column range in a excel sheet using Microsoft.Office.Interop RRS feed

  • Question

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



    • Moved by CoolDadTx Wednesday, December 6, 2017 2:22 PM Office related
    Wednesday, December 6, 2017 10:23 AM

All replies

  • 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.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, December 6, 2017 12:15 PM
  • Thanks but I'm not using SpreadSheetLight . I'm only using Microsoft.Office.Interop.Excel.Application. As this is a COM object so debugging is difficult. I can set colour on the rows but it is accepting only the last condition colour thus overriding the earlier colours. I can select the row but I cannot select the range i.e. Jobs till Remarks and pass it to Excel.Range range = Worksheet.get_Range which in may be passed to  range.Interior.Color which I think.
    Wednesday, December 6, 2017 12:38 PM
  • Thanks but I'm not using SpreadSheetLight . I'm only using Microsoft.Office.Interop.Excel.Application. As this is a COM object so debugging is difficult. I can set colour on the rows but it is accepting only the last condition colour thus overriding the earlier colours. I can select the row but I cannot select the range i.e. Jobs till Remarks and pass it to Excel.Range range = Worksheet.get_Range which in may be passed to  range.Interior.Color which I think.

    Yes I realize you are not using SpreadSheetLight, was just throwing this out there as it's less work to use than Excel automation.

    So have you tried setting a break-point and debug as suggested?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, December 6, 2017 1:36 PM
  • Thanks Karen for the response but here I'm using a dataTable and creating the excel file. Now the example which you had sited from Spreadsheetlight doesn't have such condition. I can get the rows and column range but how to use it to colour the rows and save the format is unknown to me.
    Wednesday, December 6, 2017 2:28 PM

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



    Hello Uddipto Banerji,

    "range" is the range of the whole range of the exported data and what you want to should just be the range of the specific row, right?

    So, I  would suggest you use another range to get the row range and then set its color.

    Here is the simply code.

     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;
                    Excel.Range rowRange;
                    for (int j = 0; j < RowsCount; j++)
                    {
                        for (int i = 0; i < ColumnsCount; i++)
                            Cells[j, i] = dataTable.Rows[j][i];
                        rowRange = range.Rows[j + 1];
                        if (dataTable.Rows[j][1].ToString() == "Failed")  // CHECKING CONDITION WITH THE DATATABLE
                        {
                            // USE TO COLOR THE ROW AND THE COLUMN RANGE 
                            rowRange.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 
                            rowRange.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 
                            rowRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                        }
                        else if (dataTable.Rows[j][1].ToString() == "Succeeded")
                        {
                            rowRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
                        }
                    }

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Terry Xu - MSFT Wednesday, December 20, 2017 2:11 AM
    Thursday, December 7, 2017 2:01 AM
  • Hello Uddipto Banerji,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 20, 2017 2:11 AM