/// <summary>
/// 模板格式下载，用<paramref name="items"/>中每个key, value替换模板中的标签（标签名称为key）
/// </summary>
/// <param name="name">用户下载框中显示的保存文件名，例如：SN_08082600012.xls</param>
/// <param name="prefix">内部生成的下载文件前缀，例如：SN</param>
/// <param name="template">模板文件的物理路径（不是IIS虚拟路径）</param>
/// <param name="items">标签的键、值对</param>
/// <returns>返回下载文件的链接地址（使用download.aspx）</returns>
public static string DownloadXls(string name, string prefix, string template, IDictionary <string, string> items)
{
ExcelApp excelApp = null;
ExcelWorkbook workbook = null;
ExcelWorksheet sheet = null;
string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
string filePath = DownloadFolder + fileName;
try
{
excelApp = new ExcelApp();
workbook = excelApp.Open(template, 0);
workbook.SaveAs(filePath);
sheet = workbook.Worksheets(1);
ExcelRange range = sheet.Cells();
foreach (KeyValuePair <string, string> kv in items)
{
range.Replace(kv.Key, kv.Value, false);
}
workbook.Save();
}
catch (Exception er)
{
throw er;
}
finally
{
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
}
if (workbook != null)
{
workbook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
}
if (excelApp != null)
{
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
}
}
return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath));
}
/// <summary>
/// 简单格式下载，只下载<paramref name="ds"/>的数据行
/// </summary>
/// <param name="name">用户下载框中显示的保存文件名，例如：SN_08082600012.xls</param>
/// <param name="prefix">内部生成的下载文件前缀，例如：SN</param>
/// <param name="format">数据列的格式描述信息</param>
/// <param name="ds"></param>
/// <returns>返回下载文件的链接地址（使用download.aspx）</returns>
public static string DownloadXls(string name, string prefix, IList <DownloadFormat> format, DataSet ds)
{
string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
string filePath = DownloadFolder + fileName;
ExcelApp excelapp = null;
ExcelWorkbook excelBook = null;
ExcelWorksheet excelSheet = null;
try
{
excelapp = new ExcelApp();
excelapp.DisplayAlerts = false;
excelBook = excelapp.NewWorkBook();
excelSheet = excelBook.Worksheets(1);
int rowIndex = 1;
for (int i = 0; i < format.Count; i++)
{
excelSheet.Cells(rowIndex, i + 1).Value = format[i].Title;
}
ExcelRange rg = excelSheet.Range(rowIndex, rowIndex, 1, format.Count);
rg.SelectRange();
rg.Font.Bold = true;
rg.HorizontalAlignment = 3;
rg.Interior.SetColor(221, 221, 221);
rowIndex++;
#region 写文件
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < format.Count; i++)
{
DownloadFormat ft = format[i];
if (ft.ColumnIndex == null)
{
continue;
}
for (int j = 0; j < ft.ColumnIndex.Length; j++)
{
SetCellValue(excelSheet.Cells(rowIndex, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
}
}
rowIndex++;
}
#endregion
ExcelRange excelRange = excelSheet.Cells();
excelRange.SelectRange();
excelRange.AutoFit();
excelRange.Font.Size = 10;
excelBook.SaveAs(filePath);
}
catch (Exception er)
{
throw er;
}
finally
{
if (excelSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject);
}
if (excelBook != null)
{
excelBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject);
}
if (excelapp != null)
{
excelapp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject);
}
}
return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath));
}
/// <summary>
/// 模板格式+简单格式下载，先用<paramref name="items"/>中每个key, value替换模板中的标签（标签名称为key），再下载<paramref name="ds"/>的数据行
/// </summary>
/// <param name="name">用户下载框中显示的保存文件名，例如：SN_08082600012.xls</param>
/// <param name="prefix">内部生成的下载文件前缀，例如：SN</param>
/// <param name="template">模板文件的物理路径（不是IIS虚拟路径）</param>
/// <param name="items">标签的键、值对</param>
/// <param name="rowIndex">数据行的开始位置（1开始的索引，即Excel中的行号）</param>
/// <param name="ds"></param>
/// <returns>返回下载文件的链接地址（使用download.aspx）</returns>
public static string DownloadXls(string name, string prefix, string template, IDictionary <string, string> items, int rowIndex, IList <DownloadFormat> format, DataSet ds)
{
ExcelApp excelApp = null;
ExcelWorkbook workbook = null;
ExcelWorksheet sheet = null;
string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
string filePath = DownloadFolder + fileName;
try
{
excelApp = new ExcelApp();
workbook = excelApp.Open(template, 0);
workbook.SaveAs(filePath);
sheet = workbook.Worksheets(1);
//标签替换
ExcelRange range = sheet.Cells();
if (items != null)
{
foreach (KeyValuePair <string, string> kv in items)
{
range.Replace(kv.Key, kv.Value, false);
}
}
//数据行
int index = rowIndex;
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < format.Count; i++)
{
DownloadFormat ft = format[i];
if (ft.ColumnIndex == null)
{
continue;
}
for (int j = 0; j < ft.ColumnIndex.Length; j++)
{
SetCellValue(sheet.Cells(index, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
}
}
index++;
}
workbook.Save();
}
catch (Exception er)
{
throw er;
}
finally
{
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
}
if (workbook != null)
{
workbook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
}
if (excelApp != null)
{
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
}
}
return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath));
}
public async Task <Stream> GetExportAsync(string userId)
{
var export = await this.baseHandler.GetJsonAsync(userId);
this.lang = export?.Client?.Language;
foreach (var budget in export.Budgets)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add($"Budget: {budget.Name}");
var(rI, cI) = this.DoBudget(worksheet, 2, 2, Translate("Income"), budget.Positive);
var(rE, cE) = this.DoBudget(worksheet, 2, cI + 2, Translate("Expenses"), budget.Negative);
worksheet.Cells(1, 1, 0, cE)
.Merge()
.Value(budget.Name)
.FontColor(0, 140, 180)
.FontSize(40)
.Left(3)
.Height(60);
worksheet.Cells(1, 1, Math.Max(rI, rE), cE)
.Style(x => x.Font.Name = "URW Gothic");
}
if (export.Revenue != null)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(Translate("Revenue"));
var(rI, cI) = this.DoRevenue(worksheet, 2, 2, Translate("PlannedRevenue"), export.Revenue.Positive);
var(rE, cE) = this.DoRevenue(worksheet, 2, cI + 2, Translate("PlannedExpenses"), export.Revenue.Negative);
worksheet.Cells(1, 1, 0, cE)
.Merge()
.Value(Translate("Revenue"))
.FontColor(0, 140, 180)
.FontSize(40)
.Left(3)
.Height(60);
worksheet.Cells(1, 1, Math.Max(rI, rE), cE)
.Style(x => x.Font.Name = "URW Gothic");
}
if (export.Assets != null)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(Translate("Assets"));
var(rI, cI) = this.DoAssets(worksheet, 2, 2, Translate("Assets"), export.Assets.Positive);
var(rE, cE) = this.DoAssets(worksheet, 2, cI + 2, Translate("Debts"), export.Assets.Negative);
worksheet.Cells(1, 1, 0, cE)
.Merge()
.Value(Translate("Assets"))
.FontColor(0, 140, 180)
.FontSize(40)
.Left(3)
.Height(60);
worksheet.Cells(1, 1, Math.Max(rI, rE), cE)
.Style(x => x.Font.Name = "URW Gothic");
}
return(new MemoryStream(package.GetAsByteArray()));
}
private (int row, int col) DoAssets(ExcelWorksheet worksheet, int row, int col, string name, IEnumerable <Unit <NamedValue> > values)
{
var nameElement = worksheet.Cells(row, col, 0, 3)
.Merge()
.Value(name);
row++;
worksheet.Cells(row, col, 0, 3)
.Bold().FontSize(11).FontColor(255, 255, 255)
.BackgroundColor(0, 140, 180)
.Height(30);
worksheet.Cells(row, col)
.Column(x =>
{
x.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
x.Style.Indent = 1;
})
.Width(20);
worksheet.Cells(row, col + 1, 0, 1)
.Value(Translate("Name"))
.Column(x =>
{
x.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
x.Style.Indent = 1;
})
.Merge()
.Width(30);
worksheet.Cells(row, col + 3)
.Value(Translate("Amount"))
.Column(x =>
{
x.Style.Numberformat.Format = "#,###.00";
x.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
x.Style.Indent = 1;
})
.Width(15);
worksheet.Column(col + 4).Hidden = true;
row++;
nameElement.Center().FontSize(20).Height(40);
foreach (var income in values)
{
var start = row;
var i = 0;
var elements = income.Elements.ToList();
elements.AddRange(new[] { new NamedValue {
}, new NamedValue {
} });
foreach (var e in elements)
{
i++;
worksheet.Cells(row, col + 1, 0, 3)
.Height(30)
.If(i % 2 == 1)
.Then(x => x.BackgroundColor(239, 239, 239))
.Else(x => x.BackgroundColor(255, 255, 255));
worksheet.Cells(row, col + 1, 0, 1).Merge().Value(e.Name);
if (e.Value != 0)
{
worksheet.Cells(row, col + 3).Value(e.Value);
}
worksheet.Cells(row, col + 4).Formula($"{(col + 3).GetExcelColumnName()}{row}");
row++;
}
worksheet.Cells(start, col)
.Value(income.Name)
.BackgroundColor(231, 246, 251)
.Height(30);
if (elements.Count > 1)
{
worksheet.Cells(start + 1, col, elements.Count - 2, 0)
.Merge()
.BackgroundColor(231, 246, 251);
}
worksheet.Cells(row, col, 0, 2)
.Merge()
.Value(Translate("Subtotal"))
.BorderTop(ExcelBorderStyle.Thin, Color.FromArgb(0, 140, 180))
.FontColor(0, 140, 180)
.Height(30);
var subT = (col + 4).GetExcelColumnName();
worksheet.Cells(row, col + 3)
.Formula($"SUM({subT}{start}:{subT}{start + elements.Count})")
.BorderTop(ExcelBorderStyle.Thin, Color.FromArgb(0, 140, 180))
.FontColor(0, 140, 180);
row++;
}
worksheet.Cells(row, col, 0, 2)
.Merge()
.Value(Translate("Total"))
.Bold().FontColor(0, 140, 180)
.BorderTop(ExcelBorderStyle.Medium, Color.FromArgb(0, 140, 180))
.Height(30);
var colname = (col + 4).GetExcelColumnName();
worksheet.Cells(row, col + 3)
.Formula($"SUM({colname}:{colname})")
.Bold().FontColor(0, 140, 180)
.BorderTop(ExcelBorderStyle.Medium, Color.FromArgb(0, 140, 180));
row++;
return(row, col + 4);
}