C#手记 DataSet 或DataTable导出至Excel

来源：互联网发布：php建站免费空间编辑：程序博客网时间：2024/06/05 21:52

在网上搜得，自己修改了一下拿来用了，记录一下，方便其他朋友用

/*gridView1 是form中的控件，数据源就是myTable

public void ExportDataToExcel( DataTable myTable)
        {
            try
            {
                Excel.Application xlApp = new Excel.ApplicationClass();

int rowIndex;
int colIndex;

rowIndex = 2;
colIndex = 0;

Excel.Workbook xlBook = xlApp.Workbooks.Add(true);

             //之前用的DataGrid控件，如果有设定样式可以放开这里
               /* if (gridView1.PrintStyles .Count > 0)
                {
                    Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, grid.TableStyles[0].GridColumnStyles.Count]);
                    range.MergeCells = true;
                    // xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
                    xlApp.ActiveCell.Font.Size = 18;
                    xlApp.ActiveCell.Font.Bold = true;

                    foreach (DataGridColumnStyle colu in grid.TableStyles[0].GridColumnStyles)
                    {
                        colIndex = colIndex + 1;
                        xlApp.Cells[2, colIndex] = colu.HeaderText;
                    }

int k = 0;
//得到的表所有行,赋值给单元格

                    for (int row = 0; row < myTable.Rows.Count; row++)
                    {
                        rowIndex = rowIndex + 1;
                        colIndex = 0;
                        for (int col = 0; col < grid.TableStyles[0].GridColumnStyles.Count; col++)
                        {
                            colIndex = colIndex + 1;
                            xlApp.Cells[rowIndex, colIndex] = myTable.Rows[row][col].ToString();

                            k++;
                            if (k >= 500)
                            {
                                Application.DoEvents();  //此举为了提高性能
                                k = 0; //归零
                            }
                        }
                    }
                }
                else
                {*/
                    Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, myTable.Columns.Count]);
                    range.MergeCells = true;
                   // xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
                    xlApp.ActiveCell.Font.Size = 18;
                    xlApp.ActiveCell.Font.Bold = true;

//将表中的栏位名称填到Excel的第一行

                    foreach (GridColumn Col in gridView1.Columns)
                    {
                        colIndex = colIndex + 1;
                        xlApp.Cells[2, colIndex] = Col.FieldName;
                    }

//得到的表所有行,赋值给单元格

                    for (int row = 0; row < myTable.Rows.Count; row++)
                    {
                        rowIndex = rowIndex + 1;
                        colIndex = 0;
                        for (int col = 0; col < myTable.Columns.Count; col++)
                        {
                            colIndex = colIndex + 1;
                            xlApp.Cells[rowIndex, colIndex] = myTable.Rows[row][col].ToString();
                        }
                    }
              // }

xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;

                xlApp.Cells.EntireColumn.AutoFit();
                xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter;
                xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter;

                xlApp.Visible = true;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }

效果大体如下：

导出：2千笔数据大约15秒导完：

---------------------------------

广告：俺的淘宝小店，赚点零花钱，请大家支持：http://lovehealthylife.taobao.com