Click here to Skip to main content
15,887,376 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have an excel sheet with some template in it.
Now i need to fetch records from SQL server 2008 and display in
front of that template [means some header (columns and rows) in the excel file] and beside that i want to copy records from sql server 2008. So wanted to know which way i can move forward. I am using C#.net in front end. Please guide me on thisCan you guide me further?

Template
Equipment Sub System States / Events Crew Set Up Pre - Breakfast Peak Breakfast Pre Lunch Peak Lunch Post Lunch Peak Dinner Post Dinner Crew Shut Down No Operations
Energy Consumption (KWH) Gas Consumption (Cu
I have this code for exporting to excel
C#
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.Configuration;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel; 


namespace ImportDataToExcelSP
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
SqlConnection con;
string connectionString = 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=10.207.40.14;Initial Catalog=QSR_DB_CSV;User ID=sa;Password=wipro@123;";
textBox1.Text = dateTimePicker1.Value.ToString();
con = new SqlConnection(connectionString);
con.Open();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "Retrieve_ALL_data";
sqlCmd.Parameters.Add(new SqlParameter( "@point_location",SqlDbType.VarChar,50));
sqlCmd.Parameters["@point_location"].Value = comboBox1.SelectedItem.ToString();
sqlCmd.Parameters.Add(new SqlParameter("@log_date", SqlDbType.VarChar, 30));
sqlCmd.Parameters["@log_date"].Value = textBox1.Text.ToString();

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
da.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("Template_Energy_data_analytics_McD.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 MyDocuments:\\Template_Energy_data_analytics_McD.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();
}
}

}
}

Can you Can you guide me with the further code?
Posted
Updated 23-May-12 19:33pm
v2
Comments
Wendelius 24-May-12 17:37pm    
What is the exact problem/question you're facing? You seem to have used interop succesfully.
Kiran S.V 25-May-12 3:40am    
You are the only person who reverted back to me thank you very much.

I have successfully records from DataBase to Excel using COM interop but the real problem is i want to retain the previous template which is existing in Excel after adding the records from Database as these template.

(Eg: Equipment Sub System States shows the mapping of record from Database (In which i am able to get the state of the equipment record) to Excel).
Eg: Pre - Breakfast (record from database showing state of device on Pre-Breakfast)

So please guide me how can i retain previously existing template in Excel and add the records from Data Base corresponding to it.



Template
Equipment Sub System States / Events Crew Set Up
Pre - Breakfast
Peak Breakfast
Pre Lunch
Peak Lunch
Post Lunch
Peak Dinner
Post Dinner
Crew Shut Down
No Operations
Energy Consumption (KWH)
Gas Consumption (Cu

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