对Excel的操作

微软已经封装了对Excel的操作，所以在自己项目先引用Microsoft.Office.Interop.Excel.dll程序集，代码中添加using Microsoft.Office.Interop.Excel;

Excel类的简单介绍：

ApplicationClass - 就是我们的excel应用程序。

Workbook - 就是我们平常见的一个个excel文件，经常是使用Workbooks类对其进行操作。

Workbook - 就是我们平常见的一个个excel文件，经常是使用Workbooks类对其进行操作。

Worksheet.Cells[row, column] - 就是某行某列的单元格，注意这里的下标row和column都是从1开始的，跟我平常用的数组或集合的下标有所不同。

知道了上述基本知识后，利用此类来操作excel就清晰了很多。

1.打开现有的Excel文件

Workbook workbook = xlsApp.Workbooks.Open(excelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.issing, Type.Missing, Type.Missing);  
Worksheet mySheet = workbook.Sheets[1] as Worksheet; //Wookbook默认有一个sheet页
mySheet.Name = "testsheet";  //这里设定sheet名称

2.复制sheet页

复制mySheet成一个新的sheet页，复制完后的名称是mySheet页名称后加一个(2)，这里就是testsheet(2)，复制完后，Worksheet的数量增加一个

mySheet.Copy(Type.Missing, workbook.Sheets[1]);

3.删除sheet页

