c#实现从txt文本里读取大量数据到Datetable再绑定到datagirdview，再导出到excel
客户有一个需求是把扫描和称重的数据写入到txt文本里面
然后导出显示到界面，最后导出到Excel。
就是这个流程。
从txt导出到datetable中。。。还是直接上代码吧
/// <summary>
/// txt导出到datatable
/// </summary>
/// <param name="dt">datatable</param>
/// <param name="path">txt文件路径</param>
/// <param name="a">列表序号</param>
/// <returns></returns>
private System.Data.DataTable datateble(System.Data.DataTable dt,string path,int a)
{
try
{
StreamReader st = new StreamReader(path);
dt.Columns.Add("序号", typeof(string));
dt.Columns.Add("数据内容", typeof(string));
dt.Columns.Add("时间", typeof(string));
String line;
while ((line = st.ReadLine()) != null)
{
a++;
DataRow dr = dt.NewRow();
string[] strs = line.Split('|');
dr[0] = a.ToString();
dr[2] = strs[0];
dr[1] = strs[1];
dt.Rows.Add(dr);
}
st.Close();
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
返回datatable类型，然后 绑定到datagirdview显示
导出到excel文件
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Threading;
using System.IO;
using Commonication_Program.CONFIG;
using System.Data;
using System.Threading.Tasks;
namespace Commonication_Program.RINTRUN
{
class Rt_Excel
{
public Microsoft.Office.Interop.Excel.Application m_xlApp = null;
/// <summary>
/// 将DataTable数据导出到Excel表
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
/// <param name="path">txt存放路径</param>
public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName,string path)
{
if (tmpDataTable == null)
{
return;
}
long rowNum = tmpDataTable.Rows.Count;//行数
int columnNum = tmpDataTable.Columns.Count;//列数
m_xlApp = new Microsoft.Office.Interop.Excel.Application();
m_xlApp.DisplayAlerts = false;//不显示更改提示
m_xlApp.Visible = false;
try
{
Microsoft.Office.Interop.Excel.Workbooks workbooks = m_xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
if (rowNum > 65536)//单张Excel表格最大行数
{
long pageRows = 65535;//定义每页显示的行数,行数必须小于65536
int scount = (int)(rowNum / pageRows);//导出数据生成的表单数
if (scount * pageRows < rowNum)//当总行数不被pageRows整除时，经过四舍五入可能页数不准
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(
missing, missing, missing, missing);//添加一个sheet
}
else
{
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;//表头信息
}
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
if (pageRows * sc >= rowNum)
{
result = (int)rowNum;
}
else
{
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Microsoft.Office.Interop.Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;//Sheet表最大化
}
}
else
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;
}
int r = 0;
for (r = 0; r < rowNum; r++)
{
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Microsoft.Office.Interop.Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
}
workbook.Saved = true;
workbook.SaveCopyAs(strFileName);
workbook.Close();
MessageBox.Show("文件已导出！","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
FileStream stream = File.Open(path, FileMode.OpenOrCreate, FileAccess.Write);
stream.Seek(0, SeekOrigin.Begin);
stream.SetLength(0);
stream.Close();
}
catch (Exception ex）
{
MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
m_xlApp = null;
}
}
}
}
导出十几万的数据到excel表，需要几秒钟，导出后清空txt文本。
数据越大导出越慢，当然一天的数据产量也最多在十万左右
这种方式客户欣然接受了
当然还有更快导出更大量数据到excel的方式，懒得去弄了
