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; }
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(); } }
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]; } } }