数据导出Excel

偏执的太偏执、 2023-12-09 07:41 344阅读 0赞

/// <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);
                }
            }