Excel表格

小鱼儿 2023-08-17 16:18 132阅读 0赞

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using org.in2bits.MyXls;
    
    namespace TravelB2B.Core.Utils.Excel
    {
        public class ExcelProvider
        {
            //列类型
    
            public enum ColumnType { Text, Number, 人民币 };
            //当前操作的文档
    
            private XlsDocument xls;
            //当前操作的工作表
            private Worksheet sheet;
            //保存路径，如果直接发送到客户端的话只需要名称 生成名称
            //当前操作行
    
            int row = 1;
    
            public ExcelProvider()
            {
                xls = new XlsDocument();//创建空xls文档
                xls.FileName = DateTime.Now.ToString("yy-MM-dd-HH-mm"); //保存路径，如果直接发送到客户端的话只需要名称 生成名称
                sheet = xls.Workbook.Worksheets.AddNamed(DateTime.Now.ToString("yy-MM-dd-HH-mm"));
                SetColumnInfo();           
            }
    
            public ExcelProvider(string fileName)
            {
                xls = new XlsDocument();//创建空xls文档
                xls.FileName = fileName; //保存路径，如果直接发送到客户端的话只需要名称 生成名称
                sheet = xls.Workbook.Worksheets.AddNamed(DateTime.Now.ToString("yy-MM-dd-HH-mm"));
                SetColumnInfo();           
            }
    
            public void Export(System.Data.DataTable dt, List<string> columnNames, List<ColumnType> columnTypes)
            {
                if (columnNames.Count != columnNames.Count)
                    throw new Exception("输入正确的列参数");
                Cells cells = sheet.Cells; //获得指定工作页列集合
                for (int i = 0; i < columnNames.Count; i++)
                {
                    //列操作基本
    
                    Cell cell = cells.Add(row, i + 1, columnNames[i], GetCellXF());//添加标题列返回一个列 参数：行 列 名称 样式对象
                    SetHeaderStyle(cell);
                }
    
                row++;
    
                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    for (int column = 1; column <= columnNames.Count; column++)
                    {
                        if (dr[column-1] != DBNull.Value)
                        {
                            Cell cell = cells.Add(row, column, dr[column - 1]);
                            SetCommonFont(cell.Font);
                            SetCellFormat(cell, columnTypes[column - 1]);
                        }
                    }
                    row++;
                }
                row += 2;
            }
    
            public void Send()
            {
                ////生成保存到服务器如果存在不会覆盖并且报异常所以先删除在保存新的
    
                //File.Delete(tempPath);//删除
                ////保存文档
                //xls.Save();//保存到服务器
                xls.Send();//发送到客户端
    
                ////生成保存到服务器如果存在不会覆盖并且报异常所以先删除在保存新的
    
                //File.Delete(tempPath);//删除
            }
    
            private void SetColumnInfo()
            {
                //设置文档列属性 
                ColumnInfo cinfo = new ColumnInfo(xls, sheet);//设置xls文档的指定工作页的列属性
    
                cinfo.Collapsed = true;
                //设置列的范围 如 0列-10列
    
                cinfo.ColumnIndexStart = 0;//列开始
    
                cinfo.ColumnIndexEnd = 10;//列结束
    
                cinfo.Collapsed = true;
                cinfo.Width = 60 * 60;//列宽度
    
                sheet.AddColumnInfo(cinfo);
                //设置文档列属性结束
    
            }
    
            private void SetHeaderStyle(Cell cell)
            {
                //设置XY居中
                cell.HorizontalAlignment = HorizontalAlignments.Centered;
                cell.VerticalAlignment = VerticalAlignments.Centered;
                //设置字体
                //cell.Font.Bold = true;//设置粗体
                cell.Font.ColorIndex = 0;//设置颜色码           
                //cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体 
                SetCommonFont(cell.Font);
                cell.UseBorder = true;//使用边框
                //创建列结束 
            }
    
            private void SetCommonStyle(Cell cell)
            {
                //cell.Font.FontFamily = FontFamilies.Default;//设置字体 默认为宋体  
                SetCommonFont(cell.Font);
            }
    
            private XF GetCellXF()
            {
                //创建列样式创建列时引用
    
                XF cellXF = xls.NewXF();
    
                cellXF.VerticalAlignment = VerticalAlignments.Centered;
                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
                //cellXF.Font.Height = 24 * 12;
                //cellXF.Font.Bold = true;
                cellXF.Pattern = 1;//设定单元格填充风格。如果设定为0，则是纯色填充
    
                cellXF.PatternBackgroundColor = Colors.Default31;//填充的背景底色
    
                cellXF.PatternColor = Colors.Default31;//设定填充线条的颜色
    
                //cellXF.Font.FontFamily = FontFamilies.Default;
                SetCommonFont(cellXF.Font);
                //创建列样式结束
    
                return cellXF;
            }
    
            private void SetCommonFont(Font font)
            {
                font.FontName = "宋体";
                font.Height = 10 * 20;
            }
    
            private void SetCellFormat(Cell cell, ColumnType type)
            {
                if (type == ColumnType.Text)
                { cell.Format = StandardFormats.General; }
                else if (type == ColumnType.人民币)
                { cell.Format = StandardFormats.Currency_3; }
                else if (type == ColumnType.Number)
                { cell.Format = StandardFormats.Decimal_1; cell.UseNumber = true; }
            }
        }
    
    }

