怎样将数据集的数据导入到excel中去

[likk-0608]

直接将数据集的数据加载到excel中去

[lantianxiadeyu]

自定义一个将DataGridView控件中数据导出到Excel函数
/// <summary>
/// 将DataGridView控件中数据导出到Excel
/// </summary>
/// <param name="gridView">DataGridView对象</param>
/// <param name="isShowExcle">是否显示Excel界面</param>
/// <returns></returns>
public bool ExportDataGridview(DataGridView gridView,bool isShowExcle)
{
if (gridView.Rows.Count == 0)
return false;
//建立Excel对象
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcle;
//生成字段名称
for (int i = 0; i < gridView.ColumnCount; i++)
{
excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < gridView.RowCount-1; i++)
{
for (int j = 0; j < gridView.ColumnCount; j++)
{
if (gridView[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + gridView[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
}
}
}
return true;
}u

[bdmh]

        private void WriteExcel(string filename,string sql)
        {
            Application excel;
            _Workbook xBk;
            _Worksheet xSt;
            _QueryTable xQt;
            excel = new ApplicationClass();
            if (excel == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            } 
            xBk = excel.Workbooks.Add(true);
            xSt = (_Worksheet)xBk.ActiveSheet;
            string Conn = "ODBC;DRIVER=SQL Server;SERVER=" + host + ";UID=sa;PWD=58325245;DATABASE=CMSChina";
            xQt = xSt.QueryTables.Add(Conn, xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]), sql);
            xQt.Name = "导出示例";
            xQt.FieldNames = true;
            xQt.RowNumbers = false;
            xQt.FillAdjacentFormulas = false;
            xQt.PreserveFormatting = false;
            xQt.BackgroundQuery = true;
            xQt.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells;
            xQt.AdjustColumnWidth = true;
            xQt.RefreshPeriod = 0;
            xQt.PreserveColumnInfo = true;
            xQt.Refresh(xQt.BackgroundQuery);
            excel.Visible = true;
        }

[kj289907795]

        private void toolStripButton3_Click(object sender, EventArgs e)
        {
            if (dataGridView1.RowCount > 1)
            {


                SaveFileDialog saveFileDialog = new SaveFileDialog(); //Windows 窗体 SaveFileDialog 组件是一个预先配置的对话框。它与 Windows 使用的标准“保存文件”对话框相同

                saveFileDialog.Filter = "Execl files (*.xls)|*.xls"; //获取或设置筛选器字符串，该字符串确定在 SaveFileDialog 中显示的文件类型

                saveFileDialog.FilterIndex = 0; //索引

                saveFileDialog.RestoreDirectory = true;//获取或设置一个值，该值使文件对话框将其当前目录还原为用户更改目录以搜索文件之前的初始值

                saveFileDialog.CreatePrompt = true;//获取或设置一个值，该值指示如果用户指定一个不存在的文件，SaveFileDialog 是否提示用户以允许创建文件

                saveFileDialog.Title = "Export Excel File To"; //获取或设置在文件对话框的标题栏中显示的文本


                //saveFileDialog.ShowDialog();// 显示预置对话框


                Stream myStream;//提供字节序列的一般视图，命名空间：  System.IO
                if (DialogResult.OK == saveFileDialog.ShowDialog())
                {
                    //表示用户正确指定了文件，
                    //你的代码



                    myStream = saveFileDialog.OpenFile();//为用户使用 SaveFileDialog 选定的文件名创建读/写文件流

                    StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(0));//用指定的编码及默认缓冲区大小，为指定的流初始化 StreamWriter 类的新实例

                    string str = "";

                    try
                    {

                        //写标题

                        for (int i = 0; i < dataGridView1.ColumnCount; i++)
                        {

                            if (i > 0)
                            {

                                str = str + "\t";

                            }

                            str = str + dataGridView1.Columns[i].HeaderText;

                        }


                        sw.WriteLine(str);



                        //写内容

                        for (int j = 0; j < dataGridView1.Rows.Count; j++)
                        {

                            string tempStr = "";

                            for (int k = 0; k < dataGridView1.Columns.Count; k++)
                            {

                                if (k > 0)
                                {

                                    tempStr += "\t";   // 间隔字符

                                }
                                if (dataGridView1.Rows[j].Cells[k].Value == null)
                                {
                                    tempStr += "";
                                }
                                else
                                {

                                    tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
                                }
                                
                            }

                            sw.WriteLine(tempStr);

                        }

                        sw.Close();

                        myStream.Close();

                        MessageBox.Show("导入完成！");

                    }

                    catch (Exception E)
                    {
                        MessageBox.Show(E.ToString());
                        
                    }
                    finally
                    {
                        sw.Dispose();
                        sw.Close();
                        myStream.Dispose();
                        myStream.Close();
                    }
                }
            }
            else
            {
                
                MessageBox.Show("没有数据可以导出！", "提示");
            }
        }

经过测试的

[wuyq11]

protected void AddExcel(DataSet ds)
{
DataTable dt = ds.Tables[0];
string fileName = Guid.NewGuid() + ".xls";
Excel.Application excel = new Excel.ApplicationClass();
int rowIndex = 1;
int colIndex = 0;
excel.Application.Workbooks.Add(true);
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
}
excel.Visible = false;
excel.ActiveWorkbook.SaveAs(fileName, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
excel.Quit();
excel = null;
GC.Collect();
}

[justsoso66]

代码遍地都是。。。