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

C#导出Excel的示例详解

程序员文章站 2023-12-16 13:02:46
本文实例为大家分享了c#导出excel的具体代码,供大家参考,具体内容如下 using system; using system.collections.gen...

本文实例为大家分享了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;
}
}
}

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。

上一篇:

下一篇: