数据导出Excel

   /// <summary>
        /// 导出数据到excel
        /// </summary>
        /// <param name="lst_dt">需要导出的数据</param>
        /// <param name="filePath">excel存放位置</param>
        /// <returns></returns>
        public string ExportExcel(List<DataTable> lst_dt, string filePath)
        {
            try
            {
                string FileName = "";
                string excelFlag = "000";
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                object missing = System.Reflection.Missing.Value;
                if (xlApp == null)
                {
                    MessageBoxEx.Show("无法创建Excel对象，可能您的电脑未安装Excel");
                    return null;
                }
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                workbook.Worksheets.Add(Missing.Value, Missing.Value, lst_dt.Count, Missing.Value);

                for (int m = 1; m <= lst_dt.Count; m++)
                {
                    System.Data.DataTable dtDataSource = new DataTable();
                    if (lst_dt[m - 1].Rows.Count == 0)
                    {
                        continue;
                    }
                    string sheet = DataMode_export.getSheet(int.Parse((lst_dt[m - 1].Rows[0]["DiMingLeiBie"]).ToString()));
                    dtDataSource = TableToTable(lst_dt[m - 1]);
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[m];
                    if (sheet != "0")
                    {
                        worksheet.Name = sheet;
                    }

                    if (m == 1)
                    {
                        worksheet.Cells.Select();
                        worksheet.Cells.Columns.AutoFit();
                    }
                    Microsoft.Office.Interop.Excel.Range range;
                    long totalCount = dtDataSource.Rows.Count;
                    long rowRead = 0;
                    float percent = 0;
                    // 写入字段
                    for (int i = 0; i < dtDataSource.Columns.Count; i++)
                    {
                        worksheet.Cells[1, i + 1] = dtDataSource.Columns[i].ColumnName;
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                        if (dtDataSource.Columns[i].ColumnName.Contains("K_"))
                            range.Interior.ColorIndex = 15;
                        else
                            range.Interior.ColorIndex = 39;
                        range.Font.Size = 14;
                        range.Font.Bold = true;
                        range.ColumnWidth = 15;
                    }
                    //写入数值
                    this.progressBar1.Maximum = dtDataSource.Rows.Count;
                    label1.Text = sheet;

                    for (int r = 0; r < dtDataSource.Rows.Count; r++)
                    {
                        
                        for (int i = 0; i < dtDataSource.Columns.Count; i++)
                        {
                            worksheet.Cells[r + 2, i + 1] = dtDataSource.Rows[r][i];
                            range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
                            range.NumberFormatLocal = "@";
                        }
                        this.progressBar1.Value = r + 1;
                        rowRead++;
                        percent = ((float)(100 * rowRead)) / totalCount;
                        System.Windows.Forms.Application.DoEvents();
                    }
                    //string excel = DataMode_export.getExcel(int.Parse((lst_dt[m - 1].Rows[0]["DiMingLeiBie"]).ToString()));
                    //string  saveFileName = filePath +"\\"+ excel + ".xls";
                    if (m < lst_dt.Count)
                    {
                        if (lst_dt[m].Rows.Count == 0)
                        {
                            excelFlag = "9999";
                        }
                        else
                            excelFlag = DataMode_export.getExcel(int.Parse((lst_dt[m].Rows[0]["DiMingLeiBie"]).ToString()));
                    }
                }
                FileName = "地名" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                string saveFileName = filePath + "\\" + FileName;
                workbook.Saved = true;            //保存工作薄
                if (saveFileName != filePath)
                    xlApp.ActiveWorkbook.SaveCopyAs(saveFileName);//路径需绝对路径

                workbook.Close(false, missing, missing);
                xlApp.Quit();
                GC.Collect();
                return FileName;
            }
            catch
            {
                return "";
            }
        }
        /// <summary>
        /// 导出数据到excel
        /// </summary>
        /// <param name="lst_dt">需要导出的数据</param>
        /// <param name="filePath">excel存放位置</param>
        /// <returns></returns>
        public string ExportExcels(List<DataTable> lst_dt, string filePath)
        {
            try
            {
                string savepath = "";
                Thread.Sleep(1000);
                long rowRead = 0;
                float percent = 0;
                StringBuilder sb = new StringBuilder();
                string FileName = string.Empty;
                for (int m = 1; m <= lst_dt.Count; m++)
                {
                     DataTable  dtDataSource = TableToTable(lst_dt[m - 1]);
                      long totalCount = dtDataSource.Rows.Count;
                      string sheet = DataMode_export.getSheet(int.Parse((lst_dt[m - 1].Rows[0]["DiMingLeiBie"]).ToString()));
                      FileName = sheet +"—" +"地名数据" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                      savepath = filePath + "\\" + FileName;
                      StreamWriter sw = new StreamWriter(savepath, false, Encoding.GetEncoding("gb2312"));
                    for (int k = 0; k < dtDataSource.Columns.Count; k++)
                    {
                        sb.Append(dtDataSource.Columns[k].ColumnName.ToString() + "\t");
                    }
                    sb.Append(Environment.NewLine);

                    for (int i = 0; i < dtDataSource.Rows.Count; i++)
                    {
                      
                        rowRead++;
                        percent = ((float)(100 * rowRead)) / totalCount;
                        System.Windows.Forms.Application.DoEvents();
                        for (int j = 0; j < dtDataSource.Columns.Count; j++)
                        {
                            sb.Append(dtDataSource.Rows[i][j].ToString() + "\t");
                        }
                        sb.Append(Environment.NewLine);

                    }
                    sw.Write(sb.ToString());
                    sw.Flush();
                    sw.Close();
                }
                return FileName;
                //MessageBox.Show("已经生成指定Excel文件!");
            }
            catch (Exception ex)
            {
                return "";
                //MessageBox.Show(ex.Message);
            }
        }