详解C#利用DataTable导出Excel

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_30401659/article/details/51013955

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

整个小项目中主要有一下几个关键步骤: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 点击添加即可。

没有更多推荐了,返回首页