using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using System.IO;
public partial class DataBasetoExcell : System.Web.UI.Page
{
//OleDbConnection excelConnection = new OleDbConnection("database=Prasad;uid=sa;pwd=sa123;server=.;");
SqlConnection strConnection = new SqlConnection("database=Prasad;uid=sa;pwd=sa123;server=.;");
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnDBtoExcell_Click(object sender, EventArgs e)
{
SqlDataAdapter daa = new SqlDataAdapter("select * from tblemp", strConnection);
DataSet ds = new DataSet();
daa.Fill(ds);
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
string tablename = string.Empty;
for (int b = 0; b < ds.Tables.Count; b++)
{
tablename = ds.Tables[0].TableName.ToString();
}
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; )
{
for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; )
{
//Below 2 Lines With Data and Columns
//string data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
//xlWorkSheet.Cells[i + 1, j + 1] = data.ToString();
// Below Foreach Only Column Names
foreach (DataColumn dc in ds.Tables[0].Columns)
{
xlWorkSheet.Cells[0 + 1, j + 1] = dc.ColumnName.ToString();
i++;
j++;
}
}
}
xlWorkBook.SaveAs(@"C:\" + tablename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
SqlDataAdapter da = new SqlDataAdapter("insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\\" + tablename + ";','SELECT * FROM [Sheet1$]') select * from tblemp ", strConnection);
//SqlDataAdapter da = new SqlDataAdapter("select * from tblemp", strConnection);
DataTable dt = new DataTable();
da.Fill(dt);
}
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();
}
}
}
No comments:
Post a Comment