使用 Aspose.Cells 实现 excel导入

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Diagnostics;
using Aspose.Cells;
public partial class P_BrieFing_BrieFingExcelExport_MSLL : System.Web.UI.Page
{
    protected string PID = string.Empty;//工程主键
    protected string XM_ID = string.Empty;//监测项目编号
    protected void Page_Load(object sender, EventArgs e)
    {
        GetUrlRequest();
        GetInfo();
    }
    #region 获得URL中的参数
    protected void GetUrlRequest()
    {
        PID = Request.QueryString["PID"];
        XM_ID = Request.QueryString["XM_ID"];
    }
    #endregion

    #region 得到监测的工程基本数据数据
    protected void GetInfo()
    {
        P_PROJECT bll = new P_PROJECT();
        P_PROJECTInfo ProjectModel = bll.GetModelById(PID);
        string Test_Orgcode = MyCommon.GetEnterpriseCodeByID(ProjectModel.E_ENTERPRISEID);//获取当前工程 监测机构用户编号
        string PROJECT_NUM = ProjectModel.CODE;//项目编号
        string ProName = ProjectModel.NAME;//项目名称
        string ProAddress = ProjectModel.ADDRESS;//项目地址

        List<DBParameter> paras = new List<DBParameter>();
        paras.Add(new OracleDBParameter("p_Projectid", PID));
        paras.Add(new OracleDBParameter("XM_ID", XM_ID));
        P_ITEM ITemBll = new P_ITEM();
        var ITEMModel = ITemBll.GetModel(paras, "");

        E_DEVICE DeviceBll = new E_DEVICE();
        E_DEVICEInfo entity = DeviceBll.GetModel(ITEMModel.E_DEVICEID);
        string XMJCYQ = "";
        if (entity != null)
        {
            XMJCYQ = entity.NAME;//项目监测仪器
        }
        SetExcelData(ProName, ProAddress, XMJCYQ, PROJECT_NUM);
    }

    #endregion

