如何为这段代码格式化导出的Excel工作表？ [英] How do I format my exported excel sheet for this piece of code?

问题描述

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

推荐答案

您可以将单元格格式化为你正在添加数据，即

You can format the cell as you are adding the data i.e. before

xlWorkSheet.Cells[i + 1, j + 1] = data;

你可以把

Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "a1");
formatRange.EntireRow.Font.Bold = true;

（取自和）来自将格式应用于单元格 [ ^ ]

(Taken from and) other examples of applying format to cell[^]

这篇关于如何为这段代码格式化导出的Excel工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！