/// <summary>
/// 将 DataTable 导出到 Excel
/// </summary>
/// <param name="fApplication"></param>
/// <param name="fWorkBook"></param>
/// <param name="fOption"></param>
private static void ExportToExcel(Excel.Application fApplication, Excel._Workbook fWorkBook, Excel.Worksheet worksheet, DataTableExportOptions fOption)
{
Excel.Range range;
worksheet.Name = fOption.WorkSheetName;
if (fOption.DataTable == null) return;
int rowCount = fOption.DataTable.Rows.Count;
int colCount = fOption.VisibleColumnOptions.Count;
int colIndex = 0;
int rowIndex = 0;
#region " Set Header Values "
object[,] colValues = new object[1, colCount];
foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
{
if (!option.Visible) continue;
colValues[0, colIndex] = option.Caption;
colIndex++;
}
range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, 1));
range.Value2 = colValues;
#endregion
#region " Header Style "
range.Font.Bold = true;
//range.Font.Name = "Georgia";
range.Font.Name = "宋体";
range.Font.Size = 10;
range.RowHeight = 26;
range.EntireColumn.AutoFit();
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//range.Locked = true;
#endregion
#region " Set Row Values "
object[,] rowValues = new object[rowCount, colCount];
rowIndex = 0;
foreach (DataRow dataRow in fOption.DataTable.Rows)
{
colIndex = 0;
foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
{
//rowValues[rowIndex, colIndex] = dataRow[option.ColumnName]; //防止自动将
rowValues[rowIndex, colIndex] ="'"+dataRow[option.ColumnName];
colIndex++;
}
rowIndex++;
}
range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1));
// worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)).Merge(
range.Value2 = rowValues;
#region 测试合并自定的单元格 20120612 CS
//int tempRowCountStart = 1;
//int tempRowCountEnd = 1;
////for (int i = 2; i <=rowCount+1; i++)
//Excel.Range tempRange;
//// int tempi = 0;
//string startValue = ""; ;
//fApplication.DisplayAlerts = false; //取消合并CELL的时候弹出提示框
//foreach (DataRow dr in fOption.DataTable.Rows)
//{
// //tempi = tempi + 1;
// if (dr[0].ToString() == startValue)
// {
// tempRowCountEnd = tempRowCountEnd + 1;
// startValue = dr[0].ToString();
// }
// else
// {
// if (tempRowCountEnd - tempRowCountStart > 0)
// {
// //合并本次付款金额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]);
// tempRange.Merge(tempRange.MergeCells);
// //订单金额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]);
// tempRange.Merge(tempRange.MergeCells);
// //发票总金额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]);
// tempRange.Merge(tempRange.MergeCells);
// //财务最后付款日期
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]);
// tempRange.Merge(tempRange.MergeCells);
// //财务付款总额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]);
// tempRange.Merge(tempRange.MergeCells);
// }
// tempRowCountStart = tempRowCountEnd + 1;
// tempRowCountEnd = tempRowCountStart;
// startValue = dr[0].ToString();
// }
//}
//#region 解决最后全相同的时候，不合并的情况
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]);
//tempRange.Merge(tempRange.MergeCells);
////订单金额
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]);
//tempRange.Merge(tempRange.MergeCells);
////发票总金额
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]);
//tempRange.Merge(tempRange.MergeCells);
////财务最后付款日期
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]);
//tempRange.Merge(tempRange.MergeCells);
////财务付款总额
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]);
//tempRange.Merge(tempRange.MergeCells);
//fApplication.DisplayAlerts = true; //取消警告弹出提示框
//#endregion
#endregion
#region " Row Style "
range.Font.Name = "宋体";
range.Font.Size = 9;
// range.RowHeight = 18;
range.EntireColumn.AutoFit();
//range.Borders.ColorIndex = 2;
#endregion
#endregion
#region " Set Borders "
range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1));
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.ColumnWidth = 12;
range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, rowCount + 1));
range.Borders.Weight = Excel.XlBorderWeight.xlThin;
range.Borders.Color = Color.Black.ToArgb();
#endregion
}
public static void ExportToExcel(DataTableExportOptions fOption, String fFileName)
{
ExportToExcel(new List <DataTableExportOptions>(new DataTableExportOptions[] { fOption }), fFileName);
}
public static void ExportToExcel(DataTableExportOptions fOption, String fFileName)
{
ExportToExcel(new List<DataTableExportOptions>(new DataTableExportOptions[] { fOption }), fFileName);
}
/// <summary>
/// 将 DataTable 导出到 Excel
/// </summary>
/// <param name="fApplication"></param>
/// <param name="fWorkBook"></param>
/// <param name="fOption"></param>
private static void ExportToExcel(Excel.Application fApplication, Excel._Workbook fWorkBook, Excel.Worksheet worksheet, DataTableExportOptions fOption)
{
Excel.Range range;
worksheet.Name = fOption.WorkSheetName;
if (fOption.DataTable == null)
{
return;
}
int rowCount = fOption.DataTable.Rows.Count;
int colCount = fOption.VisibleColumnOptions.Count;
int colIndex = 0;
int rowIndex = 0;
#region " Set Header Values "
object[,] colValues = new object[1, colCount];
foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
{
if (!option.Visible)
{
continue;
}
colValues[0, colIndex] = option.Caption;
colIndex++;
}
range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, 1));
range.Value2 = colValues;
#endregion
#region " Header Style "
range.Font.Bold = true;
//range.Font.Name = "Georgia";
range.Font.Name = "宋体";
range.Font.Size = 10;
range.RowHeight = 26;
range.EntireColumn.AutoFit();
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//range.Locked = true;
#endregion
#region " Set Row Values "
object[,] rowValues = new object[rowCount, colCount];
rowIndex = 0;
foreach (DataRow dataRow in fOption.DataTable.Rows)
{
colIndex = 0;
foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
{
//rowValues[rowIndex, colIndex] = dataRow[option.ColumnName]; //防止自动将
rowValues[rowIndex, colIndex] = "'" + dataRow[option.ColumnName];
colIndex++;
}
rowIndex++;
}
range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1));
// worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1)).Merge(
range.Value2 = rowValues;
#region 测试合并自定的单元格 20120612 CS
//int tempRowCountStart = 1;
//int tempRowCountEnd = 1;
////for (int i = 2; i <=rowCount+1; i++)
//Excel.Range tempRange;
//// int tempi = 0;
//string startValue = ""; ;
//fApplication.DisplayAlerts = false; //取消合并CELL的时候弹出提示框
//foreach (DataRow dr in fOption.DataTable.Rows)
//{
// //tempi = tempi + 1;
// if (dr[0].ToString() == startValue)
// {
// tempRowCountEnd = tempRowCountEnd + 1;
// startValue = dr[0].ToString();
// }
// else
// {
// if (tempRowCountEnd - tempRowCountStart > 0)
// {
// //合并本次付款金额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]);
// tempRange.Merge(tempRange.MergeCells);
// //订单金额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]);
// tempRange.Merge(tempRange.MergeCells);
// //发票总金额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]);
// tempRange.Merge(tempRange.MergeCells);
// //财务最后付款日期
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]);
// tempRange.Merge(tempRange.MergeCells);
// //财务付款总额
// tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]);
// tempRange.Merge(tempRange.MergeCells);
// }
// tempRowCountStart = tempRowCountEnd + 1;
// tempRowCountEnd = tempRowCountStart;
// startValue = dr[0].ToString();
// }
//}
//#region 解决最后全相同的时候，不合并的情况
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 4], worksheet.Cells[tempRowCountEnd, 4]);
//tempRange.Merge(tempRange.MergeCells);
////订单金额
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 9], worksheet.Cells[tempRowCountEnd, 9]);
//tempRange.Merge(tempRange.MergeCells);
////发票总金额
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 11], worksheet.Cells[tempRowCountEnd, 11]);
//tempRange.Merge(tempRange.MergeCells);
////财务最后付款日期
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 29], worksheet.Cells[tempRowCountEnd, 29]);
//tempRange.Merge(tempRange.MergeCells);
////财务付款总额
//tempRange = worksheet.get_Range(worksheet.Cells[tempRowCountStart, 30], worksheet.Cells[tempRowCountEnd, 30]);
//tempRange.Merge(tempRange.MergeCells);
//fApplication.DisplayAlerts = true; //取消警告弹出提示框
//#endregion
#endregion
#region " Row Style "
range.Font.Name = "宋体";
range.Font.Size = 9;
// range.RowHeight = 18;
range.EntireColumn.AutoFit();
//range.Borders.ColorIndex = 2;
#endregion
#endregion
#region " Set Borders "
range = worksheet.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount, rowCount + 1));
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.ColumnWidth = 12;
range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, rowCount + 1));
range.Borders.Weight = Excel.XlBorderWeight.xlThin;
range.Borders.Color = Color.Black.ToArgb();
#endregion
}