/// <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();
}
}
