/// <summary> /// 把DataTable导出到EXCEL /// </summary> /// <param name="reportName">报表名称</param> /// <param name="dt">数据源表</param> /// <param name="saveFileName">Excel全路径文件名</param> /// <returns>导出是否成功</returns> public bool ExportExcel(DataTable dt, string saveFileName) { if (dt == null) { _ReturnStatus = -1; _ReturnMessage = "数据集为空!"; return false; } bool fileSaved = false; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlApp == null) { _ReturnStatus = -1; _ReturnMessage = "无法创建Excel对象,可能您的计算机未安装Excel"; return false; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 worksheet.Cells.Font.Size = 10; Microsoft.Office.Interop.Excel.Range range; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; //写入字段 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入数值 for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = "'" + dt.Rows[r][i].ToString(); } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; } range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[dt.Rows.Count + 1, dt.Columns.Count]); range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); if (dt.Rows.Count > 0) { range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; range.NumberFormatLocal = "@"; } if (dt.Columns.Count > 1) { range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; range.NumberFormatLocal = "@"; } //保存文件 if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); fileSaved = true; } catch (Exception ex) { fileSaved = false; _ReturnStatus = -1; _ReturnMessage = "导出文件时出错,文件可能正被打开! " + ex.Message; } } else { fileSaved = false; } //释放Excel对应的对象 if (range != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range); range = null; } if (worksheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); worksheet = null; } if (workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; } if (workbooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); workbooks = null; } xlApp.Application.Workbooks.Close(); xlApp.Quit(); if (xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; } GC.Collect(); return fileSaved; }