//导出Excel文件 public static void ExportToExcel(SqlConnection conn, string[] SqlString, string PathName, bool State) { Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application(); if (Excel == null) { config.ShowDialog("无法创建Excel对象,可能您的机子未安装Excel"); return; } Workbooks Excel_Books = Excel.Workbooks; Workbook Excel_Book = Excel_Books.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Worksheet Excel_Sheet = (Worksheet)Excel_Book.Worksheets[1]; int RowsNum, colsNum=0, TotalRowsNum = 0; decimal[] R = new decimal[100]; decimal[] sum = new decimal[100]; //开始导出数据 for (int m = 0; m < SqlString.Length; m++) { if (SqlString[m] == null) break; SqlDataAdapter da = new SqlDataAdapter(SqlString[m], conn); System.Data.DataTable dt = new System.Data.DataTable(); DataSet ds = new DataSet(); da.Fill(ds); dt = ds.Tables[0]; RowsNum = dt.Rows.Count; colsNum = dt.Columns.Count; //导出表头信息 if (RowsNum == 0) continue; for (int i = 0; i < colsNum; i++) { Excel_Sheet.Cells[TotalRowsNum+1, i + 1] = dt.Columns[i].ColumnName; } string ExcelString = "A" + (TotalRowsNum + 1) + ":" + Convert.ToChar('A' + colsNum - 1) + (TotalRowsNum + 1); Excel_Sheet.get_Range(ExcelString, Type.Missing).Font.Bold = true; Excel_Sheet.get_Range(ExcelString, Type.Missing).Interior.Color = ColorTranslator.ToOle(ColorTranslator.FromWin32((256 * 256 * 223) + (256 * 223) + 223)); Excel_Sheet.get_Range(ExcelString, Type.Missing).Borders.ColorIndex = 1; //把数据保存在Excel中 for (int i = 0; i < RowsNum; i++) { for (int j = 0; j < colsNum; j++) { if (dt.Rows[i][j].GetType() == typeof(string)) { Excel.Cells[i + 2 + TotalRowsNum, j + 1] = "'" + dt.Rows[i][j].ToString(); } else { Excel.Cells[i + 2 + TotalRowsNum, j + 1] = dt.Rows[i][j].ToString(); if (dt.Rows[i][j].GetType() == typeof(double) || dt.Rows[i][j].GetType() == typeof(decimal)) { R[j + TotalRowsNum] += Convert.ToDecimal(dt.Rows[i][j]); sum[j] += Convert.ToDecimal(dt.Rows[i][j]); } } } } //汇总 if (State) { int CurrentRow = TotalRowsNum + RowsNum + 2; Excel_Sheet.Cells[CurrentRow, 1] = "合计"; for (int i = 0; i < colsNum; i++) { if (R[i + TotalRowsNum] != 0) Excel_Sheet.Cells[CurrentRow, i + 1] = R[i + TotalRowsNum]; } Excel_Sheet.get_Range("A" + CurrentRow + ":" + Convert.ToChar('A' + colsNum - 1) + CurrentRow, Type.Missing).Font.Color = ColorTranslator.ToOle(Color.White); Excel_Sheet.get_Range("A" + CurrentRow + ":" + Convert.ToChar('A' + colsNum - 1) + CurrentRow, Type.Missing).Font.Bold = true; Excel_Sheet.get_Range("A" + CurrentRow + ":" + Convert.ToChar('A' + colsNum - 1) + CurrentRow, Type.Missing).Interior.Color = ColorTranslator.ToOle(ColorTranslator.FromWin32((256 * 256 *206) + (256 *155) + 99)); } TotalRowsNum += dt.Rows.Count+3; } Excel_Sheet.Cells[TotalRowsNum+1, 1] = "总计"; for (int i = 0; i < colsNum; i++) { if (sum[i] != 0) Excel_Sheet.Cells[TotalRowsNum + 1, i + 1] = sum[i]; ; } Excel_Sheet.get_Range("A" + (TotalRowsNum + 1) + ":" + Convert.ToChar('A' + colsNum - 1) + (TotalRowsNum + 1), Type.Missing).Font.Color = ColorTranslator.ToOle(Color.White); Excel_Sheet.get_Range("A" + (TotalRowsNum +1) + ":" + Convert.ToChar('A' + colsNum - 1) + (TotalRowsNum + 1), Type.Missing).Font.Bold = true; Excel_Sheet.get_Range("A" + (TotalRowsNum + 1) + ":" + Convert.ToChar('A' + colsNum - 1) + (TotalRowsNum + 1), Type.Missing).Interior.Color = ColorTranslator.ToOle(ColorTranslator.FromWin32((256 * 256 * 10) + (256 * 10) + 160)); Excel_Sheet.Columns.EntireColumn.AutoFit(); try { Excel_Book.Saved = true; Excel_Book.SaveCopyAs(PathName); } catch (Exception ex) { config.ShowDialog(ex.Message); return; } Excel.Visible = false; Excel.Quit(); Excel = null; config.ShowDialog("生成Excel文件成功"); }