C# .NET Excel 导入 导出

1，Excel数据导入

　　将Excel作为类似access的数据源

　　using System.Data.OleDb;
　　using System.IO;

OpenFileDialog of = new OpenFileDialog();
of.Title = "Excel文件";
            of.FileName = "";
            of.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            of.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx";
            of.ValidateNames = true;
            of.CheckFileExists = true;
            of.CheckPathExists = true;
                string strExcel = "";
                OleDbDataAdapter olda = null;
                DataSet ds = new DataSet();
                strExcel = "select * from [" + sheetNames[0] + "]";
                olda = new OleDbDataAdapter(strExcel, strcon);

                olda.Fill(ds, "table1");

                List<string> columnNames = new List<string>();
                foreach (DataColumn dc in ds.Tables[0].Columns)
                {
                    columnNames.Add(dc.ColumnName);
                }
                this.progressBar1.Visible = true;
                this.progressBar1.Maximum = ds.Tables[0].Rows.Count;
                this.progressBar1.Value = 0;
                int count = 0;
                int sum = ds.Tables[0].Rows.Count;
                var nodeTypes = nodeManager.NodeTypes;

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    
                    if(string.IsNullOrEmpty(dr["名称"].ToString().Trim()))
                    {
                        continue;
                    }
                    Node node = nodeManager.New(nodeTypes[1]) as Node;
                    foreach (string name in columnNames)
                    {
                        if (string.IsNullOrEmpty(name.Trim()))
                        {
                            continue;
                        }
                        if (name == "名称")
                        {
                            node.DisplayName = dr[name].ToString().Trim();
                        }
                        else if (name == "描述")
                        {
                            node.Description = dr[name].ToString().Trim();
                        }
                        else
                        {
                            string value = dr[name].ToString().Trim();
                            if (!string.IsNullOrEmpty(value))
                            {
                                if (JackTools.CheckInt(value))
                                { node[name] = (object)int.Parse(value); }
                                else
                                    if (JackTools.CheckDouble(value))
                                    { node[name] = (object)double.Parse(value); }
                                    else
                                        if (JackTools.CheckDatetime(value))
                                        { node[name] = (object)DateTime.Parse(value); }
                                        else
                                            if (JackTools.CheckInt(value))
                                            { node[name] = (object)bool.Parse(value); }
                                            else
                                            { node[name] = value; }
                            }
                        }
                    }
                    this.progressBar1.Value++;
                    lblInfo.Text = (((double)++count / (double)ds.Tables[0].Rows.Count) * 100).ToString("0.00") + "%";
                System.Windows.Forms.Application.DoEvents();

}
                
                olcn.Close();
                this.Close();

　　

2,Excel 数据导出 生成Excel 文件

using System.IO;
using Excel=Microsoft.offic.interop.Excel


//要引用 Microsoft.office.interop.Excel.dll


    public class ExcelAndNodes
    {
        //导出邮件
        public static void ExportNodesToExcel(string fname,List<Node> list)
        {
            FileInfo file = new FileInfo(fname);
            string path = file.DirectoryName;
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = false;//是否显示Excel
            if (excel == null)
            {
                MessageBox.Show("Excel无法启动！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            Excel.Workbooks books = (Excel.Workbooks)excel.Workbooks;
            Excel.Workbook book = (Excel.Workbook)(books.Add(miss));
            Excel.Worksheet sheet = (Excel.Worksheet)(book.ActiveSheet);
            sheet.Name = "sheet1";

            //int m = 0, n = 0;
            int icolumn = 0;//已经有多少列
            //列名称行
            Dictionary<string, int> dic = new Dictionary<string, int>();
            //dic.Add("DisplayName", 1);
            //dic.Add("描述", 2);
            icolumn = 2;
            
            for (int index = 0; index < list.Count; index++)
            {

                excel.Cells[index + 2, 1] = "'" + list[index].DisplayName;
                excel.Cells[index + 2, 2] = "'" + list[index].Description;
                //int j = 2;//已经写入几列
                Hashtable ht = list[index].GetCustomProperties();
                foreach (DictionaryEntry item in ht)
                {
                    string name = item.Key.ToString();
                    if (!dic.ContainsKey(name))
                    {
                        dic.Add(name, ++icolumn);
                    }
                    int columnnum = dic[name];
                    excel.Cells[index + 2, columnnum] = "'" + (item.Value as CustomProperty).TypedValue.ToString();
                }
            }
            //写入列名
            excel.Cells[1, 1] = "'名称";
            excel.Cells[1, 2] = "'描述";


            foreach (KeyValuePair<string, int> item in dic)
            {
                excel.Cells[1, item.Value] = "'" + item.Key;
            }

            sheet.SaveAs(fname, 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(fname);
        }


    }


//
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel文件类型(*.xls,*.xlsx)|*.xls;*.xlsx";
            if(saveFileDialog.ShowDialog()==DialogResult.OK)
            {
                string fname = saveFileDialog.FileName;
                FileInfo file = new FileInfo(fname);
                string path = file.DirectoryName;
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                if (File.Exists(fname))
                {
                    if (MessageBox.Show("该文件已经存在，是否覆盖？", "系统提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
                    { 
                        //导出文件
                    }
                }
            }