Need to export data sql to excel

[Mohd Irshad Hasmat Ansari]

I m developing a application where I have to make a report from sql server to excel. I don’t know how to do that please somebody help me.

Sql server à C# à excel

 

I need to import some particular column only

 

Thanks

Irshad

[Paul]

First hit on Google. You can bypass C#.

 

http://www.mssqltips.com/tip.asp?tip=1202

--
_______________________________

Take care,
Paul

It is not we non-interventionists who are isolationists. The real isolationists are those who impose sanctions and embargos on countries and peoples across the globe and who choose to use force overseas to promote democracy. A counterproductive approach that actually leads the U.S. to be more resented and more isolated in the world.

Dr. Ron Paul

www.RonPaul2008.com

[shin...@gmail.com]

hi Irshad

u can do tht:

protected void btnExcel_Click(object sender, EventArgs e)
{
DataSet dsExport = new DataSet();
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
DataGrid dgGrid = new DataGrid();

DataSet ds = new DataSet();
SqlParameter[] arrParamArgs = { new SqlParameter("",)

};
dsExport = dbl.GetDataSetProc("Proc_name", arrParamArgs);

dgGrid.DataSource = dsExport.Tables[0];

//Report Header
hw.WriteLine("<b><u><font size='5'>ATR Report</font></u></
b>");

//Get the HTML for the control.
dgGrid.HeaderStyle.Font.Bold = true;
dgGrid.DataBind();
dgGrid.RenderControl(hw);

//Write the HTML back to the browser.
Response.ContentType = "application/vnd.ms-excel";
Page.EnableViewState = false;

Response.Write(tw.ToString());
Response.End();
}



call this on export button click event.

TC
shini
On Sep 23, 8:53 pm, "Mohd Irshad Hasmat Ansari"

[dboy haha]

Here man this is large piece of the puzzle and this should run but it is missing a very important piece. Just look up file handles and how to release them.


void BtnConvertClick(object sender, System.EventArgs e)
        {                       
            excelApp = new Excel.ApplicationClass();
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo ("en-US");                                   
            workBook = excelApp.Workbooks.Add(Missing.Value);
             //progressbar setting            
            prgProg.Maximum =  chcklstTables.CheckedIndices.Count;

            int prgValue = 0;
            string TableName = string.Empty; //table name to naming the sheets
           
//all tablenames are in checklistbox, so check only checked items
            foreach(int indexChecked in chcklstTables.CheckedIndices)
            {   
                Application.DoEvents();
               
                prgValue++;
                 
                prgProg.Value = prgValue;                
               
                sqlCmd = "SELECT * FROM " + chcklstTables.Items[indexChecked].ToString();
                myDtExp = dbe.GetDataTable(connstr,sqlCmd);                                 
               
                TableName = chcklstTables.Items[indexChecked].ToString();               
               
                //check if table exists...
                if (myDtExp == null)
                {
                    lstProgress.Items.Add("TABLE " + TableName + " DOESN'T EXISTS IN DB...");
                    continue;
                }

                //if table name is more than 31 characters - max. lenght for Excel sheet name, shorten it...
                if(TableName.Length > 31)
                {
                    lstProgress.Items.Add("TABLE NAME " + TableName + " HAS BEEN SHORTED TO 31 CHARACTERS: " + TableName.Substring(0,31));
                    TableName = TableName.Substring(0,31);
                }               
               
                try
                {
                    //add new sheet to workbook...
                    sheet = (Excel.Worksheet) workBook.Sheets.Add(Missing.Value,Missing.Value,1,Excel.XlSheetType.xlWorksheet);
                    sheet.Name = TableName;                
                }
                catch(Exception ex1)
                {                   
                    lstProgress.Items.Add("TABLE " + TableName + " HAS PRODUCED ERROR");
                }
                //this code produces 100% CPU usage...          
                try
                {
                    int ColumnIndex=0;                      
                    foreach(DataColumn col in myDtExp.Columns)
                    {
                           Application.DoEvents();
                        ColumnIndex++;
                           excelApp.Cells[1,ColumnIndex]=col.ColumnName;                   
                    }
                                       
                    int rowIndex=0;
                    foreach(DataRow myRow in myDtExp.Rows)
                    {        
                        Application.DoEvents();
                        rowIndex++;
                        ColumnIndex=0;        
                        foreach(DataColumn col in myDtExp.Columns)        
                        { 
                            ColumnIndex++;               
                            excelApp.Cells[rowIndex+1,ColumnIndex]=myRow[col.ColumnName].ToString();
                        }
                    }
                }
                catch(Exception ex2)
                {                   
                    lstProgress.Items.Add("TABLE " + TableName + " HAS PRODUCED ERROR");
                }
                lstProgress.Items.Add(TableName + " ... Done");
            }
            //save workbook to specified folder...
            workBook.SaveAs("C:\\test.xls",Excel.XlFileFormat.xlXMLSpreadsheet,Missing.Value ,Missing.Value ,
            false, false, Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value ,
            Missing.Value ,Missing.Value ,Missing.Value ,Missing.Value);           
           
            //release objects from memory...
            myDtExp.Dispose();           
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            workBook = null;
            sheet = null;
            excelApp = null;           
           
            MessageBox.Show ("Operation done...");
        }   

On Tue, Sep 23, 2008 at 11:53 AM, Mohd Irshad Hasmat Ansari <irshad...@gmail.com> wrote:

[AstroDrabb]

On Tue, Sep 23, 2008 at 12:32 PM, dboy haha <houseofla...@gmail.com> wrote:

<snip>
 

//this code produces 100% CPU usage...          
try
{
  int ColumnIndex=0;                      
  foreach(DataColumn col in myDtExp.Columns)
  {
    Application.DoEvents();
    ColumnIndex++;
    excelApp.Cells[1,ColumnIndex]=col.ColumnName;                   
  }
           
  int rowIndex=0;
  foreach(DataRow myRow in myDtExp.Rows)
  {        
    Application.DoEvents();
    rowIndex++;
    ColumnIndex=0;        
    foreach(DataColumn col in myDtExp.Columns)        
    { 
      ColumnIndex++;               
      excelApp.Cells[rowIndex+1,ColumnIndex]=myRow[col.ColumnName].ToString();
    }
  }
}
catch(Exception ex2)
{                   
  lstProgress.Items.Add("TABLE " + TableName + " HAS PRODUCED ERROR");
}

Look at the Excel.Range object.  Much faster to fill in data and much less CPU usage.

using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;


Excel.Application oXL = null;
Excel._Workbook oWBEnrollment = null;
Excel._Worksheet oSheet = null;
Excel.Range oRng = null;
DataSet ds = null;
int[,] colValues = new int[12, 3];

oXL = new Excel.Application();

// open enrollment report
oWBEnrollment = (Excel._Workbook)(oXL.Workbooks._Open("myfile.xls", Missing.Value,
    false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));

// get worksheet
oSheet = (Excel._Worksheet)oWB.Sheets["Summary"];

ds = SqlHelper.ExecuteDataset(con, "sp_GetData_EnrollmentSummary");
if (ds.Tables[0].Rows.Count > 0)
{
    DataRow r = null;
    for (int i = 0; i < 12; i++)
    {
        r = ds.Tables[0].Rows[i];
        colValues[i, 0] = (r["Corp_EE"] == DBNull.Value ? 0 : int.Parse(r["Corp_EE"].ToString()));
        colValues[i, 1] = (r["Corp_EE"] == DBNull.Value ? 0 : int.Parse(r["OO_EE"].ToString()));
        colValues[i, 2] = (r["Corp_EE"] == DBNull.Value ? 0 : int.Parse(r["OO_Count"].ToString()));
    }
    oRng = oSheet.get_Range("B8", "D19");  // 12 rows by 3 cols
    oRng.Value2 = colValues;
}


Jim