导出数据到Excel

    但要服务器安装office

  引用COM:Interop.Microsoft.Office.Core.dll和Interop.Microsoft.Office.Interop.Excel.dll

  /// <summary>
    /// 将数据集中的数据导出到EXCEL文件
    /// </summary>
    /// <param name="dataSet">输入数据集</param>
    /// <param name="isShowExcle">是否显示该EXCEL文件</param>
    /// <returns></returns>
    public bool DataSetToExcel(System.Data.DataTable dataTable, bool isShowExcle)
    {
        int rowNumber = dataTable.Rows.Count;//不包括字段名
        int columnNumber = dataTable.Columns.Count;
        int colIndex = 0;

        if (rowNumber == 0)
        {
            return false;
        }

        //建立Excel对象
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //excel.Application.Workbooks.Add(true);
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
        excel.Visible = isShowExcle;
        //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
        Microsoft.Office.Interop.Excel.Range range;

        //生成字段名称
        foreach (DataColumn col in dataTable.Columns)
        {
            colIndex++;
            excel.Cells[1, colIndex] = col.ColumnName;
        }

        object[,] objData = new object[rowNumber, columnNumber];

        for (int r = 0; r < rowNumber; r++)
        {
            for (int c = 0; c < columnNumber; c++)
            {
                objData[r, c] = dataTable.Rows[r][c];
            }
            //Application.DoEvents();
        }

        // 写入Excel
        range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
        range.NumberFormat = "@";//设置单元格为文本格式
        range.Value2 = objData;
        //worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";

        return true;
    }

posted @ 2011-03-31 14:30  Microbar  阅读(514)  评论(0编辑  收藏  举报