コード例 #1
0
ファイル: X_FXDKTB_D.cs プロジェクト: weihongji/YuLin
        private void PopulateSheet(string filePath, TargetTableSheet sheet)
        {
            Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
            ExcelHelper.InitSheet(filePath, sheet);

            var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
            Logger.Debug("Openning connction to " + filePath);
            oleConn.Open();
            var sql = string.Format("EXEC spX_FXDKTB_D '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
            var dao = new SqlDbHelper();
            Logger.Debug("Running " + sql);
            var reader = dao.ExecuteReader(sql);

            int rowCount = 0;
            while (reader.Read()) {
                rowCount++;
                sql = GetInsertSql(reader, sheet);
                try {
                    OleDbCommand cmd = new OleDbCommand(sql, oleConn);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex) {
                    Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
                    Logger.Error(ex);
                    oleConn.Close();
                    throw ex;
                }
            }
            oleConn.Close();
            Logger.DebugFormat("{0} records exported.", rowCount);

            ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
        }
コード例 #2
0
        private string GetSheetSuffix(TargetTableSheet sheet)
        {
            var suffix = "";

            switch (sheet.Index)
            {
            case 1:
                suffix = "FYJ";
                break;

            case 2:
                suffix = "BLDK";
                break;

            case 3:
                suffix = "YQ";
                break;

            case 4:
                suffix = "ZQX";
                break;

            case 5:
                suffix = "GZDK";
                break;

            default:
                Logger.Error("Unknown sheet index: " + sheet.Index.ToString());
                throw new Exception("Unknown sheet index: " + sheet.Index.ToString());
            }
            return(suffix);
        }
コード例 #3
0
ファイル: X_CSHSX_M.cs プロジェクト: weihongji/YuLin
        private string GetInsertSql(SqlDataReader reader, TargetTableSheet sheet)
        {
            var fields = new StringBuilder();
            var values = new StringBuilder();

            fields.AppendFormat("[{0}]", sheet.Columns[0].Name);
            values.Append(DataUtility.GetSqlValue(reader, 0));
            for (int i = 1; i < sheet.Columns.Count; i++)
            {
                fields.AppendFormat(", [{0}]", sheet.Columns[i].Name);
                if (i == 8)
                {
                    values.Append(", '" + GetDates(reader[i].ToString()) + "'");
                }
                else
                {
                    values.Append(", " + DataUtility.GetSqlValue(reader, i));
                }
            }

            var sql = new StringBuilder();

            sql.AppendLine(string.Format("INSERT INTO [{0}$] ({1})", sheet.Name, fields.ToString()));
            sql.AppendLine(string.Format("SELECT {0}", values.ToString()));
            return(sql.ToString());
        }
コード例 #4
0
ファイル: LoanRiskPerMonth.cs プロジェクト: weihongji/YuLin
 private string GetSheetSuffix(TargetTableSheet sheet)
 {
     var suffix = "";
     switch (sheet.Index) {
         case 1:
             suffix = "FYJ";
             break;
         case 2:
             suffix = "BLDK";
             break;
         case 3:
             suffix = "YQ";
             break;
         case 4:
             suffix = "ZQX";
             break;
         case 5:
             suffix = "GZDK";
             break;
         default:
             Logger.Error("Unknown sheet index: " + sheet.Index.ToString());
             throw new Exception("Unknown sheet index: " + sheet.Index.ToString());
     }
     return suffix;
 }
コード例 #5
0
ファイル: X_BLDKJC_X.cs プロジェクト: weihongji/YuLin
 private string PopulateSheet3(string filePath, TargetTableSheet sheet)
 {
     var result = "";
     var dao = new SqlDbHelper();
     var sql = string.Format("EXEC spX_BLDKJC_X_3 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
     Logger.Debug("Running " + sql);
     var table = dao.ExecuteDataTable(sql);
     result = ExcelHelper.PopulateX_BLDKJC_X_3(filePath, sheet, this.AsOfDate, table);
     return result;
 }
コード例 #6
0
        private string PopulateSheetVS(string filePath, TargetTableSheet sheet)
        {
            var result = "";
            var dao    = new SqlDbHelper();
            var sql    = string.Format("EXEC spX_WJFL_M_vs '{0}'", this.AsOfDate.ToString("yyyyMMdd"));

            Logger.Debug("Running " + sql);
            var table = dao.ExecuteDataTable(sql);

            result = ExcelHelper.PopulateX_WJFL_M_VS(filePath, sheet, this.AsOfDate, table);
            return(result);
        }
コード例 #7
0
ファイル: C_DQDKQK_M.cs プロジェクト: weihongji/YuLin
        private string GetInsertSql(SqlDataReader reader, TargetTableSheet sheet, List<string> cols)
        {
            var fields = new StringBuilder();
            var values = new StringBuilder();
            fields.AppendFormat("[{0}]", cols[0]);
            values.Append(DataUtility.GetSqlValue(reader, 0));
            for (int i = 1; i < cols.Count; i++) {
                fields.AppendFormat(", [{0}]", cols[i]);
                values.Append(", " + DataUtility.GetSqlValue(reader, i));
            }

            var sql = new StringBuilder();
            sql.AppendLine(string.Format("INSERT INTO [{0}$] ({1})", sheet.Name, fields.ToString()));
            sql.AppendLine(string.Format("SELECT {0}", values.ToString()));
            return sql.ToString();
        }
コード例 #8
0
        private void PopulateSheet(string filePath, TargetTableSheet sheet)
        {
            Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));

            var sql = string.Format("EXEC spC_DQDKQK_M '{0}', '{1}', '{2}'", this.AsOfDate.ToString("yyyyMMdd")
                                    , sheet.Index == 1 ? "ImportPublic" : "ImportPrivate"
                                    , sheet.Index == 1 ? PublicColumns : PrivateColumns);
            var dao = new SqlDbHelper();

            Logger.Debug("Running: " + sql);
            var reader      = dao.ExecuteReader(sql);
            var columnNames = new List <string>();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                columnNames.Add(reader.GetName(i));
            }
            int rowCount = 0;

            ExcelHelper.InitSheet(filePath, sheet, columnNames);
            Logger.Debug("Openning connction to " + filePath);
            var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");

            oleConn.Open();
            while (reader.Read())
            {
                rowCount++;
                sql = GetInsertSql(reader, sheet, columnNames);
                try {
                    OleDbCommand cmd = new OleDbCommand(sql, oleConn);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex) {
                    Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
                    Logger.Error(ex);
                    oleConn.Close();
                    throw ex;
                }
            }
            oleConn.Close();
            Logger.DebugFormat("{0} records exported.", rowCount);

            ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
        }
