通过DataTable导出Excel

通过传入不同的数据集来控制Excel中sheet的数目，此例依照两个为例，仅供大家参考:

#region 導出Excel
        private void outPutExcel(System.Data.DataTable dt, System.Data.DataTable dt2)
        {
            if (dt == null) return;
            Excel.Application xlApp = new Excel.Application();
            if (xlApp == null)
            {
                //clsLog.m_CreateErrorLog("无法创建Excel对象，可能计算机未安装Excel", "", "");
                return;
            }
            //創建Excel對象
            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            //Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            Excel.Worksheet worksheet = null;
            for (int sheetcount = 0; sheetcount < 2; sheetcount++)//循环根据自己需要的sheet的数目这里是两个
            {
                if (worksheet == null)
                {
                    worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
                }
                else
                {
                    worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, worksheet, 1, Type.Missing);
                }
                Excel.Range range = null;
                if (sheetcount == 0)
                {
                    long totalCount = dt.Rows.Count;
                    long rowRead = 0;
                    float percent = 0;
                    worksheet.Name = "明細資料一";//第一个sheet在Excel中显示的名称
                    ////写入标题
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                        range = (Excel.Range)worksheet.Cells[1, i + 1];
                        range.Interior.ColorIndex = 15;//背景颜色
                        range.Font.Bold = true;//粗体
                        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
                        //加边框
                        range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
                        range.ColumnWidth = 4.63;//设置列宽
                        range.EntireColumn.AutoFit();//自动调整列宽
                        //r1.EntireRow.AutoFit();//自动调整行高
                    }

                    //写入内容
                    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];
                            range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
                            range.Font.Size = 9;//字体大小
                            //加边框
                            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
                            range.EntireColumn.AutoFit();//自动调整列宽
                        }
                        rowRead++;
                        percent = ((float)(100 * rowRead)) / totalCount;
                    }
                    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
                    if (dt.Columns.Count > 1)
                    {
                        range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
                    }
                }
                else if (sheetcount == 1)
                {
                    long totalCount = dt2.Rows.Count;
                    long rowRead = 0;
                    float percent = 0;
                    worksheet.Name = "建議解決方案";
                    ////写入标题
                    for (int i = 0; i < dt2.Columns.Count; i++)
                    {
                        worksheet.Cells[1, i + 1] = dt2.Columns[i].ColumnName;
                        range = (Excel.Range)worksheet.Cells[1, i + 1];
                        range.Interior.ColorIndex = 15;//背景颜色
                        range.Font.Bold = true;//粗体
                        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
                        //加边框
                        range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
                        range.ColumnWidth = 14.63;//设置列宽
                        range.EntireColumn.AutoFit();//自动调整列宽
                        //r1.EntireRow.AutoFit();//自动调整行高
                    }
                    //写入内容
                    for (int r = 0; r < dt2.Rows.Count; r++)
                    {
                        for (int i = 0; i < dt2.Columns.Count; i++)
                        {
                            worksheet.Cells[r + 2, i + 1] = dt2.Rows[r][i];
                            range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
                            range.Font.Size = 9;//字体大小
                            //加边框
                            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
                            range.EntireColumn.AutoFit();//自动调整列宽
                        }
                        rowRead++;
                        percent = ((float)(100 * rowRead)) / totalCount;
                        //System.Windows.Forms.Application.DoEvents();
                    }
                    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
                    if (dt2.Columns.Count > 1)
                    {
                        range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
                    }
                }
            }

//下面是将Excel存储在服务器上指定的路径与存储的名称
            try
            {
                string tPath = System.AppDomain.CurrentDomain.BaseDirectory;
                if (!Directory.Exists(tPath + "Excel"))
                {
                    Directory.CreateDirectory(tPath + "Excel");
                }
                workbook.SaveCopyAs(tPath + "Excel" + "\\" + System.DateTime.Today.ToString("yyyyMMdd") + pCompany + "测试.xls");
                }
                //writeLog("生成Exce附件成功！");//此处是我的一个方法，记录log讯息的
            }
            catch(Exception ex)
            {
                string strMsgErr = "生成Excel附件過程中出現異常，詳細信息如：" + ex.ToString();
                //writeLog(strMsgErr);//这个也是写log档的
            }
            //我们导出Excel的时候会在进程中调用Excel进程，导出之后必须强制杀掉进程            
            try
            {
                if (xlApp != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(xlApp.Hwnd), out lpdwProcessId);
                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Delete Excel Process Error:" + ex.Message);
            } 
        }
        #endregion

到这里从数据库中导出Excel就完成了，并生成在相应的目录下。

这只是一个简单的导出方法，重点是我们要学会通过这个方法，将其运用到其他的导出Excel的功能需求中，这个时候我们需要做的可能有改变参数传入的个数，类型，等等。

之后我还会恭喜出，动态的从数据库中导出Excel，并将其作为发送邮件的附件发送到指定的接受人，以及将发送的邮件完成的保存成二进制文件或者XML文件，供我们追寻，如果发送失败的话，不需要重新生成所有的东西，只需调用保存的二进制文件或者XML文档即可完成重新发送邮件，方便快捷！

OK啦，今天就先说这么多啦！希望有更好的实现方法的朋友能够回复我，将你的资料共享出来！拜拜！