从数据库读出数据,写进excel

Question

我每次读100条数据,数据内容类似这个
1   jack    男 [email protected]   086-12345678   在线要求就是把数据按字段写进excel,每列1个字段
不能用数据流的方式生成excel,那样虽然是xls后缀,但是其实是txt
 SqlConnection conn = new SqlConnection(@"Server=.;Initial Catalog=person;Uid=sa;Pwd=111111;");
            conn.Open();
            SqlCommand cmd = new SqlCommand(@"SELECT TOP 10 * FROM users ", conn);
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            System.Data.DataTable t = ds.Tables[0];
            if (t == null) return;
            try
            {
               System.Reflection.Missing miss = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.ApplicationClass m_objExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                m_objExcel.Visible = false;
                Microsoft.Office.Interop.Excel.Workbooks m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook m_objBook = (Microsoft.Office.Interop.Excel.Workbook)(m_objBooks.Add(miss));
                Microsoft.Office.Interop.Excel.Worksheet m_objSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;
                Microsoft.Office.Interop.Excel.Range ers = m_objSheet.get_Range((object)"A1", System.Reflection.Missing.Value);
                //后面的实在不知道怎么写了,我是可以写死然后1列1列的插进去,但是字段很多,我不可能在代码里写满A1 B1 C1 D1 .....
               for (int i = 0; i < t.Rows.Count; i++)
                {
                    //Console.WriteLine(t.Rows[i][0].ToString());
                    //表格的自动编号strLine = strLine + (i+1) +Convert.ToChar(9);
                    for (int j = 0; j < t.Columns.Count; j++)
                    {
                        strLine = strLine + t.Rows[i][j].ToString() + Convert.ToChar(9);
                    }
                    //往EXCEL里写,但是不知道怎么写
                    strLine = "";                    m_objBook.SaveAs("d:\\aaa" + ".xls", miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
                    m_objBook.Close(false, miss, miss);
                    m_objBooks.Close();
                    m_objExcel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(ers);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
                    GC.Collect();
                    Console.WriteLine("OK");
                    Console.ReadLine();
                }
            }
            catch
            {            }求高手帮忙

Answer 1

错了,
for (int j = 0; j < t.Columns.Count; j++)
                    {
                        strLine = strLine + t.Rows[i][j].ToString() + Convert.ToChar(9);
                    }这个应该是在for的外边,是循环出字段名

Answer 2

遍历数据集打开EXCEL模板赋值给单元格
EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"' 
EXEC master..xp_cmdshell 'bcp "SELECT * FROM 表名" queryout C:\ a.xls -c -Sservername -Usa -Ppassword' 
public void ExportToExcel(string FileType, string FileName)
  {
  Response.Charset = "GB2312";
  Response.ContentEncoding = System.Text.Encoding.UTF8;
  Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());
  Response.ContentType = FileType;
  this.EnableViewState = false;
  StringWriter tw = new StringWriter();
  HtmlTextWriter hw = new HtmlTextWriter(tw);
  GridView1.RenderControl(hw);
  Response.Output.Write(tw.ToString());
  Response.Flush();
  Response.End();  }
  protected void Button1_Click(object sender, EventArgs e)
  {
  ExportToExcel("application/ms-excel","A.xls");  }

Answer 3

刚才的代码贴的有点乱,因为在测试,也不知道哪行是哪行了,重贴
最开始我是这样写的for (int n = 1; n < 2; n++)
            {
                SqlConnection conn = new SqlConnection(@"Server=.;Initial Catalog=person;Uid=sa;Pwd=111111;");
                conn.Open();
                SqlCommand cmd = new SqlCommand(@"SELECT TOP 100 * FROM users", conn);
                SqlDataAdapter ada = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                ada.Fill(ds);
                System.Data.DataTable t = ds.Tables[0];
                if (t == null) return;
                try
                {
                    string fname2 = "d:\\aaa\\";
                    string fname1 = n + "_Profile";
                    string FileName = fname2 + fname1 + ".xls";
                    while (File.Exists(FileName))
                    {
                        fname1 = n + 1 + "_Profile";
                        FileName = fname2 + fname1 + ".xls";
                        n += 1;
                    }
                    FileStream objFileStream;
                    StreamWriter objStreamWriter;
                    string strLine = "";
                    objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
                    objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);
                    //插入标题
                    for (int i = 0; i < t.Columns.Count; i++)
                    {
                        strLine = strLine + t.Columns[i].ColumnName.ToString() + Convert.ToChar(9);
                    }
                    objStreamWriter.WriteLine(strLine);
                    strLine = "";
                    //开始插入数据
                    for (int i = 0; i < t.Rows.Count; i++)
                    {
                        //Console.WriteLine(t.Rows[i][0].ToString());
                        //表格的自动编号strLine = strLine + (i+1) +Convert.ToChar(9);
                        for (int j = 0; j < t.Columns.Count; j++)
                        {
                            strLine = strLine + t.Rows[i][j].ToString() + Convert.ToChar(9);
                        }
                        objStreamWriter.WriteLine(strLine);
                        strLine = "";
                        //string aaa = t.Rows[i][0].ToString();
                        cmd = new SqlCommand(@"INSERT INTO UID(ID)VALUES('" + Convert.ToInt32(t.Rows[i][0].ToString()) + "')", conn);
                        cmd.ExecuteNonQuery();
                    }
                    objStreamWriter.Close();
                    objFileStream.Close();
                }
                catch (Exception exp)
                {
                    Console.WriteLine(exp.ToString());
                }
                Console.WriteLine(" ---  已经生成: {0} 个文件", n);
                conn.Close();
            }
            Console.WriteLine(" ---  Excel文件创建完成");
            Console.ReadLine();结果生成的其实是txt,一直都没有注意,现在想生成excel,不知道该怎么写

Answer 4

是要真正的excel,不要txt...
帮忙啊