public void Execute()
{
var workbook = ExcelExtension.LoadExcel(Xlsx);
var sheet = workbook.GetSheetAt(0);
for (var i = BeginRow - 1; i < EndRow; i++)
{
var row = sheet.GetRow(i);
var xzqh = row.Cell(XzqhCol).Value();
if (!string.IsNullOrEmpty(row.Cell(DwmcCol).Value()))
{
continue;
}
var dwmc = "";
Match match = null;
foreach (var regex in regex)
{
match = Regex.Match(xzqh, regex);
if (match.Success)
{
dwmc = match.Groups[1].Value;
break;
}
}
WriteLine($"{i} {xzqh} => {dwmc}");
row.Cell(DwmcCol).SetValue(dwmc);
}
workbook.Save(Util.StringEx.AppendToFileName(Xlsx, ".upd"));
}
public void Execute()
{
using var db = new Context();
var saveXlsx = $@"D:\参保管理\参保全覆盖\雨湖区未参保落实台账{Util.DateTime.FormatedDate()}.xlsx";
if (!string.IsNullOrEmpty(FileName))
{
saveXlsx = $@"D:\参保管理\参保全覆盖\{FileName}";
}
var sql = "SELECT * FROM fc_yxfsj ";
if (!string.IsNullOrEmpty(Where))
{
sql += $" where {Where} ";
}
sql += " ORDER BY CONVERT(dwmc USING gbk), " +
"FIELD(SUBSTRING(xfpc,2,1),'一','二','三','四','五','六','七','八','九'), no";
// WriteLine($"{sql}, ${saveXlsx}"); return;
IQueryable <Yxfsj> data = db.Yxfsjs.FromSqlRaw(sql);
WriteLine($"开始导出未参保落实台账: =>{saveXlsx}");
var workbook = ExcelExtension.LoadExcel(tmplXlsx);
var sheet = workbook.GetSheetAt(0);
int startRow = 2, currentRow = 2;
foreach (var d in data)
{
var index = currentRow - startRow + 1;
WriteLine($"{index} {d.Idcard} {d.Name}");
var row = sheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(index);
row.Cell("B").SetValue(d.Dwmc);
row.Cell("C").SetValue(d.Xfpc);
row.Cell("D").SetValue(d.No);
row.Cell("E").SetValue(d.Name);
row.Cell("F").SetValue(d.Idcard);
row.Cell("G").SetValue(d.Tcq);
row.Cell("H").SetValue(d.Xzj);
row.Cell("I").SetValue(d.Csq);
row.Cell("J").SetValue(d.Sfycb);
row.Cell("K").SetValue(d.Cbsj);
row.Cell("L").SetValue(d.Wcbyy);
}
workbook.Save(saveXlsx);
WriteLine($"结束导出未参保落实台账: =>{saveXlsx}");
}
public void Execute()
{
var workbook = ExcelExtension.LoadExcel(SourceExcel);
var sheet = workbook.GetSheetAt(0);
WriteLine("生成分组映射表");
var map = new Dictionary <string, List <int> >();
for (var index = BeginRow - 1; index < EndRow; index++)
{
var xzj = sheet.Row(index).Cell(DistCol).Value();
if (Regex.IsMatch(xzj, DistPattern))
{
if (!map.ContainsKey(xzj))
{
map[xzj] = new List <int>();
}
map[xzj].Add(index);
}
}
WriteLine("生成分组数据表");
foreach (var xzj in map.Keys)
{
var count = map[xzj].Count;
WriteLine($"{xzj}: {count}");
if (count <= 0)
{
continue;
}
var outWorkbook = ExcelExtension.LoadExcel(TemplateExcel);
var outSheet = outWorkbook.GetSheetAt(0);
int startRow = 1, currentRow = startRow;
(int Begin, int End)cols = (1, 7);
map[xzj].ForEach(rowIndex => {
var index = currentRow - startRow + 1;
var inRow = sheet.Row(rowIndex);
var outRow = outSheet.GetOrCopyRow(currentRow++, startRow);
outRow.Cell("A").SetValue(index);
for (var col = cols.Begin; col <= cols.End; col++)
{
outRow.Cell(col).SetValue(inRow.Cell(col).Value());
}
});
outWorkbook.Save(
Path.Join(OutDir, $"{xzj}{map[xzj].Count}.xls"));
//break;
}
}
public static void TestExcel()
{
var workbook = ExcelExtension.LoadExcel(@"D:\精准扶贫\雨湖区精准扶贫底册模板.xlsx");
var sheet = workbook.GetSheetAt(0);
var row = sheet.Row(2);
row.Cell("U").SetValue("属于贫困人员");
row.Cell("V").SetValue("认定身份");
workbook.Save($@"D:\精准扶贫\雨湖区精准扶贫底册模板{DateTime.FormatedDate()}.xlsx");
}
/// <summary>将Excel表格导入数据库</summary>
///
/// <param name="startRow">开始行(从1开始)</param>
/// <param name="endRow">结束行(包含)</param>
///
public static int LoadExcel<T>(
this DbContext context, string fileName, int startRow,
int endRow, List<string> fields, List<string> noQuotes = null,
bool printSql = false, string ident = "", int tableIndex = 0)
where T : class
{
var workbook = ExcelExtension.LoadExcel(fileName);
var sheet = workbook.GetSheetAt(tableIndex);
var regex = new Regex("^[A-Z]+$", RegexOptions.IgnoreCase);
var builder = new StringBuilder();
for (var index = startRow - 1; index < endRow; index++)
{
try
{
var values = new List<string>();
foreach (var row in fields)
{
string value = row;
if (regex.IsMatch(row))
{
value = sheet.Row(index).Cell(row).Value();
if (noQuotes == null || !noQuotes.Contains(row))
value = $"'{value}'";
}
values.Add(value);
}
builder.Append(string.Join(',', values));
builder.Append("\n");
}
catch (Exception ex)
{
throw new Exception($"LoadExcel error at row {index + 1}", ex);
}
}
var tmpFileName = Path.GetTempFileName();
File.AppendAllText(tmpFileName, builder.ToString());
var cvsFileName = new Uri(tmpFileName).AbsolutePath;
var tableName = context.GetTableName<T>();
var sql = $@"load data infile '{cvsFileName}' into table `{tableName}` " +
@"CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY " +
@"ENCLOSED BY '\'' LINES TERMINATED BY '\n';";
var result = context.ExecuteSql(sql, printSql, ident);
if (File.Exists(tmpFileName))
File.Delete(tmpFileName);
return result;
}
public void Execute()
{
var workbook = ExcelExtension.LoadExcel(CertExcel);
var sheet = workbook.GetSheetAt(0);
var map = Program.GenerateGroupData(sheet, BeginRow, EndRow);
if (Directory.Exists(OutputDir))
{
Directory.Move(OutputDir, OutputDir + ".orig");
}
Directory.CreateDirectory(OutputDir);
var total = 0;
foreach (var(xzj, group) in map)
{
WriteLine($"{(xzj+":").FillRight(11)} {group.Total}");
total += group.Total;
Directory.CreateDirectory(Path.Join(OutputDir, xzj));
foreach (var(csq, list) in group.Data)
{
WriteLine($" {(csq+":").FillRight(11)} {list.Count}");
var outWorkbook = ExcelExtension.LoadExcel(Program.CertTemplate);
var outSheet = outWorkbook.GetSheetAt(0);
int startRow = 4, currentRow = 4;
list.ForEach(rowIndex =>
{
var index = currentRow - startRow + 1;
var inRow = sheet.Row(rowIndex);
var row = outSheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(index);
row.Cell("B").SetValue(inRow.Cell("C").Value());
row.Cell("C").SetValue((inRow.Cell("E").Value() == "1") ? "男" : "女");
row.Cell("D").SetValue(inRow.Cell("D").Value());
row.Cell("E").SetValue(inRow.Cell("A").Value());
row.Cell("M").SetValue(inRow.Cell("I").Value());
});
outWorkbook.Save(
Path.Join(OutputDir, xzj, $"{csq}.xls"));
}
}
WriteLine($"{"合计:".FillRight(11)} {total}");
}
public void Execute()
{
using var db = new Context();
WriteLine("开始更新落实总台账");
var workbook = ExcelExtension.LoadExcel(Xlsx);
var sheet = workbook.GetSheetAt(0);
for (var index = BeginRow - 1; index < EndRow; index++)
{
var row = sheet.Row(index);
var no = row.Cell("A").Value();
var book = new Books
{
Dwmc = row.Cell("B").Value(),
Name = row.Cell("C").Value(),
Idcard = row.Cell("D").Value(),
Address = row.Cell("E").Value(),
Hsqk = row.Cell("F").Value(),
};
WriteLine($"{no} {book.Idcard} {book.Name.FillRight(6)}");
var fcbook = from b in db.Books
where b.Idcard == book.Idcard
select b;
if (fcbook.Any())
{
foreach (var fb in fcbook)
{
db.Entry(fb).CurrentValues.SetValues(book);
}
}
else
{
db.Add(book);
}
db.SaveChanges();
}
WriteLine("结束更新落实总台账");
}
public int LoadExcel(
string tableName, string excelFile, int startRow, int endRow,
string[] fields, HashSet <string> noQuote = null)
{
var workbook = ExcelExtension.LoadExcel(excelFile);
var sheet = workbook.GetSheetAt(0);
var buf = new StringBuilder();
for (var index = startRow; index <= endRow; index++)
{
var values = new List <string>();
foreach (var row in fields)
{
var value = sheet.GetRow(index).Cell(row).Value();
if (noQuote != null && noQuote.Contains(row))
{
value = $"'{value}'";
}
values.Add(value);
}
buf.Append(string.Join(",", values));
buf.Append("\n");
}
var tmpFile = Path.GetTempFileName();
try
{
File.WriteAllText(tmpFile, buf.ToString());
var loadSql =
$"load data infile '{tmpFile.Replace(@"\", @"\\")}' into table `{tableName}` " +
"CHARACTER SET utf8 FIELDS TERMINATED BY ',' " +
"OPTIONALLY ENCLOSED BY '\\'' LINES TERMINATED BY '\\n'";
return(Database.ExecuteSqlRaw(loadSql));
}
finally
{
File.Delete(tmpFile);
}
}
protected override IEnumerable <FpRawData> Fetch()
{
var workbook = ExcelExtension.LoadExcel(Xlsx);
var sheet = workbook.GetSheetAt(0);
for (var index = BeginRow - 1; index < EndRow; index++)
{
var row = sheet.Row(index);
if (row != null)
{
var name = row.Cell("C").Value();
var idcard = row.Cell("D").Value();
idcard = idcard.Trim();
if (idcard.Length < 18)
{
continue;
}
if (idcard.Length > 18)
{
idcard = idcard.Substring(0, 18).ToUpper();
}
var birthDay = idcard.Substring(6, 8);
var xzj = row.Cell("A").Value();
var csq = row.Cell("B").Value();
yield return(new FpRawData
{
Name = name,
Idcard = idcard,
BirthDay = birthDay,
Xzj = xzj,
Csq = csq,
Type = "特困人员",
Detail = "是",
Date = Date
});
}
}
}
public void Execute()
{
var workbook = ExcelExtension.LoadExcel(CertExcel);
var sheet = workbook.GetSheetAt(0);
var map = Program.GenerateGroupData(sheet, BeginRow, EndRow);
var total = 0;
foreach (var(xzj, group) in map)
{
WriteLine($"{(xzj+":").FillRight(11)} {group.Total}");
total += group.Total;
if (Full)
{
foreach (var(csq, list) in group.Data)
{
WriteLine($" {(csq+":").FillRight(11)} {list.Count}");
}
}
}
WriteLine($"{"合计:".FillRight(11)} {total}");
}
public void Execute()
{
var workbook = ExcelExtension.LoadExcel(Program.paymentXlsx);
var sheet = workbook.GetSheetAt(0);
var(year, month, _) = Util.DateTime.SplitDate(Date);
sheet.Cell("A1").SetValue($"{year}年{month}月个人账户返还表");
var date = DateTime.Now.ToString("yyyyMMdd");
var dateCH = DateTime.Now.ToString("yyyy年M月d日");
sheet.Cell("H2").SetValue($"制表时间：{dateCH}");
Session.Use(session =>
{
int startRow = 4, currentRow = 4;
decimal sum = 0;
session.SendService(new PaymentQuery(Date, State));
var result = session.GetResult <Network.Payment>();
result.Data.Sort((x, y) => x.NO - y.NO);
foreach (var data in result.Data)
{
if (data.payType == "3")
{
session.SendService(new PaymentDetailQuery(
NO: $"{data.NO}", yearMonth: $"{data.yearMonth}",
state: $"{data.state}", type: $"{data.type}"));
var detailResult = session.GetResult <PaymentDetail>();
var payment = detailResult[0];
string reason = null, bankName = null;
session.SendService(new DyzzfhQuery(payment.idcard));
var dyzzResult = session.GetResult <Dyzzfh>();
if (!dyzzResult.IsEmpty)
{
session.SendService(new DyzzfhDetailQuery(dyzzResult[0]));
var dyzzDetailResult = session.GetResult <DyzzfhDetail>();
if (!dyzzDetailResult.IsEmpty)
{
var info = dyzzDetailResult[0];
reason = info.reason.Name;
bankName = info.BankName;
}
}
else
{
session.SendService(new CbzzfhQuery(payment.idcard));
var cbzzResult = session.GetResult <Cbzzfh>();
if (!cbzzResult.IsEmpty)
{
session.SendService(new CbzzfhDetailQuery(cbzzResult[0]));
var cbzzDetailResult = session.GetResult <CbzzfhDetail>();
if (!cbzzDetailResult.IsEmpty)
{
var info = cbzzDetailResult[0];
reason = info.reason.Name;
bankName = info.BankName;
}
}
}
var row = sheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(currentRow - startRow);
row.Cell("B").SetValue(payment.name);
row.Cell("C").SetValue(payment.idcard);
var type = payment.TypeCH;
if (reason != null)
{
type = $"{type}({reason})";
}
var amount = payment.amount;
row.Cell("D").SetValue(type);
row.Cell("E").SetValue(payment.payList);
row.Cell("F").SetValue(amount);
row.Cell("G").SetValue(
Util.StringEx.ConvertToChineseMoney(amount));
row.Cell("H").SetValue(data.name);
row.Cell("I").SetValue(data.account);
row.Cell("J").SetValue(bankName);
sum += amount;
}
}
var trow = sheet.GetOrCopyRow(currentRow, startRow);
trow.Cell("A").SetValue("合计");
trow.Cell("F").SetValue(sum);
workbook.Save(Util.StringEx.AppendToFileName(
Program.paymentXlsx, date));
});
}
public void Execute()
{
var dlny = Util.DateTime.ConvertToDashedDate(Date);
var saveXlsx = $@"{Program.rootDir}\到龄贫困人员待遇核定情况表(截至{Date}).xlsx";
var workbook = ExcelExtension.LoadExcel(Program.fphdXlsx);
var sheet = workbook.GetSheetAt(0);
int startRow = 3, currentRow = 3;
Result <Dyry> result = null;
Session.Use(session =>
{
session.SendService(new DyryQuery(dlny));
result = session.GetResult <Dyry>();
});
if (result != null && result.Data.Count > 0)
{
using var context = new FpDbContext();
foreach (var data in result.Data)
{
var idcard = data.idcard;
var fpData = from e in context.FpRawData
where e.Idcard == idcard &&
(e.Type == "贫困人口" ||
e.Type == "特困人员" ||
e.Type == "全额低保人员" ||
e.Type == "差额低保人员")
select e;
if (fpData.Any())
{
string type = "", name = "";
foreach (var record in fpData)
{
if (record.Type == "贫困人口")
{
name = record.Name;
type = record.Type;
break;
}
else if (record.Type == "特困人员" &&
type != "贫困人口")
{
name = record.Name;
type = record.Type;
}
else if (record.Type == "全额低保人员" &&
type != "贫困人口" &&
type != "特困人员")
{
name = record.Name;
type = record.Type;
}
else if (record.Type == "差额低保人员" &&
string.IsNullOrEmpty(type))
{
name = record.Name;
type = record.Type;
}
}
WriteLine(
$"{currentRow - startRow + 1} {data.idcard} {data.name} {type}");
var qjns = data.Yjnx - data.Sjnx;
if (qjns < 0)
{
qjns = 0;
}
var row = sheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(currentRow - startRow);
row.Cell("B").SetValue(data.xzqh);
row.Cell("C").SetValue(data.name);
row.Cell("D").SetValue(data.idcard);
row.Cell("E").SetValue(data.birthDay);
row.Cell("F").SetValue(data.sex.ToString());
row.Cell("G").SetValue(data.hjClass.ToString());
row.Cell("H").SetValue(name);
row.Cell("I").SetValue(type);
row.Cell("J").SetValue(data.JBState);
row.Cell("K").SetValue(data.lqny);
row.Cell("L").SetValue(data.Yjnx);
row.Cell("M").SetValue(data.Sjnx);
row.Cell("N").SetValue(qjns);
row.Cell("O").SetValue(data.qbzt);
row.Cell("P").SetValue(data.bz);
}
}
}
workbook.Save(saveXlsx);
}
void GetPaymentReport(
Session session, string name, string idcard,
string outdir, int retry = 3)
{
session.SendService(new DyfhQuery(idcard, "0"));
var result = session.GetResult <Dyfh>();
if (!result.IsEmpty)
{
session.SendService(new BankInfoQuery(idcard));
var bankInfoResult = session.GetResult <BankInfo>();
var payInfo = result[0].PaymentInfo;
//Console.WriteLine(payInfo.Success);
while (!payInfo.Success)
{
if (--retry > 0)
{
payInfo = result[0].PaymentInfo;
}
else
{
throw new ApplicationException("养老金计算信息无效");
}
//Console.WriteLine(payInfo.Success);
}
var workbook = ExcelExtension.LoadExcel(Program.payInfoXlsx);
var sheet = workbook.GetSheetAt(0);
sheet.Cell("A5").SetValue(payInfo.Groups[1].Value);
sheet.Cell("B5").SetValue(payInfo.Groups[2].Value);
sheet.Cell("C5").SetValue(payInfo.Groups[3].Value);
sheet.Cell("F5").SetValue(payInfo.Groups[4].Value);
sheet.Cell("I5").SetValue(payInfo.Groups[5].Value);
sheet.Cell("L5").SetValue(payInfo.Groups[6].Value);
sheet.Cell("A8").SetValue(payInfo.Groups[7].Value);
sheet.Cell("B8").SetValue(payInfo.Groups[8].Value);
sheet.Cell("C8").SetValue(payInfo.Groups[9].Value);
sheet.Cell("E8").SetValue(payInfo.Groups[10].Value);
sheet.Cell("F8").SetValue(payInfo.Groups[11].Value);
sheet.Cell("G8").SetValue(payInfo.Groups[12].Value);
sheet.Cell("H8").SetValue(payInfo.Groups[13].Value);
sheet.Cell("I8").SetValue(payInfo.Groups[14].Value);
sheet.Cell("J8").SetValue(payInfo.Groups[15].Value);
sheet.Cell("K8").SetValue(payInfo.Groups[16].Value);
sheet.Cell("L8").SetValue(payInfo.Groups[17].Value);
sheet.Cell("M8").SetValue(payInfo.Groups[18].Value);
sheet.Cell("A11").SetValue(payInfo.Groups[19].Value);
sheet.Cell("B11").SetValue(payInfo.Groups[20].Value);
sheet.Cell("C11").SetValue(payInfo.Groups[21].Value);
sheet.Cell("D11").SetValue(payInfo.Groups[22].Value);
sheet.Cell("E11").SetValue(payInfo.Groups[23].Value);
sheet.Cell("F11").SetValue(payInfo.Groups[24].Value);
sheet.Cell("G11").SetValue(payInfo.Groups[25].Value);
sheet.Cell("H11").SetValue(payInfo.Groups[26].Value);
sheet.Cell("I11").SetValue(payInfo.Groups[27].Value);
sheet.Cell("J11").SetValue(payInfo.Groups[28].Value);
sheet.Cell("K11").SetValue(payInfo.Groups[29].Value);
sheet.Cell("L11").SetValue(payInfo.Groups[30].Value);
sheet.Cell("M11").SetValue(payInfo.Groups[31].Value);
sheet.Cell("I12").SetValue(
System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
if (!bankInfoResult.IsEmpty)
{
var bankInfo = bankInfoResult[0];
sheet.Cell("B15").SetValue(bankInfo.name ?? "");
sheet.Cell("F15").SetValue(bankInfo.BankName ?? "");
if (bankInfo.cardNumber != null)
{
var card = bankInfo.cardNumber;
var l = card.Length;
if (l > 7)
{
card = card.Substring(0, 3) + "".PadLeft(l - 7, '*') +
card.Substring(l - 4);
}
else if (l > 4)
{
card = "".PadLeft(l - 4, '*') + card.Substring(l - 4);
}
sheet.Cell("J15").SetValue(card);
}
else
{
sheet.Cell("B15").SetValue("未绑定银行账户");
}
}
workbook.Save(
Path.Join(outdir, $"{name}[{idcard}]养老金计算表.xlsx"));
}
else
{
throw new ApplicationException("未查到该人员核定数据");
}
}
public void Execute()
{
var workbook = ExcelExtension.LoadExcel(SourceExcel);
var sheet = workbook.GetSheetAt(0);
WriteLine("生成分组映射表");
var map = new Dictionary <string, List <int> >();
for (var index = BeginRow - 1; index < EndRow; index++)
{
var xzqh = sheet.Cell(index, XzqhCol).Value();
Match match = null;
foreach (var regex in Xzqh.regex)
{
match = Regex.Match(xzqh, regex);
if (match.Success)
{
break;
}
}
if (match == null || !match.Success)
{
throw new ApplicationException($"未匹配行政区划: {xzqh}");
}
else
{
var xzj = match.Groups[2].Value;
if (!map.ContainsKey(xzj))
{
map[xzj] = new List <int>();
}
map[xzj].Add(index);
}
}
WriteLine("生成分组数据表");
/*if (Directory.Exists(OutDir))
* Directory.Move(OutDir, OutDir + ".orig");
* Directory.CreateDirectory(OutDir);*/
foreach (var xzj in map.Keys)
{
var count = map[xzj].Count;
WriteLine($"{xzj}: {count}");
if (count <= 0)
{
continue;
}
var outWorkbook = ExcelExtension.LoadExcel(TemplateExcel);
var outSheet = outWorkbook.GetSheetAt(0);
int startRow = TemplateBeginRow - 1, currentRow = startRow;
map[xzj].ForEach(rowIndex => {
var index = currentRow - startRow + 1;
var inRow = sheet.Row(rowIndex);
//WriteLine($" {index} {currentRow} {startRow}");
var outRow = outSheet.GetOrCopyRow(currentRow++, startRow);
for (var cell = inRow.FirstCellNum; cell < inRow.LastCellNum; cell++)
{
//WriteLine($"{cell}");
outRow.Cell(cell).SetValue(inRow.Cell(cell).Value());
}
if (NOCel != null)
{
outRow.Cell(NOCel).SetValue(index);
}
});
outWorkbook.Save(
Path.Join(OutDir, $"{xzj}{map[xzj].Count}.xls"));
}
}
public void Execute()
{
var items = new List <Item>();
decimal total = 0;
var typeCH = Network.OtherPayment.Name(Type);
Session.Use(session =>
{
session.SendService(new OtherPaymentQuery(Type, Date));
var result = session.GetResult <Network.OtherPayment>();
result.Data.ForEach(payment =>
{
if (!string.IsNullOrEmpty(payment.typeCH))
{
session.SendService(new OtherPaymentDetailQuery(payment.payList));
var result = session.GetResult <OtherPaymentDetail>();
result.Data.ForEach(paymentDetail =>
{
if (!string.IsNullOrEmpty(paymentDetail.region) &&
paymentDetail.flag == "0")
{
session.SendService(new OtherPaymentPersonalDetailQuery(
paymentDetail.grbh, paymentDetail.payList, paymentDetail.personalPayList));
var result = session.GetResult <OtherPaymentPersonalDetail>();
int?startDate = null, endDate = null;
var count = result.Count;
if (count > 0)
{
startDate = result[0].date;
if (count > 2)
{
endDate = result[count - 2].date;
}
else
{
endDate = startDate;
}
}
total += paymentDetail.amount;
items.Add(new Item
{
region = paymentDetail.region,
name = paymentDetail.name,
idcard = paymentDetail.idcard,
type = typeCH,
yearMonth = paymentDetail.yearMonth,
startDate = startDate,
endDate = endDate,
amount = paymentDetail.amount
});
}
});
}
});
});
var culture = new CultureInfo("zh-CN");
items.Sort((x, y) =>
culture.CompareInfo.Compare(x.region, y.region));
var workbook = ExcelExtension.LoadExcel(Program.payListXlsx);
var sheet = workbook.GetSheetAt(0);
int startRow = 3, currentRow = 3;
var date = DateTime.Now.ToString("yyyyMMdd");
var dateCH = DateTime.Now.ToString("yyyy年M月d日");
sheet.Cell("G2").SetValue($"制表时间：{dateCH}");
foreach (var item in items)
{
var row = sheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(currentRow - startRow);
row.Cell("B").SetValue(item.region);
row.Cell("C").SetValue(item.name);
row.Cell("D").SetValue(item.idcard);
row.Cell("E").SetValue(item.type);
row.Cell("F").SetValue(item.yearMonth?.ToString());
row.Cell("G").SetValue(item.startDate?.ToString());
row.Cell("H").SetValue(item.endDate?.ToString());
row.Cell("I").SetValue(item.amount);;
}
var trow = sheet.GetOrCopyRow(currentRow, startRow);
trow.Cell("C").SetValue("共计");
trow.Cell("D").SetValue(currentRow - startRow);
trow.Cell("H").SetValue("合计");
trow.Cell("I").SetValue(total);
workbook.Save(Util.StringEx.AppendToFileName(
Program.payListXlsx, $"({typeCH}){date}"));
}
public void Execute()
{
var workbook = ExcelExtension.LoadExcel(Program.personListXlsx);
var sheet = workbook.GetSheetAt(0);
int startRow = 3, currentRow = 3;
decimal sum = 0, payedSum = 0;
var date = DateTime.Now.ToString("yyyyMMdd");
var dateCH = DateTime.Now.ToString("yyyy年M月d日");
sheet.Cell("G2").SetValue($"制表时间：{dateCH}");
Session.Use(session =>
{
session.SendService(new OtherPersonQuery(Type, "1", ""));
var result = session.GetResult <OtherPerson>();
result.Data.ForEach((otherPerson) =>
{
if (otherPerson.grbh == null) // 无效数据
{
return;
}
if (!All && !otherPerson.dfState.Value.Equals("1"))
{
return;
}
if (!otherPerson.dfState.Value.Equals("1") && // 不是正常代发
!(otherPerson.dfState.Value.Equals("2") && // 是暂停代发且居保不是正常状态
otherPerson.jbState.Value.Equals("1")))
{
return;
}
decimal payAmount = 0;
if (otherPerson.standard is decimal standard)
{
var startYear = otherPerson.startYearMonth / 100;
var startMonth = otherPerson.startYearMonth % 100;
startMonth -= 1;
if (startMonth == 0)
{
startYear -= 1;
startMonth = 12;
}
if (otherPerson.endYearMonth is int endYearMonth)
{
startYear = endYearMonth / 100;
startMonth = endYearMonth % 100;
}
var match = Regex.Match(Date, @"^(\d\d\d\d)(\d\d)$");
if (match.Success)
{
var endYear = int.Parse(match.Groups[1].Value);
var endMonth = int.Parse(match.Groups[2].Value);
payAmount =
((endYear - startYear) * 12 + endMonth - startMonth) * standard;
}
}
else if (Type == "801" && otherPerson.standard == null &&
otherPerson.totalPayed == 5000)
{
return;
}
var row = sheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(currentRow - startRow);
row.Cell("B").SetValue(otherPerson.region);
row.Cell("C").SetValue(otherPerson.name);
row.Cell("D").SetValue(otherPerson.idcard);
row.Cell("E").SetValue(otherPerson.startYearMonth);
row.Cell("F").SetValue(otherPerson.standard?.ToString());
row.Cell("G").SetValue(otherPerson.type);
row.Cell("H").SetValue(otherPerson.dfState.Name);
row.Cell("I").SetValue(otherPerson.jbState.Name);
row.Cell("J").SetValue(otherPerson.endYearMonth?.ToString());
if (otherPerson.totalPayed != null)
{
var payed = (decimal)otherPerson.totalPayed;
row.Cell("K").SetValue(payed);
payedSum += payed;
}
row.Cell("L").SetValue(payAmount);
sum += payAmount;
});
});
var trow = sheet.GetOrCopyRow(currentRow, startRow);
trow.Cell("A").SetValue("");
trow.Cell("C").SetValue("共计");
trow.Cell("D").SetValue(currentRow - startRow);
trow.Cell("F").SetValue("");
trow.Cell("J").SetValue("合计");
trow.Cell("K").SetValue(payedSum);
trow.Cell("L").SetValue(sum);
if (!All)
{
workbook.Save(Util.StringEx.AppendToFileName(
Program.personListXlsx, $"({OtherPerson.Name(Type)}){date}"));
}
else
{
workbook.Save(Util.StringEx.AppendToFileName(
Program.personListXlsx, $"({OtherPerson.Name(Type)}ALL){date}"));
}
}
public void Execute()
{
const string tmplXlsx = @"D:\参保管理\参保全覆盖2\243030220200701000001Q020310.xls";
string[] files = new string[]
{
@"D:\参保管理\参保全覆盖2\原始下发数据\雨湖区未参加城乡居保.xls",
@"D:\参保管理\参保全覆盖2\原始下发数据\雨湖区未参加城乡居保１.xls"
};
const string dir = @"D:\参保管理\参保全覆盖2\上传比对数据\";
int serialNO = 1;
void genCData(string file, string tmpl)
{
var workbook = ExcelExtension.LoadExcel(file);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
var sheetName = workbook.GetSheetName(i);
WriteLine($"生成比对上传数据: {sheetName}");
var outWorkbook = ExcelExtension.LoadExcel(tmpl);
var outSheet = outWorkbook.GetSheetAt(0);
int startRow = 3, currentRow = 3;
var sheet = workbook.GetSheetAt(i);
for (int r = 0; r <= sheet.LastRowNum; r++)
{
var row = sheet.Row(r);
var idcard = row.Cell("D").Value();
var name = row.Cell("C").Value();
var type = "170";
var xzqh = "430302";
if (string.IsNullOrEmpty(idcard))
{
break;
}
WriteLine($"{currentRow - startRow + 1} {idcard} {name}");
var outRow = outSheet.GetOrCopyRow(currentRow++, startRow);
outRow.Cell("A").SetValue(idcard);
outRow.Cell("B").SetValue(name);
outRow.Cell("C").SetValue(type);
outRow.Cell("D").SetValue(xzqh);
}
var NO = Util.StringEx.FillLeft($"{serialNO++}", 6, '0');
var outFile = $"{2}{430302}{Util.DateTime.FormatedDate()}{NO}{"Q02031"}{0}.xls";
var path = Path.Join(dir, outFile);
WriteLine($"保存至: {path}");
outWorkbook.Save(path);
}
}
foreach (var file in files)
{
genCData(file, tmplXlsx);
}
}
public void Execute()
{
if (Directory.Exists(outputDir))
{
Directory.Move(outputDir, outputDir + ".orig");
}
Directory.CreateDirectory(outputDir);
using var db = new Context();
var sql = "SELECT * FROM fc2_stxfsj ORDER BY id";
IQueryable <FC2Stxfsj> data = db.FC2Stxfsj.FromSqlRaw(sql);
var dataCount = data.Count();
var excelCount = dataCount / countPerExcel;
if (dataCount - excelCount * countPerExcel > 0)
{
excelCount += 1;
}
WriteLine($"全覆盖下发数据共计: {dataCount}, 共分为 {excelCount} 个excel文件");
for (var excelNO = 1; excelNO <= excelCount; excelNO++)
{
sql = $"SELECT * FROM fc2_stxfsj ORDER BY id LIMIT {(excelNO - 1) * countPerExcel}, {countPerExcel}";
data = db.FC2Stxfsj.FromSqlRaw(sql);
var fileName = $"雨湖区全覆盖下发数据清册{excelNO}.xlsx";
var filePath = Path.Join(outputDir, fileName);
WriteLine($"开始导出第 {excelNO} 个文件: => {fileName}");
var workbook = ExcelExtension.LoadExcel(tmplXlsx);
var sheet = workbook.GetSheetAt(0);
int startRow = 2, currentRow = 2;
foreach (var d in data)
{
var index = currentRow - startRow + 1;
WriteLine($"{index} {d.Id} {d.Idcard} {d.Name}");
var row = sheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(d.Id);
row.Cell("B").SetValue(d.Name);
row.Cell("C").SetValue(d.Idcard);
row.Cell("D").SetValue(d.Address);
row.Cell("E").SetValue(d.ManageName);
row.Cell("F").SetValue(d.Hsqk);
row.Cell("G").SetValue(d.Slcb);
row.Cell("H").SetValue(d.Swcb);
row.Cell("I").SetValue(d.InZxxssj == "1" ? "是" : "");
row.Cell("J").SetValue(d.InSfwqjb == "1" ? "是" : "");
row.Cell("K").SetValue(d.Dwmc);
if (index >= countPerExcel)
{
break;
}
}
workbook.Save(filePath);
WriteLine($"结束导出第 {excelNO} 个文件: => {fileName}");
}
}
public void Execute()
{
var startDate = StartDate != null?ConvertToDashedDate(StartDate) : "";
var endDate = EndDate != null?ConvertToDashedDate(EndDate) : "";
var timeSpan = "";
if (startDate != "")
{
timeSpan += startDate;
if (endDate != "")
{
timeSpan += "_" + endDate;
}
}
WriteLine(timeSpan);
var dir = @"D:\精准扶贫\";
var xlsx = "批量信息变更模板.xls";
Result <Cbsh> result = null;
Session.Use(session =>
{
session.SendService(new CbshQuery(startDate, endDate));
result = session.GetResult <Cbsh>();
});
if (result != null)
{
WriteLine($"共计 {result.Count} 条");
if (result.Count > 0)
{
var workbook = ExcelExtension.LoadExcel(Path.Join(dir, xlsx));
var sheet = workbook.GetSheetAt(0);
int index = 1, copyIndex = 1;
var export = false;
using var context = new FpDbContext();
foreach (var cbsh in result.Data)
{
var data = from fpData in context.FpHistoryData
where fpData.Idcard == cbsh.idcard
select fpData;
if (data.Any())
{
var info = data.First();
WriteLine(
$"{cbsh.idcard} {cbsh.name.FillRight(6)} {cbsh.birthDay} {info.Jbrdsf} " +
$"{(info.Name != cbsh.name ? info.Name : "")}");
var row = sheet.GetOrCopyRow(index++, copyIndex, false);
row.Cell("B").SetValue(cbsh.idcard);
row.Cell("E").SetValue(cbsh.name);
row.Cell("J").SetValue(_jbClassMap[info.Jbrdsf]);
export = true;
}
else
{
WriteLine($"{cbsh.idcard} {cbsh.name.FillRight(6)} {cbsh.birthDay}");
}
}
if (Export && export)
{
WriteLine($"导出 批量信息变更{timeSpan}.xls");
workbook.Save(
Path.Join(dir, $"批量信息变更{timeSpan}.xls"), true);
}
}
}
}
static void Main(string[] args)
{
if (args.Length < 2)
{
"使用方法：缴费结束年月 测算表格路径 [不处理的备注内容]".Println();
Environment.Exit(-1);
}
var jfjsny = Convert.ToInt32(args[0]);
var workbook = ExcelExtension.LoadExcel(args[1]);
string nodeal = args.Length > 2 ? args[2] : null;
var sheet = workbook.GetSheetAt(0);
for (var irow = 4; irow <= sheet.LastRowNum; irow++)
{
var memo = sheet.Cell(irow, 15)?.CellValue()?.Trim() ?? "";
if (!string.IsNullOrEmpty(nodeal) && Regex.IsMatch(memo, nodeal))
{
continue;
}
try
{
var jfys = (int)sheet.Cell(irow, 8).NumericCellValue;
var ptys = (int)sheet.Cell(irow, 9).NumericCellValue;
var minuend = GetSpansFromEnd(jfjsny, jfys);
var subtractor = new List <int>();
var wdcb = sheet.Cell(irow, 14).CellValue().Trim();
if (wdcb != null && wdcb != "")
{
var wdcbs = wdcb.Split("|");
for (var i = 0; i < wdcbs.Length; i++)
{
var yjfny = wdcbs[i];
var nys = yjfny.Split("-");
if (nys.Length > 1)
{
var ksny = Convert.ToInt32(nys[0]);
var jsny = Convert.ToInt32(nys[1]);
subtractor.AddRange(GetSpansFromTo(ksny, jsny));
}
else
{
var ksny = Convert.ToInt32(nys[0]);
subtractor.Add(ksny);
}
}
}
SpansSubtract(minuend, subtractor);
var spans = GetSpansList(minuend);
var total = 0;
var ptSpans = new List <string>();
var grSpans = new List <string>();
var inPtSpans = true;
for (var i = 0; i < spans.Count(); i++)
{
var span = spans.ElementAt(i);
var sspan = $"{span.begMonth}-{span.endMonth}[{span.count}]";
total += span.count;
if (inPtSpans && total >= ptys)
{
if (total == ptys)
{
ptSpans.Add(sspan);
}
else
{
int delta = total - ptys;
ptSpans.Add($"{span.begMonth}-{StepMonth(span.endMonth, -delta)}[{span.count-delta}]");
grSpans.Add($"{StepMonth(span.endMonth, -delta+1)}-{span.endMonth}[{delta}]");
}
inPtSpans = false;
continue;
}
if (inPtSpans)
{
ptSpans.Add(sspan);
}
else
{
grSpans.Add(sspan);
}
}
var sptSpans = ptSpans.JoinToString("|");
if (total < ptys)
{
sheet.Cell(irow, 17).SetValue($"补贴月数不足{sptSpans}[{total}<{ptys}]");
}
else
{
sheet.Cell(irow, 16).SetValue(grSpans.JoinToString("|"));
sheet.Cell(irow, 17).SetValue(sptSpans);
}
}
catch
{
continue;
}
}
workbook.Save(Utils.FileNameAppend(args[1], ".new"));
workbook.Close();
}
public static void ExportFpData(
string monthOrAll, string tmplXlsx, string saveXlsx)
{
using var db = new FpDbContext();
WriteLine($"开始导出扶贫底册: {monthOrAll}扶贫数据=>{saveXlsx}");
var workbook = ExcelExtension.LoadExcel(tmplXlsx);
var sheet = workbook.GetSheetAt(0);
int startRow = 2, currentRow = 2;
IQueryable <Database.Jzfp2021.FpData> data = null;
if (monthOrAll.ToUpper() == "ALL")
{
data = db.FpHistoryData.FromSqlRaw(
"SELECT * FROM fphistorydata ORDER BY CONVERT( xzj USING gbk ), " +
"CONVERT( csq USING gbk ), CONVERT( name USING gbk )");
}
else
{
data = db.FpMonthData.FromSqlRaw(
"SELECT * FROM fpmonthdata WHERE month={0} ORDER BY CONVERT( xzj USING gbk ), " +
"CONVERT( csq USING gbk ), CONVERT( name USING gbk )", monthOrAll);
}
foreach (var d in data)
{
var index = currentRow - startRow + 1;
WriteLine($"{index} {d.Idcard} {d.Name}");
var row = sheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(index);
row.Cell("B").SetValue(d.NO);
row.Cell("C").SetValue(d.Xzj);
row.Cell("D").SetValue(d.Csq);
row.Cell("E").SetValue(d.Address);
row.Cell("F").SetValue(d.Name);
row.Cell("G").SetValue(d.Idcard);
row.Cell("H").SetValue(d.BirthDay);
row.Cell("I").SetValue(d.Pkrk);
row.Cell("J").SetValue(d.PkrkDate);
row.Cell("K").SetValue(d.Tkry);
row.Cell("L").SetValue(d.TkryDate);
row.Cell("M").SetValue(d.Qedb);
row.Cell("N").SetValue(d.QedbDate);
row.Cell("O").SetValue(d.Cedb);
row.Cell("P").SetValue(d.CedbDate);
row.Cell("Q").SetValue(d.Yejc);
row.Cell("R").SetValue(d.YejcDate);
row.Cell("S").SetValue(d.Ssjc);
row.Cell("T").SetValue(d.SsjcDate);
row.Cell("U").SetValue(d.Sypkry);
row.Cell("V").SetValue(d.Jbrdsf);
row.Cell("W").SetValue(d.JbrdsfFirstDate);
row.Cell("X").SetValue(d.JbrdsfLastDate);
row.Cell("Y").SetValue(d.Jbcbqk);
row.Cell("Z").SetValue(d.JbcbqkDate);
}
workbook.Save(saveXlsx);
WriteLine($"结束导出扶贫底册: {monthOrAll}扶贫数据=>{saveXlsx}");
}
public void Execute()
{
var saveXlsx = $@"{Program.rootDir}\信息核对报告表{Date}.xlsx";
Result <Dyfh> result = null;
Session.Use(session =>
{
session.SendService(new DyfhQuery());
result = session.GetResult <Dyfh>();
});
if (!result.IsEmpty)
{
using var context = new FpDbContext();
foreach (var data in result.Data)
{
var idcard = data.idcard;
var fpData = from e in context.FpRawData
where e.Idcard == idcard &&
(e.Type == "贫困人口" ||
e.Type == "特困人员" ||
e.Type == "全额低保人员" ||
e.Type == "差额低保人员")
select e;
if (fpData.Any())
{
data.bz = "按人社厅发〔2018〕111号文办理";
foreach (var record in fpData)
{
if (record.Type == "贫困人口")
{
data.fpName = record.Name;
data.fpType = record.Type;
data.fpDate = record.Date;
break;
}
else if (record.Type == "特困人员" &&
data.fpType != "贫困人口")
{
data.fpName = record.Name;
data.fpType = record.Type;
data.fpDate = record.Date;
}
else if (record.Type == "全额低保人员" &&
data.fpType != "贫困人口" &&
data.fpType != "特困人员")
{
data.fpName = record.Name;
data.fpType = record.Type;
data.fpDate = record.Date;
}
else if (record.Type == "差额低保人员" &&
string.IsNullOrEmpty(data.fpType))
{
data.fpName = record.Name;
data.fpType = record.Type;
data.fpDate = record.Date;
}
}
}
}
}
var workbook = ExcelExtension.LoadExcel(Program.infoXlsx);
var sheet = workbook.GetSheetAt(0);
int startRow = 3, currentRow = 3;
foreach (var data in result.Data)
{
var index = currentRow - startRow + 1;
WriteLine($"{index} {data.idcard} {data.name} {data.bz} {data.fpType}");
var row = sheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(index);
row.Cell("B").SetValue(data.name);
row.Cell("C").SetValue(data.idcard);
row.Cell("D").SetValue(data.xzqh);
row.Cell("E").SetValue(data.payAmount);
row.Cell("F").SetValue(data.payMonth);
row.Cell("G").SetValue("是 [ ]");
row.Cell("H").SetValue("否 [ ]");
row.Cell("I").SetValue("是 [ ]");
row.Cell("J").SetValue("否 [ ]");
row.Cell("L").SetValue(data.bz ?? "");
row.Cell("M").SetValue(data.fpType ?? "");
row.Cell("N").SetValue(data.fpDate ?? "");
row.Cell("O").SetValue(data.fpName ?? "");
}
workbook.Save(saveXlsx);
}
static void Main(string[] args)
{
var srcDir = @"E:\机关养老保险\1015确认表";
var outDir = @"E:\机关养老保险\1015确认表（新表）";
var tmpXls = @"E:\机关养老保险\（模板）试点期间参保人员缴费确认表.xls";
foreach (var xls in Directory.EnumerateFiles(srcDir))
{
WriteLine($"{xls}");
var workbook = ExcelExtension.LoadExcel(xls);
var sheet = workbook.GetSheetAt(0);
var outWorkbook = ExcelExtension.LoadExcel(tmpXls);
var outSheet = outWorkbook.GetSheetAt(0);
(int start, int end)copyRange = (1, 11);
int startRow = 4, currentRow = 4;
var code = sheet.Cell(2, 2).Value();
var name = sheet.Cell(2, 6).Value();
outSheet.Cell(2, 2).SetValue(code);
outSheet.Cell(2, 6).SetValue(name);
WriteLine($"{name} {code}");
for (var i = 4; i < sheet.LastRowNum; i++)
{
var r = copyRange.start;
var row = sheet.GetRow(i);
var id = row.Cell(r)?.Value();
if (id == null)
{
continue;
}
if (Regex.IsMatch(id, @"^\d+$"))
{
WriteLine($"{currentRow} {id}");
var outRow = outSheet.GetOrCopyRow(currentRow++, startRow);
outRow.Cell(r).SetValue(id);
for (r += 1; r < copyRange.end; r++)
{
if (r == 8 || r == 9)
{
outRow.Cell(r).SetCellValue(row.Cell(r).NumericCellValue);
}
else
{
outRow.Cell(r).SetValue(row.Cell(r).Value());
}
}
}
else if (id == "说明：")
{
var total = sheet.GetRow(i - 2).Cell("B").Value();
var hj = sheet.GetRow(i - 1).Cell("I").NumericCellValue;
var lx = sheet.GetRow(i - 1).Cell("J").NumericCellValue;
WriteLine($"{total} 合计 {hj} {lx}");
var outRow = outSheet.GetOrCopyRow(currentRow++, startRow);
//outRow.Cell("B").SetValue(total);
outRow.Cell("B").SetCellFormula(
$"CONCATENATE(\"共 \",SUMPRODUCT(1/COUNTIF(C5:C{currentRow-1},C5:C{currentRow-1}&\"*\")),\" 人\")");
outRow.Cell("H").SetValue("合计");
//outRow.Cell("I").SetCellValue(hj);
outRow.Cell("I").SetCellFormula($"SUM(I5:I{currentRow-1})");
//outRow.Cell("J").SetCellValue(lx);
outRow.Cell("J").SetCellFormula($"SUM(J5:J{currentRow-1})");
break;
}
}
outWorkbook.Save(Path.Join(outDir, $"试点期间参保人员缴费确认表_{code}_{name}.xls"));
//break;
}
}
public void Execute()
{
var(year, month, _) = Util.DateTime.SplitDate(Date);
if (month[0] == '0')
{
month = month.Substring(1);
}
var inXlsx = $@"{Program.rootDir}\信息核对报告表{Date}.xlsx";
var outputDir = $@"{Program.rootDir}\{year}年{month}月待遇核定数据";
var workbook = ExcelExtension.LoadExcel(inXlsx);
var sheet = workbook.GetSheetAt(0);
WriteLine("生成分组映射表");
var map = new Dictionary <string, Dictionary <string, List <int> > >();
for (var index = BeginRow - 1; index < EndRow; index++)
{
var xzqh = sheet.Cell(index, "D").Value();
Match match = null;
foreach (var regex in Xzqh.regex)
{
match = Regex.Match(xzqh, regex);
if (match.Success)
{
break;
}
}
if (match == null || !match.Success)
{
throw new ApplicationException($"未匹配行政区划: {xzqh}");
}
else
{
var xzj = match.Groups[2].Value;
var csq = match.Groups[3].Value;
if (!map.ContainsKey(xzj))
{
map[xzj] = new Dictionary <string, List <int> >();
}
if (!map[xzj].ContainsKey(csq))
{
map[xzj][csq] = new List <int> {
index
}
}
;
else
{
map[xzj][csq].Add(index);
}
}
}
WriteLine("生成分组目录并分别生成信息核对报告表");
if (Directory.Exists(outputDir))
{
Directory.Move(outputDir, outputDir + ".orig");
}
Directory.CreateDirectory(outputDir);
foreach (var xzj in map.Keys)
{
WriteLine($"{xzj}:");
Directory.CreateDirectory(Path.Join(outputDir, xzj));
foreach (var csq in map[xzj].Keys)
{
WriteLine($" {csq}: {map[xzj][csq].ToLiteral()}");
Directory.CreateDirectory(Path.Join(outputDir, xzj, csq));
var outWorkbook = ExcelExtension.LoadExcel(Program.infoXlsx);
var outSheet = outWorkbook.GetSheetAt(0);
int startRow = 3, currentRow = 3;
map[xzj][csq].ForEach(rowIndex =>
{
var index = currentRow - startRow + 1;
var inRow = sheet.Row(rowIndex);
WriteLine(
$" {index} {inRow.Cell("C").Value()} {inRow.Cell("B").Value()}");
var row = outSheet.GetOrCopyRow(currentRow++, startRow);
row.Cell("A").SetValue(index);
row.Cell("B").SetValue(inRow.Cell("B").Value());
row.Cell("C").SetValue(inRow.Cell("C").Value());
row.Cell("D").SetValue(inRow.Cell("D").Value());
row.Cell("E").SetValue(inRow.Cell("E").Value());
row.Cell("F").SetValue(inRow.Cell("F").Value());
row.Cell("G").SetValue("是 [ ]");
row.Cell("H").SetValue("否 [ ]");
row.Cell("I").SetValue("是 [ ]");
row.Cell("J").SetValue("否 [ ]");
row.Cell("L").SetValue(inRow.Cell("L").Value());
});
outWorkbook.Save(
Path.Join(outputDir, xzj, csq, $"{csq}信息核对报告表.xlsx"));
}
}
WriteLine("\n按分组生成养老金养老金计算表");
Session.Use(session =>
{
foreach (var xzj in map.Keys)
{
foreach (var csq in map[xzj].Keys)
{
map[xzj][csq].ForEach(index =>
{
var row = sheet.Row(index);
var name = row.Cell("B").Value();
var idcard = row.Cell("C").Value();
WriteLine($" {idcard} {name}");
try
{
GetPaymentReport(
session, name, idcard, Path.Join(outputDir, xzj, csq));
}
catch (Exception e)
{
WriteLine($" {idcard} {name} 获得养老金计算表岀错: {e}");
}
});
}
}
});
}