C#读取CSV/Excel文件数据并显示在DataGridView实用指南

作者：袖梨 2026-09-08

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“C#读取CSV/Excel文件数据并显示在DataGridView”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

目录
  • 解决方案结构
  • 完整代码实现
    • 1. 主窗体 (Form1.cs)
    • 2. 文件读取器 (FileReader.cs)
    • 3. 数据处理类 (DataProcessor.cs)
    • 4. 程序入口 (Program.cs)
    • 5. 应用程序设置文件 (App.config)
    • 6. NuGet 包设置 (packages.config)
  • 功能特点
    • 1. 文件读取功能
    • 2. 数据处理功能
    • 3. 用户界面功能
    • 4. 性能优化
  • 采用说明
    • 1. 环境要求
    • 2. 操作步骤
    • 3. 采用示例
  • 技术亮点
    • 1. CSV解析算法
    • 2. Excel数据类型处理
    • 3. 数据类型推断
  • 扩展功能
    • 1. 添加数据库兼容
    • 2. 添加数据过滤功能
    • 3. 添加数据统计功能
  • 常用问题解决
    • 1. 文件格式问题
    • 2. 大文件处理
    • 3. 特殊格式处理
  • 项目总结

结合项目来看，C# 用来读取 CSV 和 Excel 文件数据同时显示在 DataGridView 控件中，兼容文件选择、数据预览、格式处理和错误处理等功能。

解决方案结构

CsvExcelReader/
├── CsvExcelReader.sln
├── CsvExcelReader/
│   ├── App.config
│   ├── Form1.cs
│   ├── Form1.Designer.cs
│   ├── Form1.resx
│   ├── Program.cs
│   ├── FileReader.cs
│   ├── DataProcessor.cs
│   └── Properties/
│       ├── AssemblyInfo.cs
│       └── Resources.Designer.cs
└── packages.config

完整代码实现

1. 主窗体 (Form1.cs)

