myXls导出EXCEL

宓和同

2023-12-01

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DBL
{
    public class LineLossInfo
    {
        public LineLossInfo()
        {
 
        }
        public LineLossInfo(string Date, double ProvideValue, double UseValue, double LossValue, double LossRate, string Info)
        {
            this.Date = Date;
            this.ProvideValue = ProvideValue;
            this.UseValue = UseValue;
            this.LossValue = LossValue;
            this.LossRate = LossRate;
            this.Info = Info;
        }
        public string Date { get; set; }
        public string Info { get; set; }
        public double LossRate { get; set; }
        public double UseValue { get; set; }
        public double ProvideValue { get; set; }
        public double LossValue { get; set; }

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Utils
{
   public class CustomAttribute:Attribute
    {

       public string colName { get; set; }
       public int colNum { get; set; }
       public CustomAttribute(string colName)
       {
           this.colName = colName;
       }
       public CustomAttribute(string colName,int colNum):this(colName)
       {
           this.colNum = colNum;
       }
       public CustomAttribute()
       {
 
       }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using org.in2bits.MyXls.ByteUtil;
using org.in2bits.MyXls;
using System.Data;
using System.Reflection;
namespace Utils
{
    public class ExportToExcel<T> where T : class
    {

        
        public static object DataSource { get; set; } 
        /// <summary>
        /// 设置标题格式
        /// </summary>
        /// <param name="xls"></param>
        /// <returns></returns>
        public static XF SetTitle(XlsDocument xls)
        {
            XF xfTitle = xls.NewXF();//格式

            xfTitle.HorizontalAlignment = HorizontalAlignments.Centered;

            xfTitle.VerticalAlignment = VerticalAlignments.Centered;

            xfTitle.Font.FontName = "宋体";

            xfTitle.Font.Height = 16 * 20;

            xfTitle.Font.Bold = true;

            return xfTitle;

        }
        /// <summary>
        /// 设置数据格式
        /// </summary>
        /// <param name="xls"></param>
        /// <returns></returns>
        public static XF SetData(XlsDocument xls)
        {
            XF xfData = xls.NewXF();
            xfData.Font.FontName = "宋体";
            xfData.UseBorder = true;
            xfData.BottomLineStyle = 1;
            xfData.BottomLineColor = Colors.Black;
            xfData.TopLineStyle = 1;
            xfData.TopLineColor = Colors.Black;
            xfData.LeftLineStyle = 1;
            xfData.LeftLineColor = Colors.Black;
            xfData.RightLineStyle = 1;
            xfData.RightLineColor = Colors.Black;
            return xfData;
        }

        //设置Excel数据列标题的格式
        public static XF SetHead(XlsDocument xls)
        {
            XF xfDataHead = xls.NewXF();
            xfDataHead.HorizontalAlignment = HorizontalAlignments.Centered;
            xfDataHead.VerticalAlignment = VerticalAlignments.Centered;
            xfDataHead.Font.FontName = "宋体";
            xfDataHead.Font.Bold = true;
            xfDataHead.UseBorder = true;
            xfDataHead.BottomLineStyle = 1;
            xfDataHead.BottomLineColor = Colors.Black;
            xfDataHead.TopLineStyle = 1;
            xfDataHead.TopLineColor = Colors.Black;
            xfDataHead.LeftLineStyle = 1;
            xfDataHead.LeftLineColor = Colors.Black;
            xfDataHead.RightLineStyle = 1;
            xfDataHead.RightLineColor = Colors.Black;
            return xfDataHead;

        }

        public static XlsDocument ExportExcel(string Title, string FileName, string WorksheetName)
        {
            XlsDocument xls = new XlsDocument();
            xls.FileName = FileName;
            Worksheet sheet = xls.Workbook.Worksheets.Add(WorksheetName);

            XF xfDataHead = SetHead(xls);
            XF xfData = SetData(xls);
            Cells cells = sheet.Cells;
            if (DataSource != null)
            {
                if (DataSource is DataTable)
                {

                  DataTable dt=  DataSource as DataTable;
                   
                   

                }
                else if (DataSource is DataSet)
                {
                    DataSet  ds = DataSource as DataSet;
                }
                else if (DataSource is IList<T>)
                {

                    Type type = typeof(T);

                    PropertyInfo[] propertyInfos = type.GetProperties(); 
                 

                    foreach (PropertyInfo pro in propertyInfos)
                    {

                  
                        object obj = pro.GetCustomAttributes(false)[0];

                        CustomAttribute cus = null;

                        if (obj != null)
                        {
                            if (obj is CustomAttribute)
                            {

                                cus = obj as CustomAttribute;
                                ColumnInfo colInfo = new ColumnInfo(xls, sheet);
                                colInfo.ColumnIndexStart = (ushort)cus.colNum;
                                colInfo.ColumnIndexEnd = (ushort)cus.colNum;
                                colInfo.Width = 15 * 256;
                                sheet.AddColumnInfo(colInfo);
                                cells.Add(3, cus.colNum, cus.colName, xfDataHead);

                            }

                        }
                         
                        int row=3;

                        //获取值
                        foreach (T t in DataSource as IList<T>)
                        {

                            object val = pro.GetValue(t, null);
                            Console.WriteLine("row:{0},col:{1},val:{2}",row,cus.colNum,val);
                            row++;
                            cells.Add(row, cus.colNum, val,xfData);

                        } 

                    }

                    if (Title != "")
                    {
                        XF xfTitle = SetTitle(xls);
                        MergeArea merge = new MergeArea(1, 2, 1, propertyInfos.Length);
                        sheet.AddMergeArea(merge);
                        cells.Add(1, 1, Title, xfTitle);




                    }

                }
            }

            return xls;




        }
    }
}
 //参考网络资源
 private IList<LineLossInfo> lossListExcel = null;
        protected void Button_Del_Click(object sender, EventArgs e)
        {


            lossListExcel = new List<LineLossInfo>();

            for(int mi=1;mi<=100;mi++)
            {
            lossListExcel.Add(new LineLossInfo() { Date=DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ProvideValue=mi*10, Info="测试" , LossRate=mi, LossValue=mi+10*mi, UseValue=mi+10 });
            }

            XlsDocument xls = new XlsDocument();

            xls.FileName = "LineLossMonthDetail.xls";//指定文件名

            Worksheet sheet = xls.Workbook.Worksheets.Add("LineLossMonthDetail");//sheet名

            #region 设置各数据列的大小

            ColumnInfo colInfo1 = new ColumnInfo(xls, sheet);

            colInfo1.ColumnIndexStart = 1;

            colInfo1.ColumnIndexEnd = 1;

            colInfo1.Width = 15 * 256;

            sheet.AddColumnInfo(colInfo1);

            ColumnInfo colInfo2 = new ColumnInfo(xls, sheet);

            colInfo2.ColumnIndexStart = 2;

            colInfo2.ColumnIndexEnd = 2;

            colInfo2.Width = 15 * 256;

            sheet.AddColumnInfo(colInfo2);

            ColumnInfo colInfo3 = new ColumnInfo(xls, sheet);

            colInfo3.ColumnIndexStart = 3;

            colInfo3.ColumnIndexEnd = 3;

            colInfo3.Width = 15 * 256;

            sheet.AddColumnInfo(colInfo3);

            ColumnInfo colInfo4 = new ColumnInfo(xls, sheet);

            colInfo4.ColumnIndexStart = 4;

            colInfo4.ColumnIndexEnd = 4;

            colInfo4.Width = 15 * 256;

            sheet.AddColumnInfo(colInfo4);

            ColumnInfo colInfo5 = new ColumnInfo(xls, sheet);

            colInfo5.ColumnIndexStart = 5;

            colInfo5.ColumnIndexEnd = 5;

            colInfo5.Width = 32 * 256;

            sheet.AddColumnInfo(colInfo5);

            #endregion

            Cells cells = sheet.Cells;

            #region 合并单元格，得到报表标题

            MergeArea maTitle = new MergeArea(1, 2, 1, 6);

            sheet.AddMergeArea(maTitle);

            XF xfTitle = xls.NewXF();//格式

            xfTitle.HorizontalAlignment = HorizontalAlignments.Centered;

            xfTitle.VerticalAlignment = VerticalAlignments.Centered;

            xfTitle.Font.FontName = "宋体";

            xfTitle.Font.Height = 16 * 20;

            xfTitle.Font.Bold = true;

            cells.Add(1, 1, "测试标题", xfTitle);

            #endregion

            MergeArea maTime1 = new MergeArea(3, 3, 1, 6);

            sheet.AddMergeArea(maTime1);

            XF xfTopBar = xls.NewXF();

            xfTopBar.Font.FontName = "宋体";

            cells.Add(3, 1, "测试标题", xfTopBar);

            #region 设置Excel数据列标题的格式

            XF xfDataHead = xls.NewXF();

            xfDataHead.HorizontalAlignment = HorizontalAlignments.Centered;

            xfDataHead.VerticalAlignment = VerticalAlignments.Centered;

            xfDataHead.Font.FontName = "宋体";

            xfDataHead.Font.Bold = true;

            xfDataHead.UseBorder = true;

            xfDataHead.BottomLineStyle = 1;

            xfDataHead.BottomLineColor = Colors.Black;

            xfDataHead.TopLineStyle = 1;

            xfDataHead.TopLineColor = Colors.Black;

            xfDataHead.LeftLineStyle = 1;

            xfDataHead.LeftLineColor = Colors.Black;

            xfDataHead.RightLineStyle = 1;

            xfDataHead.RightLineColor = Colors.Black;

            #endregion

            #region 添加列标题

            cells.Add(4, 1, "日期", xfDataHead);

            cells.Add(4, 2, "供电量", xfDataHead);

            cells.Add(4, 3, "用电量", xfDataHead);

            cells.Add(4, 4, "损失量", xfDataHead);

            cells.Add(4, 5, "损失率", xfDataHead);

            cells.Add(4, 6, "说明", xfDataHead);

            #endregion

            #region 设置各数据列的格式

            XF xfData = xls.NewXF();

            xfData.Font.FontName = "宋体";

            xfData.UseBorder = true;

            xfData.BottomLineStyle = 1;

            xfData.BottomLineColor = Colors.Black;

            xfData.TopLineStyle = 1;

            xfData.TopLineColor = Colors.Black;

            xfData.LeftLineStyle = 1;

            xfData.LeftLineColor = Colors.Black;

            xfData.RightLineStyle = 1;

            xfData.RightLineColor = Colors.Black;

            #endregion

            #region 填充数据

            int i = 5;//从第五行开始为数据行

            double provideTotal = 0;

            double useTotal = 0;

            double lossTotal = 0;

            foreach (LineLossInfo lossItem in lossListExcel)
            {

                cells.Add(i, 1, lossItem.Date, xfData);

                if (Double.IsNaN(lossItem.ProvideValue))

                    cells.Add(i, 2, "-", xfData);

                else
                {

                    cells.Add(i, 2, lossItem.ProvideValue, xfData);

                    provideTotal += lossItem.ProvideValue;

                }

                if (Double.IsNaN(lossItem.UseValue))

                    cells.Add(i, 3, "-", xfData);

                else
                {

                    cells.Add(i, 3, lossItem.UseValue, xfData);

                    useTotal += lossItem.UseValue;

                }

                if (Double.IsNaN(lossItem.LossValue))

                    cells.Add(i, 4, "-", xfData);

                else
                {

                    cells.Add(i, 4, lossItem.LossValue, xfData);

                    lossTotal += lossItem.LossValue;

                }

                if (Double.IsNaN(lossItem.LossRate))

                    cells.Add(i, 5, "-", xfData);

                else

                    cells.Add(i, 5, lossItem.LossRate, xfData);

                if (String.IsNullOrEmpty(lossItem.Info))

                    cells.Add(i, 6, "-", xfData);

                else

                    cells.Add(i, 6, lossItem.Info, xfData);

                i++;

            }

            //添加总计

            cells.Add(i, 2, "总计：", xfData);

            cells.Add(i, 3, provideTotal, xfData);

            cells.Add(i, 4, useTotal, xfData);

            cells.Add(i, 5, lossTotal, xfData);

            #endregion

            //发送到客户端

            xls.Send();
        }

myXls导出EXCEL

相关阅读

相关文章

相关问答

相关文档