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