/// <summary> /// 將資料列陣列貼到指定的Worksheet /// </summary> public static int PasteDataRows(Worksheet sheet, System.Data.DataRow[] rows, PasteDataRowsOptions options) { int writeRow = options.Row; int col = options.Column; //建立總和查詢Dictionary Dictionary <string, decimal> sumDic = null; if (options.IncludeSummary) { sumDic = new Dictionary <string, decimal>(); foreach (string sumCol in options.SummaryColumns) { sumDic.Add(sumCol, 0); } } //逐一將資料列貼上 foreach (DataRow dr in rows) { //object[] objects = dr.ItemArray; //for (int i = 0; i < objects.Length; i++) //{ // sheet.Cells[writeRow, col + i] = objects[i]; //} writeRow = PasteDataRow(sheet, dr, writeRow, col); if (options.IncludeSummary) { foreach (string sumCol in options.SummaryColumns) { sumDic[sumCol] += (Convert.IsDBNull(dr[sumCol]) ? 0 : (decimal)dr[sumCol]); } } //writeRow++; } //產生總和列 if (options.IncludeSummary && rows.Length > 0) { System.Data.DataTable table = rows[0].Table; DataRow sumRow = table.NewRow(); sumRow[0] = options.SummaryPrefix; foreach (string sumCol in options.SummaryColumns) { sumRow[sumCol] = sumDic[sumCol]; } table.Rows.Add(sumRow); writeRow = PasteDataRow(sheet, sumRow, writeRow, col); writeRow++; sumRow.Delete(); } return(writeRow); }
public int PasteSummaryRow(DataRow[] rows, PasteDataRowsOptions options) { int writeRow = options.Row; int col = options.Column; //建立總和查詢Dictionary Dictionary <string, decimal> sumDic = null; sumDic = new Dictionary <string, decimal>(); foreach (string sumCol in options.SummaryColumns) { sumDic.Add(sumCol, 0); } //逐一將資料列貼上 foreach (DataRow dr in rows) { foreach (string sumCol in options.SummaryColumns) { sumDic[sumCol] += (Convert.IsDBNull(dr[sumCol]) ? 0 : Convert.ToDecimal(dr[sumCol])); } } //產生總和列 if (rows.Length > 0) { DataTable table = rows[0].Table.Clone(); DataRow sumRow = table.NewRow(); sumRow[0] = options.SummaryPrefix; foreach (string sumCol in options.SummaryColumns) { if (!string.IsNullOrEmpty(table.Columns[sumCol].Expression)) { table.Columns[sumCol].Expression = string.Empty; } sumRow[sumCol] = sumDic[sumCol]; } foreach (string noSumCol in options.NoSummaryColumns) { table.Columns[noSumCol].Expression = string.Empty; sumRow[noSumCol] = DBNull.Value; } table.Rows.Add(sumRow); writeRow = PasteDataRow(sumRow, writeRow, col); writeRow++; sumRow.Delete(); } return(writeRow); }
/// <summary> /// 將資料列貼到指定的Worksheet /// </summary> public int PasteDataRow(DataRow dataRow, int row, int col, PasteDataRowsOptions options) { object[] objects; if (_reportProfile.Table == null) { objects = dataRow.ItemArray; } else { List <object> objs = new List <object>(); //for (int i = 0; i < dataRow.Table.Columns.Count; i++) //{ // DataColumn column = dataRow.Table.Columns[i]; // if (_reportProfile.Contains(column)) // objs.Add(dataRow[column]); //} foreach (ReportColumn rptCol in _reportProfile.Columns) { objs.Add(dataRow[rptCol.Column.ColumnName]); } objects = objs.ToArray(); } Range writeRange = GetRange(row, col, row, col + objects.Length - 1); if (_reportProfile != null) { bool hoz = _reportProfile.HorizontalGrid; bool ver = _reportProfile.VerticalGrid; SetBorder(writeRange, hoz, hoz, ver, ver); if (ver) { SetBorder(writeRange, XlBordersIndex.xlInsideVertical); } } this[writeRange] = objects; return(row + 1); }
/// <summary> /// 將資料列陣列貼到指定的Worksheet /// </summary> public int PasteDataRows(DataRow[] rows, PasteDataRowsOptions options) { int writeRow = options.Row; int col = options.Column; //建立總和查詢Dictionary Dictionary <string, decimal> sumDic = null; if (options.IncludeSummary) { sumDic = new Dictionary <string, decimal>(); foreach (string sumCol in options.SummaryColumns) { sumDic.Add(sumCol, 0); } } //逐一將資料列貼上 foreach (DataRow dr in rows) { //object[] objects = dr.ItemArray; //for (int i = 0; i < objects.Length; i++) //{ // _sheet.Cells[writeRow, col + i] = objects[i]; //} writeRow = PasteDataRow(dr, writeRow, col, options); if (options.IncludeSummary) { foreach (string sumCol in options.SummaryColumns) { sumDic[sumCol] += (Convert.IsDBNull(dr[sumCol]) ? 0 : Convert.ToDecimal(dr[sumCol])); } } //writeRow++; } if (BeforePasteDataRowSummary != null) { BeforePasteDataRowSummary(this, rows, options, ref writeRow, col, options.PasteDataRowEventArgs); } //產生總和列 if (options.IncludeSummary && rows.Length > 0) { DataTable table = rows[0].Table.Clone(); DataRow sumRow = table.NewRow(); sumRow[0] = options.SummaryPrefix; foreach (string sumCol in options.SummaryColumns) { if (!string.IsNullOrEmpty(table.Columns[sumCol].Expression)) { table.Columns[sumCol].Expression = string.Empty; } sumRow[sumCol] = sumDic[sumCol]; } foreach (string noSumCol in options.NoSummaryColumns) { //if (!string.IsNullOrEmpty(table.Columns[sumCol].Expression)) table.Columns[noSumCol].Expression = string.Empty; sumRow[noSumCol] = DBNull.Value; } table.Rows.Add(sumRow); writeRow = PasteDataRow(sumRow, writeRow, col); writeRow++; sumRow.Delete(); } return(writeRow); }