public static void BuildFillParameterFromConfig(FillParameter fillParameter, Dictionary<string, object> paramList) { if(paramList.ContainsKey("sheetIndex")) { fillParameter.SheetIndex = Convert.ToInt32(paramList["sheetIndex"]); } if(paramList.ContainsKey("rowOffset")) { fillParameter.RowOffset = Convert.ToInt32(paramList["rowOffset"]); } if (paramList.ContainsKey("columnOffset")) { fillParameter.ColumnOffset = Convert.ToInt32(paramList["columnOffset"]); } if (paramList.ContainsKey("columnCount")) { fillParameter.ColumnNameArray = new string[Convert.ToInt32(paramList["columnCount"])]; } if (paramList.ContainsKey("tableName")) { fillParameter.TableName = paramList["tableName"].ToString(); } if (paramList.ContainsKey("batchSize")) { fillParameter.BatchSize = Convert.ToInt32(paramList["batchSize"]); } if (paramList.ContainsKey("customizeBatch")) { fillParameter.CustomizeBatch = Convert.ToBoolean(paramList["customizeBatch"]); } if (paramList.ContainsKey("isNeedQueryData")) { fillParameter.IsNeedQueryData = Convert.ToBoolean(paramList["isNeedQueryData"]); } }
protected override FillParameter[] InitializeFillParameters() { Dictionary<string, object> ParamList = XmlHelper.GetFillParams("RequestReport", "paramCount"); int paramCount = Convert.ToInt32(ParamList["value"]); FillParameter[] fillParameters = new FillParameter[paramCount]; for (int paramIndex = 1; paramIndex <= paramCount; paramIndex++) { if (paramIndex == 1) { if (dateType == DateType.Fixed) { fillParameters[paramIndex - 1] = new FillParameterBuilder( this.ReportDate, "param" + paramIndex.ToString()).ToFillParameter1(); } else { fillParameters[paramIndex - 1] = new FillParameterBuilder( this.StartDate, this.EndDate, "param" + paramIndex.ToString()).ToFillParameter1(); } } else { if (dateType == DateType.Fixed) { fillParameters[paramIndex - 1] = new FillParameterBuilder( this.ReportDate, "param" + paramIndex.ToString()).ToFillParameter2(); } else { fillParameters[paramIndex - 1] = new FillParameterBuilder( this.StartDate, this.EndDate, "param" + paramIndex.ToString()).ToFillParameter2(); } } } return fillParameters; }
public static Worksheet FillSheetRange(Workbook book, FillParameter fillParameter) { if (fillParameter.IsNeedQueryData) { //parameter validation check if (fillParameter.RowHandlerDelegate == null) { throw new Exception("RowHandlerDelegate must be initialized."); } if (fillParameter.QueryBuilderDelegate == null) { throw new Exception("QueryBuilderDelegate must be initialized."); } } Worksheet sheet = book.Sheets[fillParameter.SheetIndex]; sheet.Activate(); if (fillParameter.IsNeedQueryData) { using (SqlConnection connection = ConnectionFactory.GetConnection(fillParameter.connectionString) ) { Logger.Debug("Start Query Data"); SqlCommand command = fillParameter.QueryBuilderDelegate(connection, fillParameter.TableName, fillParameter.ColumnNameArray, fillParameter.WhereClause); Logger.Debug(string.Format("Query String : {0}", command.CommandText)); connection.Open(); SqlDataReader odr = null; // TODO: 暂时不支持存储过程 //if (command.CommandType == CommandType.StoredProcedure) //{ // SqlParameter resultList = new SqlParameter("P_LIST", OracleType.Cursor); // resultList.Direction = System.Data.ParameterDirection.Output; // command.Parameters.Add(resultList); // command.ExecuteNonQuery(); // odr = (SqlDataReader) resultList.Value; //} //else //{ odr = command.ExecuteReader(CommandBehavior.CloseConnection); //} Logger.Debug("End Query Data"); var startColumn = SheetDataAdapter.CalExcelColumnName(fillParameter.ColumnOffset); var endColumn = SheetDataAdapter.CalExcelColumnName(fillParameter.ColumnOffset + fillParameter.ColumnNameArray.Count() - 1); var columnsCount = fillParameter.ColumnNameArray.Count(); var cachedResult = new object[fillParameter.BatchSize,columnsCount]; var batch = 0; var recordIndex = 0; var rowIndex = 0; Logger.Debug("Start Fill Data"); if (!fillParameter.CustomizeBatch) //把是否自定义处理判断提到读取数据之前,否则会丢失第一条数据 { try { while (odr.Read()) { fillParameter.RowHandlerDelegate(cachedResult, odr, recordIndex%fillParameter.BatchSize, recordIndex, fillParameter.ColumnNameArray); recordIndex++; batch = recordIndex/fillParameter.BatchSize; rowIndex = fillParameter.PerRecordPerRow ? recordIndex : (recordIndex/columnsCount) + 1; if (rowIndex%fillParameter.BatchSize == 0) { var tempRange = sheet.Range[ startColumn + (fillParameter.RowOffset + 1 + fillParameter.BatchSize*(batch - 1)), endColumn + (fillParameter.BatchSize*batch + fillParameter.RowOffset)]; tempRange.Value2 = cachedResult; cachedResult = new object[fillParameter.BatchSize,columnsCount]; } //row index 1 based instead of 0 } } catch { Logger.Info("RecordIndex:" + recordIndex); throw; } } else { fillParameter.RowHandlerDelegate(cachedResult, odr, recordIndex%fillParameter.BatchSize, recordIndex, fillParameter.ColumnNameArray); } if (!fillParameter.CustomizeBatch) { if (rowIndex%fillParameter.BatchSize != 0) { var finalRange = sheet.Range[ startColumn + (fillParameter.RowOffset + 1 + fillParameter.BatchSize*batch), endColumn + ((fillParameter.BatchSize*batch + fillParameter.RowOffset) + rowIndex%fillParameter.BatchSize)]; var shrinkedArray = new object[rowIndex%fillParameter.BatchSize,columnsCount]; for (int i = 0; i < shrinkedArray.GetLength(0); i++) { for (int j = 0; j < shrinkedArray.GetLength(1); j++) { shrinkedArray[i, j] = cachedResult[i, j]; } } finalRange.Value2 = shrinkedArray; } } else { var finalRange = CalRange(sheet, fillParameter.RowOffset, fillParameter.ColumnOffset, fillParameter.RowOffset + fillParameter.BatchSize - 1, fillParameter.ColumnOffset + fillParameter.ColumnNameArray.Count() - 1); finalRange.Value2 = cachedResult; } Logger.Debug("End Fill Data"); } } if (fillParameter.UpdateSheetDelegate != null) { Logger.Debug("Start Update Sheet"); fillParameter.UpdateSheetDelegate(sheet); Logger.Debug("End Update Sheet"); } //if (rowIndex <= 0) //{ // throw new NoResultException("No data retrieved from [" + fillParameter.WhereClause+"]"); //} return sheet; }