详解C#利用DataTable导出Excel

本文主要实现利用总表向分表中添加数据，主界面如下：

在整个小项目中主要有一下几个关键步骤：Excel表的读取及转成datatable进行相关的数据操作，到后面datatable转成excel保存,

整个代码如下：

using System;using System.Collections.Generic;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.IO;using System.Data.OleDb;using System.Windows.Forms;<strong>using Microsoft.Office.Interop.Excel</strong>;namespace WindowsFormsApplication1{    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        string detailPath1 = "";        string detailPath2 = "";        private void button1_Click(object sender, EventArgs e)        {            OpenFileDialog oFile1 = new OpenFileDialog();            oFile1.Filter = "Excel文件(*.xls)|*.xls";            if (oFile1.ShowDialog() == DialogResult.OK)            {                string oFName1 = oFile1.FileName.ToString();                detailPath1 = textBox1.Text = oFName1;            }        }        private void button2_Click(object sender, EventArgs e)        {            OpenFileDialog oFile2 = new OpenFileDialog();            oFile2.Filter = "Excel文件(*.xls)|*.xls";            if (oFile2.ShowDialog() == DialogResult.OK)            {                string oFName2 = oFile2.FileName.ToString();                detailPath2 = textBox2.Text = oFName2;            }        }        //CheckedListBox实现单选        private void checkedListBox1_SelectedIndexChanged(object sender, EventArgs e)        {            for (int i = 0; i < checkedListBox1.Items.Count; i++)            {                checkedListBox1.SetItemChecked(i, false);            }            if (checkedListBox1.CheckedItems == null)            {                checkedListBox1.SetItemChecked(checkedListBox1.SelectedIndex, false);            }            else            {                checkedListBox1.SetItemChecked(checkedListBox1.SelectedIndex, true);            }        }        private void button3_Click(object sender, EventArgs e)        {            string pro = checkedListBox1.SelectedItem.ToString();            System.Data.DataTable dtRead = FillDataSet(detailPath1).Tables[0];            //获取读入表的行数            int readLine = dtRead.Rows.Count;            System.Data.DataTable dtWrite = FillDataSet(detailPath2).Tables[0];            //Excel表插入列            dtWrite.Columns.Add("代码", Type.GetType("System.String"));            //获取写入表的行数            int writeLine = dtWrite.Rows.Count;            //循环进行数据操作            for (int i = 0; i < writeLine; i++)            {                string num = dtWrite.Rows[i]["准考证号"].ToString();                for (int j = 0; j < readLine; j++)                {                    string number = dtRead.Rows[j]["考生号"].ToString();                    if (num.Equals(number))                    {                        if (pro.Equals(dtRead.Rows[j]["必考项目"].ToString()))                        {                            string code1 = dtRead.Rows[j]["必考代码"].ToString();                            dtWrite.Rows[i]["代码"] = code1;                        }                        else if (pro.Equals(dtRead.Rows[j]["选考项目"].ToString()))                        {                            string code1 = dtRead.Rows[j]["选考代码"].ToString();                            dtWrite.Rows[i]["代码"] = code1;                        }                        else                        {                        }                    }                }            }                        //路径截取            int index = detailPath2.LastIndexOf(@"\");            string test = detailPath2.Substring(0, index + 1);            string str = test + DateTime.Now.ToLongDateString().ToString() + "成绩导出" + pro + ".xls";            SaveDataTableToExcel(dtWrite, str);                    }        public static DataSet FillDataSet(string FilePath)        {            if (!File.Exists(FilePath))            {                throw new Exception("Excel文件不存在！");            }            ArrayList TableList = new ArrayList();            System.Data.DataSet ds = new DataSet();            string conStr = GetConnection(FilePath);            if (conStr.Length > 0)            {                TableList = GetExcelTables(FilePath, conStr);                if (TableList.Count <= 0)                {                    return null;                }                System.Data.DataTable table;                OleDbConnection dbcon = new OleDbConnection(conStr);                try                {                    if (dbcon.State == ConnectionState.Closed)                    {                        dbcon.Open();                    }                    for (int i = 0; i < TableList.Count; i++)                    {                        if (i < 1)                        {                            string dtname = TableList[i].ToString();                            try                            {                                OleDbCommand cmd = new OleDbCommand("select * from [" + dtname + "$]", dbcon);                                OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);                                table = new System.Data.DataTable(dtname);                                adapter.Fill(table);                                ds.Tables.Add(table);                            }                            catch (Exception exp)                            {                                throw exp;                            }                        }                        else                            break;                    }                }                finally                {                    if (dbcon.State == ConnectionState.Open)                    {                        dbcon.Close();                    }                }            }            return ds;        }        /// <summary>        /// 获取Excel数据表列表        /// </summary>        /// <returns></returns>        public static ArrayList GetExcelTables(string FilePath, string conStr)        {            //将Excel架构存入数据里            System.Data.DataTable dt = new System.Data.DataTable();            ArrayList TablesList = new ArrayList();            if (File.Exists(FilePath))            {                using (OleDbConnection conn = new OleDbConnection(conStr))                {                    try                    {                        conn.Open();                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                    }                    catch (Exception exp)                    {                        throw exp;                    }                    //获取数据表个数                    int tablecount = dt.Rows.Count;                    for (int i = 0; i < tablecount; i = i + 2)                    {                        if (i < 1)                        {                            string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');                            if (TablesList.IndexOf(tablename) < 0)                            {                                TablesList.Add(tablename);                            }                        }                        else                            break;                    }                }            }            return TablesList;        }        /// <summary>        /// 根据文件后缀选择对应的链接字符串并返回        /// </summary>        /// <param name="FilePath">文件链接</param>        /// <returns></returns>        public static string GetConnection(string FilePath)        {            int StratIndex = FilePath.LastIndexOf('.');            string Extension = FilePath.Substring(StratIndex, FilePath.Length - StratIndex);//文件后缀名            string conStr = "";//文件链接字符串            if (Extension == ".xlsx")                conStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " + FilePath + "; Extended properties=EXCEL 12.0";            else                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;  Data Source=" + FilePath + "; Extended Properties=Excel 8.0";            return conStr;        }        /// <summary>        /// 将datatable转化成excel保存        /// </summary>        public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)        {            Microsoft.Office.Interop.Excel.Application app =                new Microsoft.Office.Interop.Excel.ApplicationClass();            try            {                app.Visible = false;                Workbook wBook = app.Workbooks.Add(true);                Worksheet wSheet = wBook.Worksheets[1] as Worksheet;                int row = 0;                row = excelTable.Rows.Count;                int col = excelTable.Columns.Count;                //设置单元格格式为文本                Range r = wSheet.get_Range(app.Cells[1, 1], app.Cells[row + 1, col]);                r.NumberFormat = "@";                r.EntireColumn.AutoFit();                for (int i = 0; i < row; i++)                {                    for (int j = 0; j < col; j++)                    {                        string str = excelTable.Rows[i][j].ToString();                        wSheet.Cells[i + 2, j + 1] = str;                    }                }                for (int i = 0; i < col; i++)                {                    wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;                }                //设置禁止弹出保存和覆盖的询问提示框                app.DisplayAlerts = false;                app.AlertBeforeOverwriting = false;                //保存工作簿                wBook.Save();                //保存excel文件                app.Save(filePath);                app.SaveWorkspace(filePath);                app.Quit();                app = null;                MessageBox.Show("成绩导出完成！！！", "成绩导出", MessageBoxButtons.OK, MessageBoxIcon.Information);                return true;              }            catch (Exception err)            {                MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",                    MessageBoxButtons.OK, MessageBoxIcon.Information);                return false;            }            finally            {            }        }    }}

在整个小项目中涉及到Microsoft.Office.Interop.Excel引用，具体操作点击工具栏中的“项目”---> "添加引用"，选择Microsoft.Office.Interop.Excel 点击添加即可。