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