Click here to Skip to main content
15,892,298 members
Please Sign up or sign in to vote.
1.00/5 (4 votes)
See more:
C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections;
using Excel= Microsoft.Office.Interop.Excel;



namespace AutoStatus
{


    public partial class genesis : Form
    {

        bool AppAdded = false;
        SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\v11.0%%");

        public genesis()
        {
            InitializeComponent();
            getrelease();

        }

        private void getrelease()
        {
            conn.Open();

            SqlDataAdapter Adapter = new SqlDataAdapter("SELECT distinct release FROM mytable", conn);
            DataSet DS = new DataSet();
            Adapter.Fill(DS, "Adapter");
            comboBox1.ValueMember = "release";
            comboBox1.DataSource = DS.Tables["Adapter"];
            comboBox1.Enabled = true;


            conn.Close();
            getapp();

        }
        private void getapp()
        {

            conn.Open();

            SqlDataAdapter Adap = new SqlDataAdapter("SELECT distinct app FROM mytable", conn);
            DataSet ds = new DataSet();
            Adap.Fill(ds, "Adap");
            comboBox2.ValueMember = "app";
            comboBox2.DataSource = ds.Tables["Adap"];
            comboBox2.Enabled = true;
            AppAdded = true;

            conn.Close();

           
        }



        private void getcomponents()
        {
            string s = comboBox2.SelectedValue.ToString();

            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter("select distinct components from mytable where app='" + s + "'", conn);
            DataSet ds = new DataSet();
            da.Fill(ds); 
            comboBox3.DataSource = ds.Tables[0];
            comboBox3.DisplayMember = "components";
            comboBox3.ValueMember = "components";

            conn.Close();
        }



        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.AppAdded)
                getcomponents();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            String query = "insert into Data (release,app,components,overallstatus,fsd,estimate,noqc,numberq,openq,devstatus,ftstatus,uatstatus,releasestatus,comment) VALUES (@release,@app,@components,@overallstatus,@fsd,@estimate,@noqc,@numberq,@openq,@devstatus,@ftstatus,@uatstatus,@releasestatus,@comment)";
            /*"values ('" + comboBox1.Text + "','" + comboBox2.Text + "','"
                        + comboBox3.Text + "','" + comboBox4.Text + "','"
                        + dateTimePicker1.Value + "','" + textBox1.Text + "','"
                        + textBox2.Text + "','" + textBox3.Text + "','"
                        + textBox4.Text + "','" + dateTimePicker2.Text + "','"
                        + dateTimePicker3.Text + "','" + dateTimePicker4.Text + "','"
                        + comboBox5.Text + "','" + textBox5.Text + "')";*/

            SqlDataReader dbr;
            SqlCommand cmd = new SqlCommand(query, conn);


           
            cmd.Parameters.AddWithValue("@release",comboBox1.Text);
            cmd.Parameters.AddWithValue("@app", comboBox2.Text);
            cmd.Parameters.AddWithValue("@components", comboBox3.Text);
            cmd.Parameters.AddWithValue("@overallstatus", comboBox4.Text);
            cmd.Parameters.AddWithValue("@fsd", Convert.ToString(dateTimePicker1.Value.Date));
            cmd.Parameters.AddWithValue("@estimate", textBox1.Text);
            cmd.Parameters.AddWithValue("@noqc", textBox2.Text);
            cmd.Parameters.AddWithValue("@numberq", textBox3.Text);
            cmd.Parameters.AddWithValue("@openq", textBox4.Text);
            cmd.Parameters.AddWithValue("@devstatus", Convert.ToString(dateTimePicker1.Value.Date));
            cmd.Parameters.AddWithValue("@ftstatus", Convert.ToString(dateTimePicker1.Value.Date));
            cmd.Parameters.AddWithValue("@uatstatus", Convert.ToString(dateTimePicker1.Value.Date));
            cmd.Parameters.AddWithValue("@releasestatus", comboBox5.Text);
            cmd.Parameters.AddWithValue("@comment", textBox5.Text);
            


            try
            {
                conn.Open();
                dbr = cmd.ExecuteReader();
                MessageBox.Show("saved");
                while (dbr.Read())
                {
                }
            }
            catch (Exception es)
            {
                MessageBox.Show(es.Message);
            }
            conn.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {

            //SqlConnection cnn ;
            //string connectionString = null;
            string sql = null;
            string data = null;
            int i = 0;
            int j = 0;

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
            //cnn = new SqlConnection(connectionString);
            conn.Open();
            sql = "SELECT * FROM Data";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);

            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                    xlWorkSheet.Cells[i + 1, j + 1] = data;
                }
            }

            xlWorkBook.SaveAs("csharp.net-StatusDashboard.xls", Excel.XlFileFormat.xlWorkbookNormal, 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);

            MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-StatusDashboard.xls");
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}
Posted
Updated 12-Oct-15 2:27am
v2
Comments
CHill60 12-Oct-15 8:30am    
You can format it however you like. What is your problem?
Member 11978212 12-Oct-15 10:43am    
How to do it via coding?
Sinisa Hajnal 12-Oct-15 11:00am    
What is your particular problem (for example, I need third column with green background and every third row with red letters)...
What have you tried? And which part of your code doesn't work as you want it to? Post only relevant code, not whole program. Thank you.

1 solution

You can format the cell as you are adding the data i.e. before
C#
xlWorkSheet.Cells[i + 1, j + 1] = data;
You could put
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "a1");
formatRange.EntireRow.Font.Bold = true;
(Taken from and) other examples of applying format to cell[^]
 
Share this answer
 

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