/// <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);
}