    #region 往Excel写入数据
    protected void SetExcelData(string ProName, string Address, string JCYQ, string PROJECT_NUM)
    {

        Workbook SourceBook1 = new Workbook();
        string TempatPath = Server.MapPath("~/ExcelTemplate/锚索拉力模板.xls");

        XM_YL_F bllYL = new XM_YL_F();
        DataTable dtFromDB = bllYL.GetDataTableByProjectNum(PROJECT_NUM);//从数据库中读取数据
        //for (int j = 0; j < dtFromDB.Rows.Count;j++)
        //{
        //    string result = Convert.ToString(dtFromDB.Rows[j]["result"]);
        //    string[] arrs = result.Split(',');//获得一条数据
        //}
        int JCAmount = 0;
        if (dtFromDB.Rows.Count > 0)
        {
            JCAmount = Convert.ToString(dtFromDB.Rows[0]["result"]).Split(',').Length + 1;//获得监测次数 数据result是从第二次获得的 因此+1
        }
        List<string> fileList = new List<string>();

        double TableAmount = Math.Ceiling(Convert.ToDouble(JCAmount) / 5);//获得多少个Excel

        int JCDAmount = dtFromDB.Rows.Count;//有几个监测点 就有几行
        #region 如果存在数据
        if (TableAmount > 0)
        {
            for (int k = 0; k < TableAmount; k++)//循环生成Excel
            {

                Workbook tempBook = new Workbook();

                //创建设计模板对象，并绑定数据源
                WorkbookDesigner designer = new WorkbookDesigner();

                #region 给第X次赋值
                designer.Open(TempatPath);
                int jc1 = k * 5 + 1;
                if (k == 0)
                {
                    designer.SetDataSource("CS1", "第1次");
                }
                else
                {
                    designer.SetDataSource("FCS", "上次累计(m)");
                    designer.SetDataSource("CS1", "第" + (jc1) + "次");
                }


                int jc2 = k * 5 + 0;//如果是第二页 jc2=5
                int jc2N = jc2 + 2;
                designer.SetDataSource("CS2", "第" + jc2N + "次");

                int jc3 = k * 5 + 1;
                int jc3N = jc3 + 2;
                designer.SetDataSource("CS3", "第" + jc3N + "次");

                int jc4 = k * 5 + 2;
                int jc4N = jc4 + 2;
                designer.SetDataSource("CS4", "第" + jc4N + "次");

                int jc5 = k * 5 + 3;
                int jc5N = jc5 + 2;
                designer.SetDataSource("CS5", "第" + jc5N + "次");

                #endregion

                DataTable datatable = new DataTable();
                datatable = CreateTable("CD,CS,Num1,Num2,Num3,Num4,Num5,Num6,,Num7,Num8,Num9,Num10,Num11,Num12,Num13,Num14,Num15", "JK");

                #region 循环取出数据
                for (int i = 0; i < JCDAmount; i++)
                {
                    DataRow row = datatable.NewRow();

                    string result = Convert.ToString(dtFromDB.Rows[i]["result"]);

                    row["CD"] = Convert.ToString(dtFromDB.Rows[i]["SY_Num"]);//测点
                    if (k != 0)//如果不是第一页 则需要去上一页中的累计值 
                    {
                        int LastLJ = k * 5 - 2;//第6条数据 应该是第5条数据 而第一天数据是空的 因此-2
                        row["CS"] = result.Split(',')[LastLJ].Split('@')[2];
                    }
                    else
                    {
                        row["CS"] = Convert.ToString(dtFromDB.Rows[i]["F0"]);//初始数据
                    }


                    if (k == 0)
                    {
                        row["Num1"] = "0";
                        row["Num2"] = "0";
                        row["Num3"] = "0";//第一次数据 水平位移为0

                    }
                    else
                    {
                        if (jc1 <= JCAmount - 2)
                        {//如果是1111.0则用"/"替换
                            row["Num1"] = result.Split(',')[jc1].Split('@')[1] == "1111.0" ? "/" : result.Split(',')[jc1].Split('@')[1];
                            row["Num2"] = result.Split(',')[jc1].Split('@')[2] == "1111.0" ? "/" : result.Split(',')[jc1].Split('@')[2];
                            row["Num3"] = result.Split(',')[jc1].Split('@')[3] == "1111.0" ? "/" : result.Split(',')[jc1].Split('@')[3];
                        }
                        else
                        {
                            row["Num1"] = "/";
                            row["Num2"] = "/";
                            row["Num3"] = "/";
                        }
                    }


                    if (jc2 <= JCAmount - 2)//如果有6条数据 第二页第二条数据5 是不存在的
                    {
                        row["Num4"] = result.Split(',')[jc2].Split('@')[1] == "1111.0" ? "/" : result.Split(',')[jc2].Split('@')[1]; //如果数据是1111.0 则替换为/
                        row["Num5"] = result.Split(',')[jc2].Split('@')[2] == "1111.0" ? "/" : result.Split(',')[jc2].Split('@')[2];
                        row["Num6"] = result.Split(',')[jc2].Split('@')[3] == "1111.0" ? "/" : result.Split(',')[jc2].Split('@')[3];
                    }
                    else
                    {
                        row["Num4"] = "/";
                        row["Num5"] = "/";
                        row["Num6"] = "/";
                    }


                    if (jc3 <= JCAmount - 2)
                    {
                        row["Num7"] = result.Split(',')[jc3].Split('@')[1] == "1111.0" ? "/" : result.Split(',')[jc3].Split('@')[1];
                        row["Num8"] = result.Split(',')[jc3].Split('@')[2] == "1111.0" ? "/" : result.Split(',')[jc3].Split('@')[2];
                        row["Num9"] = result.Split(',')[jc3].Split('@')[3] == "1111.0" ? "/" : result.Split(',')[jc3].Split('@')[3];
                    }
                    else
                    {
                        row["Num7"] = "/";
                        row["Num8"] = "/";
                        row["Num9"] = "/";
                    }


                    if (jc4 <= JCAmount - 2)
                    {
                        row["Num10"] = result.Split(',')[jc4].Split('@')[1] == "1111.0" ? "/" : result.Split(',')[jc4].Split('@')[1];
                        row["Num11"] = result.Split(',')[jc4].Split('@')[2] == "1111.0" ? "/" : result.Split(',')[jc4].Split('@')[2];
                        row["Num12"] = result.Split(',')[jc4].Split('@')[3] == "1111.0" ? "/" : result.Split(',')[jc4].Split('@')[3];
                    }
                    else
                    {
                        row["Num10"] = "/";
                        row["Num11"] = "/";
                        row["Num12"] = "/";
                    }


                    if (jc4 <= JCAmount - 2)
                    {
                        row["Num13"] = result.Split(',')[jc5].Split('@')[1] == "1111.0" ? "/" : result.Split(',')[jc5].Split('@')[1];
                        row["Num14"] = result.Split(',')[jc5].Split('@')[2] == "1111.0" ? "/" : result.Split(',')[jc5].Split('@')[2];
                        row["Num15"] = result.Split(',')[jc5].Split('@')[3] == "1111.0" ? "/" : result.Split(',')[jc5].Split('@')[3];
                    }
                    else
                    {
                        row["Num13"] = "/"; ;
                        row["Num14"] = "/"; ;
                        row["Num15"] = "/";
                    }

                    datatable.Rows.Add(row);
                }
                #endregion

                designer.SetDataSource("Title", "表7-" + Convert.ToInt32(k + 1) + " 基坑锚索拉力监测结果表");
                designer.SetDataSource("ProName", ProName);
                designer.SetDataSource("Paddress", Address);
                designer.SetDataSource("PYQ", JCYQ);
                designer.SetDataSource("PGF", "《广州市地区建筑基坑支护技术规定》(GJB 02-98)");

                designer.SetDataSource(datatable);
                designer.Process();

                //修改Sheet的名称
                designer.Workbook.Worksheets[0].Name = "test" + k.ToString();

                #region 合并根据数据源和自定义模板，生成相应的报表Excel文件
                string tt = string.Format("Combind{0}.xls", k + DateTime.Now.ToString("yyyyMMddhhmmss"));
                string fileToSave = Server.MapPath(tt);
                designer.Save(fileToSave);
                fileList.Add(fileToSave);

                //第一次要打开
                if (k == 0)
                {
                    SourceBook1.Open(fileToSave);
                }
                else
                {
                    //第二个使用Combind函数操作
                    tempBook.Open(fileToSave);
                    SourceBook1.Combine(tempBook);
                }
                #endregion
            }
          
            string FileName = "基坑锚索拉力监测结果表" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
            string soucePath = @"D:\JKJC\" + FileName;
            SourceBook1.Save(soucePath);

            //删除临时文件
            foreach (string file in fileList)
            {
                if (File.Exists(file))
                {
                    File.Delete(file);
                }
            }
            //Process.Start(soucePath);
            DownloadHelper.DownloadFile(soucePath, FileName);
        }
        #endregion
        else
        {
            #region 如果没有数据
            WorkbookDesigner designer = new WorkbookDesigner();
            designer.Open(TempatPath);

            designer.SetDataSource("Title", "表7-1基坑支撑轴力监测结果表");
            designer.SetDataSource("ProName", ProName);
            designer.SetDataSource("Paddress", Address);
            designer.SetDataSource("PYQ", JCYQ);
            designer.SetDataSource("PGF", "《广州市地区建筑基坑支护技术规定》(GJB 02-98)");

            DataTable datatable = this.CreateTable("CD,CS,Num1,Num2,Num3,Num4,Num5,Num6,,Num7,Num8,Num9,Num10,Num11,Num12,Num13,Num14,Num15", "JK");
            designer.SetDataSource("CS1", "第1次");
            designer.SetDataSource("CS2", "第2次");
            designer.SetDataSource("CS3", "第3次");
            designer.SetDataSource("CS4", "第4次");
            designer.SetDataSource("CS5", "第4次");
            DataRow row = datatable.NewRow();

            row["CD"] = "/";
            row["CS"] = "/";
            row["Num1"] = "/";
            row["Num2"] = "/";
            row["Num3"] = "/";
            row["Num4"] = "/";
            row["Num5"] = "/";
            row["Num6"] = "/";
            row["Num7"] = "/";
            row["Num8"] = "/";
            row["Num9"] = "/";
            row["Num10"] = "/";
            row["Num11"] = "/";
            row["Num12"] = "/";
            row["Num13"] = "/";
            row["Num14"] = "/";
            row["Num15"] = "/";
            datatable.Rows.Add(row);

            designer.SetDataSource(datatable);
            designer.Process();


           
            string FileName = "基坑锚索拉力监测结果表" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
            string path = @"D:\JKJC\" + FileName;
            if (File.Exists(path))
            {
                File.Delete(path);
            }
            designer.Save(path, FileFormatType.Default);
            //Process.Start(path);
            #endregion
            DownloadHelper.DownloadFile(path, FileName);

        }
    }
    #endregion

    #region 创建Table
    public DataTable CreateTable(string nameString, string tableName)
    {
        string[] strArray = nameString.Split(new char[] { ',', ';' });
        List<string> nameList = new List<string>();
        foreach (string str in strArray)
        {
            if (!string.IsNullOrEmpty(str))
            {
                nameList.Add(str);
            }
        }
        return this.CreateTable(nameList, tableName);
    }


    public DataTable CreateTable(List<string> nameList, string tableName)
    {
        if (nameList.Count <= 0)
        {
            return null;
        }
        DataTable table = new DataTable(tableName);
        foreach (string str in nameList)
        {
            DataColumn column = new DataColumn
            {
                DataType = Type.GetType("System.String"),
                ColumnName = str
            };
            table.Columns.Add(column);
        }
        return table;
    }
    #endregion
}