usingSystem;
usingSystem.Data;
usingSystem.Text;
usingSystem.Windows.Forms;
//--
usingSystem.Data.SqlClient;
usingSystem.Configuration;
usingSystem.IO;
usingMicrosoft.Office.Interop.Excel;
usingSystem.Reflection;
usingSystem.Runtime.InteropServices;
//--作为一个类，在你需要的项目中新建个类，名字与这个相同，这直接复制里面的代码即可。。
namespaceStudentAttendenceManage
{
publicclassExportToExcel
{
publicstaticvoidExportToExcelMessage(DataGridViewdgv)
{
#region验证可操作性
//申明保存对话框
SaveFileDialogdlg=newSaveFileDialog();
dlg.FileName=DateTime.Now.Year+"年"+DateTime.Now.Month+"月"+DateTime.Now.Day+"日"+"同学信息资料";
//默然文件后缀
dlg.DefaultExt="xls";
//文件后缀列表
dlg.Filter="EXCEL文件(.XLS)|.xls";
//默然路径是系统当前路径
dlg.InitialDirectory=Directory.GetCurrentDirectory();
//打开保存对话框
if(dlg.ShowDialog()==DialogResult.Cancel)return;
//返回文件路径
stringfileNameString=dlg.FileName;
//验证strFileName是否为空或值无效
if(fileNameString.Trim()=="")
{return;}
//定义表格内数据的行数和列数
introwscount=dgv.Rows.Count;
intcolscount=dgv.Columns.Count;
//行数必须大于0
if(rowscount<=0)
{
MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
//列数必须大于0
if(colscount<=0)
{
MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if(rowscount>65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if(colscount>255)
{
MessageBox.Show("数据记录行数太多，不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfofile=newFileInfo(fileNameString);
if(file.Exists)
{
try
{
file.Delete();
}
catch(Exceptionerror)
{
MessageBox.Show(error.Message,"删除失败",MessageBoxButtons.OK,MessageBoxIcon.Warning);
return;
}
}
#endregion
Microsoft.Office.Interop.Excel.ApplicationobjExcel=null;
Microsoft.Office.Interop.Excel.WorkbookobjWorkbook=null;
Microsoft.Office.Interop.Excel.Worksheetobjsheet=null;
try
{
#region导出至Excel
objExcel=newMicrosoft.Office.Interop.Excel.Application();
objWorkbook=objExcel.Workbooks.Add(Missing.Value);
objsheet=(Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
objExcel.Visible=false;
intdisplayColumnsCount=1;
for(inti=0;i<=dgv.ColumnCount-1;i++)
{
if(dgv.Columns[i].Visible==true)
{
objExcel.Cells[1,displayColumnsCount]=dgv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
for(introw=0;row<=dgv.RowCount-1;row++)
{
//tempProgressBar.PerformStep();
displayColumnsCount=1;
for(intcol=0;col{
if(dgv.Columns[col].Visible==true)
{
try
{
objExcel.Cells[row+2,displayColumnsCount]=dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch(Exceptionex)
{
MessageBox.Show("440：出现异常，异常信息为："+ex.Message,"提示信息");
}
}
}
}
objWorkbook.SaveAs(fileNameString,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,Missing.Value,Missing.Value,Missing.Value,
Missing.Value,Missing.Value);
#endregion
}
catch(Exceptionerror)
{
MessageBox.Show(error.Message,"警告",MessageBoxButtons.OK,MessageBoxIcon.Warning);
return;
}
finally
{
#region关闭Excel应用
if(objWorkbook!=null)objWorkbook.Close(Missing.Value,Missing.Value,Missing.Value);
if(objExcel.Workbooks!=null)objExcel.Workbooks.Close();
if(objExcel!=null)objExcel.Quit();
objsheet=null;
objWorkbook=null;
objExcel=null;
#endregion
}
MessageBox.Show(fileNameString+"\n\n导出完毕!","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}
}
异常情况说明：如果自己手动添加了列，导出后的数据会显示在数据库列的前面。。 |
|
