C#Excel操作

1.添加对Microsoft.Office.Interop.Excel程序集引用

2.创建Excel应用对象

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

3.Excel操作

 　　3.1 相关的操作方法

　　参考博客：http://www.cnblogs.com/springyangwc/archive/2011/08/12/2136498.html

　　3.2 对Excel样式的操作

　　参考博客：http://www.cnblogs.com/flyinghigher/archive/2012/03/15/2398560.html

4.相关资料

　　DataSet数据Excel文件导出

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.Data.SqlClient;
 
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn;
            string str = ""; //SQL语句
            conn = new SqlConnection("server=;database=;uid=;pwd="); //数据库连接
            SqlCommand sqlcmd = new SqlCommand(str, conn);
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = sqlcmd;
            DataSet ds = new DataSet();
            sda.Fill(ds, "cs");
 
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "*.xlsx|*.xlsx";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "导出文件保存路径";
            saveFileDialog.ShowDialog();
            string strName = saveFileDialog.FileName;
            if (strName.Length != 0)
            {
                if (ds.Tables[0].Rows.Count == 0)
                {
                    return;
                }
                else
                {
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;
                    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                    excel.Application.Workbooks.Add(true);
                    excel.Visible = false;
                    if (excel == null)
                    {
                        MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                    Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                    sheet.Name = "Sheet1";
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        excel.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
                    }
                    for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        {
                            excel.Cells[i + 2, j + 1] = ds.Tables[0].Rows[i][j];
                        }
                    }
                    sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                    book.Close(false, miss, miss);
                    books.Close();
                    excel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    GC.Collect();
                    MessageBox.Show("数据已经成功导出!");
                    System.Diagnostics.Process.Start(strName);
                }
            }
        }
    }
}

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 += "	"; 
//            } 
//        } 
//        stringBuffer += "
"; 
//    } 
//    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; 
} 
} 
}

5.附加

xSheet.Copy(Type.Missing, workbook.Sheets[1]);
xSheet.Columns.Clear();
xSheet.Rows.Clear();
xSheet.Name = "复制的Sheet";