导出Excel再总结（2）--新思路

当然，此方法不是本人摸索出来的，这里只是借鉴了网络上N多大大的提供，个人结合实际运用整理所得，主要是方便个人回顾，方便各位不知道此中方式的同志参考。大家可以讨论指正，提供出处及扩展。互相学习进步。

废话不说，懒人风格，贴代码：

此下为封装类。

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace CECC.WinAdmin.Service
{
    /// <summary>
    /// 通过文件结构直接生成xls文件
    /// </summary>
    public class ExcelWriter
    {
        FileStream _wirter;
        public ExcelWriter(string strPath)
        {
            _wirter = new FileStream(strPath, FileMode.OpenOrCreate);
        }
        /// <summary>
        /// 写入short数组
        /// </summary>
        /// <param name="values"></param>
        private void _writeFile(short[] values)
        {
            foreach (short v in values)
            {
                byte[] b = BitConverter.GetBytes(v);
                _wirter.Write(b, 0, b.Length);
            }
        }
        /// <summary>
        /// 写文件头
        /// </summary>
        public void BeginWrite()
        {
            _writeFile(new short[] { 0x809, 8, 0, 0x10, 0, 0 });
        }
        /// <summary>
        /// 写文件尾
        /// </summary>
        public void EndWrite()
        {
            _writeFile(new short[] { 0xa, 0 });
            _wirter.Close();
        }
        /// <summary>
        /// 写一个数字到单元格x,y
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="value"></param>
        public void WriteNumber(short x, short y, double value)
        {
            _writeFile(new short[] { 0x203, 14, x, y, 0 });
            byte[] b = BitConverter.GetBytes(value);
            _wirter.Write(b, 0, b.Length);
        }
        /// <summary>
        /// 写一个字符到单元格x,y
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="value"></param>
        public void WriteString(short x, short y, string value)
        {
            byte[] b = Encoding.Default.GetBytes(value);
            _writeFile(new short[] { 0x204, (short)(b.Length + 8), x, y, 0, (short)b.Length });
            _wirter.Write(b, 0, b.Length);
        }
    }
}

 

下面是运用实例，只贴主要代码了，主要是看框架。

private void btnDataToExcel_Click(object sender, EventArgs e)
        {
            this.btnDataToExcel.Enabled = false;
            if (this.PageNum * Program.SetPageSize <= 0)
            {
                this.btnDataToExcel.Enabled = true;
                MessageBox.Show("没有数据可以导出");
                return;
            }
            if (this.PageNum * Program.SetPageSize > 65535)
            {
                this.btnDataToExcel.Enabled = true;
                MessageBox.Show("数据量过大，请分批导出");
                return;
            }

            DataTable dtExcel = ST.GetAccountTableList(this.SqlTxt, 1, this.PageNum * Program.SetPageSize);
            string fileName = this.comboBox_FormType.Text+"财务报表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            if (string.IsNullOrEmpty(this.BOX_ClientCode.Text.Trim()))
            {
                fileName = "所有终端" + this.dateTimePicker_Start.Value.ToString("yyyyMMddHHmmss") + "至" + this.dateTimePicker_End.Value.ToString("yyyyMMddHHmmss") + fileName;
            }
            else
            {
                fileName = this.BOX_ClientCode.Text.Trim() + "终端" + this.dateTimePicker_Start.Value.ToString("yyyyMMddHHmmss") + "至" + this.dateTimePicker_End.Value.ToString("yyyyMMddHHmmss") + fileName;
            }
            this.saveFileDialog_Excel.FileName = fileName;
            string CurrFilePath = string.Empty;
            if (this.saveFileDialog_Excel.ShowDialog() == DialogResult.OK)
            {
                CurrFilePath = this.saveFileDialog_Excel.FileName;
            }
            else
            {
                this.btnDataToExcel.Enabled = true;
                MessageBox.Show("导出过程取消");
                return;
            }

            if (!string.IsNullOrEmpty(CurrFilePath))
            {
                try
                {
                    Service.ExcelWriter Ew = new CECC.WinAdmin.Service.ExcelWriter(CurrFilePath);
                    Ew.BeginWrite();
                    for (short i = 0; i < dtExcel.Rows.Count; i++)
                    {
                        for (short j = 0; j < dtExcel.Columns.Count; j++)
                        {
                            if (i == 0)
                            {
                                Ew.WriteString(i, j, dtExcel.Columns[Convert.ToInt32(j)].ColumnName);
                            }
                            Ew.WriteString(Convert.ToInt16(i+1), j, dtExcel.Rows[Convert.ToInt32(i)][Convert.ToInt32(j)].ToString());
                        }
                    }
                    Ew.EndWrite();

                    this.btnDataToExcel.Enabled = true;
                    CurrFilePath = string.Empty;
                    MessageBox.Show("导出成功！");
                }
                catch
                {
                    this.btnDataToExcel.Enabled = true;
                    CurrFilePath = string.Empty;
                    MessageBox.Show("导出失败！\n失败原因：\n可能是要覆盖的文件处于编辑状态，请关闭文件后再执行操作");
                }
            }
        }

 

好了，这次就到这里，人比较懒，没有过多赘述和解释。主要是不班门弄斧，大家见谅。

希望大家有想法的或者对此是专家级的，欢迎分享您的知识财富，呵呵，带领大家一起‘富’起来！