c# datatable转成excel类

string sql = "实际sql语句";
            DataTable dt = OracleHelper.ExecuteDataTable(sql);
            ExcelHepler excelHepler = new ExcelHepler();

            ArrayList arr_Get_Att = new ArrayList();

            //附件
            if (dt != null && dt.Rows.Count > 0)
            {
                string name = "汇总表-" + DateTime.Now.ToString("yyyyMMdd");
excel
                FileStream file = new FileStream(@"D:\bpmUnfinished\" + name + ".xls", FileMode.Create, System.IO.FileAccess.Write);
               
                byte[] bytes = excelHepler.DataTable2Excel(dt, name);
                file.Write(bytes, 0, bytes.Length);
                file.Close();
                arr_Get_Att.Add(@"D:\bpmUnfinished\" + name + ".xls");
              
            }

　　

ExcelHepler类：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace MfgLotState
{
    class ExcelHepler
    {
        /// <summary>
        /// 类版本
        /// </summary>
        public string version
        {
            get { return "0.1"; }
        }

        readonly int EXCEL03_MaxRow = 65535;

        /// <summary>
        /// 将DataTable转换为excel2003格式。
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public byte[] DataTable2Excel(DataTable dt, string sheetName)
        {

            IWorkbook book = new HSSFWorkbook();
            if (dt.Rows.Count < EXCEL03_MaxRow)
                DataWrite2Sheet(dt, 0, dt.Rows.Count - 1, book, sheetName);
            else
            {
                int page = dt.Rows.Count / EXCEL03_MaxRow;
                for (int i = 0; i < page; i++)
                {
                    int start = i * EXCEL03_MaxRow;
                    int end = (i * EXCEL03_MaxRow) + EXCEL03_MaxRow - 1;
                    DataWrite2Sheet(dt, start, end, book, sheetName + i.ToString());
                }
                int lastPageItemCount = dt.Rows.Count % EXCEL03_MaxRow;
                DataWrite2Sheet(dt, dt.Rows.Count - lastPageItemCount, lastPageItemCount, book, sheetName + page.ToString());
            }
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            return ms.ToArray();
        }
        private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, IWorkbook book, string sheetName)
        {
            ISheet sheet = book.CreateSheet(sheetName);
            IRow header = sheet.CreateRow(0);
            int lengthTmp = 10;
            int[] lengthColumn = new int[dt.Columns.Count];

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = header.CreateCell(i);
                string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
                cell.SetCellValue(val);

                lengthTmp = Encoding.Default.GetBytes(val).Length;
                if (lengthColumn[i] < lengthTmp)
                {
                    lengthColumn[i] = lengthTmp;
                }
            }

            int rowIndex = 1;
            for (int i = startRow; i <= endRow; i++)
            {
                DataRow dtRow = dt.Rows[i];
                IRow excelRow = sheet.CreateRow(rowIndex++);
                for (int j = 0; j < dtRow.ItemArray.Length; j++)
                {
                    excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());

                    lengthTmp = Encoding.Default.GetBytes(dtRow[j].ToString()).Length;
                    if (lengthColumn[j] < lengthTmp)
                    {
                        lengthColumn[j] = lengthTmp;
                    }
                }
            }

            for(int columnNum = 0; columnNum<dt.Columns.Count; columnNum++)
            {
                int columnWidth = sheet.GetColumnWidth(columnNum) / 256;

                columnWidth = lengthColumn[columnNum];

                if(columnWidth>100)
                {
                    columnWidth = 100;
                }
                else
                {
                    columnWidth = columnWidth + columnWidth/10;
                }
               
                sheet.SetColumnWidth(columnNum, columnWidth * 278);
            }

        }
    }
}