C#读取Excel文档

C#读取Excel文档

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.IO;

namespace ZzsReadExcel
{
    public partial class Form1 : Form
    {
        private readonly byte[] Xor = new byte[] { 13, 14, 15, 16, 17 };

        public Form1()
        {
            InitializeComponent();
        }

        private void Btn_SelectExcel_Click(object sender, EventArgs e)
        {
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = string.Empty;
                foreach (var item in fileDialog.FileNames)
                {
                    textBox1.Text += item + "\r\n";
                }
            }
        }

        private void Btn_CreateData_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(textBox1.Text)) return;

            string[] strs = textBox1.Text.Trim().Split('\r', '\n');
            for (int i = 0; i < strs.Length; i++)
            {
                if (strs[i].Length == 0) continue;
                ReadExcel(strs[i]);
            }
        }

        private void ReadExcel(string path)
        {
            if (string.IsNullOrEmpty(path)) return;
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            DataTable dt = null;
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                using (OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn))
                {
                    DataSet ds = new DataSet();
                    myCommand.Fill(ds);
                    dt = ds.Tables[0];
                }
            }
            CreateData(path, dt);
        }

        private void CreateData(string path, DataTable dt)
        {
            int row = dt.Rows.Count;
            int columns = dt.Columns.Count;

            byte[] buffer = null;
            using (MemoryStream ms = new MemoryStream())
            {
                ms.Write(BitConverter.GetBytes(row), 0, 4);
                ms.Write(BitConverter.GetBytes(columns), 0, 4);
                for (int i = 0; i < row; i++)
                {
                    for (int j = 0; j < columns; j++)
                    {
                        byte[] tmp = System.Text.Encoding.UTF8.GetBytes(dt.Rows[i][j].ToString().Trim());
                        ms.Write(BitConverter.GetBytes(tmp.Length), 0, 4);
                        ms.Write(tmp, 0, tmp.Length);
                    }
                }
                buffer = ms.ToArray();
            }

            //压缩
            buffer = ZlibHelper.CompressBytes(buffer);
            //异或
            int length = Xor.Length;
            for (int i = 0; i < buffer.Length; i++)
            {
                buffer[i] = (byte)(buffer[i] ^ Xor[i % length]);
            }

            using (FileStream fs = new FileStream("Test.data", FileMode.Create))
            {
                fs.Write(buffer, 0, buffer.Length);
            }
            MessageBox.Show("提示", "创建成功!");
        }

        private void Btn_ReadData_Click(object sender, EventArgs e)
        {
            if (fileDialogData.ShowDialog() == DialogResult.OK)
            {
                ReadData(fileDialogData.FileName);
            }
        }

        private void ReadData(string path)
        {
            if (string.IsNullOrEmpty(path)) return;

            byte[] buffer = null;
            using (FileStream fs = new FileStream(path, FileMode.Open))
            {
                buffer = new byte[fs.Length];
                fs.Read(buffer, 0, buffer.Length);
            }
            //异或
            int length1 = Xor.Length;
            for (int i = 0; i < buffer.Length; i++)
            {
                buffer[i] = (byte)(buffer[i] ^ Xor[i % length1]);
            }
            //解压
            buffer = ZlibHelper.DeCompressBytes(buffer);
            using (MemoryStream ms = new MemoryStream(buffer))
            {
                byte[] tmp = new byte[4];
                byte[] tmp1 = new byte[4];
                ms.Read(tmp, 0, 4);
                ms.Read(tmp1, 0, 4);
                int rows = BitConverter.ToInt32(tmp, 0);
                int columns = BitConverter.ToInt32(tmp1, 0);
                System.Text.StringBuilder sbr = new System.Text.StringBuilder();
                for (int i = 0; i < rows; i++)
                {
                    for (int j = 0; j < columns; j++)
                    {
                        byte[] tmp2 = new byte[4];
                        ms.Read(tmp2, 0, 4);
                        int length = BitConverter.ToInt32(tmp2, 0);
                        byte[] tmp3 = new byte[length];
                        ms.Read(tmp3, 0, length);
                        string str = System.Text.Encoding.UTF8.GetString(tmp3);
                        sbr.Append(str + "\t");
                    }
                    sbr.Append("\r\n");
                }
                textBox2.Text = sbr.ToString();
            }
        }
    }
}