MedicalInsurance/Common/ExPortToExcel.cs

using System;
using System.Collections;
using System.Windows.Forms;
//using Excel = Microsoft.Office.Interop.Excel;

using System;
using System.Data;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using System.Collections.Generic;
using System.Threading.Tasks;


namespace PTMedicalInsurance.Common
{
    public class ExportToExcel
    {
        /// <summary>
        /// 将DataTable导出为CSV文件（完全替代原有的Excel导出）
        /// </summary>
        public static string DataTabletoCSV(DataTable tmpDataTable, string strFileName)
        {
            // 1. 前置校验：空值和路径合法性检查
            if (tmpDataTable == null || tmpDataTable.Rows.Count == 0)
            {
                MessageBox.Show("无导出数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return string.Empty;
            }

            // 校验并处理导出路径
            if (!ValidateAndFixExportPath(ref strFileName))
            {
                return string.Empty;
            }

            // 确保扩展名为.csv
            strFileName = EnsureCsvExtension(strFileName);

            FileStream fs = null;
            StreamWriter sw = null;
            StringBuilder sb = new StringBuilder(65536); // 64KB缓冲区

            try
            {
                int rowCount = tmpDataTable.Rows.Count;
                int columnCount = tmpDataTable.Columns.Count;
                const int batchSize = 5000; // 每批处理5000行

                // 2. 创建文件流
                fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write, FileShare.None);

                // 写入UTF-8 BOM，确保Excel打开不乱码
                fs.WriteByte(0xEF);
                fs.WriteByte(0xBB);
                fs.WriteByte(0xBF);

                sw = new StreamWriter(fs, Encoding.UTF8);

                // 3. 写入表头
                List<string> headers = new List<string>();
                for (int i = 0; i < columnCount; i++)
                {
                    string headerName = GetColumnNameMapping(tmpDataTable.Columns[i].ColumnName);
                    headers.Add(EscapeCsvValue(headerName));
                }
                sw.WriteLine(string.Join(",", headers));

                // 4. 分批写入数据
                for (int r = 0; r < rowCount; r++)
                {
                    // 每批处理完成后写入文件
                    if (r > 0 && r % batchSize == 0)
                    {
                        sw.Write(sb.ToString());
                        sb.Clear();
                        sw.Flush();
                        Application.DoEvents(); // 防止UI卡死
                    }

                    // 构建当前行
                    for (int i = 0; i < columnCount; i++)
                    {
                        object cellValue = tmpDataTable.Rows[r][i];

                        // 格式化单元格值
                        if (cellValue == null || cellValue == DBNull.Value)
                        {
                            sb.Append(string.Empty);
                        }
                        else
                        {
                            string value = cellValue.ToString().Trim();

                            // 特殊处理：如果是数字类型但需要保留前导零（如身份证号、电话号码）
                            if (IsNumeric(value))
                            {
                                // 检查是否是需要保留前导零的字段
                                string columnName = tmpDataTable.Columns[i].ColumnName;
                                if (IsIdNumberField(columnName))
                                {
                                    // 身份证号、电话号码等字段，强制作为文本处理
                                    sb.Append(EscapeCsvValue(value));
                                }
                                else
                                {
                                    sb.Append(value);
                                }
                            }
                            else
                            {
                                sb.Append(EscapeCsvValue(value));
                            }
                        }

                        if (i < columnCount - 1)
                        {
                            sb.Append(',');
                        }
                    }
                    sb.AppendLine();
                }

                // 5. 写入剩余数据
                if (sb.Length > 0)
                {
                    sw.Write(sb.ToString());
                }

                sw.Flush();

                // 6. 导出成功提示
                DialogResult result = MessageBox.Show(
                    $"CSV导出成功！\n文件路径：{strFileName}\n总行数：{rowCount}\n\n是否立即打开文件？",
                    "导出成功",
                    MessageBoxButtons.YesNo,
                    MessageBoxIcon.Information);

                if (result == DialogResult.Yes)
                {
                    try
                    {
                        System.Diagnostics.Process.Start(strFileName);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"打开文件失败：{ex.Message}", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                }

                return strFileName;
            }
            catch (UnauthorizedAccessException ex)
            {
                MessageBox.Show($"导出失败：权限不足！请检查文件路径是否可写。\n详情：{ex.Message}",
                    "权限异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return string.Empty;
            }
            catch (IOException ex)
            {
                MessageBox.Show($"导出失败：文件读写错误。\n详情：{ex.Message}",
                    "IO异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return string.Empty;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"导出异常：{ex.Message}\n堆栈信息：{ex.StackTrace}",
                    "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return string.Empty;
            }
            finally
            {
                // 7. 确保资源释放
                if (sw != null)
                {
                    try { sw.Close(); sw.Dispose(); } catch { }
                }
                if (fs != null)
                {
                    try { fs.Close(); fs.Dispose(); } catch { }
                }
                sb.Clear();
                GC.Collect();
            }
        }       

        /// <summary>
        /// 杀掉不死进程
        /// </summary>
        public void killProcessThread()
        {
            ArrayList myProcess = new ArrayList();
            for (int i = 0; i < myProcess.Count; i++)
            {
                try
                {
                    System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
                }
                catch { }
            }
        }

        #region 辅助方法

        /// <summary>
        /// 校验并修复导出路径
        /// </summary>
        private static bool ValidateAndFixExportPath(ref string filePath)
        {
            try
            {
                // 1. 检查路径是否为空
                if (string.IsNullOrWhiteSpace(filePath))
                {
                    filePath = Path.Combine(
                        Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
                        $"导出数据_{DateTime.Now:yyyyMMddHHmmss}.csv");
                }

                // 2. 检查目录是否存在
                string directory = Path.GetDirectoryName(filePath);
                if (!string.IsNullOrEmpty(directory) && !Directory.Exists(directory))
                {
                    Directory.CreateDirectory(directory);
                }

                // 3. 检查文件是否被占用
                if (File.Exists(filePath))
                {
                    try
                    {
                        using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Write, FileShare.None))
                        {
                            // 文件可写，直接返回
                        }
                    }
                    catch
                    {
                        DialogResult result = MessageBox.Show($"文件【{filePath}】已被占用，是否覆盖？",
                            "文件占用", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                        if (result == DialogResult.No)
                        {
                            return false;
                        }

                        // 尝试删除
                        try { File.Delete(filePath); } catch { }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"路径校验失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }

        /// <summary>
        /// 确保文件扩展名为.csv
        /// </summary>
        private static string EnsureCsvExtension(string filePath)
        {
            string extension = Path.GetExtension(filePath)?.ToLower();
            if (extension != ".csv")
            {
                return Path.ChangeExtension(filePath, ".csv");
            }
            return filePath;
        }

        /// <summary>
        /// 列名映射（保留原有的映射逻辑）
        /// </summary>
        private static string GetColumnNameMapping(string originalName)
        {
            var columnMapping = new Dictionary<string, string>
            {
                { "Adm_Dr", "HIS就诊号" },
                { "MdtrtID", "医保住院号" },
                { "PatientName", "姓名" },
                { "Age", "年龄" },
                { "Gend", "性别" },
                { "PsnCertNO", "身份证号" },
                { "EmpName", "单位或地址" },
                { "PsnType", "医保类型" },
                { "AdmInDepName", "病人部门名称" },
                { "AdmBed", "床号" },
                { "MainDiagName", "入院诊断" },
                { "AttendDoctorNO", "床位医生" },
                { "ChiefPhyDocName", "门诊医生" },
                { "OpertName", "入院登记人姓名" },
                { "RegDate", "入院登记时间" },
                { "insuplcAdmdvs", "参保地区划" },
                { "InHospNo", "HIS住院号" },
                { "Address", "现住址" }
            };

            return columnMapping.TryGetValue(originalName, out string newName) ? newName : originalName;
        }

        /// <summary>
        /// 判断是否为身份证号等需要保留前导零的字段
        /// </summary>
        private static bool IsIdNumberField(string columnName)
        {
            string lowerName = columnName.ToLower();
            return lowerName.Contains("id") ||
                   lowerName.Contains("cert") ||
                   lowerName.Contains("身份证") ||
                   lowerName.Contains("电话") ||
                   lowerName.Contains("phone") ||
                   lowerName.Contains("code") ||
                   lowerName.Contains("编码");
        }

        /// <summary>
        /// 判断是否为数字
        /// </summary>
        private static bool IsNumeric(string str)
        {
            return double.TryParse(str, out _);
        }

        /// <summary>
        /// 转义CSV值中的特殊字符
        /// </summary>
        private static string EscapeCsvValue(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return string.Empty;
            }

            // 检查是否包含特殊字符
            bool containsSpecialChar = value.Contains(",") ||
                                       value.Contains("\"") ||
                                       value.Contains("\r") ||
                                       value.Contains("\n") ||
                                       value.Contains("\t");

            if (containsSpecialChar)
            {
                // 双引号转义
                value = value.Replace("\"", "\"\"");
                return $"\"{value}\"";
            }

            return value;
        }

        #endregion



    }

}