如何使用c＃将sql datatable导出到Excel工作表？ [英] how to export sql datatable into excel sheet using c#?

问题描述

我开发了一个软件，通过点击menuitem导出Excel工作表。



所以现在我想得到列名。但这只会显示列数据吗？

我该怎么办？

  private   void  exportEnquiryToolStripMenuItem_Click（ object  sender，EventArgs e）
 {
 
 SqlConnection cnn; 
  string  connectionstring =  null ; 
  string  sql =  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  Microsoft.Office.Interop.Excel.Application（）; 
 xlWorkBook = xlApp.Workbooks.Add（misValue）; 
 xlWorkSheet =（Excel.Worksheet）xlWorkBook.Worksheets.get_Item（ 1 ）; 
 
 connectionstring =  数据源= PD-JANAKAN;初始目录= EnqInfo;集成安全性=真正的; 
 cnn =  new  SqlConnection（connectionstring）; 
 cnn.Open（）; 
 sql =   SELECT * FROM Inquiry; 
 SqlDataAdapter dscmd =  new  SqlDataAdapter（sql，cnn）; 
 DataSet ds =  new  DataSet（）; 
 dscmd.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 ]。行[i ] .ItemArray [j]的的ToString（）; 
 xlWorkSheet.Cells [i +  1 ，j +  1 ] = data; 
} 
} 
 
 
 xlWorkBook.SaveAs（  D：\\informations.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文件，你可以找到文件d：\\Sam-informations.xls）; 
} 
 
  private   void  releaseObject（ object  obj）
 {
  try  
 {
 System.Runtime.InteropServices .Marshal.ReleaseComObject（OBJ）; 
 obj =  null ; 
} 
  catch （例外情况）
 {
 obj =  null ; 
 MessageBox.Show（ 在发布对象时发生异常 + ex.ToString（）） ; 
} 
 最后 
 {
 GC.Collect（）; 
} 
 
} 
 
} 

解决方案

你不是将列标题分配给电子表格。在循环数据表之前，您需要指定它。



在旁注中，不要拨打 GC.Collect

i have developed a software to export a excel sheet by clicking menuitem.

so now i want to get column name as well. but this will show only column data?
What i have to do?

private void exportEnquiryToolStripMenuItem_Click(object sender, EventArgs e)
        {

            SqlConnection cnn;
            string connectionstring = null;
            string sql = 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 Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            connectionstring = "Data Source=PD-JANAKAN;Initial Catalog=EnqInfo;Integrated Security=True";
            cnn = new SqlConnection(connectionstring);
            cnn.Open();
            sql = "SELECT * FROM Enquiry";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.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("D:\\informations.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 D:\\Sam-informations.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();
            }
 
        }

        }

解决方案

You are not assigning column headers to the spreadsheet. You will need to assign that before you loop through data table.

On a side note, do not make a call to GC.Collect.

这篇关于如何使用c＃将sql datatable导出到Excel工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！