xlsApp.DisplayAlerts = false; //如果想删除某个sheet页，首先要将此项设为fasle。 
(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete();

4.选中sheet页

(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Select(Type.Missing);

 

 5.另存sheet页

workbook.Saved = true; workbook.SaveCopyAs(filepath); 

 

 

6.释放Excel资源

workbook.Close(true, Type.Missing, Type.Missing); 
workbook = null; xlsApp.Quit(); xlsApp = null;
GC.Collect();//垃圾回收

 

 下面贴出我做的小的案例，导出数据到Excel表中：

前台页面：

 

 

 后台代码：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace importExcel
{
    public partial class 导出数据 : Form
    {
        private string saveAddr = string.Empty;
        public 导出数据()
        {
            InitializeComponent();
        }

        private void 导出数据_Load(object sender, EventArgs e)
        {
        }

        //执行导出
        private void btnConfirm_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(saveAddr))
            {
                MessageBox.Show("请选择地址!");
                return;
            }

            DataTable dt = getData(1, 0, 0, 0); ;
            int totalCount = Convert.ToInt32(dt.Rows[0][0].ToString());//获取总数量
            int rowPerPage = 100;//设置一次从表中取出多少数据

            int pageCount = Convert.ToInt32(totalCount / rowPerPage);//要取几次
            int yuCount = totalCount % rowPerPage;//最后一次要取的数量
             
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook book1 = excel.Application.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Worksheet sheet1 = null;
            if (!checkBox1.Checked)
            {
                sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)book1.Sheets[1];

            }

            int alreadyCount = 0;
            int PageIndex = 1;
            DataTable dt2 = null;

            while (PageIndex <= pageCount)
            {
                try
                {
                    dt2 = new DataTable();
                    dt2.Clear();
                    dt2 = getData(0, PageIndex, rowPerPage, 0);
                    alreadyCount += dt2.Rows.Count;

                    if (checkBox1.Checked)
                    {
                        addSheet(dt2, book1);
                    }
                    else
                    {
                        addToExcel(dt2, sheet1);
                    }
                    progressBar2.Value = 100 * alreadyCount / totalCount;
                }
                catch { }
                finally
                {
                    PageIndex++;
                }
            }

            DataTable dt3 = getData(2, PageIndex, rowPerPage, yuCount);
            if (!checkBox1.Checked)
            {
                addToExcel(dt3, sheet1);
            }
            else
            {
                addSheet(dt3, book1);
            }

            alreadyCount += dt3.Rows.Count;
            progressBar2.Value = 100 * alreadyCount / totalCount;

            string path = saveAddr;//Excel文件保存的位置
            excel.Visible = false;
            excel.ActiveWorkbook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            excel.Quit();
            excel = null;
            progressBar1.Value = 100;
            saveAddr = string.Empty;
            GC.Collect();//垃圾回收
            MessageBox.Show("导出数据成功！");
        }

        //选择保存地址
        private void btnSelectAddr_Click(object sender, EventArgs e)
        {
            saveFileDialog1.Filter = "Excel文件(*.xls)|*.xls|所有文件(*.*)|*.*";
            DialogResult dia = saveFileDialog1.ShowDialog();
            if (dia == DialogResult.OK)
            {
                saveAddr = saveFileDialog1.FileName;
                textBox1.Text = saveAddr;
            }
        }

        //获取数据
        private DataTable getData(int status, int pageNum, int rowsPerPage, int yuCount)
        {
            try
            {
                DataTable dt = new DataTable();
                using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                {
                    conn.Open();

                    SqlCommand cmd = conn.CreateCommand();
                    string sql = string.Format(@"SELECT TOP {0} * FROM Customer WHERE  CustomerID NOT IN 
                                                (SELECT TOP ({1}*{2}) CustomerID  FROM Customer Order by CustomerID  ) 
                                                Order by CustomerID   ",
                                                 rowsPerPage, pageNum - 1, rowsPerPage);
                    if (status == 1)
                    {
                        sql = "select  top 1 (select  count(*) from Customer)as TotalCount, * from Customer order by CustomerID ";
                    }

                    if (status == 2)
                    {
                        sql = string.Format(@"with aaa as(
                                    select top {0} * from Customer order by CustomerID  desc
                                    ) select * from aaa order by CustomerID ", yuCount);
                    }
                    cmd.CommandText = sql;

                    SqlDataAdapter ada = new SqlDataAdapter(cmd);
                    ada.Fill(dt);
                }
                return dt;
            }
            catch { GC.Collect(); }
            return null;
        }

        //数据添加到Excel,不分sheet
        int rowIndex = 1;
        private bool addToExcel(DataTable dt, Microsoft.Office.Interop.Excel.Worksheet sheet1)
        {
            bool isSuc = false;
            try
            {
                //添加表头
                if (rowIndex == 1)
                {
                    dt.Columns.RemoveAt(0);
                    int columnIndex = 0;
                    foreach (DataColumn col in dt.Columns)
                    {
                        columnIndex++;
                        sheet1.Cells[1, columnIndex] = col.ColumnName;
                    }
                }

                int colIndex = 0;
                int itemRowIndex = 0;
                int totalCount = dt.Rows.Count;

                foreach (DataRow row in dt.Rows)
                {
                    rowIndex++;
                    itemRowIndex++;
                    progressBar1.Value = 100 * itemRowIndex / totalCount;

                    colIndex = 0;
                    foreach (DataColumn col in dt.Columns)
                    {
                        colIndex++;
                        sheet1.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                    }
                }

                isSuc = true;
            }
            catch
            {
                GC.Collect();
                isSuc = false;
            }
            return isSuc;
        }

        //数据添加excel,分sheet
        int sheetIndex = 0;
        void addSheet(DataTable dt, Microsoft.Office.Interop.Excel.Workbook workbook)
        {
            try
            {
                int colIndex = 0;
                int itemRowIndex = 0;
                int totalCount = dt.Rows.Count;
                sheetIndex++;

                Microsoft.Office.Interop.Excel.Worksheet sheet = null;
                if (sheetIndex != 1)
                {
                    sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                    sheet.Name = string.Format("第{0}页", sheetIndex);
                }
                else
                {
                    //wookbook默认有一个sheet
                    sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
                    sheet.Name = "第1页";
                }

                //添加表头
                int columnIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    columnIndex++;
                    sheet.Cells[1, columnIndex] = col.ColumnName;
                }

                foreach (DataRow row in dt.Rows)
                {
                    itemRowIndex++;
                    progressBar1.Value = 100 * itemRowIndex / totalCount;

                    colIndex = 0;
                    foreach (DataColumn col in dt.Columns)
                    {
                        colIndex++;
                        sheet.Cells[itemRowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                    }
                }
            }
            catch
            {
                GC.Collect();
            }
        }

        //内存回收
        private void 导出数据_FormClosed(object sender, FormClosedEventArgs e)
        {
            GC.Collect();//垃圾回收
            GC.Collect();//垃圾回收
        }



    }
}