1  #region 订单利润报表下载
      2         public void ProfitListExcelDown(DateTime? startTime, DateTime? endTime,int? dataType)
      3         {
      4             //结算 1，全部汇总  TotalNumber 2，结算金额  Settlement amount 3，零售金额 Retail value 4、分销利润 Distribution profit
      5            
      6             int TotalNumber = 0;
      7             decimal SettlementAmount = 0;
      8             decimal RetailValue = 0;
      9             decimal DistributionProfit = 0;
     10 
     11             dataType=dataType ?? 1;
     12             endTime = endTime ?? DateTime.Now;
     13             startTime = startTime ?? DateTime.Now.AddDays(-7);
     14 
     15             System.Collections.IList TotalData = TravelB2B.Core.Domain.Order.ListForProfitExcel(CurrentUser.Company.System.ID, CurrentUser.UserID, startTime, endTime);
     16 
     17             string str;
     18             #region 下单日期 or 游玩日期
     19             //
     20             if (dataType.Value == 1)
     21             {
     22                 str = "下单日期";
     23             }
     24             else
     25             {
     26                 str = "游玩日期";
     27             }
     28             #endregion
     29 
     30             #region 字段名称
     31             System.Collections.Generic.List<string> cellNames = new System.Collections.Generic.List<string>{
     32                         "订单号",
     33                          str,
     34                         "订单内容",
     35                         "订单票数",
     36                         "结算金额",
     37                         "零售金额",
     38                         "预付",
     39                         "返佣",
     40                         "分销利润",
     41                         "业务员"
     42                         };
     43             #endregion 
     44 
     45             #region 类型
     46             System.Collections.Generic.List<TravelB2B.Core.Utils.Excel.ExcelProvider.ColumnType> cellTypes =
     47                 new System.Collections.Generic.List<ExcelProvider.ColumnType>{
     48                         ExcelProvider.ColumnType.Text,
     49                         ExcelProvider.ColumnType.Text,
     50                         ExcelProvider.ColumnType.Text,
     51                         ExcelProvider.ColumnType.Text,
     52                         ExcelProvider.ColumnType.Text,
     53                         ExcelProvider.ColumnType.Text,
     54                         ExcelProvider.ColumnType.Text,
     55                         ExcelProvider.ColumnType.Text,
     56                         ExcelProvider.ColumnType.Text,
     57                         ExcelProvider.ColumnType.Text
     58                 };
     59             #endregion 
     60             //
     61             string fileName = "";
     62             if (startTime.HasValue) fileName += startTime.Value.ToString("yyyy-MM-dd");
     63             if (endTime.HasValue && startTime.Value != endTime.Value) fileName += "-" + endTime.Value.ToString("yyyy-MM-dd");
     64 
     65             ExcelProvider provider = new ExcelProvider(fileName+"利润明细表单");
     66 
     67             System.Data.DataTable data = new System.Data.DataTable();
     68 
     69             foreach (var name in cellNames)
     70             {
     71                 data.Columns.Add(name, typeof(object));
     72             }
     73 
     74             #region 查找出Excel表中的数据
     75             if (TotalData != null)
     76             {
     77                 foreach (object[] item in TotalData)
     78                 {
     79                     //a.ID,a.ProductName,a.TotalNumber,a.TotalConferPrice,a.PayTime,a.EffectiveTime,a.PaySet,a.TotalSalePrice,a.TotalPrice
     80                     string rebate="0";
     81                     string online="0";
     82                     
     83                     string time1=null;
     84 
     85                     //求出每一单的金额
     86                     decimal pay = Convert.ToDecimal(item[7]) - Convert.ToDecimal(item[8]);
     87 
     88                     #region 返佣
     89                     if (Convert.ToInt32(item[6]) == 0)
     90                     {
     91                         rebate = item[8].ToString();
     92                     }
     93                     else
     94                     {
     95                         online = item[7].ToString();
     96                     } 
     97                     #endregion
     98                     
     99                     #region 下单日期 or 游玩日期
    100                     //
    101                     if (dataType.Value == 1 && item[4] != null)
    102                     {
    103                         time1 = ((DateTime)item[4]).ToString("yyyy-MM-dd");
    104                     }
    105                     if (dataType.Value == 3 &&  item[5] != null)
    106                     {
    107                         time1 = ((DateTime)item[5]).ToString("yyyy-MM-dd");
    108                     } 
    109                     #endregion
    110 
    111                     data.Rows.Add(
    112                         item[0], //订单号 
    113                         time1, //下单日期
    114                         item[1], //订单内容
    115                         item[2],//订单票数
    116                         item[8], //结算金额
    117                         item[7].ToString(), //零售金额
    118                         online, //预付
    119                         rebate, //返佣
    120                         pay, //分销利润
    121                         ""//业务员
    122                     );
    123 
    124                     TotalNumber += Convert.ToInt32(item[2]);
    125                     SettlementAmount += Convert.ToDecimal(item[8]);
    126                     RetailValue += Convert.ToDecimal(item[7]);
    127                     DistributionProfit += pay;
    128                 }
    129                 data.Rows.Add("结算", "", "", TotalNumber, SettlementAmount, RetailValue, "", "", DistributionProfit, "");
    130                 provider.Export(data, cellNames, cellTypes);
    131                 provider.Send();
    132             } 
    133             #endregion
    134 
    135         }
    136 
    137         #endregion

转载于:https://www.cnblogs.com/hugeboke/p/11575192.html