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