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);
            }
        }
Пример #2
0
        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);
                    }
                }
            }
        }
Пример #4
0
        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);
        }
Пример #6
0
        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);
            }
        }
Пример #7
0
        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);
            }
        }
Пример #8
0
        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;
                    }
                }
            }
        }