C#导出Excel的示例详解

using system;
using system.collections.generic;
using system.text;
using system.data;
using system.windows.forms;
using system.reflection;

namespace dms
{
/// <summary>
/// c#操作excel类
/// </summary>
class exceloperate
{
//法一
//public bool datasettoexcel(dataset dataset, bool isshowexcle)
//{
//  datatable datatable = dataset.tables[0];
//  int rownumber = datatable.rows.count;
//  int columnnumber = datatable.columns.count;

//  if (rownumber == 0)
//  {
//    messagebox.show("没有任何数据可以导入到excel文件！");
//    return false;
//  }

//  //建立excel对象
//  microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application();
//  excel.application.workbooks.add(true);
//  excel.visible = isshowexcle;//是否打开该excel文件

//  //填充数据
//  for (int c = 0; c < rownumber; c++)
//  {
//    for (int j = 0; j < columnnumber; j++)
//    {
//      excel.cells[c + 1, j + 1] = datatable.rows[c].itemarray[j];
//    }
//  }

//  return true;
//}


//法二
//public bool datasettoexcel(dataset dataset, bool isshowexcle)
//{
//  datatable datatable = dataset.tables[0];
//  int rownumber = datatable.rows.count;

//  int rowindex = 1;
//  int colindex = 0;


//  if (rownumber == 0)
//  {
//    return false;
//  }

//  //建立excel对象
//  microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application();
//  excel.application.workbooks.add(true);
//  excel.visible = isshowexcle;

//  //生成字段名称
//  foreach (datacolumn col in datatable.columns)
//  {
//    colindex++;
//    excel.cells[1, colindex] = col.columnname;
//  }

//  //填充数据
//  foreach (datarow row in datatable.rows)
//  {
//    rowindex++;
//    colindex = 0;
//    foreach (datacolumn col in datatable.columns)
//    {
//      colindex++;
//      excel.cells[rowindex, colindex] = row[col.columnname];
//    }
//  }

//  return true;
//}

//法三（速度最快）
/// <summary>
/// 将数据集中的数据导出到excel文件
/// </summary>
/// <param name="dataset">输入数据集</param>
/// <param name="isshowexcle">是否显示该excel文件</param>
/// <returns></returns>
public bool datasettoexcel(dataset dataset, bool isshowexcle)
{
datatable datatable = dataset.tables[0];
int rownumber = datatable.rows.count;//不包括字段名
int columnnumber = datatable.columns.count;
int colindex = 0;

if (rownumber == 0) 
{
return false;
}

//建立excel对象 
microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application();
//excel.application.workbooks.add(true);
microsoft.office.interop.excel.workbook workbook = excel.workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet);
microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1];
excel.visible = isshowexcle;
//microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)excel.worksheets[1];
microsoft.office.interop.excel.range range;

//生成字段名称 
foreach (datacolumn col in datatable.columns)
{
colindex++;
excel.cells[1, colindex] = col.columnname;
}

object[,] objdata = new object[rownumber, columnnumber]; 

for (int r = 0; r < rownumber; r++) 
{
for (int c = 0; c < columnnumber; c++)
{
objdata[r, c] = datatable.rows[r][c];
}
//application.doevents();
}

// 写入excel 
range = worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, columnnumber]);
//range.numberformat = "@";//设置单元格为文本格式
range.value2 = objdata;
worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, 1]).numberformat = "yyyy-m-d h:mm";

return true; 
}

//法四
//public bool datasettoexcel(dataset dataset, bool isshowexcle)
//{
//  datatable datatable = dataset.tables[0];
//  int rownumber = datatable.rows.count;
//  int columnnumber = datatable.columns.count;
//  string stringbuffer = "";

//  if (rownumber == 0)
//  {
//    messagebox.show("没有任何数据可以导入到excel文件！");
//    return false;
//  }

//  //建立excel对象
//  microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application();
//  excel.application.workbooks.add(true);
//  excel.visible = isshowexcle;//是否打开该excel文件

//  //填充数据
//  for (int i = 0; i < rownumber; i++)
//  {
//    for (int j = 0; j < columnnumber; j++)
//    {
//      stringbuffer += datatable.rows[i].itemarray[j].tostring();
//      if (j < columnnumber - 1)
//      {
//        stringbuffer += "\t";
//      }
//    }
//    stringbuffer += "\n";
//  }
//  clipboard.clear();
//  clipboard.setdataobject(stringbuffer);
//  ((microsoft.office.interop.excel.range)excel.cells[1, 1]).select();
//  ((microsoft.office.interop.excel.worksheet)excel.activeworkbook.activesheet).paste(missing.value, missing.value);
//  clipboard.clear();

//  return true;
//}

//public bool datasettoexcel(dataset dataset, string filename, bool isshowexcle)
//{
//  datatable datatable = dataset.tables[0];
//  int rownumber = datatable.rows.count;
//  int columnnumber = datatable.columns.count;

//  if (rownumber == 0)
//  {
//    messagebox.show("没有任何数据可以导入到excel文件！");
//    return false;
//  }

//  //建立excel对象
//  microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application();
//  microsoft.office.interop.excel.workbook workbook = excel.application.workbooks.add(true);
//  excel.visible = false;//是否打开该excel文件

