欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-15 14:21:02
本文实例讲述了c#导出数据到excel文件的方法。分享给大家供大家参考。具体实现方法如下: /// /// 导出到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#程序设计有所帮助。