/// <summary> /// 根据dt,获得列头的结构 /// </summary> private StoreColumnCollection GetTreeTop() { if (_pivotTop == null) { return(null); } var dt = _dt; var rowList = dt.Rows.Cast <DataRow>().ToList(); var result = DoGetTree(_pivotTop, rowList); if (result != null && result.Any()) { new StoreColumn { Children = result }.EachLeaf(a => { a.Children = _pivotValue.Select(b => new StoreColumn(b)).ToList(); a.Children.ForEach(b => { b.data = a.data + "_" + b.data; }); }); } else//当没有top时 { result = new StoreColumnCollection(); result.AddRange(_pivotValue.Select(b => new StoreColumn(b)).ToList()); } return(result); }
private StoreColumnCollection DoGetTree(List <string> pivot, List <DataRow> rowList, StoreColumn parent = null) { //if (pivot == null) { return null; } if (pivot == null || pivot.Count == 0) { return(new StoreColumnCollection()); } var dt = _dt; //var rowList = dt.Rows.Cast<DataRow>().ToList(); var result = new StoreColumnCollection(); List <IGrouping <string, DataRow> > group = rowList .GroupBy <DataRow, string>(dr => { var g = ""; g += (dr[pivot[0]] ?? "").ToString(); return(g); }).ToList(); //按A分组 List <string> next = null; if (pivot.Count > 1) { next = new List <string>(pivot); next.RemoveAt(0); } foreach (IGrouping <string, DataRow> ig in group) { //var item = new StoreColumn { }; result.Add(ig.Key, a => { if (parent != null) { a.data = parent.data + "_" + a.data; } if (next != null) { a.Children = DoGetTree(next, ig.ToList(), a); //a.Children.ForEach(b =>//这样有问题,因为3层+=2层,然后2层+=1层,这样的话不能连起来--benjamin todo //{ // b.data = a.data + "_" + b.data; //}); } }); } return(result); }
public void SaveToFileTest(string path) { var fileName = Path.GetFileName(path); //var dt = _dt; var dt = PFDataHelper.DataTableGroupBy(_dt, PFDataHelper.MergeList(_pivotLeft, _pivotTop).ToArray(), new PFKeyValueCollection <SummaryType>(_pivotValue.Select(a => new PFKeyValue <SummaryType> { Key = a, Value = SummaryType.Sum })) ); //var dt = PFDataHelper.DataTableGroupBy(_dt, // _pivotTop.ToArray(), // new PFKeyValueCollection<SummaryType>(_pivotTop.Select(a => new PFKeyValue<SummaryType> { Key = a, Value = SummaryType.Sum })) // ); StoreColumnCollection columns = null; var pagingResult = PFDataHelper.PagingStore(dt, new PagingParameters { }, columns, false, null); var exporter = Exporter.Instance(pagingResult ?? new PagingResult(), new ExporterOption { FileType = "xlsx",//benjamin todo Scheme = Exporter.FinancialScheme , SheetTitle = fileName //, //SheetTitle = GetWordCMonth(cmonthff) + hr + fgsname }).FileName("总表"); //这里的下载名没用到 var export = (exporter.GetExport() as XlsxExport); //var path = Path.Combine(PFDataHelper.BaseDirectory, "output", "excelPo.xlsx"); var directoryName = Path.GetDirectoryName(path); PFDataHelper.DeleteFile(path); PFDataHelper.CreateDirectory(directoryName); export.workbook.Save(path); }
public Exporter Title(StoreColumnCollection columns) { var title = new List <List <StoreColumn> >(); columns.ForEach(a => { if (a.Children.Any() && a.GetAllLeafCount(b => b.visible) < 1)//如果不是叶节点且所有子叶节点都隐藏,那么本父节点也隐藏 { a.visible = false; } }); var maxDepth = new StoreColumn { Children = columns }.GetDepth() - 1; StoreColumnCollection.StoreColumnTo2DArray(ref title, columns, ref maxDepth); _title = title; _columns = columns; return(this); }
public Workbook SaveToExcel(string path) { var scheme = new PrintPageScheme { TopMargin = 0.4, RightMargin = 0.3, BottomMargin = 0.4, LeftMargin = 0.3, DataRowHeight = 13.5, DataFontSize = 9, HeadRowHeight = 13.5, HeadForegroundColor = System.Drawing.Color.LightBlue, //默认是rgb(0,0,0),但显示为无色,原因未明 HeadFontSize = 9, TitleRowHeight = 25.5,//20, TitleFontSize = 16, TitleFontIsBold = true, TitleHorizontalAlignment = PFTextAlignmentType.Center, FootRowHeight = 36.75, FootFontSize = 11 }; var leftHeadScheme = new PrintPageScheme { TopMargin = 0.4, RightMargin = 0.3, BottomMargin = 0.4, LeftMargin = 0.3, DataRowHeight = 13.5, DataFontSize = 9, HeadRowHeight = 13.5, HeadForegroundColor = System.Drawing.Color.LightGreen, //默认是rgb(0,0,0),但显示为无色,原因未明 HeadFontSize = 9, TitleRowHeight = 25.5,//20, TitleFontSize = 16, TitleFontIsBold = true, TitleHorizontalAlignment = PFTextAlignmentType.Center, FootRowHeight = 36.75, FootFontSize = 11 }; var fileName = Path.GetFileName(path); var _sheetTitle = Path.GetFileNameWithoutExtension(path); IExport export = new XlsxExport(); export.Init(null, scheme);// Exporter.FinancialScheme); Dictionary <int, int> currentHeadRow = new Dictionary <int, int>(); Dictionary <int, int> currentHeadCell = new Dictionary <int, int>(); Dictionary <string, List <int> > fieldIndex = new Dictionary <string, List <int> >(); Dictionary <string, List <int> > fieldIndexLeft = new Dictionary <string, List <int> >(); int titleRowCount = 0; int titleCellCount = 0; if (!string.IsNullOrWhiteSpace(_sheetTitle)) { titleRowCount++; } Func <int, int> GetCurrentHeadRow = cell => currentHeadRow.ContainsKey(cell) ? currentHeadRow[cell] : titleRowCount; Func <int, int> GetCurrentHeadCell = row => currentHeadCell.ContainsKey(row) ? currentHeadCell[row] : titleCellCount; var currentRow = 0; var currentCell = 0; //提前计算leftTitle是为了可以知道top要向右移多少格 var columns = GetTreeTop(); var leftTitle = new List <List <StoreColumn> >(); var leftColumns = GetTreeLeft(); leftColumns.ForEach(a => { if (a.Children.Any() && a.GetAllLeafCount(b => b.visible) < 1)//如果不是叶节点且所有子叶节点都隐藏,那么本父节点也隐藏 { a.visible = false; } }); var maxDepth = new StoreColumn { Children = leftColumns }.GetDepth() - 1; var topBeginCell = maxDepth; StoreColumnCollection.StoreColumnTo2DArray(ref leftTitle, leftColumns, ref maxDepth); //标题--wxj20181011 StoreColumn temp = new StoreColumn { Children = columns }; int columnCount = temp.GetAllLeafCount(a => a.visible); var firstData = temp.FirstLeaf(a => true).data; if (!string.IsNullOrWhiteSpace(_sheetTitle)) { export.FillData(0, 0, firstData, _sheetTitle); //export.SetTitleStyle(0, 0, columnCount - 1+ leftTitle.Count, 0); export.SetTitleStyle(0, 0, columnCount - 1 + (HasLeft() ? leftTitle.Count : 0), 0); currentRow++; } #region 生成top 3秒(后来发现只有调试时特别慢,原因未明) ////生成多行题头 var title = new List <List <StoreColumn> >(); columns.ForEach(a => { if (a.Children.Any() && a.GetAllLeafCount(b => b.visible) < 1)//如果不是叶节点且所有子叶节点都隐藏,那么本父节点也隐藏 { a.visible = false; } }); maxDepth = new StoreColumn { Children = columns }.GetDepth() - 1; StoreColumnCollection.StoreColumnTo2DArray(ref title, columns, ref maxDepth); var _title = title; //var topBeginCell = leftTitle.Count; for (var i = 0; i < _title.Count; i++) { currentCell = topBeginCell; for (var j = 0; j < _title[i].Count; j++) { var item = _title[i][j]; if (item.visible == false) { continue; } //隐藏列不导出--wxj20181009 while (currentRow < GetCurrentHeadRow(currentCell)) { currentCell++; } export.FillData(currentCell, currentRow, "title_" + item.data, item.title ?? item.data);//e:\svn\businesssys2018\yjquery.web\areas\bonus\views\reportquery05\treegrid.cshtml里的title是null if (item.rowspan + item.colspan > 2) { export.MergeCell(currentCell, currentRow, currentCell + item.colspan - 1, currentRow + item.rowspan - 1); } if (!string.IsNullOrEmpty(item.data)) { if (!fieldIndex.ContainsKey(item.data)) { fieldIndex[item.data] = new List <int>(); } fieldIndex[item.data].Add(currentCell); } for (var k = 0; k < item.colspan; k++) { currentHeadRow[currentCell] = GetCurrentHeadRow(currentCell++) + item.rowspan; } //resultCount++; } currentRow++; } #endregion //设置题头样式 //export.SetHeadStyle(0, 0, currentCell - 1, currentRow - 1); //export.SetHeadStyle(0, titleRowCount, currentHeadRow.Count - 1, currentRow - 1);//上面那样,当后面的列不是多表头时,背景色只填到最后一个多表头为止 export.SetHeadStyle(topBeginCell, titleRowCount, currentHeadRow.Count + topBeginCell - 1, currentRow - 1); #region 生成left ////生成left var _leftTitle = leftTitle; var leftBeginRow = currentRow; var dataBeginRow = currentRow; currentCell = 0; try { for (var i = 0; i < _leftTitle.Count; i++) { currentRow = leftBeginRow; if (currentRow == 7) { var aa = "aa"; } for (var j = 0; j < _leftTitle[i].Count; j++) { var item = _leftTitle[i][j]; if (item.visible == false) { continue; } //隐藏列不导出--wxj20181009 while (currentCell < GetCurrentHeadCell(currentRow)) { currentRow++; } export.FillData(currentCell, currentRow, "leftTitle_" + item.data, item.title ?? item.data);//e:\svn\businesssys2018\yjquery.web\areas\bonus\views\reportquery05\treegrid.cshtml里的leftTitle是null if (item.rowspan + item.colspan > 2) { //export.MergeCell(currentCell, currentRow, currentCell + item.colspan - 1, currentRow + item.rowspan - 1); export.MergeCell(currentCell, currentRow, currentCell + item.rowspan - 1, currentRow + item.colspan - 1);//这里应该要改(和top比是应该反过来)--benjamin todo } if (!string.IsNullOrEmpty(item.data)) { if (!fieldIndexLeft.ContainsKey(item.data)) { fieldIndexLeft[item.data] = new List <int>(); } fieldIndexLeft[item.data].Add(currentRow); } for (var k = 0; k < item.colspan; k++) { currentHeadCell[currentRow] = GetCurrentHeadCell(currentRow++) + item.rowspan; } //resultCount++; } currentCell++; } } catch (Exception e) { throw e; //var aa = "a"; } #endregion //设置left题头样式 //export.SetHeadStyle(0, 0, currentCell - 1, currentRow - 1); //export.SetHeadStyle(0, titleRowCount, currentHeadRow.Count - 1, currentRow - 1);//上面那样,当后面的列不是多表头时,背景色只填到最后一个多表头为止 //export.SetHeadStyle(topBeginCell, titleRowCount, currentHeadRow.Count + topBeginCell - 1, currentRow - 1); (export as XlsxExport).SetHeadStyle(0, leftBeginRow, currentCell - 1, currentHeadCell.Count + leftBeginRow - 1, leftHeadScheme); #region 填充数据 var sColumn = new StoreColumn { Children = columns }; var sLeftColumn = new StoreColumn { Children = leftColumns }; var x = topBeginCell; //var dataXCount = _pivotTop.Count; var dataYCount = HasLeft() ? _leftTitle[_leftTitle.Count - 1].Count : 1;//就算没有左,也要有一行 _title[_title.Count - 1].ForEach(c => { for (int y = 0; y < dataYCount; y++) { var r = HasLeft() ? _leftTitle[_leftTitle.Count - 1][y] : new StoreColumn(); //var xGroupValues = new List<string>(); //var yGroupValues = new List<string>(); //try //{ // for (int m = 0; m < _pivotTop.Count; m++) // { // xGroupValues.Add(_title[m][x - topBeginCell].title.ToString());//这样有问题的,_title并不是每行的第二维长度都一样--benjamin todo // } // for (int m = 0; m < _pivotLeft.Count; m++) // { // yGroupValues.Add(_leftTitle[m][y].title.ToString()); // } // //xGroupValues.Add(_title[0][x - topBeginCell].title.ToString());//data??title--benjamin // //yGroupValues.Add(_leftTitle[0][y- leftBeginRow].title.ToString()); //} //catch (Exception e) //{ // throw e; // //var a = "a"; //} var xGroupValues = c.data.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries).ToList(); var yGroupValues = HasLeft() ? r.data.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries).ToList() : null; var valueField = xGroupValues[xGroupValues.Count - 1]; xGroupValues.Remove(valueField); var whereList = new List <string>(); for (int m = 0; m < _pivotTop.Count; m++) { whereList.Add(string.Format("{0}='{1}'", _pivotTop[m], xGroupValues[m])); } if (yGroupValues != null) { for (int m = 0; m < _pivotLeft.Count; m++) { try { whereList.Add(string.Format("{0}='{1}'", _pivotLeft[m], yGroupValues[m])); } catch (Exception e) { throw e; //var aa = e; } } } var valueRows = _dt.Select(string.Join(" and ", whereList)); if (valueRows != null && valueRows.Length > 0) { //export.FillData(x, y+ leftBeginRow, r.data + "_" + c.data, valueRows[0][c.data]); export.FillData(x, y + leftBeginRow, r.data + "_" + c.data, valueRows[0][valueField]); } } //var y = leftBeginRow; //_leftTitle[_leftTitle.Count - 1].ForEach(r => //{ // var xGroupValues = new List<string>(); // var yGroupValues = new List<string>(); // try // { // for (int m = 0; m < _pivotTop.Count; m++) // { // xGroupValues.Add(_title[m][x - topBeginCell].title.ToString()); // } // for (int m = 0; m < _pivotLeft.Count; m++) // { // yGroupValues.Add(_leftTitle[m][y - leftBeginRow].title.ToString()); // } // //xGroupValues.Add(_title[0][x - topBeginCell].title.ToString());//data??title--benjamin // //yGroupValues.Add(_leftTitle[0][y- leftBeginRow].title.ToString()); // }catch(Exception e) // { // throw e; // //var a = "a"; // } // //var xGroupFields = c.data.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries).ToList(); // ////var yGroupFields = r.data.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries).ToList(); // //var valueField = xGroupFields[xGroupFields.Count - 1]; // //xGroupFields.Remove(valueField); // var whereList =new List<string>(); // for(int m = 0; m < _pivotTop.Count; m++) // { // whereList.Add(string.Format("{0}='{1}'", _pivotTop[m], xGroupValues[m])); // } // for (int m = 0; m < _pivotLeft.Count; m++) // { // whereList.Add(string.Format("{0}='{1}'", _pivotLeft[m], yGroupValues[m])); // } // var valueRows = _dt.Select(string.Join(" and ", whereList)); // if (valueRows != null && valueRows.Length > 0) // { // export.FillData(x, y, r.data + "_" + c.data, valueRows[0][c.data]); // } // //var value = _dt.Select(string.Join(" and ", whereList))[0][c.data]; // ////var value =_dt.Select(string.Format("{0}='{1}'",_pivotTop[0], xGroupValues[0]))[0][c.data]; // ////var value = PFDataHelper.DataTableGroupBy( // //// _dt, // //// PFDataHelper.MergeList(xGroupFields, yGroupFields).ToArray(), // //// new PFKeyValueCollection<SummaryType> { { valueField, SummaryType.Sum } } // //// ); // //export.FillData(x, y, r.data + "_" + c.data, value); // y++; //}); x++; }); #endregion export.SetRowsStyle(topBeginCell, leftBeginRow, currentHeadRow.Count + topBeginCell - 1, HasLeft() ? (currentHeadCell.Count + leftBeginRow - 1) : leftBeginRow); var tmpEx = export as XlsxExport; if (tmpEx != null) { return(tmpEx.workbook); //tmpEx.workbook.Save(path); } return(null); }