//  //填充数据
//  for (int i = 0; i < rownumber; i++)
//  {
//    for (int j = 0; j < columnnumber; j++)
//    {
//      excel.cells[i + 1, j + 1] = datatable.rows[i].itemarray[j];
//    }
//  }

//  //string filename = path + "\\" + datetime.now.tostring().replace(':', '_') + ".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.value, missing.value);

//  try
//  {
//    workbook.saved = true;
//    excel.usercontrol = false;
//    //excelapp.quit();
//  }
//  catch (exception exception)
//  {
//    messagebox.show(exception.message);
//  }
//  finally
//  {
//    workbook.close(microsoft.office.interop.excel.xlsaveaction.xlsavechanges, missing.value, missing.value);
//    excel.quit();
//  }

//  if (isshowexcle)
//  {
//    system.diagnostics.process.start(filename);
//  }
//  return true;
//}

//public bool datasettoexcel(dataset dataset, string filename, bool isshowexcle)
//{
//  datatable datatable = dataset.tables[0];
//  int rownumber = datatable.rows.count;//不包括字段名
//  int columnnumber = datatable.columns.count;
//  int colindex = 0;

//  if (rownumber == 0)
//  {
//    messagebox.show("没有任何数据可以导入到excel文件！");
//    return false;
//  }

//  //建立excel对象
//  microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application();
//  //excel.application.workbooks.add(true);
//  microsoft.office.interop.excel.workbook workbook = excel.workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet);
//  microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1];
//  excel.visible = isshowexcle;
//  //microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)excel.worksheets[1];
//  worksheet.name = "挠度数据";
//  microsoft.office.interop.excel.range range;

//  //生成字段名称
//  foreach (datacolumn col in datatable.columns)
//  {
//    colindex++;
//    excel.cells[1, colindex] = col.columnname;
//  }

//  object[,] objdata = new object[rownumber, columnnumber];

//  for (int r = 0; r < rownumber; r++)
//  {
//    for (int c = 0; c < columnnumber; c++)
//    {
//      objdata[r, c] = datatable.rows[r][c];
//    }
//    //application.doevents();
//  }

//  // 写入excel
//  range = worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, columnnumber]);
//  //range.numberformat = "@";//设置单元格为文本格式
//  range.value2 = objdata;
//  worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, 1]).numberformat = "yyyy-m-d h:mm";

//  //string filename = path + "\\" + datetime.now.tostring().replace(':', '_') + ".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.value, missing.value);

//  try
//  {
//    workbook.saved = true;
//    excel.usercontrol = false;
//    //excelapp.quit();
//  }
//  catch (exception exception)
//  {
//    messagebox.show(exception.message);
//  }
//  finally
//  {
//    workbook.close(microsoft.office.interop.excel.xlsaveaction.xlsavechanges, missing.value, missing.value);
//    excel.quit();
//  }

//  //if (isshowexcle)
//  //{
//  //  system.diagnostics.process.start(filename);
//  //}
//  return true;
//}

/// <summary>
/// 将数据集中的数据保存到excel文件
/// </summary>
/// <param name="dataset">输入数据集</param>
/// <param name="filename">保存excel文件的绝对路径名</param>
/// <param name="isshowexcle">是否打开excel文件</param>
/// <returns></returns>
public bool datasettoexcel(dataset dataset, string filename, bool isshowexcle)
{
datatable datatable = dataset.tables[0];
int rownumber = datatable.rows.count;//不包括字段名
int columnnumber = datatable.columns.count;
int colindex = 0;

if (rownumber == 0) 
{
messagebox.show("没有任何数据可以导入到excel文件！");
return false;
}

//建立excel对象 
microsoft.office.interop.excel.application excel = new microsoft.office.interop.excel.application();
//excel.application.workbooks.add(true);
microsoft.office.interop.excel.workbook workbook = excel.workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet);
microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1];
excel.visible = false;
//microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)excel.worksheets[1];
microsoft.office.interop.excel.range range;

//生成字段名称 
foreach (datacolumn col in datatable.columns)
{
colindex++;
excel.cells[1, colindex] = col.columnname;
}

object[,] objdata = new object[rownumber, columnnumber]; 

for (int r = 0; r < rownumber; r++) 
{
for (int c = 0; c < columnnumber; c++)
{
objdata[r, c] = datatable.rows[r][c];
}
//application.doevents();
}

// 写入excel 
range = worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, columnnumber]);
//range.numberformat = "@";//设置单元格为文本格式
range.value2 = objdata;
worksheet.get_range(excel.cells[2, 1], excel.cells[rownumber + 1, 1]).numberformat = "yyyy-m-d h:mm";

//string filename = path + "\\" + datetime.now.tostring().replace(':', '_') + ".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.value, missing.value);

try 
{
workbook.saved = true;
excel.usercontrol = false;
//excelapp.quit();
}
catch (exception exception)
{
messagebox.show(exception.message);
}
finally
{
workbook.close(microsoft.office.interop.excel.xlsaveaction.xlsavechanges, missing.value, missing.value);
excel.quit();
}

if (isshowexcle) 
{
system.diagnostics.process.start(filename);
}
return true;
}
}
}