MedicalInsurance/TaiZhouMI/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 static async Task<string> DataTabletoCSV_BatchAsync(DataTable tmpDataTable, string strFileName, int pageSize = 10000, IProgress<int> progress = null)
        {
            if (tmpDataTable == null || tmpDataTable.Rows.Count == 0)
            {
                MessageBox.Show("无导出数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return string.Empty;
            }

            if (!ValidateAndFixExportPath(ref strFileName))
            {
                return string.Empty;
            }

            strFileName = EnsureCsvExtension(strFileName);

            int totalRows = tmpDataTable.Rows.Count;
            int columnCount = tmpDataTable.Columns.Count;
            int pageCount = (int)Math.Ceiling((double)totalRows / pageSize);

            // 使用更高效的StringBuilder
            StringBuilder sb = new StringBuilder(65536 * 4); // 256KB缓冲区

            try
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write, FileShare.None, 81920, true)) // 80KB缓冲区，异步IO
                {
                    // 写入UTF-8 BOM
                    fs.WriteByte(0xEF);
                    fs.WriteByte(0xBB);
                    fs.WriteByte(0xBF);

                    using (StreamWriter sw = new StreamWriter(fs, Encoding.UTF8, 81920)) // 80KB缓冲区
                    {
                        // 写入表头
                        List<string> headers = new List<string>(columnCount);
                        for (int i = 0; i < columnCount; i++)
                        {
                            headers.Add(EscapeCsvValue(GetColumnNameMapping(tmpDataTable.Columns[i].ColumnName)));
                        }
                        await sw.WriteLineAsync(string.Join(",", headers));

                        // 分页写入数据
                        for (int page = 0; page < pageCount; page++)
                        {
                            int startRow = page * pageSize;
                            int endRow = Math.Min(startRow + pageSize, totalRows);

                            sb.Clear();

                            for (int r = startRow; r < endRow; r++)
                            {
                                BuildCsvRow(tmpDataTable.Rows[r], columnCount, sb);

                                // 每1000行输出一次，避免StringBuilder过大
                                if ((r - startRow + 1) % 1000 == 0 || r == endRow - 1)
                                {
                                    await sw.WriteAsync(sb.ToString());
                                    sb.Clear();
                                }
                            }

                            await sw.FlushAsync();

                            // 报告进度
                            progress?.Report((int)((page + 1) * 100.0 / pageCount));

                            // 可选：每处理完一页，让出控制权，避免UI卡死
                            await Task.Delay(1);
                        }
                    }
                }

                // 导出成功后的处理
                DialogResult result = MessageBox.Show(
                    $"CSV导出成功！\n文件路径：{strFileName}\n总行数：{totalRows}\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 (Exception ex)
            {
                MessageBox.Show($"导出异常：{ex.Message}\n堆栈信息：{ex.StackTrace}",
                    "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return string.Empty;
            }
            finally
            {
                // 强制垃圾回收，释放内存
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        /// <summary>
        /// 构建CSV行
        /// </summary>
        private static void BuildCsvRow(DataRow row, int columnCount, StringBuilder sb)
        {
            for (int i = 0; i < columnCount; i++)
            {
                object cellValue = row[i];

                if (cellValue == null || cellValue == DBNull.Value)
                {
                    sb.Append(string.Empty);
                }
                else
                {
                    string value = cellValue.ToString().Trim();
                    string columnName = row.Table.Columns[i].ColumnName;

                    // 根据字段类型处理
                    if (IsIdNumberField(columnName))
                    {
                        sb.Append(EscapeCsvValue(value)); // 作为文本
                    }
                    else if (IsNumeric(value))
                    {
                        sb.Append(value); // 作为数字
                    }
                    else
                    {
                        sb.Append(EscapeCsvValue(value));
                    }
                }

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

        /// <summary>
        /// 分批导出数据（同步版本，优化内存使用）
        /// </summary>
        public static string DataTabletoCSV_BatchOptimized(DataTable tmpDataTable, string strFileName, int pageSize = 5000)
        {
            if (tmpDataTable == null || tmpDataTable.Rows.Count == 0)
            {
                MessageBox.Show("无导出数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return string.Empty;
            }

            if (!ValidateAndFixExportPath(ref strFileName))
            {
                return string.Empty;
            }

            strFileName = EnsureCsvExtension(strFileName);

            int totalRows = tmpDataTable.Rows.Count;
            int columnCount = tmpDataTable.Columns.Count;
            int pageCount = (int)Math.Ceiling((double)totalRows / pageSize);

            // 使用更高效的StringBuilder
            StringBuilder sb = new StringBuilder(65536 * 4); // 256KB缓冲区

            try
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write, FileShare.None, 81920))
                {
                    // 写入UTF-8 BOM
                    fs.WriteByte(0xEF);
                    fs.WriteByte(0xBB);
                    fs.WriteByte(0xBF);

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

                        // 分页写入数据
                        for (int page = 0; page < pageCount; page++)
                        {
                            int startRow = page * pageSize;
                            int endRow = Math.Min(startRow + pageSize, totalRows);

                            sb.Clear();

                            for (int r = startRow; r < endRow; r++)
                            {
                                BuildCsvRow(tmpDataTable.Rows[r], columnCount, sb);

                                // 每1000行写入一次，避免StringBuilder过大
                                if ((r - startRow + 1) % 1000 == 0 || r == endRow - 1)
                                {
                                    sw.Write(sb.ToString());
                                    sb.Clear();
                                }
                            }

                            sw.Flush();

                            // 每处理完一页，检查是否有UI消息（仅在UI线程调用时）
                            if (Application.MessageLoop)
                            {
                                Application.DoEvents();
                            }
                        }
                    }
                }

                MessageBox.Show($"CSV导出成功！\n文件路径：{strFileName}\n总行数：{totalRows}",
                    "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);

                return strFileName;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"导出异常：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return string.Empty;
            }
            finally
            {
                GC.Collect();
            }
        }

        /// <summary>
        /// 超大数据量导出（分页处理）
        /// </summary>
        public static string DataTabletoCSV_Batch(DataTable tmpDataTable, string strFileName, int pageSize = 10000)
        {
            if (tmpDataTable == null || tmpDataTable.Rows.Count == 0)
            {
                MessageBox.Show("无导出数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return string.Empty;
            }

            if (!ValidateAndFixExportPath(ref strFileName))
            {
                return string.Empty;
            }

            strFileName = EnsureCsvExtension(strFileName);

            int totalRows = tmpDataTable.Rows.Count;
            int columnCount = tmpDataTable.Columns.Count;
            int pageCount = (int)Math.Ceiling((double)totalRows / pageSize);

            try
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write, FileShare.None))
                {
                    // 写入UTF-8 BOM
                    fs.WriteByte(0xEF);
                    fs.WriteByte(0xBB);
                    fs.WriteByte(0xBF);

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

                        // 分页写入数据
                        for (int page = 0; page < pageCount; page++)
                        {
                            int startRow = page * pageSize;
                            int endRow = Math.Min(startRow + pageSize, totalRows);

                            for (int r = startRow; r < endRow; r++)
                            {
                                List<string> rowValues = new List<string>(columnCount);

                                for (int i = 0; i < columnCount; i++)
                                {
                                    object cellValue = tmpDataTable.Rows[r][i];

                                    if (cellValue == null || cellValue == DBNull.Value)
                                    {
                                        rowValues.Add(string.Empty);
                                    }
                                    else
                                    {
                                        string value = cellValue.ToString().Trim();

                                        // 根据字段类型处理
                                        string columnName = tmpDataTable.Columns[i].ColumnName;
                                        if (IsIdNumberField(columnName))
                                        {
                                            rowValues.Add(EscapeCsvValue(value)); // 作为文本
                                        }
                                        else if (IsNumeric(value))
                                        {
                                            rowValues.Add(value); // 作为数字
                                        }
                                        else
                                        {
                                            rowValues.Add(EscapeCsvValue(value));
                                        }
                                    }
                                }

                                sw.WriteLine(string.Join(",", rowValues));

                                if (r % 500 == 0)
                                {
                                    Application.DoEvents();
                                }
                            }

                            sw.Flush();

                            // 可选：显示进度
                            int progress = (int)((page + 1) * 100.0 / pageCount);
                            // 可以在这里更新进度条
                        }
                    }
                }

                MessageBox.Show($"CSV导出成功！\n文件路径：{strFileName}\n总行数：{totalRows}",
                    "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);

                return strFileName;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"导出异常：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return string.Empty;
            }
        }

        #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   

    }
}