605 lines
26 KiB
C#
605 lines
26 KiB
C#
using Kingdee.BOS.App.Data;
|
||
using Kingdee.BOS.Core;
|
||
using Kingdee.BOS.Core.DynamicForm;
|
||
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
|
||
using Kingdee.BOS.Core.Report;
|
||
using Kingdee.BOS.Core.Report.PlugIn;
|
||
using Kingdee.BOS.Orm.DataEntity;
|
||
using Kingdee.BOS.Util;
|
||
using NPOI.HSSF.Util;
|
||
using NPOI.SS.Formula.Functions;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.SS.Util;
|
||
using NPOI.XSSF.UserModel;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.ComponentModel;
|
||
using System.Data;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Security.Cryptography;
|
||
|
||
namespace Pilot.Report.Exploitation.AccountsReceivable
|
||
{
|
||
/// <summary>
|
||
/// 【表单插件】简易版-按钮触发
|
||
/// </summary>
|
||
[Description("【表单插件】简易版-按钮触发-导出EXCEL"), HotUpdate]
|
||
public class LiteVersionPlugIn : AbstractSysReportPlugIn
|
||
{
|
||
public override void BarItemClick(BarItemClickEventArgs e)
|
||
{
|
||
base.BarItemClick(e);
|
||
List<string> BarItemKeyList = new List<string> { "Test_Button_3", "Test_Button_1" };
|
||
// 判断 应收对账单【简易版】按钮
|
||
if (BarItemKeyList.Contains(e.BarItemKey))
|
||
{
|
||
string IsXN = e.BarItemKey == "Test_Button_3" ? "XN" : "";
|
||
var reportModel = this.SysReportModel;
|
||
|
||
//单据头
|
||
var rptTitles = reportModel.ReportTitles;
|
||
string fClient = rptTitles.FirstOrDefault(a => a.TitleKey == "FClient").TitleValue.ToString();
|
||
string fDate = rptTitles.FirstOrDefault(a => a.TitleKey == "FDate").TitleValue.ToString();
|
||
string fProject = rptTitles.FirstOrDefault(a => a.TitleKey == "FProject").TitleValue.ToString();
|
||
string FState = rptTitles.FirstOrDefault(a => a.TitleKey == "FState").TitleValue.ToString();
|
||
string F_SFYJKH = rptTitles.FirstOrDefault(a => a.TitleKey == "F_SFYJKH").TitleValue.ToString();
|
||
|
||
//string FPath = "应收账款对账单【简易版】.xlsx";
|
||
string FPath = string.Format("{0}-{1}-应收账款对账单.xlsx", string.IsNullOrWhiteSpace(fDate) ? DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") : fDate, fClient);
|
||
|
||
// 在临时文件目录，生成一个完整的文件名: C:\Program Files\Kingdee\K3Cloud\WebSite\...\JD.xls
|
||
string filePath = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, Path.GetFileName(FPath));
|
||
// 生成一个供用户下载文件的url地址: http:\\localhost\K3Cloud\...\JD.xls
|
||
string fileUrl = PathUtils.GetServerPath(KeyConst.TEMPFILEPATH, Path.GetFileName(FPath));
|
||
//打开文件下载界面
|
||
DynamicFormShowParameter showParameter = new DynamicFormShowParameter();
|
||
showParameter.FormId = "BOS_FileDownload";
|
||
showParameter.OpenStyle.ShowType = ShowType.Modal;
|
||
showParameter.CustomComplexParams.Add("url", fileUrl);
|
||
//显示
|
||
this.View.ShowForm(showParameter);
|
||
|
||
|
||
//单据体
|
||
var list = reportModel.DataSource.Rows;
|
||
|
||
string sqlTest = string.Format(@"/*dialect*/Select FAmount,FDate From MBBA_t_Cust100006 Where Month(FDATE) = Month(DATEADD(MONTH, -1, '{0}'))", fDate);
|
||
|
||
var sqlTestList = DBUtils.ExecuteDynamicObject(Context, sqlTest);
|
||
|
||
int Num = 0;
|
||
decimal fAmount = 0;
|
||
string fDate2 = "";
|
||
if (sqlTestList.Count() > 0)
|
||
{
|
||
Num = 1;
|
||
fAmount = Math.Round(Convert.ToDecimal(sqlTestList[0]["FAmount"]),2);
|
||
fDate2 = sqlTestList[0]["FDate"].ToString().Split(' ')[0];
|
||
}
|
||
|
||
Main(filePath, fClient, fDate, fProject, list, Num, fAmount, fDate2, F_SFYJKH, Context, IsXN);
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 赋值EXCEL
|
||
/// </summary>
|
||
static void Main(string outputFilePath, string fClient, string fDate, string fProject, DataRowCollection list, int Num, decimal fAmount,string fDate2, string F_SFYJKH, Kingdee.BOS.Context Context, string IsXN)
|
||
{
|
||
#region Excel代码
|
||
|
||
string filePath = $@"D:\KingdeeModel\对账单模板{IsXN}.xlsx";
|
||
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
|
||
IWorkbook workbook = new XSSFWorkbook(file);
|
||
file.Close();
|
||
|
||
//获取工作簿中的第一个工作表（索引为0）
|
||
ISheet sheet = workbook.GetSheetAt(0);
|
||
//冻结该工作表至13行
|
||
sheet.CreateFreezePane(0, 13);
|
||
|
||
#endregion
|
||
|
||
#region 单元格样式
|
||
|
||
// 创建一个单元格样式-------------------------------
|
||
ICellStyle cellStyle = workbook.CreateCellStyle();
|
||
|
||
//创建字体格式为Times New Roman，字体大小为10号
|
||
IFont font1 = workbook.CreateFont();
|
||
font1.FontName = "Times New Roman";
|
||
font1.FontHeightInPoints = 10;
|
||
|
||
// 设置边框样式
|
||
cellStyle.BorderTop = BorderStyle.Thin;
|
||
cellStyle.BorderBottom = BorderStyle.Thin;
|
||
cellStyle.BorderLeft = BorderStyle.Thin;
|
||
cellStyle.BorderRight = BorderStyle.Thin;
|
||
// 设置水平居中对齐
|
||
cellStyle.Alignment = HorizontalAlignment.Right;
|
||
// 设置垂直居中对齐
|
||
cellStyle.VerticalAlignment = VerticalAlignment.Center;
|
||
//设置数值格式保留两位小数
|
||
cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
|
||
//设置字体和大小
|
||
cellStyle.SetFont(font1);
|
||
|
||
ICellStyle cellStyle3 = workbook.CreateCellStyle();
|
||
|
||
// 设置边框样式
|
||
cellStyle3.BorderTop = BorderStyle.Thin;
|
||
cellStyle3.BorderBottom = BorderStyle.Thin;
|
||
cellStyle3.BorderLeft = BorderStyle.Thin;
|
||
cellStyle3.BorderRight = BorderStyle.Thin;
|
||
// 设置水平居中对齐
|
||
cellStyle3.Alignment = HorizontalAlignment.Center;
|
||
// 设置垂直居中对齐
|
||
cellStyle3.VerticalAlignment = VerticalAlignment.Center;
|
||
//设置字体和大小
|
||
cellStyle3.SetFont(font1);
|
||
|
||
// 创建一个单元格样式---------------------------------
|
||
ICellStyle cellStyle2 = workbook.CreateCellStyle();
|
||
|
||
// 设置边框样式
|
||
cellStyle2.BorderTop = BorderStyle.Thin;
|
||
cellStyle2.BorderBottom = BorderStyle.Thin;
|
||
cellStyle2.BorderLeft = BorderStyle.Thin;
|
||
cellStyle2.BorderRight = BorderStyle.Thin;
|
||
//设置字体和大小
|
||
cellStyle2.SetFont(font1);
|
||
// 设置垂直居中对齐
|
||
cellStyle2.VerticalAlignment = VerticalAlignment.Center;
|
||
//自动换行
|
||
cellStyle2.WrapText = true;
|
||
|
||
// 创建单元格样式---------------------------------------
|
||
ICellStyle style = workbook.CreateCellStyle();
|
||
|
||
// 设置边框样式
|
||
style.BorderTop = BorderStyle.Thin;
|
||
style.BorderBottom = BorderStyle.Thin;
|
||
style.BorderLeft = BorderStyle.Thin;
|
||
style.BorderRight = BorderStyle.Thin;
|
||
|
||
// 创建字体并设置为加粗
|
||
IFont font = workbook.CreateFont();
|
||
font.IsBold = true; // 设置字体加粗
|
||
font.FontName = "Times New Roman";
|
||
font.FontHeightInPoints = 10;
|
||
// 将字体应用到单元格样式
|
||
style.SetFont(font);
|
||
|
||
// 创建单元格样式---------------------------------------
|
||
ICellStyle style2 = workbook.CreateCellStyle();
|
||
|
||
// 创建字体并设置为加粗
|
||
IFont font2 = workbook.CreateFont();
|
||
font2.IsBold = true; // 设置字体加粗
|
||
font2.FontName = "Times New Roman";
|
||
font2.FontHeightInPoints = 10;
|
||
// 将字体应用到单元格样式
|
||
style2.SetFont(font2);
|
||
|
||
// 创建一个单元格样式-------------------------------
|
||
ICellStyle style3 = workbook.CreateCellStyle();
|
||
|
||
// 设置边框样式
|
||
style3.BorderTop = BorderStyle.Thin;
|
||
style3.BorderBottom = BorderStyle.Thin;
|
||
style3.BorderLeft = BorderStyle.Thin;
|
||
style3.BorderRight = BorderStyle.Thin;
|
||
// 设置水平居中对齐
|
||
style3.Alignment = HorizontalAlignment.Right;
|
||
// 设置垂直居中对齐
|
||
style3.VerticalAlignment = VerticalAlignment.Center;
|
||
// 创建字体并设置为加粗
|
||
IFont font3 = workbook.CreateFont();
|
||
font3.IsBold = true; // 设置字体加粗
|
||
font3.FontName = "Times New Roman";
|
||
font3.FontHeightInPoints = 10;
|
||
// 将字体应用到单元格样式
|
||
style3.SetFont(font3);
|
||
style3.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
|
||
|
||
|
||
// 创建一个单元格样式-------------------------------
|
||
ICellStyle style4 = workbook.CreateCellStyle();
|
||
|
||
// 设置边框样式
|
||
style4.BorderTop = BorderStyle.Thin;
|
||
style4.BorderBottom = BorderStyle.Thin;
|
||
style4.BorderLeft = BorderStyle.Thin;
|
||
style4.BorderRight = BorderStyle.Thin;
|
||
// 设置水平居中对齐
|
||
style4.Alignment = HorizontalAlignment.Left;
|
||
// 设置垂直居中对齐
|
||
style4.VerticalAlignment = VerticalAlignment.Center;
|
||
// 将字体应用到单元格样式
|
||
style4.SetFont(font3);
|
||
style4.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
|
||
|
||
|
||
// 创建一个单元格样式-------------------------------
|
||
ICellStyle style5 = workbook.CreateCellStyle();
|
||
|
||
// 设置边框样式
|
||
style5.BorderTop = BorderStyle.Thin;
|
||
style5.BorderBottom = BorderStyle.Thin;
|
||
style5.BorderLeft = BorderStyle.Thin;
|
||
style5.BorderRight = BorderStyle.Thin;
|
||
// 设置水平居中对齐
|
||
style5.Alignment = HorizontalAlignment.Center;
|
||
// 设置垂直居中对齐
|
||
style5.VerticalAlignment = VerticalAlignment.Center;
|
||
// 将字体应用到单元格样式
|
||
style5.SetFont(font3);
|
||
|
||
|
||
// 创建单元格样式---------------------------------------
|
||
ICellStyle style6 = workbook.CreateCellStyle();
|
||
|
||
// 创建字体并设置为加粗
|
||
IFont font5 = workbook.CreateFont();
|
||
font5.FontName = "Times New Roman";
|
||
font5.FontHeightInPoints = 10;
|
||
//font5.IsBold = true; // 设置字体加粗
|
||
|
||
// 将字体应用到单元格样式
|
||
style6.SetFont(font5);
|
||
#endregion
|
||
|
||
#region 获取金额汇总数据
|
||
|
||
decimal YFHpriceSum = 0;
|
||
decimal YFPpriceSum = 0;
|
||
decimal priceSum = 0;
|
||
|
||
for (var i = 0; i < list.Count; i++)
|
||
{
|
||
if (list[i]["Number"].ToString() == "合计")
|
||
{
|
||
YFHpriceSum = Math.Round(Convert.ToDecimal(list[i]["ShippedDebt"]), 2);
|
||
YFPpriceSum = Math.Round(Convert.ToDecimal(list[i]["InvoicedDebt"]), 2);
|
||
//priceSum = Math.Round(Convert.ToDecimal(list[i]["ShippedDebt"]) + Convert.ToDecimal(list[i]["InvoicedDebt"]), 2);
|
||
priceSum = Math.Round(Convert.ToDecimal(list[i]["OwedTickets"]), 2);
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 四行一列修改/六行二列修改/十一行一列修改/十二行一列修改
|
||
|
||
IRow row = sheet.GetRow(3);
|
||
IRow row2 = sheet.GetRow(5);
|
||
IRow row22 = sheet.GetRow(6);
|
||
IRow ro = sheet.GetRow(11);
|
||
ICell cell = row.GetCell(0);
|
||
ICell cell2 = row2.GetCell(1);
|
||
ICell cell22 = row22.GetCell(8);
|
||
ICell cel3 = ro.GetCell(0);
|
||
IRow row3 = sheet.GetRow(10);
|
||
ICell cell3 = row3.GetCell(0);
|
||
//将新创建或获取的单元格的值修改
|
||
cell22.SetCellValue("");
|
||
var YFHpriceSumZW = ToChineseNumber(YFHpriceSum, Context);
|
||
|
||
string CommonDate = string.IsNullOrWhiteSpace(fDate) ? DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") : fDate;
|
||
|
||
cell3.SetCellValue("贵公司截至 " + CommonDate + " 共欠我公司货款金额为：" + YFHpriceSum.ToString("N") + "元（大写：" + YFHpriceSumZW + "）");
|
||
//将新创建或获取的单元格的值修改
|
||
cell.SetCellValue("尊敬的 " + fClient + " 客户您好，我公司已根据合同/订单的要求向贵公司交付相关产品并请验收确认，鉴于我公司对贵公司已通知发货部分的交付义务已完成，");
|
||
cell2.SetCellValue(fClient);
|
||
var fAmountZW = ToChineseNumber(fAmount, Context);
|
||
if (Num > 0 && F_SFYJKH == "2")
|
||
{
|
||
cel3.SetCellValue("其中上期（截止" + fDate2 + "共欠我公司货款金额为：" + fAmount + "元（大写：" + fAmountZW + "）");
|
||
}
|
||
else
|
||
{
|
||
cel3.SetCellValue("");
|
||
}
|
||
#endregion
|
||
|
||
#region 明细赋值
|
||
|
||
// 将字体应用到单元格样式
|
||
style2.SetFont(font2);
|
||
int n = 14;
|
||
|
||
int j = 13;
|
||
|
||
for (var i = 0; i < list.Count; i++)
|
||
{
|
||
string date = string.IsNullOrWhiteSpace(list[i]["DATE"].ToString()) ? "" : Convert.ToDateTime(list[i]["DATE"].ToString().Split(' ')[0]).ToString("yyyy/M/d");
|
||
decimal OrderAmount = string.IsNullOrWhiteSpace(list[i]["OrderAmount"].ToString()) ? 0 : Math.Round(Convert.ToDecimal(list[i]["OrderAmount"]), 2);
|
||
decimal ShipmentsAmount = string.IsNullOrWhiteSpace(list[i]["ShipmentsAmount"].ToString()) ? 0 : Math.Round(Convert.ToDecimal(list[i]["ShipmentsAmount"]), 2);
|
||
decimal FBillAllAmount = string.IsNullOrWhiteSpace(list[i]["FBillAllAmount"].ToString()) ? 0 : Math.Round(Convert.ToDecimal(list[i]["FBillAllAmount"]), 2);
|
||
decimal OrdeFALLAMOUNTFORrAmount = string.IsNullOrWhiteSpace(list[i]["FALLAMOUNTFOR"].ToString()) ? 0 : Math.Round(Convert.ToDecimal(list[i]["FALLAMOUNTFOR"]), 2);
|
||
decimal PaymentAmount = string.IsNullOrWhiteSpace(list[i]["PaymentAmount"].ToString()) ? 0 : Math.Round(Convert.ToDecimal(list[i]["PaymentAmount"]), 2);
|
||
decimal ShippedDebt = string.IsNullOrWhiteSpace(list[i]["ShippedDebt"].ToString()) ? 0 : Math.Round(Convert.ToDecimal(list[i]["ShippedDebt"]), 2);
|
||
decimal InvoicedDebt = string.IsNullOrWhiteSpace(list[i]["InvoicedDebt"].ToString()) ? 0 : Math.Round(Convert.ToDecimal(list[i]["InvoicedDebt"]), 2);
|
||
|
||
//将合计行放到最下方
|
||
IRow rowi = sheet.CreateRow(j + i);
|
||
//if (list[i]["Number"].ToString() == "合计")
|
||
//{
|
||
// rowi = sheet.CreateRow(j + list.Count - 1);
|
||
// j = 12;
|
||
//}
|
||
string Number = list[i]["Number"].ToString();
|
||
string CONTRACTNUMBER = list[i]["CONTRACTNUMBER"].ToString();
|
||
if (list[i]["Number"].ToString() == "待核销")
|
||
{
|
||
Number = "";
|
||
CONTRACTNUMBER = list[i]["Number"].ToString();
|
||
}
|
||
ICell c1 = rowi.CreateCell(0); c1.SetCellValue(Number); c1.CellStyle = cellStyle3;
|
||
ICell c2 = rowi.CreateCell(1); c2.SetCellValue(""+date+""); c2.CellStyle = cellStyle2;
|
||
ICell c3 = rowi.CreateCell(2); c3.SetCellValue(CONTRACTNUMBER); c3.CellStyle = cellStyle2;
|
||
ICell c4 = rowi.CreateCell(3); c4.SetCellValue(list[i]["ProjectName"].ToString()); c4.CellStyle = cellStyle2;
|
||
ICell c5 = rowi.CreateCell(4); c5.SetCellValue(Convert.ToDouble(OrderAmount)); c5.CellStyle = cellStyle;
|
||
ICell c6 = rowi.CreateCell(5); c6.SetCellValue(Convert.ToDouble(ShipmentsAmount )); c6.CellStyle = cellStyle;
|
||
ICell c7 = rowi.CreateCell(6); c7.SetCellValue(Convert.ToDouble(FBillAllAmount )); c7.CellStyle = cellStyle;
|
||
ICell c8 = rowi.CreateCell(7); c8.SetCellValue(Convert.ToDouble(OrdeFALLAMOUNTFORrAmount )); c8.CellStyle = cellStyle;
|
||
ICell c9 = rowi.CreateCell(8); c9.SetCellValue(Convert.ToDouble(PaymentAmount )); c9.CellStyle = cellStyle;
|
||
ICell c10 = rowi.CreateCell(9); c10.SetCellValue(Convert.ToDouble(ShippedDebt )); c10.CellStyle = cellStyle;
|
||
ICell c11 = rowi.CreateCell(10); c11.SetCellValue(Convert.ToDouble(InvoicedDebt)); c11.CellStyle = cellStyle;
|
||
ICell c12 = rowi.CreateCell(11); c12.SetCellValue(list[i]["IsEnded"].ToString()); c12.CellStyle = cellStyle3;
|
||
//ICell c13 = rowi.CreateCell(12); c13.SetCellValue(list[i]["Remark"].ToString()); c13.CellStyle = cellStyle2;
|
||
n++;
|
||
if(list[i]["Number"].ToString() == "合计")
|
||
{
|
||
c1.CellStyle = style5;
|
||
c2.CellStyle = style3;
|
||
c3.CellStyle = style3;
|
||
c4.CellStyle = style3;
|
||
c5.CellStyle = style3;
|
||
c6.CellStyle = style3;
|
||
c7.CellStyle = style3;
|
||
c8.CellStyle = style3;
|
||
c9.CellStyle = style3;
|
||
c10.CellStyle = style3;
|
||
c11.CellStyle = style3;
|
||
c12.CellStyle = style3;
|
||
//c13.CellStyle = style3;
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 赋值汇总数据行
|
||
|
||
//获取工作簿中的第一个工作表（索引为1）
|
||
ISheet sheet2 = workbook.GetSheetAt(1);
|
||
IRow rr1 = sheet2.GetRow(15);
|
||
IRow rr2 = sheet2.GetRow(16);
|
||
IRow rr3 = sheet2.GetRow(17);
|
||
ICell cc1 = rr1.GetCell(6 + 1);
|
||
ICell cc2 = rr2.GetCell(6 + 1);
|
||
ICell cc3 = rr3.GetCell(6 + 1);
|
||
cc1.SetCellValue(Convert.ToDouble(YFHpriceSum));
|
||
cc2.SetCellValue(Convert.ToDouble(YFPpriceSum));
|
||
cc3.SetCellValue(Convert.ToDouble(priceSum));
|
||
|
||
#endregion
|
||
|
||
#region 明细行后模板赋值
|
||
|
||
IRow R1 = sheet2.GetRow(15);
|
||
IRow R2 = sheet2.GetRow(16);
|
||
IRow R3 = sheet2.GetRow(17);
|
||
IRow R4 = sheet2.GetRow(18);
|
||
IRow R5 = sheet2.GetRow(19);
|
||
IRow R6 = sheet2.GetRow(20);
|
||
IRow R7 = sheet2.GetRow(21);
|
||
|
||
IRow r1 = sheet.CreateRow(n);
|
||
IRow r2 = sheet.CreateRow(n + 1);
|
||
IRow r3 = sheet.CreateRow(n + 2);
|
||
IRow r4 = sheet.CreateRow(n + 3);
|
||
IRow r5 = sheet.CreateRow(n + 4);
|
||
IRow r6 = sheet.CreateRow(n + 5);
|
||
IRow r7 = sheet.CreateRow(n + 6);
|
||
|
||
//第一行
|
||
for (int i = 0; i < R1.LastCellNum; i++)
|
||
{
|
||
var test1 = "";
|
||
if (R1.GetCell(i) != null)
|
||
{
|
||
test1 = R1.GetCell(i).ToString();
|
||
}
|
||
ICell CR1 = r1.CreateCell(i);
|
||
CR1.SetCellValue(test1);
|
||
if (i == 4 + 1 || i == 5 + 1 || i == 7 + 1 || i == 8 + 1 || i == 9 + 1)
|
||
{
|
||
CR1.CellStyle = style;
|
||
}
|
||
else if(i == 6 + 1)
|
||
{
|
||
CR1.SetCellValue(Convert.ToDouble(test1));
|
||
CR1.CellStyle = style4;
|
||
}
|
||
else
|
||
{
|
||
CR1.CellStyle = style2;
|
||
}
|
||
}
|
||
|
||
//第二行
|
||
for (int i = 0; i < R2.LastCellNum; i++)
|
||
{
|
||
var test1 = "";
|
||
if (R2.GetCell(i) != null)
|
||
{
|
||
test1 = R2.GetCell(i).ToString();
|
||
}
|
||
ICell CR1 = r2.CreateCell(i);
|
||
CR1.SetCellValue(test1);
|
||
if (i == 4 + 1 || i == 5 + 1 || i == 7 + 1 || i == 8 + 1 || i == 9 + 1)
|
||
{
|
||
CR1.CellStyle = style;
|
||
}
|
||
else if (i == 6 + 1)
|
||
{
|
||
CR1.SetCellValue(Convert.ToDouble(test1));
|
||
CR1.CellStyle = style4;
|
||
}
|
||
else
|
||
{
|
||
CR1.CellStyle = style2;
|
||
}
|
||
}
|
||
|
||
//第三行
|
||
for (int i = 0; i < R3.LastCellNum; i++)
|
||
{
|
||
var test1 = "";
|
||
if (R3.GetCell(i) != null)
|
||
{
|
||
test1 = R3.GetCell(i).ToString();
|
||
}
|
||
ICell CR1 = r3.CreateCell(i);
|
||
CR1.SetCellValue(test1);
|
||
if (i == 4 + 1 || i == 5 + 1 || i == 7 + 1 || i == 8 + 1 || i == 9 + 1)
|
||
{
|
||
CR1.CellStyle = style;
|
||
}
|
||
else if (i == 6 + 1)
|
||
{
|
||
CR1.SetCellValue(Convert.ToDouble(test1));
|
||
CR1.CellStyle = style4;
|
||
}
|
||
else
|
||
{
|
||
CR1.CellStyle = style2;
|
||
}
|
||
}
|
||
|
||
//第四行
|
||
for (int i = 0; i < R4.LastCellNum; i++)
|
||
{
|
||
var test1 = "";
|
||
if (R4.GetCell(i) != null)
|
||
{
|
||
test1 = R4.GetCell(i).ToString();
|
||
}
|
||
ICell CR1 = r4.CreateCell(i);
|
||
CR1.SetCellValue(test1);
|
||
CR1.CellStyle = style2;
|
||
}
|
||
|
||
//第五行
|
||
for (int i = 0; i < R5.LastCellNum; i++)
|
||
{
|
||
var test1 = "";
|
||
if (R5.GetCell(i) != null)
|
||
{
|
||
test1 = R5.GetCell(i).ToString();
|
||
}
|
||
ICell CR1 = r5.CreateCell(i);
|
||
CR1.SetCellValue(test1);
|
||
if (i >= 4)
|
||
{
|
||
CR1.CellStyle = style2;
|
||
}
|
||
else
|
||
{
|
||
CR1.CellStyle = style6;
|
||
}
|
||
}
|
||
|
||
//第六行
|
||
for (int i = 0; i < R6.LastCellNum; i++)
|
||
{
|
||
var test1 = "";
|
||
if (R6.GetCell(i) != null)
|
||
{
|
||
test1 = R6.GetCell(i).ToString();
|
||
}
|
||
ICell CR1 = r6.CreateCell(i);
|
||
CR1.SetCellValue(test1);
|
||
if (i >= 4)
|
||
{
|
||
CR1.CellStyle = style2;
|
||
}
|
||
else
|
||
{
|
||
CR1.CellStyle = style6;
|
||
}
|
||
}
|
||
|
||
//第七行
|
||
for (int i = 0; i < R7.LastCellNum; i++)
|
||
{
|
||
var test1 = "";
|
||
if (R7.GetCell(i) != null)
|
||
{
|
||
test1 = R7.GetCell(i).ToString();
|
||
}
|
||
ICell CR1 = r7.CreateCell(i);
|
||
CR1.SetCellValue(test1);
|
||
if (i >= 4)
|
||
{
|
||
CR1.CellStyle = style2;
|
||
}
|
||
else
|
||
{
|
||
CR1.CellStyle = style6;
|
||
}
|
||
}
|
||
|
||
//合并单元格
|
||
for (int i = 0; i < 7; i++)
|
||
{
|
||
// 合并单元格的起始行、结束行、起始列和结束列
|
||
int firstRow = n + i; // 比如第一行
|
||
int lastRow = n + i; // 合并到第三行（0-based index）
|
||
int firstCol = 4 + 1; // 比如第一列
|
||
int firstCol2 = 7 + 1; // 比如第一列
|
||
int firstCol3 = 0; // 比如第一列
|
||
int lastCol = 5 + 1; // 合并到第三列（0-based index）
|
||
int lastCol2 = 9 + 1; // 合并到第三列（0-based index）
|
||
int lastCol3 = 3 + 1; // 合并到第三列（0-based index）
|
||
// 创建CellRangeAddress对象
|
||
CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
|
||
CellRangeAddress cellRangeAddress2 = new CellRangeAddress(firstRow, lastRow, firstCol2, lastCol2);
|
||
CellRangeAddress cellRangeAddress3 = new CellRangeAddress(firstRow, lastRow, firstCol3, lastCol3);
|
||
// 合并单元格
|
||
sheet.AddMergedRegion(cellRangeAddress);
|
||
sheet.AddMergedRegion(cellRangeAddress2);
|
||
sheet.AddMergedRegion(cellRangeAddress3);
|
||
}
|
||
//移除第二个工作表
|
||
workbook.RemoveSheetAt(1);
|
||
//重命名工作表
|
||
workbook.SetSheetName(0,string.Format("简易版{0}", CommonDate));
|
||
#endregion
|
||
|
||
#region Excel代码2
|
||
|
||
string folderPath = Path.GetDirectoryName(outputFilePath);
|
||
if (!Directory.Exists(folderPath))
|
||
{
|
||
Directory.CreateDirectory(folderPath);
|
||
}
|
||
FileStream exportFile = new FileStream(outputFilePath, FileMode.Create, FileAccess.Write);
|
||
workbook.Write(exportFile);
|
||
exportFile.Close();
|
||
|
||
#endregion
|
||
|
||
}
|
||
|
||
public static string ToChineseNumber(decimal number, Kingdee.BOS.Context Context)
|
||
{
|
||
//Select top 1 dbo.ConvertAmountToChineseWords(12345.14) as 'PriceText' From T_SAL_ORDER
|
||
string sql = string.Format(@"/*dialect*/Select top 1 dbo.ConvertAmountToChineseWords({0}) as 'PriceText' From T_SAL_ORDER", number);
|
||
var list = DBUtils.ExecuteDynamicObject(Context, sql);
|
||
return list[0]["PriceText"].ToString();
|
||
}
|
||
}
|
||
}
|