private void btnExport_Click(object sender, EventArgs e)
{
var saveFileDlg = new SaveFileDialog {Filter = Resources.SaveFileFilter};
if (DialogResult.OK.Equals(saveFileDlg.ShowDialog()))
{
var workbookParameterContainer = new WorkbookParameterContainer();
workbookParameterContainer.Load(@"Template\Template.xml");
SheetParameterContainer sheetParameterContainer = workbookParameterContainer["重复单元格式化器"];
ExportHelper.ExportToLocal(@"Template\Template.xls", saveFileDlg.FileName,
new SheetFormatter("重复单元格式化器",
new RepeaterFormatter<StudentInfo>(sheetParameterContainer["rptStudentInfo_Start"],
sheetParameterContainer["rptStudentInfo_End"], StudentLogic.GetList(),
new CellFormatter<StudentInfo>(sheetParameterContainer["Name"], t => t.Name),
new CellFormatter<StudentInfo>(sheetParameterContainer["Gender"], t => t.Gender ? "男" : "女"),
new CellFormatter<StudentInfo>(sheetParameterContainer["Class"], t => t.Class),
new CellFormatter<StudentInfo>(sheetParameterContainer["RecordNo"], t => t.RecordNo),
new CellFormatter<StudentInfo>(sheetParameterContainer["Phone"], t => t.Phone),
new CellFormatter<StudentInfo>(sheetParameterContainer["Email"], t => t.Email)
)
)
);
}
}
public static WorkbookParameterContainer Parse(string templatePath)
{
var workbookParameterContainer = new WorkbookParameterContainer();
IWorkbook workbook = NPOIHelper.LoadWorkbook(templatePath);
foreach (ISheet sheet in workbook)
{
workbookParameterContainer[sheet.SheetName] = new SheetParameterContainer
{
SheetName = sheet.SheetName
};
foreach (IRow row in sheet)
{
foreach (ICell cell in row.Cells)
{
if (cell.CellType.Equals(CellType.String))
{
MatchCollection matches = new Regex(@"(?<=\$\[)([\w]*)(?=\])").Matches(cell.StringCellValue);
foreach (Match match in matches)
{
workbookParameterContainer[sheet.SheetName][match.Value] = new Parameter
{
Name = match.Value,
RowIndex = cell.RowIndex,
ColumnIndex = cell.ColumnIndex
};
}
}
}
}
}
return workbookParameterContainer;
}
private void btnExport_Click(object sender, EventArgs e)
{
var saveFileDlg = new SaveFileDialog {Filter = Resources.SaveFileFilter};
if (DialogResult.OK.Equals(saveFileDlg.ShowDialog()))
{
var workbookParameterContainer = new WorkbookParameterContainer();
workbookParameterContainer.Load(@"Template\Template.xml");
var sheetParameterContainer = workbookParameterContainer["局部格式化器"];
ExportHelper.ExportToLocal(@"Template\Template.xls", saveFileDlg.FileName,
new SheetFormatter("局部格式化器",
new PartFormatter(sheetParameterContainer["UserName"], "Jensen"),
new PartFormatter(sheetParameterContainer["GroupNo"], "116476496")
)
);
}
}
private void btnExport_Click(object sender, EventArgs e)
{
var saveFileDlg = new SaveFileDialog {Filter = Resources.SaveFileFilter};
if (DialogResult.OK.Equals(saveFileDlg.ShowDialog()))
{
var workbookParameterContainer = new WorkbookParameterContainer();
workbookParameterContainer.Load(@"Template\Template.xml");
var sheetParameterContainer = workbookParameterContainer["单元格格式化器"];
ExportHelper.ExportToLocal(@"Template\Template.xls", saveFileDlg.FileName,
new SheetFormatter("单元格格式化器",
new CellFormatter(sheetParameterContainer["String"], "Hello World!"),
new CellFormatter(sheetParameterContainer["Boolean"], true),
new CellFormatter(sheetParameterContainer["DateTime"], DateTime.Now),
new CellFormatter(sheetParameterContainer["Double"], 3.14),
new CellFormatter(sheetParameterContainer["Image"],Image.FromFile("Image/C#高级编程.jpg").ToBuffer())
)
);
}
}
/// <summary>
/// 导出并保存
/// </summary>
/// <param name="filename">保存文件名，包含路径</param>
/// <param name="title">财务文件中的title</param>
/// <param name="caiWuTotal">财务累计</param>
/// <param name="guoKuTotal">国库累计</param>
/// <param name="tiaoJieBiao">调节表</param>
public void Save(string filename, string title, double caiWuTotal, double guoKuTotal, IEnumerable<TiaoJieItem> tiaoJieBiao)
{
//设置报表内标题与sheet名称
var reportTitles = GetReportTitles(title);
//创建excel参数容器
var workbookParameterContainer = new WorkbookParameterContainer();
workbookParameterContainer.Load(@"Template\Template.xml");
//创建sheet的参数容器
var sheetParameterContainer = workbookParameterContainer["直内"];
//计算小计
var caiWuSubTotal = tiaoJieBiao.Sum(t => t.CreditAmount);
var guoKuSubTotal = tiaoJieBiao.Sum(t => t.Amount);
//输出excel
ExportHelper.ExportToLocal(@"Template\Template.xls", filename,
new SheetFormatter("直内",
new PartFormatter(sheetParameterContainer["CaiWuTitle"], reportTitles.Item1),
new PartFormatter(sheetParameterContainer["GuoKuTitle"], reportTitles.Item2),
new CellFormatter(sheetParameterContainer["CurrentDate"], DateTime.Today.ToShortDateString()),
new CellFormatter(sheetParameterContainer["CaiWuTotal"], caiWuTotal),
new CellFormatter(sheetParameterContainer["GuoKuTotal"], guoKuTotal),
new CellFormatter(sheetParameterContainer["CaiWuSubTotal"], caiWuSubTotal),
new CellFormatter(sheetParameterContainer["GuoKuSubTotal"], guoKuSubTotal),
new CellFormatter(sheetParameterContainer["CaiWuBalance"], caiWuTotal - caiWuSubTotal),
new CellFormatter(sheetParameterContainer["GuoKuBalance"], guoKuTotal - guoKuSubTotal),
new TableFormatter<TiaoJieItem>(sheetParameterContainer["VoucherDate"], tiaoJieBiao,
new CellFormatter<TiaoJieItem>(sheetParameterContainer["Amount"], t => t.Amount),
new CellFormatter<TiaoJieItem>(sheetParameterContainer["CreateDate"], t => t.CreateDate),
new CellFormatter<TiaoJieItem>(sheetParameterContainer["CreditAmount"], t => t.CreditAmount),
new CellFormatter<TiaoJieItem>(sheetParameterContainer["Remark"], t => t.Remark),
new CellFormatter<TiaoJieItem>(sheetParameterContainer["RemarkReason"], t => t.RemarkReason),
new CellFormatter<TiaoJieItem>(sheetParameterContainer["VoucherDate"], t => t.VoucherDate),
new CellFormatter<TiaoJieItem>(sheetParameterContainer["VoucherNumber"], t => t.VoucherNumber)
)
)
);
//修改sheetname
SetSheetName(filename, reportTitles.Item3);
}
