/// <summary>
/// 将 DataTable 导出到 Excel
/// </summary>
/// <param name="fApplication"></param>
/// <param name="fWorkBook"></param>
/// <param name="fOption"></param>
private static void ExportToExcel(Microsoft.Office.Interop.Excel.Application fApplication, Microsoft.Office.Interop.Excel._Workbook fWorkBook, Microsoft.Office.Interop.Excel.Worksheet worksheet, DataTableExportOptions fOption)
{
Microsoft.Office.Interop.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.Size = 10;
* range.RowHeight = 26;
* range.EntireColumn.AutoFit();
*
#endregion
*/
#region " Set Row Values "
int[,] isVisited = new int[rowCount, colCount];
object[,] rowValues = new object[rowCount, colCount];
int rangeBeginRow = 0;
int rangeBeginCol = 0;
int rangeEndRow = 0;
int rangeEndCol = 0;
object rangeValue = new object();
rowIndex = 0;
foreach (DataRow dataRow in fOption.DataTable.Rows)
{
colIndex = 0;
foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
{
rangeValue = fOption.DataTable.Rows[rowIndex][colIndex];
rangeBeginRow = rowIndex;
rangeBeginCol = colIndex;
rangeEndRow = rowIndex;
rangeEndCol = colIndex;
if (isVisited[rowIndex, colIndex] == 0 && !(option.ColumnName.Equals("3")) && !(option.ColumnName.Equals("6")))
{
// int Tag = 0;
while (isVisited[rangeEndRow, rangeEndCol] == 0 && fOption.DataTable.Rows[rangeEndRow][rangeEndCol].Equals(rangeValue))
{
isVisited[rangeEndRow, rangeEndCol] = 1;
if (rangeEndCol >= colCount - 1)
{
// Tag = 1;
rangeEndCol++;
break;
}
else
{
//Console.WriteLine(fOption.DataTable.Rows[rangeEndRow][rangeEndCol]);
rangeEndCol++;
//Console.WriteLine(fOption.DataTable.Rows[rangeEndRow][rangeEndCol]);
}
}
rangeEndCol--;
// if (Tag == 0)
// {
isVisited[rangeEndRow, rangeEndCol] = 0;
while (isVisited[rangeEndRow, rangeEndCol] == 0 && fOption.DataTable.Rows[rangeEndRow][rangeEndCol].Equals(rangeValue))
{
isVisited[rangeEndRow, rangeEndCol] = 1;
if (rangeEndRow >= rowCount - 1)
{
rangeEndRow++;
break;
}
else
{
rangeEndRow++;
}
}
rangeEndRow--;
// }
//onsole.WriteLine("范围 （{0}，{1}）-（{2}，{3}）",rangeBeginCol + 1, rangeBeginRow + 1,rangeEndCol + 1, rangeEndRow + 1);
}
range = worksheet.get_Range(GetExcelCellName(rangeBeginCol + 1, rangeBeginRow + 1), GetExcelCellName(rangeEndCol + 1, rangeEndRow + 1));
range.MergeCells = true;
range.Value2 = rangeValue;
range.EntireColumn.AutoFit();
colIndex++;
}
rowIndex++;
}
var colorlst1 = colorlist[colorindex];
colorindex++;
for (int i = 0; i < colorlst1.Count - 1; i++)
{
range = worksheet.get_Range(GetExcelCellName(1, colorlst1[i] + 1), GetExcelCellName(6, colorlst1[i + 1]));
if (i == 0)
{
range.Interior.Color = Color.FromArgb(221, 217, 195);
}
if (i == 2)
{
range.Interior.Color = Color.FromArgb(128, 198, 128);
}
if (i == 3)
{
range.Interior.Color = Color.FromArgb(83, 142, 213);
}
if (i == 4)
{
range.Interior.Color = Color.FromArgb(217, 151, 149);
}
if (i == 1)
{
range.Interior.Color = Color.FromArgb(252, 213, 180);
}
if (i == 5)
{
range.Interior.Color = Color.FromArgb(117, 146, 60);
}
range.Font.Size = 14;
}
//foreach (DataRow dataRow in fOption.DataTable.Rows)
//{
// colIndex = 0;
// foreach (DataColumnExportOptions option in fOption.VisibleColumnOptions)
// {
// rowValues[rowIndex, colIndex] = dataRow[option.ColumnName];
// colIndex++;
// }
// rowIndex++;
//}
//range = worksheet.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount, rowCount + 0));
//range.Value2 = rowValues;
//#region " Row Style "
//range.Font.Name = "Georgia";
//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, 1), GetExcelCellName(colCount, rowCount + 0));
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
range.Borders.Color = Color.Black.ToArgb();
range.Font.Name = "黑体";
range.Font.Name = "Times New Roman";
#endregion
//worksheet.Cells[1][1] = "adf";
worksheet.Columns["A:B", Type.Missing].ColumnWidth = 12;
worksheet.Columns["C:D", Type.Missing].ColumnWidth = 12;
worksheet.Columns["E:F", Type.Missing].ColumnWidth = 12;
worksheet.Columns["G:H", Type.Missing].ColumnWidth = 12;
//设置上标
//range = worksheet.get_Range(GetExcelCellName(6, 8), GetExcelCellName(6, 8));
//range.Characters[3, 1].Font.Superscript = true;
}
public static void ExportToExcel(DataTableExportOptions fOption, String fFileName)
{
ExportToExcel(new List <DataTableExportOptions>(new DataTableExportOptions[] { fOption }), fFileName);
}