c# datagridview导出到excel【转载】

c# datagridview导出到excel【转载】 http://hi.baidu.com/weizier/blog/item/8212caea1123b4d6d439c9fe.html

本作者使用的是方法二:

方法一:添加dll引用

右击选择你所在的项目的“引用”,选择“添加引用”。

弹出“添加引用”对话框。

选择“COM”选项卡。

选择“Microsoft Excel 11.0 Object Library”

单击“确定”按钮。

代码

public static bool ExportForDataGridview(DataGridView gridView, string fileName, bool isShowExcle)
{ //建立Excel对象 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
if (app == null)
{
return false;
} app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return false;
}
string sLen = "";
//取得最后一列列名
char H = (char)(64 + gridView.ColumnCount / 26);
char L = (char)(64 + gridView.ColumnCount % 26);
if (gridView.ColumnCount < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
} //标题
string sTmp = sLen + "1";
Range ranCaption = worksheet.get_Range(sTmp, "A1");
string[] asCaption = new string[gridView.ColumnCount];
for (int i = 0; i < gridView.ColumnCount; i++)
{
asCaption[i] = gridView.Columns[i].HeaderText;
}
ranCaption.Value2 = asCaption; //数据
object[] obj = new object[gridView.Columns.Count];
for (int r = 0; r < gridView.RowCount - 1; r++)
{
for (int l = 0; l < gridView.Columns.Count; l++)
{
if (gridView[l, r].ValueType == typeof(DateTime))
{
obj[l] = gridView[l, r].Value.ToString();
}
else
{
obj[l] = gridView[l, r].Value;
}
}
string cell1 = sLen + ((int)(r + 2)).ToString();
string cell2 = "A" + ((int)(r + 2)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
ran.Value2 = obj;
}
//保存
workbook.SaveCopyAs(fileName);
workbook.Saved = true;
}
finally
{
//关闭
app.UserControl = false;
app.Quit();
}
return true; }

方法二

用流保存成xls文件.  这种方法比较好,不用引用Excel组件.     下面是具体例子,可以参考

using System.IO;

///<summary>

///另存新档按钮

///</summary>

private void SaveAs()  //另存新档按钮     导出成Excel

{

SaveFileDialog saveFileDialog = new SaveFileDialog();

saveFileDialog.Filter = "Execl files (*.xls)|*.xls";

saveFileDialog.FilterIndex = 0;

saveFileDialog.RestoreDirectory = true;

saveFileDialog.CreatePrompt = true;

saveFileDialog.Title = "Export Excel File To";

saveFileDialog.ShowDialog();

Stream myStream;

myStream = saveFileDialog.OpenFile();

//StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));

StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));

string str = "";

try

{

//写标题

for (int i = 0; i < dgvAgeWeekSex.ColumnCount; i++)

{

if (i > 0)

{

str += "\t";

}

str += dgvAgeWeekSex.Columns[i].HeaderText;

}

sw.WriteLine(str);

//写内容

for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++)

{

string tempStr = "";

for (int k = 0; k < dgvAgeWeekSex.Columns.Count; k++)

{

if (k > 0)

{

tempStr += "\t";

}

tempStr += dgvAgeWeekSex.Rows[j].Cells[k].Value.ToString();

}

sw.WriteLine(tempStr);

}

sw.Close();

myStream.Close();

}

catch (Exception e)

{

MessageBox.Show(e.ToString());

}

finally

{

sw.Close();

myStream.Close();

}

}

方法三

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace AssetsUI {
 public partial class FrmPrint : Form {
  DataSet publicDs = null;
  //Form main=null;
  public FrmPrint(System.Data.DataSet privateDs) {//System.Data.DataSet privateDs
   publicDs = privateDs;
   //main=f;
   InitializeComponent();
  }
  string filePath="";
  System.Data.DataTable dt=new System.Data.DataTable();
  DataSet ds=new DataSet();
  private void btnOpen_Click(object sender, EventArgs e) {
   if(DialogResult.OK==this.saveFileDialog1.ShowDialog()){//打开“打开文件对话框”
    filePath=this.saveFileDialog1.FileName;
    this.txtFileName.Text=filePath;
   }
  }
  public void print(){
   int rowIndex = 1;//行起始坐标
   int colIndex = 0;//列起始坐标
   ApplicationClass EXL = new ApplicationClass();
   _Workbook WBook;
   _Worksheet WSheet;
   if (EXL == null) {
    throw (new Exception("EXCEL没有安装!"));
   }
   WBook = EXL.Workbooks.Add(true);
   WSheet = (_Worksheet)WBook.ActiveSheet;
   WSheet.Name = this.txtCaption.Text;//"我的Excel";//表名字
   //---------------------
   foreach (DataColumn col in publicDs.Tables[0].Columns)
    {
    colIndex++;
    WSheet.Cells[1, colIndex] = col.ToString();
    WSheet.get_Range(WSheet.Cells[4, colIndex], WSheet.Cells[4, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
   //colIndex++;
   //WSheet.Cells[4, colIndex] = "列2";
   //WSheet.get_Range(WSheet.Cells[4, colIndex], WSheet.Cells[4, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
    }
   
   
    foreach(DataRow row in publicDs.Tables[0].Rows)
    {
     colIndex = 0;
     rowIndex++;
     foreach (DataColumn c in publicDs.Tables[0].Columns){
      colIndex++;
      WSheet.Cells[rowIndex, colIndex] = row[colIndex-1].ToString();
      WSheet.get_Range(WSheet.Cells[rowIndex, colIndex],WSheet.Cells[rowIndex, colIndex]).HorizontalAlignment =Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                 }
    }
    //定义一个使用缺省参数的对象
    object oMissiong = System.Reflection.Missing.Value;
    WSheet.SaveAs(filePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
    WBook.Save();
    //停止excel应用程序
    EXL.Quit();
    //释放资源
    System.Runtime.InteropServices.Marshal.ReleaseComObject(WSheet);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(WBook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(EXL);
    System.GC.Collect();//强制垃圾回收
   //--------end--------------
  }
  
  private void FrmPrint_Load(object sender, EventArgs e) {
   this.txtCaption.Text=DateTime.Now.ToShortDateString();
   dt=publicDs.Tables[0].Clone();
   //foreach(DataColumn c in dt.Columns){
   //for(int i=0;i<dt.Columns.Count;i++){
   //    dt.Columns.Add("a");
   //}
   dataGridView1.DataSource = dt;
  }
  private void btnOK_Click(object sender, EventArgs e) {
   print();
  }
 }
}

上一篇:mongodb window安装配置


下一篇:DataGridView导出到Excel的三个方法