C#导出数据到Excel文件的方法

本文实例讲述了c#导出数据到excel文件的方法。分享给大家供大家参考。具体实现方法如下：

/// <summary>
/// 导出到excel类，项目需引用microsodt.office.interop.excel,
/// 类文件需using system.data与system.windows.forms命名空间
/// </summary>
public class ctoexcel
{
  /// <summary>
  /// 导出到excel
  /// </summary>
  /// <param name="filename">默认文件名</param>
  /// <param name="listview">数据源，一个页面上的listview控件</param>
  /// <param name="titlerowcount">标题占据的行数，为0表示无标题</param>
  public void exportexcel(string filename, system.windows.forms.listview listview,int titlerowcount)
  {
   string savefilename = "";
   //bool filesaved = false;
   savefiledialog savedialog = new savefiledialog();
   savedialog.defaultext = "xls";
   savedialog.filter = "excel文件|*.xls";
   savedialog.filename = filename;
   savedialog.showdialog();
   savefilename = savedialog.filename;
   if (savefilename.indexof(":") < 0) return; //被点了取消 
   microsoft.office.interop.excel.application xlapp;
   try
   {
    xlapp = new microsoft.office.interop.excel.application();
   }
   catch (exception)
   {
    messagebox.show("无法创建excel对象，可能您的机子未安装excel");
    return;
   }
   finally
   {
   }
   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
   //写title
   if(titlerowcount!=0)
    mergecells(worksheet, 1, 1, titlerowcount, listview.columns.count, listview.tag.tostring());
   //写入列标题
   for (int i = 0; i <= listview.columns.count - 1; i++)
   {
    worksheet.cells[titlerowcount+1, i + 1] = listview.columns[i].text;
   }
   //写入数值
   for (int r = 0; r <= listview.items.count - 1; r++)
   {
    for (int i = 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.range rg = 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 (exception ex)
    {
     //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>
  /// <param name="filename">默认的文件名</param>
  /// <param name="datatable">数据源,一个datatable数据表</param>
  /// <param name="titlerowcount">标题占据的行数，为0则表示无标题</param>
  public void exportexcel(string filename,system.data.datatable datatable,int titlerowcount)
  {
   string savefilename = "";
   //bool filesaved = false;
   savefiledialog savedialog = new savefiledialog();
   savedialog.defaultext = "xls";
   savedialog.filter = "excel文件|*.xls";
   savedialog.filename = filename;
   savedialog.showdialog();
   savefilename = savedialog.filename;
   if (savefilename.indexof(":") < 0) return; //被点了取消 
   microsoft.office.interop.excel.application xlapp;
   try
   {
    xlapp = new microsoft.office.interop.excel.application();
   }
   catch (exception)
   {
    messagebox.show("无法创建excel对象，可能您的机子未安装excel");
    return;
   }
   finally
   {
   }
   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
   //写title
   if(titlerowcount!=0)
    mergecells(worksheet, 1, 1, titlerowcount, datatable.columns.count, datatable.tablename);
   //写入列标题
   for (int i = 0; i <= datatable.columns.count - 1; i++)
   {
    worksheet.cells[titlerowcount+1, i + 1] = datatable.columns[i].columnname;
   }
   //写入数值
   for (int r = 0; r <= datatable.rows.count - 1; r++)
   {
    for (int i = 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.range rg = 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 (exception ex)
    {
     //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> 
  /// <param name="sheetindex">worksheet索引</param> 
  /// <param name="beginrowindex">开始行索引</param> 
  /// <param name="begincolumnindex">开始列索引</param> 
  /// <param name="endrowindex">结束行索引</param> 
  /// <param name="endcolumnindex">结束列索引</param> 
  /// <param name="text">合并后range的值</param> 
  public void mergecells(microsoft.office.interop.excel.worksheet worksheet, int beginrowindex, int begincolumnindex, int endrowindex, int endcolumnindex, string text)
  {
   microsoft.office.interop.excel.range range = 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#程序设计有所帮助。