C#导出数据到Excel文件的方法
本文实例讲述了C#导出数据到Excel文件的方法。分享给大家供大家参考。具体实现方法如下：
///<summary>
///导出到Excel类，项目需引用Microsodt.Office.Interop.Excel,
///类文件需usingSystem.Data与System.Windows.Forms命名空间
///</summary>
publicclassCToExcel
{
///<summary>
///导出到Excel
///</summary>
///<paramname="fileName">默认文件名</param>
///<paramname="listView">数据源，一个页面上的ListView控件</param>
///<paramname="titleRowCount">标题占据的行数，为0表示无标题</param>
publicvoidExportExcel(stringfileName,System.Windows.Forms.ListViewlistView,inttitleRowCount)
{
stringsaveFileName="";
//boolfileSaved=false;
SaveFileDialogsaveDialog=newSaveFileDialog();
saveDialog.DefaultExt="xls";
saveDialog.Filter="Excel文件|*.xls";
saveDialog.FileName=fileName;
saveDialog.ShowDialog();
saveFileName=saveDialog.FileName;
if(saveFileName.IndexOf(":")<0)return;//被点了取消
Microsoft.Office.Interop.Excel.ApplicationxlApp;
try
{
xlApp=newMicrosoft.Office.Interop.Excel.Application();
}
catch(Exception)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
finally
{
}
Microsoft.Office.Interop.Excel.Workbooksworkbooks=xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbookworkbook=workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写Title
if(titleRowCount!=0)
MergeCells(worksheet,1,1,titleRowCount,listView.Columns.Count,listView.Tag.ToString());
//写入列标题
for(inti=0;i<=listView.Columns.Count-1;i++)
{
worksheet.Cells[titleRowCount+1,i+1]=listView.Columns[i].Text;
}
//写入数值
for(intr=0;r<=listView.Items.Count-1;r++)
{
for(inti=0;i<=listView.Columns.Count-1;i++)
{
worksheet.Cells[r+titleRowCount+2,i+1]=listView.Items[r].SubItems[i].Text;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
//if(Microsoft.Office.Interop.cmbxType.Text!="Notification")
//{
//Excel.Rangerg=worksheet.get_Range(worksheet.Cells[2,2],worksheet.Cells[ds.Tables[0].Rows.Count+1,2]);
//rg.NumberFormat="00000000";
//}
if(saveFileName!="")
{
try
{
workbook.Saved=true;
workbook.SaveCopyAs(saveFileName);
//fileSaved=true;
}
catch(Exceptionex)
{
//fileSaved=false;
MessageBox.Show("导出文件时出错,文件可能正被打开！n"+ex.Message);
}
}
//else
//{
//fileSaved=false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
//if(fileSaved&&System.IO.File.Exists(saveFileName))System.Diagnostics.Process.Start(saveFileName);//打开EXCEL
MessageBox.Show(fileName+"导出到Excel成功","提示",MessageBoxButtons.OK);
}
///<summary>
///DataTable导出到Excel
///</summary>
///<paramname="fileName">默认的文件名</param>
///<paramname="dataTable">数据源,一个DataTable数据表</param>
///<paramname="titleRowCount">标题占据的行数，为0则表示无标题</param>
publicvoidExportExcel(stringfileName,System.Data.DataTabledataTable,inttitleRowCount)
{
stringsaveFileName="";
//boolfileSaved=false;
SaveFileDialogsaveDialog=newSaveFileDialog();
saveDialog.DefaultExt="xls";
saveDialog.Filter="Excel文件|*.xls";
saveDialog.FileName=fileName;
saveDialog.ShowDialog();
saveFileName=saveDialog.FileName;
if(saveFileName.IndexOf(":")<0)return;//被点了取消
Microsoft.Office.Interop.Excel.ApplicationxlApp;
try
{
xlApp=newMicrosoft.Office.Interop.Excel.Application();
}
catch(Exception)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
finally
{
}
Microsoft.Office.Interop.Excel.Workbooksworkbooks=xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbookworkbook=workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写Title
if(titleRowCount!=0)
MergeCells(worksheet,1,1,titleRowCount,dataTable.Columns.Count,dataTable.TableName);
//写入列标题
for(inti=0;i<=dataTable.Columns.Count-1;i++)
{
worksheet.Cells[titleRowCount+1,i+1]=dataTable.Columns[i].ColumnName;
}
//写入数值
for(intr=0;r<=dataTable.Rows.Count-1;r++)
{
for(inti=0;i<=dataTable.Columns.Count-1;i++)
{
worksheet.Cells[r+titleRowCount+2,i+1]=dataTable.Rows[r][i].ToString();
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
//if(Microsoft.Office.Interop.cmbxType.Text!="Notification")
//{
//Excel.Rangerg=worksheet.get_Range(worksheet.Cells[2,2],worksheet.Cells[ds.Tables[0].Rows.Count+1,2]);
//rg.NumberFormat="00000000";
//}
if(saveFileName!="")
{
try
{
workbook.Saved=true;
workbook.SaveCopyAs(saveFileName);
//fileSaved=true;
}
catch(Exceptionex)
{
//fileSaved=false;
MessageBox.Show("导出文件时出错,文件可能正被打开！n"+ex.Message);
}
}
//else
//{
//fileSaved=false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
//if(fileSaved&&System.IO.File.Exists(saveFileName))System.Diagnostics.Process.Start(saveFileName);//打开EXCEL
MessageBox.Show(fileName+"导出到Excel成功","提示",MessageBoxButtons.OK);
}
///<summary>
///合并单元格，并赋值，对指定WorkSheet操作
///</summary>
///<paramname="sheetIndex">WorkSheet索引</param>
///<paramname="beginRowIndex">开始行索引</param>
///<paramname="beginColumnIndex">开始列索引</param>
///<paramname="endRowIndex">结束行索引</param>
///<paramname="endColumnIndex">结束列索引</param>
///<paramname="text">合并后Range的值</param>
publicvoidMergeCells(Microsoft.Office.Interop.Excel.WorksheetworkSheet,intbeginRowIndex,intbeginColumnIndex,intendRowIndex,intendColumnIndex,stringtext)
{
Microsoft.Office.Interop.Excel.Rangerange=workSheet.get_Range(workSheet.Cells[beginRowIndex,beginColumnIndex],workSheet.Cells[endRowIndex,endColumnIndex]);
range.ClearContents();//先把Range内容清除，合并才不会出错
range.MergeCells=true;
range.Value2=text;
range.HorizontalAlignment=Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment=Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
}
希望本文所述对大家的C#程序设计有所帮助。