private void SetPrintPageScheme(PrintPageScheme scheme)
{
//_printPageScheme = scheme;
//sheet.PageSetup.TopMargin = scheme.TopMargin;
//sheet.PageSetup.RightMargin = scheme.RightMargin;
//sheet.PageSetup.BottomMargin = scheme.BottomMargin;
//sheet.PageSetup.LeftMargin = scheme.LeftMargin;
////sheet.Cells.SetRowHeight()
}
private PrintPageScheme _printPageScheme;//打印页面样式方案--wxj20181011
public void Init(object data, PrintPageScheme scheme)
{
//Aspose.Cells.License l = new Aspose.Cells.License();
//l.SetLicense(Path.Combine(HttpRuntime.AppDomainAppPath, "lib/Aid/License.lic"));
document = new Document();
builder = new Aspose.Words.DocumentBuilder(document);
table = builder.StartTable();
if (scheme != null)
{
SetPrintPageScheme(scheme);
}
}
public virtual void SetHeadStyle(int x1, int y1, int x2, int y2, PrintPageScheme printPageScheme)
{
var style = GetHeadStyle(printPageScheme);
for (var y = y1; y <= y2; y++)
{
for (var x = x1; x <= x2; x++)
{
var cell = sheet.Cells[y, x];
cell.SetStyle(style);
}
if (printPageScheme != null)
{
sheet.Cells.SetRowHeight(y, printPageScheme.HeadRowHeight);
}
}
}
private Style GetHeadStyle(PrintPageScheme printPageScheme)
{
//表头样式
var headStyle = workbook.CreateStyle();
headStyle.HorizontalAlignment = TextAlignmentType.Center;//居中对齐
headStyle.VerticalAlignment = TextAlignmentType.Center;
headStyle.IsTextWrapped = true;
////表头单元格背景色
headStyle.ForegroundColor = System.Drawing.Color.LightGreen;
headStyle.Pattern = BackgroundType.Solid;
////表头单元格边框
headStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
headStyle.Borders[BorderType.TopBorder].Color = Color.Black;
headStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
headStyle.Borders[BorderType.RightBorder].Color = Color.Black;
headStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
headStyle.Borders[BorderType.BottomBorder].Color = Color.Black;
headStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
headStyle.Borders[BorderType.LeftBorder].Color = Color.Black;
if (printPageScheme != null)
{
headStyle.ForegroundColor = printPageScheme.HeadForegroundColor;
headStyle.Font.Size = printPageScheme.HeadFontSize;// 10;
}
//headStyle.Borders.SetStyle(CellBorderType.Thin);//统一设置,会连对角线都加上的,暂不知道如何解决
//headStyle.Borders.SetColor(Color.Black);
//headStyle.Font.Size = 10;
//headStyle.Font.IsBold = false;
//////表头字体设置
////var font = workbook.CreateFont();
////font.FontHeightInPoints = 12;//字号
////font.Boldweight = 600;//加粗
//////font.Color = HSSFColor.WHITE.index;//颜色
////headStyle.SetFont(font);
return(headStyle);
}
private PrintPageScheme _printPageScheme;//打印页面样式方案--wxj20181011
/// <summary>
/// 报错:(初步怀疑是破解盗版时产生的问题)
/// time:[2019/5/22 15:13:47]
/// System.NullReferenceException: Object reference not set to an instance of an object.
/// at ح.ⴗ.(Stream Ԡ)
/// at ح.ⴗ.(String ⴧ, Assembly )
/// at Perfect.XlsExport.Init(Object data, PrintPageScheme scheme)
/// at Perfect.Exporter.Export(IExport export)
/// at Perfect.Exporter.Export(String type)
/// at Perfect.Exporter.Instance(PagingResult pagingResult, ExporterOption opts)
/// at YJQuery.Web.Areas.ProjOut.Controllers.ProjectRequirementController.GetExporter(String cmonthff, String fgsno, String fgsname, String hr, String fbatch, String SfName)
/// </summary>
/// <param name="data"></param>
/// <param name="scheme"></param>
public void Init(object data, PrintPageScheme scheme)
{
//try
//{
// XlsExport.InitializeAsposeCells();
// //Aspose.Cells.License l = new Aspose.Cells.License();
// ////l.SetLicense(Path.Combine(HttpRuntime.AppDomainAppPath, "lib/Aid/License.lic"));
// //l.SetLicense(Path.Combine(PFDataHelper.BaseDirectory, "lib/Aid/License.lic"));
//}
//catch (Exception e)
//{
// PFDataHelper.WriteError(e);
//}
workbook = new Workbook(FileFormatType.Xlsx);
sheet = workbook.Worksheets[0];
if (scheme != null)
{
SetPrintPageScheme(scheme);
}
}
public static Exporter Instance(PagingResult pagingResult, ExporterOption opts)
{
var export = new Exporter();
//export._controller = controller;
var context = HttpContext.Current;
string fileType = opts.FileType; PrintPageScheme scheme = opts.Scheme; string sheetTitle = opts.SheetTitle; string sheetFoot = opts.SheetFoot;
export._printPageScheme = scheme;
export._sheetTitle = sheetTitle;
export._sheetFoot = sheetFoot;
//if (context.Request.Form["titles"] != null)
// export.Title(JsonConvert.DeserializeObject<List<List<StoreColumn>>>(context.Request.Form["titles"]));//原本的title是从前端传过来,现改为统一用_data里的columns
//if (context.Request.Form["dataGetter"] != null)
//{
//export.Data(context.Request.Form["dataGetter"]);
export.Data(pagingResult);
var columns = (export._data as PagingResult).columns as StoreColumnCollection;//这里可以扩展为多表头
////export.Title(new List<List<StoreColumn>> { columns });//树型必需转2维数组
//var title = new List<List<StoreColumn>>();
//var maxDepth = new StoreColumn { Children = columns }.GetDepth() - 1;
//StoreColumnCollection.StoreColumnTo2DArray(ref title, columns, ref maxDepth);
//export.Title(title);
export.Title(columns);
//}
export.Export(fileType);
if (context != null && context.Request.Form["compressType"] != null)
{
export.Compress(context.Request.Form["compressType"]);
}
return(export);
}
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);
}
