C# Datatable to excel NPOI导出

excel NPOI导出

        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="Name">导出的数据名称</param>
        /// <param name="IsDown">是否在浏览器下载</param>
        public static void ExportExcel(this DataTable dt, string Name = "", bool IsDown = false)
        {
            try
            {
                //创建一个工作簿
                IWorkbook workbook = new HSSFWorkbook();
                if (string.IsNullOrWhiteSpace(Name))
                {
                    Name = "导出的数据";
                }
                //创建一个 sheet 表
                ISheet sheet = workbook.CreateSheet(Name);

                //创建一行
                IRow rowH = sheet.CreateRow(0);

                //创建一个单元格
                ICell cell = null;

                //创建单元格样式
                //ICellStyle cellStyle = workbook.CreateCellStyle();

                //创建格式
                //IDataFormat dataFormat = workbook.CreateDataFormat();

                //设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
                //cellStyle.DataFormat = dataFormat.GetFormat("@");

                //标头格式
                var HeadStyle = CreateCellStyle(workbook, true);
                var BodyStyle = CreateCellStyle(workbook, false);
                //设置列名
                foreach (DataColumn col in dt.Columns)
                {
                    //创建单元格并设置单元格内容
                    rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);

                    //设置单元格格式
                    rowH.Cells[col.Ordinal].CellStyle = HeadStyle;
                }

                //写入数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int columnWidth = sheet.GetColumnWidth(i) / 256;

                    //跳过第一行，第一行为列名
                    IRow row = sheet.CreateRow(i + 1);
                    int length = 10;
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());

                        cell.CellStyle = BodyStyle;
                        length = Encoding.Default.GetBytes(dt.Rows[i][j].ToString()).Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }

                    sheet.SetColumnWidth(i, columnWidth * 256);
                }

                //创建一个 IO 流
                MemoryStream ms = new MemoryStream();
                //写入到流
                workbook.Write(ms);
                //转换为字节数组
                byte[] bytes = ms.ToArray();

                //还可以调用下面的方法，把流输出到浏览器下载
                if (IsDown)
                {
                    OutputClient(bytes);
                }
                else
                {   //设置导出文件路径
                    string path = AppDomain.CurrentDomain.BaseDirectory + "Export\\"; //HttpContext.Current.Server.MapPath("Export/");  //string dataDir = AppDomain.CurrentDomain.BaseDirectory;//获得当前服务器程序的运行目录

                    //判断是否已有目录
                    if (Directory.Exists(path) == false)
                    {
                        Directory.CreateDirectory(path);
                    }
                    //设置新建文件路径及名称
                    string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xls";

                    //创建文件
                    FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);
                    file.Write(bytes, 0, bytes.Length);
                    file.Flush();

                    file.Close();
                    file.Dispose();
                }
                //释放资源
                bytes = null;
                ms.Close();
                ms.Dispose();

                workbook.Close();
                sheet = null;
                workbook = null;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        /// <summary>
        /// 单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="isHead"></param>
        /// <returns></returns>
        private static ICellStyle CreateCellStyle(IWorkbook workbook, bool isHead)
        {
            var cellStyle = workbook.CreateCellStyle();

            var font = workbook.CreateFont();
            font.IsBold = isHead; // 粗体
            cellStyle.SetFont(font);
            if (isHead)
            {
                cellStyle.Alignment = HorizontalAlignment.Center; // 水平居中
                cellStyle.VerticalAlignment = VerticalAlignment.Center; // 垂直居中
            }
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.WrapText = true;//内容自动换行，避免存在换行符的内容合并成单行

            return cellStyle;
        }

        /// <summary>
        /// 浏览器下载
        /// </summary>
        /// <param name="bytes"></param>
        public static void OutputClient(byte[] bytes)
        {
            HttpResponse response = HttpContext.Current.Response;

            response.Buffer = true;

            response.Clear();
            response.ClearHeaders();
            response.ClearContent();

            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss")));

            response.Charset = "GB2312";
            response.ContentEncoding = Encoding.GetEncoding("GB2312");

            response.BinaryWrite(bytes);
            response.Flush();

            response.Close();
        }