Example #1
0
        public override string FileExtract(string extractFilePath, FileInfoType fileInfoType, string businessDate, string tableName, string fileImportFormatDirectory)
        {
            string result = string.Empty;

            DCEngineExcelParam dcEngineExcelParam = new DCEngineExcelParam();
            dcEngineExcelParam.ContentRowIndexStart = 1;
            dcEngineExcelParam.EndRowExceptCount = 1;
            dcEngineExcelParam.ContentColumnIndexStart = 0;
            dcEngineExcelParam.ContentColumnIndexEnd = 5;
            dcEngineExcelParam.RowExceptSQL = string.Empty;
            dcEngineExcelParam.SheetName = "0";
            dcEngineExcelParam.FileInfoType = fileInfoType;

            DCExcelProcess dcExcelProcess = new DCExcelProcess();
            dcExcelProcess.ExtractFileToDataBase(dcEngineExcelParam, businessDate, extractFilePath, tableName, ref result);

            return result;
        }
Example #2
0
        public void ExtractFileToDataBase(DCEngineExcelParam dcEngineExcelParam, string businessDate, string extractFilePath, string tableName, ref string result)
        {
            try
            {
                DataTable dtStore = TableGetSchema(tableName);
                FileImportToDataTable(dcEngineExcelParam, businessDate, extractFilePath, ref dtStore);
                DataTableSaveToDataBase(ref dtStore, tableName);

                if (!string.IsNullOrEmpty(dcEngineExcelParam.RowExceptSQL.Trim()))
                {
                    ExecuteSQLAfterImport(tableName, dcEngineExcelParam.RowExceptSQL);
                }
            }
            catch (Exception ex)
            {
                result = ex.ToString();
            }
        }
Example #3
0
        private void FileImportToDataTable(DCEngineExcelParam dcEngineExcelParam, string businessDate, string extractFilePath, ref DataTable dtStore)
        {
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + extractFilePath + "; Extended Properties='Excel 12.0;HDR=No; IMEX=1'";

            DataTable dtContent = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                int sheetIndex;
                if (int.TryParse(dcEngineExcelParam.SheetName, out sheetIndex))
                {
                    conn.Open();
                    DataTable dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    for (int index = 0; index < dtSchema.Rows.Count; index++)
                    {
                        if (index == sheetIndex)
                        {
                            dcEngineExcelParam.SheetName = dtSchema.Rows[index]["TABLE_NAME"].ToString();
                            break;
                        }
                    }
                }

                OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + dcEngineExcelParam.SheetName + "]", connString);
                da.Fill(dtContent);
            }

            if (dtContent == null || dtContent.Rows.Count == 0)
            {
                string errorInfo = string.Format("Excel文件:{0}, SheetName:{1}读取不到数据", extractFilePath, dcEngineExcelParam.SheetName);
                throw new Exception(errorInfo);
            }

            if (dcEngineExcelParam.ContentRowIndexStart < 0)
            {
                string errorInfo = string.Format("Excel文件:{0}, SheetName:{1}, ContentRowIndexStart:{2} 小于0", extractFilePath, dcEngineExcelParam.SheetName, dcEngineExcelParam.ContentRowIndexStart);
                throw new Exception(errorInfo);
            }

            if (dcEngineExcelParam.ContentRowIndexStart > dtContent.Rows.Count - 1)
            {
                dcEngineExcelParam.ContentRowIndexStart = dtContent.Rows.Count - 1;
            }

            if (dtContent.Rows.Count - dcEngineExcelParam.EndRowExceptCount - 1 < dcEngineExcelParam.ContentRowIndexStart)
            {
                string errorInfo = string.Format("Excel文件:{0}, SheetName:{1}, dtContent.Rows.Count:{2} - EndRowExceptCount:{3} -1 < ContentRowIndexStart:{4}", extractFilePath, dcEngineExcelParam.SheetName, dtContent.Rows.Count, dcEngineExcelParam.EndRowExceptCount, dcEngineExcelParam.ContentRowIndexStart);
                throw new Exception(errorInfo);
            }

            if (dcEngineExcelParam.ContentColumnIndexStart < 0 || dcEngineExcelParam.ContentColumnIndexStart > dtContent.Columns.Count - 1)
            {
                string errorInfo = string.Format("Excel文件:{0}, SheetName:{1}, ContentColumnIndexStart:{2} 大于 数据表列最大索引值:{3} 或小于0", extractFilePath, dcEngineExcelParam.SheetName, dcEngineExcelParam.ContentColumnIndexStart, dtContent.Columns.Count - 1);
                throw new Exception(errorInfo);
            }

            if (dcEngineExcelParam.ContentColumnIndexEnd < 0)
            {
                string errorInfo = string.Format("Excel文件:{0}, SheetName:{1}, ContentColumnIndexEnd:{2} 小于0", extractFilePath, dcEngineExcelParam.SheetName, dcEngineExcelParam.ContentColumnIndexEnd);
                throw new Exception(errorInfo);
            }

            if (dcEngineExcelParam.ContentColumnIndexEnd > dtContent.Columns.Count - 1)
            {
                dcEngineExcelParam.ContentColumnIndexEnd = dtContent.Columns.Count - 1;
            }

            for (int indexRow = dcEngineExcelParam.ContentRowIndexStart; indexRow < dtContent.Rows.Count - dcEngineExcelParam.EndRowExceptCount; indexRow++)
            {
                int descColumnIndex = 0;
                DataRow descNewRow = dtStore.NewRow();
                descNewRow[descColumnIndex++] = Guid.NewGuid();

                if (dcEngineExcelParam.FileInfoType != FileInfoType.BillBase && dcEngineExcelParam.FileInfoType != FileInfoType.NoMasterBase)
                {
                    descNewRow[descColumnIndex++] = businessDate;
                }

                dtStore.Rows.Add(descNewRow);

                for (int indexCol = dcEngineExcelParam.ContentColumnIndexStart; indexCol <= dcEngineExcelParam.ContentColumnIndexEnd; indexCol++)
                {
                    descNewRow[descColumnIndex++] = dtContent.Rows[indexRow][indexCol];
                }
            }
        }