コード例 #9
0
        private string GetInsertSql(SqlDataReader reader, TargetTableSheet sheet, List <string> cols)
        {
            var fields = new StringBuilder();
            var values = new StringBuilder();

            fields.AppendFormat("[{0}]", cols[0]);
            values.Append(DataUtility.GetSqlValue(reader, 0));
            for (int i = 1; i < cols.Count; i++)
            {
                fields.AppendFormat(", [{0}]", cols[i]);
                values.Append(", " + DataUtility.GetSqlValue(reader, i));
            }

            var sql = new StringBuilder();

            sql.AppendLine(string.Format("INSERT INTO [{0}$] ({1})", sheet.Name, fields.ToString()));
            sql.AppendLine(string.Format("SELECT {0}", values.ToString()));
            return(sql.ToString());
        }
コード例 #10
0
ファイル: X_CSHSX_M.cs プロジェクト: weihongji/YuLin
        private string PopulateSheet2(string filePath, TargetTableSheet sheet)
        {
            var result = "";
            var dao    = new SqlDbHelper();
            var sql    = string.Format("EXEC spX_CSHSX_M_2 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));

            Logger.Debug("Running " + sql);
            var table = dao.ExecuteDataTable(sql);

            if (table != null)
            {
                result = ExcelHelper.PopulateX_CSHSX_M_2(filePath, sheet, this.AsOfDate, table);
            }
            else
            {
                result = "Procedure returned zero rows";
            }
            return(result);
        }
コード例 #11
0
ファイル: X_CSHSX_M.cs プロジェクト: weihongji/YuLin
        private string GetInsertSql(SqlDataReader reader, TargetTableSheet sheet)
        {
            var fields = new StringBuilder();
            var values = new StringBuilder();
            fields.AppendFormat("[{0}]", sheet.Columns[0].Name);
            values.Append(DataUtility.GetSqlValue(reader, 0));
            for (int i = 1; i < sheet.Columns.Count; i++) {
                fields.AppendFormat(", [{0}]", sheet.Columns[i].Name);
                if (i == 8) {
                    values.Append(", '" + GetDates(reader[i].ToString()) + "'");
                }
                else {
                    values.Append(", " + DataUtility.GetSqlValue(reader, i));
                }
            }

            var sql = new StringBuilder();
            sql.AppendLine(string.Format("INSERT INTO [{0}$] ({1})", sheet.Name, fields.ToString()));
            sql.AppendLine(string.Format("SELECT {0}", values.ToString()));
            return sql.ToString();
        }
コード例 #12
0
ファイル: C_DQDKQK_M.cs プロジェクト: weihongji/YuLin
        private void PopulateSheet(string filePath, TargetTableSheet sheet)
        {
            Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));

            var sql = string.Format("EXEC spC_DQDKQK_M '{0}', '{1}', '{2}'", this.AsOfDate.ToString("yyyyMMdd")
                , sheet.Index == 1 ? "ImportPublic" : "ImportPrivate"
                , sheet.Index == 1 ? PublicColumns : PrivateColumns);
            var dao = new SqlDbHelper();
            Logger.Debug("Running: " + sql);
            var reader = dao.ExecuteReader(sql);
            var columnNames = new List<string>();
            for (int i = 0; i < reader.FieldCount; i++) {
                columnNames.Add(reader.GetName(i));
            }
            int rowCount = 0;

            ExcelHelper.InitSheet(filePath, sheet, columnNames);
            Logger.Debug("Openning connction to " + filePath);
            var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");

            oleConn.Open();
            while (reader.Read()) {
                rowCount++;
                sql = GetInsertSql(reader, sheet, columnNames);
                try {
                    OleDbCommand cmd = new OleDbCommand(sql, oleConn);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex) {
                    Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
                    Logger.Error(ex);
                    oleConn.Close();
                    throw ex;
                }
            }
            oleConn.Close();
            Logger.DebugFormat("{0} records exported.", rowCount);

            ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
        }
