public class ExcelEditor { public void ExportToExcel(string filename, System.Data.DataTable dt, string excelname) { if (dt == null) return; string saveFileName = ""; bool fileSaved = false; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = filename; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) return; //被点了取消 Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Excel.Range range; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; worksheet.Cells[1, 6] = excelname; worksheet.get_Range("a2", "a2").ColumnWidth = 9; worksheet.get_Range("b2", "b2").ColumnWidth = 9; worksheet.get_Range("c2", "c2").ColumnWidth = 13; worksheet.get_Range("d2", "d2").ColumnWidth = 13; worksheet.get_Range("e2", "e2").ColumnWidth = 9; worksheet.get_Range("f2", "f2").ColumnWidth = 13; worksheet.get_Range("l2", "l2").ColumnWidth = 40; worksheet.get_Range("o2", "o2").ColumnWidth = 10; //写入字段 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName; range = (Excel.Range)worksheet.Cells[2, i + 1]; range.Interior.ColorIndex = 15; range.Font.Bold = true; } //写入数值 //this.CaptionVisible = true; for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 3, i + 1] = "'" + dt.Rows[r][i]; } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; //this.CaptionText = "正在导出数据["+ percent.ToString("0.00") +"%]..."; System.Windows.Forms.Application.DoEvents(); } //this.CaptionVisible = false; //this.CaptionText = oldCaption; range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]); range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); fileSaved = true; } catch (Exception ex) { fileSaved = false; MessageBox.Show("导出文件时出错,文件可能正被打开!/n" + ex.Message); } } else { fileSaved = false; } xlApp.Quit(); GC.Collect();//强行销毁 MessageBox.Show("报表导出成功", "系统提示"); //if (fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); } }