如何将c#导出数据到Excel？

1、filename是导出的文件名

//导出数据到Excel表

public void ExportExcel(DataSet my_Ds, string filename)

{

//DataSet ds = this.SqlDataSource1;

Excel.Application oExcel;

oExcel = new Excel.Application();

try

{

Excel.Workbook oBook;

Object oMissing = System.Reflection.Missing.Value;

oBook = oExcel.Workbooks.Add(oMissing);

HttpResponse response = HttpContext.Current.Response;

int lie = my_Ds.Tables[0].Columns.Count;

int hang = my_Ds.Tables[0].Rows.Count;

int i, j, t;

string panduanstring = "";

i = 1;

for (j = 0; j < lie; j++)//标题

{

oExcel.Cells[1, i++] = my_Ds.Tables[0].Columns[j].ColumnName;

}

t = 1;

for (i = 0; i < hang; i++)//内容

{

for (j = 0; j < lie; j++)

{

panduanstring = my_Ds.Tables[0].Rows[i][j].ToString();

if (panduanstring.GetType().ToString() == "System.String")

{

oExcel.Cells[i + 2, t++] = "'" + my_Ds.Tables[0].Rows[i][j].ToString();

}

else

oExcel.Cells[i + 2, t++] = my_Ds.Tables[0].Rows[i][j].ToString();

}

t = 1;

}

oExcel.Visible = true;

oBook.Saved = true;

oExcel.UserControl = false;

string path = Server.MapPath("excel/");

string mm = path + filename + ".xls";

oExcel.ActiveWorkbook.SaveCopyAs(mm);

oExcel.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oExcel);

GC.Collect();

response.Redirect("excel/" + filename + ".xls");

//oExcel.Quit();

//System.IO.File.Delete(path + filename + ".xls");

}

catch

{

//oExcel.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oExcel);

GC.Collect();

}

}

2、

using System;

using System.Data;

using System.Windows.Forms;

using System.IO;

namespace wsbmdcsj.operation

{

/// <summary>

/// ExportExcel 的摘要说明。

/// </summary>

public class ExportExcel

{

public ExportExcel()

{

//

// TODO: 在此处添加构造函数逻辑

//

}

//DataGrid导出Excel

public void ExptExcel(DataSet dataset)

{

DataSet ds = dataset;//取得dataGrid绑定的DataSet

if(ds==null) return;

string saveFileName="";

//bool fileSaved=false;

SaveFileDialog saveDialog=new SaveFileDialog();

saveDialog.DefaultExt ="xls";

saveDialog.Filter="Excel文件|*.xls";

saveDialog.FileName ="Sheet1";

saveDialog.ShowDialog();

saveFileName=saveDialog.FileName;

if(saveFileName.IndexOf(":")<0) return; //被点了取消

Excel.Application xlApp=new Excel.Application();

if(xlApp==null)

{

MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");

return;

}

System.Windows.Forms.Application.DoEvents();

Excel.Workbooks workbooks=xlApp.Workbooks;

Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

Excel.Range range;

//string oldCaption=this

long totalCount=ds.Tables[0].Rows.Count;//ds.Tables[0].Rows.Count;

//long rowRead=0;

//float percent=0;

//worksheet.Cells[1,1]=title;

//写入字段

for(int i=0;i<ds.Tables[0].Columns.Count;i++)

{

worksheet.Cells[1,i+1]=ds.Tables[0].Columns[i].ColumnName;

range=(Excel.Range)worksheet.Cells[1,i+1];

range.Interior.ColorIndex = 15;

range.Font.Bold = true;

}

//写入数值

//this.CaptionVisible = true;

for(int r=0;r<ds.Tables[0].Rows.Count;r++)

{

for(int i=0;i<ds.Tables[0].Columns.Count;i++)

{

worksheet.Cells[r+2,i+1]=ds.Tables[0].Rows[r][i];

}

//rowRead++;

//percent=((float)(100*rowRead))/totalCount;

//this.CaptionText = "正在导出数据["+ percent.ToString("0.00") +"％]...";

System.Windows.Forms.Application.DoEvents();

}

//this.CaptionVisible = false;

//this.CaptionText = oldCaption;

range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds.Tables[0].Rows.Count+2,ds.Tables[0].Columns.Count]);

range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);

//range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

//range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;

//range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;

if(ds.Tables[0].Columns.Count>1)

{

range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic;

range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;

range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;

}

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();//强行销毁

//导出后启动Excel

//if(fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName);

MessageBox.Show("成功导出Excell！","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);

}

}

}

本文地址：http://www.45fan.com/a/question/72952.html