コード例 #13
0
        private void PopulateSheet(string filePath, TargetTableSheet sheet)
        {
            Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));

            var columnNames = this.Columns.Select(x => x.MappingName).ToList();

            ExcelHelper.InitSheet(filePath, sheet, columnNames);
            Logger.Debug("Openning connction to " + filePath);
            var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");

            oleConn.Open();

            var sql = string.Format("EXEC {0} '{1}', '{2}', '{3}', '{4}'", this.SPName, GetSheetSuffix(sheet), this.AsOfDate.ToString("yyyyMMdd"), this.AsOfDate2.ToString("yyyyMMdd"), DbColumnNames);
            var dao = new SqlDbHelper();

            Logger.Debug("Running: " + sql);
            var reader   = dao.ExecuteReader(sql);
            int rowCount = 0;

            while (reader.Read())
            {
                rowCount++;
                sql = GetInsertSql(reader, sheet, columnNames);
                try {
                    OleDbCommand cmd = new OleDbCommand(sql, oleConn);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex) {
                    Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
                    Logger.Error(ex);
                    oleConn.Close();
                    throw ex;
                }
            }
            oleConn.Close();
            Logger.DebugFormat("{0} records exported.", rowCount);

            ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate, this.AsOfDate2);
        }
コード例 #14
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateX_CSHSX_M_1(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
        {
            logger.Debug("Populating X_CSHSX_M_1");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[sheet.Index];

                int excelRow = 5;
                for (int i = 0; i < dataTable.Rows.Count; i++) {
                    for (int j = 0; j < 18; j++) {
                        ((Range)theSheet.Cells[excelRow, 3 + j]).Value2 = dataTable.Rows[i][2 + j];
                    }
                    excelRow++;
                    if (excelRow == 6) { // 1.按贷款担保方式
                        excelRow = 7;
                    }
                    else if (excelRow == 8) { // 1.2保证贷款 --留给excel公式计算
                        i++;
                        excelRow = 9;
                    }
                    else if (excelRow == 11) { // 2.按贷款逾期情况
                        excelRow = 12;
                    }
                    else if (excelRow == 15) { // 3.按贷款对象
                        excelRow = 16;
                    }
                }

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #15
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateX_CSHSX_M_4(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
        {
            logger.Debug("Populating X_CSHSX_M_4");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[sheet.Index];

                int rowStartAt = 7;
                for (int i = 0; i < dataTable.Rows.Count; i++) {
                    int excelColumn = 2;
                    for (int j = 0; j < dataTable.Columns.Count; j++) {
                        if (j == 6) { // 借款开始日
                            ((Range)theSheet.Cells[rowStartAt + i, excelColumn]).Value2 = string.Format("{0}至{1}", ((DateTime)dataTable.Rows[i][j]).ToString("yyyy年MM月dd日"), ((DateTime)dataTable.Rows[i][j + 1]).ToString("yyyy年MM月dd日"));
                            j++; // 跳过借款到期日
                        }
                        else {
                            ((Range)theSheet.Cells[rowStartAt + i, excelColumn]).Value2 = dataTable.Rows[i][j];
                        }
                        excelColumn++;
                        if (excelColumn == 8) { // 首付比例
                            excelColumn = 10;
                        }
                        else if (excelColumn == 12) { // 担保情况, L-Q列
                            excelColumn = 18;
                        }
                    }
                }

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #16
0
ファイル: X_CSHSX_M.cs プロジェクト: weihongji/YuLin
 private string PopulateSheet1(string filePath, TargetTableSheet sheet)
 {
     var result = "";
     var dao = new SqlDbHelper();
     var sql = string.Format("EXEC spX_CSHSX_M_1 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
     Logger.Debug("Running " + sql);
     var table = dao.ExecuteDataTable(sql);
     if (table != null) {
         result = ExcelHelper.PopulateX_CSHSX_M_1(filePath, sheet, this.AsOfDate, table);
     }
     else {
         result = "Procedure returned zero rows";
     }
     return result;
 }
コード例 #17
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
 public static bool SubstituteReportHeader(Worksheet theSheet, TargetTableSheet sheet, DateTime asOfDate)
 {
     return SubstituteReportHeader(theSheet, sheet, asOfDate, new DateTime(1900, 1, 1));
 }
コード例 #18
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateSF6301_141(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable, System.Data.DataTable dataTable2)
        {
            logger.Debug("Populating SF6301_141");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[1];

                int rowsBeforeStart = 5;
                int r = 0;
                for (int i = 1; i <= 21; i++) {
                    if (i == 2 || i == 3 || i == 8 || i == 13 || i == 17 || i == 19) {
                        continue;
                    }
                    for (int j = 1; j <= 7; j++) {
                        if (dataTable.Rows[r][j + 1] == DBNull.Value) {
                            ((Range)theSheet.Cells[rowsBeforeStart + i, 2 + j]).Value2 = "0.00";
                        }
                        else {
                            ((Range)theSheet.Cells[rowsBeforeStart + i, 2 + j]).Value2 = dataTable.Rows[r][j + 1];
                        }
                    }
                    r++;
                }

                // 授信户数
                for (int j = 1; j <= 7; j++) {
                    ((Range)theSheet.Cells[rowsBeforeStart + 25, 2 + j]).Value2 = dataTable2.Rows[0]["Count" + j.ToString()];
                    ((Range)theSheet.Cells[rowsBeforeStart + 26, 2 + j]).Value2 = dataTable2.Rows[0]["Count" + j.ToString()];
                }

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #19
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
 public static void FinalizeSheet(string filePath, TargetTableSheet sheet, int dataRowCount, DateTime asOfDate)
 {
     FinalizeSheet(filePath, sheet, dataRowCount, asOfDate, new DateTime(1900, 1, 1));
 }
コード例 #20
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
 public static string PopulateGF1304_081(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
 {
     return PopulateGF1301_081(filePath, sheet, asOfDate, dataTable);
 }
コード例 #21
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateGF1403_111(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
        {
            logger.Debug("Populating GF1403_111");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[1];

                int rowStartAt = 8;
                for (int i = 0; i < dataTable.Rows.Count && i < 10; i++) {
                    ((Range)theSheet.Cells[rowStartAt + i, 3]).Value2 = dataTable.Rows[i]["CustomerName"];
                    ((Range)theSheet.Cells[rowStartAt + i, 4]).Value2 = dataTable.Rows[i]["IdCode"];
                    ((Range)theSheet.Cells[rowStartAt + i, 5]).Value2 = dataTable.Rows[i]["Balance"];
                    ((Range)theSheet.Cells[rowStartAt + i, 6]).Value2 = dataTable.Rows[i]["Balance"];
                    ((Range)theSheet.Cells[rowStartAt + i, 8]).Value2 = dataTable.Rows[i]["ZC"];
                    ((Range)theSheet.Cells[rowStartAt + i, 9]).Value2 = dataTable.Rows[i]["GZ"];
                    ((Range)theSheet.Cells[rowStartAt + i, 10]).Value2 = dataTable.Rows[i]["CJ"];
                    ((Range)theSheet.Cells[rowStartAt + i, 11]).Value2 = dataTable.Rows[i]["KY"];
                    ((Range)theSheet.Cells[rowStartAt + i, 12]).Value2 = dataTable.Rows[i]["SS"];
                }

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #22
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateGF1101_121(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
        {
            logger.Debug("Populating GF1101_121");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[1];

                int excelRow = 10;
                for (int i = 0; i < dataTable.Rows.Count; i++) {
                    ((Range)theSheet.Cells[excelRow, 5]).Value2 = dataTable.Rows[i]["ZC"];
                    ((Range)theSheet.Cells[excelRow, 6]).Value2 = dataTable.Rows[i]["GZ"];
                    ((Range)theSheet.Cells[excelRow, 8]).Value2 = dataTable.Rows[i]["CJ"];
                    ((Range)theSheet.Cells[excelRow, 9]).Value2 = dataTable.Rows[i]["KY"];
                    ((Range)theSheet.Cells[excelRow, 10]).Value2 = dataTable.Rows[i]["SS"];
                    if (excelRow == 29) { // 2.1 - 2.20 end
                        excelRow = 31;
                    }
                    else if (excelRow == 35) { // 2.22买断式转贴现 end
                        excelRow = 38;
                    }
                    else if (excelRow == 38) { // 7. 个人经营性贷款 end
                        excelRow = 40;
                    }
                    else {
                        excelRow++;
                    }
                }

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #23
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateGF1103_121(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
        {
            logger.Debug("Populating GF1103_121");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[1];

                int excelRow = 8;
                int lastDirectionId = 0;
                for (int i = 0; i < 97; i++) {
                    if ((int)dataTable.Rows[i]["DirectionId"] > 0 && (int)dataTable.Rows[i]["DirectionId"] != lastDirectionId) {
                        excelRow += 1; // 此行用公式计算, 跳过
                        lastDirectionId = (int)dataTable.Rows[i]["DirectionId"];
                    }
                    ((Range)theSheet.Cells[excelRow, 5]).Value2 = dataTable.Rows[i]["ZC"];
                    ((Range)theSheet.Cells[excelRow, 6]).Value2 = dataTable.Rows[i]["GZ"];
                    ((Range)theSheet.Cells[excelRow, 8]).Value2 = dataTable.Rows[i]["CJ"];
                    ((Range)theSheet.Cells[excelRow, 9]).Value2 = dataTable.Rows[i]["KY"];
                    ((Range)theSheet.Cells[excelRow, 10]).Value2 = dataTable.Rows[i]["SS"];
                    if (excelRow == 123) { // 20.1国际组织 end
                        excelRow = 126;
                    }
                    else {
                        excelRow++;
                    }
                }

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #24
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
 public static void InitSheet(string filePath, TargetTableSheet sheet)
 {
     InitSheet(filePath, sheet, null);
 }
コード例 #25
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static void FinalizeSheet(string filePath, TargetTableSheet sheet, int dataRowCount, DateTime asOfDate, DateTime asOfDate2)
        {
            int sheetIndex = sheet.Index;

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
            theExcelApp.DisplayAlerts = false; // Without this line, the template sheet cannot be deleted.
            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            Worksheet theSheetTemplate = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;

                theSheet = (Worksheet)theExcelBook.Sheets[sheetIndex];
                theSheetTemplate = (Worksheet)theExcelBook.Sheets[sheetIndex + 1];

                bool dummyHeader = false;
                int dummyHeaderRows = 0;
                if (sheet.TableId == (int)XEnum.ReportType.X_FXDKTB_D
                        || sheet.TableId == (int)XEnum.ReportType.X_FXDKBH_D
                        || sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3
                    ) {
                    dummyHeader = true;
                    dummyHeaderRows = 1;
                }

                //Remove data rows left by template
                Range oRange;
                var sampleRows = (sheet.FooterStartRow - 1) - sheet.RowsBeforeHeader - 1; // (sheet.FooterStartRow - 1) is the last sample row if there is
                if (sampleRows > 0) {
                    int removeRowFrom = dummyHeader ? 1 : 2;
                    int removeRowTo = dummyHeader ? removeRowFrom + sampleRows : removeRowFrom + sampleRows - 1;
                    oRange = (Range)theSheet.get_Range(string.Format("{0}:{1}", removeRowFrom, removeRowTo));
                    oRange.Delete();
                }

                //Header
                if (sheet.RowsBeforeHeader > 0) {
                    theSheetTemplate.Activate();
                    oRange = (Range)theSheetTemplate.get_Range("1:" + sheet.RowsBeforeHeader.ToString());
                    oRange.Select();
                    oRange.Copy();

                    theSheet.Activate();
                    oRange = theSheet.get_Range("1:1");
                    oRange.Select();
                    oRange.Insert();
                }

                var columnCount = sheet.Columns.Count;
                if (sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3) {
                    columnCount = 27;
                }

                //Totals
                int dataRowFrom = sheet.RowsBeforeHeader + 2 - dummyHeaderRows;
                int footerRowFrom = dataRowFrom + dataRowCount;
                int footerRowTo = footerRowFrom + (sheet.FooterEndRow - sheet.FooterStartRow);

                // Copy the footer back
                if (sheet.TableId == (int)XEnum.ReportType.X_FXDKTB_D
                        || sheet.TableId == (int)XEnum.ReportType.X_ZXQYZJXQ_S
                        || sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3
                    ) {
                    if (sheet.FooterStartRow > 0) {
                        theSheetTemplate.Activate();
                        oRange = (Range)theSheetTemplate.get_Range(string.Format("{0}:{1}", sheet.FooterStartRow, sheet.FooterEndRow));
                        oRange.Select();
                        oRange.Copy();

                        theSheet.Activate();
                        oRange = theSheet.get_Range(string.Format("{0}:{0}", footerRowFrom));
                        oRange.Select();
                        oRange.Insert();
                    }
                    if (sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3) {
                        oRange = theSheet.get_Range("A7:A7");
                        oRange.Select();
                    }
                }

                if (sheet.TableId == (int)XEnum.ReportType.X_WJFL_M) {
                    ((Range)theSheet.Cells[footerRowFrom, 1]).Value2 = "合计";
                    ((Range)theSheet.Cells[footerRowFrom, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    if (dataRowCount > 0) {
                        if (sheet.Name.Equals("逾期")) {
                            ((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = string.Format("=SUM(C{0}:C{1})", dataRowFrom, footerRowFrom - 1);
                            ((Range)theSheet.Cells[footerRowFrom, 4]).Value2 = string.Format("=SUM(D{0}:D{1})", dataRowFrom, footerRowFrom - 1);
                            ((Range)theSheet.Cells[footerRowFrom, 6]).Value2 = string.Format("=SUM(F{0}:F{1})", dataRowFrom, footerRowFrom - 1);
                        }
                        else {
                            ((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = string.Format("=SUM(C{0}:C{1})", dataRowFrom, footerRowFrom - 1);
                            ((Range)theSheet.Cells[footerRowFrom, 5]).Value2 = string.Format("=SUM(E{0}:E{1})", dataRowFrom, footerRowFrom - 1);
                        }
                    }
                }
                else if (sheet.TableId == (int)XEnum.ReportType.F_HYB_M) {
                    ((Range)theSheet.Cells[footerRowFrom, 1]).Value2 = "合计";
                    ((Range)theSheet.Cells[footerRowFrom, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    if (dataRowCount > 0) {
                        ((Range)theSheet.Cells[footerRowFrom, 6]).Value2 = string.Format("=SUM(F{0}:F{1})", dataRowFrom, footerRowFrom - 1);
                    }
                }
                else if (sheet.TableId == (int)XEnum.ReportType.X_FXDKTB_D || sheet.TableId == (int)XEnum.ReportType.X_FXDKBH_D) {
                    if (dataRowCount > 0) {
                        for (int i = 2; i <= sheet.Columns.Count; i++) {
                            if (i == 5 || i == 8 || i == 11 || i == 15) {
                                ((Range)theSheet.Cells[footerRowFrom, i]).Value2 = string.Format("={0}{1}/B{1}", GetColumnCharacters(i - 1), footerRowFrom);
                            }
                            else {
                                ((Range)theSheet.Cells[footerRowFrom, i]).Value2 = string.Format("=SUM({0}{1}:{0}{2})", GetColumnCharacters(i), dataRowFrom, footerRowFrom - 1);
                            }
                        }
                    }
                }

                if (sheet.TableId == (int)XEnum.ReportType.X_WJFL_M || sheet.TableId == (int)XEnum.ReportType.F_HYB_M) {
                    //绘制数据部分的表格线
                    int dataRowStartIndex = sheet.RowsBeforeHeader + 1 + 1;
                    Range dataRange = theSheet.Range[theSheet.Cells[dataRowStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
                    dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                    dataRange.Font.Size = 10;
                }
                else if (sheet.TableId == (int)XEnum.ReportType.X_FXDKTB_D) {
                    int dataRowStartIndex = sheet.RowsBeforeHeader + 1 + 1;
                    Range dataRange = theSheet.Range[theSheet.Cells[dataRowStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
                    dataRange.RowHeight = 21;
                }
                else if (sheet.TableId == (int)XEnum.ReportType.X_FXDKBH_D) {
                    Range dataRange = theSheet.Range[theSheet.Cells[dataRowFrom, 1], theSheet.Cells[footerRowFrom, columnCount]];
                    dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                    dataRange.RowHeight = 24;
                    // amount & numbers
                    dataRange = theSheet.Range[theSheet.Cells[dataRowFrom, 2], theSheet.Cells[footerRowFrom, columnCount]];
                    dataRange.HorizontalAlignment = XlHAlign.xlHAlignRight;

                    dataRange = theSheet.Range[theSheet.Cells[footerRowFrom, 1], theSheet.Cells[footerRowFrom, columnCount]];
                    dataRange.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192);

                    ((Range)theSheet.Cells[footerRowFrom, 1]).Value2 = "总计";
                    ((Range)theSheet.Cells[footerRowFrom, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                }
                else if (sheet.TableId == (int)XEnum.ReportType.C_DQDKQK_M) {
                    columnCount = 2;
                    while (true) {
                        if (string.IsNullOrEmpty(((Range)theSheet.Cells[sheet.RowsBeforeHeader + 1, columnCount]).Value2)) {
                            break;
                        }
                        else {
                            columnCount++;
                        }
                    }
                    columnCount--;
                    int headerStartIndex = sheet.RowsBeforeHeader + 1;
                    Range dataRange = theSheet.Range[theSheet.Cells[headerStartIndex, 1], theSheet.Cells[headerStartIndex, columnCount]];
                    dataRange.Interior.Color = System.Drawing.Color.FromArgb(204, 204, 255);
                    dataRange = theSheet.Range[theSheet.Cells[headerStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
                    dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                    dataRange.Font.Size = 10;

                    ((Range)theSheet.Cells[footerRowFrom, 2]).Value2 = "合计";
                    ((Range)theSheet.Cells[footerRowFrom, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    if (dataRowCount > 0) {
                        if (sheet.Name.IndexOf("对公") >= 0) {
                            ((Range)theSheet.Cells[footerRowFrom, 6]).Value2 = string.Format("=SUM(F{0}:F{1})", dataRowFrom, footerRowFrom - 1);
                        }
                        else if (sheet.Name.IndexOf("个人") >= 0) {
                            ((Range)theSheet.Cells[footerRowFrom, 8]).Value2 = string.Format("=SUM(H{0}:H{1})", dataRowFrom, footerRowFrom - 1);
                        }
                    }
                }
                else if (sheet.TableId == (int)XEnum.ReportType.C_XZDKMX_D || sheet.TableId == (int)XEnum.ReportType.C_JQDKMX_D) {
                    columnCount = 1;
                    while (true) {
                        if (string.IsNullOrEmpty(((Range)theSheet.Cells[sheet.RowsBeforeHeader + 1, columnCount]).Value2)) {
                            break;
                        }
                        else {
                            columnCount++;
                        }
                    }
                    columnCount--;
                    int headerStartIndex = sheet.RowsBeforeHeader + 1;
                    // Column Header
                    Range dataRange = theSheet.Range[theSheet.Cells[headerStartIndex, 1], theSheet.Cells[headerStartIndex, columnCount]];
                    dataRange.Font.Bold = true;
                    // Data Rows & Footer
                    dataRange = theSheet.Range[theSheet.Cells[headerStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
                    dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                    dataRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    dataRange.Font.Size = 10;

                    // Footer
                    dataRange = theSheet.Range[theSheet.Cells[footerRowFrom, 1], theSheet.Cells[footerRowFrom, columnCount]];
                    dataRange.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192);

                    ((Range)theSheet.Cells[footerRowFrom, 1]).Value2 = "总计";
                    ((Range)theSheet.Cells[footerRowFrom, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    if (sheet.TableId == (int)XEnum.ReportType.C_XZDKMX_D) {
                        if (dataRowCount > 0) {
                            ((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = string.Format("=SUM(C{0}:C{1})", dataRowFrom, footerRowFrom - 1);
                            ((Range)theSheet.Cells[footerRowFrom, 9]).Value2 = string.Format("=SUM(I{0}:I{1})", dataRowFrom, footerRowFrom - 1);
                        }
                        else {
                            ((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = "0.00";
                            ((Range)theSheet.Cells[footerRowFrom, 9]).Value2 = "0.00";
                        }
                    }
                    else if (sheet.TableId == (int)XEnum.ReportType.C_JQDKMX_D) {
                        if (dataRowCount > 0) {
                            ((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = string.Format("=SUM(C{0}:C{1})", dataRowFrom, footerRowFrom - 1);
                            ((Range)theSheet.Cells[footerRowFrom, 4]).Value2 = string.Format("=SUM(D{0}:D{1})", dataRowFrom, footerRowFrom - 1);
                            ((Range)theSheet.Cells[footerRowFrom, 10]).Value2 = string.Format("=SUM(J{0}:J{1})", dataRowFrom, footerRowFrom - 1);
                            ((Range)theSheet.Cells[footerRowFrom, 11]).Value2 = string.Format("=SUM(K{0}:K{1})", dataRowFrom, footerRowFrom - 1);
                        }
                        else {
                            ((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = "0.00";
                            ((Range)theSheet.Cells[footerRowFrom, 4]).Value2 = "0.00";
                            ((Range)theSheet.Cells[footerRowFrom, 10]).Value2 = "0.00";
                            ((Range)theSheet.Cells[footerRowFrom, 11]).Value2 = "0.00";
                        }
                    }
                }
                else if (sheet.TableId == (int)XEnum.ReportType.X_ZXQYZJXQ_S) {
                    int dataRowStartIndex = sheet.RowsBeforeHeader + 1 + 1;
                    Range dataRange = theSheet.Range[theSheet.Cells[dataRowStartIndex, 1], theSheet.Cells[footerRowTo, columnCount]];
                    dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                    dataRange.Font.Size = 10;

                    var lastYear = asOfDate.Year - 1;
                    var lastSeason = asOfDate.Month / 3;
                    if (lastSeason == 0) { // as_of_date = 2016-1-31
                        lastYear--;
                        lastSeason = 4;
                    }
                    var season = "";
                    if (lastSeason == 1 || lastSeason == 3) {
                        season = string.Format("{0}年度第{1}季度", lastYear + 1, lastSeason);
                    }
                    else {
                        season = string.Format("{0}年{1}半年发放余额", lastYear + 1, (lastSeason == 2 ? "上" : "下"));
                    }
                    ((Range)theSheet.Cells[sheet.RowsBeforeHeader + 1, columnCount - 1]).Value2 = string.Format("{0}年末发放余额", lastYear);
                    ((Range)theSheet.Cells[sheet.RowsBeforeHeader + 1, columnCount]).Value2 = season;

                    ((Range)theSheet.Cells[1, 1]).Select();
                }
                else if (sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3) {
                    //绘制数据部分的表格线
                    int dataRowStartIndex = sheet.RowsBeforeHeader + 1 + 1;
                    Range dataRange = theSheet.Range[theSheet.Cells[dataRowStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
                    dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                }

                SubstituteReportHeader(theSheet, sheet, asOfDate, asOfDate2);

                theSheetTemplate.Delete();

                theExcelBook.Save();
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theSheetTemplate != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheetTemplate);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
        }
コード例 #26
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateGF0102_161(string filePath, TargetTableSheet sheet, DateTime asOfDate, decimal total, decimal guanZhu, decimal ciJi, decimal keYi, decimal sunShi, decimal overdue90)
        {
            logger.Debug("Populating GF0102_161");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[1];
                ((Range)theSheet.Cells[6, 3]).Value2 = total;
                ((Range)theSheet.Cells[9, 3]).Value2 = guanZhu;
                ((Range)theSheet.Cells[11, 3]).Value2 = ciJi;
                ((Range)theSheet.Cells[12, 3]).Value2 = keYi;
                ((Range)theSheet.Cells[13, 3]).Value2 = sunShi;
                ((Range)theSheet.Cells[15, 3]).Value2 = overdue90;

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #27
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateX_BLDKJC_X_1(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
        {
            logger.Debug("Populating X_BLDKJC_X_1");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[sheet.Index];

                int excelRow = 7;
                for (int i = 0; i < dataTable.Rows.Count; i++) {
                    for (int j = 0; j < 4; j++) {
                        if (excelRow >= 24) {
                            ((Range)theSheet.Cells[excelRow, 2 + j]).Value2 = ((decimal)dataTable.Rows[i][2 + j] / 100);
                        }
                        else {
                            ((Range)theSheet.Cells[excelRow, 2 + j]).Value2 = dataTable.Rows[i][2 + j];
                        }
                    }
                    if (excelRow == 8) { // 关注类贷款余额 starts
                        excelRow = 10;
                    }
                    else if (excelRow == 11) { // 法人类不良贷款余额 starts
                        excelRow = 17;
                    }
                    else if (excelRow == 19) { // 个人类不良贷款余额 starts
                        excelRow = 21;
                    }
                    else {
                        excelRow++;
                    }
                }

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #28
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateGF1900_151(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
        {
            logger.Debug("Populating GF1900_151");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[1];

                int rowStartAt = 7;
                for (int i = 0; i < dataTable.Rows.Count; i++) {
                    for (int j = 0; j < 5; j++) {
                        ((Range)theSheet.Cells[rowStartAt + i, 14 + j]).Value2 = dataTable.Rows[i][2 + j];
                    }
                }

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #29
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateSF6401_141(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable, System.Data.DataTable dataTable2)
        {
            logger.Debug("Populating SF6401_141");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[1];

                // 1.1 - 1.21
                int rowStartAt = 7;
                for (int i = 0; i < 21; i++) {
                    for (int j = 1; j <= 6; j++) {
                        ((Range)theSheet.Cells[rowStartAt + i, 2 + j]).Value2 = dataTable.Rows[i]["Balance" + j.ToString()];
                    }
                    ((Range)theSheet.Cells[rowStartAt + i, 9]).Value2 = dataTable.Rows[i]["Balance6"];
                }
                // 2. 贷款当年累计发放额
                for (int j = 1; j <= 6; j++) {
                    ((Range)theSheet.Cells[29, 2 + j]).Value2 = dataTable.Rows[21]["Balance" + j.ToString()];
                }
                ((Range)theSheet.Cells[29, 9]).Value2 = dataTable.Rows[21]["Balance6"];

                // 3. 贷款当年累计发放户数
                // 4. 贷款当年累计申请户数
                for (int j = 1; j <= 6; j++) {
                    ((Range)theSheet.Cells[30, 2 + j]).Value2 = dataTable2.Rows[0]["Count" + j.ToString()];
                    ((Range)theSheet.Cells[31, 2 + j]).Value2 = dataTable2.Rows[0]["Count" + j.ToString()];
                }
                ((Range)theSheet.Cells[30, 9]).Value2 = dataTable2.Rows[0]["Count6"];
                ((Range)theSheet.Cells[31, 9]).Value2 = dataTable2.Rows[0]["Count6"];

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #30
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static bool SubstituteReportHeader(Worksheet theSheet, TargetTableSheet sheet, DateTime asOfDate, DateTime asOfDate2)
        {
            var changed = false;
            var columnCount = sheet.Columns.Count;
            for (int i = 1; i <= sheet.RowsBeforeHeader; i++) {
                for (int j = 1; j <= columnCount; j++) {
                    var cell = ((Range)theSheet.Cells[i, j]);
                    string val = "";
                    try {
                        val = cell.Value2;
                    }
                    catch { }
                    if (!string.IsNullOrWhiteSpace(val)) {
                        if (asOfDate2.Year > 2001) {
                            if (val.IndexOf("year2") >= 0) {
                                val = val.Replace("year2", asOfDate2.Year.ToString());
                            }
                            if (val.IndexOf("month2") >= 0) {
                                val = val.Replace("month2", asOfDate2.Month.ToString());
                            }
                            if (val.IndexOf("day2") >= 0) {
                                val = val.Replace("day2", asOfDate2.Day.ToString());
                            }
                        }

                        if (val.IndexOf("year") >= 0) {
                            val = val.Replace("year", asOfDate.Year.ToString());
                        }
                        if (val.IndexOf("month") >= 0) {
                            val = val.Replace("month", asOfDate.Month.ToString());
                        }
                        if (val.IndexOf("day") >= 0) {
                            val = val.Replace("day", asOfDate.Day.ToString());
                        }
                        if (!val.Equals((string)cell.Value2)) {
                            cell.Value2 = val;
                            changed = true;
                        }
                    }
                }
            }
            return changed;
        }
コード例 #31
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static string PopulateGF0107_141(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
        {
            logger.Debug("Populating GF0107_141");

            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();

            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;
                theSheet = (Worksheet)theExcelBook.Sheets[1];

                // 2.1 - 2.20
                int rowStartAt = 10;
                for (int i = 0; i < 20; i++) {
                    ((Range)theSheet.Cells[rowStartAt + i, 3]).Value2 = dataTable.Rows[i]["Balance"];
                }
                // 2.21 个人贷款(不含个人经营性贷款)
                rowStartAt = 31;
                for (int i = 0; i < 4; i++) {
                    ((Range)theSheet.Cells[rowStartAt + i, 3]).Value2 = dataTable.Rows[i + 20]["Balance"];
                }
                // 4. 个人经营性贷款
                ((Range)theSheet.Cells[38, 3]).Value2 = dataTable.Rows[24]["Balance"];

                SubstituteReportHeader(theSheet, sheet, asOfDate);

                theExcelBook.Save();
                logger.Debug("Population done");
            }
            catch (Exception ex) {
                logger.Error(ex);
                throw;
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
            return string.Empty;
        }
コード例 #32
0
ファイル: ExcelHelper.cs プロジェクト: weihongji/YuLin
        public static void InitSheet(string filePath, TargetTableSheet sheet, List<string> columnNames)
        {
            if (sheet.RowsBeforeHeader == 0 && sheet.FooterStartRow == 0) {
                return;
            }
            Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
            Workbook theExcelBook = null;
            Worksheet theSheet = null;
            Worksheet theSheetTemplate = null;
            bool excelOpened = false;
            try {
                theExcelBook = theExcelApp.Workbooks.Open(filePath);
                excelOpened = true;

                theSheet = (Worksheet)theExcelBook.Sheets[sheet.Index];
                if (!theSheet.Name.Equals(sheet.Name)) {
                    theSheet.Name = sheet.Name;
                }
                theSheet.Copy(Type.Missing, theSheet);
                ((Worksheet)theExcelBook.Sheets[sheet.Index + 1]).Name = theSheet.Name + "Template";
                Range range = null;

                // Remove footer
                if (sheet.FooterStartRow > 0) {
                    range = (Range)theSheet.get_Range(string.Format("{0}:{1}", sheet.FooterStartRow, sheet.FooterEndRow));
                    range.Delete();
                }

                // Make the column header row as the first row
                if (sheet.RowsBeforeHeader > 0) {
                    range = (Range)theSheet.get_Range("1:" + sheet.RowsBeforeHeader.ToString());
                    range.Delete();
                }
                if (columnNames != null) {
                    for (int i = 1; i <= columnNames.Count; i++) {
                        theSheet.Cells[1, i] = columnNames[i - 1];
                    }
                }
                theExcelBook.Save();
            }
            finally {
                if (excelOpened) {
                    theExcelBook.Close(false, null, null);
                }
                theExcelApp.Quit();
                if (theSheet != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
                }
                if (theSheetTemplate != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheetTemplate);
                }
                if (theExcelBook != null) {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
                GC.Collect();
            }
        }