tijian_tieying/web/dccdc/Common/export.cs

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace dccdc.Common
{
    public class Export
    {
        public static object ExportExcel(DataTable dt, string count = "")
        {
            try
            {
                //新建一个 Excel 工作簿
                ExcelPackage package = new ExcelPackage();
                // 添加一个 sheet 表
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dt.TableName == "" ? "sheel1" : dt.TableName);
                int rowIndex = 1;   // 起始行为 1
                int colIndex = 1;   // 起始列为 1

                //总设置 放在上面style 顶部优先
                //设置字体，也可以是中文，比如：宋体
                worksheet.Cells.Style.Font.Name = "微软雅黑";
                //字体大小
                worksheet.Cells.Style.Font.Size = 10;
                //垂直居中
                worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                //水平居中
                worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                //总结
                if (count != "")
                {
                    worksheet.Cells[rowIndex, colIndex, rowIndex, dt.Columns.Count].Merge = true;
                    worksheet.Cells[rowIndex, colIndex].Value = count;
                    worksheet.Cells[rowIndex, colIndex].Style.WrapText = true;
                    worksheet.Row(rowIndex).Style.Font.Color.SetColor(10, 238, 130, 238);
                    worksheet.Row(rowIndex).Height = 60;
                    worksheet.Row(rowIndex).Style.Font.Size = 15;
                    rowIndex++;
                }

                //设置列名
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[rowIndex, colIndex + i].Value = dt.Columns[i].ColumnName;
                }
                worksheet.Row(rowIndex).Style.Font.Color.SetColor(1, 131, 111, 255);
                worksheet.Row(rowIndex).Height = 25;
                rowIndex++;

                //写入数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        worksheet.Cells[rowIndex + i, colIndex + j].Value = dt.Rows[i][j].ToString();
                        //自动调整列宽，也可以指定最小宽度和最大宽度
                        worksheet.Column(colIndex + j).AutoFit();
                    }
                    worksheet.Row(rowIndex + i).Height = 25;
                }

                ////字体加粗
                //worksheet.Cells.Style.Font.Bold = true;
                ////字体颜色
                //worksheet.Cells.Style.Font.Color.SetColor(System.Drawing.Color.Black);

                ////单元格背景样式，要设置背景颜色必须先设置背景样式
                //worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
                ////单元格背景颜色
                //worksheet.Cells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.PapayaWhip);
                ////设置单元格所有边框样式和颜色
                //worksheet.Cells.Style.Border.BorderAround(ExcelBorderStyle.Medium, System.Drawing.Color.PeachPuff);

                ////单独设置单元格四边框 Top、Bottom、Left、Right 的样式和颜色
                ////worksheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                ////worksheet.Cells.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                ////单元格是否自动换行
                //worksheet.Cells.Style.WrapText = false;
                ////设置单元格格式为文本
                //worksheet.Cells.Style.Numberformat.Format = "@";
                ////单元格自动适应大小
                //worksheet.Cells.Style.ShrinkToFit = true;


                //第二种保存方式 //写入文件流
                string path = HttpContext.Current.Server.MapPath("/Export/");
                if (!Directory.Exists(path))
                    Directory.CreateDirectory(path);
                string filename = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";
                using (FileStream fileStream2 = new FileStream(path + filename, FileMode.Create))
                {
                    package.SaveAs(fileStream2);
                }

                //创建一个内存流，然后转换为字节数组，输出到浏览器下载
                //MemoryStream ms = new MemoryStream();
                //package.SaveAs(ms);
                //byte[] bytes = ms.ToArray();
                //也可以直接获取流
                //Stream stream = package.Stream;

                ////也可以直接获取字节数组
                //byte[] bytes = package.GetAsByteArray();
                ////调用下面的方法输出到浏览器下载
                //OutputClient(bytes);

                worksheet.Dispose();
                package.Dispose();
                return new { state = 1, message = "/Export/" + filename };
            }
            catch (Exception ex)
            {
                return new { state = 0, message = ex.Message };
            }
        }


        public static void OutputClient(byte[] bytes)
        {
            HttpResponse response = HttpContext.Current.Response;
            response.Buffer = true;

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

            //response.ContentType = "application/ms-excel";
            response.ContentType = "application/vnd.openxmlformats - officedocument.spreadsheetml.sheet";
            response.AppendHeader("Content-Type", "text/html; charset=GB2312");
            response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss")));

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

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

            response.End();
        }
    }
}