通过Visual Studio C#格式化Excel [英] Format Excel through Visual Studio C#

问题描述

1)how do i start the database on line A2 of the Excel Spreadsheet?
2)How do i add column names for the data?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace Final
{
    public partial class Excel2 : Form
    {
        public Excel2()
        {
          InitializeComponent(); 
        }
 

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


        private void Excel2_Load(object sender, EventArgs e)
        {
            Global.Dta = new SqlDataAdapter("SELECT Orders.OrderID, Customers.CompanyName, Customers.ContactName, Customers.Region, Orders.OrderDate, Orders.ShippedDate, Orders.Freight FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID", Global.Con);
            Global.dts = new DataSet();
            Global.Dta.Fill(Global.dts);
            dgvEmployee.DataSource = Global.dts.Tables[0];
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            Excel.Range chart;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misvalue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            xlWorkSheet.get_Range("A1", "D1").Merge(false);
            chart = xlWorkSheet.get_Range("A1", "A4");
            chart.FormulaR1C1 = "ORDER";
            chart.HorizontalAlignment = 3;
            chart.VerticalAlignment = 3;

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

            xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misvalue, misvalue, misvalue, misvalue, Excel.XlSaveAsAccessMode.xlExclusive, misvalue, misvalue, misvalue, misvalue, misvalue);
            xlWorkBook.Close(true,misvalue,misvalue);
            xlApp.Quit();

            MessageBox.Show("Excel created");

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

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

1)如何在Excel电子表格的A2行上启动数据库?
2)如何为数据添加列名?

1)how do i start the database on line A2 of the Excel Spreadsheet?
2)How do i add column names for the data?

推荐答案

1)使用xlWorkSheet.Cells[i+2, j+1]=data，因此您将从A2开始
2)在excel中，没有列鬃毛.实际上，它们是从A命名的....您只需在数据上方的第一行中添加字段名称

1) Use xlWorkSheet.Cells[i+2, j+1]=data, thus you will start from A2
2) In excel there are no column manes. Actually they are named from A.... You can simply add to the first row above your data the field names

这篇关于通过Visual Studio C#格式化Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！