using System;
using System.Data;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace CsvExcelReader
{
    public partial class MainForm : Form
    {
        private readonly FileReader fileReader = new FileReader();
        private readonly DataProcessor dataProcessor = new DataProcessor();
        
        public MainForm()
        {
            InitializeComponent();
            InitializeUI();
        }

        private void InitializeUI()
        {
            // 窗体设置
            this.Text = "CSV/Excel 数据读取器";
            this.Size = new Size(900, 600);
            this.StartPosition = FormStartPosition.CenterScreen;
            this.BackColor = Color.FromArgb(240, 240, 240);
            
            // 创建控件
            lblTitle = new Label
            {
                Text = "CSV/Excel 数据读取器",
                Font = new Font("微软雅黑", 16, FontStyle.Bold),
                ForeColor = Color.DarkSlateBlue,
                AutoSize = true,
                Location = new Point(20, 20)
            };
            
            btnOpenCsv = new Button
            {
                Text = "打开 CSV 文件",
                Size = new Size(120, 40),
                Location = new Point(30, 70),
                BackColor = Color.SteelBlue,
                ForeColor = Color.White,
                FlatStyle = FlatStyle.Flat,
                Font = new Font("微软雅黑", 10)
            };
            btnOpenCsv.FlatAppearance.BorderSize = 0;
            btnOpenCsv.Click += BtnOpenCsv_Click;
            
            btnOpenExcel = new Button
            {
                Text = "打开 Excel 文件",
                Size = new Size(120, 40),
                Location = new Point(160, 70),
                BackColor = Color.ForestGreen,
                ForeColor = Color.White,
                FlatStyle = FlatStyle.Flat,
                Font = new Font("微软雅黑", 10)
            };
            btnOpenExcel.FlatAppearance.BorderSize = 0;
            btnOpenExcel.Click += BtnOpenExcel_Click;
            
            btnSaveCsv = new Button
            {
                Text = "保存为 CSV",
                Size = new Size(120, 40),
                Location = new Point(290, 70),
                BackColor = Color.Orange,
                ForeColor = Color.White,
                FlatStyle = FlatStyle.Flat,
                Font = new Font("微软雅黑", 10),
                Enabled = false
            };
            btnSaveCsv.FlatAppearance.BorderSize = 0;
            btnSaveCsv.Click += BtnSaveCsv_Click;
            
            btnSaveExcel = new Button
            {
                Text = "保存为 Excel",
                Size = new Size(120, 40),
                Location = new Point(420, 70),
                BackColor = Color.Purple,
                ForeColor = Color.White,
                FlatStyle = FlatStyle.Flat,
                Font = new Font("微软雅黑", 10),
                Enabled = false
            };
            btnSaveExcel.FlatAppearance.BorderSize = 0;
            btnSaveExcel.Click += BtnSaveExcel_Click;
            
            btnClear = new Button
            {
                Text = "清除数据",
                Size = new Size(120, 40),
                Location = new Point(550, 70),
                BackColor = Color.Gray,
                ForeColor = Color.White,
                FlatStyle = FlatStyle.Flat,
                Font = new Font("微软雅黑", 10)
            };
            btnClear.FlatAppearance.BorderSize = 0;
            btnClear.Click += BtnClear_Click;
            
            // 状态标签
            lblStatus = new Label
            {
                Text = "就绪",
                AutoSize = true,
                Location = new Point(30, 120),
                Font = new Font("微软雅黑", 9),
                ForeColor = Color.DimGray
            };
            
            // 数据网格视图
            dataGridView = new DataGridView
            {
                Location = new Point(20, 150),
                Size = new Size(840, 400),
                BackgroundColor = Color.White,
                BorderStyle = BorderStyle.FixedSingle,
                AllowUserToAddRows = false,
                AllowUserToDeleteRows = false,
                ReadOnly = true,
                AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells,
                RowHeadersVisible = false,
                ColumnHeadersDefaultCellStyle = new DataGridViewCellStyle
                {
                    BackColor = Color.SteelBlue,
                    ForeColor = Color.White,
                    Font = new Font("微软雅黑", 10, FontStyle.Bold)
                },
                DefaultCellStyle = new DataGridViewCellStyle
                {
                    Font = new Font("微软雅黑", 9),
                    SelectionBackColor = Color.LightSteelBlue
                },
                EnableHeadersVisualStyles = false
            };
            
            // 进度条
            progressBar = new ProgressBar
            {
                Location = new Point(20, 560),
                Size = new Size(840, 20),
                Visible = false
            };
            
            // 添加控件到窗体
            this.Controls.Add(lblTitle);
            this.Controls.Add(btnOpenCsv);
            this.Controls.Add(btnOpenExcel);
            this.Controls.Add(btnSaveCsv);
            this.Controls.Add(btnSaveExcel);
            this.Controls.Add(btnClear);
            this.Controls.Add(lblStatus);
            this.Controls.Add(dataGridView);
            this.Controls.Add(progressBar);
        }

        #region 控件声明
        private Label lblTitle;
        private Button btnOpenCsv;
        private Button btnOpenExcel;
        private Button btnSaveCsv;
        private Button btnSaveExcel;
        private Button btnClear;
        private Label lblStatus;
        private DataGridView dataGridView;
        private ProgressBar progressBar;
        #endregion

        #region 事件处理
        private void BtnOpenCsv_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                openFileDialog.Filter = "CSV 文件 (*.csv)|*.csv|所有文件 (*.*)|*.*";
                openFileDialog.Title = "选择 CSV 文件";
                
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    LoadFile(openFileDialog.FileName, FileType.Csv);
                }
            }
        }

        private void BtnOpenExcel_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                openFileDialog.Filter = "Excel 文件 (*.xls;*.xlsx)|*.xls;*.xlsx|所有文件 (*.*)|*.*";
                openFileDialog.Title = "选择 Excel 文件";
                
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    LoadFile(openFileDialog.FileName, FileType.Excel);
                }
            }
        }

        private void BtnSaveCsv_Click(object sender, EventArgs e)
        {
            if (dataGridView.DataSource == null) return;
            
            using (SaveFileDialog saveFileDialog = new SaveFileDialog())
            {
                saveFileDialog.Filter = "CSV 文件 (*.csv)|*.csv";
                saveFileDialog.Title = "保存为 CSV 文件";
                saveFileDialog.FileName = "data_export.csv";
                
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        dataProcessor.ExportToCsv((DataTable)dataGridView.DataSource, saveFileDialog.FileName);
                        lblStatus.Text = $"数据已成功导出到: {saveFileDialog.FileName}";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"导出失败: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }

        private void BtnSaveExcel_Click(object sender, EventArgs e)
        {
            if (dataGridView.DataSource == null) return;
            
            using (SaveFileDialog saveFileDialog = new SaveFileDialog())
            {
                saveFileDialog.Filter = "Excel 文件 (*.xlsx)|*.xlsx";
                saveFileDialog.Title = "保存为 Excel 文件";
                saveFileDialog.FileName = "data_export.xlsx";
                
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        dataProcessor.ExportToExcel((DataTable)dataGridView.DataSource, saveFileDialog.FileName);
                        lblStatus.Text = $"数据已成功导出到: {saveFileDialog.FileName}";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"导出失败: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }

        private void BtnClear_Click(object sender, EventArgs e)
        {
            dataGridView.DataSource = null;
            btnSaveCsv.Enabled = false;
            btnSaveExcel.Enabled = false;
            lblStatus.Text = "数据已清除";
        }
        #endregion

        #region 文件加载与处理
        private void LoadFile(string filePath, FileType fileType)
        {
            try
            {
                progressBar.Visible = true;
                progressBar.Style = ProgressBarStyle.Marquee;
                lblStatus.Text = $"正在加载文件: {Path.GetFileName(filePath)}...";
                Application.DoEvents();
                
                DataTable dataTable = fileType == FileType.Csv 
                    ? fileReader.ReadCsv(filePath) 
                    : fileReader.ReadExcel(filePath);
                
                if (dataTable == null || dataTable.Rows.Count == 0)
                {
                    lblStatus.Text = "文件为空或未找到数据";
                    return;
                }
                
                // 数据预处理
                dataTable = dataProcessor.ProcessDataTable(dataTable);
                
                // 绑定到DataGridView
                dataGridView.DataSource = dataTable;
                
                // 启用保存按钮
                btnSaveCsv.Enabled = true;
                btnSaveExcel.Enabled = true;
                
                lblStatus.Text = $"已加载: {Path.GetFileName(filePath)} | 行: {dataTable.Rows.Count} | 列: {dataTable.Columns.Count}";
            }
            catch (Exception ex)
            {
                MessageBox.Show($"加载文件失败: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                lblStatus.Text = $"错误: {ex.Message}";
            }
            finally
            {
                progressBar.Visible = false;
            }
        }
        #endregion
    }

    public enum FileType
    {
        Csv,
        Excel
    }
}

2. 文件读取器 (FileReader.cs)

using System;
using System.Data;
using System.IO;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace CsvExcelReader
{
    public class FileReader
    {
        /// <summary>
        /// 读取CSV文件并返回DataTable
        /// </summary>
        public DataTable ReadCsv(string filePath)
        {
            if (!File.Exists(filePath))
                throw new FileNotFoundException("文件不存在", filePath);
            
            DataTable dataTable = new DataTable();
            int rowCount = 0;
            
            using (var reader = new StreamReader(filePath, Encoding.UTF8))
            {
                string line;
                bool isFirstRow = true;
                
                while ((line = reader.ReadLine()) != null)
                {
                    // 处理可能的引号包围字段
                    string[] fields = ParseCsvLine(line);
                    
                    if (isFirstRow)
                    {
                        // 创建列
                        foreach (string field in fields)
                        {
                            dataTable.Columns.Add(field);
                        }
                        isFirstRow = false;
                    }
                    else
                    {
                        // 添加行数据
                        if (fields.Length > dataTable.Columns.Count)
                        {
                            // 添加缺失的列
                            for (int i = dataTable.Columns.Count; i < fields.Length; i++)
                            {
                                dataTable.Columns.Add($"Column{dataTable.Columns.Count + 1}");
                            }
                        }
                        
                        DataRow row = dataTable.NewRow();
                        for (int i = 0; i < fields.Length; i++)
                        {
                            if (i < dataTable.Columns.Count)
                            {
                                row[i] = fields[i];
                            }
                        }
                        dataTable.Rows.Add(row);
                    }
                    
                    rowCount++;
                }
            }
            
            return dataTable;
        }

        /// <summary>
        /// 解析CSV行，处理逗号和引号
        /// </summary>
        private string[] ParseCsvLine(string line)
        {
            var fields = new System.Collections.Generic.List<string>();
            bool inQuotes = false;
            var currentField = new StringBuilder();
            
            for (int i = 0; i < line.Length; i++)
            {
                char c = line[i];
                
                if (c == '"')
                {
                    // 处理转义引号
                    if (inQuotes && i + 1 < line.Length && line[i + 1] == '"')
                    {
                        currentField.Append('"');
                        i++;
                    }
                    else
                    {
                        inQuotes = !inQuotes;
                    }
                }
                else if (c == ',' && !inQuotes)
                {
                    // 字段结束
                    fields.Add(currentField.ToString());
                    currentField.Clear();
                }
                else
                {
                    currentField.Append(c);
                }
            }
            
            // 添加最后一个字段
            fields.Add(currentField.ToString());
            
            return fields.ToArray();
        }

        /// <summary>
        /// 读取Excel文件并返回DataTable
        /// </summary>
        public DataTable ReadExcel(string filePath)
        {
            if (!File.Exists(filePath))
                throw new FileNotFoundException("文件不存在", filePath);
            
            IWorkbook workbook;
            DataTable dataTable = new DataTable();
            int rowCount = 0;
            
            using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                // 根据扩展名创建不同的工作簿
                if (Path.GetExtension(filePath).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
                {
                    workbook = new XSSFWorkbook(fileStream);
                }
                else if (Path.GetExtension(filePath).Equals(".xls", StringComparison.OrdinalIgnoreCase))
                {
                    workbook = new HSSFWorkbook(fileStream);
                }
                else
                {
                    throw new NotSupportedException("不支持的Excel文件格式");
                }
                
                // 获取第一个工作表
                ISheet sheet = workbook.GetSheetAt(0);
                if (sheet == null) return dataTable;
                
                // 获取标题行
                IRow headerRow = sheet.GetRow(0);
                if (headerRow == null) return dataTable;
                
                // 创建列
                for (int i = 0; i < headerRow.LastCellNum; i++)
                {
                    ICell cell = headerRow.GetCell(i);
                    string columnName = cell?.ToString() ?? $"Column{i + 1}";
                    dataTable.Columns.Add(columnName);
                }
                
                // 添加数据行
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;
                    
                    DataRow dataRow = dataTable.NewRow();
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            switch (cell.CellType)
                            {
                                case CellType.String:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        dataRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd");
                                    }
                                    else
                                    {
                                        dataRow[j] = cell.NumericCellValue;
                                    }
                                    break;
                                case CellType.Boolean:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.Formula:
                                    dataRow[j] = cell.NumericCellValue;
                                    break;
                                default:
                                    dataRow[j] = cell.ToString();
                                    break;
                            }
                        }
                    }
                    dataTable.Rows.Add(dataRow);
                    rowCount++;
                }
            }
            
            return dataTable;
        }
    }
}

