DataSet导出Excel

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.IO;

using System.Data ;

using System.Data.OleDb;



namespace ExportExcel

{



public class ExportExcel

{

public static void ExportToExcel(DataGridView dgv, string reportTitle)

{

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

if (xlApp == null)

{

MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

int rowIndex = 2;

int colIndex = 0;

Excel.Workbook xlBook = xlApp.Workbooks.Add(true);

Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, dgv.ColumnCount]);

range.MergeCells = true;

xlApp.ActiveCell.FormulaR1C1 = reportTitle;

xlApp.ActiveCell.Font.Size = 18;

xlApp.ActiveCell.Font.Bold = true;



foreach (DataGridViewColumn column in dgv.Columns)

{

colIndex = colIndex + 1;

xlApp.Cells[2, colIndex] = column.HeaderText;

}



for (int row = 0; row < dgv.Rows.Count; row++)

{

rowIndex = rowIndex + 1;

for (int col = 0; col < dgv.Columns.Count; col++)

{

xlApp.Cells[rowIndex, col + 1] = dgv.Rows[row].Cells[col].Value.ToString();

}

}



xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, dgv.Columns.Count]).Font.Bold = true;

xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 0;

xlApp.Cells.EntireColumn.AutoFit();

xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter;

xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter;

try

{

xlApp.Visible = false;

xlApp.Save(reportTitle);



}

catch

{



}

finally

{

xlApp.Quit();

}

}

public static void ExportToExcel(DataGridView dgv)

{

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

if (xlApp == null)

{

MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

int rowIndex = 1;

int colIndex = 0;

Excel.Workbook xlBook = xlApp.Workbooks.Add(true);



foreach (DataGridViewColumn column in dgv.Columns)

{

colIndex++;

xlApp.Cells[1, colIndex] = column.HeaderText;

}



for (int row = 0; row < dgv.Rows.Count; row++)

{

rowIndex++;

for (int col = 0; col < dgv.Columns.Count; col++)

{

xlApp.Cells[rowIndex, col + 1] = dgv.Rows[row].Cells[col].Value.ToString();

}

}

xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, dgv.Columns.Count]).Font.Bold = true;

xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 0;

xlApp.Cells.EntireColumn.AutoFit();

xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter;

xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter;

try

{

xlApp.Visible = false;

xlApp.Save("Sheet1");



}

catch

{



}

finally

{

xlApp.Quit();

}

}

public static void ExportToExcel(DataSet ds, string fileStrName)

{

Excel.Application excel = new Excel.ApplicationClass();

if (excel == null)

{

MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

int rowindex = 2;

int colindex = 0;

excel.Application.Workbooks.Add(true);

DataTable dt = ds.Tables[0];

Excel.Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns .Count]);

range.MergeCells = true;

excel.ActiveCell.FormulaR1C1 =fileStrName;

excel.ActiveCell.Font.Size = 18;

excel.ActiveCell.Font.Bold = true;

foreach (DataColumn col in dt.Columns)

{

colindex=colindex +1;

excel.Cells[2, colindex] = col.ColumnName;

}

foreach (DataRow row in dt.Rows)

{

colindex = 0;

rowindex++;

foreach (DataColumn col in dt.Columns)

{

colindex++;

excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();

}

}

excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Bold = true;

excel.get_Range(excel.Cells[1, 1], excel.Cells[rowindex, colindex]).Borders.LineStyle = 0;

excel.Cells.EntireColumn.AutoFit();

excel.Cells.VerticalAlignment = Excel.Constants.xlCenter;

excel.Cells.HorizontalAlignment = Excel.Constants.xlCenter;

try

{

excel.Visible = false;

excel.Save("Sheet1");



}

catch { }

finally

{

excel.Quit();

excel = null;

}

}

public static void ExportToExcel(DataSet ds)

{

Excel.Application excel = new Excel.ApplicationClass();

if (excel == null)

{

MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

int rowindex = 1;

int colindex = 0;

excel.Application.Workbooks.Add(true);

DataTable dt = ds.Tables[0];

foreach (DataColumn col in dt.Columns)

{

colindex++;

excel.Cells[1, colindex] = col.ColumnName;

}

foreach (DataRow row in dt.Rows)

{

colindex = 0;

rowindex++;

foreach (DataColumn col in dt.Columns)

{

colindex++;

excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();

}

}

excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Bold = true;

excel.get_Range(excel.Cells[1, 1], excel.Cells[rowindex, colindex]).Borders.LineStyle = 0;

excel.Cells.EntireColumn.AutoFit();

excel.Cells.VerticalAlignment = Excel.Constants.xlCenter;

excel.Cells.HorizontalAlignment = Excel.Constants.xlCenter;

try

{

excel.Visible = false;

excel.Save("Sheet1");

}

catch { }

finally

{

excel.Quit();

excel = null;

}



}

public static DataSet ExcelToDataSet(string strFilePath)

{

DataSet ds = null;

try

{

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFilePath + ";" + "Extended Properties=Excel 8.0;";

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

string strExcel = "";

OleDbDataAdapter myCommand = null;



strExcel = "select * from [Sheet1$]";

myCommand = new OleDbDataAdapter(strExcel, strConn);

ds = new DataSet();

myCommand.Fill(ds, "table1");

}

catch

{

return null;

MessageBox.Show("Excel文件属性设置有误,不能导入!");



}

return ds;

}

public static void ExportToText(DataGridView dgv, string reportTitle)

{

SaveFileDialog dlg = new SaveFileDialog();

dlg.Title = "输出报表";

dlg.Filter = "文本文件(*.txt)|*.txt";

if (DialogResult.OK == dlg.ShowDialog())

{

//遍历求出各列内容的最大长度，以便按格式对齐

int[] colContentLength = new int[dgv.ColumnCount];

for (int row = 0; row < dgv.Rows.Count; row++)

{

for (int col = 0; col < dgv.ColumnCount; col++)

{

if (dgv.Rows[row].Cells[col].Value.ToString().Length > colContentLength[col])

{

colContentLength[col] = dgv.Rows[row].Cells[col].Value.ToString().Length;

}

}

}



string fileName = dlg.FileName;

FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);

StreamWriter sw = new StreamWriter(fs);

//通过流来写文件

try

{

sw.WriteLine(reportTitle);

sw.WriteLine();

sw.WriteLine("----------------------------------------------------------");

//写列名

int position = 0;

foreach (DataGridViewColumn column in dgv.Columns)

{

sw.Write(column.HeaderText.PadRight(colContentLength[position++] + 4));

}

//写内容

for (int row = 0; row < dgv.Rows.Count; row++)

{

sw.WriteLine();

for (int col = 0; col < dgv.ColumnCount; col++)

{

sw.Write(dgv.Rows[row].Cells[col].Value.ToString().PadRight(colContentLength[col] + 8));

}

}

sw.WriteLine();

sw.WriteLine("----------------------------------------------------------");

sw.Flush();

}

catch

{

MessageBox.Show("导出文件出错，请重试！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

sw.Close();

fs.Close();

}



}

}





}

}要导入个COM 组件,很重要哦!Mirosoft Excel 11.0 Object Library 是要的