我要评论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=".netframework,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:\data\sales.csv"); // 读取excel文件 datatable exceldata = reader.readexcel(@"c:\data\inventory.xlsx"); // 处理数据 var processor = new dataprocessor(); datatable processeddata = processor.processdatatable(csvdata); // 导出数据 processor.exporttocsv(processeddata, @"c:\output\processed.csv"); processor.exporttoexcel(processeddata, @"c:\output\processed.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数据并显示的资料请关注代码网其它相关文章！
发表评论