Click here to Skip to main content
15,880,469 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
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)how do i start the database on line A2 of the Excel Spreadsheet?
2)How do i add column names for the data?
Posted
Updated 30-Aug-12 6:22am
v2

1 solution

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