数据输出excel表的问题

Question

http://blog.csdn.net/liang4571231/archive/2008/08/30/2851988.aspx对你有用

Answer 1

http://www.cnblogs.com/acelove/archive/2005/02/21/106886.html老方法了

Answer 2

        public bool ExportDataGridview(DataGridView gridView, bool isShowExcele)
        {
            //建立Excel对象
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcele;
            int rowCount = gridView.Rows.Count;
            int colCount = gridView.Columns.Count;
            object[,] dataArray = new object[rowCount, colCount];
            for (int i = 0; i < gridView.ColumnCount; i++)
            {
                dataArray[0, i] = gridView.Columns[i].HeaderText;
            }
            for (int i = 0; i < rowCount-1; i++)
            {
                for (int j = 0; j < colCount; j++)
                {
                    if (gridView[j, i].Value == typeof(string))
                    {
                        dataArray[i+1, j] = "" + gridView[i, j].Value.ToString();
                    }
                    else
                    {
                        dataArray[i+1, j]  = gridView[j, i].Value.ToString();
                    }
                }
            }
            excel.get_Range("A1", excel.Cells[rowCount, colCount]).Value2 = dataArray;
            excel.Cells.EntireColumn.AutoFit();
            return true;
        }

Answer 3

用EXCEl对象不太好，因为当电脑内OFFICE版本不致时，会出错
用XML －》XLS 就没问题了，给段代码你吧：
   //---将表格写成XML文件---不用样式表
         public static void ExportXls(DataGridView dgv,string filename)
        {
            try
            {
                filename = filename.Replace("xls", "xml"); ;
                System.Xml.XmlTextWriter writer = null;
                writer = new System.Xml.XmlTextWriter(filename, null);
                ////为使文件易读，使用缩进
                writer.Formatting = System.Xml.Formatting.Indented;                ////写XML声明
                writer.WriteStartDocument(true);                //写入注释
               // writer.WriteComment("xml To xls ＝ xml文件转换成xls文件");                writer.WriteStartElement("Workbook");
                writer.WriteAttributeString("xmlns:msxsl", "urn:schemas-microsoft-com:xslt");
                writer.WriteAttributeString("xmlns:user", "urn:my-scripts");
                writer.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
                writer.WriteAttributeString("xmlns:o", "urn:schemas-microsoft-com:office:office");
                writer.WriteAttributeString("xmlns:x", "urn:schemas-microsoft-com:office:excel");
                writer.WriteAttributeString("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet");
                writer.WriteAttributeString("xmlns:html", "http://www.w3.org/TR/REC-html40");
                //--单元格格式---
                writer.WriteStartElement("Styles");
                writer.WriteStartElement("Style");
                writer.WriteAttributeString("ss:ID", "s0");
                //--以下是对齐样式
                writer.WriteStartElement("Alignment");
                writer.WriteAttributeString("ss:Horizontal", "Center");
                writer.WriteAttributeString("ss:Vertical", "Bottom");
                writer.WriteEndElement();
                //--以下是字体样式
                writer.WriteStartElement("Font");
                writer.WriteAttributeString("ss:Bold", "1"); //黑体
                writer.WriteEndElement();                writer.WriteEndElement();    //endStyle
                writer.WriteEndElement();    //endStyles
                //------------------------------设置工作表-----------
                writer.WriteStartElement("Worksheet");
                ////属性
                writer.WriteAttributeString("ss:Name", "sheet1"); //sheet1的名字
                writer.WriteStartElement("Table");
                writer.WriteAttributeString("ss:ExpandedColumnCount", dgv.Columns.Count.ToString ());                //---设置每列的宽度－－－
                for(int i=0;i<dgv.Columns.Count;i++)
                {
                    writer.WriteStartElement("Column");
                    writer.WriteAttributeString("ss:AutoFitWidth", "0");
                    writer.WriteAttributeString("ss:Width", "100");               
                    writer.WriteEndElement();
                }
                //--列头字段
                writer.WriteStartElement("Row");
                for (int i = 0; i < dgv.Columns.Count;i++ )
                {
                    writer.WriteStartElement("Cell");
                    writer.WriteAttributeString("ss:StyleID", "s0");
                    writer.WriteStartElement("Data");
                    writer.WriteAttributeString("ss:Type", "String");
                    writer.WriteValue(dgv.Columns[i].HeaderText);
                    writer.WriteEndElement();
                    writer.WriteEndElement();//endCell
                }
                writer.WriteEndElement();//endRow
                //－－数据字段
                for (int i = 0; i < dgv.Rows .Count ;i++ )
                {
                    writer.WriteStartElement("Row");                    for(int j=0;j<dgv.Columns .Count ;j++ )
                    {
                        writer.WriteStartElement("Cell");
                        writer.WriteStartElement("Data");
                        writer.WriteAttributeString("ss:Type", "String");
                        writer.WriteValue(dgv.Rows[i].Cells [j].Value .ToString ());
                        writer.WriteEndElement();
                        writer.WriteEndElement();
                    }
                    writer.WriteEndElement();
                }
                writer.WriteEndDocument();//endTable                //缓冲器内的内容写入文件
                writer.Flush();
                writer.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "创建XML出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (!System.IO.File.Exists(filename))
            {
                return;
            }
            //--以下是给文件改名
            System.Diagnostics.Process p2 = new System.Diagnostics.Process();
            p2.StartInfo.FileName = "cmd.exe";
            string newfile = filename.Substring(filename.LastIndexOf('\\') + 1).Replace("xml", "xls");
            string command = "ren  \"" + filename + "\" " + newfile;
            p2.StartInfo.Arguments = "/c " + command;
            p2.StartInfo.UseShellExecute = false;
            p2.StartInfo.RedirectStandardInput = true;  
            p2.StartInfo.RedirectStandardOutput = true;  
            p2.StartInfo.RedirectStandardError = true;   
            p2.StartInfo.CreateNoWindow = true;          
            p2.Start();
            MessageBox.Show("导出成功！", "表格导出到文件",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);        }
    }

Answer 4

 我是使用 OdbcDataReader dr = cmd.ExecuteReader()来 读取数据库中的数据的，该如何将数据显示在excel表中。谢谢！

Answer 5

private void btnExport_Click(object sender, System.EventArgs e)
{
//--先保存到服务器上
MemberDB memberDB = new MemberDB();
DataSet ds=memberDB.GetExportEmail();//--GetExportEmail()能够从数据库中获取相应的数据
StringBuilder MemberEmail =new StringBuilder();
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
{
MemberEmail.Append(ds.Tables[0].Rows[i][0].ToString()+";");
}
string filePath=Server.MapPath("../Upload/localfiles/Email.txt");
FileStream myWStream = new FileStream(filePath,FileMode.OpenOrCreate,FileAccess.Write);
BinaryWriter binWrit = new BinaryWriter(myWStream);
binWrit.Write(MemberEmail.ToString());
binWrit.Close();
myWStream.Close();

//--从服务器上下载到本地然后删掉服务器上的文件
string FileName=Server.MapPath("../Upload/localfiles/Email.txt");
FileInfo fi=new FileInfo(@FileName);   
Response.Clear();   
Response.ClearHeaders();   
Response.Buffer = false;   
Response.ContentType = "application/octet-stream";   
Response.AppendHeader("Content-Disposition","attachment;filename=" +HttpUtility.UrlEncode(fi.FullName,System.Text.Encoding.UTF8));   
Response.AppendHeader("Content-Length",fi.Length.ToString());   
Response.WriteFile(fi.FullName);   
Response.Flush();   
File.Delete(FileName);
Response.End();
}