3. 数据处理类 (DataProcessor.cs)

using System;
using System.Data;
using System.Globalization;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace CsvExcelReader
{
    public class DataProcessor
    {
        /// <summary>
        /// 处理DataTable数据（类型转换、空值处理等）
        /// </summary>
        public DataTable ProcessDataTable(DataTable inputTable)
        {
            if (inputTable == null) return null;
            
            DataTable processedTable = inputTable.Clone();
            
            // 处理列数据类型
            foreach (DataColumn column in processedTable.Columns)
            {
                // 尝试推断列的数据类型
                Type inferredType = InferColumnType(inputTable, column.ColumnName);
                if (inferredType != null)
                {
                    column.DataType = inferredType;
                }
            }
            
            // 处理行数据
            foreach (DataRow row in inputTable.Rows)
            {
                DataRow newRow = processedTable.NewRow();
                
                for (int i = 0; i < inputTable.Columns.Count; i++)
                {
                    string columnName = inputTable.Columns[i].ColumnName;
                    object value = row[i];
                    
                    // 处理空值
                    if (value == null || value == DBNull.Value || string.IsNullOrWhiteSpace(value.ToString()))
                    {
                        newRow[columnName] = DBNull.Value;
                        continue;
                    }
                    
                    // 处理数值类型
                    if (processedTable.Columns[columnName].DataType == typeof(int))
                    {
                        if (int.TryParse(value.ToString(), out int intValue))
                        {
                            newRow[columnName] = intValue;
                        }
                        else
                        {
                            newRow[columnName] = DBNull.Value;
                        }
                    }
                    else if (processedTable.Columns[columnName].DataType == typeof(double))
                    {
                        if (double.TryParse(value.ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out double doubleValue))
                        {
                            newRow[columnName] = doubleValue;
                        }
                        else
                        {
                            newRow[columnName] = DBNull.Value;
                        }
                    }
                    else if (processedTable.Columns[columnName].DataType == typeof(DateTime))
                    {
                        if (DateTime.TryParse(value.ToString(), out DateTime dateValue))
                        {
                            newRow[columnName] = dateValue;
                        }
                        else
                        {
                            newRow[columnName] = DBNull.Value;
                        }
                    }
                    else
                    {
                        newRow[columnName] = value.ToString().Trim();
                    }
                }
                
                processedTable.Rows.Add(newRow);
            }
            
            return processedTable;
        }

        /// <summary>
        /// 推断列的数据类型
        /// </summary>
        private Type InferColumnType(DataTable table, string columnName)
        {
            int totalRows = table.Rows.Count;
            if (totalRows == 0) return typeof(string);
            
            int intCount = 0;
            int doubleCount = 0;
            int dateCount = 0;
            int stringCount = 0;
            
            foreach (DataRow row in table.Rows)
            {
                object value = row[columnName];
                if (value == null || value == DBNull.Value) continue;
                
                string strValue = value.ToString().Trim();
                if (string.IsNullOrEmpty(strValue)) continue;
                
                if (int.TryParse(strValue, out _))
                {
                    intCount++;
                }
                else if (double.TryParse(strValue, NumberStyles.Any, CultureInfo.InvariantCulture, out _))
                {
                    doubleCount++;
                }
                else if (DateTime.TryParse(strValue, out _))
                {
                    dateCount++;
                }
                else
                {
                    stringCount++;
                }
            }
            
            // 如果80%以上的值都是整数，则认为是整数类型
            if ((double)intCount / totalRows > 0.8) return typeof(int);
            
            // 如果80%以上的值都是浮点数，则认为是浮点数类型
            if ((double)(intCount + doubleCount) / totalRows > 0.8) return typeof(double);
            
            // 如果80%以上的值都是日期，则认为是日期类型
            if ((double)dateCount / totalRows > 0.8) return typeof(DateTime);
            
            return typeof(string);
        }

        /// <summary>
        /// 导出DataTable到CSV文件
        /// </summary>
        public void ExportToCsv(DataTable dataTable, string filePath)
        {
            if (dataTable == null) return;
            
            using (var writer = new StreamWriter(filePath, false, Encoding.UTF8))
            {
                // 写入列头
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    writer.Write(EscapeCsvField(dataTable.Columns[i].ColumnName));
                    if (i < dataTable.Columns.Count - 1)
                    {
                        writer.Write(",");
                    }
                }
                writer.WriteLine();
                
                // 写入数据行
                foreach (DataRow row in dataTable.Rows)
                {
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        object value = row[i];
                        string strValue = value?.ToString() ?? string.Empty;
                        writer.Write(EscapeCsvField(strValue));
                        if (i < dataTable.Columns.Count - 1)
                        {
                            writer.Write(",");
                        }
                    }
                    writer.WriteLine();
                }
            }
        }

        /// <summary>
        /// 转义CSV字段
        /// </summary>
        private string EscapeCsvField(string field)
        {
            if (string.IsNullOrEmpty(field)) return string.Empty;
            
            // 如果包含逗号、换行符或双引号，则用双引号包围
            if (field.Contains(",") || field.Contains(""") || field.Contains("n") || field.Contains("r"))
            {
                return """ + field.Replace(""", """") + """;
            }
            return field;
        }

        /// <summary>
        /// 导出DataTable到Excel文件
        /// </summary>
        public void ExportToExcel(DataTable dataTable, string filePath)
        {
            if (dataTable == null) return;
            
            IWorkbook workbook;
            if (Path.GetExtension(filePath).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                workbook = new XSSFWorkbook();
            }
            else
            {
                workbook = new HSSFWorkbook();
            }
            
            ISheet sheet = workbook.CreateSheet("Sheet1");
            
            // 创建标题行
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(dataTable.Columns[i].ColumnName);
                
                // 设置标题样式
                ICellStyle style = workbook.CreateCellStyle();
                IFont font = workbook.CreateFont();
                font.Boldweight = (short)FontBoldWeight.Bold;
                style.SetFont(font);
                cell.CellStyle = style;
            }
            
            // 填充数据
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    object value = dataTable.Rows[i][j];
                    
                    if (value == DBNull.Value || value == null)
                    {
                        cell.SetCellValue(string.Empty);
                    }
                    else if (value is int intValue)
                    {
                        cell.SetCellValue(intValue);
                    }
                    else if (value is double doubleValue)
                    {
                        cell.SetCellValue(doubleValue);
                    }
                    else if (value is DateTime dateValue)
                    {
                        cell.SetCellValue(dateValue);
                        ICellStyle dateStyle = workbook.CreateCellStyle();
                        IDataFormat format = workbook.CreateDataFormat();
                        dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                        cell.CellStyle = dateStyle;
                    }
                    else
                    {
                        cell.SetCellValue(value.ToString());
                    }
                }
            }
            
            // 自动调整列宽
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            
            // 保存文件
            using (var fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fileStream);
            }
        }
    }
}

