C# 将DataTable 导出 Excel

首先 引用  Excel

完整代码

         // 速度最快 
        /// <summary>
        /// 将数据集中的数据导出到EXCEL文件
        /// </summary>
        /// <param name="dataTable">输入数据集</param>
        /// <param name="isShowExcle">是否显示该EXCEL文件</param>
        /// <returns></returns>
        public bool DataTableToExcel(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;

           worksheet.Columns.ColumnWidth = 15;//设置默认列宽

            //生成字段名称 
            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 
            //.net 2.0写法
            //range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
            //.net 4.0写法
            range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];
            //range.NumberFormat = "@";//设置单元格为文本格式
            range.Value2 = objData;
            //.net 2.0写法
            //worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";
            //.net 4.0写法
            //worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]].NumberFormat = "yyyy-m-d h:mm";

            return true;
        }

 

 调用方法

private void btnToExcel_BtnClick(object sender, EventArgs e)
{
            //将dataGridView数据转为DataTable
            DataTable dt = this.dataGridView1.DataSource as DataTable;

            if (dt == null || dt.Rows.Count <= 0)
            {
                MessageBox.Show("没有要导出的数据，请查询后再试！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            clsExcelHelper objExcel=new clsExcelHelper();
 
            bool bolResult = objExcel.DataTableToExcel(dt, true);         

　　　　　　  if (bolResult == true)
            {
                MessageBox.Show("安装工单 导出成功！" + strFileName, "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("安装工单 导出失败！", "消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

}