Access导出报表，列数动态

Access导出报表，列数动态

        public void ExportAccessoryRepairReport(string sTime, string eTime, string provinceId, string provinceName)
        {
            try
            {
                DateTime startTime = Convert.ToDateTime(sTime);
                DateTime endTime = Convert.ToDateTime(eTime);
                var asList = _orderManage.QueryAccessoryStatistics(startTime, endTime, provinceId);
                var cityList = _orderManage.GetCitiesByProId(provinceId).OrderBy(p => p.CityCode).ToList();
                var cityNameList = cityList.Select(p => p.CityName).ToArray();
                var accessoryIdList = asList.Select(p => p.AccessoryId).Distinct();

                string filePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Data\\AccessoryTemplate.xlsx";
                WorkbookDesigner designer = new WorkbookDesigner();
                designer.Workbook = new Workbook(filePath);
                designer.SetDataSource("cityNameList", cityNameList);
                designer.SetDataSource("ProvinceName", $"{provinceName}配件报修报表");
                designer.SetDataSource("DateTimePart", $"{sTime}至{eTime}");
                designer.Process();
                designer.Workbook.Worksheets[0].Cells[4, 0].PutValue("&=&={-4}");
                designer.Workbook.Worksheets[0].Cells[4, 1].PutValue("&=[Data].AccessoryName");
                designer.Workbook.Worksheets[0].Cells[4, 2].PutValue("&=[Data].AccessoryModel");
                int repairIndex = 3;
                int replaceIndex = 5 + cityList.Count();
                int tempRepairIndex = repairIndex;
                int tempReplaceIndex = replaceIndex;
                string colName;
                for (int i = 0; i < cityList.Count(); i++)
                {
                    designer.Workbook.Worksheets[0].Cells[4, repairIndex].PutValue($"&=[Data].Repair{cityList[i].CityCode}");
                    designer.Workbook.Worksheets[0].Cells[4, replaceIndex].PutValue($"&=[Data].Replace{cityList[i].CityCode}");
                    colName = designer.Workbook.Worksheets[0].Cells[0, repairIndex].Name.Substring(0, designer.Workbook.Worksheets[0].Cells[0, repairIndex].Name.Length - 1);
                    designer.Workbook.Worksheets[0].Cells[5, repairIndex].PutValue($"&=&=sum({colName}5:{colName}{{-1}})");
                    colName = designer.Workbook.Worksheets[0].Cells[0, replaceIndex].Name.Substring(0, designer.Workbook.Worksheets[0].Cells[0, replaceIndex].Name.Length - 1);
                    designer.Workbook.Worksheets[0].Cells[5, replaceIndex].PutValue($"&=&=sum({colName}5:{colName}{{-1}})");
                    repairIndex++;
                    replaceIndex++;
                }
                colName = designer.Workbook.Worksheets[0].Cells[0, repairIndex].Name.Substring(0, designer.Workbook.Worksheets[0].Cells[0, repairIndex].Name.Length - 1);
                designer.Workbook.Worksheets[0].Cells[5, repairIndex].PutValue($"&=&=sum({colName}5:{colName}{{-1}})");
                colName = designer.Workbook.Worksheets[0].Cells[0, replaceIndex].Name.Substring(0, designer.Workbook.Worksheets[0].Cells[0, replaceIndex].Name.Length - 1);
                designer.Workbook.Worksheets[0].Cells[5, replaceIndex].PutValue($"&=&=sum({colName}5:{colName}{{-1}})");

                string startColName = designer.Workbook.Worksheets[0].Cells[5, tempRepairIndex].Name.Substring(0, designer.Workbook.Worksheets[0].Cells[0, tempRepairIndex].Name.Length - 1);
                string endColName = designer.Workbook.Worksheets[0].Cells[5, repairIndex - 1].Name.Substring(0, designer.Workbook.Worksheets[0].Cells[0, repairIndex - 1].Name.Length - 1);
                designer.Workbook.Worksheets[0].Cells[4, repairIndex].PutValue($"&=&=sum({startColName}{{r}}:{endColName}{{r}})");
                startColName = designer.Workbook.Worksheets[0].Cells[5, tempReplaceIndex].Name.Substring(0, designer.Workbook.Worksheets[0].Cells[0, tempReplaceIndex].Name.Length - 1);
                endColName = designer.Workbook.Worksheets[0].Cells[5, replaceIndex - 1].Name.Substring(0, designer.Workbook.Worksheets[0].Cells[0, replaceIndex - 1].Name.Length - 1);
                designer.Workbook.Worksheets[0].Cells[4, replaceIndex].PutValue($"&=&=sum({startColName}{{r}}:{endColName}{{r}})");

                designer.Workbook.SaveOptions.SaveFormat = SaveFormat.Xlsx;
                string tempFile = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + string.Format("astemp{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff"));
                designer.Workbook.Save(tempFile);

                designer = new WorkbookDesigner();
                designer.Workbook = new Workbook(tempFile);
                DataTable dt = new DataTable();
                dt.Columns.Add("AccessoryName");
                dt.Columns.Add("AccessoryModel");
                foreach (var city in cityList)
                {
                    dt.Columns.Add("Repair" + city.CityCode, typeof(int));
                    dt.Columns.Add("Replace" + city.CityCode, typeof(int));
                    dt.Columns["Repair" + city.CityCode].DefaultValue = 0;
                    dt.Columns["Replace" + city.CityCode].DefaultValue = 0;
                }
                foreach (var id in accessoryIdList)
                {
                    DataRow row = dt.NewRow();
                    var acc = asList.Where(p => p.AccessoryId == id).ToList();
                    row["AccessoryName"] = acc[0].AccessoryName;
                    row["AccessoryModel"] = acc[0].AccessoryModel;

                    foreach (var item in acc)
                    {
                        row[$"Repair{item.CityCode}"] = item.AccessoryRepairNum;
                        row[$"Replace{item.CityCode}"] = item.AccessoryReplaceNum;
                    }
                    dt.Rows.Add(row);
                }
                dt.TableName = "Data";
                designer.SetDataSource(dt);
                designer.Process();
                designer.Workbook.SaveOptions.SaveFormat = SaveFormat.Xlsx;
                designer.Workbook.Save(System.Web.HttpContext.Current.Response, string.Format("配件报修报表{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff")),
                    ContentDisposition.Attachment, designer.Workbook.SaveOptions);
                designer = null;
                System.IO.File.Delete(tempFile);
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('系统错误')</script>");
                WriteLog.CreateLog(ex);
            }
        }

 

Excel模板：