4. 程序入口 (Program.cs)

using System;
using System.Windows.Forms;

namespace CsvExcelReader
{
    static class Program
    {
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new MainForm());
        }
    }
}

5. 应用程序设置文件 (App.config)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <startup> 
    <supportedRuntime version="v4.0" sku=".Nramework,Version=v4.7.2"/>
  </startup>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="NPOI" publicKeyToken="0DF73EC7942D9264" culture="neutral"/>
        <bindingRedirect oldVersion="0.0.0.0-2.5.6.0" newVersion="2.5.6.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="NPOI.OOXML" publicKeyToken="0DF73EC7942D9264" culture="neutral"/>
        <bindingRedirect oldVersion="0.0.0.0-2.5.6.0" newVersion="2.5.6.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="NPOI.HPSF" publicKeyToken="0DF73EC7942D9264" culture="neutral"/>
        <bindingRedirect oldVersion="0.0.0.0-2.5.6.0" newVersion="2.5.6.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

6. NuGet 包设置 (packages.config)

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="NPOI" version="2.5.6" targetFramework="net472" />
  <package id="NPOI.OOXML" version="2.5.6" targetFramework="net472" />
  <package id="NPOI.HPSF" version="2.5.6" targetFramework="net472" />
</packages>

功能特点

1. 文件读取功能

