【C#-导出Excel】DataSet导出Excel

1、添加引用

2、封装方法

using System;

using System.Data;

using System.IO;

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

namespace Demo

{

    /// <summary>

    /// Excel操作类

    /// </summary>

    public class Excel

    {

        private readonly HSSFWorkbook workbook = null;

        private readonly DataSet dataSet = null;

        private readonly string excelFilename = string.Empty;

        private readonly ICellStyle cellDateTimeStyle = null;

        /// <summary>

        /// Excel操作类构造函数

        /// </summary>

        /// <param name="dataSet"></param>

        /// <param name="excelFilename"></param>

        public Excel(DataSet dataSet, string excelFilename)

        {

            this.dataSet = dataSet;

            this.excelFilename = excelFilename;

            workbook = new HSSFWorkbook();

            IDataFormat iDataFormat = workbook.CreateDataFormat();

            //设置一个DateTime单元格的样式

            cellDateTimeStyle = workbook.CreateCellStyle();

            cellDateTimeStyle.DataFormat = iDataFormat.GetFormat("yyyy-m-d h:mm:ss");

        }

        /// <summary>

        /// 生成excel

        /// </summary>

        /// <returns></returns>

        public bool Write()

        {

            bool returns = false;

            using (FileStream saveFile = new FileStream(excelFilename, FileMode.Create, FileAccess.ReadWrite))

            {

                WriteExcelFile(dataSet);

                workbook.Write(saveFile);

                returns = true;

            }

            return returns;

        }

        private void WriteExcelFile(DataSet ds)

        {

            foreach (DataTable dt in ds.Tables)

            {

                ISheet sheet = workbook.CreateSheet(dt.TableName);

                WriteDataTableToExcelWorksheet(dt, sheet);

            }

        }

        private void WriteDataTableToExcelWorksheet(DataTable dt, ISheet sheet)

        {

            //生成sheet第一行列名

            IRow row = sheet.CreateRow();

            for (int i = ; i < dt.Columns.Count; i++)

            {

                ICell cell = row.CreateCell(i);

                cell.SetCellValue(dt.Columns[i].Caption);

            }

            //写入数据

            for (int i = ; i < dt.Rows.Count; i++)

            {

                IRow rows = sheet.CreateRow(i + );

                for (int j = ; j < dt.Columns.Count; j++)

                {

                    ICell cell = rows.CreateCell(j);

                    var cellValue = dt.Rows[i][j];

                    if (cellValue == DBNull.Value)

                    {

                        continue;

                    }

                    //类型判断处理

                    var type = cellValue.GetType();

                    var typeName = type.Name.ToLower();

                    switch (typeName)

                    {

                        case "int32":

                            cell.SetCellValue(double.Parse(cellValue.ToString()));

                            break;

                        case "string":

                            cell.SetCellValue(cellValue.ToString());

                            break;

                        case "datetime":

                            cell.CellStyle = cellDateTimeStyle;

                            cell.SetCellValue(DateTime.Parse(cellValue.ToString()));

                            break;

                        case "guid":

                            cell.SetCellValue(cellValue.ToString());

                            break;

                        case "boolean":

                            cell.SetCellValue(bool.Parse(cellValue.ToString()));

                            break;

                        default:

                            throw new Exception(string.Format("类型:{0}未做处理,请自行添加", typeName));

                    }

                }

            }

        }

    }

}

3、实际应用

using System;

using System.Data;

namespace Demo

{

    class Program

    {

        static void Main(string[] args)

        {

            DataSet dataSet = new DataSet();

            DataTable dataTable = new DataTable();

            dataTable.TableName = "Demo";

            dataTable.Columns.Add("Id", typeof(int));

            dataTable.Columns.Add("名称", typeof(string));

            dataTable.Columns.Add("年龄", typeof(int));

            dataTable.Columns.Add("添加日期", typeof(DateTime));

            for (int i = ; i < ; i++)

            {

                dataTable.Rows.Add(i, "名称demo" + i, , DateTime.Now);

            }

            dataSet.Tables.Add(dataTable);

            Excel excel = new Excel(dataSet, "Demo.xls");

            excel.Write();

        }

    }

}