C#导出表格多个sheet

转载

mb5fe559b5073e8 2021-09-27 17:23:00

文章标签 sql 竖向数据指定位置重命名 文章分类 C/C++ 后端开发

//竖向位移
            if (factortypeid == 124)
            {
            string sql = string.Format(@"
            SELECT B.SENSOR_LOCATION_DESCRIPTION,Convert(decimal(18,2),A.SURFACE_DISPLACEMENT_X_VALUE)AS SURFACE_DISPLACEMENT_X_VALUE,A.ACQUISITION_DATETIME FROM T_THEMES_DEFORMATION_SURFACE_DISPLACEMENT AS A LEFT JOIN T_DIM_SENSOR AS B ON A.SENSOR_ID = B.SENSOR_ID
            WHERE B.SENSOR_ID IN ({0}) 
            AND  A.ACQUISITION_DATETIME >  '{1}' AND A.ACQUISITION_DATETIME < '{2}'  AND A.AGG_TYPE  {3}  ORDER BY A.ACQUISITION_DATETIME desc
           ", sensors, startdate, enddate, aggTypeFilter);
                DataTable tmpDataTable = SqlHelper.ExecuteDataSetText(sql, null).Tables[0];
                if (tmpDataTable.Rows.Count <= 0)
                {
                    msg = "数据不存在，请重新选择条件";
                    return msg;
                }
                // 对数据进行分组每个分组一个sheet
                var query = from p in tmpDataTable.AsEnumerable()
                           group p by new { SENSOR_LOCATION_DESCRIPTION = p.Field<string>("SENSOR_LOCATION_DESCRIPTION") } into m
                           select new
                           {
                               SENSOR_LOCATION_DESCRIPTION = m.Key.SENSOR_LOCATION_DESCRIPTION
                           };

                sql = string.Format(@"SELECT TOP 1 STRUCTURE_NAME_CN,SAFETY_FACTOR_TYPE_NAME FROM T_DIM_SENSOR AS S inner JOIN T_DIM_STRUCTURE AS T ON S.STRUCT_ID = T.ID inner  JOIN T_DIM_SAFETY_FACTOR_TYPE AS G
                       ON G.SAFETY_FACTOR_TYPE_ID = S.SAFETY_FACTOR_TYPE_ID WHERE S.SENSOR_ID IN ({0})", sensors);
                DataTable SheetName = SqlHelper.ExecuteDataSetText(sql, null).Tables[0];
                var STRUCTURE_NAME = SheetName.Rows[0]["STRUCTURE_NAME_CN"].ToString();
                var FACTOR_NAME = SheetName.Rows[0]["SAFETY_FACTOR_TYPE_NAME"].ToString();

                //模板路径
                var path = HttpContext.Current.Server.MapPath("/upload/ImportTemplate/竖向位移导出数据.xlsx");
                //指定文档
                FileInfo newFile = new FileInfo(path);
                //开启
                using (ExcelPackage pck = new ExcelPackage(newFile))
                {
                    //设定ExcelWorkBook
                    ExcelWorkbook workBook = pck.Workbook;
                    ExcelWorksheet currentWorksheet = pck.Workbook.Worksheets[1];//只有一个分组就是它一个sheet
                    int a =1;
                    foreach (var item in query.AsEnumerable())
                    {
                        //currentWorksheet = pck.Workbook.Worksheets[a]; //这个是因为模板里面是默认3个sheet 分组超过3个就会报错，改成新建sheet
                        if (a > 1) //多个分组，循环几遍就新建几个sheet
                        { 
                        currentWorksheet = pck.Workbook.Worksheets.Add("currentWorksheet");
                        }
                        if (workBook != null)
                        {
                            if (workBook.Worksheets.Count > 0)
                            {
                                currentWorksheet.Cells[1, 1].Value = Convert.ToString("设备位置");
                                currentWorksheet.Cells[1, 2].Value = Convert.ToString("竖向位移(mm)");
                                currentWorksheet.Cells[1, 3].Value = Convert.ToString("采集时间");
                                int i = 2;
                                foreach (DataRow datalist in tmpDataTable.Rows)
                                {
                                    if (datalist["SENSOR_LOCATION_DESCRIPTION"].ToString() == item.SENSOR_LOCATION_DESCRIPTION)
                                    {
                                        currentWorksheet.Cells[i, 1].Value = Convert.ToString(datalist["SENSOR_LOCATION_DESCRIPTION"].ToString());//盘点ID
                                        currentWorksheet.Cells[i, 2].Value = Convert.ToString(datalist["SURFACE_DISPLACEMENT_X_VALUE"].ToString());//盘点明细ID
                                        currentWorksheet.Cells[i, 3].Value = Convert.ToString(datalist["ACQUISITION_DATETIME"].ToString());
                                        i++;
                                    }
                                }
                            }
                        }
                        currentWorksheet.Name = item.SENSOR_LOCATION_DESCRIPTION; //对当前sheet 重命名sheet名称
                        a++;
                    }
                    //currentWorksheet.Name = FACTOR_NAME;
                    //下载到指定位置路径文件.xlsx
                    var folder = HttpContext.Current.Server.MapPath("/Upload/download/");
                    Directory.CreateDirectory(folder);
                    url = "/upload/download/" + STRUCTURE_NAME + "-" + FACTOR_NAME + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                    string pathInfoList = HttpContext.Current.Server.MapPath(url);
                    pck.SaveAs(new FileInfo(pathInfoList));
                }

            }
            msg = ConfigurationManager.AppSettings["Url"] + url;

            return msg;