CSV文件：

  • 兼容逗号分隔的标准CSV格式
  • 自动处理引号包围的字段
  • 兼容包含逗号的字段内容
  • 自动检测列头和新建数据结构

Excel文件：

  • 兼容.xls和.xlsx格式
  • 自动识别第一个工作表
  • 智能数据类型识别（文本、数值、日期）
  • 处理合并单元格和公式

2. 数据处理功能

数据类型推断：

  • 自动识别整数、浮点数、日期和文本
  • 基于数据分布智能判断最佳类型

数据清洗：

  • 自动去除多余空格
  • 处理空值和DBNull
  • 格式化日期和数字

错误处理：

  • 捕获并显示文件读取错误
  • 处理格式不正确的数据行
  • 提供详细的错误信息

3. 用户界面功能

直观的操作界面：

  • 清晰的按钮布局
  • 状态提示和进度指示
  • 响应式设计

数据预览：

  • 自动调整列宽
  • 交替行颜色提高可读性
  • 兼容滚动查看大数据集

数据导出：

  • 一键导出为CSV或Excel
  • 保留数据类型和格式
  • 自动命名导出文件

4. 性能优化

流式读取：

  • 大文件分块处理
  • 内存高效的数据加载

后台处理：

  • 进度条显示加载状态
  • 异步操作防止界面冻结

