/// <summary> /// 业务逻辑 /// </summary> /// <param name="excelId"></param> /// <param name="accountItemIds"></param> /// <param name="financialDataItemIds"></param> /// <param name="qiJianTypeId"></param> /// <returns></returns> public Stream Process(int excelId, string accountItemIds, string financialDataItemIds, int qiJianTypeId, int onlyStatisticChildren, int xiangMuTreeTypeId, int statisticAccountChildren) { var result = new List <List <string> >(); var columnData = _financialDataExtendBLL.GetGridColumns(excelId, financialDataItemIds, onlyStatisticChildren, xiangMuTreeTypeId); var gridData = _financialDataExtendBLL.GetGridData(excelId, accountItemIds, financialDataItemIds, qiJianTypeId, onlyStatisticChildren, xiangMuTreeTypeId, statisticAccountChildren); var columnList = new List <string>(); foreach (var gridColumn in columnData.GridColumns) { columnList.Add(gridColumn.text); } result.Add(columnList); foreach (var dataDic in gridData.rows) { var dataList = new List <string>(); foreach (var gridColumn in columnData.GridColumns) { dataList.Add(dataDic[gridColumn.dataIndex]); } result.Add(dataList); } var excelBuilder = ExcelFactory.CreateBuilder(); var sheet = excelBuilder.InsertSheet("统计数据"); sheet.InsertSheetContent(ExcelExportRequest.GetInstance(result)); var stream = new MemoryStream(); excelBuilder.Save(stream, SaveFormat.Xlsx); stream.Position = 0; return(stream); }
/// <summary> /// 业务逻辑 /// </summary> /// <param name="excelId"></param> /// <returns></returns> public Stream Process(string ids) { var excelDatas = new List <List <string> >(); excelDatas.Add(GetExcelTitel()); var idsArr = ids.Split(','); var companyList = new List <Company>(); var areas = _companyBll.GetAllArea(); foreach (var id in idsArr) { if (string.IsNullOrEmpty(id)) { continue; } var idArr = id.Split('_'); var findId = Convert.ToInt32(idArr[1]); if (idArr[0] == "1") { var companys = _companyBll.GetCompanyByAreaId(findId); if (companys != null) { companyList.AddRange(companys); } } else { var company = _companyBll.GetCompanyByKey(findId); if (company != null) { companyList.Add(company); } } } foreach (var company in companyList) { var area = areas.FirstOrDefault(x => x.AreaId == company.AreaId); var companyRecords = _companyBll.GetCompanyconnectrecordByCompanyId(company.CompanyId); if (companyRecords != null && companyRecords.Count > 0) { foreach (var record in companyRecords) { excelDatas.Add(GetOneCellData(area, company, record)); } } else { excelDatas.Add(GetOneCellData(area, company, new CompanyConnectRecord())); } } var excelBuilder = ExcelFactory.CreateBuilder(); var sheet = excelBuilder.InsertSheet("sheet1"); sheet.InsertSheetContent(ExcelExportRequest.GetInstance(excelDatas)); var stream = new MemoryStream(); excelBuilder.Save(stream, SaveFormat.Xlsx); stream.Position = 0; return(stream); }
public string ExportGridToExcel(ExcelExportRequest <IncidentTypeSearchCriteria, DisplayWithName> excelExportRequest) { var path = $"{HttpRuntime.AppDomainAppPath}\\ExcelExport.xlsx"; var data = this.SearchAndGetPage(excelExportRequest.SearchCriteria).Data; data.ToExcel(excelExportRequest.Display, path); return("ExcelExport.xlsx"); }
/// <summary> /// 将列名和数据合并到一个数组 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="titles"></param> /// <param name="models"></param> /// <returns></returns> public static ExcelExportRequest ConvertToRowsData <T>(List <List <string> > titles, List <T> models) { var rows = new List <List <string> >(); var colWidths = new List <double>(); var type = typeof(T); var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance); if (models == null || models.Count <= 0 || titles == null || titles.Any(d => d.Count <= 0 || d.Count != properties.Length)) { return(null); } rows.AddRange(titles); foreach (var model in models) { var row = new List <string>(); var sortDic = new SortedDictionary <int, string>(); foreach (PropertyInfo t in properties) { var attributes = t.GetCustomAttributes(typeof(ExcelOrderAttribute), false); if (attributes.Length > 0) { var match = (attributes[0] as ExcelOrderAttribute); if (match == null) { continue; } var val = t.GetValue(model, null); string text; if (val == null || (match.IsReplaceHisotry && val.ToString().Equals(match.HistoryVal.ToString()))) { text = ""; } else { text = val + match.Unit; } sortDic.Add(match.Order, text); colWidths.Add(match.ColWidth); } } foreach (var item in sortDic) { row.Add(item.Value); } rows.Add(row); } return(ExcelExportRequest.GetInstance(rows, 0, titles.Count, null, colWidths)); }
/// <summary> /// 填充内容 /// </summary> /// <param name="sheet"></param> /// <param name="request">待插入数据</param> public static void InsertSheetContent(this SheetBuilder sheet, ExcelExportRequest request) { var titleStyle = GetDefaultStyle(true); var rowStyle = GetDefaultStyle(); sheet.SetSheetMerges(request.Merges); int rowIndex = request.RowIndex; var colWidths = new List <double>(); foreach (var row in request.RowsData) { List <double> textRowNums = new List <double>(); for (int col = 0; col < row.Count; col++) { sheet.Worksheet.Cells[rowIndex, col].SetStyle(rowIndex < request.TitleRowCount ? titleStyle : rowStyle); sheet.WriteText(rowIndex, col, row[col] == null ? "" : row[col].Replace("^^", "\n")); int length = Encoding.UTF8.GetBytes(row[col] == null ? "" : row[col]).Length; double width = length * 2 < 8 ? 8 : length * 2; if (rowIndex == (request.TitleRowCount - 1)) { double colSetWidth = request.ColumnsWidth != null && request.ColumnsWidth.Count > col ? request.ColumnsWidth[col] : 0; double colWidth = colSetWidth > 0 ? colSetWidth : width; sheet.SetColumnWidth(col, colWidth); colWidths.Add(colWidth); } double textRowNum = 2; if (rowIndex > (request.TitleRowCount - 1) && colWidths.Count > 0) { textRowNum = Math.Ceiling(width / colWidths[col]); } textRowNums.Add(textRowNum); } var rowHeight = rowStyle.Font.Size * (textRowNums.Max() <= 2 ? 2 : textRowNums.Max()); sheet.SetRowHeight(rowIndex, rowHeight > 400 ? 400 : rowHeight); rowIndex++; } if (request.IsAutoRowFit) { sheet.Worksheet.AutoFitRows(); } }