请教大神们一个导出EXCEL的问题

pandarsok2000 2020-04-10 09:26:10

现在实现了数据查询结果使用DataGridView 分页显示功能，还需要一个功能，将查询结果导出EXCEL,可是现在只能实现对当前分页显示数据的导出，而我想将所有符合SQL查询条件的结果均导出，不知道要怎么写，我现在属于新手，还请大神们请教

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace test
{
public partial class Form2 : Form
{
DataView mydv=new DataView();

public Form2()
{
InitializeComponent();

}

public int pageSize = 10; //每页记录数
public int recordCount = 0; //总记录数
public int pageCount = 0; //总页数
public int currentPage = 0; //当前页
public string orderstr;//排序顺序
DataTable dtSource = new DataTable();

///LoadPage方法
/// <summary>
/// loaddpage方法
/// </summary>
private void LoadPage()
{

for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
{
this.dataGridView1.Columns[i].MinimumWidth = 60;
}
for (int i = 1; i < this.dataGridView1.Columns.Count; i++)
{
this.dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
}

if (currentPage < 1) currentPage = 1;
if (currentPage > pageCount) currentPage = pageCount;

int beginRecord;
int endRecord;
DataTable dtTemp;
dtTemp = dtSource.Clone();

beginRecord = pageSize * (currentPage - 1);
if (currentPage == 1) beginRecord = 0;
endRecord = pageSize * currentPage;
if (recordCount == 0)
MessageBox.Show("找不到符合条件的记录");
else
{
if (currentPage == pageCount) endRecord = recordCount;

for (int i = beginRecord; i < endRecord; i++)
{
dtTemp.ImportRow(dtSource.Rows[i]);
}
}

dataGridView1.ScrollBars = ScrollBars.Vertical;
dataGridView1.CellBorderStyle = DataGridViewCellBorderStyle.Single;

dataGridView1.DataSource = dtTemp; //datagridview控件名是tf_dgv1
toolStripLabel1.Text = "当前位于第 "+currentPage.ToString()+" 页";//当前页
toolStripLabel4.Text = "共 " + pageCount.ToString()+" 页";//总页数
toolStripLabel6.Text = "查询到 "+recordCount.ToString()+" 条符合条件的记录";//总记录数
}

/// <summary>
/// 分页的方法
/// </summary>
/// <param name="str"></param>
private void fenye(string str) //str是sql语句
{
SqlConnection myconn = new SqlConnection();

myconn.ConnectionString = Form1.mystr;
myconn.Open();
DataSet myds = new DataSet();
SqlDataAdapter myadp = new SqlDataAdapter(str, myconn);
myadp.Fill(myds, "ksb");
dataGridView1.DataSource = myds.Tables["ksb"];
mydv = myds.Tables["ksb"].DefaultView;
dtSource = myds.Tables[0];
recordCount = dtSource.Rows.Count;

pageCount = (recordCount / pageSize);
if ((recordCount % pageSize) > 0)
{
pageCount++;
}

//默认第一页
currentPage = 1;

LoadPage();//调用加载数据的方法
}

private void form2_load(object sender, EventArgs e)
{
this.comboBox1.SelectedIndex = 0;
}

private void button1_Click(object sender, EventArgs e)
{
if (txt1.Text != "")

if (comboBox1.Text == "已领")
{
string str = "select * from ksb where (姓名 like'%" + txt1.Text + "%'or 身份证号 like'%" + txt1.Text + "%') and 领证时间 is not null" ;
fenye(str);//分页

}
else if (comboBox1.Text == "未领")
{
string str = "select * from ksb where (姓名 like'%" + txt1.Text + "%'or 身份证号 like'%" + txt1.Text + "%') and 领证时间 is null" ;
fenye(str);//分页

}
else

{
string str = "select * from ksb where 姓名 like'%" + txt1.Text + "%'or 身份证号 like'%" + txt1.Text + "%' " ;
fenye(str);//分页

}

}

private void toolStripButton1_Click(object sender, EventArgs e)
{
currentPage = 1;
LoadPage();
}

private void toolStripButton2_Click(object sender, EventArgs e)
{
currentPage = pageCount;
LoadPage();
}

private void toolStripButton3_Click(object sender, EventArgs e)
{
currentPage++;
LoadPage();
}

private void toolStripButton4_Click(object sender, EventArgs e)
{
currentPage--;
LoadPage();
}

private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
string fileName = "";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp =
new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook =
workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{ worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; }
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[r + 2, 1] = dataGridView1.Rows[r].Cells[0].Value;
worksheet.Cells[r + 2, 2] = "'"+dataGridView1.Rows[r].Cells[1].Value;
worksheet.Cells[r + 2, 3] = dataGridView1.Rows[r].Cells[2].Value;
worksheet.Cells[r + 2, 4] = dataGridView1.Rows[r].Cells[3].Value;
worksheet.Cells[r + 2, 5] = "'" + dataGridView1.Rows[r].Cells[4].Value;
worksheet.Cells[r + 2, 6] = dataGridView1.Rows[r].Cells[5].Value;
worksheet.Cells[r + 2, 7] = dataGridView1.Rows[r].Cells[6].Value;
worksheet.Cells[r + 2, 8] = "'" + dataGridView1.Rows[r].Cells[7].Value;
worksheet.Cells[r + 2, 9] = "'"+dataGridView1.Rows[r].Cells[8].Value;
worksheet.Cells[r + 2, 10] = dataGridView1.Rows[r].Cells[9].Value;
worksheet.Cells[r + 2, 11] = dataGridView1.Rows[r].Cells[10].Value;
worksheet.Cells[r + 2, 12] = "'" + dataGridView1.Rows[r].Cells[11].Value;
worksheet.Cells[r + 2, 13] = "'" + dataGridView1.Rows[r].Cells[12].Value;

}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "EXCEL文件导出成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName); //fileSaved = true;
}
catch (Exception ex)
{//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}

}
}

...全文