资源管理：

  • 及时释放文件句柄
  • 采用using语句确保资源释放

采用说明

1. 环境要求

.NET Framework 4.7.2 或更高版本

安装NuGet包：

Install-Package NPOI
Install-Package NPOI.OOXML
Install-Package NPOI.HPSF

2. 操作步骤

打开文件：

  • 点击"打开CSV文件"按钮选择CSV文件
  • 点击"打开Excel文件"按钮选择Excel文件

查看数据：

  • 文件加载后显示在DataGridView中
  • 自动识别数据类型并显示
  • 可借助滚动条查看所有数据

导出数据：

  • 加载数据后导出按钮启用
  • 点击"保存为CSV"导出为CSV格式
  • 点击"保存为Excel"导出为Excel格式

清除数据：

  • 点击"清除数据"按钮清空当前显示

3. 采用示例

// 示例：在代码中直接使用读取器
var reader = new FileReader();
// 读取CSV文件
DataTable csvData = reader.ReadCsv(@"C:datasales.csv");
// 读取Excel文件
DataTable excelData = reader.ReadExcel(@"C:datainventory.xlsx");
// 处理数据
var processor = new DataProcessor();
DataTable processedData = processor.ProcessDataTable(csvData);
// 导出数据
processor.ExportToCsv(processedData, @"C:outputprocessed.csv");
processor.ExportToExcel(processedData, @"C:outputprocessed.xlsx");

