C#DataGridView导出excel的⼏种⽅法第⼀种、⾸先本form上游datagridview的控件及数据，再建⼀个button控件作为导出按钮，在按钮click事件中写⼊以下代码
此乃数据集的⽅式，即先将数据放⼊数据集表⾥作为对象与excel⼀⼀对应
//保存⽂件对话框
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel⽂件|*.xlsx|Word⽂件|*.docx";
sfd.FilterIndex = 0;
if (sfd.ShowDialog() == DialogResult.OK)
{
string search = "select * from 旧备件表 where(0=0)";
if (Box1.Text.Length > 0)
{
search = search + " and 物料编码=" + "'" + textBox1.Text + "'";
}
if (Box2.Text.Length > 0)
{
search = search + " and 设备号=" + "'" + textBox2.Text + "'";
}
if (Box3.Text.Length > 0)
{
search = search + " and 物料描述 like" + "'%" + textBox3.Text + "%'";//实现物料描述的模糊查询
}
if (Box4.Text.Length > 0)
{
search = search + " and 备件序列号 like" + "'%" + textBox4.Text + "%'";//实现备件序列号的模糊查询
}
//调⽤导出Excel的⽅法，传⼊DataTable数据表和路径
SqlDataAdapter sda = new SqlDataAdapter(search, DataBase.GetSqlConnection());
System.Data.DataTable dt = new System.Data.DataTable();
//将数据库中查到的数据填充到DataTable数据表
sda.Fill(dt);
ExportExcel(dt, sfd.FileName);
}
}
void ExportExcel(System.Data.DataTable dt, string filepath)
{
//创建Excel应⽤程序类的⼀个实例，相当于从电脑开始菜单打开Excel
ApplicationClass xlsxapp = new ApplicationClass();
//新建⼀张Excel⼯作簿
Workbook wbook = xlsxapp.Workbooks.Add(true);
//第⼀个sheet页
Worksheet wsheet = (Worksheet)_Item(1);
//将DataTable的列名显⽰在Excel表第⼀⾏
for (int i = 0; i < dt.Columns.Count; i++)
{
/
/注意Excel表的⾏和列的索引都是从1开始的
wsheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
//遍历DataTable，给Excel赋值
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
//从第⼆⾏第⼀列开始写⼊数据
wsheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
}
}
//保存⽂件
wbook.SaveAs(filepath);
//释放资源
xlsxapp.Quit();
}
第⼆种、此乃直接将datagridview⾥的数据⼀⼀导出放⼊excel指定的单元格⾥。
private void button3_Click(object sender, EventArgs 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; }
getsavefilename//写⼊数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i == 3)//将这⼀列有前导零的加上⼀个单引号
{
worksheet.Cells[r + 2, i + 1] = "'" + dataGridView1.Rows[r].Cells[i].Value;
string temp = "'" + dataGridView1.Rows[r].Cells[i].Value;//看下值
}
else
{
worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
}
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽⾃适应
MessageBox.Show(fileName + "资料保存成功", "提⽰", 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();//强⾏销毁 }
}
第三种：⽹上的
SaveFileDialog SaveDialog = new SaveFileDialog();
SaveDialog.Filter = "Excel ⽂件(*.xls)|*.xls|Excel ⽂件(*.xlsx)|*.xlsx|所有⽂件(*.*)|*.*";
SaveDialog.RestoreDirectory = true;
if (SaveDialog.ShowDialog() == DialogResult.OK)
{
GenerateAttachment(SaveDialog.FileName,dtExport);
}
try
{
//需要添加 Microsoft.Office.Interop.Excel引⽤
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)//服务器上缺少Excel组件，需要安装Office软件
{
return;
}
app.Visible = false;
app.UserControl = true;
string strTempPath = System.Windows.Forms.Application.StartupPath + "\\Template\\Form.xls";
Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(strTempPath); //加载模板
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel.__Item(1); //第⼀个⼯作薄。
if (worksheet == null)//⼯作薄中没有⼯作表
{
return;
}
//1、获取数据
int rowCount = DT.Rows.Count;
if (rowCount < 1)//没有取到数据
{
return;
}
//2、写⼊数据，Excel索引从1开始
for (int i = 1; i <= rowCount; i++)
{
int row_ = 1 + i; //Excel模板上表头占了1⾏
int dt_row = i - 1; //dataTable的⾏是从0开始的
worksheet.Cells[row_, 1] = DT.Rows[dt_row]["itemname"].ToString();
worksheet.Cells[row_, 2] = DT.Rows[dt_row]["Color"].ToString();
worksheet.Cells[row_, 3] = DT.Rows[dt_row]["Grade1"].ToString();
// worksheet.Cells[row_, 4] = DT.Rows[dt_row]["ProAreaName"].ToString();
worksheet.Cells[row_, 4] = DT.Rows[dt_row]["Quantity"].ToString();
worksheet.Cells[row_, 5] = DT.Rows[dt_row]["Unit_name"].ToString();
worksheet.Cells[row_, 6] = DT.Rows[dt_row]["TotalAmt"].ToString();
}
worksheet.Cells[DT.Rows.Count + 2, 1] = "合计";
worksheet.Cells[DT.Rows.Count + 2, 4] = DT.Compute("sum(Quantity)", "");
worksheet.Cells[DT.Rows.Count + 2, 6] = DT.Compute("sum(TotalAmt)", "");
//调整Excel的样式。
//Microsoft.Office.Interop.Excel.Range rg = _Range("A3", worksheet.Cells[rowCount + 2, 32]);
//rg.Borders.LineStyle = 1; //单元格加边框
//worksheet.Columns.AutoFit(); //⾃动调整列宽
//隐藏某⼀⾏
//选中部分单元格，把选中的单元格所在的⾏的Hidden属性设为true
//_Range(app.Cells[2, 1], app.Cells[2, 32]).EntireRow.Hidden = true;
//删除某⼀⾏
// _Range(app.Cells[2, 1], app.Cells[2, 32]).EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
//3、保存⽣成的Excel⽂件
//Missing在System.Reflection命名空间下
//string savePath = System.Windows.Forms.Application.StartupPath+"/Temp/T1_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
workbook.SaveAs(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Va //workbook.SaveAs(FileName,FileFormat,Password,WriteResPassword,ReadOnlyRecommended,CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
//4、按顺序释放资源
NAR(worksheet);
NAR(sheets);
NAR(workbook);
NAR(workbooks);
app.Quit();
NAR(app);
MessageBox.Show("保存成功", "提⽰", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
catch (Exception ex)
{
MessageBox.Show("异常，异常信息为："+ex.ToString(),"");
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论