Exemplo n.º 1
0
        /// <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;
        }
Exemplo n.º 2
0
 public static void ExportToExcel(DataTableExportOptions fOption, String fFileName)
 {
     ExportToExcel(new List <DataTableExportOptions>(new DataTableExportOptions[] { fOption }), fFileName);
 }