技术亮点

1. CSV解析算法

private string[] ParseCsvLine(string line)
{
    var fields = new List<string>();
    bool inQuotes = false;
    var currentField = new StringBuilder();
    
    for (int i = 0; i < line.Length; i++)
    {
        char c = line[i];
        
        if (c == '"')
        {
            // 处理转义引号
            if (inQuotes && i + 1 < line.Length && line[i + 1] == '"')
            {
                currentField.Append('"');
                i++;
            }
            else
            {
                inQuotes = !inQuotes;
            }
        }
        else if (c == ',' && !inQuotes)
        {
            // 字段结束
            fields.Add(currentField.ToString());
            currentField.Clear();
        }
        else
        {
            currentField.Append(c);
        }
    }
    
    // 添加最后一个字段
    fields.Add(currentField.ToString());
    
    return fields.ToArray();
}

2. Excel数据类型处理

switch (cell.CellType)
{
    case CellType.String:
        dataRow[j] = cell.StringCellValue;
        break;
    case CellType.Numeric:
        if (DateUtil.IsCellDateFormatted(cell))
        {
            dataRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd");
        }
        else
        {
            dataRow[j] = cell.NumericCellValue;
        }
        break;
    case CellType.Boolean:
        dataRow[j] = cell.BooleanCellValue;
        break;
    case CellType.Formula:
        dataRow[j] = cell.NumericCellValue;
        break;
    default:
        dataRow[j] = cell.ToString();
        break;
}

3. 数据类型推断

private Type InferColumnType(DataTable table, string columnName)
{
    int totalRows = table.Rows.Count;
    if (totalRows == 0) return typeof(string);
    
    int intCount = 0;
    int doubleCount = 0;
    int dateCount = 0;
    int stringCount = 0;
    
    foreach (DataRow row in table.Rows)
    {
        // 分析每个值的类型...
    }
    
    // 根据分布确定最佳类型
    if ((double)intCount / totalRows > 0.8) return typeof(int);
    if ((double)(intCount + doubleCount) / totalRows > 0.8) return typeof(double);
    if ((double)dateCount / totalRows > 0.8) return typeof(DateTime);
    
    return typeof(string);
}

扩展功能

1. 添加数据库兼容

