Click here to Skip to main content
15,891,372 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am creating a Datatable in C# and export it in excel sheet...How to do in company column cells color by using loops, IF company name is wipro then yellow ELSE red.HELP!

What I have tried:

C#
namespace Datatable2Excel
{
    public partial class Form1 : Form
    {
        DataTable ctr_raw_table = new DataTable();
        public Form1()
        {
            
            InitializeComponent();
            
            ctr_raw_table.Columns.Add("Name", typeof(String));
            ctr_raw_table.Columns.Add("Company", typeof(String));
            ctr_raw_table.Columns.Add("Job", typeof(String));
            ctr_raw_table.Columns.Add("Level", typeof(String));
            ctr_raw_table.Columns.Add("ID", typeof(String));
            ctr_raw_table.Columns.Add("Address", typeof(String));
            string[] row = new string[] { "John", "Wipro", "Engineer", "0203794", "India", "Mumbai" };
            ctr_raw_table.Rows.Add(row);
            row = new string[] { "David", "TCS", "Engineer", "02065394", "India", "Thane" };
            ctr_raw_table.Rows.Add(row);
            row = new string[] { "Albert", "Infosys", "Engineer", "0206394", "India", "Mumbai" };
            ctr_raw_table.Rows.Add(row);
            row = new string[] { "Daniel", "IBM", "Engineer", "02036594", "India", "Mumbra" };
            ctr_raw_table.Rows.Add(row);
            row = new string[] { "James", "Accenture", "Engineer", "02560394", "India", "Bhiwandi" };
            ctr_raw_table.Rows.Add(row);
            row = new string[] { "Jacob", "L&T", "Engineer", "02036594", "India", "kalyan" };
            ctr_raw_table.Rows.Add(row);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Export_Ctr_Excel(ctr_raw_table, "Output");
        }
        private void Export_Ctr_Excel(DataTable tablelist, string excelFilename)
        {

            Microsoft.Office.Interop.Excel.Application objexcelapp = new Microsoft.Office.Interop.Excel.Application();
            objexcelapp.Application.Workbooks.Add(Type.Missing);
            objexcelapp.Columns.AutoFit();
            for (int i = 1; i < tablelist.Columns.Count + 1; i++)
            {
                Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)objexcelapp.Cells[1, i];
                xlRange.Font.Bold = -1;

                
                int Row = 1;
                int Column = 1;  

                Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)objexcelapp.Cells[Row, Column];
                rng.EntireColumn.Font.Bold = true;
                rng.EntireColumn.Font.Italic = true;
                rng.EntireColumn.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.HotPink);
                rng.EntireColumn.BorderAround();

                xlRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                xlRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                objexcelapp.Cells[1, i] = tablelist.Columns[i - 1].ColumnName;

                DataTable dt = tablelist.Columns[i];
                if  (DataColumnCollection.tablelist.Column.Company = "Wipro")

                {
                    Console.WriteLine(System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow));
                }
                else
                {
                    Console.WriteLine(System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red));
                }

            }
            for (int i = 0; i < tablelist.Rows.Count; i++)
            {
                for (int j = 0; j < tablelist.Columns.Count; j++)
                {
                    if (tablelist.Rows[i][j] != null)
                    {
                        Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)objexcelapp.Cells[i + 2, j + 1];

                        xlRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                        xlRange.Borders.Weight = 1d;
                        xlRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        objexcelapp.Cells[i + 2, j + 1] = tablelist.Rows[i][j].ToString();
                    }
                }
            }
            objexcelapp.Columns.AutoFit();
            System.Windows.Forms.Application.DoEvents();
            if (Directory.Exists("C:\\CTR_Data\\"))
            {
                objexcelapp.ActiveWorkbook.SaveCopyAs("C:\\CTR_Data\\" + excelFilename + ".xlsx");
            }
            else
            {
                Directory.CreateDirectory("C:\\CTR_Data\\");
                objexcelapp.ActiveWorkbook.SaveCopyAs("C:\\CTR_Data\\" + excelFilename + ".xlsx");
            }
            objexcelapp.ActiveWorkbook.Saved = true;
            System.Windows.Forms.Application.DoEvents();
            foreach (Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
            {
                proc.Kill();
            }
        }

        private object Worksheets(string v)
        {
            throw new NotImplementedException();

        }
    }
}
Posted
Updated 23-May-19 16:17pm
v3

Use formulas with conditional formatting

Use formulas with conditional formatting - Excel[^]
 
Share this answer
 
Comments
Abu Aamir Ahmad 24-May-19 6:45am    
Thank you so much for give me responce but i want to do it in my C# coding by using loops: If_else, can you please see my coding and say what can i do? Thanking you
Quote:
How to give in company column cells color, IF company name is wipro then yellow ELSE red...help

Guessing you know how to do it in Excel but not with interop.
Ask Excel to show you how your actions translate to code.
Record a macro with your actions (change cell color), then go to VBE editor (Alt+F11) and look at macro code, it is basically what you have to tell Interop with little adaptation.
[Update]
Quote:
i want to change cell color by using loops in c# not on excel sheet...i wish when i'll export my data table in excel it will already happens

This look very confuse.
As far as I can see, a Datatable is not a GUI component, and thus, DataTable cells don't have colors. But Excel cells have colors.
 
Share this answer
 
v4
Comments
Abu Aamir Ahmad 24-May-19 6:46am    
Thank you so much for give me responce but i want to do it in my C# coding by using loops: If_else, can you please see my coding and say what can i do? Thanking you
Patrice T 24-May-19 6:54am    
What is your problem ?
doing loop ? or changing cells color ?
Abu Aamir Ahmad 24-May-19 7:12am    
i want to change cell color by using loops in c# not on excel sheet...i wish when i'll export my data table in excel it will already happens
Abu Aamir Ahmad 24-May-19 12:30pm    
can you please correct my this code?...

DataTable dt = tablelist.Columns[i];
if (DataColumnCollection.tablelist.Column.Company = "Wipro")

{
Console.WriteLine(System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow));
}
else
{
Console.WriteLine(System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red));
}
Patrice T 24-May-19 12:47pm    
Sorry I don't use this.
Use Improve question to update your question.
So that everyone can pay attention to this information.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900