public static PublishAndCancelFileEntity getFromRow(Row row, SharedStringTablePart t)
{
string curCol = "";
string dateValue = "";
try
{
if (row != null)
{
PublishAndCancelFileEntity entity = new PublishAndCancelFileEntity();
List <Cell> cells = row.Descendants <Cell>().ToList();
curCol = "A";
Cell c = LYJUtil.GetCell(curCol, row.RowIndex, cells);
if (c == null)
{
return(null);
}
entity.seqNo = LYJUtil.GetValue(c, t);
if (string.IsNullOrWhiteSpace(entity.seqNo))
{
return(null);
}
curCol = "B";
dateValue = LYJUtil.GetValue(LYJUtil.GetCell(curCol, row.RowIndex, cells), t);
entity.publishDate = LYJUtil.GetDateTime(dateValue);
curCol = "C";
entity.pubOrCancel = LYJUtil.GetValue(LYJUtil.GetCell(curCol, row.RowIndex, cells), t);
curCol = "E";
entity.fullName = LYJUtil.GetValue(LYJUtil.GetCell(curCol, row.RowIndex, cells), t);
curCol = "J";
string amtValue = LYJUtil.GetValue(LYJUtil.GetCell(curCol, row.RowIndex, cells), t);
entity.amount = decimal.Parse(amtValue, System.Globalization.NumberStyles.Float);
curCol = "K";
dateValue = LYJUtil.GetValue(LYJUtil.GetCell(curCol, row.RowIndex, cells), t);
entity.startDate = LYJUtil.GetDateTime(dateValue);
curCol = "L";
dateValue = LYJUtil.GetValue(LYJUtil.GetCell(curCol, row.RowIndex, cells), t);
entity.endDate = LYJUtil.GetDateTime(dateValue);
return(entity);
}
else
{
throw new MyException("存在空行");
}
}
catch (Exception ex)
{
string msg = "当日披露发行文件及申请取消发行债券基本信息列表第" + row.RowIndex + "行" + curCol + "列存在问题";
throw new MyException(msg + ex.Message + ex.StackTrace);
}
}
public static SendSuccessEntity getFromCell(Row row, SharedStringTablePart t)
{
string curCol = "";
try
{
if (row != null)
{
SendSuccessEntity entity = new SendSuccessEntity();
List <Cell> cells = row.Descendants <Cell>().ToList();
curCol = "B";
Cell cellB = LYJUtil.GetCell("B", row.RowIndex, cells);
if (cellB != null)
{
entity.bondName = LYJUtil.GetValue(cellB, t);
if (string.IsNullOrWhiteSpace(entity.bondName))
{
return(null);
}
}
else
{
return(null);
}
curCol = "C";
entity.bondManager = LYJUtil.GetValue(LYJUtil.GetCell("C", row.RowIndex, cells), t);
curCol = "F";
entity.bondType = LYJUtil.GetValue(LYJUtil.GetCell("F", row.RowIndex, cells), t);
curCol = "H";
entity.bondLevel = LYJUtil.GetValue(LYJUtil.GetCell("H", row.RowIndex, cells), t);
curCol = "L";
string pubAmtStr = LYJUtil.GetValue(LYJUtil.GetCell("L", row.RowIndex, cells), t);
entity.pubAmout = decimal.Parse(pubAmtStr, System.Globalization.NumberStyles.Float);
return(entity);
}
else
{
throw new MyException("存在空行");
}
}
catch (Exception ex)
{
string msg = "向清算所发送登记材料第" + row.RowIndex + "行" + curCol + "列存在问题";
throw new MyException(msg + ex.Message + ex.StackTrace);
}
}
public override void loadData(DateTime date, int _qishu)
{
if (fileName == null)
{
throw new MyException("文件读取失败");
}
dateNow = date;
qishu = _qishu;
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
if (firstRow.Count != 23)
{
throw new MyException("表格列数不对");
}
string kTitle = LYJUtil.GetValue(firstRow[10], workbook.SharedStringTablePart);
if (kTitle.Trim() != "发行额（亿元）")
{
throw new MyException("表格列数不对");
}
string nTitle = LYJUtil.GetValue(firstRow[13], workbook.SharedStringTablePart);
if (nTitle.Trim() != "缴款日")
{
throw new MyException("表格列数不对");
}
dataList = new List <RZGJPayDtlEntity>();
for (int i = 1; i < rows.Count; i++)
{
RZGJPayDtlEntity data = RZGJPayDtlEntity.getFromCell(rows[i], workbook.SharedStringTablePart);
if (data != null)
{
dataList.Add(data);
}
}
}
}
public void loadData()
{
if (fileName == null)
{
throw new MyException("文件读取失败");
}
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 3)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows[1].Descendants <Cell>().ToList();
string aTitle = LYJUtil.GetValue(LYJUtil.GetCell("B", rows[1].RowIndex, firstRow), workbook.SharedStringTablePart);
if (aTitle.Trim() != "债券简称")
{
throw new MyException("表格列数不对");
}
//string cTitle = LYJUtil.GetValue(LYJUtil.GetCell("F", rows[1].RowIndex, firstRow), workbook.SharedStringTablePart);
//if (cTitle.Trim() != "债券品种")
//{
// throw new MyException("表格列数不对");
//}
//string kTitle = LYJUtil.GetValue(LYJUtil.GetCell("L", rows[1].RowIndex, firstRow), workbook.SharedStringTablePart);
//if (kTitle.Trim() != "发行额（亿元）")
//{
// throw new MyException("表格列数不对");
//}
dataList = new List <SendSuccessEntity>();
for (int i = 2; i < rows.Count; i++)
{
var data = SendSuccessEntity.getFromCell(rows[i], workbook.SharedStringTablePart);
if (data != null)
{
dataList.Add(data);
}
}
}
}
private List <PublishAndCancelFileEntity> readEntity(SpreadsheetDocument doc, WorkbookPart workbook, List <Sheet> sheets, int sheet_index)
{
List <PublishAndCancelFileEntity> entity = new List <PublishAndCancelFileEntity>();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[sheet_index].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("当日sheet数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
if (firstRow.Count < 10)
{
throw new MyException("当日sheet列数不对");
}
string bTitle = LYJUtil.GetValue(firstRow[1], workbook.SharedStringTablePart);
if (bTitle.Trim() != "披露日期")
{
throw new MyException("表格列数不对");
}
//string cTitle = LYJUtil.GetValue(firstRow[2], workbook.SharedStringTablePart);
//if (!(cTitle.Trim().Contains("发行") && cTitle.Trim().Contains("取消发行")))
//{
// throw new MyException("表格列数不对");
//}
//string jTitle = LYJUtil.GetValue(firstRow[9], workbook.SharedStringTablePart);
//if (jTitle.Trim() != "计划发行金额（万元）")
//{
// throw new MyException("表格列数不对");
//}
for (int i = 1; i < rows.Count; i++)
{
var rowdat = PublishAndCancelFileEntity.getFromRow(rows[i], workbook.SharedStringTablePart);
if (rowdat != null)
{
entity.Add(rowdat);
}
}
return(entity);
}
public static CompDebitEntity getFromCell(Row row, SharedStringTablePart t, bool is企业债, string fName)
{
string curCol = "";
try
{
if (row != null)
{
CompDebitEntity entity = new CompDebitEntity();
List <Cell> cells = row.Descendants <Cell>().ToList();
curCol = "A";
Cell cellA = LYJUtil.GetCell("A", row.RowIndex, cells);
if (cellA == null)
{
return(null);
}
entity.code = LYJUtil.GetValue(cellA, t);
if (string.IsNullOrWhiteSpace(entity.code))
{
return(null);
}
curCol = "B";
Cell cellB = LYJUtil.GetCell("B", row.RowIndex, cells);
if (cellB != null)
{
entity.bondName = LYJUtil.GetValue(cellB, t);
}
else
{
return(null);
}
curCol = "E";
string planAmtStr = LYJUtil.GetValue(LYJUtil.GetCell("E", row.RowIndex, cells), t);
if (string.IsNullOrWhiteSpace(planAmtStr))
{
entity.planAmt = 0;
}
else
{
entity.planAmt = decimal.Parse(planAmtStr, System.Globalization.NumberStyles.Float);
}
curCol = "G";
string pubAmtStr = LYJUtil.GetValue(LYJUtil.GetCell("G", row.RowIndex, cells), t);
if (string.IsNullOrWhiteSpace(pubAmtStr))
{
entity.pubAmt = 0;
entity.amt = entity.planAmt;
}
else
{
entity.pubAmt = decimal.Parse(pubAmtStr, System.Globalization.NumberStyles.Float);
entity.amt = entity.pubAmt;
}
if (is企业债)
{
curCol = "D";
}
else
{
curCol = "W";
}
string dateValue = LYJUtil.GetValue(LYJUtil.GetCell(curCol, row.RowIndex, cells), t);
entity.calcDate = DateTime.FromOADate(double.Parse(dateValue));
return(entity);
}
else
{
throw new MyException("存在空行");
}
}
catch (Exception ex)
{
string msg = ex.Message;
if (row != null)
{
msg = "新发行债券(" + fName + ")第" + row.RowIndex + "行" + curCol + "列存在问题";
}
throw new MyException(msg + "\r\n" + ex.Message + ex.StackTrace);
}
}
public static RZGJPayDtlEntity getFromCell(Row row, SharedStringTablePart t)
{
string curCol = "";
try
{
if (row != null)
{
RZGJPayDtlEntity entity = new RZGJPayDtlEntity();
List <Cell> cells = row.Descendants <Cell>().ToList();
curCol = "A";
Cell cellA = LYJUtil.GetCell("A", row.RowIndex, cells);
if (cellA == null)
{
return(null);
}
entity.seqNo = LYJUtil.GetValue(cellA, t);
if (string.IsNullOrWhiteSpace(entity.seqNo))
{
return(null);
}
curCol = "B";
entity.pubCompName = LYJUtil.GetValue(LYJUtil.GetCell("B", row.RowIndex, cells), t);
curCol = "C";
entity.bondName = LYJUtil.GetValue(LYJUtil.GetCell("C", row.RowIndex, cells), t);
curCol = "K";
string amtValue = LYJUtil.GetValue(LYJUtil.GetCell("K", row.RowIndex, cells), t);
// if(amtValue.Contains("E"))
// {
entity.pubAmount = decimal.Parse(amtValue, System.Globalization.NumberStyles.Float);
// }
// else
// {
// entity.pubAmount = decimal.Parse(amtValue);
// }
string dateValue = LYJUtil.GetValue(LYJUtil.GetCell("N", row.RowIndex, cells), t);
entity.payDate = DateTime.FromOADate(double.Parse(dateValue));
curCol = "O";
var tmpC = LYJUtil.GetCell(curCol, row.RowIndex, cells);
if (tmpC == null)
{
entity.hangye_1st = "空";
}
else
{
entity.hangye_1st = LYJUtil.GetValue(tmpC, t);
}
curCol = "Q";
var tmpQ = LYJUtil.GetCell(curCol, row.RowIndex, cells);
if (tmpQ == null)
{
entity.ownnerType = "空";
}
else
{
entity.ownnerType = LYJUtil.GetValue(tmpQ, t);
}
return(entity);
}
else
{
throw new MyException("存在空行");
}
}
catch (Exception ex)
{
string msg = "债务融资工具缴款明细（北金所提供）第" + row.RowIndex + "行" + curCol + "列存在问题";
throw new MyException(msg + ex.Message + ex.StackTrace);
}
}
public void loadData()
{
if (thisYearFileName == null || lastYearFileName == null ||
thisYearFileNameQ == null || lastYearFileNameQ == null)
{
throw new MyException("文件未选择");
}
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(thisYearFileName, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
string aTitle = LYJUtil.GetValue(firstRow[0], workbook.SharedStringTablePart);
if (aTitle.Trim() != "交易代码")
{
throw new MyException("表格列不对");
}
string fTitle = LYJUtil.GetValue(firstRow[4], workbook.SharedStringTablePart);
if (fTitle.Trim() != "计划发行规模(亿)")
{
throw new MyException("表格列不对");
}
string hTitle = LYJUtil.GetValue(firstRow[6], workbook.SharedStringTablePart);
if (hTitle.Trim() != "发行规模(亿)")
{
throw new MyException("表格列不对");
}
thisYearData = new List <CompDebitEntity>();
for (int i = 1; i < rows.Count; i++)
{
var data = CompDebitEntity.getFromCell(rows[i], workbook.SharedStringTablePart, false, "新发行债券今年");
if (data != null)
{
thisYearData.Add(data);
}
else
{
break;
}
}
}
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(lastYearFileName, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
string aTitle = LYJUtil.GetValue(firstRow[0], workbook.SharedStringTablePart);
if (aTitle.Trim() != "交易代码")
{
throw new MyException("表格列不对");
}
string fTitle = LYJUtil.GetValue(firstRow[4], workbook.SharedStringTablePart);
if (fTitle.Trim() != "计划发行规模(亿)")
{
throw new MyException("表格列不对");
}
string hTitle = LYJUtil.GetValue(firstRow[6], workbook.SharedStringTablePart);
if (hTitle.Trim() != "发行规模(亿)")
{
throw new MyException("表格列不对");
}
lastYearData = new List <CompDebitEntity>();
for (int i = 1; i < rows.Count; i++)
{
var data = CompDebitEntity.getFromCell(rows[i], workbook.SharedStringTablePart, false, "新发行债券去年");
if (data != null)
{
lastYearData.Add(data);
}
else
{
break;
}
}
}
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(thisYearFileNameQ, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
string aTitle = LYJUtil.GetValue(firstRow[0], workbook.SharedStringTablePart);
if (aTitle.Trim() != "交易代码")
{
throw new MyException("表格列不对");
}
string fTitle = LYJUtil.GetValue(firstRow[4], workbook.SharedStringTablePart);
if (fTitle.Trim() != "计划发行规模(亿)")
{
throw new MyException("表格列不对");
}
string hTitle = LYJUtil.GetValue(firstRow[6], workbook.SharedStringTablePart);
if (hTitle.Trim() != "发行规模(亿)")
{
throw new MyException("表格列不对");
}
thisYearDataQ = new List <CompDebitEntity>();
for (int i = 1; i < rows.Count; i++)
{
var data = CompDebitEntity.getFromCell(rows[i], workbook.SharedStringTablePart, true, "新发行债券今年企");
if (data != null)
{
thisYearDataQ.Add(data);
}
else
{
break;
}
}
}
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(lastYearFileNameQ, false))
{
WorkbookPart workbook = doc.WorkbookPart;
WorkbookPart wbPart = doc.WorkbookPart;
List <Sheet> sheets = wbPart.Workbook.Descendants <Sheet>().ToList();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets[0].Id);
Worksheet sheet = worksheetPart.Worksheet;
List <Row> rows = sheet.Descendants <Row>().ToList();
if (rows.Count < 2)
{
throw new MyException("表格数据不对");
}
List <Cell> firstRow = rows.FirstOrDefault().Descendants <Cell>().ToList();
string aTitle = LYJUtil.GetValue(firstRow[0], workbook.SharedStringTablePart);
if (aTitle.Trim() != "交易代码")
{
throw new MyException("表格列不对");
}
string fTitle = LYJUtil.GetValue(firstRow[4], workbook.SharedStringTablePart);
if (fTitle.Trim() != "计划发行规模(亿)")
{
throw new MyException("表格列不对");
}
string hTitle = LYJUtil.GetValue(firstRow[6], workbook.SharedStringTablePart);
if (hTitle.Trim() != "发行规模(亿)")
{
throw new MyException("表格列不对");
}
lastYearDataQ = new List <CompDebitEntity>();
for (int i = 1; i < rows.Count; i++)
{
var data = CompDebitEntity.getFromCell(rows[i], workbook.SharedStringTablePart, true, "新发行债券去年企");
if (data != null)
{
lastYearDataQ.Add(data);
}
else
{
break;
}
}
}
}