public class DatabaseExporter
{
    public void ExportToSqlServer(DataTable data, string connectionString, string tableName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = tableName;
                
                // 映射列
                foreach (DataColumn column in data.Columns)
                {
                    bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                }
                
                bulkCopy.WriteToServer(data);
            }
        }
    }
}

2. 添加数据过滤功能

public DataTable FilterData(DataTable data, string columnName, string filterValue)
{
    DataView dv = new DataView(data);
    dv.RowFilter = $"[{columnName}] LIKE '%{filterValue}%'";
    return dv.ToTable();
}

3. 添加数据统计功能

public DataTable GetSummaryStatistics(DataTable data)
{
    DataTable summary = new DataTable();
    summary.Columns.Add("Column", typeof(string));
    summary.Columns.Add("Type", typeof(string));
    summary.Columns.Add("Count", typeof(int));
    summary.Columns.Add("NullCount", typeof(int));
    summary.Columns.Add("Min", typeof(string));
    summary.Columns.Add("Max", typeof(string));
    summary.Columns.Add("Avg", typeof(string));
    
    foreach (DataColumn column in data.Columns)
    {
        DataRow row = summary.NewRow();
        row["Column"] = column.ColumnName;
        row["Type"] = column.DataType.Name;
        
        int count = data.Rows.Count;
        int nullCount = 0;
        double? min = null, max = null, sum = 0;
        int numericCount = 0;
        
        foreach (DataRow dataRow in data.Rows)
        {
            object value = dataRow[column];
            if (value == DBNull.Value || value == null)
            {
                nullCount++;
                continue;
            }
            
            if (column.DataType == typeof(int) || column.DataType == typeof(double))
            {
                double numValue = Convert.ToDouble(value);
                if (!min.HasValue || numValue < min) min = numValue;
                if (!max.HasValue || numValue > max) max = numValue;
                sum += numValue;
                numericCount++;
            }
        }
        
        row["Count"] = count;
        row["NullCount"] = nullCount;
        row["Min"] = min?.ToString() ?? "N/A";
        row["Max"] = max?.ToString() ?? "N/A";
        row["Avg"] = numericCount > 0 ? (sum / numericCount).ToString() : "N/A";
        
        summary.Rows.Add(row);
    }
    
    return summary;
}

常用问题解决

1. 文件格式问题

问题：中文乱码

解决：在读取CSV时指定正确的编码

// 尝试不同编码
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (var reader = new StreamReader(filePath, Encoding.GetEncoding("GB2312")))

2. 大文件处理

问题：加载大文件时内存不足

解决：采用分页加载

// 分批读取大文件
public DataTable ReadLargeCsv(string filePath, int batchSize = 1000)
{
    // 实现分页读取逻辑
}

3. 特殊格式处理

问题：Excel中的公式值

解决：采用NPOI的公式求值器

IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
CellValue cellValue = evaluator.Evaluate(cell);

项目总结

结合项目来看，这个C#解决方案提供了完整的CSV/Excel文件读取和显示功能，具有以下特点：

多格式兼容：

  • 兼容CSV和Excel文件（.xls/.xlsx）
  • 智能识别文件格式
  • 处理各种数据格式和编码

强大的数据处理：

  • 自动数据类型推断
  • 数据清洗和转换
  • 空值处理和错误恢复

用户友好界面：

  • 直观的操作流程
  • 实时状态反馈
  • 响应式数据展示

灵活的导出功能：

  • 兼容CSV和Excel导出
  • 保留数据类型和格式
  • 自定义导出选项

健壮的错误处理：

  • 全面的异常捕获
  • 详细的错误信息
  • 优雅的降级处理

理解这一步时，以上就是C#读取CSV/Excel文件数据同时显示在DataGridView的详细内容，更多关于C#读取CSV/Excel数据并显示的资料请关注脚本之家其它相关文章！

您可能感兴趣的文章:
  • C#实现Excel与CSV批量转换工具实战
  • C#采用Spire.XLS实现Excel到CSV的转换与导出功能
  • C#采用Spire.XLS for .NET实现Excel和CSV互转的全过程
  • C#实现Excel转为CSV的高效解决方案
  • C#对Excel表csv文件的读写操作详解

相关文章

精彩推荐