本文介绍了一种将成交报告的应收应付数据导出至Excel的方法。通过C#编程实现Excel文档的创建与数据填充，并提供了详细的代码示例。文章还涵盖了如何合并单元格、设置样式及自动调整列宽等技巧。
/// <summary>
/// 导出成交报告应收应付到EXCEL
/// </summary>
public void InExportToExcelAgreement(HtmlViewCondition htmlView, SearchAgreementViewModel search)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel(*.xls)|*.xls|All Files(*.*)|*.*";
dialog.FileName = "应收应付统计.xls";
BaseCommon.CheckInitHtmlViewCondition(ref htmlView);
var xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;
try
{
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Workbooks xlBooks = xlApp.Workbooks;
Workbook xlBook = xlBooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet xlSheet = (Worksheet)xlBook.Worksheets[1];
//新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错
//xlApp.Application.Workbooks.Add(true);
Range range = null;
//****** 抬头 *********************************************************************************
range = xlSheet.Range["A1", "K1"];//
range.Merge(Missing.Value); // 合并单元格
range.Columns.AutoFit(); // 设置列宽为自动适应
// 设置单元格左边框加粗
range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;
// 设置单元格右边框加粗
range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 设置单元格水平居中
range.Value2 = "应收应付清单";
range.Font.Size = 18; // 设置字体大小
range.Font.ColorIndex = 5; // 设置字体颜色
//range.Interior.ColorIndex = 6; // 设置单元格背景色
range.RowHeight = 25; // 设置行高
range.ColumnWidth = 20; // 设置列宽
//给第二行赋值，即表格的标题
xlSheet.Cells[2, 1] = "合同编号";
xlSheet.Cells[2, 2] = "成交日期";
xlSheet.Cells[2, 3] = "类型";
xlSheet.Cells[2, 4] = "物业地址";
xlSheet.Cells[2, 5] = "签约人";
xlSheet.Cells[2, 6] = "业主/客户";
xlSheet.Cells[2, 7] = "收付日";
xlSheet.Cells[2, 8] = "款类";
xlSheet.Cells[2, 9] = "应收";
xlSheet.Cells[2, 10] = "实收";
xlSheet.Cells[2, 11] = "状态";
int rowIndex = 3;//这个用来标记数据有多少行位置，从第三行开始显示数据
int myrowIndex = 3;
int totalsize = 0;
var lvwList = AgreementLogic.GetAgreementFollow(htmlView, out totalsize, search);//取出数据信息
foreach (var model in lvwList)
{
var count = AgreementLogic.GetAgreementIncomingOutgoing(model.Id).Count;//取单个成交报告收支表的个数
myrowIndex = myrowIndex + count;
}
//标题栏
range = xlSheet.Range[xlSheet.Cells[2, 1], xlSheet.Cells[2, 11]];
range.Interior.ColorIndex = 45;//设置标题背景色为 浅橙色
range.Font.Bold = true;//标题字体加粗
foreach (var objItem in lvwList)
{
var type = "";
if (objItem.DeamndType == 0)
{
type = "出售";
}
if (objItem.DeamndType == 1)
{
type = "出租";
}
if (objItem.DeamndType == 2)
{
type = "办证";
}
var agreement = AgreementLogic.BrowseAgreementDetial(objItem.Id);
xlSheet.Cells[rowIndex, 1] = objItem.SerialNumber;//合同编号
xlSheet.Cells[rowIndex, 2] = Convert.ToDateTime(objItem.DealTime).ToString("yyyy-MM-dd");//成交日期
xlSheet.Cells[rowIndex, 3] = string.Format("{0}{1}", type, HaoyoujuDict.Haoyoujudict.DeamndTypeDict[objItem.PropertyType]);//类型
xlSheet.Cells[rowIndex, 4] = objItem.PropertyAddress;//物业地址
xlSheet.Cells[rowIndex, 5] = objItem.Name;//签约人
xlSheet.Cells[rowIndex, 6] = string.Format("{0}/{1}", agreement.ForSaleName, agreement.ToBuyName);//业主客户
xlSheet.Cells[rowIndex, 11] = string.Format("{0}-{1}", HaoyoujuDict.Haoyoujudict.AgreementStatusDict[objItem.Status], objItem.IsLock ? "锁定" : "正常");//状态
var list = AgreementLogic.GetAgreementIncomingOutgoing(objItem.Id);//去成交报告的收支信息
int count = 0;
if (list.Count == 0)//如果收支信息为空
{
xlSheet.Cells[rowIndex, 7] = null;//收付日
xlSheet.Cells[rowIndex, 8] = null;//款类
xlSheet.Cells[rowIndex, 9] = null;//应收
xlSheet.Cells[rowIndex, 10] = null;//实收
count = 1;
}
else
{
foreach (var m in list)//取出每个成交报告下的收支详细信息
{
xlSheet.Cells[rowIndex + count, 7] = Convert.ToDateTime(m.BillTime).ToString("yyyy-MM-dd");//收付日
xlSheet.Cells[rowIndex + count, 8] = HaoyoujuDict.Haoyoujudict.AgreementIncomingTypeDict[m.Type];//款类
xlSheet.Cells[rowIndex + count, 9] = string.Format("{0}元", m.MustAmount);//应收
xlSheet.Cells[rowIndex + count, 10] = string.Format("{0}元", m.ActualAmount);//实收
count += 1;
}
}
#region 合并单元格
//如果一个成交报告的收支信息的数量超过两条，则需要合并单元格
if (list.Count >= 2)
{
range = xlSheet.Range[xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex + count - 1, 1]];
range.Merge(Missing.Value);
range.Value2 = objItem.SerialNumber;//合同编号
range = xlSheet.Range[xlSheet.Cells[rowIndex, 2], xlSheet.Cells[rowIndex + count - 1, 2]];
range.Merge(Missing.Value);
range.Value2 = Convert.ToDateTime(objItem.DealTime).ToString("yyyy-MM-dd");//成交日期
range = xlSheet.Range[xlSheet.Cells[rowIndex, 3], xlSheet.Cells[rowIndex + count - 1, 3]];
range.Merge(Missing.Value);
range.Value2 = string.Format("{0}{1}", type, HaoyoujuDict.Haoyoujudict.DeamndTypeDict[objItem.PropertyType]);//类型
range = xlSheet.Range[xlSheet.Cells[rowIndex, 4], xlSheet.Cells[rowIndex + count - 1, 4]];
range.Merge(Missing.Value);
range.Value2 = objItem.PropertyAddress;//物业地址
range = xlSheet.Range[xlSheet.Cells[rowIndex, 5], xlSheet.Cells[rowIndex + count - 1, 5]];
range.Merge(Missing.Value);
range.Value2 = objItem.Name;//签约人
range = xlSheet.Range[xlSheet.Cells[rowIndex, 6], xlSheet.Cells[rowIndex + count - 1, 6]];
range.Merge(Missing.Value);
range.Value2 = string.Format("{0}/{1}", agreement.ForSaleName, agreement.ToBuyName);//业主客户
range = xlSheet.Range[xlSheet.Cells[rowIndex, 11], xlSheet.Cells[rowIndex + count - 1, 11]];
range.Merge(Missing.Value);
range.Value2 = string.Format("{0}-{1}", HaoyoujuDict.Haoyoujudict.AgreementStatusDict[objItem.Status], objItem.IsLock ? "锁定" : "正常");//状态
}
#endregion
rowIndex = rowIndex + count;
}
//数据区域
range = xlSheet.Range[xlSheet.Cells[2, 1], xlSheet.Cells[rowIndex, 11]];
range.Borders.LineStyle = 1;
range.Font.Size = 11;
range.Columns.AutoFit();
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 设置单元格水平居中
range = xlSheet.Range[xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, 11]];
range.Merge(Missing.Value); // 合并单元格
range.RowHeight = 20;
range.Value2 = "导出时间: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
range.HorizontalAlignment = XlHAlign.xlHAlignRight;// 设置单元格水平居中
//***** 格式设定 ******************************************************************************
if (xlSheet != null)
{
xlApp.Visible = false;//显示填充效果
xlBook.SaveCopyAs("D:\\"+dialog.FileName);//保存到服务器地址
xlBook.Close(false, null, null); //关闭excel
xlApp.Quit();//关掉任务管理器的进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
GC.Collect();//销毁
string path = "D:\\"+dialog.FileName;//找到服务器下载路径
System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();//清除原有会话
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8 ;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("content-disposition", "attachment;filename=" + file.Name);
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
//Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行
Response.End();
}
}
catch (Exception ex)
{
Logs.Context.Error(this, ex);
xlApp.Quit();
GC.Collect();//销毁
}
}
加上页面JS吧
$("#btn_toSubmit").click(function () {
var url = '@(Url.Action("InExportToExcelAgreement", "ExcelTonji"))?{0}'.format($("#form1").serialize());
$("#form1").attr("action", url);
$("#form1").submit();
$("#form1").attr("action", "");
});
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
