博客开通第二十七天
将数据库中的数据导出成Excel文件，方法一：
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
namespace ExportData
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
DataTable dtTable = new DataTable();
private void Excel文件toolStripMenuItem_Click(object sender, EventArgs e)
{
string dtName = "";
if (comboBox1.Text == "TB_GSM")
{
dtName = "TB_GSM";
}
else if (comboBox1.Text == "TB_SCDMA")
{
dtName = "TB_SCDMA";
}
else if (comboBox1.Text == "TB_LTE")
{
dtName = "TB_LTE";
}
string strCon = "server=.;database=lcc;uid=sa;pwd=123456";
SqlConnection conn = new SqlConnection(strCon);
conn.Open();
string strSql = "select * from "+ dtName ;
SqlCommand cmd = new SqlCommand(strSql,conn);
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
DataTable dtTable = new DataTable();
sda.Fill(dtTable);
//SqlDataReader sdr = cmd.ExecuteReader();TB_GSM
ExportDataTable(dtTable);
}
#region 导出方法1
private void ExportDataTable(DataTable dt)
{
if (dt.Rows.Count == 0)
{
MessageBox.Show("无数据导出!");
return;
}
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "*.xls|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
Microsoft.Office.Interop.Excel.Workbook WorkBook = excel.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = WorkBook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
ExcelSheet.Cells.Select();
ExcelSheet.Cells.Columns.AutoFit();
for (int i = 0; i < dt.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString().Trim();
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
excel.Cells[i + 2, j + 1] = "'" + dt.Rows[i][j].ToString();
}
}
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
ExcelSheet.SaveAs(saveFileDialog1.FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
WorkBook.Save();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
ExcelSheet = null;
WorkBook = null;
excel = null;
GC.Collect();
MessageBox.Show("数据库中的表成功导出为Excel文件！");
}
catch
{
System.GC.Collect();
MessageBox.Show("导出数据失败!");
}
}
}
#endregion
private void Form1_Load(object sender, EventArgs e)
{
comboBox1.Items.Add("TB_GSM");
comboBox1.Items.Add("TB_SCDMA");
comboBox1.Items.Add("TB_LTE");
}
将数据库中的数据导出成Excel文件，方法二：
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
namespace ExportData
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
DataTable dtTable = new DataTable();
private void Excel文件toolStripMenuItem_Click(object sender, EventArgs e)
{
string dtName = "";
if (comboBox1.Text == "TB_GSM")
{
dtName = "TB_GSM";
}
else if (comboBox1.Text == "TB_SCDMA")
{
dtName = "TB_SCDMA";
}
else if (comboBox1.Text == "TB_LTE")
{
dtName = "TB_LTE";
}
string strCon = "server=.;database=lcc;uid=sa;pwd=123456";
SqlConnection conn = new SqlConnection(strCon);
conn.Open();
//string strSql = "select * from "+ dtName ;
string strSql = "select name from sysObjects where xtype= 'U'";
SqlCommand cmd = new SqlCommand(strSql,conn);
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
DataTable dtTable = new DataTable();
sda.Fill(dtTable);
//SqlDataReader sdr = cmd.ExecuteReader();TB_GSM
//ExportDataTable(dtTable);
ExportToExcel(dtTable);
}
private void Form1_Load(object sender, EventArgs e)
{
comboBox1.Items.Add("TB_GSM");
comboBox1.Items.Add("TB_SCDMA");
comboBox1.Items.Add("TB_LTE");
}
#region 导出方法2
public static void ExportToExcel(DataTable dt)//DataGridView dataGridView另存新档按钮 导出成Excel
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.Title = "导出Excel";
saveFileDialog.ShowDialog();
if ("" == saveFileDialog.FileName || null == saveFileDialog.FileName)
return;
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{
//标题
for (int i = 0; i < dt.Rows.Count; i++)//从GridView中读取数据用 i < dataGridView.ColumnCount;
{
if (i > 0)
{
str += "\t";
}
str += dt.Columns[i].ColumnName;//从GridView中读取数据用str += dataGridView.Columns[i].HeaderText;
}
sw.WriteLine(str);
//内容
for (int j = 0; j < dt.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dt.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dt.Rows[j][k].ToString();//从GridView中读取数据用tempStr += dataGridView.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception)
{
//MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
#endregion
在网上找的：
将DataGridView里的数据导出为Excel文件，或者其他格式的。
这是一种最简单的一种导表的方法。
步骤如下：
1、添加 Microsoft.Office.Interop.Excel的引用。
2、添加下面的方法到你的类中：
/// <summary>
/// 将DataGridView控件中数据导出到Excel
/// </summary>
/// <param name="gridView">DataGridView对象</param>
/// <param name="isShowExcele">是否显示Excel界面</param>
/// <returns></returns>
public bool ExportDataGridView(DataGridView gridView, bool isShowExcele)
{
if (gridView.Rows.Count == 0)
return false;
//创建Excel对象
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcele;
//生成字段的名称
for (int i = 0; i < gridView.ColumnCount; i++)
{
excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < gridView.RowCount; 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;
}
3、调用的方法的写法：
private void 导出数据ToolStripMenuItem1_Click(object sender, EventArgs e)
{
if (!ExportDataGridView(dataGridView_CarsInfo, true))
{
MessageBox.Show("表格中没有数据，无法导出数据！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
将DataGridView里数据导出成多种格式：
这是在别人的帮助下整理出来的：
可以随意选择存储方式
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
namespace CodeStandard.DataExport
{
public partial class ExportData : Form
{
public ExportData()
{
InitializeComponent();
}
private void ExportData_Load(object sender, EventArgs e)
{
this.dataGridView1.Rows.Add("18731", "460-00-22296-16000", "62", "460-00-22296-16030", "62", "");
this.dataGridView1.Rows.Add("18731", "460-00-22296-16020", "66", "460-00-22296-18317", "66", "");
this.dataGridView1.Rows.Add("18731", "460-00-22296-16030", "62", "460-00-22296-16000", "62", "");
this.dataGridView1.Rows.Add("18731", "460-00-22296-18317", "66", "460-00-22296-16020", "66", "");
this.dataGridView1.Rows.Add("18731", "460-00-22454-14849", "516", "460-00-22454-34849", "517", "");
this.dataGridView1.Rows.Add("18731", "460-00-22454-34849", "517", "460-00-22454-14849", "516", "");
int i = 5;
for (int j = 1; j <= i; j++)
{
this.dataGridView1.Rows.Add("18731", "460-00-22296-16000", "62", "460-00-22296-16030", "62", "");
}
}
//导出为Excel文件按钮
private void btnExcel_Click(object sender, EventArgs e)
{
// DataGridView dg = dataGridView1;
// ExportToExcel(dataGridView1);
if (!ExportToExcel(dataGridView1, true))
{
MessageBox.Show("表格中没有数据，无法导出数据！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public bool ExportToExcel(DataGridView gridView, bool isShowExcele)
{
if (gridView.Rows.Count == 0)
return false;
//创建Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcele;
//生成字段的名称
for (int i = 0; i < gridView.ColumnCount; i++)
{
excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < gridView.RowCount; 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
{
if (gridView[j, i].Value!=null)
excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
}
}
}
return true;
}
}
