csharp: Export or Import excel using MyXls



excel 2003 (效果不太理想)

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 org.in2bits.MyXls;
using org.in2bits.MyXls.ByteUtil;
using System.IO;
using Directory = org.in2bits.MyOle2.Directory;
using NUnit.Framework;
using org.in2bits.MyOle2;
using System.Diagnostics;

namespace MyxlsDemo
{

    /// <summary>
    /// 涂聚文
    /// 20150730
    /// 效果不太理想.
    /// </summary>
    public partial class Form2 : Form
    {
        string strFileUrl = "";
        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        DataSet setData()
        {
            //Create an Emplyee DataTable
            DataTable employeeTable = new DataTable("Employee");
            employeeTable.Columns.Add("Employee ID");
            employeeTable.Columns.Add("Employee Name");
            employeeTable.Rows.Add("1", "涂聚文");
            employeeTable.Rows.Add("2", "geovindu");
            employeeTable.Rows.Add("3", "李蘢怡");
            employeeTable.Rows.Add("4", "ноппчц");
            employeeTable.Rows.Add("5", "ニヌネハヒフキカォноппчц");
            //Create a Department Table
            DataTable departmentTable = new DataTable("Department");
            departmentTable.Columns.Add("Department ID");
            departmentTable.Columns.Add("Department Name");
            departmentTable.Rows.Add("1", "IT");
            departmentTable.Rows.Add("2", "HR");
            departmentTable.Rows.Add("3", "Finance");

            //Create a DataSet with the existing DataTables
            DataSet ds = new DataSet("Organization");
            ds.Tables.Add(employeeTable);
            ds.Tables.Add(departmentTable);
            return ds;

        }
        /// <summary>
        ///
        /// </summary>
        public Form2()
        {
            InitializeComponent();
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form2_Load(object sender, EventArgs e)
        {
            this.dataGridView1.DataSource = setData().Tables[0];
        }
        /// <summary>
        /// Excel 2003
        /// 涂聚文
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnFile_Click(object sender, EventArgs e)
        {
            try
            {
                //bool imail = false;
                this.Cursor = Cursors.WaitCursor;
                openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                //JPEG Files (*.jpeg)|*.jpeg|PNG Files (*.png)|*.png|JPG Files (*.jpg)|*.jpg|GIF Files (*.gif)|*.gif
                openFileDialog1.Filter = "Excel 2000-2003 files(*.xls)|*.xls|Excel 2007 files (*.xlsx)|*.xlsx";//|(*.xlsx)|*.xlsx Image Files(*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|All files (*.*)|*.*  txt files (*.txt)|*.txt|All files (*.*)|*.*"
                openFileDialog1.FilterIndex = 2;
                openFileDialog1.RestoreDirectory = true;
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    if (!openFileDialog1.FileName.Equals(String.Empty))
                    {
                        //重新加载清除数据
                        //this.combSheet.DataSource = null;
                        //if (this.combSheet.Items.Count != 0)
                        //{
                        //    this.combSheet.Items.Clear();
                        //}
                        FileInfo f = new FileInfo(openFileDialog1.FileName);
                        if (f.Extension.Equals(".xls") || f.Extension.Equals(".XLS") || f.Extension.Equals(".xlsx"))
                        {
                            this.Cursor = Cursors.WaitCursor;
                            strFileUrl = openFileDialog1.SafeFileName;
                            this.txtFileUrl.Text = openFileDialog1.FileName;
                            string currentfilename = openFileDialog1.FileName;
                            this.txtFileUrl.Text = currentfilename;
                            XlsDocument xls = new XlsDocument(currentfilename);
                            DataTable com = new DataTable();
                            com.Columns.Add("id", typeof(int));
                            com.Columns.Add("name", typeof(string));
                           // xls.FileName = currentfilename;
                            for(int id  = 0; id < xls.Workbook.Worksheets.Count; id++)
                            {
                                com.Rows.Add(id,xls.Workbook.Worksheets[id].Name);
                            }
                            this.combSheet.DataSource = com;
                            this.combSheet.DisplayMember = "name";
                            this.combSheet.ValueMember = "id";
                            Worksheet sheet = xls.Workbook.Worksheets[0];
                            DataTable dt = new DataTable();
                            //xls.Workbook.Worksheets[0].Name.ToString();
                            int i = 0;
                            int FirstRow = (int)sheet.Rows.MinRow;
                            if (i == 0)
                            {
                                //write data in every cell in the first row in the first worksheet as the column header(note: in order to write data from xls document in DataTable)
                                for (int j = 1; j < sheet.Rows[1].CellCount + 1; j++)
                                {
                                    string ColumnName = Convert.ToString(sheet.Rows[1].GetCell(ushort.Parse(j.ToString())).Value);
                                    DataColumn column = new DataColumn(ColumnName);
                                    dt.Columns.Add(column);

                                }
                                FirstRow++;
                            }
                            // write data(not including column header) in datatable rows in sequence
                            for (int k = FirstRow; k < sheet.Rows.MaxRow + 1; k++)
                            {
                                Row row = sheet.Rows[ushort.Parse(k.ToString())];
                                DataRow dataRow = dt.NewRow();
                                for (int z = 1; z < sheet.Rows[ushort.Parse(k.ToString())].CellCount + 1; z++)
                                {
                                    // write data in the current cell if it exists
                                    if (row.GetCell(ushort.Parse(z.ToString())) != null)
                                    {
                                        dataRow[z - 1] = row.GetCell(ushort.Parse(z.ToString())).Value.ToString();
                                    }

                                }
                                dt.Rows.Add(dataRow);
                            }

                            this.dataGridView1.DataSource = dt;

                            this.Cursor = Cursors.Default;
                        }
                        else
                        {
                            MessageBox.Show("错添文件类型");
                        }
                    }
                    else
                    {
                        MessageBox.Show("你要选择一下精确位置的文件");
                    }

                }
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }

            this.Cursor = Cursors.Default;
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnImport_Click(object sender, EventArgs e)
        {

        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonExport_Click(object sender, EventArgs e)
        {
            ExportEasy(setData().Tables[0], "ex.xls");
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strFileName"></param>
        public static void ExportEasy(DataTable dtSource, string strFileName)
        {
            try
            {
                XlsDocument xls = new XlsDocument();
                Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
                //填充表头
                foreach (DataColumn col in dtSource.Columns)
                {
                    sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
                }

                //填充内容
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        sheet.Cells.Add(i + 2, j + 1, dtSource.Rows[i][j].ToString());
                    }
                }

                //保存
                xls.FileName = strFileName;
                xls.Save();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
        }
    }
}